-- 2. 创建带 in 模式参数的存储过程 -- 根据女神名查询对应的男神信息 CREATEPROCEDURE myp2(IN beautyName VARCHAR(20)) BEGIN SELECT bo.* FROM boys bo RIGHTJOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name = beautyName; END $
CALL myp2('小昭')$
-- 传入双参数,验证登陆 CREATEPROCEDURE myp4(IN username VARCHAR(20), INPASSWORDVARCHAR(20)) BEGIN DECLAREresultVARCHAR(20) DEFAULT''; SELECTCOUNT(*) INTOresult FROMadmin WHERE admin.username = username AND admin.password = PASSWORD;
-- SELECT result; SELECTIF(result>0, 'success', 'failed'); END $
CALL myp4('lin', 4)$
-- 3. 创建带 out 模式的存储过程 -- 根据女神名返回男神名 CREATEPROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20)) BEGIN SELECT bo.boyname INTO boyName FROM boys bo INNERJOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name=beautyName; END $
# 调用 CALL myp5('小昭', @bName)$ SELECT @bName$
-- 根据女神名返回对应男神名和魅力值 CREATEPROCEDURE myp6(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT userCP INT) BEGIN SELECT bo.boyname, bo.userCP INTO boyName, userCP FROM boys bo INNERJOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name=beautyName; END $
-- 1. 无参有返回 -- 返回公司员工个数 CREATEFUNCTION myf1() RETURNSINT BEGIN DECLARE c INTDEFAULT0; SELECTCOUNT(*) INTO c FROM employees; RETURN c; END$ SELECT myf1()$
-- 报错:ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL -- 设置:SET global log_bin_trust_function_creators=TRUE;
-- 2. 有参数返回 -- 根据员工名返回工资 CREATEFUNCTION myf2(empName VARCHAR(20)) RETURNSDOUBLE BEGIN SET @sal=0; SELECT salary INTO @sal FROM employees WHERE last_name = empName;
RETURN @sal; END$ select myf2('Kochhar') $
-- 根据部门名返回平均工资 CREATEFUNCTION myf3(deptName VARCHAR(20)) RETURNSDOUBLE BEGIN DECLARE sal DOUBLE;
SELECTAVG(salary) INTO sal FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.department_name=deptName;
CASE 变量|表达式|字段 WHEN 要判断的值 THEN 返回值 1 或语句 1; WHEN 要判断的值 THEN 返回值 2 或语句 2; … ELSE 要返回的值 n 或语句 2; END CASE;
方式二:
CASE WHEN 要判断的条件1 THEN 返回值 1 WHEN 要判断的条件2 THEN 返回值 2 … ELSE 要返回的值 n END
可以作为表达式,嵌套在其他语句中使用,比如 BEGIN END 中/外
可以作为独立的语句去使用,只能放在 BEGIN/END 中
如果 WHEN 中条件成立,则执行 THEN 然后结束,如果都不满足,执行 ELSE。ELSE 可以省略。如果没有 ELSE 并且 WHEN 都不满足,返回 NULL
1 2 3 4 5 6 7 8 9 10 11 12
-- 存储过程,显示成绩 CREATEPROCEDURE test_case(IN score INT) BEGIN CASE WHEN score >= 90AND score <= 100THENSELECT'A'; WHEN score >=80 THEN SELECT 'B'; WHEN score >=60 THEN SELECT 'C'; ELSE SELECT'D'; ENDCASE; END$
CALL test_case(95)$
if 结构
if 条件1 then 语句1; elseif 条件2 then 语句2; … [else 语句n;] end if;
只能放在 begin/end 中
1 2 3 4 5 6 7 8 9
-- 根据传入成绩返回等级 CREATEFUNCTION test_if(score INT) RETURNSCHAR BEGIN IF score >= 90AND score <=100THENRETURN'A'; ELSEIF score >= 80 THEN RETURN 'B'; ELSEIF score >= 60 THEN RETURN 'C'; ELSE RETURN 'D'; ENDIF; END $
-- 批量插入 admin CREATEPROCEDURE pro_while2(IN insertCount INT) BEGIN DECLARE i INTDEFAULT1; WHILE i<insertCount DO INSERTINTOadmin (username, `password`) VALUES (CONCAT('jjjj', i), '666'); SET i=i+1; ENDWHILE; END $
-- 添加 leave 控制 -- 清空并重置 index TRUNCATETABLEadmin$
CREATEPROCEDURE pro_while3(IN insertCount INT) BEGIN DECLARE i INTDEFAULT1; a: WHILE i<insertCount DO INSERTINTOadmin (username, `password`) VALUES (CONCAT('jjjj', i), '666'); IF i>= 20 THEN LEAVE a; ENDIF; SET i=i+1; ENDWHILE a; END $ call pro_while3(100)$
-- iterate, 只插入偶数次 TRUNCATETABLEadmin$ CREATEPROCEDURE pro_while4(IN insertCount INT) BEGIN DECLARE i INTDEFAULT1; a: WHILE i<insertCount DO SET i=i+1; IF MOD(i, 2) != 0 THEN ITERATE a; ENDIF; INSERTINTOadmin (username, `password`) VALUES (CONCAT('jjjj', i), '666'); ENDWHILE a; END $ call pro_while4(100)$