一、数据库存储过程举例：
1、搭建数据库
id last_name gender email 1 mike 0 [email protected] 2 book 0 [email protected] 3 tom 1 [email protected] 4 jerry 1 [email protected] 5 hhee 1 [email protected] 6 jerry4 1 [email protected] 7 smith0x1 1 [email protected] 8 mas 1 [email protected] 9 smith0x1 1 [email protected] 10 allen0x1 0 [email protected]
2、创建存储过程
USE `mybatis`; DROP PROCEDURE IF EXISTS proc_employee; DELIMITER // CREATE PROCEDURE proc_employee(IN p_start INT, IN p_end INT,OUT p_count INT) BEGIN SELECT COUNT(*) INTO p_count FROM `tbl_employee`; SELECT * FROM ( SELECT a.* FROM `tbl_employee` a WHERE a.`id` < p_end) b WHERE b.`id` >p_start; END// DELIMITER ;
特别注意点：
开头部分:DELIMITER //【这两斜杠前面有空格，需要注意】 结尾部分：DELIMITER ;【分号前面也有个空格，需要注意】 在定义过程时，使用DELIMITER // 命令将语句的结束符号从分号 ; 临时改为//，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。
3、mysql客户端调用存储过程
set @p_count=1; CALL proc_employee(1,8,@p_count); select @p_count;
二、数据库操作举例
-- 8-1: Stored Procedure Intro
-- http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1_appe.html
-- stored procedures
-- paste this script into the mysql client
USE test;
DELIMITER | -- // permit semi-colons within SP
DROP PROCEDURE IF EXISTS worldmesgproc |
CREATE PROCEDURE worldmesgproc( IN s CHAR( 10 ) )
SELECT CONCAT_WS( ' ', s, 'world!' );
|
DROP FUNCTION IF EXISTS worldmesgfunc |
CREATE FUNCTION worldmesgfunc( s CHAR( 10 ) ) RETURNS CHAR( 20 )
RETURN CONCAT_WS( s, 'world!' );
|
DELIMITER ; -- // restore semi-colon as delimiter
CALL worldmesgproc( 'Hello' );
Hello world!
SELECT worldmesgfunc( 'Hello' );
Hello world!
CALL worldmesgfunc( 'Hi' ); -- // funcs cannot be called
ERROR 1289 at line 18: PROCEDURE worldmesgfunc does not exist
DROP PROCEDURE worldmesgproc;
DROP FUNCTION worldmesgfunc;
-- # EOF
-- 8-2: DECLARE ... HANDLER in an SP
-- # Here, because DECLARE EXIT HANDLER … instructs MySQL to exit the SP
-- # if SQLSTATE = 23000, the statement SET @err=-1 never executes. Notice that
-- # in order to be able to mark the end of the SP, the code has to set the delimiter to
-- # something other than a semi-colon; this is best done just before creating the SP,
-- # and reset immediately after. The DELIMITER command does not need a second
-- # terminator.
USE test ;
SET @err = 0 ;
SELECT 'Before running errhandlerdemo:', @err ;
CREATE TABLE IF NOT EXISTS testhandler (i INT, PRIMARY KEY(i)) ;
DELIMITER |
DROP PROCEDURE IF EXISTS errhandlerdemo ;
CREATE PROCEDURE errhandlerdemo()
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET @err=23000;
INSERT INTO testhandler VALUES( NULL) ;
SET @err=-1 ;
END ;
|
DELIMITER ;
CALL errhandlerdemo() |
DROP TABLE testhandler |
SELECT 'After running errhandlerdemo:', @err;
-- # EOF
-- 8-3:IF...THEN... in an SP
USE test;
DROP FUNCTION IF EXISTS is_even;
DELIMITER |
CREATE FUNCTION is_even( x INT ) RETURNS INT
BEGIN
DECLARE iRet INT DEFAULT 0;
IF x/2 = 0 THEN
SET iRet = 1;
END IF;
RETURN iRet;
END
|
DELIMITER ;
SELECT is_even( 3 );
-- #EOF
-- 8-4: CASE...WHEN...ENDCASE in an SP
USE test;
DROP PROCEDURE IF EXISTS case1proc;
DELIMITER |
CREATE PROCEDURE case1proc( IN x INT )
BEGIN
CASE x
WHEN 'string' THEN SELECT 'non-matching value will not execute';
WHEN 0 THEN SELECT 'matching value executes';
WHEN 17 THEN SELECT 'non-matching value will not execute';
END CASE;
END;
|
DROP PROCEDURE IF EXISTS case2proc |
CREATE PROCEDURE case2proc( IN x INT )
BEGIN
DECLARE s CHAR( 10 );
CASE
WHEN x < 0 THEN SET s = 'less than zero';
WHEN x < 10 THEN SET s = 'units';
WHEN x < 100 THEN SET s = 'tens';
WHEN x < 1000 THEN SET s = 'hundreds';
ELSE SET s = 'a thousand or more';
END CASE;
SELECT CONCAT( 'range is ', s );
END;
|
DELIMITER ;
CALL case1proc( 0 ); -- # output: matching value executes
CALL case2proc( 100 ); -- # output: range is hundreds
-- # EOF
-- 8-5: DO .. REPEAT
DELIMITER |
DROP PROCEDURE IF EXISTS dorepeat;
CREATE PROCEDURE dorepeat( IN imax INT )
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE s CHAR( 20 ) DEFAULT 'Loops:';
REPEAT
BEGIN
SET s = CONCAT_WS( ' ', s, i );
SET i = i + 1;
END;
UNTIL i > imax END REPEAT;
SELECT s;
END
|
DELIMITER ;
CALL dorepeat( 5 );
-- #EOF
-- 8-6: DO ... WHILE ...
USE test;
DROP PROCEDURE IF EXISTS whileproc;
DELIMITER |
CREATE PROCEDURE whileproc( IN x INT )
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE s CHAR( 20 ) DEFAULT "Loops:";
WHILE i-5 DO
BEGIN
SET i = i + 1;
SET s = CONCAT_WS( ' ', s, i );
END;
END WHILE;
SELECT s;
END;
|
DELIMITER ;
CALL whileproc( 5 ); -- output: Loops: 1 2 3 4 5
-- #EOF
-- 8-7: Cursor, handler, loop and iteration in an SP
USE test;
DROP TABLE IF EXISTS curtest1;
DROP TABLE IF EXISTS curtest2;
CREATE TABLE curtest1( i INT PRIMARY KEY, j INT, name CHAR(10) );
CREATE TABLE curtest2( x INT );
INSERT INTO curtest1 VALUES (0,0,'first'),(1,2,'second'),(2,1,'third');
DROP PROCEDURE IF EXISTS cursxmpl;
DELIMITER |
CREATE PROCEDURE cursxmpl()
BEGIN
DECLARE mi INT;
DECLARE mj INT;
DECLARE done INT DEFAULT 0;
DECLARE curs CURSOR FOR SELECT i, j FROM test.curtest1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN curs;
loop0: WHILE NOT done DO
FETCH curs INTO mi, mj;
IF NOT done THEN
IF mi < mj THEN
INSERT INTO test.curtest2 VALUES (mi);
ELSEIF mi > mj THEN
INSERT INTO test.curtest2 VALUES (mj);
ELSE
ITERATE loop0;
END IF;
END IF;
END WHILE loop0;
CLOSE curs;
END;
|
DELIMITER ;
CALL cursxmpl();
SELECT * FROM curtest2;
-- # EOF