Hey!! Friends, we are back again with a new article of ORACLE PL/SQL TUTORIAL. In this article, we will discuss How to write the Procedures in PL/SQL.
Please also read the previous article Arrays in PL/SQL – Oracle PL/SQL Tutorial
The topics which will in this article:
- Introduction of Subprogram
- Creating Procedures in PL/SQL
- Executing Procedures in PL/SQL
- Deleting the Procedures in PL/SQL
- FAQS (Frequently Asked Questions)
- At a schema level
- Inside the package
- Inside the PL/SQL block
- Functions − These subprograms return a single value, mainly it uses for computing and returns a value.
- Procedures − These subprograms do not return a particular value directly, it mainly uses to perform an action.
- Header section
- Declaration section
- Execution section
- Exception handling section
What is Subprogram?
A subprogram is a unit/module that performs a specific task. All subprograms combine to form an extensive program. This is called the ‘Modular design’. A subprogram can be contacted by another subprogram or program which is called the calling program.
A subprogram is created by−
A subprogram is standalone at the Schema level and creates by using the CREATE PROCEDURE or the CREATE FUNCTION statement. In the database, It is stored, and also like other tables or programs, it can be deleted by using the DROP PROCEDURE or DROP FUNCTION statement.
Like Function, A subprogram also created inside a package is a packaged subprogram. Like Function, we can also delete the package with the DROP PACKAGE statement. We will discuss more packages in the chapter ‘PL/SQL – Packages.’
PL/SQL provides two types of subprograms −
In this article, we will focus on PL/SQL Procedures, we will discuss the Function in the next tutorial of PL/SQL.
Technically speaking, a procedure is a named block that stores as a schema object in the Oracle Database.
A Pl/SQL Procedure is consist of 4 anonymous blocks of PL/SQL.
Header: The header section contains the name of the procedure and also the parameters or variables which pass through the procedure.
Declaration Section: In the declaration section, we declare the variable which will use in the execution section.
Execution Section: It is a mandatory part of the block and contains statements that perform the designated action.
Exception handling section: It is an optional part and contains the code that handles run-time errors. But for the suggestion, it is good practice to use this section in your procedure.
How to create Procedures in PL/SQL
A PL/SQL procedure is created with the CREATE OR REPLACE PROCEDURE statement. The syntax for the CREATE OR REPLACE PROCEDURE statement is as follows −
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
EXCEPTION
WHEN OTHERS THEN
<exception part>
END procedure_name; Explanation of Syntax:
The procedure-name means here, the name of the procedure.
For creating a procedure or function, we have to define parameters. There are three ways to pass or initiate parameters in the procedure:
The IN parameter represents the value that will comes from outside in the procedure. And the OUT represents the parameter that uses to return the value of the procedure.
The INOUT parameters are both readable and writable. The procedure can read and modify both.
The procedure-body contains the executable part where all the action will be performed. And the exception part added for catching the run-time error in pl/SQL procedures.
Example of Procedure in PL/SQL
CREATE OR REPLACE PROCEDURE display_contact(
in_customer_id IN NUMBER
cust_contact
OUT NUMBER
)
IS
r_contact contacts NUMBER(10);
BEGIN
SELECT PERSONAL_CONTACT
INTO r_contact
FROM contacts
WHERE customer_id = in_customer_id;
UPDATE contacts
SET PERSONAL_CONTACT = r_contact
WHERE customer_id = in_customer_id;
cust_contact :=r_contact
;
EXCEPTION
WHEN OTHERS THEN
cust_contact
:=NULL;
END;When the above mention code executes using the SQL prompt/ PL/SQL developer, it will produce the result like −
The procedure was executed successfully.How to Execute a Standalone Procedure
A Standalone procedure can be call out in two ways.
- By using the EXECUTE keyword or,
- By calling the name of the procedure from a PL/SQL block
The above mention example the procedure named ‘display_contact’ which can be call out by the EXECUTE keyword as −
EXECUTE display_contact;
The PL/SQL procedure can also be call out from another PL/SQL block −
BEGIN
display_contact;
END;
/OR
BEGIN
vcontact:= display_contact( in_customer_id IN NUMBER
cust_contact
OUT NUMBER);
END;
/How to Delete a Procedure
If you need to delete or drop the procedure then you can use the DROP PROCEDURE statement. The syntax is:
DROP PROCEDURE procedure-name;
In this tutorial, you have discovered how to create a PL/SQL procedure, it’s execution, and how to delete or drop or remove a procedure from the database.
FAQs:
How can one search PL/SQL code for a string/ key value?
ANSWER: The following query is very handy if one wants to know where certain tables, columns, and expressions are referenced in your PL/SQL source code.
SELECT type, name, line FROM user_source WHERE UPPER(text) LIKE UPPER(‘%&KEYWORD%’);
How does one get the value of a sequence into a PL/SQL variable?
ANSWER: There is a tow way to do this:
i := sq_sequence.NEXTVAL;
On the previous versions, you have to use embedded SQL statements to obtain sequence values:
select sq_sequence.NEXTVAL into: I from dual;
Is there a limit on the size of a PL/SQL block?
ANSWER: Yes, the maximum size is not an over than byte limit, but related to the parse tree that creates when you compile the code. You can also run the following given select statement to query the size of an existing package or procedure:
SQL Code> select * from dba_object_size where name = ‘procedure_name’;
In the above article, we discussed the procedures in PL/SQL, how to create and delete. I hope this will help you a lot. For more, please stay with us.
Thank You 🙂