-- delimiter
DELIMITER
create procedure test()
begin
SELECT * FROM test ;
end
delimiter ;
CALL test();
-- 变量声明 declare
DROP PROCEDURE test1;
DELIMITER
create procedure test1()
begin
-- 声明一个变量
DECLARE userName varchar(32) DEFAULT '1';
-- 使用set赋值
set userName = '小李';
into 赋值
SELECT name into userName FROM test WHERE id = 1 ;
返回变量
SELECT userName ;
end
delimiter ;
CALL test1();
-- in out inout参数
DROP PROCEDURE test2;
DELIMITER
create procedure test2(IN userId int,out username varchar(32))
begin
-- 声明一个变量
DECLARE userId int default 0;
-- into 赋值
SELECT name into username FROM test WHERE id = userId ;
-- 返回变量
SELECT userId,username ;
end
delimiter ;
set @uname ='';
set @userId =2;
CALL test2(@userId,@uname);
-- in
DROP PROCEDURE test7;
DELIMITER
create procedure test7(in userId int)
begin
declare username varchar(32) default '';
select name into username from test where id=userId;
select username;
end
delimiter ;
CALL test7(2);
-- if then
DROP PROCEDURE test8 ;
delimiter;
CREATE PROCEDURE test8 ( IN userid INT )
begin
declare my_status int default 0;
select id into my_status from test where id=userid;
if(my_status=1)
then
select id into my_status from test where id=1;
elseif(my_status=2)
then
select id into my_status from test where id=2;
else
select -1 into my_status;
end if;
SELECT my_status ;
end;
delimiter;
CALL test8 (1);
-- 循环
DROP PROCEDURE test11 ;
delimiter;
CREATE PROCEDURE test11 ( IN userid INT )
begin
DECLARE var int DEFAULT 0 ;
DECLARE str VARCHAR(256) DEFAULT '' ;
set var = 0 ;
while var<6 do
set var=var+1 ;
select concat(var,'_') into str from test where id=userid;
end WHILE ;
SELECT str ;
end;
delimiter;
CALL test11 (1);
-- 循环loop
DROP PROCEDURE test12 ;
delimiter;
CREATE PROCEDURE test12 ( IN userid INT )
begin
DECLARE var int DEFAULT 0 ;
DECLARE str VARCHAR(256) DEFAULT '0' ;
set var = 0 ;
testloop:LOOP
if var<5
then
set var= var+1;
SELECT var ;
set str= CONCAT(str,var);
end if ;
LEAVE testloop ;
end LOOP ;
SELECT str ;
end;
delimiter;
-- case
delimiter;
CREATE PROCEDURE test13()
BEGIN
DECLARE str VARCHAR(50) DEFAULT '';
case years when 40 then "" ;
End case;
end ;
-- 循环 loop loop 是死循环 repat while
DROP PROCEDURE test15 ;
delimiter;
CREATE PROCEDURE test15()
BEGIN
DECLARE str VARCHAR(256) DEFAULT '1';
DECLARE c_index int DEFAULT 1;
scc: loop
SELECT str ;
if c_index >10 THEN
LEAVE scc ;
end if ;
set c_index =c_index+1;
set str= CONCAT(str,',',c_index);
end loop ;
SELECT str ;
End ;
CALL test15();
-- 用户变量
delimiter ||
CREATE PROCEDURE test16()
BEGIN
set@ss='123' ;
end ||
delimiter ||
call test16();
SELECT @ss;
-- 循环打印
DROP PROCEDURE test17;
delimiter ||
CREATE PROCEDURE test17()
BEGIN
DECLARE a int ;
DECLARE a1 VARCHAR(255) DEFAULT '';
set a =1 ;
cnt:loop
if a >10 THEN LEAVE cnt ;
else
set a =a+1;
set a1 = CONCAT(a,',',a1);
SELECT a ;
SELECT a1;
end if ;
end loop cnt;
END ||
CALL test17();
-- handler
DROP PROCEDURE test18;
delimiter ;
CREATE PROCEDURE test18()
BEGIN
DECLARE e_id int ;
DECLARE e_name VARCHAR(32);
DECLARE flag boolean DEFAULT true ;
DECLARE emm CURSOR for
SELECT id ,name
FROM test ;
-- handler
DECLARE CONTINUE HANDLER for not found set flag = false;
open emm ;
emmlop:loop
fetch emm into e_id,e_name;
if flag then SELECT e_id,e_name ;
else leave emmlop ;
end if ;
end loop emmlop;
CLOSE emm ;
end ;
delimiter;
CALL test18() ;
-- 查看触发器
show TRIGGERS
-- 删除触发器
drop TRIGGER emp_dept
-- 创建触发器
delimiter
-- 创建触发器
CREATE TRIGGER emp_dept
-- 在插入之后
AFTER INSERT
-- on 对应操作的表
on dept
-- 逐行操作
for each row
-- 业务逻辑
BEGIN
INSERT into dept_log(id,date,doc) VALUES(null,NOW(),CONCAT(new.id,',',new.deptno,new.dname,new.loc));
end