Create an Oracle Procedure | SQL PLSQL

Published: 22 February 2024
on channel: Cloud Research & Software Engineering

In Oracle PL/SQL, a procedure is a named PL/SQL block that can perform one or more specific tasks. Unlike functions, procedures do not return values directly; they are designed to perform actions or operations and may have input and output parameters. Here's an overview of creating procedures in PL/SQL:

```sql
CREATE [OR REPLACE] PROCEDURE procedure_name
(parameter1 IN datatype1,
parameter2 OUT datatype2,
parameter3 IN OUT datatype3)
IS
-- Declarations (optional)
variable1 datatype;
variable2 datatype;

BEGIN
-- Statements to perform some operations
-- You can use SQL and procedural statements here
-- Assign values to variables, perform calculations, etc.

-- Set value for OUT and IN OUT parameters
parameter2 := value_to_be_set;

-- Additional logic

EXCEPTION
-- Exception handling (optional)
WHEN exception1 THEN
-- Handle exception1
WHEN exception2 THEN
-- Handle exception2;

END procedure_name;
/
```

Explanation:

*CREATE [OR REPLACE] PROCEDURE:*
`CREATE PROCEDURE` is the SQL statement used to create a new procedure.
`OR REPLACE` is optional and allows modifying an existing procedure if it already exists.

*procedure_name:*
Specifies the name of the procedure.

*Parameters:*
Input parameters are declared using `IN` keyword.
Output parameters are declared using `OUT` keyword.
Input/Output parameters are declared using `IN OUT` keywords.
Parameters are used to pass values into the procedure and receive values from the procedure.
Data types must be specified for each parameter.

*IS:*
Begins the declarative section where you can declare variables and constants.

*BEGIN:*
Begins the executable section where you write the main logic of your procedure.

*OUT and IN OUT parameters:*
Output parameters are used to return values from the procedure.
`OUT` and `IN OUT` parameters must be explicitly set within the procedure.

*EXCEPTION:*
Optional section for handling exceptions.
You can specify different exception conditions and handle them accordingly.

*/ (slash):*
Executes the SQL statement and terminates the PL/SQL block.

Here's a simple example of a PL/SQL procedure that inserts a record into a table:

```sql
CREATE OR REPLACE PROCEDURE insert_employee
(emp_id IN NUMBER,
emp_name IN VARCHAR2,
emp_salary IN NUMBER)
IS
BEGIN
INSERT INTO employees (employee_id, employee_name, salary)
VALUES (emp_id, emp_name, emp_salary);
END insert_employee;
/
```

You can then call this procedure in a PL/SQL block or from another program to insert a new employee record:

```sql
BEGIN
insert_employee(101, 'John Doe', 50000);
END;
/
```

This would insert a new record into the `employees` table with the specified values.

On this page of the site you can watch the video online Create an Oracle Procedure | SQL PLSQL with a duration of hours minute second in good quality, which was uploaded by the user Cloud Research & Software Engineering 22 February 2024, share the link with friends and acquaintances, this video has already been watched 32 times on youtube and it was liked by 2 viewers. Enjoy your viewing!

13,912

323