RDBMS

Stored Procedures and Functions

A stored procedure (database procedure) is a named PL/SQL block stored in the database as an object and invoked by name; it may take parameters. A function is the same but returns a value that the caller uses.

create or replace procedure newpayment(rollno number, amt number) is
begin
   insert into payments values (rollno, sysdate, amt);
   commit;
end;
/
SQL> execute newpayment(10, 2000);

create or replace function getdueamt(prollno number) return number is
   v_fee     number(5);
   v_amtpaid number(5);
begin
   select sum(amount) into v_amtpaid from payments where rollno = prollno;
   select fee into v_fee from courses
   where ccode = ( select ccode from batches
                   where bcode in ( select bcode from students where rollno = prollno ) );
   return v_fee - v_amtpaid;
end;
/
SQL> select rollno, getdueamt(rollno) from students;    -- used like ROUND or SUBSTR

• Oracle creates a procedure even when it has errors (Warning: Procedure created with compilation errors) – use SHOW ERRORS and re-create it with OR REPLACE.
• EXECUTE runs a procedure; you must own it or have the EXECUTE privilege.
• Source code is in USER_SOURCE (where name = 'NEWPAYMENT').
• Creating needs the CREATE PROCEDURE system privilege plus object privileges on referenced objects – granted directly, not through roles.

Recompiling

Procedures are stored compiled. If one becomes INVALID (e.g. SCOTT’s procedure lacked INSERT on BOOK.PAYMENTS), Oracle recompiles it implicitly on next use; to avoid that run-time cost recompile explicitly:

alter procedure newpayment compile;
alter function  getdueamt  compile;
select status from user_objects where object_name = 'NEWPAYMENT';   -- VALID

Parameters and Parameter Modes

• Formal parameters – declared in the procedure definition (pccode, pamt); they receive values.
• Actual parameters – the values given in the call (v_ccode, 2000); copied to the formal parameters.

create or replace procedure get_dates( pccode in varchar2,
                                       first_date out date,
                                       last_date  out date ) is
begin
   select min(stdate) into first_date from batches where ccode = pccode;
   select max(stdate) into last_date  from batches where ccode = pccode;
end;
/
declare
   min_date date;
   max_date date;
begin
   get_dates('ora', min_date, max_date);
   dbms_output.put_line(min_date || ':' || max_date);
end;

12-JAN-01:15-AUG-01

NOCOPY: OUT and IN OUT parameters are passed by value by default (copied in and back out). For large structures (records, objects) the NOCOPY hint passes them by reference, saving time and memory: procedure change(pstudent in out nocopy student_type).

Definer Rights vs Invoker Rights

RAISE_APPLICATION_ERROR

Creates your own application error number and message, terminates the program and sends the error to the host. The notes give the error number range as -20001 to -20999 (Oracle’s documentation states the reserved range as -20000 to -20999).

exception
   when no_data_found then
      raise_application_error(-20100, 'Invalid course code');

Packages

A package is a collection of related procedures, functions, variables and datatypes identified by one name (which also avoids name conflicts). It has two parts:

• Specification – declares the public items, callable from outside as package_name.object_name.
• Body – defines the public procedures/functions and cursors, plus private procedures, functions and variables used only inside the package. An optional initialisation part (BEGIN … at the end of the body) runs when a user first refers to the package.

create or replace package course_pkg as
   procedure changefee (pccode varchar2, newfee number);
   function  getbatchcourse (pbcode varchar2) return varchar2;
end;
/
create or replace package body course_pkg as
   procedure changefee (pccode varchar2, newfee number) is
   begin
      update courses set fee = newfee where ccode = pccode;
      if sql%found then
         commit;
      else
         raise_application_error(-20010, 'Invalid course code');
      end if;
   end;

   function getbatchcourse (pbcode varchar2) return varchar2 is
      v_ccode courses.ccode%type;
   begin
      select ccode into v_ccode from batches where bcode = pbcode;
      return v_ccode;
   exception
      when no_data_found then
         raise_application_error(-20011, 'Invalid batch code');
   end;
end;
/
SQL> execute course_pkg.changefee('ora', 5000);

Chapter 19 Notes (PDF)

Question / Answer (15 questions)

Moderate-level questions based on the Chapter 19 notes (PDF). Click a question to see the answer.

MCQ Test : Check Your Understanding (5 questions)

Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.

Bibliography/References

The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.

1. Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
2. Oracle Corporation. (n.d.). Oracle Database PL/SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/
3. Oracle Corporation. (n.d.). Oracle Database Development Guide, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/adfns/
4. Oracle Corporation. (n.d.). Oracle Database Security Guide, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/
5. Feuerstein, S., & Pribyl, B. (2014). Oracle PL/SQL Programming (6th ed.). O’Reilly Media.