存储过程高级篇
1983tracy
8年前
<h3>参数传入模式</h3> <p>PL/SQL存储过程有三种传参模式in 、out、in out。默认情况下（即不写）为in模式</p> <p>先来看看下面的存储过程：</p> <pre> <code class="language-sql">--in 、out、 in out模式测试 create or replace procedure proc_param_demo(p1 in number,p2 out number,p3 in out number) as begin dbms_output.put_line('测试in 模式存储过程中p1的值为：'||p1);--标准输出 dbms_output.put_line('测试out 模式存储过程中p2的值为：'||p2); dbms_output.put_line('测试in out模式存储过程中p3的值为：'||p3); --p1 :=100; --报错 p2 := 100; --:=PL/SQL标准赋值 p3 :=100; end;</code></pre> <p>调试完错误之后，调用上面的存储过程，这次调用我们不直接把值传递给存储过程的参数。所以需要使用declare关键字来声明变量，然后就可以在执行区域才可以使用这些变量：</p> <pre> <code class="language-sql">declare --声明区域 p1 number; p2 number; p3 number; begin p1 := 10; p2 := 10; p3 := 10; proc_param_demo(p1,p2,p3); dbms_output.put_line('测试in 模式存储过程后p1的值为：'||p1); dbms_output.put_line('测试out 模式存储过程后p2的值为：'||p2); dbms_output.put_line('测试in out模式存储过程后p3的值为：'||p3); end;</code></pre> <p>输出结果如下图所示：</p> <p><img src="https://simg.open-open.com/show/2b7d395c7c273c5b8b2051f8a2642a3d.png"> 通过测试可以得到以下结果：</p> <ul> <li>in模式可以理解为引用传递，它的值被完整的传入到存储过程中，但在执行过程中不能被重新赋值，我们传递的值执行之后不会发生改变。</li> <li>out模式为值传递，它的值在传入到存储过程的时候会默认赋值为null，可以在执行的时候为其赋值，执行之后传递的值也会随之改变</li> <li>in out模式为值传递，它的值被完整的传入到存储过程中，可以在执行的时候为其赋值，执行之后传递的值也会随之改变</li> </ul> <p>总结：可以把in模式看做是向存储过程传递的不想被改变的参数，可以把out模式看做返回值，当执行之后out模式的值就会随着业务逻辑发生改变以供我们使用，而in out模式则很灵活，我们即可以把它当参数传递，也可以当做返回值来使用。</p> <h3>控制语句</h3> <p>流程控制语句语句无处不在，只要有一点编程基础就能够理解，在这里我列举一些在工作中常用的语法</p> <p>IF判断语句</p> <pre> <code class="language-sql">IF 条件 THEN 执行体; END IF;</code></pre> <pre> <code class="language-sql">IF 条件 THEN 执行体; ELSE 执行体; END IF;</code></pre> <pre> <code class="language-sql">IF 条件 THEN 执行体; ELSEIF 条件 THEN 执行体; ELSE 执行体; END IF;</code></pre> <p>CASE选择语句</p> <pre> <code class="language-sql">CASE 变量 WHEN 匹配变量 THEN 执行体; WHEN 匹配变量 THEN 执行体; WHEN 匹配变量 THEN 执行体; ELSE 执行体; END CASE;</code></pre> <p>LOOP循环语句</p> <pre> <code class="language-sql">LOOP 执行体; IF 条件 THEN 执行体; EXIT;--此处为跳出循环 END IF; END LOOP</code></pre> <pre> <code class="language-sql">LOOP 执行体; EXIT WHEN 条件;--跳出循环 对比的有CONTINUE和CONTINUE WHEN语法 END LOOP</code></pre> <pre> <code class="language-sql">FOR i IN 1..3 LOOP -- FOR EACH 语法，遍历1~3并输出 --执行体; DBMS_OUTPUT.PUT_LINE (TO_CHAR(i)); END LOOP;</code></pre> <p>流程控制语句先了解其语法即可，因为它无处不在，我们就可以在实践中慢慢的掌握并精通。</p> <h3>Cursor游标</h3> <p>An explicit cursor names the unnamed work area in which the database stores processing information when it executes a multiple-row query. When you have named the work area, you can access its information, and process the rows of the query individually.</p> <p>翻译：Cursor就是当我们在数据库一块未命名的存储数据的工作空间进行多行查询的时候对其进行命名。当你对一块工作空间命名之后，你就可以获取并处理这些查询的数据</p> <p>总结：可以把Cursor理解为一个有名称的结果集，当需要的时候就可以拿过来用，也可以吧Cursor看成一个实体类，存储了我们定义的数据。</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/05f26f7aedc5ef63b6bdc64ba24db80b.png"></p> <p>接下来我们通过看一个具体的例子了解cursor的具体实现，需求为从emp中查出所有的姓名和其职位输出</p> <pre> <code class="language-sql">create or replace procedure proc_cursor_demo as cursor emp_cursor is --定义cursor select * from emp; --将数据空间指向emp_cursor begin for emp_data in emp_cursor loop --遍历cursor数据输出 dbms_output.put_line(emp_data.ename ||' 的工作为: '||emp_data.job); end loop; end;</code></pre> <p>结果如下</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/2e8ca442cbaba515a21b3968b9a4ac5e.png"></p> <h3>异常</h3> <p style="text-align:center"><img src="https://simg.open-open.com/show/e8bff65541e8628092a31bb9fb2588ed.png"></p> <p>存储过程的异常在工作中非常常用，但具体使用一般很简单，包括自定义异常和系统异常， jojo52013145 总结的很好，如果想深入了解请参考他的文章。</p> <h3>实践</h3> <p>不管学习什么光说不练都是假把式，看到的知识不一定是自己的，只有实践之后才能真正的理解，让我们通过一个例子。比如说我们有这样的一个需求，要把emp表中有奖金的员工提取到另外一个表，并在员工的姓名后添加后缀_V。下面就是实现代码：</p> <pre> <code class="language-sql">create or replace procedure proc_copy_demo (tn in varchar2) as v_count number; --检查是否已经有这个名称的表了 tablename varchar2(20); --表名称 v_emp_copy emp%rowtype; --表结构 v_sql varchar(200); --sql cursor emp_data_cursor is -- 将有emp表中有奖金的人员放到cursor中 select * from emp where comm>0; --过滤有奖金的 begin tablename := tn; -- 定义表名称 select count(*) into v_count from user_objects where object_name = upper(tablename); -- 查询用户表，校验是否已经存在名称为tablename的表 if v_count>0 then -- 表存在 execute immediate 'drop table '|| tablename ||' cascade constraints'; -- 执行删除表 execute immediate执行SQL语句 end if; execute immediate 'create table '|| tablename ||' as select * from emp where 1=2'; -- 新建表结果和emp相同的表，添加条件后只创建表，不添加数据 for emp_data in emp_data_cursor loop -- 从cursor中取出数据，做一些处理并插入到指定表中 v_emp_copy.empno := emp_data.empno; v_emp_copy.ename := emp_data.ename || '_A'; v_emp_copy.job := emp_data.job; v_emp_copy.mgr := emp_data.mgr; v_emp_copy.hiredate := emp_data.hiredate; v_emp_copy.sal := emp_data.sal; v_emp_copy.comm := emp_data.comm; v_emp_copy.deptno := emp_data.deptno; v_sql := 'insert into '||tablename||' values (' ||v_emp_copy.empno ||',''' ||v_emp_copy.ename ||''',''' ||v_emp_copy.job ||''',' ||v_emp_copy.mgr ||',''' ||v_emp_copy.hiredate ||''',' ||v_emp_copy.sal ||',' ||v_emp_copy.comm ||',' ||v_emp_copy.deptno ||')'; dbms_output.put_line(v_sql); execute immediate v_sql; end loop; commit; exception -- 捕获异常常用写法 when others then dbms_output.put_line('捕获的异常代码（SQLCODE） 为 ' ||SQLCODE ); -- 输出异常信息 dbms_output.put_line('捕获的异常信息（SQLERRM） 为 ' ||SQLERRM); end; -------------------------test------------------------------- begin proc_copy_demo('emp_copy'); end; -------------------------test-------------------------------</code></pre> <p>运行之后可能会出现下面的输出，PL/SQL抛出了一个异常，通过异常捕获机制，我们可以轻松的找到问题所在。</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/9f5027a236979735baeeb296fc258fbb.png"></p> <p>因为我们没有为scott用户赋予创建标的权限，具体代码如下：</p> <pre> <code class="language-sql">revoke create table from scott -- 收回scott创建表的权限 grant create table to scott -- 赋予scott创建表的权限</code></pre> <p>然后再执行上面的存储过程，我们会发现想要的数据已经被提取到emp_copy表中了。</p> <p> </p> <p>来自：http://www.cnblogs.com/Singleorb/p/6231364.html</p> <p> </p>