oracle储存过程 oracle储存过程

一，is 和 as

在Oracle的存储过程和函数中，其实IS和AS是同义词，没有什么区别。
还有在自定义类型（TPYE）和包（PACKAGE）时，使用IS和AS也并没有什么区别。
但是在创建视图（VIEW）时，只能使用AS而不能使用IS。
在声明游标（CURSOR）时，只能使用IS而不能使用AS。

二，cursor

存储过程中查询语句如何返回多行结果？
我们知道，如果存储过程中查询语句有多行结果输出，会报错：ORA-01422: exact fetch returns more than requested number of rows
若想让存储过程中的查询语句返回多行结果不报错，则需要使用游标来实现。
本例主要也是用来熟悉存储过程中游标的简单使用方法。案例所涉及的数据表使用的是oracle自带的scott用户。

1.打开输出选项

SET SERVEROUTPUT ON;

2.创建查询员工薪水的存储过程

create or replace procedure proc_salary is --定义变量 v_empno emp.empno%TYPE; v_ename emp.ename%TYPE; v_sal emp.sal%TYPE; --定义游标 CURSOR emp_cursor IS SELECT empno, ename, sal from emp; BEGIN--循环开始 LOOPIF NOT emp_cursor%ISOPENTHEN OPEN emp_cursor; END IF; FETCH emp_cursor INTOv_empno, v_ename, v_sal; --退出循环的条件 EXIT WHEN emp_cursor%NOTFOUND OR emp_cursor%NOTFOUND IS NULL; dbms_output.put_line('员工编号为' || v_empno || '的' || v_ename || '薪水为:' || v_sal); END LOOP; END;

3.执行存储过程

exec proc_salary;

SQL> exec proc_salary; 员工编号为7369的SMITH薪水为:800 员工编号为7499的ALLEN薪水为:1600 员工编号为7521的WARD薪水为:1250 员工编号为7566的JONES薪水为:2975 员工编号为7654的MARTIN薪水为:1250 员工编号为7698的BLAKE薪水为:2850 员工编号为7782的CLARK薪水为:2450 员工编号为7788的SCOTT薪水为:3000 员工编号为7839的KING薪水为:5000 员工编号为7844的TURNER薪水为:1500 员工编号为7876的ADAMS薪水为:1100 员工编号为7900的JAMES薪水为:950 员工编号为7902的FORD薪水为:3000 员工编号为7934的MILLER薪水为:1300PL/SQL procedure successfully completed.Elapsed: 00:00:00.00

三，minus (“减法”，“减去”的意思)

在程序设计的过程中，往往会遇到两个记录集的比较。如华东电网PMS接口中实现传递一天中变更(新增、修改、删除)的数据。实现的方式有多种，如编程存储过程返回游标，在存储过程中对两批数据进行比较等等。

本文主要讨论利用ORACLE的MINUS函数，直接实现两个记录集的比较。
2 实现步骤

假设两个记录集分别以表的方式存在，原始表为A，产生的比较表为B。

2.1 判断原始表和比较表的增量差异

利用MINUS函数，判断原始表与比较表的增量差异。
此增量数据包含两部分：
1)原始表A有、比较表B没有;
2)原始表A和比较表B都有，但是某些字段发生了改变。

2.2 判断比较表与原始表的增量差异

利用MINUS函数，判断比较表与原始表的增量差异。
此增量数据包含两部分：
1)比较表B有、原始表A没有;
2)比较表B和原始表A都有，但是某些字段发生了改变。

2.3 得出结果集

利用SQL语句中的对两种增量差异的处理，实现判别出比较表相对于原始表是进行了“插入”、“修改”、“删除”的情况。

3 实例演练
3.1创建表并插入数据

Create table A(A1 number(12),A2 varchar2(50)); Create table B(B1 number(12),B2 varchar2(50)); Insert Into A Values (1,'a'); Insert Into A Values (2,'ba'); Insert Into A Values (3,'ca'); Insert Into A Values (4,'da'); Insert Into B Values (1,'a'); Insert Into B Values (2,'bba'); Insert Into B Values (3,'ca'); Insert Into B Values (5,'dda'); Insert Into B Values (6,'Eda'); COMMIT;

3.2进行增量差异数据比较
3.2.1原始表A与比较表B的增量差异

Select * from A minus select * from B;

结果如下：

A1A2 --------------------------------------------------------------- 2ba 4da

3.2.2比较表B与原始表A的增量差异

Select * from B minus select * from A;

结果如下：

B1B2 --------------------------------------------------------------- 2bba 5dda 6Eda

3.2.3两种增量差异的合集

此合集包含3类数据：
--1、原始表A存在、比较表B不存在，属于删除类数据，出现次数1
--2、原始表A不存在、比较表B存在，属于新增类数据，出现次数1
--3、原始表A和比较表B都存在，属于修改类数据，出现次数2

Select A1,A2,1 t from (Select * from A minus select * from B) union Select B1,B2,2 t from (Select * from B minus select * from A);

结果如下：

A1A2T ------------- -------------------------------------------------- ---------- 2ba1 2bba2 4da1 5dda2 6Eda2

3.3得到结果

Select A1,sum(t) from (Select A1,A2,1 t from (Select * from A minus select * from B) union Select B1,B2,2 t from (Select * from B minus select * from A)) Group by A1;

结果如下：

A1SUM(T) ----------------------- 62 23 41 52

结果中SUM(T)为1的为“删除”的数据，SUM(T)为2的为“新增”的数据，SUM(T)为3的为“修改”的数据。
4 分析
4.1 效率分析

序号
数据库配置
Oracle版本
原表数据量
比较表数据
字段列数
耗时
1
Cpu:2.5GHz/内存:2048M
9i
928335
3608159
19
171.594s
2
Cpu:2.5GHz/内存:2048M
9i
928335
3608159
10
121.469s
3
Cpu:2.5GHz/内存:2048M
9i
928335
3608159
5
68.938s
4
Cpu:2.5GHz/内存:2048M
9i
49933
928335
19
33s
5
Cpu:2.5GHz/内存:2048M
9i
49933
928335
10
25.968s
6
Cpu:2.5GHz/内存:2048M
9i
49933
928335
5
11.484s
7
16cpu:3.5GHz/内存:64G
10g
575283
575283
11
13.812s
8
16cpu:3.5GHz/内存:64G
10g
109987
109987
40
2.17s

4.2实现分析

在两个结果集比较的过程中，减少原始表和比较表比较的字段数目以及原始表和比较表的数据量都可以提高效率。

5 总结

此比较方法在执行效率上，可能不是非常好，但是能解决效率要求并不太高的问题。在实现上利用了Oracle的minus函数，此文在于引起大家对于Oracle函数的认识。

四，For 循环

For ... in ... LOOP--执行语句end LOOP;

(1)循环遍历游标

create or replace procedure test() asCursor cursor is select name from student; name varchar(20); beginfor name in cursor LOOPbegindbms_output.putline(name); end; end LOOP; end test;

【oracle储存过程】(2)循环遍历数组

create or replace procedure test(varArray in myPackage.TestArray) as--(输入参数varArray 是自定义的数组类型，定义方式见标题6)i number; begini := 1; --存储过程数组是起始位置是从1开始的，与java、C、C++等语言不同。因为在Oracle中本是没有数组的概念的，数组其实就是一张--表(Table),每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历for i in 1..varArray.count LOOPdbms_output.putline(' The No.' || i || ' record in varArray is:' ||varArray(i)); end LOOP; end test;