存储过程入门

1、为什么学习存储过程？

（1）通过把处理封装在容易使用的单元中，简化复杂的操作；

（2）由于不要求反复建立一系列处理步骤，这保证了数据的完整性，如果所有的开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的；

（3）简化对变动的管理。如果表名、列名或业务逻辑有变化，只需要更改存储过程的代码，使用它的开发人员甚至不需要知道这些变化，也就是具备了安全性；

（4）提高了性能，因为使用存储过程比单独使用SQL语句要快；（我们都知道SQL是先编译再执行的，而存储过程是预编译在服务器中的，当执行的时候跳过编译的环节效率自然会提高）

（5）存储过程可用来编写功能更灵活的代码。

因此，存储过程的具备三个特性:简单可复用、安全以及高性能；

 

示例：

DELIMITER //

DROP PROCEDURE IF EXISTS `p_insert_camera`;

-- 存储过程头部区域开始

CREATE PROCEDURE

`p_insert_camera`    --存储过程名称

(

IN `c_no` int, IN `c_start_time` varchar(20),IN `c_device` varchar(32), IN `c_url` varchar(100),OUT `camera_count` int 

)    -- parameter_declaration 声明参数（注意不需要写长度），存储过程头部区域结束

--声明区域，不需要声明变量可以不写

BEGIN -- PL/SQL标准执行语句

    --执行区域

    INSERT INTO r_camera (`no`, `start_time`, `device`, `url`) VALUES(c_no, c_start_time, c_device, c_url);

    select count(*) into camera_count from r_camera;

END

//

DELIMITER ;

头部区域：

    用于编写最基本的存储过程头部标记，定义是否要创建一个替代原有存储过程的存储过程；决定是否定义参数；定义参数的类型（in out inout）；定义执行权限（Schema）。

声明区域：

    用于声明变量（要定义长度）包括cursor；

执行区域：

    用于执行业务逻辑代码，可以使用条件语句（选择、判断、循环。。。）来进行一些业务逻辑CRUD的处理

 

2、存储过程的参数

1. IN类型，表示传递给存储过程；

2. OUT 类型，表示存储过程返回的结果，在调用存储过程时需要传入@开始的变量；

3. INOUT类型，表示在存储过程中可以传入和传出； 

PS:  由于存储过程中每个SQL语句中用；作为分隔符，会和单个SQL造成冲突，因此可使用DELIMITER重新定义分类符，如该例子中定义//为分隔符，自然存储过程结尾就用END //结尾，而不再是END。同时，分隔符//成对出现后，恢复到默认的";"作为分隔符；

 

3、存储过程的调用，查询，删除

1）调用

CALL p_insert_camera('123', '2019-09-16 10:39:21', 'device01', 'url_xxxxx', @total);

SELECT @camera_count;

2）查询

SHOW CREATE PROCEDURE p_insert_camera;

-- 查询所有存储过程的状态，如果在定义存储过程中使用COMMENT添加注释，可以查看。同时可以LIKE进行过滤结果。

如SHOW PROCEDURE STATUS LIKE '%camera%';

3）删除

DROP PROCEDUREp_insert_camera;

 

4、控制语句，循环语句

1）if-then、if-then-else、if-then-elseif

IF 条件 THEN

  -- 执行体;

END IF;


IF 条件 THEN

  -- 执行体;

ELSE

  -- 执行体;

END IF;

IF 条件 THEN

  --执行体;

ELSEIF 条件 THEN

  -- 执行体;

ELSE

  -- 执行体;

END IF;

2)  case
 

CASE 变量

  WHEN 匹配变量  THEN   执行体;

  WHEN 匹配变量  THEN   执行体;

  WHEN 匹配变量  THEN   执行体;

  ELSE 执行体;
END CASE;

 

3)  loop循环

LOOP

  执行体;


  IF 条件 THEN

    continue;

  END IF; 

  --这里还可以用

  CONTINUE WHEN 条件;


  EXIT WHEN 条件;  --跳出循环 对比的有CONTINUE和CONTINUE WHEN语法

END LOOP;

3）while循环

WHILE 条件 LOOP

  执行体;

END LOOP;

4） for循环

FOR i IN 1..3 LOOP -- FOR EACH 语法，遍历1~3并输出

    执行体;

    如：DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));

END LOOP;

 

 

详细还可以参考官方文档：https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/controlstructures.htm#LNPLS004