Creating and Executing a Procedure in PL/SQL
Introduction
In this article, we will explore a simple PL/SQL code snippet that demonstrates the creation and execution of a procedure. We will break down the code and explain its key concepts, and structure, and provide code examples for better understanding.
Key Concepts
Before diving into the code, let's briefly discuss some key concepts related to PL/SQL and procedures:
- PL/SQL: PL/SQL (Procedural Language/Structured Query Language) is Oracle's procedural extension to SQL. It allows developers to write procedural code blocks that can be executed on the Oracle database server.
- Procedure: A procedure is a named PL/SQL block that performs a specific task or set of tasks. It can be invoked by other PL/SQL blocks or called directly from an application.
Code Structure
Let's analyze the structure of the provided PL/SQL code:
Procedure
SET SERVEROUTPUT ON;
-- CREATE PROCEDURE
CREATE OR REPLACE PROCEDURE PRO_01
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLO');
END;
-- EXECUTE PROCEDURE
EXEC PRO_01;The code consists of three sections: setting the server output, creating a procedure, and executing the procedure.
Code Examples
Now, let's break down each section of the code and explain its purpose:
Setting the Server Output
Set Serveroutput
SET SERVEROUTPUT ON;
This line of code enables the output display from the server. It allows us to see the output generated by the DBMS_OUTPUT.PUT_LINE statement within the procedure.
Creating a Procedure
Procedure
CREATE OR REPLACE PROCEDURE PRO_01
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLO');
END;
This code block creates a procedure named "PRO_01". The procedure does not take any parameters and has a single statement inside the BEGIN and END keywords.
The statement DBMS_OUTPUT.PUT_LINE('HELLO'); prints the string "HELLO" to the server output.
Executing the Procedure
Executing
EXEC PRO_01;
This line of code executes the procedure "PRO_01". The EXEC keyword is used to invoke the procedure by its name.
Conclusion
In this article, we have explored a simple PL/SQL code snippet that demonstrates the creation and execution of a procedure. We have discussed the key concepts of PL/SQL and procedures, analyzed the code structure, and provided detailed explanations for each section of the code. By understanding this example, you can start building more complex procedures to perform specific tasks in your Oracle database applications.
