Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

EnterpriseDB: Declaring and Calling Procedures Oracle Tips by Burleson

The blocks in a procedure follow the same structure as an anonymous block:  declaration, execution and exception.  A procedure may have named and anonymous blocks nested within it.

This is the syntax for a procedure:

CREATE OR REPLACE PROCEDURE <procedure name>[(<variable list>)]
AS
  [<declarations>]
BEGIN
  <executable statements>
  [RETURN;]
[EXCEPTION
  <exceptions>]
END;

As you can see, the syntax looks much like an anonymous block but instead of a DECLARE keyword, it uses a CREATE OR REPLACE PROCEDURE syntax.  A RETURN in a procedure is an unconditional exit from that procedure; it will exit all loops and conditional logic and immediately terminate the subprogram.

This is the simple form of a procedure following our early NULL logic:

CREATE OR REPLACE PROCEDURE null_proc
AS 
BEGIN
  NULL;
END;

The above procedure doesn't do much beyond compile.  It is a valid procedure though.  No procedure does much until you call it (this one just doesn't do anything even then).  Below is the call for the new procedure, null_proc:

BEGIN
  null_proc;
END;

Below is a little procedure that is more useful.  This procedure accepts two parameters: varchar2 and number.  The number is added to 10 and both parameters are displayed to the screen.

CREATE OR REPLACE PROCEDURE show_stuff(
   p_char_data IN VARCHAR2,
   p_number_data IN NUMBER )
AS
BEGIN

  DBMS_OUTPUT.PUT_LINE( p_char_data ||
     to_char( p_number_data + 10 ) ); 

END;

I call my procedure:

BEGIN
  show_stuff( 'My number is: ', 15);
END;

I see my output:

INFO:  My number is: 25

A very good use (best practice) of procedures and ref cursors is the ability to reuse a select statement.  When you embed select statements in your code, it gets very expensive to maintain when schemas change.  Rather than doing that, you can call ref cursor with parameters.

In this example, I show you how to query the emp table for a specific row:

CREATE OR REPLACE PROCEDURE get_emp(
  p_emp_no IN emp.empno%TYPE,
  p_emp_data OUT SYS_REFCURSOR )
AS
BEGIN
  OPEN p_emp_data FOR SELECT * FROM emp WHERE empno = p_emp_no;
END;

The call:

DECLARE
  v_emp_curs SYS_REFCURSOR;
  v_emp_row emp%ROWTYPE;

BEGIN 

  v_emp_row.empno := 7788; 

  get_emp( v_emp_row.empno, v_emp_curs ); 

  FETCH v_emp_curs INTO v_emp_row;
  DBMS_OUTPUT.PUT_LINE( 'Emp is: ' || v_emp_row.ename );
  CLOSE v_emp_curs; 

END; 

The output: 

INFO:  Emp is: SCOTT

The procedure opens a ref cursor with the expected query.  The parameters are an OUT ref cursor to be passed back and an IN employee number.  This code could be written to default to all rows if p_empno is null.

The calling block declares a ref cursor (which is the cursor coming back).  It also declares an emp row type and populates the row type empno with 7788.  It passes this variable and the ref cursor to the newly created procedure.

When the procedure completes, the cursor is open.  The calling routine fetches a row and displays it before closing the cursor.  Using code from the section above on ref cursors, this code would easily be modified to handle multiple rows (by looping and fetching and closing when the cursor is empty).

Unlike Oracle, EnterrpiseDB does not support nested named blocks.  It does support unnamed, nested blocks.

CREATE OR REPLACE PROCEDURE show_stuff(
   p_char_data IN VARCHAR2,
   p_number_data IN NUMBER )
AS

  v_ename emp.ename%TYPE; 

  -- Nested named block
  -- Cannot do in EnterpiseDB but is allowed in Oracle
  PROCEDURE show_data( p_string IN VARCHAR2 )
  AS
  BEGIN
    DBMS_OUTPUT.PUT_LINE( p_string );
  END; 

BEGIN 

--  show_data(p_char_data || to_char( p_number_data + 10 ) ); 

  -- Nested anonymous block
  BEGIN
    SELECT ename
      INTO v_ename
      FROM emp
      LIMIT 1; 

    show_data( 'Ename: ' || v_ename );
  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      show_data( 'No Data Found' );
  END; 

END;

In general, most of the code you write will be procedures.  If you need more than a single return value, you should always use a procedure. There are times where you only return a single value and/or you need to use the value in an expression.  At those times, it is preferable to write a function instead of a procedure.

        
     

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.