PL/SQL Procedures - Stored Procedures

Stored Procedures

What is a Stored Procedure?

A stored procedure or in simple a proc is a named PL/SQL block which performs one or more specific task. This is similar to a procedure in other programming languages. A procedure has a header and a body. The header consists of the name of the procedure and the parameters or variables passed to the procedure. The body consists or declaration section, execution section and exception section similar to a general PL/SQL Block. A procedure is similar to an anonymous PL/SQL Block but it is named for repeated usage.
We can pass parameters to procedures in three ways.
1) IN-parameters
2) OUT-parameters
3) IN OUT-parameters
A procedure may or may not return any value.
General Syntax to create a procedure is:

CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters] 

IS    

Declaration section 

BEGIN    

Execution section 

EXCEPTION    

Exception section 

END; 

IS - marks the beginning of the body of the procedure and is similar to DECLARE in anonymous PL/SQL Blocks. The code between IS and BEGIN forms the Declaration section.
The syntax within the brackets [ ] indicate they are optional. By using CREATE OR REPLACE together the procedure is created if no other procedure with the same name exists or the existing procedure is replaced with the current code.

The below example creates a procedure ‘employer_details’ which gives the details of the employee.

1> CREATE OR REPLACE PROCEDURE employer_details

2> IS 

3>  CURSOR emp_cur IS 

4>  SELECT first_name, last_name, salary FROM emp_tbl;

5>  emp_rec emp_cur%rowtype;

6> BEGIN 

7>  FOR emp_rec in sales_cur 

8>  LOOP 

9>  dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name

10>    || ' ' ||emp_cur.salary);

11> END LOOP;

12>END;

13> /

How to execute a Stored Procedure?

There are two ways to execute a procedure.

1) From the SQL prompt.

EXECUTE [or EXEC] procedure_name; 

2) Within another procedure – simply use the procedure name.

procedure_name;

NOTE: In the examples given above, we are using backward slash ‘/’ at the end of the program. This indicates the oracle engine that the PL/SQL program has ended and it can begin processing the statements.

Procedures VS Functions

Procedures are traditionally the workhorse of the coding world and functions are traditionally the smaller, more specific pieces of code. In general, if you need to update the chart of accounts, you would write a procedure. If you need to retrieve the organization code for a particular GL account, you would write a function.
Here are a few more differences between a procedure and a function:

• A function MUST return a value
• A procedure cannot return a value
• Procedures and functions can both return data in OUT and IN OUT parameters
• The return statement in a function returns control to the calling program and returns the results of the function
• The return statement of a procedure returns control to the calling program and cannot return a value
• Functions can be called from SQL, procedure cannot
• Functions are considered expressions, procedure are not

That's about all the differences I can think of off the top of my head. Can you think of any more?

Parameters

Named PL/SQL programs (procedures and functions) can take parameters. Parameters are named variables that are available to a program and that modify program behavior and/or data. Parameters are optional on both procedures and functions.
Parameters are declared when a procedure or function are declared and are declared between an open and a close parenthesis (()).
Parameters may be named anything that follows Oracle naming standards. Keep them under 30 characters, they must start with a letter and contain no spaces. There additional rules but those are the ones that are most commonly violated.

There are three types of parameter: IN, OUT and IN OUT.

An IN parameter is used an input only. An IN parameter cannot be changed by the called program.
An OUT parameter is initially NULL. The program assigns the parameter a value and that value is returned to the calling program.

An IN OUT parameter may or may not have an initial value. That initial value may or may not be modified by the called program. Any changes made to the parameter are returned to the calling program.
Parameters are declared with data types but without data type length or precision. That means that a parameter may be declared as VARCHAR2 but it will not be declared with a length component (VARCHAR2(30) would not be valid).

Parameters may also be assigned a default value. You can either use the assignment operator (:=) or use the DEFAULT keyword. When a parameter has a default value, you do not need to include that parameter in the call. You MUST always include IN OUT and OUT parameters.

Sample parameter declarations:

( 
    parameter_1 IN VARCHAR2 := 'ABC',
    parameter_2 IN VARCHAR2 DEFAULT 'ABC',
    parameter_3 IN OUT NUMBER,
    parameters_can_be_named_anything OUT DATE
  )

