PLSQL Procedures

procedure is a subprogram that performs a specific task. It's a part of PL/SQL and can be stored in the database for reuse.

Features:

1. Modularity: Procedures allow you to break down complex tasks into smaller, manageable units of code, promoting better organization and readability.
2. Reusability: Can be reused across multiple applications.
3. Parameters: Accepts IN, OUT, and IN OUT parameters for dynamic input/output.
4. Encapsulation: Hides implementation details, allowing for easier maintenance.
5. Error Handling: Can include exception handling to manage errors gracefully.
6. Transaction Control: Supports COMMIT and ROLLBACK for database transactions.
7. Performance: Optimized execution can improve performance for complex operations.
8. Overloading: Multiple procedures can have the same name with different parameters.
9. Integration: Can be called from triggers and grouped in packages.

Types of parameters:

1. IN Parameter: Accepts a value when the procedure is called. It cannot be modified within the procedure.
2. OUT Parameter: Returns a value to the calling environment. It must be assigned a value within the procedure.
3. IN OUT Parameter: Accepts an initial value and can return a modified value.

Example for Simple procedure:

CREATE OR REPLACE PROCEDURE welcome_proc
AS
BEGIN
dbms_output.put_line('Welcome to Pearl Database Service!');
dbms_output.put_line('Please visite the website www.pearldbs.com');
END;
/

Run: EXECUTE welcome_proc;    or exec welcome_proc

IN Parameter Example:

Create or replace procedure update_employees ( emp_id in integer)
Is
Begin
Update employees set salary=’5000’ where employee_id=emp_id;
End update_employees;
/

Run: EXECUTE update_employees(1);

OUT Parameter Example:

Create or replace procedure select_employees (emp_id in employees.employee_id%type)(fname out employees.salary%type)
Is
Begin
Select first_name into fname from employees where employee_id=emp_id;
End update_employees;
/

Run:
Declare
fname varchar2(15);
Begin
Select_employees(&emp_id,fname)
Dbms_output.putline(fname);
End;
/

IN OUT Parameter Example:

Create or replace employees_inout (emp_id in out number)
Is
Begin
Select salary into emp_id from employees where employee_id=emp_id;
End employees_inout;
/

Run:

Declare
Id number := &emp_id;
Begin
Employee_inout(&emp_id)
Dbms_output.put_line(emp_id);
End;
/

List out the procedures:

Select object_name, object_type from user_objects where object_type=’PRPCEDURE’;
SELECT owner, object_name, procedure_name FROM all_procedures ORDER BY owner, object_name, procedure_name;
SELECT object_name, procedure_name FROM user_procedures ORDER BY object_name, procedure_name;
SELECT owner, object_name, procedure_name FROM dba_procedures ORDER BY owner, object_name, procedure_name;
SELECT text from user_source where name=’PROCEDURE_NAME’;

Drop the Procedures:

DROP PROCEDURE procedure_name;