存储过程
1. 概念
存储过程和函数可以理解为一段SQL语句的集合(一段SQL语句集合PHP方法实现业务逻辑)， 它们被事先编译好并且存储在数据库中。 在Pascal语言中， 是有“过程”和“函数”的区分， 过程可以理解为没有返回值得函数。 不过在C家族语言中， 则没有过程这个概念， 统一为函数。
优点：
1. 第一点优势就是执行速度快。因为我们的每个 SQL 语句都需要经 过编译，然后再运行，但是存储过程都是直接编译好了之后，直接 运行即可。 2. 第二点优势就是减少网络流量。我们传输一个存储过程比我们传 输大量的 SQL 语句的开销要小得多。 3. 第三点优势就是提高系统安全性。因为存储过程可以使用权限控 制，而且参数化的存储过程可以有效地防止 SQL 注入攻击。保证了 其安全性。 4. 第四点优势就是耦合性降低。当我们的表结构发生了调整或变动 之后，我们可以修改相应的存储过程，我们的应用程序在一定程度 上需要改动的地方就较小了。 44 / 123 5. 第五点优势就是重用性强。因为我们写好一个存储过程之后，再 次调用它只需要一个名称即可，也就是”一次编写，随处调用”,而且 使用存储过程也可以让程序的模块化加强。
缺点：
1. 第一个缺点就是移植性差。因为存储过程是和数据库绑定的，如 果我们要更换数据库之类的操作，可能很多地方需要改动。
2. 第二个缺点就是修改不方便。因为对于存储过程而言，我们并不 能特别有效的调试，它的一些 bug 可能发现的更晚一些，增加了应 用的危险性。
3. 第三个缺点就是优势不明显和赘余功能。对于小型 web 应用来说， 如果我们使用语句缓存，发现编译 SQL 的开销并不大，但是使用存 储过程却需要检查权限一类的开销，这些赘余功能也会在一定程度 上拖累性能。
2. 存储过程操作
1. 创建存储过程
CREATE PROCEDURE 存储过程名称(参数列表)
begin
代码； 业务逻辑区域
end；
2. 调用存储过程
call 存储过程名称(参数列表);
3. 删除存储过程
DROP PROCEDURE 存储过程名称;
3. 无参存储过程
1. 创建无参存储过程
delimiter $ create procedure user_procedure() begin select * from user_test; select * from user_view; end$ delimiter ；
2. 使用
call user_procedure;
4. 有参存储过程
1. 存储过程有参数类型这种说法， 它的类型可以取值有三个： in、ot、 inout
(1) in 表示只是用来输入。 (2) out 表示只是用来输出。 (3) inout 可以用来输入，也可以用作输出。
2. 有参存储过程创建
cretae procedure 存储过程名称(参数类型 参数名称 参数数据类型, 参数类型 参数名称 参数数据类型)
begin
SQL语句
end;
delimiter $ create procedure user_procdure(in x int, out y varchar(10)) begin select * from user_test; select username into y from user_test where id = x; end$ delimiter ;
in是输入， 传入参数x
out是输出， select username into y from user_test where id = x; 中， 将id=x的username查询出来赋值给y， 然后可以通过select @y; 查询出y的值
或可以使用set 直接给y赋值 set y = 123；
3. 使用
5. PDO操作存储过程
<?php /** * 数据库DAO -->>> 对数据库进行操作的类 */ class Db { /** * 连接数据的地址 * @var string */ CONST DRIVER_CLASS = 'mysql:port=3306;host=localhost;dbname=srudent_env'; /** * 数据库的用户名 * @var string */ CONST USERNAME = 'root'; /** * 数据库的密码 * @var string */ CONST PASSWORD = 'root'; /** * 数据库连接出错 * @var string|array */ private $error = '没有异常'; /** * 连接数据库驱动 * @var PDO */ private $pdo; public function __construct() { try { // 初始化执行数据库类 // jdbc:mysql://localhost:3306/" $this->pdo = new PDO(self::DRIVER_CLASS, self::USERNAME, self::PASSWORD); $this->pdo->query('SET NAMES UTF8'); $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { // throw new \Exception($e->getMessage(), 500); return $e->getMessage(); } } /** * 读操作 -->> 查询 * @param string $sql 查询sql * @return array 执行结果 */ public function query($sql) { try { $result = $this->pdo->query($sql); $data = []; foreach($result as $key => $value){ $data[] = $value; } return (count($data) <= 1) ? $data[0] : $data ; } catch (PDOException $e) { // throw new \Exception($e->getMessage(), 500); return $e->getMessage(); } } /** * [call description] * @param string $sql 查询的语句 * @param string $select_param 参数 * @return [type] */ public function call($sql, $select_param = null) { $stmt = $this->pdo->prepare($sql); if ($stmt->execute()) { if (isset($select_param)) { return $this->pdo->query($select_param)->fetchAll(); } else { return $this->pdo->fetchAll(); } return true; } else { return false; } } /** * 执行SQL * @param string $sql 查询sql * @return array 执行结果 */ public function execute($sql) { try { return $this->pdo->exec($sql); } catch (PDOException $e) { // throw new \Exception($e->getMessage(), 500); return $e->getMessage(); } } //------------------ //属性get | set 方法 //------------------ /** * 获取系统错信息 */ public function getError() { return $this->error; } public function write($data) { file_put_contents("log.txt",$data."\n",FILE_APPEND); } } $db = new Db;
require_once 'db.php'; $sql = ' create procedure user_procedure(in x int, out y varchar(10)) begin select * from user_test; select username into y from user_test where id = x; end; '; var_dump($db->execute($sql)); echo 'OK';
require_once 'db.php'; $sql = ' call user_procedure(1, @y) '; var_dump($db->call($sql, 'select @y')); echo 'OK';
6. 存储过程中的流程控制语句及其他语法
if 的语法格式为： if 条件表达式 then 语句 [elseif 条件表达式 then 语句] .... [else 语句] end if case 的语法格式 首先是第一种写法： case 表达式 when 值 then 语句 when 值 then 语句 ... [else 语句] end case 然后是第二种写法： case when 表达式 then 语句 when 表达式 then 语句 .... [else 语句] end case loop 循环 语法格式为： [标号:] loop 循环语句 end loop [标号] while while a>100 do 循环语句 End while Repeat //游标 SQL语句1 UNTIL 条件表达式 END Repeat; Loop SQL语句 所有的条件判断和跳出需要自己实现 End loop leave 语句用来从标注的流程构造中退出，它通常和 begin...end 或循环一起使用 leave 标号; 声明语句结束符，可以自定义: DELIMITER [符合] delimiter $$ $$
7. 变量
存储过程中是可以使用变量的， 我们可以通过declare来定义一个局部变量， 该变量的作用域只是begin....end块中
变量的定义必须卸载符合语句的开头， 并且在任何其他语句的前面。 我们可以一次声明多个相同类型的变量， 我们还可以使用default来赋予默认值。
delimiter $
create procedure user_procedure(in x int, out y varchar(10), out z varchar(10))
begin
declare a,b varchar(10) default 1; # 可以一次声明多个相同类型的变量
set y = a; # 可以通过set直接赋值
select username into b from user_test where id = x; # 可以通过select语句赋值
set z = b;
end$
delimiter ;
结果
8. 游标
我们可以在存储过程中使用游标来对结果集进行循环处理
游标的使用步骤基本分为： 声明、打开、 取值、 关闭
declare 游标名 cursor for 查询语句(结果集) // 声明游标 open 游标名 // 打开游标 fetch 游标名 into 变量名 // 循环处理结果集 业务处理 close 游标名 // 关闭游标 declare continue handler for not found
delimiter $$ create procedure exchange(out count int ) begin declare supply_id1 int default 0; declare amount1 int default 0; -- 游标标识 declare blag int default 1; -- 游标 declare order_cursor cursor for select supply_id,amount from order_group; -- not found 这个异常进行处理 declare continue handler for not found set blag = 0; set count = 0; -- 打开游标 open order_cursor; -- 遍历 read_loop: LOOP fetch order_cursor into supply_id1,amount1; if blag = 0 then leave read_loop; end if; if supply_id1 = 1 then set count = count + amount1; end if; end loop read_loop; end; $$ delimiter ; call exchange(@count); select @count;
9. 总结
存储过程和函数的优势是可以将数据的处理放在数据库服务器上 进行，避免将大量的结果集传输给客户端，减少了数据的传输，因 此也减少了宽带和服务器的压力。
但是在数据库服务器上进行大量的运算也会占用服务器的 CPU， 造成数据库服务器的压力。
一般来说是不建议在存储过程中进行大量的复杂的运算的，它们 不是数据库服务器的强项，我们应该把这些操作让应用服务器去处 理。
浙公网安备 33010602011771号