Learning notes for Mysql stored procedures

Technical points

A stored procedure includes a name, a list of arguments, and a set of SQL statements that can include many SQL statements. The following is the process of defining a stored procedure:
CREATE PROCEDURE Proc_name (in parameter integer) begindeclare variable varchar (a); if Parameter=1 Thenset ' MySQL '; elseset variable= ' PHP '; end If;insert to TB (name) values (variable);

The establishment of a stored procedure in MySQL starts with the keyword CREATE PROCEDURE, followed by the name and parameters of the stored procedure. MySQL stored procedure names are case-insensitive, for example, PROCE1 () and Proce1 () represent the same stored procedure name. The stored procedure name cannot be the same as the built-in function in the MySQL database.
The parameters of a stored procedure are generally composed of 3 parts. The first part can be in, out, or inout. In indicates that parameters are passed in to a stored procedure, out represents an outgoing parameter, inout represents a defined parameter that can be passed in to a stored procedure, and can be modified by a stored procedure, and the stored procedure defaults to an incoming parameter, so parameter in can be omitted. The second part is the name of the parameter. The third part is the type of the parameter, which is the type of all the fields available in the MySQL database, and can be separated by commas if there are multiple parameters.
The statement block of the MySQL stored procedure begins with the beginning and ends with end. The statement body can contain declarations of variables, control statements, SQL query statements, and so on. Because the internal statement of the stored procedure ends with a semicolon, the statement end flag ";" should be used before the stored procedure is defined. Change to a different character, and the chance that the character will appear in the stored procedure should be lower and can be changed with the keyword delimiter. For example:
Mysql>delimiter//

After the stored procedure is created, it can be deleted with the following statement, which refers to the stored procedure name (proc_name).
drop procedure Proc_name

implementation Process

(1) The MySQL stored procedure is created under the command prompt, so you should first open the Command Prompt window.
(2) After entering the command prompt window, you should first log on to the MySQL database server, and at the command prompt, enter the following command:
Mysql–u User name –p user password

(3) Change the statement closing symbol, this instance changes the statement terminator to "//." The code is as follows:
Delimiter//

(4) A database should be selected before the stored procedure is created. The code is as follows:
Use database name

(5) Create a stored procedure.
(6) Invoking the stored procedure through the call statement.

Extrapolate

--Build the table

Use test;
CREATE TABLE User (
ID Mediumint (8) unsigned NOT NULL auto_increment,
Name char NOT null default ',
Pass char (+) NOT null default ',
Note text is not NULL,
Primary KEY (ID)
) Engine=innodb Charset=utf8;

--Example One

Delimiter//
CREATE PROCEDURE Proc_name (in Parameter integer)
Begin
If Parameter=0 Then
SELECT * from user order by ID ASC;
Else
SELECT * from user order by id desc;
End If;
End
//
delimiter;
Show warnings;
Call Proc_name (1);
Call Proc_name (0);

--Example Two

drop procedure Proc_name;
Delimiter//
CREATE PROCEDURE Proc_name (in Parameter integer)
Begin
DECLARE variable varchar (20);
If Parameter=1 Then
Set variable= ' Windows ';
Else
Set variable= ' Linux ';
End If;
Select parameter;
End
//
delimiter;
Show warnings;
Call Proc_name (1);
Call Proc_name (0);

Using transactions in Stored procedures transaction

A. When dealing with transactions, use SqlException to catch SQL errors, and then handle them; According to this inference, we must capture the SQL error in the MySQL stored procedure, and the Final Judgment is rollback (ROLLBACK) or commit (commit). So the stored procedure is:

DELIMITER $$
DROP PROCEDURE IF EXISTS test_sp1 $$
CREATE PROCEDURE Test_sp1 ()
BEGIN
DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER for SQLEXCEPTION SET t_error=1;

START TRANSACTION;
INSERT into Test VALUES (NULL, ' Test SQL 001 ');
INSERT into Test VALUES (' 1 ', ' Test SQL 002 ');

IF t_error = 1 THEN
ROLLBACK;
ELSE
COMMIT;
End IF;

end$$
DELIMITER;

In this example, we defined a SQLEXCEPTION parameter t_error for TEST_SP1 (), and continued execution (CONTINUE) when a SQL error was encountered; If there is no error in the execution status, commit instead of rolling back!

Two. When the transaction is invoked, the execution state of the transaction (that is, whether the transaction is committed or rolled back) is returned to the person being transferred.

A second example is given below:

CREATE definer= ' 3dmodelbaseadmin ' @ '% ' PROCEDURE ' P_userconfirmpay ' (
in P_lid int,
In P_endtime DATETIME,
In P_moneyaftertax decimal (10,2),
In P_integralaftertax decimal (10,0),
in P_sellerid int unsigned,
in P_cashorpoints int,
In P_loginname_site varchar (50),
In P_transactionid_site char (100),
In P_orderid char (100),
In P_remarks_site char (100),
In P_transactionid char (100),
In P_cmemid INT UNSIGNED,
In P_curtotal DECIMAL (10,2),
In P_curtotalcintegral decimal (10,0),
In P_remarks char (100))
BEGIN

