Stored procedures or Local Procedures in sql

Stored procedures or Local Procedures:
CREATE [OR REPLACE] PROCEDURE procedure_name [(argument [{IN | OUT | IN OUT}]
argument_type, ...)] {IS | AS}

Procedure_body;

• Procedure_body is a PL/SQL block, must have at least one statement (can be NULL).
• 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. The argument_type must be unconstrained, e.g. CHAR(20) is not allowed.
• Through parameters, procedure can return values
• If you omit CREATE OR REPLACE, the PROCEDURE become 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: 

PROCEDURE apply_discount -- local procedure

(company_id 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;

 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;
   salry_missing  EXCEPTION;

BEGIN

   SELECT sal INTO current_salary FROM emp WHERE empno = emp_id;

   IF 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 within a PL/SQL block as an executable statement:
procedure_name(arguments);
E.g. apply_discount(new_company_id, 0.15); -- 15% discount
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.

7.7.2 Formal Parameters
Parameter Modes
Use parameter modes to define the behavior of formal parameters. The three parameter modes, IN (the
default), OUT, and IN OUT, can be used with any subprogram. However, avoid using the OUT and IN
OUT modes with functions. The purpose of a function is to take zero or more arguments and return a single
value. It is a poor programming practice to have a function return multiple values. Also, functions should be
free from side effects, i.e. change the values of variables not local to the subprogram.
IN: An IN parameter lets you pass values to the subprogram being called. Inside the subprogram, an IN
parameter acts like a constant. Therefore, it cannot be assigned a value. For example, the following
assignment statement causes a compilation error:

PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS

   minimum_purchase  CONSTANT REAL := 10.0;

   service_charge    CONSTANT REAL := 0.50;

BEGIN

   IF amount < minimum_purchase THEN

      amount := amount + service_charge;  -- illegal

   END IF;

...

The actual parameter that corresponds to an IN formal parameter can be a constant, literal, initialized
variable, or expression. Unlike OUT and IN OUT parameters, IN parameters can be initialized to default values.

OUT: An OUT parameter lets you return values to the caller of a subprogram. Inside the subprogram, an OUT
parameter acts like an un-initialized variable. Therefore, its value cannot be assigned to another variable or
reassigned to itself. For instance, the following assignment statement causes a compilation error:

PROCEDURE calc_bonus (emp_id INTEGER, bonus OUT REAL) IS

   hire_date  DATE;

BEGIN

   SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp   WHERE empno = emp_id;

   IF MONTHS_BETWEEN(SYSDATE, hire_date) 60 THEN

      bonus := bonus + 500;  -- syntax error

   END IF;

   ...

The actual parameter that corresponds to an OUT formal parameter must be a variable; it cannot be a
constant or expression. For example, the following procedure call is illegal:

calc_bonus(7499, salary + commission);  -- syntax error

PL/SQL checks for this syntax error at compile time to prevent the overwriting of constants and expressions. An OUT actual parameter can (but need not) have a value before the subprogram is called. However, the value is lost when you call the subprogram. Inside the subprogram, an OUT formal parameter cannot be used in an expression; the only operation allowed on the parameter is to assign it a value.
Before exiting a subprogram, explicitly assign values to all OUT formal parameters. Otherwise, the values of
corresponding actual parameters are indeterminate. If you exit successfully, PL/SQL assigns values to the
actual parameters. However, if you exit with an un-handled exception, PL/SQL does not assign values to the
actual parameters.
IN OUT: An IN OUT parameter lets you pass initial values to the subprogram being called and return updated values to the caller. Inside the subprogram, an IN OUT parameter acts like an initialized variable. Therefore, it can
be assigned a value and its value can be assigned to another variable. That means you can use an IN OUT
formal parameter as if it were a normal variable. You can change its value or reference the value in any way,
as the following example shows:

PROCEDURE calc_bonus (emp_id INTEGER, bonus IN OUT REAL) IS

  hire_date      DATE;

   bonus_missing  EXCEPTION;

BEGIN

   SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp WHERE empno = emp_id;

   IF bonus IS NULL THEN

      RAISE bonus_missing;

   END IF;

   IF MONTHS_BETWEEN(SYSDATE, hire_date) 60 THEN

      bonus := bonus + 500;

   END IF;

   ...

EXCEPTION

   WHEN bonus_missing THEN

      ...

END calc_bonus;

The actual parameter that corresponds to an IN OUT formal parameter must be a variable; it cannot be a
constant or expression.
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).