Oracle Stored Procedures

1. Definition
The so-called stored procedure (Stored Procedure) is a set of SQL statements that are used to complete a particular database function, which is
Compiled and stored in the database system. In use, the user specifies the stored procedure name that is already defined and gives the corresponding stored procedure parameters
To invoke and execute it to complete one or a series of database operations.

2. Creation of stored procedures
The Oracle stored procedure consists of three parts: the procedure Declaration, the execution procedure part, and the stored procedure exception.

(1) non-parametric stored procedure syntax

Create or Replace procedure Noparpro     as  // statement   ;    begin // implementation   ;   Exception// stored procedure exception   ;    End;

(2) example of a stored procedure with a parameter

Create or Replace procedure queryempname (sfindno emp.empno%type)   as     sName emp.ename  %type;     Sjob emp.job%type;   begin          ....  Exception ...         .   End;

(3) with parameter stored procedure with assignment method

Create or Replace procedurerunbyparmeters (IsalinchEmp.sal%type, sname outvarchar, SjobinchOutvarchar)    asicount Number; begin        Select Count(*) intoIcount fromEmpwhereSal>Isal andJob=Sjob; ifIcount=1  Then          ....        Else         ....       End if; Exception whenToo_many_rows ThenDbms_output. Put_Line ('return value more than 1 rows');  whenOthers ThenDbms_output. Put_Line ('An error occurred during the runbyparmeters process! '); End;

Where the parameter in represents the input parameter, which is the default mode of the parameter.
Out represents the return value parameter, and the type can use a legitimate type in any Oracle.
Parameters defined in out mode can only be assigned within the procedure body, indicating that the parameter can pass a value callback with his procedure
In-out indicates that the parameter can pass a value to the procedure, or a value can be passed out.

(4) stored procedure midstream definition use

 as //definition (cursor a result set that can be traversed)CURSORCur_1 is     SELECTArea_code,cmcode,SUM(Rmb_amt)/10000RMB_AMT_SN,SUM(Usd_amt)/10000USD_AMT_SN frombgd_area_cm_m_base_tWHEREYm>=Vs_ym_sn_beg andYm<=Vs_ym_sn_endGROUP  byArea_code,cmcode; begin //execute (Common for statement traversal cursor) forRecinchcur_1 LOOPUPDATExxxxxxxxxxx_tSETRmb_amt_sn=Rec.rmb_amt_sn,usd_amt_sn=REC.USD_AMT_SNWHEREArea_code=Rec.area_code andCmcode=Rec. Cmcode andYm=Is_ym; ENDLOOP;

(5) Definition of cursors

--display the cursor's handlingDeclare  ---DECLARE cursor, create and name a SQL workspacecursorCursor_name is      SelectReal_name fromAccount_hcz; V_realnamevarchar2( -);begin     Opencursor_name;---Open cursor, execute result set generated by SQL statement    FetchCursor_name intoV_realname;--extract cursor to extract records from result setDbms_output.put_line (v_realname); Closecursor_name;--Close CursorEnd;

3. Calls to stored procedures in Oracle

(1) Procedure Call method one

Declarerealsal emp.sal%type; Realnamevarchar( +); Realjobvarchar( +); begin   //The procedure call begins realsal:=1100; Realname:="'; Realjob:='Clerk'; Runbyparmeters (realsal,realname,realjob);--Must be dbms_output sequentially. Put_Line (Realname||'   '||realjob); END;//End of Procedure Call

(2) Procedure call mode two

Declarerealsal emp.sal%type; Realnamevarchar( +); Realjobvarchar( +); begin    //The procedure call begins realsal:=1100; Realname:="'; Realjob:='Clerk'; --Specify the value corresponding variable order variable runbyparmeters (sname=Realname,isal=Realsal,sjob=realjob); Dbms_output. Put_Line (Realname||'   '||realjob); END;//End of Procedure Call

(3) Procedure Call method Three (SQL command line mode)

1Sql>execProc_emp ('parameter 1','Parameter 2');//no return value procedure call2Sql>varVsal NumberSQL> execProc_emp ('parameter 1',: Vsal);//There are return value procedure calls or: Call Proc_emp ('parameter 1',: Vsal);//There is a return value procedure call

Oracle Stored Procedures