Mysql Stored Procedures

Stored Procedure Learning

Before the article starts, we first give the sample table Allintersection. The examples in this article are in navicate for MySQL.

I. Creation and invocation of stored procedures

1. Basic syntax and examples:

Basic syntax for creating stored procedures:

Java code

    CREATE PROCEDURE Sp_name ()      begin        ...      End  

Call this stored procedure basic syntax:

SQL code

Call Sp_name ();

Note: The stored procedure name must be appended with parentheses, even if the stored procedure has no parameters passed

Basic syntax for deleting stored procedures:

SQL code

Drop procedure Sp_name;  

Caveats: You cannot delete another stored procedure in one stored procedure, only another stored procedure.

Other common commands:
1. Show procedure Status
Displays basic information about all stored procedures in the database, including the owning database, stored procedure name, creation time, and so on.
2. Show CREATE PROCEDURE Sp_name
Displays detailed information about a stored procedure.

Example 1:

Create a stored procedure:

SQL code

    Create proceduredayrepeat ()begin        SelectIntersectionunit as 'intersection units', Intersectionname as 'Intersection name', drivedirection as 'Driving Direction', SCar+MCar+Bcar as 'Total Flow'         fromallintersection; End;

Call stored Procedure dayrepeat ():

SQL code

Call Dayrepeat ();  

The returned results are as follows:

2. Stored procedures with parameter passing
Example 2:

SQL code

　　

    Create proceduretest1 (out LSdecimal(8,2), out HSdecimal(8,2), out AVSdecimal(8,2)      )      begin        Select min(SCar) as 'minimum vehicle flow in small vehicles'         intols fromallintersection; Select Max(SCar) as 'maximum flow of small car'         intoHS fromallintersection; Select avg(SCar) as 'vehicle traffic average of small cars'         intoAVS fromallintersection; End;

This stored procedure accepts 3 parameters: LS, HS, AVS. Each parameter must have a type, where the decimal value is used. The keyword out indicates that the corresponding parameter is used to send a value (returned to the caller) from the stored procedure. MySQL supports in (passed to stored procedure), out (outgoing from stored procedure), and inout (incoming and outgoing to stored procedures)
Note: Recordsets are not allowed types, so multiple rows and columns cannot be returned through one parameter.

Since this stored procedure requires 3 parameters, it must pass exactly 3 parameters, not many and many, so this call statement gives 3 parameters, which are the names of the 3 variables that stored the result of the procedure.

SQL code

1. Call Test1 (@ Small car traffic minimum, @ Small car flow maximum, @ Small car vehicle average value);

When called, this statement does not display any data. It returns variables that can be displayed later (or used in other processes).

In order to display the retrieved results, you can do the following:

SQL code

1. SELECT @ Small vehicle traffic average, @ Small car vehicle flow maximum, @ Small car vehicle Volume average;

The results of the search are as follows:

Example 3:
Here is another example, this time using the in and out parameters. Istotal accept the intersection name parameter and return the total flow of traffic at that junction.

SQL code

    Create procedureIstotal (inchIsidint, out Itotaldecimal(8,2)      )      begin        SelectSCar+MCar+Bcar as 'total traffic flow at intersection'         fromallintersectionwhereId=Isid intoItotal; End;

ISID is defined as in because the ID is passed into the stored procedure. Itotal is defined as out because the total is to be returned from the stored procedure.

To call this stored procedure and display the aggregate results, use the following statement:

SQL code

    Call Istotal (1,@total);       Select @total;  

The results are as follows:

Two. Using Cursors

1. Creating Cursors
Cursors are created with the Declare statement. As shown in the following example:

SQL code

    Create procedure test2 ()       begin          Declare cursor           for      Select *       from allintersection;       End;  

2. Opening and closing cursors
The cursor is opened with the open cursor statement and closed with the close cursor.

SQL code

    Open cursortest;       Close cursortest;  

Close releases all internal memory and resources used by the cursor, so it should be closed when each cursor is no longer needed. After a cursor has been closed, it cannot be used if it is not re-opened. However, the declared cursor does not need to be declared again, and opening it is possible with the open statement.
3. Using cursor data
After a cursor is opened, you can use the FETCH statement to access each of its rows separately. The FETCH statement specifies what data is retrieved (the required columns) and where the retrieved data is stored. It also moves forward the inner row pointer in the cursor so that the next FETCH statement retrieves the next row (the same row is not repeated).

SQL code

    Create proceduretest3 ()begin          DeclareOint;--declaring a local variable      DeclareCursorTest3cursor        --declaring a cursor         for            SelectID fromallintersection; OpenCursorTest3;--Open Cursor        FetchCursorTest3 intoO--Get Intersectionname        CloseCursorTest3;--Close Cursors    End;

Where fetch is used to retrieve the Intersectionname column of the current row (which will automatically start with the first row) into a locally declared variable named O. Do any processing on the retrieved data section.

Example 4:

This example loops through the data, from the first line to the last row.

SQL code

    Create proceduretest4 ()begin          DeclareDone Booleandefault 0; DeclareOint;--declaring a local variable      DeclareCursorTest4cursor        --declaring a cursor         for            SelectID fromallintersection; Declare ContinueHandler forSQLState'02000' SetDone=1; OpenCursorTest4;--Open Cursor        --Traverse all the rowsRepeatFetchCursorTest4 intoO--Get IntersectionnameUntil doneEndRepeat--End Loop        CloseCursorTest4;--Close Cursors    End;

Unlike the previous example, the fetch in this example is within repeat, so it is executed repeatedly to true (until done end repeat; provisions). To make it work, define the variable done with a default of 0 (false, not end). So how does it get set to true at the end? The answer is with the following statement:

SQL code

1. Declare continue handler for sqlstate ' 02000 ' set done=1;

This statement defines a continue HANDLER, which is the code that is executed when the condition occurs. Here, it indicates that SQLState ' 02000 ' appears when set done=1. SQLSTATE ' 02000 ' is a condition not found, which occurs when repeat cannot continue because there are no more rows for the loop.

Example 5:

This example is more complex than the example above, and the actual processing of the extracted data.

SQL code

    Create proceduretest5 ()begin          --declaring local variables        DeclareDone Booleandefault 0; DeclareOint; --declare t DECIMAL (8,2);             DeclareCursorTest5cursor        --declaring a cursor               for            SelectId fromallintersection; --define continue handler      Declare ContinueHandler forSQLState'02000' SetDone=1; --Create a daysrepeat table to store the results        Create Table if  not existsdaysrepeat (IDint, totaldecimal(8,2)); OpenCURSORTEST5;--Open Cursor               --Traverse all the rows    FetchCursorTest5 intoo; Repeat call Istotal (o,@t); Insert  intodaysrepeat (ID, total)Values(O,@t); FetchCursorTest5 intoO--Get IntersectionnameUntil doneEndRepeat--End Loop        CloseCURSORTEST5;--Close Cursors    End;

Then execute the stored procedure:

SQL code

1. Call Test5 ();

The result is to generate a new table daysrepeat, and populate the table with the data, as shown in the populated tables:

Mysql Stored Procedures