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:
- 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.
- IS: Marks the beginning of the
procedure's declaration and executable section.
- BEGIN: Marks the beginning of the
procedure's implementation or body.
- 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.
- 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!
Comments
Post a Comment