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!|
+--------------+