Stored Procedures

Stored Procedures

1.Its a PL/SQL Named Sub program,Which compiled and Stored in the Database
for the repeated usage.
2.Its a Schema Object
3."Procedure Perform an action"
4.We can call the procedure inside the anonymous block.

Example Diagram::

=================

=Stored Program =

=================

| |

| |

| |

-----------------------------------

=  Stored  Procedure =

+

=   Stored  Function   =

-----------------------------------

| |

| |

| |

-----------------------------------

=  Stand Alone + Package  =

-----------------------------------

Note::

*If the Procedure is created with out any bug
then the Message Shows "Procedure Created".

*If the Procedure have any bug then its shows
like "Procedure Created with Compilation Error"

*In this case use "SHOW ERROR" Command to
list out the Error in the Procedure and debug the code.
*SELECT * FROM user_procedures;

SELECT text FROM user_source WHERE name = UPPER('addnum_sp');


How to execute Procedure in Oracle??

Using EXECUTE Or EXEC key word we can execute our procedure

SYNTAX::

EXECUTE Proc_Name;(If proc have arguments Then EXECUTE prc_name(arg1.....)
OR
EXEC Proc_Name;

Naming Standard::

Ends with  _SP or Start with P_

_sp or P_

Parsing Argument(Parameter Mode)

1.IN
2.OUT
3.IN OUT

Example::

CREATE OR REPLACE PROCEDURE Sum_sp
AS
   v_a   NUMBER := 100;
   v_b   NUMBER := 910;
   v_c   NUMBER;
BEGIN
   v_c := v_a + v_b;
   DBMS_OUTPUT.put_line (v_c);
END Sum_sp;
/

Procedure created.

SQL> SET SERVEROUTPUT ON;
SQL> EXEC Sum_Sp;
1010

We can Call Procedure With In Anonymouse Block::

BEGIN
   Sum_sp;
END;
/

1010


USING ARGUMENT PARSING METHOD::

CREATE OR REPLACE PROCEDURE Add_Num_Sp (p_a IN NUMBER, p_b IN NUMBER)
AS
   v_c   NUMBER;
BEGIN
   v_c := p_a + p_b;
   DBMS_OUTPUT.put_line (v_c);
END Add_Num_Sp;
/
Procedure created.

SQL> EXEC Add_Num_Sp(129,222);
351
PL/SQL procedure successfully completed.


CREATE OR REPLACE PROCEDURE addnum_sp (p_a   IN     NUMBER,
                                       p_b   IN     NUMBER,
                                       p_c      OUT NUMBER)
AS
BEGIN
   p_c := p_a + p_b;
END addnum_sp;
/
Procedure created.


METHOD I

SQL> VARIABLE A NUMBER;
SQL> EXEC addnum_sp(212,322,:A);
PL/SQL procedure successfully completed.
SQL> PRINT A;
A
----------
534

METHOD II

DECLARE
   v_out   NUMBER;
BEGIN
   addnum_sp (123, 234, v_out);
   DBMS_OUTPUT.put_line (v_out);
END;
/

357


CREATE OR REPLACE PROCEDURE phone_format (p_num IN OUT VARCHAR2)
AS
BEGIN
   p_num :=
         '('
      || SUBSTR (p_num, 1, 3)
      || ')'
      || SUBSTR (p_num, 4, 3)
      || '-'
      || SUBSTR (p_num, 7);
END phone_format;
/

Procedure created.
SQL> VARIABLE B VARCHAR2(30);
SQL> EXEC :B := '9865677997';
PL/SQL procedure successfully completed.
SQL> PRINT B;
B
---------
9865677997
SQL> EXEC phone_format(:B);
PL/SQL procedure successfully completed.
SQL> PRINT B;
B
--------------
(986)567-7997

CREATE OR REPLACE PROCEDURE phone_format (p_num       IN OUT VARCHAR2,
                                          p_err_msg      OUT VARCHAR2,
                                          p_status       OUT VARCHAR2)
AS
   abort_ex   EXCEPTION;
BEGIN
   IF LENGTH (p_num) <> 10
   THEN
      RAISE abort_ex;
   END IF;

   p_num :=
         '('
      || SUBSTR (p_num, 1, 3)
      || ')'
      || SUBSTR (p_num, 4, 3)
      || '-'
      || SUBSTR (p_num, 7);
   p_status := 'S';
EXCEPTION
   WHEN abort_ex
   THEN
      p_status := 'F';
      p_err_msg := p_num || ' is not a valid phone number';
END phone_format;
/



DECLARE
   v_pnum      VARCHAR2 (30) := '986567799';
   v_err_msg   VARCHAR2 (4000);
   v_status    VARCHAR2 (20);
BEGIN
   phone_format (v_pnum, v_err_msg, v_status);

   SELECT DECODE (v_status, 'S', 'Success', 'Failure'),
          NVL (v_err_msg, 'No Error')
     INTO v_status, v_err_msg
     FROM DUAL;

   IF v_status = 'Success'
   THEN
      DBMS_OUTPUT.put_line ('New Format : ' || v_pnum);
   END IF;

   DBMS_OUTPUT.put_line ('Exec Status : ' || v_status);
   DBMS_OUTPUT.put_line ('Err Message : ' || v_err_msg);
END;
/

Exec Status : Failure
Err Message : 986567799 is not a valid phone number
PL/SQL procedure successfully completed.


NOTATIONS IN ORACLE PROCEDURE::


     1.Possitional Notation
     2.Named Notation
     3.Mixed Notation

Possitional Notation 

EXEC add3_sp(10,20,30);

Named Notation

EXEC add3_sp(p_a => 10, p_b => 20, p_c => 30);

Mixed Notation

EXEC add3_sp(10, p_b => 20, p_c => 30);

Below is Not Possible:

--EXEC add3_sp(10, p_b => 20, 30);

--Stored Procedure End--

Read More

Exceptions In Oracle

Exception Handling In Oracle

                      Exception means "Handling the Errors".

Types of Exceptions::

        1.Pre Defined Exceptions
        2.User Defined Exceptions

1.Pre Defined Exceptions

        1.zero_divide
        2.no_data_found
        3.dup_val_on_index
        4.value_error
        5.too_many_rows
        6.others


2.User Defined Error::

        1.Age limit Check(Age_limit)
        2.Male or Female(Gender_Ex)
        3.Min or Max Salary Check

These kind of case we can declare Exceptions and Raise the Exception.

Application Error::

-00000 to -19999 : Oracle Error Range
-20000 to -20999 : User error range
PRAGMA Exception_init(name, number)
SQLERM
SELCODE

Examples

DECLARE
   v_a   NUMBER;
BEGIN
   SELECT 5 / 5
     INTO v_a
     FROM DUAL;

   DBMS_OUTPUT.put_line (v_a);
END;
/

1
PL/SQL procedure successfully completed.

ZERO_DIVIDE_ERROR::

DECLARE
   v_a   NUMBER;
BEGIN
   DBMS_OUTPUT.put_line ('----> 1 <----');

   SELECT 5 / 0
     INTO v_a
     FROM DUAL;

   DBMS_OUTPUT.put_line (v_a);
   DBMS_OUTPUT.put_line ('----> 2 <----');
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN
      DBMS_OUTPUT.put_line ('Enter Some other Number');
END;
/
----> 1 <----
Enter Some other Number
PL/SQL procedure successfully completed.

Example

DECLARE
   v_first_name   employees.first_name%TYPE;
   v_id           NUMBER := &id;
BEGIN
   SELECT First_name
     INTO v_first_name
     FROM employees
    WHERE employee_id = v_id;

   DBMS_OUTPUT.put_line ('Name : ' || v_first_name);
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN
      DBMS_OUTPUT.put_line ('Enter Some other Number');
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line (v_id || ' is not a valid employee id');
END;
/

Enter value for id: 101
Name : Neena
PL/SQL procedure successfully completed.

Example

DECLARE
   v_first_name   employees.first_name%TYPE;
   v_id           NUMBER := &id;
BEGIN
   SELECT First_name
     INTO v_first_name
     FROM employees
    WHERE employee_id = v_id;

   DBMS_OUTPUT.put_line ('Name : ' || v_first_name);
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN
      DBMS_OUTPUT.put_line ('Enter Some other Number');
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line (v_id || ' is not a valid employee id');
END;
/

Enter value for id: 1212
1212 is not a valid employee id
PL/SQL procedure successfully completed.

VALUE_ERROR::

DECLARE
   v_a   NUMBER (2);
BEGIN
   v_a := 23;
   DBMS_OUTPUT.put_line (v_a);
   v_a := 134;
EXCEPTION
   WHEN VALUE_ERROR
   THEN
      DBMS_OUTPUT.put_line ('Check the size');
END;
/
23
Check the size
PL/SQL procedure successfully completed.

Too_Many_Rows::

DECLARE
   v_empid   employees.employee_id%TYPE;
BEGIN
   SELECT employee_id
     INTO v_empid
     FROM employees
    WHERE first_name = 'Steven';

   DBMS_OUTPUT.put_line ('Employee Id : ' || v_empid);
EXCEPTION
   WHEN TOO_MANY_ROWS
   THEN
      DBMS_OUTPUT.put_line ('Declare Cursor');
END;
/
Declare Cursor
PL/SQL procedure successfully completed.

SQLERRM::

DECLARE
   v_empid   employees.employee_id%TYPE;
BEGIN
   DBMS_OUTPUT.put_line (SQLERRM (-1422));

   SELECT employee_id
     INTO v_empid
     FROM employees
    WHERE first_name = 'Steven';

   DBMS_OUTPUT.put_line ('Employee Id : ' || v_empid);
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN
      DBMS_OUTPUT.put_line ('Enter some other number');
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
         'Some other error ->' || SQLERRM || '~' || SQLCODE);
