Oracle Stored Procedure PROCEDURE

Stored Procedures

A set of SQL statements that are used to complete a specific database function that is compiled and stored in the database system. At the time of use, the user invokes and executes a stored procedure name that has already been defined and gives the corresponding stored procedure parameters to complete one or a series of database operations.

Contains three parts: a procedure declaration, a procedure part, an exception to a stored procedure (optional).

Example

Create or Replace procedure sp_ (  p_sresult out   integer,--return value   p_id in number   --id) as      v_id Number       (+);      Cursor  V_CHAC_CR  is                select T.money from T001 t   where t.sid=p_id;--cursor begin   Open V_CHAC_CR ();   Loop        fetch V_CHAC_CR into v_id;         Exit when V_chac_cr%notfound;        INSERT into F002 (CNT)  values (v_id);   End Loop;   Close V_CHAC_CR;   commit;   p_sresult:=1;exception--exception Handling when   others then   p_sresult:=0;   Rollback;end sp_;

Project Application

1. Get the table next ID value

CREATE OR REPLACE PROCEDURE pnextid (tablename   in VARCHAR2, idno out number   ) is SqlString   VARCHAR2 (500); BEGIN sqlstring: = ' SELECT NVL (max (ID), 0) +1 from ' | | TableName;  Execute immediate sqlstring into Idno;               --Dynamic execution  merge into Tsequence a using (select Idno as ID, TableName as Name from dual) b   --Combined into table   merge into OK table On  (Upper (A.name) =upper (b.name))                       --Capitalization when table name is  matched then update set a.id= case when B.id>a.id then B . ID else a.id+ 1 End   --update;  Case and   then  else  end;  When isn't matched then insert (ID, Name) VALUES (b.id,b.name);                           --insert;  Select ID into Idno from Tsequence WHERE upper (name) = UPPER (TableName); EXCEPTION when  OTHERS then   idno: =-1; END Pnextid;

2. Returning list data (cursors)

CREATE OR REPLACE PROCEDURE sp_content (      o_result out Types.cursortype,--result set      IP_PWF in  varchar2)--Correlation process   AS/*  --type declaration is a cursor variable    create or Replace package testpackage are type outlist is REF CURSOR; */  V_sql Varch AR2 (+);  Vn_count  Numeric (12,0);  Vn_num    Numeric (12,0);  vn_id     Numeric (12,0);  Vs_table  varchar2 (+);  Vn_inst Numeric (12,0); begin   Vn_count:=length (IP_PWF);   Vn_num:=instr (IP_PWF, '. ');   Vn_id:=to_number (substr (ip_pwf,1,vn_num-1));   Vs_table:=substr (Ip_pwf,vn_num+1,vn_count-vn_num); Begin v_sql:= ' Select NVL (max (Job_code), 0) from  ' | | vs_table| | ' WHERE company = ' | | vn_id| | "; Execute immediate v_sql into Vn_inst; Exception when   others and   vn_inst:=0; end; open O_result for select caller,enddate,content from Tuser t where c Lass_code < Vn_inst;end sp_content;

　　

　

Oracle Stored Procedure PROCEDURE