how to create stored procedures without parameters by manish sharma

While discussing the syntax in the Introduction to PL/SQL stored procedures we learnt that a stored procedure can have zero, one or many parameters. Today in this tutorial we will learn how to create a PL/SQL stored procedure with zero parameters or say without any parameters. Apart from creating a stored procedure in oracle database, we will also learn in this tutorial the multiple ways of calling a stored procedure in a PL/SQL program.

How To Create PL/SQL Stored Procedure without Parameters In Oracle Database

In the following example we will create a very simple procedure. I will try to keep the example as easy as possible so that all the PL/SQL learning enthusiasts out there can understand the process of creating a stored procedure easily.

CREATE OR REPLACE PROCEDURE pr_RebellionRider IS
  var_name VARCHAR2 (30):= 'Manish';
  var_web VARCHAR2 (30) := 'RebellionRider.com';
BEGIN
  DBMS_OUTPUT.PUT_LINE('Whats Up Internet? I am '||var_name||' from '||var_web);
END Pr_RebellionRider;
/

In the above example I have created a PL/SQL Stored procedure with the name pr_RebellionRider which has two variables capable of holding strings of VARCHAR2 datatype. In the execution section this PL/SQL procedure has only one DBMS OUTPUT statement which is displaying the strings stored into those variable back to the user in a formatted manner.

For the detailed explanation of the above code please watch video tutorial on my YouTube channel on the same topic.

How to Call PL/SQL Stored Procedures in Oracle Database

After successfully creating and compiling the stored procedure, next you have to call this subroutine. You can do so in multiple ways such as:

  • Call a PL/SQL stored procedure using EXECUTE statement.
  • Call a PL/SQL stored procedure using an Anonymous PL/SQL block.
  • Call a PL/SQL stored procedure using a Named PL/SQL block.

**If in case your subroutine such as stored procedure consists of server side  PL/SQL statement then do make sure to set the “Server Output On” to see the result.

Call a PL/SQL stored procedure using EXECUTE statement

The best way to quickly check the output of your stored procedure or test the working of your PL/SQL procedure is to call it using EXECUTE keyword. In order to call a stored procedure using EXECUTE keyword you simply have to write the same keyword followed by the name of the procedure.

EXECUTE PR_RebellionRider;

Or you can also write the first 4 letters of the EXECUTE keyword followed by the procedure name.

EXEC PR_RebellionRider; 

Both the statements are the same and will do the same work.

Call a PL/SQL stored procedure using an Anonymous PL/SQL block

The second way of calling a procedure is to place a procedure call statement inside the execution section of an anonymous PL/SQL block.

BEGIN
  PR_RebellionRider;
END;
/

You simply have to write the name of your stored procedure inside the execution section of an anonymous and named PL/SQL block. The compiler will automatically interpret that as a procedure call statement. If your procedure accepts any parameters then you can supply values for parameters here. We will talk in detail about stored procedures with parameters in our next tutorial.

Suggested Reading: PL/SQL Blocks in Oracle Database

Try yourself

The third way of calling a stored procedure in Oracle Database is by using named PL/SQL Blocks. This is what you should try yourself.

Write a PL/SQL stored procedure displaying your favorite string and then try calling it using a named PL/SQL Block such as Database Triggers or PL/SQL Function.

You can send me your answers or if you have any query then do write to me via my email or on my Facebook or Twitter. You can find all my contact links on the Contact Page of my website.  

You can help others in learning by sharing this blog with your friends on your social media. Thanks & have a great day!

Hey, if you learn faster by watching the video then look no further! I have done a brief video tutorial explaining the topic. Check it out now.