Procedures in PL/SQL

Creating a Procedure

Stored procedure is a procedure stored in a database (e.g., If a procedure is stored in a database, it becomes like a library function).
Local procedure is not stored in a database but used within a program (e.g., a typical procedure that can be defined within a program like in C/C++).

General Syntax for Stored Procedure or Local Procedure:

[CREATE [OR REPLACE]] PROCEDURE procedure_name [(argument [{IN | OUT | IN OUT}]
argument_type, …)] {IS | AS}
Procedure_body;

• Procedure_body is a PL/SQL block (BEGIN…END section)
• Creating a procedure is a DDL operation -- so implicit COMMIT is done
• Either IS or AS can be used
• Parameter mode (Ada style of call-by-value or reference): IN (read-only), OUT (write-only value is ignored and NOT allowed at RHS value), IN OUT (read-write), default is IN mode.
• Through parameters, procedure can return values
• If you omit CREATE OR REPLACE, the PROCEDURE becomes a local procedure.

The structure of a procedure

CREATE OR REPLACE PROCEDURE procedure_name [(...)] IS
    Declarative section                -- there is NO DECLARE keyword
BEGIN
    Execution section
EXCEPTION
    Exception section
END [procedure_name];           -- procedure_name is optional (good style) but matched with if used

=> If there are no parameters in the procedure, no parenthesis is needed in the procedure head.

Example 1:

Example of PL/SQL block that declares a Local Procedure

DECLARE

    PROCEDURE apply_discount (company_id_in IN company.company_id%TYPE, discount_in IN NUMBER) IS
    min_discount CONSTANT NUMBER := .05;
    max_discount CONSTANT NUMBER := .25;
    invalid_discount EXCEPTION;

    BEGIN
                IF discount_in BETWEEN min_discount AND max_discount THEN
                    UPDATE item
                    SET item_amount := item_amount*(1-discount_in);
                    WHERE EXISTS (SELECT 'x' FROM order
                                                  WHERE order.order_id = item.order_id
                                                       AND order.company_id=company_id_in);

                     IF SQL%ROWCOUNT = 0 THEN
                          RAISE NO_DATA_FOUND;
                    END IF;
              ELSE
                   RAISE invalid_discount;
              END IF;

    EXCEPTION
           WHEN invalid_discount THEN
                     DBMS_OUTPUT.PUT_LINE('The specified discount is invalid.');
           WHEN NO_DATA_FOUND THEN
                     DBMS_OUTPUT.PUT_LINE('No orders in the system for company:' || TO_CHAR(company_id_in));
    END apply_discount;

BEGIN

apply_discount(‘c001’, 0.3); -- a local procedure call

END;

Note: SQL%ROWCOUNT is a server variable, it stores the number of rows affected by the last statement executed.

Example 2: . Debits a bank account: When invoked or called, this procedure accepts an account number and a debit amount. It uses the account number to select the account balance from the accts database table. Then, it uses the debit amount to compute a new balance. If the new balance is less than zero, an exception is raised; otherwise, the bank account is updated. The example also illustrate the use of Exception

PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
   old_balance REAL;
   new_balance REAL;
   overdrawn    EXCEPTION;

BEGIN
SELECT bal INTO old_balance
FROM accts WHERE acctno = acct_id;

   new_balance := old_balance - amount;

   IF new_balance < 0 THEN
         RAISE overdrawn;
   ELSE
      UPDATE accts
      SET bal = new_balance WHERE acctno = acct_id;
   END IF;

EXCEPTION
WHEN overdrawn THEN
dbms_output.putline("overdrawn");

END debit_account;

Example 3: procedure raise_salary, which increases the salary of an employee: When called, this procedure accepts an employee number and a salary increase amount. It uses the employee number to select the current salary from the emp database table. If the employee number is not found or if the current salary is null, an exception is raised. Otherwise, the salary is updated.

PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS
current_salary REAL;
salary_missing EXCEPTION;

BEGIN

SELECT sal INTO current_salary
FROM emp WHERE empno = emp_id;

   IF SQL%ROWCOUNT = 0 THEN
       RAISE NO_DATA_FOUND;
   ELSIF current_salary IS NULL THEN
       RAISE salary_missing;
   ELSE
       UPDATE emp SET sal = sal + increase WHERE empno = emp_id;
   END IF;

EXCEPTION

   WHEN NO_DATA_FOUND THEN
      INSERT INTO emp_audit VALUES (emp_id, 'No such number');
   WHEN salary_missing THEN
      INSERT INTO emp_audit VALUES (emp_id, 'Salary is null');

END;

How to call a procedure

procedure_name(arguments); -- within the PL/SQL block as an executable statement
E.g. apply_discount(new_company_id, 0.15); -- 15% discount

=> If the procedure does not have any parameters, then you must call the procedure without any parameters:

E.g. display_store_summary; -- no parenthesis needed

Defining local procedures

• Local subprograms are defined in the declarative section.
• The keyword CREATE [OR REPLACE] is omitted, and start with PROCEDURE keyword.

Debugging Procedures

SQL*Plus SHOW ERRORS command will display all of the errors (e.g., line and column number for each error as well as text error message) associated with the most recently created procedural object. This command will check the USER_ERRORS data dictionary view for the errors associated with the most recent compilation attempt for that procedural object.

Constraints on Formal Parameters

• It is illegal to constrain parameters with a length, precision, and scale, e.g., VARCHAR2(20), NUMBER(2) except for anchored types such as %TYPE
• Parameter can have default values using DEFAULT or := -- in this case, you can omit certain argument.