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.
Or you can also write the first 4 letters of the EXECUTE keyword followed by the procedure name.
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
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 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.