END;
/

ORA-01422: exact fetch returns more than requested number of rows
Some other error -> ORA-01422: exact fetch returns more than
requested number of rows ~ -1422
PL/SQL procedure successfully completed.


USER DEFINED EXCEPTIONS::

DECLARE
   v_empid    NUMBER := 1004;
   v_name     VARCHAR2 (4000) := 'Karthic';
   v_salary   NUMBER := 8000;
   abort_ex   EXCEPTION;
BEGIN
   IF v_salary <= 10000
   THEN
      RAISE abort_ex;
   END IF;

   INSERT INTO emp_tb
        VALUES (v_empid, v_name, v_salary);
EXCEPTION
   WHEN abort_ex
   THEN
      DBMS_OUTPUT.put_line ('Salary Should be greater than 10000');
END;
/

Salary Should be greater than 10000
PL/SQL procedure successfully completed.

APPLICATION ERROR::

DECLARE
   v_empid    NUMBER := 1004;
   v_name     VARCHAR2 (4000) := 'Karthic';
   v_salary   NUMBER := 8000;
BEGIN
   IF v_salary <= 10000
   THEN
      RAISE_APPLICATION_ERROR (-20001, 'Salary must be greater than 10000');
   END IF;

   INSERT INTO emp_tb
        VALUES (v_empid, v_name, v_salary);
