PL/SQL Procedure

PL/SQL procedures are named blocks of code within the PL/SQL language that can perform specific tasks or a series of tasks.

They are similar to functions but with a key difference: while functions must return a value, procedures may or may not return a value.

Inside the procedure, you can write SQL queries, control structures (like loops and conditionals), and other PL/SQL statements to accomplish your desired tasks.

Once you've defined a procedure, you can call it from other PL/SQL code or SQL statements, making your code modular and reusable.

Here's the basic syntax for creating a PL/SQL procedure:

CREATE [OR REPLACE] PROCEDURE procedure_name

   (parameter1 datatype, parameter2 datatype, ...)

IS

   -- Declarations (optional)

BEGIN

   -- Procedure body (implementation)

END;

This structure outlines how to create a PL/SQL procedure in Oracle:

1.     CREATE [OR REPLACE] PROCEDURE procedure_name: This keyword starts the creation of a new procedure or replaces an existing one if OR REPLACE is used. procedure_name is the name you give to the procedure.

2.     (parameter1 datatype, parameter2 datatype, ...): Inside the parentheses, you define parameters that the procedure can accept. Each parameter has a name (parameter1, parameter2, etc.) and a data type (datatype).

3.     IS: Marks the beginning of the procedure's declaration and executable section.

4.     -- Declarations (optional): Optional section where you can declare local variables and constants that are used within the procedure.

5.     BEGIN: Marks the beginning of the procedure's implementation or body.

6.     -- Procedure body (implementation): Here, you write the actual logic and statements that define what the procedure does when it is executed. This can include SQL queries, conditional statements (IF-THEN-ELSE), loops, and any other PL/SQL constructs.

7.     END;: Marks the end of the procedure.

Here's a simple example of a PL/SQL procedure that prints a message:

CREATE OR REPLACE PROCEDURE print_message

   (message VARCHAR2)

IS

BEGIN

   DBMS_OUTPUT.PUT_LINE(message);

END;

This PL/SQL code snippet demonstrates the creation of a procedure named print_message that prints a message to the console:

1. CREATE OR REPLACE PROCEDURE print_message (message VARCHAR2):
• CREATE OR REPLACE PROCEDURE: This syntax creates a new procedure named print_message or replaces it if it already exists.
• print_message: This is the name of the procedure.
• (message VARCHAR2): This specifies a single input parameter named message of type VARCHAR2, which is a data type that holds variable-length character strings.
2. IS: Marks the beginning of the procedure's declaration and executable section.
3. BEGIN: Marks the beginning of the procedure's implementation or body.
4. DBMS_OUTPUT.PUT_LINE(message);: Inside the procedure body, DBMS_OUTPUT.PUT_LINE is a built-in procedure in PL/SQL that outputs a line of text to the console. In this case, it outputs the value of the message parameter passed to the procedure.
5. END;: Marks the end of the procedure.

 

You can then call this procedure like this

BEGIN

   print_message('Hello, world!');

END;

This will output:

Hello, world!