Oracle Stored Procedure demo sharing, oracle Stored Procedure demo

Oracle Stored Procedure demo sharing, oracle Stored Procedure demo

-- 1. Define the Stored Procedure

-- Definition

Create or replace procedure proc01

As n int; -- add points after defining variables

Begin

Select count (*) into n from emp;

Dbms_output.put_line ('employee table has '| n |' records! ');

End;

-- Call

Declare

Begin

Proc01 ();

End;

-- 2. Stored Procedures with Parameters

-- Definition

Create or replace procedure p3 (j1 varchar2)

As

Cursor c1 is select * from emp where job = j1;

Emper emp % rowtype;

Begin

Open c1;

Loop

Fetch c1 into emper;

Exit when c1 % notfound;

Dbms_output.put_line (emper. ename );

End loop;

Close c1;

End;

-- Call

Declare

Begin

P3 ('cler ');

End;

-- Process with Input and Output Parameters

Create or replace procedure p4 (a in int, B in int, c out int)

As

Begin

C: = 0; -- assign an initial value to c

For I in a... B

Loop

C: = c + I; -- accumulate

End loop;

End;

-- Call

Declare

A int: = 1;

B int: = 100;

C int;

Begin

P4 (a, B, c); -- call

Dbms_output.put_line (c );

End;

-- Calculate the factorial of a given number (with input and output parameters)

Create or replace procedure p5 (a in int, B out int)

As

Begin

B: = 1;

For I in 1. a -- factorial of

Loop

B: = B * I;

End loop;

End;

-- Call

Declare

A int: = 5;

B int;

Begin

P5 (a, B );

Dbms_output.put_line (B );

End;

-- Calculate the factorial of a given number (input and output parameters) (using the intermediate parameter "B)

Create or replace procedure p6 (a in out int)

As

B int;

Begin

B: = a; -- assign the value of a to B.

A: = 1; -- change the value of a to 1.

For I in 1 .. B -- loop

Loop

A: = a * I;

End loop;

End;

-- Call

Declare

A int: = 6;

Begin

P6 ();

Dbms_output.put_line ();

End;