收藏私塾在线
 

欢迎您来到私塾在线网!   

请登录! 

免费注册 

交流首页 » 数据库 »MySQL存储过程  XML
发表人 内容
lou
[头像]

交流经验:
总积分:117
级别:普通会员
注册时间: 2011-11-27
文章: 1215
离线

MySQL5.1参考手册

9.3. 用户变量

用户变量的形式为@var_name,当客户端退出时,该客户端连接的所有变量将自动释放。
设置用户变量的一个途径是执行SET语句:
SET @var_name = expr [, @var_name = expr] ...
如: SET @t1=0, @t2=0, @t3=0;

/* 声明用户变量(不在存储过程中) */
set @var = 123;
select @var;





存储过程中的局部变量名不能和列名一样

/* 存储过程声明局部变量 */
declare name varchar(20) DEFAULT 'nxj';
局部变量的作用范围在它被声明的BEGIN ... END块内

/* 给变量赋值 */
SET name = 'aaa';
给局部变量、全局服务器变量赋值

或使用

SELECT ... INTO语句
SELECT t_name INTO name FROM test

-----------------------------------------------------------------------------------
例子:


/* 按用户名查询地址 */

drop procedure if exists getAddressByPersonName;

delimiter //

create procedure getAddressByPersonName(IN p_name varchar(50)                                                                                                                                                                                                                        
begin     
    select p.personName,a.addressName from t_address as a                                                                                                                                                                                                                                                                 
    inner join t_person as p                                                                                                                                                                                                                                                                                              
    inner join t_person_address as pa                                                                                                                                                                                                                                                                                     
    on                                                                                                                                                                                                                                                                                                                    
    pa.personId = p.personId and                                                                                                                                                                                                                                                                                          
    pa.addressId = a.addressId and                                                                                                                                                                                                                                                                                        
    p.personName = p_name;                                                                                                                                                                                                                                                                                              
end //

delimiter ;






/* 插入三张表 */

drop procedure if exists insertAll;

delimiter //

create procedure insertAll(p_name varchar(10),a_name varchar(50))
begin
    -- 声明变量必需在开头,变量不能以@开头
    declare pid int(11);
    declare aid int(11);


    -- 错误处理 exit/continue
    declare exit handler for SQLEXCEPTION
    begin
        -- set msg = '出错啦!';
        select '出错啦!' as msg;
        rollback;
    end;


    -- 开始事务
    start transaction;

    insert into t_person(personName) value(p_name);
    insert into t_address(addressName) value(a_name);
    
    -- 不能用这种方式赋值变量 set p_name = select personId from t_person where personName = p_name;
    -- 要用如下方法
    select personId into pid from t_person where personName = p_name; -- ①
    select addressId into aid from t_address where addressName = a_name;

    insert into t_person_address(personId,addressId) value(pid,aid);
    -- 提交事务
    commit;
    
    -- set msg = '操作成功!';
    select '操作成功!' as msg;
end //

delimiter ;




/* 测试 */
call insertAll('宁晓进','文三路');
-- 同时插入两条一样的数据,会引起①处抛异常,存储过程全部操作回滚
-- call insertAll('宁晓进','文三路');







-----------------------------------------------------------------------------------



/*
    IN参数(默认):表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回

drop procedure if exists test;

DELIMITER //

CREATE PROCEDURE test(IN p_in INT)  -- 参数IN
BEGIN
--     select p_in as beforeChange; -- result: 1
    set p_in=2;
--     select p_in as afterChange; -- result: 2
END//

DELIMITER ;

set @p = 1;
call test(@p);
select @p as origine; -- result: 1

-- 以上可以看出,p_in虽然在存储过程中被修改,但并不影响@p的值
*/




/*
    OUT参数:外部不能传参数进来,存储过程中的变量可改变,并可返回

drop procedure if exists test;

DELIMITER //

CREATE PROCEDURE test(OUT p_in INT)
BEGIN
--     select p_in as beforeChange; -- result: null,外部调用该存储过程时无法传入参数所以p_in未定义
    set p_in=2;
    select p_in as afterChange; -- result: 2
END//

DELIMITER ;

set @p = 1;
call test(@p);
select @p as origine; -- result: 2
*/

 



/*
    INOUT:表示该参数的值必须在调用存储过程时指定,并且可被改变和返回
*/
drop procedure if exists test;

DELIMITER //

CREATE PROCEDURE test(INOUT p_in INT)
BEGIN
--     select p_in as beforeChange; -- result: 1,调用外部调用该存储过程时可以传入参数
    set p_in=2;
--     select p_in as afterChange; -- result: 2,使用INOUT,存储过程内部修改的可以返回
END//

DELIMITER ;

set @p = 1;
call test(@p);
select @p as origine; -- result: 2


-----------------------------------------------------------------------------------























/* 创建procedure */

DELIMITER //

CREATE PROCEDURE test()
BEGIN
    select "hello procedure!!" as hello;
END//

DELIMITER ;   /* 还原结束符为分号 */


说明:

mysql> DELIMITER //
分隔符是你通知mysql客户端你已经完成输入一个SQL语句的字符或字符串符号。一直以来
我们都使用分号  “;”,但在存储过程中,这会产生不少问题,因为存储过程中有许多语
句,所以每一个都需要一个分号因此你需要选择一个不太可能出现在你的语句或程序中的
字符串作为分隔符。我曾用过双斜杠  “// ”,也有人用竖线  “|”。我曾见过在DB2程序中使用
“@”符号的,但我不喜欢这样。你可以根据自己的喜好来选择,但是在这个课程中为了更
容易理解,你最好选择跟我一样。如果以后要恢复使用  “;”(分号)作为分隔符,输入下
面语句就可以了:
"DELIMITER ;//".

-----------------------------------------------------------------------------------


/* 查看procedure */
show create procedure test
这个语句是一个MySQL的扩展。类似于SHOW CREATE TABLE,它返回一个可用来重新创建已命名子程序的确切字符串。




show procedure status like "test"
这个语句是一个MySQL的扩展。它返回子程序的特征,如数据库,名字,类型,创建者及创建和修改日期。如果没有指定样式,根据你使用的语句,所有存储程序和所有存储函数的信息都被列出。





/* 调用procedure */
call test();










20.2.9.3. SELECT ... INTO语句

SELECT col_name[,...] INTO var_name[,...] table_expr
这个SELECT语法把选定的列直接存储到变量。因此,只有单一的行可以被取回。
SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
注意,用户变量名在MySQL 5.1中是对大小写不敏感的。请参阅9.3节,“用户变量”。
重要: SQL变量名不能和列名一样。如果SELECT ... INTO这样的SQL语句包含一个对列的参考,并包含一个与列相同名字的局部变量,MySQL当前把参考解释为一个变量的名字。例如,在下面的语句中,xname 被解释为到xname variable 的参考而不是到xname column的:

CREATE PROCEDURE sp1 (x VARCHAR(5))
  BEGIN
    DECLARE xname VARCHAR(5) DEFAULT 'bob';
    DECLARE newname VARCHAR(5);
    DECLARE xid INT;

    SELECT xname,id INTO newname,xid
      FROM table1 WHERE xname = xname;
    SELECT newname;
  END;

当这个程序被调用的时候,无论table.xname列的值是什么,变量newname将返回值‘bob’。



20.2.12.1. IF语句

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

即:
IF ... THEN
    ...;
ELSE
    IF ... THEN
      ...;
    ELSEIF
      ...;
    ELSE
      ...;
    END IF;
END IF;

IF实现了一个基本的条件构造。如果search_condition求值为真,相应的SQL语句列表被执行。如果没有search_condition匹配,在ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。
请注意,也有一个IF() 函数,它不同于这里描述的IF语句。请参阅12.2节,“控制流程函数”。





20.2.12.2. CASE语句

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Or:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

存储程序的CASE语句实现一个复杂的条件构造。如果search_condition 求值为真,相应的SQL被执行。如果没有搜索条件匹配,在ELSE子句里的语句被执行。
注意:这里介绍的用在存储程序里的CASE语句与12.2节,“控制流程函数”里描述的SQL CASE表达式的CASE语句有轻微不同。这里的CASE语句不能有ELSE NULL子句,并且用END CASE替代END来终止。





20.2.12.3. LOOP语句

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

LOOP允许某特定语句或语句群的重复执行,实现一个简单的循环构造。在循环内的语句一直重复直循环被退出,退出通常伴随着一个LEAVE 语句。
LOOP语句可以被标注。除非begin_label存在,否则end_label不能被给出,并且如果两者都出现,它们必须是同样的。


20.2.12.4. LEAVE语句
LEAVE label
这个语句被用来退出任何被标注的流程控制构造。它和BEGIN ... END或循环一起被使用。







20.2.12.5. ITERATE语句
ITERATE label
ITERATE只可以出现在LOOP, REPEAT, 和WHILE语句内。ITERATE意思为:“再次循环。”

例如:

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN
        ITERATE label1;
    END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END






20.2.12.6. REPEAT语句

[begin_label:] REPEAT
    statement_list
    UNTIL search_condition
END REPEAT [end_label]

REPEAT语句内的语句或语句群被重复,直至search_condition 为真。
REPEAT 语句可以被标注。 除非begin_label也存在,end_label才能被用,如果两者都存在,它们必须是一样的。

例如:

mysql> delimiter //

mysql> CREATE PROCEDURE dorepeat(p1 INT)

    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT SET @x = @x + 1;
    ->   UNTIL @x > p1 END REPEAT;
    -> END
    -> //

Query OK, 0 rows affected (0.00 sec)

mysql> CALL dorepeat(1000)//

Query OK, 0 rows affected (0.00 sec)


mysql> SELECT @x//

+------+

| @x   |

+------+

| 1001 |

+------+

1 row in set (0.00 sec)





20.2.12.7. WHILE语句

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

WHILE语句内的语句或语句群被重复,直至search_condition 为真。
WHILE语句可以被标注。 除非begin_label也存在,end_label才能被用,如果两者都存在,它们必须是一样的。
例如:

CREATE PROCEDURE dowhile()

BEGIN
  DECLARE v1 INT DEFAULT 5;
  WHILE v1 > 0 DO
    ...
    SET v1 = v1 - 1;
  END WHILE;
END


推广链接
精品视频课程推荐

Java Web开发理论部分视频教程
系统掌握开发实际的Java Web应用所需的理论知识和技能(Servlet、Jsp、JavaBean、TagLib、EL、JSTL、MVC模式、连接池DataSource、JNDI等知识)。涉及项目:在线投票计数、在线人数统计、登录检查、购物车、商品管理、非法字符替换等多个随堂演示小应用。

Spring3开发实战-独家视频教程
从零到精通Spring3的开发知识;IoC/DI的思想、IoC/DI的运行流程、IoC/DI的开发指导、AOP的思想、AOP的运行流程、AOP应用的设计、Spring对JDBC和Hibernate的支持、Spring的事务、SSH的集成应用

深入浅出学Spring Web MVC视频教程
系统、完整的学习Spring Web MVC开发的知识。包括:Spring Web MVC入门;理解DispatcherServlet;注解式控制器开发详解;数据类型转换;数据格式化;数据验证; 拦截器;对Ajax的支持;文件上传下载;表单标签等内容;最后以一个综合的CRUD带翻页的应用示例来综合所学的知识



个性签名:野心勃勃
ayun
[头像]

交流经验:
总积分:1
级别:普通会员
注册时间: 2012-08-04
文章: 102
离线

非常感谢


个性签名:啊看见的回复可见阿斯顿警方
yuzebaby
[头像]

交流经验:
总积分:4517
级别:VIP1
注册时间: 2012-07-19
文章: 400
离线

数据库确实重要



个性签名:每天进步一点点
 
交流首页 » 数据库
前往:   

关于我们 | 联系我们 | 用户协议 | 私塾在线服务协议 | 版权声明 | 隐私保护

版权所有 Copyright(C)2009-2012 私塾在线学习网