DECLARE P_cmemid_site INT;
DECLARE p_balance_site DECIMAL (10,2);
DECLARE p_balance DECIMAL (10,2);
DECLARE p_intbalance_site DECIMAL (10,0);
DECLARE p_intbalance DECIMAL (10,0);

DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER for SQLEXCEPTION SET t_error=1;

START TRANSACTION;

Set p_cmemid_site= (select Cmemid from M_member where cloginname=p_loginname_site);

Update L_memberdownlog set lstate=1,endtime=p_endtime where Lid=p_lid;

Update M_membermoney set Cmoney=cmoney-p_moneyaftertax,
Cintegral = Cintegral-p_integralaftertax where cmemid=p_cmemid_site;

Update M_membermoney set Cmoney=cmoney+p_moneyaftertax,
Cintegral = Cintegral+p_integralaftertax where Cmemid=p_sellerid;

If P_cashorpoints=0 Then
Set p_balance_site = (select Cmoney from M_membermoney where cmemid=p_cmemid_site);
INSERT into Cashdetail ... (omitted here);
Else
Set p_intbalance_site = (select Cintegral from M_membermoney where cmemid=p_cmemid_site);
INSERT into Integraldetail ... (omitted here);
End If;

Update M_membermoney set Totalconsmoney=totalconsmoney+p_curtotal,
Totalconsintegral=totalconsintegral+p_curtotalcintegral where Cmemid=p_cmemid;

IF t_error = 1 THEN
ROLLBACK;
ELSE
COMMIT;
End IF;

Select T_error; Returns the execution state of a transaction to the person being called

MySQL stored procedure cursor (Cursor)

1, the role of the cursor and properties

The role of a cursor is to traverse the records returned by the query database for appropriate action; The cursor has these properties:

A, the cursor is read-only, that is, it cannot be updated;

b, the cursor is not able to scroll, that is, only in one direction to traverse, not in the record between the random retreat, can not skip some records;

c, avoid updating the data on the table where the cursor has been opened.

2. How to use Cursors

The following steps are required to use cursors:

A, first declare a cursor with the DECLARE statement

DECLARE cursor_name cursor for select_statement;
The above statement specifies a cursor for the record returned by the SELECT statement that we execute
b, second, you need to use the Open statement to open the cursor you defined above

OPEN cursor_name;
C, you can then use the FETCH statement to get the next line of data, and the cursor will also move to the corresponding record (this is similar to the iterator in Java).

FETCH cursor_name into variable list;
D, and then finally we're going to release the cursor when all we need to do is finish.

Close cursor_name;
When using cursors, it is necessary to avoid errors such as "No data to fetch" by defining a conditional handler function (condition handler) for the not found, which is the code that executes when a condition is generated, Here, however, when we point to the end of the record, we reach the not found condition, at which point we want to continue the subsequent operation, so we'll see a continue in the following code. Let's take a look at our table contents:

The following cursor uses code codes that demonstrate obtaining a product with an inventory of less than 100, and this code purely demonstrates how to use it, and there is no other meaning here:

DELIMITER $$

DROP PROCEDURE IF EXISTS ' test '. ' Cursorproc ' $$
CREATE PROCEDURE ' test '. ' Cursorproc ' ()
BEGIN
DECLARE no_more_products, Quantity_in_stock INT DEFAULT 0;
DECLARE Prd_code VARCHAR (255);
DECLARE cur_product CURSOR for the SELECT code from the products; /*first:delcare a cursor, first the cursor is defined here.
DECLARE CONTINUE HANDLER for not FOUND SET no_more_products = 1; /*when "not found" occur,just continue, this is a conditional processing, against the condition of not found * *

/* For loggging information Create a temporary form to keep * *
CREATE Temporary TABLE infologs (
Id Int (one) not NULL auto_increment,
MSG varchar (255) Not NULL,
PRIMARY KEY (Id)
);

OPEN cur_product; /*second:open the cursor then open the cursor using the Open/
FETCH cur_product into Prd_code; /*third:now you can Fetch the row writes the first row of data to the variable, and the cursor then points to the first line of the record.

REPEAT

SELECT quantity into Quantity_in_stock
From Products
WHERE code = Prd_code;

IF Quantity_in_stock < THEN
INSERT into Infologs (msg)
VALUES (Prd_code);
End IF;
FETCH cur_product into Prd_code;

UNTIL no_more_products = 1
End REPEAT;
Close cur_product; /*finally:cursor need be closed to use close to release the resources/
SELECT * from Infologs;
DROP TABLE infologs;
End $$

DELIMITER;

Here is the final result: