Oracle Stored Procedure Syntax

The basic syntax for ORACLE stored procedures is as follows:

Create or Replace procedure stored procedure name (parameter) is or as

Declaring parameters

Begin

Code

End stored procedure name.

Where or replace represents the creation if no such stored procedure is created and, if any, updates the stored procedure.

Here is a simple example of a stored procedure:

Create or Replace procedurevid_unregstatenocharging (Ptelnoinch varchar2, PClassinch  Number, PVidinchOutvarchar2, Pagentinch varchar2, PRoleinch varchar2, PRe out Number, Pdesc outvarchar2) isTtmpnum Number:=0; Tvidonunregrenotes Number:=0;begin    Select Count(*) intoTtmpnum fromT_onlinewhereTelno=Ptelno; if(Ttmpnum< 1) Then         Select Count(*) intoTvidonunregrenotes fromT_unreg_logwhereTelno=Ptelno; if(tvidonunregrenotes>1) ThenpRe:=Ten; Pdesc:='the user is logged out and cannot be billed.'; return; End if; End if; Commit; PRe:=0; Pdesc:='Normal'; return;Endvid_unregstatenocharging;

Stored procedures can have some logical judgments, you can also store procedure calls stored procedures, the above in represents the input parameters, out represents the output parameters.

Oracle stored procedure syntax