PL/SQL and Stored Procedures

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is not used in MySQL; instead, MySQL uses its own procedural language called MySQL stored procedures.

PL/SQL (Oracle):

PL/SQL is a procedural language that has SQL statements with procedural constructs like loops, conditions, and exceptions. It allows you to write code to interact with Oracle databases more effectively.

Features of PL/SQL:
  • Tight Integration with SQL: PL/SQL supports SQL data types and allows SQL statements to be embedded within procedural code.
  • Error Handling: PL/SQL has a robust exception handling mechanism to catch and handle runtime errors.
  • Blocks Structure: PL/SQL code is divided into blocks (anonymous blocks, procedures, functions, packages, triggers).
  • Control Structures: Supports traditional programming constructs like loops (FOR, WHILE), conditionals (IF, CASE), and others.
  • Stored Procedures and Functions: You can write procedures and functions to encapsulate reusable code.
  • Packages: Group related procedures, functions, variables, and other PL/SQL constructs into a single unit.
  • Triggers: Special procedures that automatically execute in response to certain events on a particular table or view.
  • Cursors: Mechanisms to fetch and process database rows returned by SQL queries.
  • Native Compilation: PL/SQL can be natively compiled to improve performance.
MySQL Stored Procedures:

MySQL stored procedures are routines that can contain SQL statements and procedural code. While MySQL does not have PL/SQL, it offers a similar procedural capability with its own syntax. 
 
Features of MySQL Stored Procedures:
  • Modularity: Encapsulate complex SQL queries and logic within stored procedures to promote code reuse.
  • Parameters: Support for input, output, and input-output parameters for passing values to and from procedures.
  • Error Handling: MySQL stored procedures support error handling using the DECLARE ... HANDLER statement.
  • Control Structures: Include procedural constructs like loops (LOOP, REPEAT, WHILE), conditionals (IF, CASE), and others.
  • Cursors: Allow fetching and processing rows returned by SQL queries.
  • Triggers: Like PL/SQL, MySQL supports triggers that can automatically execute in response to specific events.
  • User-Defined Functions (UDFs): Custom functions that can be called in SQL statements.
  • Stored Functions: Similar to stored procedures but meant to return a single value.
Example:
 
DECLARE
  message varchar2(100):= 'Hello, World!';
BEGIN
  dbms_output.put_line(message);
END;

Output:

Hello, World!

DECLARE: Section where you declare variables.
BEGIN: Marks the beginning of the executable section of the block.
dbms_output.put_line(message);: Outputs the value of the variable message to the console.
END;: Marks the end of the PL/SQL block.

Here is how you can create a stored procedure in MySQL that does something similar to your PL/SQL block, i.e., prints "Hello, World!".

MySQL does not have a direct equivalent to Oracle's dbms_output.put_line. Instead, you can use the SELECT statement to display output or use stored procedure variables.

Here’s an example of how to create and call a stored procedure in MySQL:

DELIMITER //

CREATE PROCEDURE hello_world()
BEGIN
  DECLARE message VARCHAR(100) DEFAULT 'Hello, World!';
  SELECT message;
END //

DELIMITER ;

-- Call the procedure
CALL hello_world();
 

Explanation:
  • DELIMITER //: This changes the statement delimiter to // instead of ; to allow the use of ; within the procedure body.
  • CREATE PROCEDURE hello_world(): Defines a new stored procedure named hello_world.
  • BEGIN ... END: The block where the procedure’s executable code is written.
  • DECLARE message VARCHAR(100) DEFAULT 'Hello, World!';: Declares a variable message and initializes it with the value 'Hello, World!'.
  • SELECT message;: Selects the value of message which effectively prints it when the procedure is called.
  • DELIMITER ;: Resets the delimiter back to ;.
  • CALL hello_world();: Executes the hello_world stored procedure.

When you call the hello_world procedure, it will output:
 +--------------+
| message      |
+--------------+
| Hello, World!|
+--------------+

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

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
Read more

Cursors in PL/SQL

In PL/SQL, cursors are a mechanism for accessing and manipulating query result sets. A cursor allows you to fetch and process rows returned by a query one at a time. There are two main types of cursors in PL/SQL: implicit cursors and explicit cursors. Implicit Cursors PL/SQL automatically creates implicit cursors for SELECT INTO statements, and for INSERT, UPDATE, DELETE, and MERGE statements. Example of Implicit Cursor DECLARE     v_employee_name employees.first_name%TYPE; BEGIN     -- This SELECT INTO statement uses an implicit cursor     SELECT first_name     INTO v_employee_name     FROM employees     WHERE employee_id = 100;     DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); END; In this example, PL/SQL creates an implicit cursor for the SELECT INTO statement and automatically fetches the result into the v_employee_name variable. Explicit Cursors Explicit cursors give you more control over the context area. You can open a cursor, fetch rows from the result s
Read more