Microsoft powerpoint - p4-oracle-plsql-04

5. Subprograms
5. Subprograms
• What are Subprograms ?
– Subprograms are named PL/SQL blocks that can take • PL/SQL has two types of subprograms
5 Subprograms
• Syntax
5.1 Procedures
[CREATE [OR REPLACE]]
PROCEDURE procedure_name
[(parameter[, parameter].)]
[AUTHID {DEFINER | CURRENT_USER}] {IS | AS}
[PRAGMA AUTONOMOUS_TRANSACTION;]
[local declarations]
executable statements
[EXCEPTION
exception handlers]
- where parameter stands for the following syntax:
parameter_name [IN | OUT [NOCOPY] | IN OUT [NOCOPY]]
datatype
[{:= | DEFAULT}
DEFAULT} expression]
5 Subprograms
.5.1 Procedures
PROCEDURE procedure_name[(parameter[, parameter].)]
Procedure Specification
Declarative Part
[local declarations]
executable Part
executable statements
[EXCEPTION
exception handlers]
Exception-handling Part
END [name];
Procedure Body
Programming Lab : No4 : Ex1
PROCEDURE raise_salary (emp_id INTEGER, amount REAL) IS
current_salary REAL;
salary missing EXCEPTION;
SELECT sal INTO current_salary FROM emp
WHERE empno = emp_id;
IF current salary IS NULL THEN
RAISE salary_missing;
UPDATE emp SET sal = sal + amount
WHERE empno = emp_id;
EXCEPTION
DATA FOUND THEN
INSERT INTO emp_audit VALUES (emp_id, 'No such emp
number');
WHEN salary_missing THEN
INSERT INTO
emp audit VALUES (
END raise_salary;
emp num := 1111;
raise_salary(emp_num, 10);
5 Subprograms
.5.2 Functions
5 Subprograms
.5.2 Functions
• Example
FUNCTION sal
sal_ok (salary
REAL, title
VARCHAR2)
RETURN BOOLEAN IS
losal, hisal
INTO min_sal, max_sal
FROM sals
WHERE job
RETURN (salary >= min
max_sal);
END sal_ok;
Programming Lab : No4 : Ex2 –
function create
create or replace function insert_one(
stu_number varchar2,
varchar2)
return integer is
insert into student(snumber, sname)
values (stu_number, stu_name);
if ( SQL%ROWCOUNT > 0 ) then
DBMS_OUTPUT.PUT_LINE('Insert OK');
return(1);
DBMS_OUTPUT.PUT_LINE('Insert Failed');
end insert_one;
Programming Lab : No4 : Ex2 –
function call
integer;
result := insert_one('9757005', 'Ko') ;
DBMS_OUTPUT.PUT_LINE('OK');
DBMS_OUTPUT.PUT_
---------------------------------------------------------
variable i number
execute :i := insert_one('9757009', 'Kim.D')
Programming Lab : No4 : Ex3 –
function create
create or replace function delete_one(stu_number varchar2)
return integer is
delete from student where snumber = stu number;
stu_number
if ( SQL%ROWCOUNT > 0 ) then
return(SQL%ROWCOUNT) ;
return(0);
Programming Lab : No4 : Ex3 –
function call
i := delete_one('9757005');
if ( i >= 0 ) then
DBMS_OUTPUT.PUT_LINE(i || ' students deleted');
DBMS_OUTPUT.PUT_LINE('FAIL');
execute :i := delete_one('9757009');
5 Subprograms
.5.4 Packaging
– grouping logically related subprograms in – the subprograms can be shared by many applications. – packa • subprogram bodies• they are invisible to applications. • Thus, packages allow you to hide implementation details.
5 Subprograms
.5.4 Packaging
CREATE PACKAGE emp_actions AS -- package spec
PROCEDURE hire em
p id INTEGER, name
VARCHAR2, .);
PROCEDURE fire_employee (emp_id INTEGER);
PROCEDURE raise
raise_salary (emp
(emp_id INTEGER
END emp_actions;
5. Subprograms
• example – package body
CREATE PACKAGE BODY emp_actions AS -- package body
PROCEDURE hire_employee
(emp_id INTEGER, name VARCHAR2, .) IS
INSERT INTO
END hire_employee;
PROCEDURE fire_employee (emp_id INTEGER) IS
DELETE FROM emp WHERE empno = emp id;
END fire_employee;
PROCEDURE raise_salary (emp_id INTEGER, amount REAL) IS
UPDATE emp SET sal = sal + amount
WHERE empno = emp_id;
END raise_salary;
actions;
5 Subprograms
5.5 Specifying Parameters
– an IN parameter acts like a constant
• Therefore, it cannot be assigned a value.
PROCEDURE d b
bit_account (
IN INTEGER,
minimum_purchase CONSTANT REAL DEFAULT 10.0;
service charge
service_charge CONSTANT REAL DEFAULT 0 50;
IF amount < minimum_purchase THEN
vice charge
ice_charge; -- ca ses
compilation error
END debit_account;
5 Subprograms
5.5 Specifying Parameters
return values to the caller of a subprogram – an OUT parameter acts like a variable– The actual parameter • it cannot be a constant or an expression. • For example, _bonus(7499, salary + commission); --
causes compilation error
5. Subprograms
5.5 Specifying Parameters
– OUT formal parameters are initialized to NULL– So, the datatype of an OUT formal parameter • cannot be a subtype defined as NOT NULL SUBTYPE Counter IS INTEGER NOT NULL;
rows Counter
PROCEDURE count_emps (n OUT Counter) IS
SELECT COUNT(*) INTO n FROM emp;
count_emps(rows); -- raises VALUE_ERROR
5 Subprograms
5.5 Specifying Parameters
• pass initial values to the subprogram being called • and return updated values • an initialized variable• Therefore, it can be assigned a value and • its value can be assigned to another variable.
5. Subprograms
g Parameters
• NOCOPY
– the OUT and IN OUT parameters are passed by value.
• if the subprogram exits normally, the values assigned to the OUT and IN OUT formal l parameters are copied i corresponding actual parameters.
• When the parameters hold large data structures – such as collections, records, and instances of object typ – all this copying slows down execution and uses up memory. • To prevent that, you can specify the NOCOPY hint, – which allows the PL/SQL compiler to pass OUT and IN OUT 5 Subprograms
5.6 Overloading
can use the same name for veral different TYPE DateTabTyp IS date;
TYPE RealTabTyp IS number;
hiredate_tab DateTabTyp;
comm_tab RealTabTyp;
RealTabTyp
indx BINARY_INTEGER;
PROCEDURE initialize (tab OUT DateTabTyp, n INTEGER) IS
PROCEDURE initialize (tab OUT RealTabTyp, n INTEGER) IS
indx := 50;
initialize(hiredate_tab, indx); -- calls first
initialize(comm_tab, indx); -- calls second version

Source: http://dblab.sangji.ac.kr/downloads/dbp/P4-oracle-plsql-04.pdf