dbms-notes: writing blocks to disk: (10) Subprograms: Blocks and Stored Procedures

[ Oracle PL/SQL ]

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

Forward Declaration

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;
/

About Parameters

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

Parameter Aliasing

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.

Pages

(10) Subprograms: Blocks and Stored Procedures

No comments:

Post a Comment