An Introduction to Stored Procedures in MySQL 5-优快云博客

https://code.tutsplus.com/articles/an-introduction-to-stored-procedures-in-mysql-5--net-17843

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.

If you work with MySQL a lot, you may want to check out the range of MySQL code scripts and plugins on Envato Market.

Introduction

“ 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.

Pros

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.

Cons

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.

Step 1 - Picking a Delimiter

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 “//”

Step 2 - How to Work with a Stored Procedure

Creating a Stored Procedure

 
         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 is NOT 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 ""

Calling a Stored Procedure

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);

Modify a Stored Procedure

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.

Delete a Stored 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.

Step 3 - Parameters

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.

IN example

 
         DELIMITER // 
        
         CREATE 
         PROCEDURE 
         `proc_IN` ( 
         IN 
         var1  
         INT 
         ) 
        
         BEGIN 
        
         SELECT 
         var1 + 2  
         AS 
         result; 
        
         END 
         //

OUT example

 
         DELIMITER // 
        
         CREATE 
         PROCEDURE 
         `proc_OUT` ( 
         OUT 
         var1  
         VARCHAR 
         (100)) 
        
         BEGIN 
        
         SET 
         var1 =  
         'This is a test' 
         ; 
        
         END 
         //

INOUT example

 
         DELIMITER // 
        
         CREATE 
         PROCEDURE 
         `proc_INOUT` ( 
         OUT 
         var1  
         INT 
         ) 
        
         BEGIN 
        
         SET 
         var1 = var1 * 2; 
        
         END 
         //

Step 4 - Variables

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;

Working with variables

Once the variables have been declared, you can assign them values using the SET or 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 
         //

Step 5 - Flow Control Structures

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

`IF` statement

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 
         //

`CASE` statement

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 
         //

`WHILE` statement

There are technically three standard loops: WHILE loops, LOOP loops, and REPEAT loops. 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.

Conclusion

In this lesson, we covered the fundamentals of stored procedures and some specific properties pertaining to them. Of course, you should continue your studies in areas like security, SQL statements, and performance before you can master MySQL routines.

You have to evaluate the advantages that stored procedures can potentially bring to your applications, and then make a reasonable implementation that fits your requirements. I generally use procedures; their benefits in terms of security, code maintenance and software design make them worthy of use, in my opinion. Additionally, remember that procedures in MySQL are still a work in progress. You should fully expect improvements, in terms of functionality and performance in the future.

Please don't hesitate to comment and share your ideas and opinions. And have a look at the MySQL code scripts and plugins on Envato Market to see if you find anything to help you there.

转载于:https://www.cnblogs.com/kungfupanda/p/5891240.html