Hot File

How to Stored Procedures in MySQL 5

View: 247    Dowload: 0   Comment: 0   Post by: hanhga  
Author: none   Category: MySQL   Fields: Other

9 point/2 review File has been tested

MySQL 5 introduced a plethora of new features - stored procedures being one of the most significant. In this tutorial, we will focus on what they are, and how they can make your life easier.

Introduction

MySQL 5 introduced a plethora of new features - stored procedures being one of the most significant. In this tutorial, we will focus on what they are, and how they can make your life easier.

“ A stored routine is a set of SQL statements that can be stored in the server.”

A stored procedure is a method to encapsulate repetitive tasks. They allow for variable declarations, flow control and other useful programming techniques.

The “academic” position on this is quite clear and supports the extensive use of stored procedures. On the other hand, when you consider the opinions of those who work with them day in, day out, you'll notice that reactions vary from complete, unwavering support to utter hate. Keep these in mind.

  • Share logic with other applications. Stored procedures encapsulate functionality; this ensures that data access and manipulation are coherent between different applications.
  • Isolate users from data tables. This gives you the ability to grant access to the stored procedures that manipulate the data but not directly to the tables.
  • Provide a security mechanism. Considering the prior item, if you can only access the data using the stored procedures defined, no one else can execute a DELETE SQL statement and erase your data.
  • To improve performance because it reduces network traffic. With a stored procedure, multiple calls can be melded into one.
  • Increased load on the database server -- most of the work is done on the server side, and less on the client side.
  • There's a decent learning curve. You'll need to learn the syntax of MySQL statements in order to write stored procedures.
  • You are repeating the logic of your application in two different places: your server code and the stored procedures code, making things a bit more difficult to maintain.
  • Migrating to a different database management system (DB2, SQL Server, etc) may potentially be more difficult.

The tool that I am working with in this tutorial, MySQL Query Browser, is pretty standard for database interactions. The MySQL command line tool is another excellent choice. I make note of this because the popular phpMyAdmin doesn't support stored procedure execution.

Additionally, I'll be using very rudimentary table structures, strictly to ease the explanation. I'm showing off stored procedures, and they're complex enough without worrying about big tables.

The delimiter is the character or string of characters that you'll use to tell the mySQL client that you've finished typing in an SQL statement. For ages, the delimiter has always been a semicolon. That, however, causes problems, because, in a stored procedure, one can have many statements, and each must end with a semicolon. In this tutorial I will use “//”

DELIMITER //
 
CREATE PROCEDURE `p2` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A procedure'
BEGIN
    SELECT 'Hello World !';
END//

The first part of the statement creates the procedure. The next clauses defines the optional characteristics of the procedure. Then you have the name and finally the body or routine code.

Stored procedure names are case insensitive, and you cannot create procedures with the same name. Inside a procedure body, you can't put database-manipulation statements.

The four characteristics of a procedure are:

  • Language : For portability purposes; the default value is SQL.
  • Deterministic : If the procedure always returns the same results, given the same input. This is for replication and logging purposes. The default value isNOT DETERMINISTIC.
  • SQL Security : At call time, check privileges of the user. INVOKER is the user who calls the procedure. DEFINER is the creator of the procedure. The default value is DEFINER.
  • Comment : For documentation purposes; the default value is ""

To call a procedure, you only need to enter the word CALL, followed by the name of the procedure, and then the parentheses, including all the parameters between them (variables or values). Parentheses are compulsory.

CALL stored_procedure_name (param1, param2, ....)
 
CALL procedure1(10 , 'string parameter' , @parameter_var);

MySQL provides an ALTER PROCEDURE statement to modify a routine, but only allows for the ability to change certain characteristics. If you need to alter the body or the parameters, you must drop and recreate the procedure.

DROP PROCEDURE IF EXISTS p2;

This is a simple command. The IF EXISTS clause prevents an error in case the procedure does not exist.

Let's examine how you can define parameters within a stored procedure.

  • CREATE PROCEDURE proc1 () : Parameter list is empty
  • CREATE PROCEDURE proc1 (IN varname DATA-TYPE) : One input parameter. The word IN is optional because parameters are IN (input) by default.
  • CREATE PROCEDURE proc1 (OUT varname DATA-TYPE) : One output parameter.
  • CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE) : One parameter which is both input and output.

