Stored procedure

A set of SQL statements used to complete specific database functions, which are compiled and stored in the database system. In use, the user calls and executes the stored procedure by specifying the defined stored procedure name and giving the corresponding stored procedure parameters, thus completing one or a series of database operations.

It consists of three parts: procedure declaration, execution procedure and stored procedure exception (optional).

Example

create or replace procedure sp_
(  p_sResult   out integer,--Return value
   p_ID       in   number --ID
) 
as
      v_id       number(16);
      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 next ID value of the table

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   --Combinatorial table   merge into Determination table
  on (upper(a.Name)=upper(b.Name))                       --Table name capitalization
  when matched then update set a.ID= case when b.ID>a.ID then b.ID else a.ID+ 1 end   -- update;  case when   then  else  end;
  when not 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. Return list data (cursor)

CREATE OR REPLACE PROCEDURE SP_CONTENT (
      o_result out types.cursorType, --Result set
      ip_pwf  in varchar2) --Related process     
as
   /*  --Type declaration is a cursor variable
    create or replace package TestPackage is type outlist is ref cursor;
 */
  v_sql varchar2(1000);
  vn_count  numeric(12,0);
  vn_num    numeric(12,0);
  vn_id     numeric(12,0);
  vs_table  varchar2(30);
  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 then
   vn_inst:=0;
 end;
 open o_result for
 select caller,enddate,content from tuser t where class_code < vn_inst;
end SP_CONTENT;