目录
1. 函数
1.1. 查看方法
-
基本查看方法
查询数据库中的存储过程和函数 select `name` from mysql.proc where db = 'xx' and `type` = 'PROCEDURE' //存储过程 select `name` from mysql.proc where db = 'xx' and `type` = 'FUNCTION' //函数 show procedure status; //存储过程 show function status; //函数 \G 查看存储过程或函数的创建代码 show create procedure proc_name; show create function func_name; 查看视图 SELECT * from information_schema.VIEWS //视图 SELECT * from information_schema.TABLES //表 查看触发器 SHOW TRIGGERS [FROM db_name] [LIKE expr] SELECT * FROM triggers T WHERE trigger_name=”mytrigger” \G
1.2. 函数定义
-
定义
DELIMITER $$ DROP FUNCTION IF EXISTS genPerson$$ CREATE FUNCTION genPerson(name varchar(20)) RETURNS varchar(50) BEGIN DECLARE str VARCHAR(50) DEFAULT ''; SET @tableName=name; SET str=CONCAT('create table ', @tableName,'(id int, name varchar(20));'); return str; END $$ DELIMITER ; -- 解释: （1）DELIMITER $$ 定义结束符。MySQL默认的结束符是分号，但是函数体中可能用到分号。为了避免冲突，需要另外定义结束符。 （2）DROP FUNCTION IF EXISTS genPerson$$ 如果函数genPerson已经存在了，就删除掉。 （3）CREATE FUNCTION 创建函数genPerson，函数的参数是name，返回值是varchar(50)。 （4）函数体放在BEGIN 与 END之间。 （5）DECLARE 声明变量，str类型是varchar(50)，默认值是空。 （6）CONCAT连接多个字符串。 （7）RETURN 返回拼接后的字符串str。 -
函数执行
select genPerson('student');
2. 存储过程
2.1. 概述
-
概念
- 为以后的使用而保存的一条或多条MySQL语句的集合；
- 可以将其视为：批文件；
- 存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。
- 存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
-
优点
- 存储过程可封装，并隐藏复杂的商业逻辑。
- 存储过程可以回传值，并可以接受参数。
- 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
- 存储过程可以用在数据检验，强制实行商业逻辑等。
-
缺点
- 仅适用于特定的数据库，不具有广泛的适用性；
- 存储过程的性能调校与撰写，受限于各种数据库系统。
参考：
2.2. 创建与调用
- 语法
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
routine_body:
Valid SQL routine statement
[begin_label:] BEGIN
[statement_list]
……
END [end_label]
--调用
call sp_name[(传参)];
- 解释
2.3. 存储过程参数
-
输入 in
-
输出 out
-
输入输出 inout
注意：
- 如果过程没有参数，也必须在过程名后面写上小括号例：
CREATE PROCEDURE sp_name ([proc_parameter[,...]]) …… - 确保参数的名字不等于列的名字，否则在过程体中，参数名被当做列名来处理：
- 建议：
- 输入值使用in参数。
- 返回值使用out参数。
- inout参数就尽量的少用。
2.4. 变量
-
变量定义
## 语法 DECLARE variable_name [,variable_name...] datatype [DEFAULT value]; datatype： MySQL的数据类型； varchar等； ## 示例 DECLARE l_int int unsigned default 4000000; DECLARE l_numeric number(8,2) DEFAULT 9.95; DECLARE l_date date DEFAULT '1999-12-31'; DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59'; DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded'; -
变量赋值
SET 变量名 = 表达式值 [,variable_name = expression ...] -
用户变量
---- 存储过程中 使用用户变量 mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World'); mysql > SET @greeting='Hello'; mysql > CALL GreetWorld( ); +----------------------------+ | CONCAT(@greeting,' World') | +----------------------------+ | Hello World | +----------------------------+ ---- 存储过程间传递全局范围的用户变量 mysql> CREATE PROCEDURE p1() SET @last_procedure='p1'; mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure); mysql> CALL p1( ); mysql> CALL p2( ); +-----------------------------------------------+ | CONCAT('Last procedure was ',@last_proc | +-----------------------------------------------+ | Last procedure was p1 | +-----------------------------------------------+
注意：
- 用户变量 一般以 @ 开头；
- 滥用用户变量会导致 程序难以理解及管理；
2.5. 存储过程 使用
## 调用
call sp_name[(传参)];
## 存储过程的查询
select name from mysql.proc where db='数据库名';
select routine_name from information_schema.routines where routine_schema='数据库名';
show procedure status where db='数据库名';
SHOW CREATE PROCEDURE 数据库.存储过程名; ----详细的存储过程；
## 存储过程的修改
ALTER PROCEDURE;
## 存储过程的删除
DROP PROCEDURE;
2.6. 存储过程的控制语句
-
变量作用域
- 内部的变量在其作用域范围内享有更高的优先权；
- 内部变量的作用域在 begin 与 end 之间；
- 内部变量的值 可以通过 out参数或会话变量 来保存其值；
-
条件语句
- if–then–else–endif;
mysql > DELIMITER // mysql > CREATE PROCEDURE proc2(IN parameter int) -> begin -> declare var int; -> set var=parameter+1; -> if var=0 then -> insert into t values(17); -> end if; -> if parameter=0 then -> update t set s1=s1+1; -> else -> update t set s1=s1+2; -> end if; -> end; -> // mysql > DELIMITER ; - case 语句
-> case var -> when 0 then -> insert into t values(17); -> when 1 then -> insert into t values(18); -> else -> insert into t values(19); -> end case;
- if–then–else–endif;
-
循环语句
-
while–end while
## 语法 while 条件 do --循环体 end while ## mysql > DELIMITER // mysql > CREATE PROCEDURE proc4() -> begin -> declare var int; -> set var=0; -> while var<6 do -> insert into t values(var); -> set var=var+1; -> end while; -> end; -> // mysql > DELIMITER; -
repeat…end repeat
语法：
repeat
–循环体
until 循环条件
end repeat；## 语法： repeat --循环体 until 循环条件 end repeat； ## 示例 mysql > DELIMITER // mysql > CREATE PROCEDURE proc5 () -> begin -> declare v int; -> set v=0; -> repeat -> insert into t values(v); -> set v=v+1; -> until v>=5 -> end repeat; -> end; -> // mysql > DELIMITER ; -
loop … endloop
既不需要初始条件，也不需要结束条件；## 语法 leave 是离开循环； ## 示例 mysql > DELIMITER // mysql > CREATE PROCEDURE proc6 () -> begin -> declare v int; -> set v=0; -> LOOP_LABLE:loop ---- -> insert into t values(v); -> set v=v+1; -> if v >=5 then -> leave LOOP_LABLE; ----离开 -> end if; -> end loop; -> end; -> // mysql > DELIMITER ;
注意：
- 标号可以用在 begin repeat while 或者 loop 语句前，语句标号只能在合法的语句前面使用。
- 可以跳出循环，使运行指令达到复合语句的最后一步。
-
-
ITERATE迭代
ITERATE 通过引用复合语句的标号,来从新开始复合语句:mysql > DELIMITER // mysql > CREATE PROCEDURE proc10 () -> begin -> declare v int; -> set v=0; -> LOOP_LABLE:loop -------- -> if v=3 then -> set v=v+1; -> ITERATE LOOP_LABLE; --------- -> end if; -> insert into t values(v); -> set v=v+1; -> if v>=5 then -> leave LOOP_LABLE; -------- -> end if; -> end loop; -> end; -> // mysql > DELIMITER ;
1277
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
