MySQL存储过程中的游标使⽤、循环简单实例
MySQL 5.0 版本开始⽀持存储过程。
存储过程（Stored Procedure）是⼀种在数据库中存储复杂程序，以便外部程序调⽤的⼀种数据库对象。
存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，⽤户可通过指定存储过程的名字并给定参数(需要时)来调⽤执⾏。
存储过程思想上很简单，就是数据库 SQL 语⾔层⾯的代码封装与重⽤。
优点vb语言知识点总结
存储过程可封装，并隐藏复杂的商业逻辑。
存储过程可以回传值，并可以接受参数。
存储过程⽆法使⽤ SELECT 指令来运⾏，因为它是⼦程序，与查看表，数据表或⽤户定义函数不同。
存储过程可以⽤在数据检验，强制实⾏商业逻辑等。
创建存储过程
CREATE
[DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
insect是什么意思characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
routine_body:
  Valid SQL routine statement
[begin_label:] BEGIN
  [statement_list]
    ……
END [end_label]
MYSQL 存储过程中的关键语法
-- 声明语句结束符，可以⾃定义:
DELIMITER $$
或
DELIMITER //
-- 声明存储过程:
CREATE PROCEDURE demo_in_parameter(IN p_in int)
-- 存储过程开始和结束符号:
BEGIN .... END
-- 变量赋值:
SET @p_in=1
mysql查看所有存储过程-- 变量定义:
DECLARE l_int int unsigned default 4000000;
-- 创建mysql存储过程、存储函数:
create procedure 存储过程名(参数)
sl300十字滑块联轴器
-- 存储过程体:
create function 存储函数名(参数)
osxmavericks无法升级声明异常处理的语法
-- 声明异常处理的语法结构
DECLARE
{EXIT | CONTINUE}
HANDLER FOR
{error-number | SQLSTATE error-string | condition}
SQL statement
上述定义包括：
Handler Type （CONTINUE,EXIT）//处理类型 继续或退出
Handler condition （SQLSTATE,MYSQL ERROR,CONDITION）//触发条件
Handler actions（错误触发的操作）
注意：
1、exit只退出当前的block。exit 意思是当动作成功提交后，退出所在的复合语句。即declare exit 所在的复合语句。
2、如果定义了handler action，会在continue或exit之前执⾏
发⽣错误的条件有：
1、MYSQL错误代码
2、ANSI-standard SQLSTATE code
3、命名条件。可使⽤系统内置的SQLEXCEPTION,SQLWARNING和NOT FOUND
命名条件：
declare conditon_name condition for {SQLSTATE sqlstate_code | MYSQL_ERROR_CODE};
附常见错误号对照表
MySQL error code SQLSTATE code Error message
1011 HY000 Error on delete of '%s' (errno: %d)
1021 HY000 Disk full (%s); waiting for someone to free some space . . .
1022 23000 Can't write; duplicate key in table '%s'
1027 HY000 '%s' is locked against change
1036 HY000 Table '%s' is read only
1048 23000 Column '%s' cannot be null
1062 23000 Duplicate entry '%s' for key %d
1099 HY000 Table '%s' was locked with a READ lock and can't be updated
1100 HY000 Table '%s' was not locked with LOCK TABLES
1104 42000 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
1106 42000 Incorrect parameters to procedure '%s'
1114 HY000 The table '%s' is full
1150 HY000 Delayed insert thread couldn't get requested lock for table %s
1165 HY000 INSERT DELAYED can't be used with table '%s' because it is locked with LOCK TABLES
1242 21000 Subquery returns more than 1 row
1263 22004 Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld
1264 22003 Out of range value adjusted for column '%s' at row %ld
1265 1000 Data truncated for column '%s' at row %ld
1312 0A000 SELECT in a stored program must have INTO
1317 70100 Query execution was interrupted
1319 42000 Undefined CONDITION: %s
1325 24000 Cursor is already open
1326 24000 Cursor is not open
1328 HY000 Incorrect number of FETCH variables
1329 2000 No data to FETCH
1336 42000 USE is not allowed in a stored program
1337 42000 Variable or condition declaration after cursor or handler declaration
1338 42000 Cursor declaration after handler declaration
1339 20000 Case not found for CASE statement
1348 HY000 Column '%s' is not updatable
1357 HY000 Can't drop a %s from within another stored routine
1358 HY000 GOTO is not allowed in a stored program handler
1362 HY000 Updating of %s row is not allowed in %s trigger
1363 HY000 There is no %s row in %s trigger
-- 错误异常处理⽚段
-- 游标循环变量，遍历数据结束标志，默认false
DECLARE done INT DEFAULT FALSE;
DECLARE cur_account CURSOR FOR SELECT id,`status` FROM adm_users;
-- 当错误代码为1329时将 done 的值设为TRUE，并继续执⾏当前任务
DECLARE CONTINUE HANDLER FOR 1329 SET done = TRUE;
-
- 也可以这么写：使⽤的条件为ANSI标准错误代码
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;
-- 也可以这么写
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
实例
DELIMITER $$
CREATE PROCEDURE cs() -- 创建存储过程
BEGIN -- 开始存储过程
DECLARE vid INT(10) UNSIGNED DEFAULT 0;  -- ⾃定义变量id
DECLARE vstu TINYINT(1) UNSIGNED DEFAULT 0;  -- ⾃定义变量id
-- 游标循环变量，遍历数据结束标志，默认false
DECLARE done INT DEFAULT FALSE;
-- 定义游标并输⼊结果集
DECLARE cur_account CURSOR FOR SELECT id,`status` FROM adm_users;
-- 将结束标志绑定到游标，游标循环结束⾃动转true
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;asp源代码运行方法
-- 打开游标
OPEN  cur_account;
-- 开始循环，read_loop为⾃定义循环名，结束循环时⽤到
read_loop: LOOP
-- 取值将游标当前读取⾏的数据顺序赋予⾃定义变量
FETCH cur_account INTO vid,vstu;
-
- 判断是否继续循环
IF done THEN
LEAVE read_loop; -- 结束循环
END IF;
-- 你⾃⼰想做的操作
-- SELECT vid,vstu;
-- 重新插⼊⼀条语句
INSERT INTO `adm_users`(`username`, `password`, `verify_pwd`, `name`, `type`, `gid`, `status`, `add_time`)
SELECT `username`, `password`, `verify_pwd`, `name`, `type`, `gid`, `status`, UNIX_TIMESTAMP() AS `add_time` FROM `adm_users` WHERE id = vid;  SELECT vid,vstu,LAST_INSERT_ID() AS fristin;
-
- 结束⾃定义循环体
END LOOP read_loop;
CLOSE cur_account; -- 关闭游标
END $$ -- 结束存储过程
DELIMITER ;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。