How to write and execute a named PL/SQL block?

Writing and executing a named PL/SQL block is as simple as writing an anonymous PL/SQL block.

There are many ways to write a named PL/SQL block. I will be dealing with each and every way in subsequent posts.  This post deals with writing of  a stored Procedure. Stored procedures and functions are stored inside Oracle, and can be called at any place in the same way as we call any function in any programming language.

The main advantage of stored procedure is that, you don't have to write your logic again and again. You can write your logic once, and use it whenever you want, on the simple call of a procedure/function.

Now we will write the code for a simple stored procedure

 

 
[code]CREATE OR REPLACE PROCEDURE sampleproc(p_arg number)
AS
v_var number(4);
BEGIN
v_var:=p_arg;
IF(v_var>1000) then
DBMS_OUTPUT.PUT_LINE('Input greater than 1000');
else
DBMS_OUTPUT.PUT_LINE('Input less than 1000');
END IF;
END sampleproc;
/
[/code]

Now we can execute this procedure in a number of ways.

a) Execute it from some other block

The stored procedure created above can be executed from any other block, it may be anonymous block, procedure or function.

 
[code]
BEGIN
sampleproc(1001);
END;
/
[/code]

b) Execute it directly from SQLPlus.

EXECUTE sampleproc(1001);

Note: SET SERVEROUTPUT ON for output.


For more information visit