sql 存储

-- 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