Home > Database > Mysql Tutorial > body text

mysql study notes: stored procedures

黄舟
Release: 2017-02-15 10:57:54
Original
990 people have browsed it

use test;

drop table if exists t8;
CREATE TABLE t8(s1 INT,PRIMARY KEY(s1));

drop procedure if exists handlerdemo;

DELIMITER $$
CREATE PROCEDURE handlerdemo()
BEGIN
declare xx int default 4;
DECLARE oh_no condition for sqlstate '23000';
#DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @X2=1;
declare exit handler for oh_no set @info='违反主键约束';
SET @X=1;
INSERT INTO t8 VALUES(1);
SET @X=2;
INSERT INTO t8 VALUES(1);
SET @X=3;
END
Copy after login


Call stored procedure


/* 调用存储过程*/
CALL handlerdemo();

/* 查看调用存储过程结果*/
SELECT @X,@info;
Copy after login


Experience:


1. Statement terminator

Perhaps mysql regards stored procedures and custom functions as one statement. Therefore, multiple statements in the stored procedure are separated by ";". In order to avoid conflicts, just Use delimiter to redefine the terminator.

Generally, you can define a new terminator before the stored procedure starts, such as

delimiter //

After the stored procedure is written, restore the definition: delimiter;


2. Variables

Mysql variables are the same as SQL SERVER, in the shape of @X, but no need to declare, they can be used directly.

In the stored procedure, variables do not need to be @, but they must be declared. And the statement should be placed at the head of the stored procedure (?), as in this example, otherwise an error will be reported. It's really strange. On the one hand, sometimes variables can be used without declaring them. On the other hand, sometimes the declaration position must be limited, which is confusing and seems a bit casual.

The variables inside the stored procedure have scope limited to the stored procedure. But those variables with @ seem to span sessions and connections, and appear to be global variables? Like the example above.


3. Conditions and processing

Define conditions to call processing. Such as the above example:


DECLARE oh_no condition for sqlstate '23000';
#DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @X2=1;
declare exit handler for oh_no set @info='违反主键约束';
Copy after login


The above is the above content. For more related content, please pay attention to the PHP Chinese website (www.php.cn)! For more related content, please pay attention to the PHP Chinese website (www.php.cn)!



Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template