Parameter Reference

Calling programs may refer to parameters either positionally or by named notation. It is considered a best practice to refer to parameters via named notation. Almost no one does this consistently though (myself included).
Positional notation refers to listing the parameters by the position that they are declared. Using the example above, we could positionally refer to the parameters as:

('def', 'ghi', v_number_variable, v_date_variable)

For Named notation, you would use the actual name of the parameter. This is very handy for large parameter lists and for those parameter lists that have plenty of default values. When using named notation, the parameters do not have to be listed in the order of declaration.

(parameter_3 => v_number_variable, parameter_1 => 'def', v_date_variable => v_date_variable)

Procedures

The procedure is a program that performs an action and does not return a value (outside of IN OUT and OUT parameters).
A procedure is declared as:

CREATE OR REPLACE PROCEDURE 
        () 
AS
  
BEGIN
  
  
END;

A simple PL/SQL Procedure Example:

CREATE OR REPLACE PROCEDURE my_first_proc
       (p_name IN VARCHAR2 := 'Lewis',
        p_address IN VARCHAR2 := '123 Mockingbird Ln',
        p_an_in_out_parameter IN OUT NUMBER,
        p_an_out_parameter OUT DATE ) 
AS

  v_a_variable VARCHAR2(30);

BEGIN
  IF p_name = 'Lewis'
  THEN
    DBMS_OUTPUT.PUT_LINE( p_name || ': ' ||
          p_address );
  END IF;
  
  v_a_variable := 99;
  p_an_in_out_parameter := v_a_variable;
  p_an_out_parameter := SYSDATE;
          
END;

You would call this procedure as:

DECLARE
  v_employee VARCHAR2(30) := 'BillyBob';
  v_number NUMBER := 22;
  v_date DATE;
BEGIN

  my_first_proc(
      p_name => v_employee,
      p_an_in_out_parameter => v_number,
      p_an_out_parameter => v_date );
      
  DBMS_OUTPUT.PUT_LINE(
        v_employee || ', ' ||
        to_Char(v_number) || ', ' ||
        to_char(v_date) );

  my_first_proc(
      p_an_in_out_parameter => v_number,
      p_an_out_parameter => v_date );
      
  DBMS_OUTPUT.PUT_LINE(
        v_employee || ', ' ||
        to_Char(v_number) || ', ' ||
        to_char(v_date) );

END;

Functions

The function is a program that might perform an action and does return a value.
A function is declared as:

CREATE OR REPLACE FUNCTION 
        () 
RETURN 
AS
  
BEGIN
  
  
  RETURN 
END;

We can convert the above procedure to a function and make it return the variable it declares:

CREATE OR REPLACE FUNCTION my_first_func
       (p_name IN VARCHAR2 := 'Lewis',
        p_address IN VARCHAR2 := '123 Mockingbird Ln',
        p_an_in_out_parameter IN OUT NUMBER,
        p_an_out_parameter OUT DATE ) 

RETURN VARCHAR2

AS

  v_a_variable VARCHAR2(30);

BEGIN
  IF p_name = 'Lewis'
  THEN
    RETURN -1;
  END IF;
  
  v_a_variable := 99;
  p_an_in_out_parameter := v_a_variable;
  p_an_out_parameter := SYSDATE;
          
  RETURN v_a_variable;

END;

You would call this function as:

DECLARE
  v_employee VARCHAR2(30) := 'BillyBob';
  v_number NUMBER := 22;
  v_date DATE;
  v_variable VARCHAR2(30);
BEGIN

  v_variable := 
  my_first_func(
      p_name => v_employee,
      p_an_in_out_parameter => v_number,
      p_an_out_parameter => v_date );
      
  DBMS_OUTPUT.PUT_LINE(
        v_variable || ', ' ||
        v_employee || ', ' ||
        to_Char(v_number) || ', ' ||
        to_char(v_date) );

  v_variable := 
  my_first_func(
      p_an_in_out_parameter => v_number,
      p_an_out_parameter => v_date );
      
  DBMS_OUTPUT.PUT_LINE(
        v_variable || ', ' ||
        v_employee || ', ' ||
        to_Char(v_number) || ', ' ||
        to_char(v_date) );

END;