Named Vs. Unnamed PL/SQL Procedures

There are two types of PL/SQL Procedures. Those are named and unnamed. The Named procs you can use for the stand-alone task. You can use unnamed procs for ad-hoc work and in the Shell scripts.

Anonymous Block (Unnamed)

The procedure which we don’t give is called the Anonymous procedure. There is no name over here for the procedure.

Read: How to execute a stored procedure in SQL developer

[DECLARE
… optional declaration statements …]
BEGIN
… executable statements …
[EXCEPTION
… optional exception handler statements …]
END;

Sample anonymous block

Here’s an example of Anonymous PL/SQL code.

SET SERVEROUTPUT ON; 
DECLARE
 V_MYNUMBER NUMBER(2) := 1;
BEGIN
DBMS_OUTPUT.PUT.LINE('MY INPUT IS : ' V_NUMBER); 
END;

Named PL/SQL block

Here is an example named PL/SQL block. Here the name of the procedure is pl.

Read: How to write Lookup query in PL/SQL

create or replace PROCEDURE pl(aiv_text in varchar2 )
is
begin
 DBMS_OUTPUT.put_line(aiv_text);
end;
/
execute pl('my input srini');
drop procedure pl;

Here are key takeout

• The differences in the named PL/SQL block are it has the syntax of ‘CREATE or REPLACE PROCEDURE’ and IS.
• Variables are declared inside after the procedure name.
• The execute the command you can use to call the procedure. The drop procedure command you can use to drop it.

Read: How to write UDF to check input value is number or not

Recent posts