Advantages of subprograms:
- Modularity
- Easier Application design
- Maintainability
- If underlying objects change: only subprogram needs to be modified.
- Better code clarity
- Packageability
- Reusability: Can be shared by many applications in the database.
- Better performance
- subprograms stored in p-code (exec) form. (except for FUNCTIONS)
- send a set of statements to the server in a group instead of sending individual statements.
- Reduced network traffic
- Data Security and Integrity:
- Ensure that all the activity on related tables is either performed in its entirety or not performed at all.
- Inherit the privileges of the owner by default. (AUTHID DEFINER)
Stored procedures - Procedures inherit the privileges of the owner by default
- You can use procedures to grant indirect access to the objects
- When a procedure is created it is compiled and source code and compiled form (p-code) are stored in the database.
- Procedures are stored in the database irrespective of whether or not the compilation was successfull.
- If compilation fails, USER_OBJECTS display that procedure's status as INVALID, and the procedure will FAIL to execute.
- If there are any modifications in the objects which the procedure references, it will become INVALID and will fail to execute.
- When called, the p-code is read from the disk and stored in the shared pool area (SGA).
- Check
*_PROCEDURES, *_SOURCE and *_OBJECTS for data dict info on stored procedures.
---- USER A: ----
create table emp
(name varchar2(10), sal number);
insert into emp
values ('John', 1000);
commit;
create or replace procedure ins_emp
(name varchar2, salary number)
as
begin
insert into emp values(name, salary);
dbms_output.put_line('Insert: Success');
commit;
exception
when others then
dbms_output.put_line('Error: ' ||
dbms_utility.format_error_stack());
end;
/
set serveroutput on
exec ins_emp('Mary', 12000);
select * from emp;
NAME SAL
---------- -------
John 1000
Mary 1200
---- USER B: ----
select * from a.emp;
SQL Error: ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
---- USER A: ----
grant execute on ins_emp to b;
---- USER B: ----
set serveroutput on
exec a.ins_emp('BJoe', 9000);
anonymous block completed
Insert: Success
---- USER A: ----
select * from emp;
NAME SAL
---------- -------
John 1000
Mary 1200
BJoe 9000
|
Granting indirect access to db objects.
Scenario:
|
User A:
- User A Owns table emp.
- User A needs to control access to emp, but still allow other users to add new employees.
- User A creates procedure ins_emp.
User B:
- User B do not have access to directly read or write into table A.emp.
- SELECT from B on A.emp fails
User A:
- User A grants
EXECUTE PROCEDURE on emp to User B.
User B:
- User B executes A.ins_emp.
- A.ins_emp runs with owner's privilege and inserts into A.emp.
Formal parameters can be only one of these:
- UNCONSTRAINED type name (number, varchar)
- Type constrained using %TYPE or %ROWTYPE
- Using %TYPE is recommended, because if the type of the column in the table changes, it is not necessary to change the application code.
- If the get_emp_names procedure is part of a package, you can use previously-declared public (package) variables to constrain its parameter data types. For example:
dept_number NUMBER(2);
...
PROCEDURE get_emp_names(dept_num IN dept_number%TYPE);
- Parameters can take initial values. Use either the assignment operator or the DEFAULT keyword to give a parameter an initial value. For example, these are equivalent:
PROCEDURE Get_emp_names (Dept_num IN NUMBER := 20) IS ...
PROCEDURE Get_emp_names (Dept_num IN NUMBER DEFAULT) IS ...
- Privileges required to compile the subprogram or package successfully):
- The owner of the subprogram or package must be explicitly granted the necessary object privileges for all objects referenced within the body of the code.
- The owner cannot obtain required privileges through roles.
- Package creation requires a sort. The user creating the package must be able to create a sort segment in the temporary tablespace with which the user is associated.
|
Procedure lists employees of a department
|
CREATE OR REPLACE procedure list_emp(p_dept in
employees.department_id%type default 80) as
ln employees.last_name%type;
sal employees.salary%type;
dept_out_of_range EXCEPTION;
cursor c1 (p_dept in employees.department_id%type) IS
select last_name, salary
from employees
where department_id = p_dept
order by last_name;
BEGIN
IF p_dept < 0 or p_dept > 200 THEN
RAISE dept_out_of_range;
END IF;
Open c1(p_dept);
loop
fetch c1 into ln, sal;
exit when c1%NOTFOUND;
dbms_output.put_line('Dept '|| p_dept || ', '|| ln ||', '|| sal);
end loop;
dbms_output.put_line('------ END List ----- ');
EXCEPTION
when dept_out_of_range then
dbms_output.put_line('Department '|| p_dept || ' is not a valid department');
dbms_output.put_line(sqlerrm);
dbms_output.put_line(dbms_utility.format_error_backtrace());
when others then
dbms_output.put_line(sqlerrm);
dbms_output.put_line(dbms_utility.format_error_backtrace());
END list_emp;
set serveroutput on
begin
list_emp(40);
end;
PROCEDURE list_emp compiled
anonymous block completed
Dept 40, Mavris, 6500
------ END List -----
-- IF you use an invalid department ID:
set serveroutput on
begin
list_emp(300);
end;
anonymous block completed
Department 300 is not a valid department
User-Defined Exception
ORA-06512: at DEV2.LIST_EMP", line 13
| Procedure prints last name and salary of all the
employees in the same department as the user given
|
CREATE OR REPLACE procedure list_emp2(p_user in
employees.last_name%type) as
ln employees.last_name%type;
sal employees.salary%type;
user_not_found EXCEPTION;
dept employees.department_id%type;
cursor c1 (p_dept in employees.department_id%type) IS
select last_name, salary
from employees
where department_id = p_dept
order by last_name;
FUNCTION get_dept (p_user in varchar2)
RETURN employees.department_id%type
AS
v_dept departments.department_id%type;
BEGIN
select department_id into v_dept
from employees
where upper(employees.last_name) = upper(p_user);
RETURN v_dept;
EXCEPTION
WHEN no_data_found then
RETURN -20;
END get_dept;
BEGIN
dept := get_dept(p_user);
IF dept = -20 then
RAISE user_not_found;
ELSE
Open c1(dept);
loop
fetch c1 into ln, sal;
exit when c1%NOTFOUND;
dbms_output.put_line('Dept '|| dept || ', '|| ln ||', '|| sal);
end loop;
dbms_output.put_line('------ END List ----- ');
END IF;
EXCEPTION
when user_not_found then
dbms_output.put_line('Invalid Last Name: '|| p_user);
dbms_output.put_line(sqlerrm);
when others then
dbms_output.put_line(sqlerrm);
dbms_output.put_line(dbms_utility.format_error_backtrace());
END list_emp2;
set serveroutput on
begin
list_emp2('Mavris');
end;
/
anonymous block completed
Dept 40, Mavris, 6500
------ END List -----
set serveroutput on
begin
list_emp2('Joelma');
end;
/
anonymous block completed
Invalid Last Name: Joelma
User-Defined Exception
- A forward declaration declares a nested subprogram but does not define it.
- You must define it later in the same block.
- Forward declaration and the definition must have the same subprogram heading.
DECLARE
-- Declare proc1 (forward declaration):
PROCEDURE proc1(number1 NUMBER);
-- Declare and define proc2:
PROCEDURE proc2(number2 NUMBER) IS
BEGIN
proc1(number2);
END;
-- Define proc 1:
PROCEDURE proc1(number1 NUMBER) IS
BEGIN
proc2 (number1);
END;
BEGIN
NULL;
END;
/
- Can be passed BY reference or BY value.
- BY REFERENCE: Pointer to actual parameter. ACTUAL and FORMAL parameter refer to the SAME memory location.
- BY VALUE: actual and formal parameters refer to different memory locations.
- IN mode: Actual param passed by reference. But FORMAL param CANNOT be changed.
- OUT mode: Actual param passed by value.
- If you specify NOCOPY, it might be passed by reference.
- Formal param: unitialized variable. Has NULL as initial value. Subprogram should ASSIGN a value. (So PL/SQL might COPY value from Formal to Actual at the end).
- NOCOPY is only a hint: each time the subprogram is invoked, the compiler decides, silently, whether to obey or ignore NOCOPY.
- IN OUT mode: Actual parameter is passed by value (in both directions).
- OUT and IN OUT:
- (a) if subprogram exited success: value of formal param COPIED to the acutal param.
- (b) If failure: formal value not passed. Actual retain previous value.
- IN parameters CAN be assigned a DEFAULT value when declared.
- OUT and IN OUT parameters CANNOT be assigned DEFAULT values when declared.
- PLS-00312: a POSITIONAL parameter association MAY NOT follow a named association
Default values and Constraints
If the datatype of a formal parameter is a constrained subtype the:
- if the subtype has a NOT NULL constraint, the ACTUAL parameter inherits it.
- if the subtype has the base type VARCHAR2, then ACTUAL param does not inherit the SIZE of subtype
- if subtype is numeric, actual param inherits the range, but not scale, size or precision.
set serveroutput on
declare
subtype license is varchar2(7) not null;
n license := 'DLLLDDD';
procedure p (x License) IS
begin
dbms_output.put_line(x);
end;
begin
p('1ABC123456788');
p(NULL);
end;
/
Error report:
ORA-06550: line 12, column 5:
PLS-00567: cannot pass NULL to a NOT NULL constrained formal parameter
ORA-06550: line 12, column 3:
PL/SQL: Statement ignored
- Aliasing: having TWO different names for the same memory location.
- Always occur when parameter is passed by reference.
- Can also occur when a subprogram has cursor variable parameters.
- Aliasing can occur for one invocation but not another, making subprogram results indeterminate. For example:
- If actual parameter is a global variable: an assignment to the formal parameter MAY show in the global parameter
- If the same variable is the actual parameter for two formal parameters:, then an assignment to either formal parameter might show immediately in both formal parameters.
- If the actual parameter is a package variable: an assignment to either the formal parameter or the package variable might show immediately in both the formal parameter and the package variable.
- If the subprogram is exited with an unhandled exception: then an assignment to the formal parameter might show in the actual parameter.
| CASE 1: word_list is formal parameter, with NOCOPY option.
IF compiler obeys the NOCOPY: Any update to the IN OUT parameter will reflect
immediately in the lexicon global variable.
IF compile DOES NOT obey the NOCOPY directive: any update to the IN OUT
parameter will only show up in the global var after a successful completion
of the procedure.
|
set serveroutput on
DECLARE
type definition is record (
word varchar2(20),
meaning varchar2(200));
type dictionary is varray(2000) of definition;
lexicon dictionary := dictionary(); -- global variable
procedure add_entry (
word_list IN OUT NOCOPY dictionary -- formal param no copy (should point to actual param)
) IS
Begin
word_list(1).word := 'aardvark';
end;
BEGIN
lexicon.EXTEND;
lexicon(1).word := 'aardwolf';
add_entry(lexicon); -- global variable is actual parameter
dbms_output.put_line(lexicon(1).word);
END;
/
anonymous block completed
aardvark
| CASE 2: word_list is formal parameter, with NOCOPY option.
Here an exception is raised within the procedure.
if NOCOPY was obeyed the global variable will have its
value updated despite the procedure failure..
|
set serveroutput on
DECLARE
type definition is record (
word varchar2(20),
meaning varchar2(200));
type dictionary is varray(2000) of definition;
lexicon dictionary := dictionary(); -- gloval variable
procedure add_entry (
word_list IN OUT NOCOPY dictionary -- formal param no copy
) IS
e1 exception;
Begin
word_list(1).word := 'aardvark';
raise e1;
end;
BEGIN
lexicon.EXTEND;
lexicon(1).word := 'aardwolf';
add_entry(lexicon); -- global variable is actual parameter
dbms_output.put_line(lexicon(1).word);
EXCEPTION
when others then
dbms_output.put_line(sqlerrm);
dbms_output.put_line(lexicon(1).word);
END;
/
anonymous block completed
User-Defined Exception
aardvark
| Aliasing from cursor variables |
- Cursor variable parameters are pointers.
- Therefore, if a subprogram assigns one cursor variable parameter to another,
they refer to the same memory location.
- This aliasing can have unintended results.
|
(1) The procedure has two cursor variable parameters, emp_cv1 and emp_cv2.
(2) The procedure opens emp_cv1 and assigns its value (which is a pointer) to emp_cv2.
(3) Now emp_cv1 and emp_cv2 refer to the same memory location.
(4) When the procedure closes emp_cv1, it also closes emp_cv2.
(5) Therefore, when the procedure tries to fetch from emp_cv2, PL/SQL raises an exception.
|
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
c1 EmpCurTyp;
c2 EmpCurTyp;
PROCEDURE get_emp_data (
emp_cv1 IN OUT EmpCurTyp,
emp_cv2 IN OUT EmpCurTyp
)
IS
emp_rec employees%ROWTYPE;
BEGIN
OPEN emp_cv1 FOR SELECT * FROM employees;
emp_cv2 := emp_cv1; -- now both variables refer to same location
FETCH emp_cv1 INTO emp_rec; -- fetches first row of employees
FETCH emp_cv1 INTO emp_rec; -- fetches second row of employees
FETCH emp_cv2 INTO emp_rec; -- fetches third row of employees
CLOSE emp_cv1; -- closes both variables
FETCH emp_cv2 INTO emp_rec; -- causes error when get_emp_data is invoked
END;
BEGIN
get_emp_data(c1, c2);
END;
/
Error report:
ORA-01001: invalid cursor
ORA-06512: at line 19
ORA-06512: at line 22
| Default values for PARAMETERS |
- Omitting an actual parameter does not make the value of the corresponding formal parameter NULL.
- To make the value of a formal parameter NULL, specify NULL as either the default value or the actual parameter.
Using SELECT FOR UPDATE and testing for DEFAULT parameter VALUES
- SELECT FOR UPDATE enables you to base an update on the existing values in the rows,
because it ensures that no other user can change those values before you update them.
- By default, the SELECT FOR UPDATE statement waits until the requested row lock is acquired. To change this behavior, use the NOWAIT, WAIT, or SKIP LOCKED clauses.
- When SELECT FOR UPDATE is associated with an explicit cursor, the cursor is called a FOR UPDATE cursor.
- Only a FOR UPDATE cursor can appear in the CURRENT OF clause of an UPDATE or DELETE statement.
set serveroutput on
DECLARE
PROCEDURE raise_sal_dept (
deptid in employees.department_id%type,
amount in employees.salary%type :=100, -- default
extra in employees.salary%type default 50 -- default
) IS
stmt varchar2(200);
vempid employees.employee_id%type;
vsal employees.salary%type;
-- Only a FOR UPDATE cursor can appear in the CURRENT OF clause.
-- So, a CURSOR VARIABLE CANNOT be used here. You need to define a
-- FOR UPDATE CURSOR
CURSOR vcur IS
select employee_id, salary
from employees
where department_id = deptid
FOR UPDATE;
BEGIN
OPEN vcur;
dbms_output.put_line('Updating salaries of Dept: ' || deptid);
LOOP
FETCH vcur INTO vempid, vsal;
EXIT when vcur%NOTFOUND;
dbms_output.put_line('Updating Emp Id '|| vempid ||'. New salary: ' ||
vsal ||' + '|| amount || ' + '|| extra);
UPDATE employees
set salary = salary + amount + extra
WHERE CURRENT OF vcur;
END LOOP;
END raise_sal_dept;
BEGIN
-- dept 20: 2 employees
-- dept 70: 1 employee
-- dept 1: does not exist
-- dept 30: 6 employees
raise_sal_dept(20); -- same as raise_sal_dept(20, 100, 50);
END;
/
anonymous block completed
Updating salaries of Dept: 20
Updating Emp Id 201. New salary: 13150 + 100 + 50
Updating Emp Id 202. New salary: 6150 + 100 + 50
Local Subprograms
- Local subprograms should be placed LAST in the declarative section. Otherwise, a compilation error will be generated.
- Local subprograms can ONLY be referenced from within the main program.
- So, local subprograms CANNOT be invoked from outside the parent subprogram.
Overloaded subprograms
- PL/SQL lets you overload nested subprograms, package subprograms, and type methods.
- You can use the same name for several different subprograms if their formal parameters differ in name, number, order, OR data type family
Restrictions to overloading
- You cannot use two functions with the same name that differ only in their return data type.
- You cannot use two procedures with the same name if their parameters differ only in name or mode.
- You cannot overload two subprograms if their parameters belong to the same family of data types.
