Stored Procedures
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 =
-----------------------------------
*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--
