- -- 创建存储过程
DELIMITER $$
CREATE PROCEDURE testa()
BEGIN
SELECT * FROM student WHERE id=2;
END $$
-- 调用存储过程
call testa();
1.3存储过程/函数的查、删
- 查
-- 查看存储过程或者函数的状态
SHOW PROCEDURE STATUS LIKE 'testa';
-- 查看存储过程或者函数的定义
SHOW CREATE PROCEDURE testa
- 删
-- 删除存储过程
DROP PROCEDURE testa1;
-- 删除函数
DROP FUNCTION testa1;
1.4存储过程的变量
- 需求: 编写存储过程,使用变量取id=2的用户名;变量my_uname
DELIMITER $$
CREATE PROCEDURE testa3()
BEGIN
-- 1.变量的声明使用declare,一句declare只声明一个变量,变量必须先声明后使用
-- 2.变量具有数据类型和长度,与mysql的SQL数据类型保持一致,因此甚至还能指定默认值、字符集和排序规则等
DECLARE my_uname VARCHAR(32) DEFAULT '';
-- 3.变量可以通过set来赋值,也可以通过select into的方式赋值
SET my_uname='itheima';
SELECT NAME INTO my_uname FROM student WHERE id=2;
-- 4.变量需要返回,可以使用select语句,如:select 变量名
SELECT my_uname;
END $$
CALL testa3();
- 变量作用域
- 变量是有作用域的,作用范围在begin与end块之间,end结束变量的作用范围即结束。
- .需要多个块之间传递值,可以使用全局变量,即放在所有代码块之前。
- 传参变量是全局,可以在多个块之间起作用
1.5存储过程传入参数 IN型
- 例
-- 需求:编写存储过程,传入id,返回该用户的name
DELIMITER $$
CREATE PROCEDURE getName(my_uid INT)
BEGIN
DECLARE my_uname VARCHAR(32) DEFAULT '';
SELECT NAME INTO my_uname FROM student WHERE id=my_uid;
SELECT my_uname;
END;
$$
CALL getName(2);
- 传入参数:类型为IN,表示该参数的值必须在调用存储过程时指定,如果不显式指定为IN,那么默认就是IN类型。
- IN类型参数一般只用于传入,在调用存储过程中一般不作修改和返回
- 如果调用存储过程中需要修改和返回值,可以使用OUT类型参数
1.6存储过程传出参数OUT型
- 例
-- 需求:调用存储过程时,传入uid返回该用户的uname
DELIMITER $$
CREATE PROCEDURE getName22(IN my_uid INT,OUT my_uname VARCHAR(32))
BEGIN
SELECT NAME INTO my_uname FROM student WHERE id=my_uid;
SELECT my_uname;
END;
$$
-- 指定传入参数变量
SET @uname:='';
CALL getName22(2,@uname);
-- 起别名
SELECT @uname AS myName;
- 传出参数:在调用存储过程中,可以改变其值,并可返回
- OUT是传出参数,不能用于传入参数值
- 调用存储过程时,OUT参数也需要指定,但必须是变量,不能是常量
- 如果既需要传入,同时又需要传出,则可以使用INOUT类型参数
1.7存储过程可变参数INOUT型
- 例:
-- 需求:调用存储过程时,参数my_uid和my_uname,既是传入,也是传出参数
DELIMITER $$
CREATE PROCEDURE getName3(INOUT my_uid INT,INOUT my_uname VARCHAR(32))
BEGIN
SET my_uid=2;
SET my_uname='hxf3';
SELECT id,NAME INTO my_uid,my_uname FROM student WHERE id=my_uid;
SELECT my_uid,my_uname;
END;
$$
SET @uname:='';
SET @uid:=0;
CALL getName3(@uid,@uname);
SELECT @uname AS myName;
- 可变变量INOUT:调用时可传入值,在调用过程中,可修改其值,同时也可返回值。
- INOUT调用时传入的是变量,而不是常量
1.8存储过程条件语句
- 例:
-- 需求:编写存储过程,如果用户uid是偶数则就给出uname,其它情况只返回uid
DELIMITER $$
CREATE PROCEDURE getName44(IN my_uid INT )
BEGIN
DECLARE my_uname VARCHAR(32) DEFAULT '';
IF(my_uid%2=0)
THEN
SELECT NAME INTO my_uname FROM student WHERE id=my_uid;
SELECT my_uname;
ELSE
SELECT my_uid;
END IF;
END;
$$
CALL getName44(1);
CALL getName44(2);
- 条件语句最基本的结构:if() then …else …end if;
1.9存储过程循环语句
- while循环
- 例:
-- 需求:使用循环语句,向表users(uid)中插入10条uid连续的记录。
DELIMITER $$
CREATE PROCEDURE insertdata()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE(i< 10) DO
BEGIN
SELECT i;
SET i=i+1;
INSERT INTO users(NAME , address) VALUES("孙悟空" , "广州");
END ;
END WHILE;
END;
DELIMITER $$
CREATE PROCEDURE insertdata2()
BEGIN
DECLARE i INT DEFAULT 100;
REPEAT
BEGIN
SELECT i;
SET i=i+1;
INSERT INTO users(NAME) VALUES('黑马');
END ;
UNTIL i >= 110
END REPEAT;
END;
$$
- 在存储过程和函数中,可以使用光标(有时也称为游标)对结果集进行循环的处理
- 基本使用
- 申明光标:cursor for
- 打开光标:open
- 移动光标:fetch
- 关闭光标:close
- 例:
-- 编写存储过程,使用光标,把id为偶数的记录逐一更新用户名。
DELIMITER $$
CREATE PROCEDURE testcursor()
BEGIN
-- 控制光标循环结束标记
DECLARE stopflag INT DEFAULT 0;
DECLARE my_uname VARCHAR(20);
-- cursor for 申明光标
DECLARE uname_cur CURSOR FOR SELECT NAME FROM student WHERE id%2=0 ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
OPEN uname_cur; -- 打开游标
FETCH uname_cur INTO my_uname; -- 游标向前走一步,取出一条记录放到变量my_uname中。
WHILE( stopflag=0 ) DO -- 如果游标还没有到结尾,就继续
BEGIN
UPDATE student SET NAME=CONCAT(my_uname,'_cur') WHERE NAME=my_uname;
-- 游标向前走一步,取出一条记录放到变量my_uname中。
FETCH uname_cur INTO my_uname;
END ;
END WHILE;
CLOSE uname_cur;
END;
$$
DELIMITER ;