Procedures in PL/SQL

Procedures in PL/SQL (Procedural Language/Structured Query Language) are a way to encapsulate a set of SQL and PL/SQL statements that perform a specific task. They are stored in the database and can be executed as needed, providing a modular and reusable approach to database programming.

Here’s an overview of creating and using procedures in PL/SQL:

Creating a Procedure

To create a procedure, you use the CREATE PROCEDURE statement followed by the procedure's name, parameters (if any), and the body of the procedure, which contains the executable code. Here’s a basic syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name 
   [ (parameter_name [IN | OUT | IN OUT] datatype [, ...]) ]
IS
   -- Declaration of variables
BEGIN
   -- Executable statements
EXCEPTION
   -- Exception handling
END procedure_name;

Example

Here’s a simple example of a PL/SQL procedure that inserts a new employee record into an employees table:

CREATE OR REPLACE PROCEDURE add_employee (
    p_emp_id IN employees.emp_id%TYPE,
    p_first_name IN employees.first_name%TYPE,
    p_last_name IN employees.last_name%TYPE,
    p_email IN employees.email%TYPE
)
IS
BEGIN
    INSERT INTO employees (emp_id, first_name, last_name, email)
    VALUES (p_emp_id, p_first_name, p_last_name, p_email);
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END add_employee;


Executing a Procedure

To execute a procedure, you use the EXECUTE statement or call it from another PL/SQL block:

EXECUTE add_employee(101, 'John', 'Doe', 'john.doe@example.com');

Or within an anonymous PL/SQL block:

BEGIN
    add_employee(102, 'Jane', 'Smith', 'jane.smith@example.com');
END;

Parameters in Procedures
  • IN: The default parameter mode. The parameter is used to pass values into the procedure.
  • OUT: The parameter is used to return values from the procedure.
  • IN OUT: The parameter can pass initial values into the procedure and return updated values.
Exception Handling

Exception handling in procedures allows you to manage errors that occur during the execution of the procedure. You can define an EXCEPTION block to catch and handle exceptions:

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No data found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');


Benefits of Using Procedures
Modularity: Procedures allow you to break down complex operations into smaller, manageable units.
Reusability: Once a procedure is created, it can be reused in multiple applications or scripts.
Security: Procedures can help control access to data and operations, ensuring that only authorized users can perform specific tasks.
Performance: Stored procedures can improve performance by reducing the amount of data transferred between the database and the application.

By using procedures in PL/SQL, you can create robust, maintainable, and efficient database applications.

Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

KTU DBMS LAB CSL 333 Syllabus and Experiments

 Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11
Read more

Creating a Database and Table ,executing queries - MySQL Lab Exercise

Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&
Read more