Example of creating and using Oracle Stored Procedure

Example of creating and using oracle Stored Procedure

1. Create a stored procedure
Create or replace package pk_1 as // statement for creating a package
TYPE cur is ref cursor; // declare a pointer
Procedure p1 (cr out cur );
End;

/

Create or replace package body pk_1 as // create a package
Procedure p1 (cr out cur) is
Begin
Open cr for select STATUS, table_name from dba_tables;
End;
End;

/

2. Call (in sqlplus ):
Set serveroutput on // set to allow output operations
/

Declare
Cc pk_1.cur;
S varchar2 (20 );
T varchar2 (30 );
C11 number: = 0; // The variable c11 must be assigned an initial value. Otherwise, the Operation will fail.
Begin
Pk_1.p1 (cc );
Loop
Fetch cc into s, t;

Exit when cc % notfound;

C11: = c11 + 1;

Dbms_output.put_line (status = | s |; table_name = | t );
End loop;
Close cc;
Dbms_output.put_line (c11 );
End;

/

3. Call stored procedures that do not return a result set

Create or replace procedure p1
(
Parameter1 in number,
Parameter2 out number
) Is
Value1 INTEGER: = 0;
Begin
Select count (*) into value1 from dba_tables where table_name like % t %;
Parameter2: = value1;
End p1;

Run procedure in sqlplus
Method 1:

(1) set serveroutput on
Declare
V_p_o_succeed varchar2;
Begin
Pr_jwry_info (a_p_ I _date, v_p_o_succeed );
Dbms_output.put_line (v_p_o_succeed );
End;
Method 2:

(2) var cc varchar (20)
P1 (1,: cc)
Print cc;