Oracle Procedure statement

Oracle Procedure statement stored PROCEDURE basic syntax create or replace Procedure <procedure_name> [(<parameters>)] IS www.2cto.com [declare section] BEGIN [<statement (s)>] [EXCEPTION <exception handler (s);] END [<procedure_name>]; <> indicates required, and [] indicates optional. Simple Example 1. Insert a record directly into the table 1) create a test table -- Test table EMPcreate table EMP (id number, tax number, -- tax insurance number -- INSURANCE) 2) insert a record using the stored PROCEDURE without the parameter proc_insert_emp. insert a record to the table EMP: create or replace procedure proc_insert_empIS www.2cto.com BEGIN insert into emp (id, tax, insurance) values (3,100,500); -- insert a record to the emp table commit; -- commit the transaction END proc_insert_emp; 3) insert a record using the stored procedure with the parameter -- stored procedure proc_insert_emp, insert a record create or replace procedure proc_insert_emp (id in number, -- input parameter, receive ID value tax in number, -- input parameter, receive tax value insurance in number -- input parameter, receive insurance value) ISBEGIN insert into emp (id, tax, insurance) values (id, tax, insurance ); -- insert a record to the emp table commit; -- commit the transaction END proc_insert_emp; call: exec proc_insert_emp (4,500,600); 4) with output parameters -- stored procedure proc_deduction, calculate Tax deduction and insurance create or replace procedure proc_deduction (emp_id in number, -- input parameter, receive ID value deduction out number -- output parameter) IS www.2cto.com BEGIN select tax + insurance into deduction from emp where id = emp_id; END proc_deduction; call: set serveroutput on; declare deduction number; begin proc_deduction (2, deduction ); dbms_output.put_line ('duction is: '| deduction); end; Author: CN. programmer. luxh