MySQL 存储过程的使用

 

drop procedure if exists test.get_title;
delimiter $$
create procedure test.get_title(in id int)
BEGIN
	select title,subtitle,pubname from titles,publishers
	where titleID=id and titles.pubID=publishers.pubID;
end$$

call test.get_title(1);

drop procedure if exists test.half;
delimiter $$
create procedure test.half(in a int , out b DOUBLE)
BEGIN
	set b = a/2;
end $$
call test.half(15,@result);
select @result;

 

2、变量

•    全局性的普通SQL变量。这类变量的名字前面都有一个“@”字符作为前缀。这些变量在SP里的用法与它们在普通SQL命令里的用法一样。它们的内容将一直保存到与MySQL服务器断开链接为止。
• SP内部使用的局部变量和参数。这些变量的名称前面没有“@”字符。必须在使用它们之前用declare命令对它们做出声明。局部变量的内容在过程或函数退出执行时即告丢失。局部变量只在对它们做出声明的那个begin-end语句块里有效。这意味着可以在同一个过程里声明多个同名的局部变量——只要它们不在同一个begin-end语句块里就行。
• 声明变量（declare）。变量的声明必须发生在begin-end语句块里，而且必须发生同一个begin-end语句块里的其他命令之前。

  declare varname1,varname2,... datatype [default value];

示例代码：

drop procedure if exists test.test;
create procedure test.test()
BEGIN
	declare x int default 0;
	BEGIN
		DECLARE x int DEFAULT 1;
		if true then 
			BEGIN
				DECLARE x int DEFAULT 2;
				select x ;
			END;
		end if;
		SELECT x as `outer`;
	END;
	select x as outest;
END;

call test.test();

  3、对变量赋值 。SQL语言不允许以x=x+1的形式对变量进行赋值，必须使用SET或SELECT INTO命令做这件事。后者都是SELECT命令的一程变体，它以INTO varname 结果整条命令。这种语法变量要求SELECT命令返回且只能返回一条数据记录（不请允许是多条记录）。请注意，在函数里只能使用SET命令对变量赋值，这是因为函数里不允许使用SELECT或其他SQL命令。

# 在普通环境中使用变量例子
set @var1 = value1, @var2 = value2;
select @var1:=value2;
select 2 * 7 into var1;
select @total:=count(*) from table where id =1 into @total;
select @total:=count(*) from table where id =1 ;
select title,subtitle from table1 where id =1 into @var_title,@var_subtitle;
SELECT id, data INTO x, y FROM test.t1 LIMIT 1;

 

# 在存储过程中使用变量例子。
drop procedure if exists test.total;
create procedure test.total()
BEGIN
	declare x int default 0;
	select count(*) from name1 into x;
  select x;
END;
call total();

4、出错处理（出错处理句柄）

      SP里的SQL命令在执行过程中可能会出错，所以SQL也像其他一些程序设计语言那样向程序员提供了一种利用出错处理句柄（error handler，也有称为“出错处理器”）来响应和处理这类错误的机制。

      在一个Begin-end语句块里对出错处理句柄的定义必须出现在变量、光标、出错条件的声明之后、其他SQL命令之前；具体语法如下所示：

 

declare type handler for condition1, condition2, condition3;

 下面对这个语法中的type、condition和command进行解释。

 

增加分类记录的存储过程：

 表结构的SQL：

CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(100) default NULL,
  `parent_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`)
) ;

 

drop procedure if exists test.test_insert;
create procedure test.test_insert(in newcatname varchar(100), in parent int, out newid int)
proc:BEGIN
	declare cnt int;
	set newid = -1;
	-- validation
	select count(*) from test.categories where parent_id = parent into cnt;
	if ISNULL(newcatname) or TRIM(newcatname) ="" then 
		leave proc; 
	end if;
	select count(*) from test.categories WHERE parent_id = parent and `name` = newcatname into cnt;
	if cnt =1 then 
		select id from  test.categories WHERE parent_id = parent and `name` = newcatname into newid; 
		leave proc;
	end if;
	insert into test.categories (`name`,parent_id)values(newcatname,parent);
	set newid = LAST_INSERT_ID();
end proc;
-- 调用不能与创建语句同时执行？
call test.test_insert("category name 2",0,@newid);
select @newid;

 

夺