Of course, you can define multiple parameters defined with different types.

   DELIMITER //
 
CREATE PROCEDURE `proc_IN` (IN var1 INT)
BEGIN
    SELECT var1 + 2 AS result;
END//
DELIMITER //
 
CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))
BEGIN
    SET var1 = 'This is a test';
END //
DELIMITER //
 
CREATE PROCEDURE `proc_INOUT` (OUT var1 INT)
BEGIN
    SET var1 = var1 * 2;
END //

The following step will teach you how to define variables, and store values inside a procedure. You must declare them explicitly at the start of the BEGIN/END block, along with their data types. Once you've declared a variable, you can use it anywhere that you could use a session variable, or literal, or column name.

Declare a variable using the following syntax:

DECLARE varname DATA-TYPE DEFAULT defaultvalue;

Let's declare a few variables:

DECLARE a, b INT DEFAULT 5;
 
DECLARE str VARCHAR(50);
 
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
 
DECLARE v1, v2, v3 TINYINT;

Once the variables have been declared, you can assign them values using the SETor SELECT command:

DELIMITER //
 
CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20))
BEGIN
    DECLARE a, b INT DEFAULT 5;
    DECLARE str VARCHAR(50);
    DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
    DECLARE v1, v2, v3 TINYINT;    
 
    INSERT INTO table1 VALUES (a);
    SET str = 'I am a string';
    SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5; 
END //

MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE and REPEATconstructs for flow control within stored programs. We're going to review how to useIFCASE and WHILE specifically, since they happen to be the most commonly used statements in routines.

With the IF statement, we can handle tasks which involves conditions:

DELIMITER //
 
CREATE PROCEDURE `proc_IF` (IN param1 INT)
BEGIN
    DECLARE variable1 INT;
    SET variable1 = param1 + 1;
     
    IF variable1 = 0 THEN
        SELECT variable1;
    END IF;
 
    IF param1 = 0 THEN
        SELECT 'Parameter value = 0';
    ELSE
        SELECT 'Parameter value <> 0';
    END IF;
END //

The CASE statement is another way to check conditions and take the appropriate path. It's an excellent way to replace multiple IF statements. The statement can be written in two different ways, providing great flexibility to handle multiple conditions.

DELIMITER //
 
CREATE PROCEDURE `proc_CASE` (IN param1 INT)
BEGIN
    DECLARE variable1 INT;
    SET variable1 = param1 + 1;
     
    CASE variable1
        WHEN 0 THEN
            INSERT INTO table1 VALUES (param1);
        WHEN 1 THEN
            INSERT INTO table1 VALUES (variable1); 
        ELSE
            INSERT INTO table1 VALUES (99);
    END CASE;
 
END //

or:

DELIMITER //
 
CREATE PROCEDURE `proc_CASE` (IN param1 INT)
BEGIN
    DECLARE variable1 INT;
    SET variable1 = param1 + 1;
     
    CASE
        WHEN variable1 = 0 THEN
            INSERT INTO table1 VALUES (param1);
        WHEN variable1 = 1 THEN
            INSERT INTO table1 VALUES (variable1); 
        ELSE
            INSERT INTO table1 VALUES (99);
    END CASE;
 
END //

There are technically three standard loops: WHILE loops, LOOP loops, and REPEATloops. You also have the option of creating a loop using the “Darth Vader” of programming techniques: the GOTO statement. Check out this example of a loop in action:

DELIMITER //
 
CREATE PROCEDURE `proc_WHILE` (IN param1 INT)
BEGIN
    DECLARE variable1, variable2 INT;
    SET variable1 = 0;
     
    WHILE variable1 < param1 DO
        INSERT INTO table1 VALUES (param1);
        SELECT COUNT(*) INTO variable2 FROM table1;
        SET variable1 = variable1 + 1;
    END WHILE;
END //
Step 6 - Cursors

Cursor is used to iterate through a set of rows returned by a query and process each row.

