Creating Subprograms

Contact Us or call 1-877-932-8228
Creating Subprograms

Creating Subprograms

It takes very little effort to transform the "Hello World" anonymous block into a stored procedure. This stored procedure is added to the database and is accessible to the database user who created it and any other user granted appropriate permissions.

Code Sample:

Stored-Procedures-and-Functions/Demos/proc_create_hello_world.sql
create procedure test1
is 
begin
 dbms_output.put_line('Hello from a stored procedure');
end;
/

exec test1

begin
  test1;
end;
/

Code Explanation

This simple example demonstrates the creation of a stored procedure named test1. The familiar BEGIN and END keywords mark the body of the procedure. The DECLARE keyword does not appear, any variables that would be declared would appear after the IS keyword.

In most cases, you will have variables in your subprogram that need to be declared prior to the BEGIN keyword.

Code Sample:

Stored-Procedures-and-Functions/Demos/proc_create_hello_someone.sql
create procedure test3
is 
  v_name VARCHAR2(25); 
begin
  v_name := 'Larry';
  dbms_output.put_line('Hello ' ||
                       v_name ||
                       ' from a stored procedure');
end;
/

exec test3

begin
  test3;
end;
/

Code Explanation

Although the DECLARE keyword does not appear explicitly in stored procedures and functions, variables can be declared in the same section of the block (immediately prior to the BEGIN keyword).

Next