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

Your first simple Pl/SQL program...

How to write your first PL/SQL program. Writing a PL/SQL program is not difficult. This post deals with writing a simple anonymous PL/SQL block. Let me remind you, anonymous block is a block without any name, i.e. you cannot call an anonymous by name. You can only execute an anonymous block while writing it, it cannot be stored and executed later.

The program written below assigns a string to a variable and prints it.

Before writing your PL/SQL block set your serveroutput on in Sql plus. We set serveroutput on, so that we could see output on sql plus.

SET SERVEROUTPUT ON

Now we will write the block/program.


DECLARE
v_name varchar2(20);
BEGIN
v_name:='John';
DBMS_OUTPUT.PUT_LINE('My name is '||v_name);
END;
/


Above block is divided into 3 sections, i.e.

DECLARE: We have declared a variable "v_name" in this section.


BEGIN: In this section we assign a value to v_name and later print it using PUT_LINE procedure of DBMS_OUTPUT package. "||" is the concatenation operator as we have "+'' in java or "." in PHP. Also notice the line of code after BEGIN, we use ":=" for assignment in oracle instead of "=".


END: END is used to end the block and after that "/" is used to execute the block.

If everything goes right, you will see the output "John" on Sql plus.