MySQL supports cursor in stored procedures. Here's a summary of the essential syntax to create and use a cursor.
DECLARE cursor-name CURSOR FOR SELECT ...;       /*Declare and populate the cursor with a SELECT statement */
DECLARE  CONTINUE HANDLER FOR NOT FOUND          /*Specify what to do when no more records found*/
OPEN cursor-name;                                /*Open cursor for use*/
FETCH cursor-name INTO variable [, variable];    /*Assign variables with the current column values*/
CLOSE cursor-name;                               /*Close cursor after use*/

In this example, we'll perform some simple operations using a cursor:

DELIMITER //
 
CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT)
BEGIN
    DECLARE a, b, c INT;
    DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
    OPEN cur1;
 
    SET b = 0;
    SET c = 0;
    
    WHILE b = 0 DO
        FETCH cur1 INTO a;
        IF b = 0 THEN
            SET c = c + a;
    END IF;  
    END WHILE;
 
    CLOSE cur1;
    SET param1 = c;
 
END //

Cursor has three important properties that you need to be familiar with in order to avoid unexpected results:

  • Asensitive : Once open, the cursor will not reflect changes in its source tables. In fact, MySQL does not guarantee the cursor will be updated, so you can't rely on it.
  • Read Only : Cursors are not updatable.
  • Not Scrollable : Cursors can be traversed only in one direction, forward, and you can't skip records from fetching.

How to Stored Procedures in MySQL 5

How to Stored Procedures in MySQL 5 Posted on 12-01-2016  MySQL 5 introduced a plethora of new features - stored procedures being one of the most significant. In this tutorial, we will focus on what they are, and how they can make your life easier. 4.5/10 247

Comment:

To comment you must be logged in members.

Files with category

  • SQL Query to Find Nth Highest Salary of Employee with java

    View: 196    Download: 0   Comment: 0   Author: none  

    SQL Query to Find Nth Highest Salary of Employee with java

    Category: MySQL
    Fields: Other

    0/0 review
    This is a very common interview question if you are going for the role at junior level. Most of the people learn it character by character, but few understand how this query works.

  • World OOP With PHP and MySQL

    View: 956    Download: 0   Comment: 0   Author: none  

    World OOP With PHP and MySQL

    Category: MySQL
    Fields: Other

    0.32142857142857/14 review
    Setting up the skeleton of our class is fairly simple once we figure out exactly what we need.

  • How to Build a Newsletter System With PHP and MySQL

    View: 1183    Download: 0   Comment: 0   Author: none  

    How to Build a Newsletter System With PHP and MySQL

    Category: MySQL
    Fields: Other

    1.125/4 review
    When starting any project, I like to layout the folders before I start coding, so lets do that now.

  • Top 20+ MySQL Best Practices that can not be ignored

    View: 893    Download: 0   Comment: 0   Author: none  

    Top 20+ MySQL Best Practices that can not be ignored

    Category: MySQL
    Fields: Other

    1.6666666666667/3 review
    Database operations often tend to be the main bottleneck for most web applications today.

  • Create a PHP/MySQL Powered Forum from Scratch

    View: 1040    Download: 0   Comment: 0   Author: none  

    Create a PHP/MySQL Powered Forum from Scratch

    Category: MySQL
    Fields: Other

    1.5/3 review
    In this tutorial, we're going to build a PHP/MySQL powered forum from scratch.

  • Triggers Introduction to MySQL

    View: 256    Download: 0   Comment: 0   Author: none  

    Triggers Introduction to MySQL

    Category: MySQL
    Fields: Other

    1.6666666666667/3 review
    I would guess, even armed with this knowledge, that a good many of you are not taking advantage of triggers with MySQL.

  • How to Stored Procedures in MySQL 5

    View: 247    Download: 0   Comment: 0   Author: none  

    How to Stored Procedures in MySQL 5

    Category: MySQL
    Fields: Other

    2.25/2 review
    MySQL 5 introduced a plethora of new features - stored procedures being one of the most significant. In this tutorial, we will focus on what they are, and how they can make your life easier.

  • The strength of the PDO and. MySQLi: Which Should You Use?

    View: 267    Download: 0   Comment: 0   Author: none  

    The strength of the PDO and. MySQLi: Which Should You Use?

    Category: MySQL
    Fields: Other

    1.5/3 review
    When accessing a database in PHP, we have two choices: MySQLi and PDO. So what should you know before choosing one?

 
Newsletter Email

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd