-- 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));
sql 函数写法
delimiter ||
-- 创建函数名
CREATE FUNCTION fun1(countId int )
RETURNS int -- renturns
BEGIN
DECLARE cunmu int ;
SELECT count(*) INTO cunmu FROM dept WHERE id = countId;
RETURN cunmu ; -- 返回数
END ||
SELECT fun1(2) -- 查询个数 之间select 就可以 。
-- 循环句柄的使用规则
delimiter ||
-- 创建 存储名称
CREATE PROCEDURE pro_test1()
-- 变量的声明 都放在前面
BEGIN
DECLARE e_id int;
DECLARE e_deptno int(11);
DECLARE e_dname VARCHAR(32) DEFAULT '';
DECLARE e_loc VARCHAR(64) DEFAULT '';
DECLARE has_data int DEFAULT 1 ;
-- cursor 定义句柄 for 后面接sql 语句
DECLARE emp_result CURSOR for SELECT id ,deptno,dname,loc FROM dept;
-- exit 就是直接退出 continue 表示继续执行后面的业务逻辑 for 后面接的报错的语法 ,set 设置值
-- 一定要 接在 cursor后面
DECLARE EXIT HANDLER for NOT FOUND set has_data = 0 ;
-- 打开 这个结果集
OPEN emp_result ;
-- repeat 循环
repeat
-- 遍历每一行 into 字段接收
FETCH emp_result into e_id,e_deptno,e_dname,e_loc;
SELECT CONCAT(e_id,e_deptno,e_dname,e_loc);
UNTIL has_data =0 -- until 后面没有逗号
end repeat ; -- 结束循环
CLOSE emp_result; -- 关闭集合
END ||
call pro_test1();
触发器 insert update delete 和js的事件类似
触发器的作用 数据完整性检测 ,对数据的校验 和检测,日志 。
触发中 两个变量一个OLD 一个new
通过new 或者old new 代表新增 old 代表修改之前的值
语法结构
create trigger 名称
befor/after insert/updat/delete
on table
-- 通过触发器来判断增删改查 对增删改都添加触发器
delimiter @
CREATE TRIGGER insert_test
after INSERT
on dept
for each row
BEGIN
-- 编写业务逻辑
INSERT INTO dept_log (id,date,doc) VALUES(null,now(),CONCAT(new.id));
END @
delimiter @
CREATE TRIGGER update_test
after UPDATE
on dept
for each row
BEGIN
-- 编写业务逻辑
INSERT INTO dept_log (id,date,doc) VALUES(null,now(),CONCAT(new.id));
END @
注意删除时old 的
删除触发器
drop triggers 触发器名称
查看触发器
show 触发器名称\G ;