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--