Oracle: PL/SQL - Procedures

Oracle Procedure Structure

CREATE [ OR REPLACE ] PROCEDURE Procedure_Name

[ (Parameter 1,..., Parameter N) ]

IS

[ Sequence of declarations  ]

BEGIN

[ EXCEPTION

sequence of statements for exceptions ]

END;

POINTS TO REMEMBER

  • IN (input) parameter should not be changed inside a procedure

  • IN OUT parameter acts like an initialized variable and therefore, the value can be read from and written to.

  • OUT (output) parameter is given a value inside a procedure

  • It is legal to read from OUT parameters in Oracle Version 8.0.4 and higher

  • IS or AS keyword is used instead of DECLARE in a procedure

Example:         Procedure to insert a Row into the Emp Table.

 

CREATE OR REPLACE PROCEDURE insert_emp
(emp_no IN emp.empno%Type, emp_name IN emp.ename%type,
 emp_sal IN emp.sal%type)

IS

BEGIN

INSERT INTO EMP
(empno, ename, sal)

VALUES (emp_no, emp_name, emp_sal);


DBMS_OUTPUT.PUT_LINE('Row Inserted');


END;
/

 

Code to test the Procedure.

TESTING


SET SERVEROUTPUT ON;

--Total No. of Rows

select count(*)
from emp;

--executing procedure

BEGIN

insert_emp (1234, 'ABC' ,2200);

END;
/


 

 

 

 

 

 

Custom Search
 

Home      Disclaimer      Advertise      Contact      Privacy Policy     

Copyright © 2004-10 Paked.com. All rights reserved.

Note: Site best viewed at 1024 x 768 or higher screen resolution