mysql存储过程的注意点
来源:互联网 发布:幸运抽奖软件破解版 编辑:程序博客网 时间:2024/05/29 07:18
以下为我写的一个例子:主要功能是把OA的弹窗提醒功能与redmine连接在一起;
delimiter //create trigger t_trigger AFTER insert on issues for each ROWBEGINDECLARE retValue INTEGER;DECLARE tmpname varchar(255);DECLARE title varchar(255) character set utf8;DECLARE tmpproject_id INTEGER;declare done tinyint(1) default 0;DECLARE cur1 CURSOR for select login from users where id in (select user_id from members where members.project_id = new.project_id);DECLARE CONTINUE HANDLER FOR NOT FOUND set done = 1;set tmpproject_id = new.project_id;set title = new.subject;open cur1;FETCH cur1 into tmpname;lable1: WHILE( tmpname is not NULL) DOIF done = 1 THENLEAVE lable1;END IF;select count(*) into retvalue from issues where my_test(tmpname, title);FETCH cur1 into tmpname;END WHILE;END;
写mysql存储过程应注意的几点:
1、声明变量(declare)时要注意字符集,用变量存储表字段时,表字段与变量的字符编码要一致。
2、mysql的字符合并不能用‘+’号,必须用concat函数。
3、每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束。
BEGIN
declare rt VARCHAR(100) CHARACTER SET gbk DEFAULT NULL;
declare done tinyint(1) default 0;
DECLARE ttname VARCHAR(60) CHARACTER SET gbk DEFAULT NULL;
DECLARE tsqltxt VARCHAR(512) CHARACTER SET gbk DEFAULT NULL;
DECLARE tremarks VARCHAR(60) CHARACTER SET gbk DEFAULT NULL;
DECLARE tfield VARCHAR(60) CHARACTER SET gbk DEFAULT NULL;
DECLARE curtable CURSOR FOR
SELECT distinct TABLE_name
FROM information_schema.TABLEs where TRIM(TABLE_COMMENT)<>'' and TRIM(TABLE_COMMENT)<>'VIEW' order by TABLE_name;
declare continue handler for not found set done=1;
set NAMES 'utf8';
drop table if EXISTS GetTableSQL;
CREATE TABLE `gettablesql` (
`tbname` varchar(60) CHARACTER SET gbk DEFAULT NULL,
`sqltxt` varchar(4096) CHARACTER SET gbk DEFAULT NULL,
`tabletitle` varchar(51) CHARACTER SET gbk DEFAULT NULL
) ENGINE=InnoDB;
open curtable;
tableloop:
LOOP
set tsqltxt='select';
FETCH curtable
INTO ttname;
IF done = 1 THEN
LEAVE tableloop;
END IF;
-- select ttname;
BEGIN
declare done1 tinyint(1) default 0;
DECLARE curfield CURSOR FOR
SELECT COLUMN_NAME,COLUMN_COMMENT
FROM information_schema.COLUMNS where upper(TABLE_name)=upper(ttname) and (COLUMN_COMMENT<>'') ;
declare continue handler for not found set done1=1;
OPEN curfield;
fieldloop:
LOOP
FETCH curfield
INTO tfield,tremarks;
IF done1 = 1 THEN
LEAVE fieldloop;
END IF;
if tsqltxt='select' THEN
set tsqltxt=CONCAT(tsqltxt,' ',tfield,' ','''',tremarks,'''');
ELSE
set tsqltxt=CONCAT(tsqltxt,',',tfield,' ','''',tremarks,'''');
END IF;
END LOOP fieldloop;
close curfield;
set tsqltxt=concat(tsqltxt,' from ',ttname);
insert into GetTableSQL values(ttname,tsqltxt,'');
END;
END LOOP tableloop;
close curtable;
update GetTableSQL as G set tabletitle=(select TABLE_COMMENT from information_schema.TABLEs s
where (trim(s.TABLE_COMMENT)<>'') and g.tbname=s.table_name );
select cast(count(*) as char) into rt from GetTableSQL;
set rt=concat('成功更新',rt,'个表的表名注释和字段注释到字典库GetTableSQL中!');
SELECT rt;
END
- mysql存储过程的注意点
- Mysql 存储过程注意点
- 写mysql存储过程应注意的几点
- mysql存储过程注意
- mysql存储过程需要注意的
- 使用mysql存储过程需要注意的
- MySQL 存储过程的那点知识
- sql2005创建存储过程(需要注意的几点)
- 扩展存储过程的编写和调试注意点
- IBatis.NET调用Oracle存储过程几点注意的
- 存储过程DataGrid分页及注意点
- 存储过程DataGrid分页及注意点
- ORACLE存储过程两个注意点
- mysql存储过程参数应该注意
- 存储过程DataGrid分页及注意点 选择自 mail_ricklee 的 Blog
- 技巧:SQL Server中写存储过程需注意的几点事项
- MySQL 存储过程那点事儿
- [原创]存储过程DataGrid分页及注意点
- 设置级联删除的解说
- PCI不同的地址域
- SQL提高查询效益之in、not in、between、like等条件讲述
- 内存泄漏以及常见的解决方法
- 科研牛人如何看科研文献
- mysql存储过程的注意点
- 用jQuery-mobile做的一个记事本(后台操作)
- 创建桌面悬浮窗
- c# 调用ArcEngine的GP工具
- quartz初始化
- 测试返回CURSOR
- js合并列
- 定义自治事务
- svn local delete, incoming delete upon update 解决办法