END;
/

DECLARE
*
ERROR at line 1:
ORA-20001: Salary must be greater than 10000
ORA-06512: at line 7

PRAGMA exception_init(exception_name, code)

Use this name to any unnamed ORA- error that might be raised
by your program.

SQL> create table test_exinit(id NUMBER(2) NOT NULL);
Table created.

SQL> INSERT INTO test_exinit VALUES(NULL);
INSERT INTO test_exinit VALUES(NULL)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."TEST_EXINIT"."ID")

DECLARE
   not_null_ex   EXCEPTION;
   PRAGMA EXCEPTION_INIT (not_null_ex, -01400);
BEGIN
   INSERT INTO test_exinit
        VALUES (NULL);
EXCEPTION
   WHEN not_null_ex
   THEN
      DBMS_OUTPUT.PUT_LINE ('NULL value not allowed');
END;
/

NULL value not allowed
PL/SQL procedure successfully completed.

Example => Curosr + Loop + Exception:

DECLARE
   CURSOR sal_ck_c
   IS
      SELECT First_name, salary, department_id
        FROM employees
       WHERE department_id < 40;

   sal_ex   EXCEPTION;
BEGIN
   FOR i IN sal_ck_c
   LOOP
      BEGIN
         IF i.salary < 10000
         THEN
            RAISE sal_ex;
         END IF;

         DBMS_OUTPUT.put_line (i.first_name || ' salary is ' || i.salary);
      EXCEPTION
         WHEN sal_ex
         THEN
            DBMS_OUTPUT.put_line (i.first_name || 'salary is less than 10k');
      END;
   END LOOP;
END;
/

Jennifer salary is less than 10k
Michael salary is 13000
Pat salary is less than 10k
Den salary is 11000
Alexander salary is less than 10k
Shelli salary is less than 10k
Sigal salary is less than 10k
Guy salary is less than 10k
Karen salary is less than 10k

PL/SQL procedure successfully completed.

--Exception In Oracle End--

Read More