oracle存储过程

目录

         一.什么是存储过程

二.为什么要写存储过程

三.存储过程基础

1.存储过程结构

2.存储过程语法

3.pl/sql处理存储过程

四.存储过程进阶

1.BUIK COLLECT

2.FORALL

3.pl/sql调试存储过程

4.案例实战

附.参考资料

一.什么是存储过程

存储过程，百度百科上是这样解释的，存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。

简单的说就是专门干一件事一段sql语句。

可以由数据库自己去调用，也可以由java程序去调用。

在oracle数据库中存储过程是procedure。

二.为什么要写存储过程

1.效率高

  存储过程编译一次后，就会存到数据库，每次调用时都直接执行。而普通的sql语句我们要保存到其他地方（例如：记事本  上），都要先分析编译才会执行。所以想对而言存储过程效率更高。

2.降低网络流量

存储过程编译好会放在数据库，我们在远程调用时，不会传输大量的字符串类型的sql语句。

3.复用性高

存储过程往往是针对一个特定的功能编写的，当再需要完成这个特定的功能时，可以再次调用该存储过程。

4.可维护性高

当功能要求发生小的变化时，修改之前的存储过程比较容易，花费精力少。

5.安全性高

完成某个特定功能的存储过程一般只有特定的用户可以使用，具有使用身份限制，更安全。

三.存储过程基础

1.存储过程结构

(1).基本结构

Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常（可写可不写，要增强脚本的容错性和调试的方便性那就写上异常处理）

(2).无参存储过程

CREATE OR REPLACE PROCEDURE demo AS/IS
	变量2 DATE;
	变量3 NUMBER;
BEGIN
	--要处理的业务逻辑
	EXCEPTION    --存储过程异常
END 


这里的as和is一样任选一个，在这里没有区别，其中demo是存储过程名称。

(3).有参存储过程

a.带参数的存储过程

CREATE OR REPLACE PROCEDURE 存储过程名称(param1 student.id%TYPE)
AS/IS
name student.name%TYPE;
age number :=20;
BEGIN
  --业务处理.....
END

上面脚本中，

第1行：param1 是参数，类型和student表id字段的类型一样。

第3行：声明变量name，类型是student表name字段的类型（同上）。

第4行：声明变量age，类型数数字，初始化为20

 

b.带参数的存储过程并且进行赋值

CREATE OR REPLACE PROCEDURE 存储过程名称(
       s_no in varchar,
       s_name out varchar,
       s_age number) AS
total NUMBER := 0;
BEGIN
  SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;
  dbms_output.put_line('符合该年龄的学生有'||total||'人');
  EXCEPTION
    WHEN too_many_rows THEN 
    DBMS_OUTPUT.PUT_LINE('返回值多于1行'); 
END

上面脚本中：

其中参数IN表示输入参数，是参数的默认模式。

OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。

OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程

IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去

第7行：查询语句，把参数s_age作为过滤条件，INTO关键字，把查到的结果赋给total变量。

第8行：输出查询结果，在数据库中“||”用来连接字符串

第9—11行：做异常处理

2.存储过程语法

(1).运算符

这里s，m，n是变量，类型是number；


			
分类
			
			

			
运算符
			
			

			
含义
			
			

			
示例表达式
			
		

			
 

			
 

			
 

			
算术运算符
			
			

			
+
			
			

			
加
			
			

			
s := 2 + 2;
			
		

			
-
			
			

			
减
			
			

			
s := 3 – 1;
			
		

			
*
			
			

			
乘
			
			

			
s := 2 * 3;
			
		

			
/
			
			

			
除
			
			

			
s := 6 / 2;
			
		

			
mod(,)
			
			

			
取模，取余
			
			

			
m : = mod(3,2)
			
		

			
**
			
			

			
乘方
			
			

			
10**2 =100
			
		

			
 

			
 

			
 

			
关系运算符
			
			

			
=
			
			

			
等于 
			
			

			
s = 2
			
		

			
<>或!=或~=
			
			

			
不等于
			
			

			
s != 2
			
		

			
<
			
			

			
小于
			
			

			
s < 3
			
		

			
>
			
			

			
大于
			
			

			
s > 0
			
		

			
<=
			
			

			
小于等于
			
			

			
s <= 9
			
		

			
>=
			
			

			
大于等于
			
			

			
s >= 1
			
		

			
 

			
 

			
比较运算符
			
			

			
LIKE
			
			

			
满足匹配为true
			
			

			
‘li’ like ‘%i’返回true
			
		

			
BETWEEN
			
			

			
是否处于一个范围中
			
			

			
2 between 1 and 3 返回true
			
		

			
IN
			
			

			
是否处于一个集合中
			
			

			
‘x’ in (‘x’,’y’) 返回true
			
		

			
IS NULL
			
			

			
判断变量是否为空
			
			

			
若：n:=3,n is null,返回false
			
		

			
 

			
逻辑运算符
			
			

			
AND
			
			

			
逻辑与
			
			

			
s=3 and c is null
			
		

			
OR
			
			

			
逻辑或
			
			

			
s=3 or c is null
			
		

			
NOT
			
			

			
逻辑非
			
			

			
not c is null
			
		

			
 

			
其他
			
			

			
:=
			
			

			
赋值
			
			

			
s := 0;
			
		

			
..
			
			

			
范围
			
			

			
1..9，即1至9范围
			
		

			
||
			
			

			
字符串连接
			
			

			
‘hello’||’world’
			
		
 

(2).SELECT INTO STATEMENT语句

该语句将select到的结果赋值给一个或多个变量，例如：

CREATE OR REPLACE PROCEDURE DEMO_CDD1 IS
s_name VARCHAR2;   --学生名称
s_age NUMBER;      --学生年龄
s_address VARCHAR2; --学生籍贯
BEGIN
  --给单个变量赋值
  SELECT student_address INTO s_address
  FROM student where student_grade=100;
   --给多个变量赋值
  SELECT student_name,student_age INTO s_name,s_age
  FROM student where student_grade=100;
  --输出成绩为100分的那个学生信息
  dbms_output.put_line('姓名：'||s_name||',年龄：'||s_age||',籍贯：'||s_address);
END

上面脚本中：

存储过程名称：DEMO_CDD1, student是学生表，要求查出成绩为100分的那个学生的姓名，年龄，籍贯

(3).选择语句

a.IF..END IF

学生表的sex字段：1-男生；0-女生

IF s_sex=1 THEN
  dbms_output.put_line('这个学生是男生');
END IF

b.IF..ELSE..END IF

IF s_sex=1 THEN
  dbms_output.put_line('这个学生是男生');
ELSE
  dbms_output.put_line('这个学生是女生');
END IF

(4).循环语句

a.基本循环

LOOP
  IF 表达式 THEN
    EXIT;
  END IF
END LOOP;

b.while循环

WHILE 表达式 LOOP
  dbms_output.put_line('haha');
END LOOP;

c.for循环

FOR a in 10 .. 20 LOOP
  dbms_output.put_line('value of a: ' || a);
END LOOP;

(5).游标

    Oracle会创建一个存储区域，被称为上下文区域，用于处理SQL语句，其中包含需要处理的语句，例如所有的信息，行数处理，等等。

    游标是指向这一上下文的区域。 PL/SQL通过控制光标在上下文区域。游标持有的行(一个或多个)由SQL语句返回。行集合光标保持的被称为活动集合。

a.下表是常用的游标属性：


			
属性
			
			

			
描述
			
		

			
%FOUND
			
			

			
如果DML语句执行后影响有数据被更新或DQL查到了结果，返回true。否则，返回false。
			
		

			
%NOTFOUND
			
			

			
如果DML语句执行后影响有数据被更新或DQL查到了结果，返回false。否则，返回true。
			
		

			
%ISOPEN
			
			

			
游标打开时返回true，反之，返回false。
			
		

			
%ROWCOUNT
			
			

			
返回DML执行后影响的行数。
			
		
b.使用游标

声明游标定义游标的名称和相关的SELECT语句：

CURSOR cur_cdd IS SELECT s_id, s_name FROM student;

打开游标游标分配内存，使得它准备取的SQL语句转换成它返回的行：

OPEN cur_cdd;

抓取游标中的数据，可用LIMIT关键字来限制条数，如果没有默认每次抓取一条：

FETCH cur_cdd INTO id, name ;

关闭游标来释放分配的内存:

CLOSE cur_cdd;

3.pl/sql处理存储过程

(1).新建存储过程：右键procedures，点击new，弹出PROCEDURE框，再点击OK，如下图：



 

(2).在下面的编辑区，编写存储过程脚本



 

(3).在这里我们编写一个demo_cdd存储过程，要求输出“hello world”，如下图：



 

(4).右键刚才新建的存储过程名称，点击“Test”，在点击执行按钮



 

4.案例实战

场景：

有表student(s_no, s_name, s_age, s_grade)，其中s_no-学号，也是主键，是从1开始向上排的（例如：第一个学生学号是1，第二个是2，一次类推）；s_name-学生姓名；s_age-学生年龄；s_grade-年级；这张表的数据量有几千万甚至上亿。一个学年结束了，我要让这些学生全部升一年级，即，让s_grade字段加1。

这条sql，写出来如下：

update student set s_grade=s_grade+1

分析：

如果我们直接运行运行这条sql，因数据量太大会把数据库undo表空间撑爆，从而发生异常。那我们来写个存储过程，进行批量更新，我们每10万条提交一次。

CREATE OR REPLACE PROCEDURE process_student is
total NUMBER := 0;
i NUMBER := 0;
BEGIN
  SELECT COUNT(1) INTO total FROM student;
  WHILE i<=total LOOP
    UPDATE student SET grade=grade+1 WHERE s_no=i;
    i := i + 1;
    IF i >= 100000 THEN
      COMMIT;
    END IF;
  END LOOP;
  dbms_output.put_line('finished!');
END;


四.存储过程进阶

       在上面的案例中，我们的存储过程处理完所有数据要多长时间呢？事实我没有等到它执行完，在我可接受的时间范围内它没有完成。那么对于处理这种千万级数据量的情况，存储过程是不是束手无策呢？答案是否定的，接下来我们看看其他绝招。

       我们先来分析下执行过程的执行过程：一个存储过程编译后，在一条语句一条语句的执行时，如果遇到pl/sql语句就拿去给pl/sql引擎执行，如果遇到sql语句就送到sql引擎执行，然后把执行结果再返回给pl/sql引擎。遇到一个大数据量的更新，则执行焦点（正在执行的，状态处于ACTIVE）会不断的来回切换。

       Pl/SQL与SQL引擎之间的通信则称之为上下文切换，过多的上下文切换将带来过量的性能负载。最终导致效率降低，处理速度缓慢。

       从Oracle8i开始PL/SQL引入了两个新的数据操纵语句：FORALL、BUIK COLLECT，这些语句大大滴减少了上下文切换次数（一次切换多次执行），同时提高DML性能，因此运用了这些语句的存储过程在处理大量数据时速度简直和飞一样。

1.BUIK COLLECT

    Oracle8i中首次引入了Bulk Collect特性，Bulk Collect会能进行批量检索，会将检索结果结果一次性绑定到一个集合变量中，而不是通过游标cursor一条一条的检索处理。可以在SELECT INTO、FETCH INTO、RETURNING INTO语句中使用BULK COLLECT，接下来我们一起看看这些语句中是如何使用BULK COLLECT的。

(1).SELECT INTO

查出来一个结果集合赋值给一个集合变量。

语法结构是：

SELECT field BULK COLLECT INTO var_conllect FROM table where colStatement;

说明：

       field：要查询的字段，可以是一个或多个（要保证和后面的集合变量要向对应）。

       var_collect：集合变量（联合数组等），用来存放查到的结果。

       table：表名，要查询的表。

       colStatement：后面过滤条件语句。比如s_age < 10;

例子：查出年龄小于10岁的学生姓名赋值给数组arr_name变量

SELECT s_name BULK COLLECT INTO arr_name FROM s_age < 10;

(2).FETCH INTO

从一个集合中抓取一部分数据赋值给一个集合变量。

语法结构如下：

FETCH cur1 BULK COLLECT INTO var_collect [LIMIT rows]

说明：

        cur1：是个数据集合，例如是个游标。

        var_collect：含义同上。

        [LIMIT rows]：可有可无，限制每次抓取的数据量。不写的话，默认每次一条数据。

例子：给年龄小于10岁的学生的年级降一级。

--查询年龄小于10岁的学生的学号放在游标cur_no里
CURSOR cur_no IS 
		SELECT s_no FROM student WHERE s_age < 10;

--声明了一个联合数组类型，元素类型和游标cur_no每个元素的类型一致
TYPE ARR_NO IS VARRAY(10) OF cur_no%ROWTYPE;

--声明一个该数组类型的变量no
no ARR_NO;
BEGIN
  FETCH cur_no BULK COLLECT INTO no LIMIT 100;
  FORALL i IN 1..no.count SAVE EXCEPTONS
	UPDATE student SET s_grade=s_grade-1 WHERE no(i);
END;


说明：先查出年龄小于10岁的学生的学号放在游标里，再每次从游标里拿出100个学号，进行更新，给他们的年级降一级。

(3).RETURNING

BULK COLLECT除了与SELECT，FETCH进行批量绑定之外，还可以与INSERT，DELETE，UPDATE语句结合使用，可以返回这些DML语句执行后所影响的记录内容（某些字段）。

再看一眼学生表的字段情况：student(s_no, s_name, s_age, s_grade)

语法结构如下：

DMLStatement
       RETURNING field BULK COLLECT INTO var_field;

说明：

        DMLStatement：是一个DML语句。

        field：是这个表的某个字段，当然也可以写多个逗号隔开（field1，field2, field3）。

        var_field：一个类型为该字段类型的集合，多个的话用逗号隔开，如下：

        （var_field1, var_field2, var_field3）

 

例子：获取那些因为年龄小于10岁而年级被将一级的学生的姓名集合。

TYPE NAME_COLLECT IS TABLE OF student.s_name%TYPE;
names NAME_COLLECT;
BEGIN
  UPDATE student SET s_grade=s_grade-1 WHERE s_age < 10
  RETURNING s_name BULK COLLECT INTO names;
END;

说明：

       NAME_COLLECT：是一个集合类型，类型是student表的name字段的类型。

       names：定义了一个NAME_COLLECT类型的变量。

(4).注意事项

a.不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。

b.只能在服务器端的程序中使用BULK COLLECT，如果在客户端使用，就会产生一个不支持这个特性的错误。

c.BULK COLLECT INTO 的目标对象必须是集合类型。

d.复合目标(如对象类型)不能在RETURNING INTO 子句中使用。

e.如果有多个隐式的数据类型转换的情况存在，多重复合目标就不能在BULK COLLECT INTO 子句中使用。

f.如果有一个隐式的数据类型转换，复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO 子句中。

2.FORALL

(1).语法

FORALL index IN bounds [SAVE EXCEPTIONS]     
     sqlStatement；

说明：

        index是指下标；

        bounds是一个边界，形式是start..end

        [SAVE EXCEPTIONS] 可写可不写，这个下面介绍；

        sqlStatement是一个DML语句，这里有且仅有一个sql语句；

例子：

--例子1：移除年级是5到10之间的学生
FORALL i IN 5..10
       DELETE FROM student where s_grade=i;

--例子：2，arr是一个数组,存着要升高一年级的学生名称
FORALL s IN 1..arr.count SAVE EXCEPTIONS
       UPDATE student SET s_grade=s_grade+1 WHERE s_name=arr(i);

(2).SAVE EXCEPTIONS

通常情况写我们在执行DML语句时，可能会遇到异常，可能致使某个语句或整个事务回滚。如果我们写FORALL语句时没有用SAVE EXCEPTIONS语句，那么DML语句会在执行到一半的时候停下来。

       如果我们的FORALL语句后使用了SAVE EXCEPTIONS语句，当在执行过程中如果遇到异常，数据处理会继续向下进行，发生的异常信息会保存到SQL%BULK_EXCEPTONS的游标属性中，该游标属性是个记录集合，每条记录有两个字段，例如：（1, 02300）;

       ERROR_INDEX：该字段会存储发生异常的FORALL语句的迭代编号；

       ERROR_CODE：存储对应异常的，oracle错误代码；

SQL%BULK_EXCEPTONS这个异常信息总是存储着最近一次执行的FORALL语句可能发生的异常。而这个异常记录集合异常的个数则由它的COUNT属性表示，即:

       SQL%BULK_EXCEPTONS.COUNT，SQL%BULK_EXCEPTIONS有效的下标索引范围在1到%BULK_EXCEPTIONS.COUNT之间。

(3). INDICES OF

在Oracle数据库10g之前有一个重要的限制，该数据库从IN范围子句中的第一行到最后一行，依次读取集合的内容，如果在该范围内遇到一个未定义的行，Oracle数据库将引发ORA-22160异常事件：ORA-22160: element at index [N] does not exist。针对这一问题，Oracle后续又提供了两个新语句：INDICES OF 和 VALUES OF。

接下来我们来看看这个INDICES OF语句，用于处理稀疏数组或包含有间隙的数组（例如：一个集合的某些元素被删除了）。

该语句语法结构是：

FORALL i INDICES OF collection [SAVE EXCEPTIONS]

       sqlStatement;

说明：

i：集合（嵌套表或联合数组）下标。

collection：是这个集合。

[SAVE EXCEPTIONS]和sqlStatement上面已经解释过。

例子：arr_std是一个联合数组，每个元素包含（name,age,grade）,现在要向student表插入数据。

FORALL i IN INDICES OF arr_stu
       INSERT INTO student VALUES(
           arr_stu(i).name,
              arr_stu(i).age,
              arr_stu(i).grade
       );

(4). VALUES OF

VALUES OF适用情况：绑定数组可以是稀疏数组，也可以不是，但我只想使用该数组中元素的一个子集。VALUES OF选项可以指定FORALL语句中循环计数器的值来自于指定集合中元素的值。但是，VALUES OF在使用时有一些限制：

       如果VALUES OF子句中所使用的集合是联合数组，则必须使用PLS_INTEGER和BINARY_INTEGER进行索引，VALUES OF 子句中所使用的元素必须是PLS_INTEGER或BINARY_INTEGER；

       当VALUES OF 子句所引用的集合为空，则FORALL语句会导致异常；

该语句的语法结构是：

FORALL i IN VALUES OF collection [SAVE EXCEPTIONS]
       sqlStatement;

说明：i和collection含义如上

联合数组请看文章（或自行百度）：https://blog.csdn.net/leshami/article/details/7372061

3.pl/sql调试存储过程

首先，当前这个用户得有能调试存储过程的权限，如果没有的话，以数据库管理员身份给你这个用户授权：

--userName是你要拿到调试存储过程权限的用户名
GRANT DEBUG ANY PROCEDURE,DEBUG CONNECT SESSION TO username;

(1).右键一个存储过程名称，点击测试，如下图：

这里我用的pl/sql是12.0.4版本的，下面截图中与低版本的pl/sql按钮位置都相同，只是图标不一样。



 

(2).点击两次step into按钮，进入语句调试，如下图：



 

(3).每点击一次step into按钮，会想下执行一条语句，也可以查看变量和表达式的值，如下图：



 

查看变量值：在查看变量区域，在Variable列输入变量i，在Value列点击下，该变量的值就显示出来了。

4.案例实战

场景和上面的案例实战是同一个，如下：

有表student(s_no, s_name, s_age, s_grade)，其中s_no-学号，也是主键，是从1开始向上排的（例如：第一个学生学号是1，第二个是2，一次类推）；s_name-学生姓名；s_age-学生年龄；s_grade-年级；这张表的数据量有几千万甚至上亿。一个学年结束了，我要让这些学生全部升一年级，即，让s_grade字段加1。

这条sql，写出来如下：

update student set s_grade=s_grade+1 

编写存储过程：

(1).存储过程1

名称为：process_student1，student表的s_no字段类型为varchar2(16)。

CREATE OR REPLACE PROCEDURE process_student1 AS
    CURSOR CUR_STUDENT IS SELECT s_no FROM student;
    TYPE REC_STUDENT IS VARRAY(100000) OF VARCHAR2(16);
    students REC_STUDENT;
BEGIN
  OPEN CUR_STUDENT;
  WHILE (TRUE) LOOP
    FETCH CUR_STUDENT BULK COLLECT INTO students LIMIT 100000;
    FORALL i IN 1..students.count SAVE EXCEPTIONS
      UPDATE student SET s_grade=s_grade+1 WHERE s_no=students(i);
    COMMIT;
    EXIT WHEN CUR_STUDENT%NOTFOUND OR CUR_STUDENT%NOTFOUND IS NULL;
  END LOO;
  dbms_output.put_line('finished');
END;

说明：

        把student表中要更新的记录的学号拿出来放在游标CUR_STUDENT，每次从这个游标里抓取10万条数据赋值给数组students，每次更新这10万条记录。循环进行直到游标里的数据全部抓取完。

        FETCH .. BULK COLLECT INTO .. LIMIT rows语句中：这个rows我测试目前最大可以为10万条。

(2).存储过程2（ROWID）

       如果我们这个student表没有主键，也没有索引呢，该怎么来做呢？

分析下：

       ROWNUM是伪列，每次获取结果后，然后在结果集里会产生一列，从1开始排，每次都是从1开始排。

        ROWID在每个表中，每条记录的ROWID都是唯一的。在这种情况下，我们可以用ROWID。但要注意的是，ROWID是一个类型，注意它和VARCHAR2之间的转换。有两个方法：ROWIDTOCHAR（）是把ROWID类型转换为CHAR类型；CHARTOROWID()是把CAHR类型转换为ROWID类型。

接下来我们编写存储过程process_student2，脚本如下：

CREATE OR REPLACE PROCEDURE process_student1 AS
    CURSOR CUR_STUDENT IS SELECT ROWIDTOCHAR(ROWID) FROM student;
    TYPE REC_STUDENT IS VARRAY(100000) OF VARCHAR2(16);
    students REC_STUDENT;
BEGIN
  OPEN CUR_STUDENT;
  WHILE (TRUE) LOOP
    FETCH CUR_STUDENT BULK COLLECT INTO students LIMIT 100000;
    FORALL i IN 1..students.count SAVE EXCEPTIONS
      UPDATE student SET s_grade=s_grade+1 WHERE ROWID=CHARTOROWID(students(i));
    COMMIT;
    EXIT WHEN CUR_STUDENT%NOTFOUND OR CUR_STUDENT%NOTFOUND IS NULL;
  END LOO;
  dbms_output.put_line('finished');
END;

说明：

       我们首先查到记录的ROWID并把它转换为CHAR类型，存放到游标CUR_STUDENT里，

再每次抓取10万条数据赋值给数组进行更新，更新语句的WHERE条件时，又把数组元素是CAHR类型的rowid串转换为ROWID类型。

附.参考资料

存储过程基础：

       https://www.yiibai.com/plsql/plsql_basic_syntax.html

存储过程进阶之FORALL：

       https://blog.csdn.net/leshami/article/details/7536926

       https://blog.csdn.net/jie1336950707/article/details/49966753

存储过程进阶之BUIL COLLECT：

       https://blog.csdn.net/leeboy_wang/article/details/7991021

       https://blog.csdn.net/leshami/article/details/7545597

联合数组：

       https://blog.csdn.net/leshami/article/details/7372061

Oracle存储过程详解 procedure

1.创建Oracle存储过程 prodedure

create or replace procedure test(var_name_1 in type,var_name_2 out ty
pe) as
--声明变量(变量名 变量类型)
begin
--存储过程的执行体
end test;
打印出输入的时间信息
create or replace procedure test(workDate in Date) is
begin
dbms_output.putline(The input date is:||to_date(workDate, yyyy-mm-d
d));
end test;

2.变量赋值

create or replace procedure test(workDate in Date) is
x number(4,2);
begin
x := 1;
end test;

3.判断语句

if 比较式 then begin end; end if;

create or replace procedure test(x in number) is
begin
if x >0 then
begin
x := 0 - x;
end;
end if;
if x = 0 then
begin
x: = 1;
end;
end if;
end test;

4.for循环

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

5.while 循环

while 条件语句 LOOP
begin
end;
end LOOP;
E.g
create or replace procedure test(i in number) as
begin
while i < 10 LOOP
begin
i:= i + 1;
end;
end LOOP;
end test;

6.数组

首先明确一个概念： Oracle中本是没有数组的概念的， 数组其实就是一张表(Ta

ble),每个数组元素就是表中的一个记录。

使用数组时，用户可以使用Oracle已经定义好的数组类型，或可根据自己的需

要定义数组类型。

(1)使用Oracle自带的数组类型

x array; --使用时需要需要进行初始化
e.g:
create or replace procedure test(y out array) is
x array;
begin
x := new array();
y := x;
end test;

（2）自定义的数组类型 (自定义数据类型时，建议通过创建Package的方式实现，

以便于管理) (自定义使用参见标题4.2)

 create or replace package myPackage is
-- Public type declarations type info is record( name va
rchar(20), y number);
type TestArray is table of info index by binary_integer; --此处
声明了一个TestArray 的类型数据，其实其为一张存储Info 数据类型的Table
而已，及TestArray 就是一张表，有两个字段，一个是
name， 一个是y。 需要注意的是此处使用了Index by binary_integer 编制该T
able 的索引项，也可以不写，直接写成： type TestArray is
table of info，如果不写的话使用数组时就需要进行初始化： varArray myPac
kage.TestArray; varArray := new myPackage.TestArray();
end TestArray;


7.关于package

 

ORACLE PACKAGE 包 是一组相关过程、函数、变量、常量#SinaEditor_Temp_FontName、类型和游标等PL/SQL程序设计元素的组合。包具有面向对象设计的特点，是对这些PL/SQL程序设计元素的封装。一个包由两个分开的部分组成：  

(1)包package声明或定义：包定义部分是创建包的规范说明，声明包内数据类型、变量、常量、游标等元素。这部分也是为使用者提供了透明的接口。  

(2)包体packpage body:包体是包定义部分的具体实现。  

(3)将有联系的对象打成包,方便使用  

(4)包中对象包括储存过程,函数,游标,自定义类型和变量,可以在PL_SQL块中应用这些对象.  

定义包头

create or replace package <Package_name> is  
  type <TypeName> is <Datatype>;--定义类型  
  -- Public constant declarations  
  <ConstantName> constant <Datatype> := <Value>;--声明常量  
  -- Public variable declarations  
  <VariableName> <Datatype>;  --数据类型  
  -- Public function and procedure declarations  
  function <FunctionName>(<Parameter> <Datatype>) return <Datatype>; --函数  
end <Package_name>;  

定义包体

create or replace package body <Package_name> is  
  -- Private type declarations  
  type <TypeName> is <Datatype>;  
  -- Private constant declarations  
  <ConstantName> constant <Datatype> := <Value>  
  -- Private variable declarations  
  <VariableName> <Datatype>;  
  -- Function and procedure implementations  
  function <FunctionName>(<Parameter> <Datatype>) return <Datatype> is --函数的具体内容  
    <LocalVariable> <Datatype>;  
  begin  
   <Statement>;  
    return(<Result>);  
  end;  
begin  
  -- Initialization--初始化包体,每次调用时被初始化  
  <Statement>;  
end <Package_name>;  

只有当包头编辑成功后才能编辑包体.其中的函数名与过程名须和包头中的函数过程一样.  

 包说明和包体必须有相同的名字  

 包的开始没有begin语句，与存储过程和函数不同。  

 在包的说明部分定义函数和过程的名称和参数，具体实现在包体中定义。  

 在包内声明常量、变量、类型定义、异常、及游标时不使用declare。  

 包内的过程和函数的定义不要create or replace语句。  

 包声明和包体两者分离。  

  包头(Package)与包体(Package body)的应用  

包的作用: 根据出生年月返回年龄function Getage,返回工资function Getsalary  

--创建环境 

Create Table T_PsnSalary  --工资表  
(  
Fpsncode varchar(4) default '',  --个人代码  
Fpsndesc varchar(20) default '',  --描述  
FpsnBirth varchar(20) default '', --生日  
FpsnSalary number(8,2)            --工资  
);  

 --添加数据 

Insert into T_PsnSalary(Fpsncode,Fpsndesc,FpsnBirth,FpsnSalary) Values('C001','张三','1986.01.10',1100);  
Insert into T_PsnSalary(Fpsncode,Fpsndesc,FpsnBirth,FpsnSalary) Values('C002','李四','1980.10.10',3000);  
Insert into T_PsnSalary(Fpsncode,Fpsndesc,FpsnBirth,FpsnSalary) Values('C003','王五','1996.12.10',800);  
commit;  

--创建包头 

create or replace package package_demo is  
 function Getage(birthst varchar,birthend varchar) return integer;  
 function Getsalary(VFpsncode varchar) return number;  
end package_demo;  

--创建包体

create or replace package body package_demo is  
  function Getage(birthst varchar,birthend varchar) return integer ---得到年龄函数  
  is  
    V_birth integer;  
    ToDateEnd Date;  
    Toyear number(4);  
    Tomonth number(4);  
    Fromyear number(4);  
    Frommonth number(4);  
  begin  
    if (birthend='') or (birthend is null) then   
      select sysdate into ToDateEnd from dual;         ---得到系统时间  
    end if;  
    Toyear := to_number(to_char(ToDateEnd,'YYYY'));    ---得到最后年月  
    Tomonth := to_number(to_char(ToDateEnd,'MM'));  
    Fromyear := to_number(substr(birthst,1,4));        ---计算的年月  
    Frommonth := to_number(substr(birthst,6,2));  
    if Tomonth-Frommonth>0 then  
      V_birth:=Toyear-fromyear;  
    else  
      V_birth:=Toyear-fromyear-1;  
    end if;  
    return(V_birth);  
  end Getage;  
  
  function getSalary(VFpsncode varchar) return number  ---返回工资情况  
  is  
    V_psnSalary number(8,2);  
  begin  
    Select FpsnSalary into V_psnSalary from T_PsnSalary where Fpsncode = VFpsncode;  
    return(V_psnSalary);  
  end getSalary;   
end package_demo;  

--包的调用

select a.*,package_demo.Getage(Fpsnbirth,'')age from T_psnsalary a;  --调用包得到年龄功能  
select package_demo.getsalary('C001') from dual;                     --代码得到工资  

8.游标的使用 Oracle 中Cursor是非常有用的， 用于遍历临时表中的查询结果。

其相关方法和属性也很多，现仅就常用的用法做一二介绍

(1)Cursor型游标(不能用于参数传递)
create or replace procedure test() is
cusor_1 Cursor is select std_name from student where ...; --Curso
r 的使用方式1 cursor_2 Cursor;
begin
select class_name into cursor_2 from class where ...; --Cursor的使
用方式2
可使用For x in cursor LOOP .... end LOOP; 来实现对 Cursor的遍历
end test;
(2)SYS_REFCURSOR型游标， 该游标是Oracle以预先定义的游标， 可作出参数进
行传递
create or replace procedure test(rsCursor out SYS_REFCURSOR) is
cursor SYS_REFCURSOR; name varhcar(20);
begin
OPEN cursor FOR select name from student where ... --SYS_REFCURSOR只
能通过OPEN方法来打开和赋值
LOOP
fetch cursor into name --SYS_REFCURSOR只能通过 fetch into来打
开和遍历 exit when cursor%NOTFOUND; --SYS_R
EFCURSOR中可使用三个状态属性：
---%NOTFOUND(未找到记录信息) %FOUND(找到记录信息)
---%ROWCOUNT(然后当前游标所指向的行位置)
dbms_output.putline(name);
end LOOP;
rsCursor := cursor;
end test;

下面写一个简单的例子来对以上所说的存储过程的用法做一个应用：

现假设存在两张表，一张是学生成绩表(studnet)，字段为： stdId,math,artic

le,language,music,sport,total,average,step

一张是学生课外成绩表(out_school),

字段为:stdId,parctice,comment

通过存储过程自动计算出每位学生的总成绩和平均成绩， 同时， 如果学生在课外

课程中获得的评价为 A，就在总成绩上加20分。

create or replace procedure autocomputer(step in number) is
rsCursor SYS_REFCURSOR;
commentArray myPackage.myArray;
math number;
article number;
language number;
music number;
sport number;
total number;
average number;
stdId varchar(30);
record myPackage.stdInfo;
i number;
begin
i := 1;
get_comment(commentArray); --调用名为get_comment()的存储过程获取学生
课外评分信息
OPEN rsCursor for select stdId,math,article,language,music,sport from
student t where t.step = step;
LOOP
fetch rsCursor into stdId,math,article,language,music,sport; exit whe
n rsCursor%NOTFOUND;
total := math + article + language + music + sport;
for i in 1..commentArray.count LOOP
record := commentArray(i);
if stdId = record.stdId then
begin
if record.comment = 'A' then
begin
total := total + 20;
go to next; --使用go to跳出for循环
end;
end if;
end;
end if;
end LOOP;
average := total / 5;
update student t set t.total=total and t.average = average where t.
stdId = stdId;
end LOOP;
end;
end autocomputer;
--取得学生评论信息的存储过程
create or replace procedure get_comment(commentArray out myPackage.my
Array) is
rs SYS_REFCURSOR；
record myPackage.stdInfo;
stdId varchar(30);
comment varchar(1);
i number;
begin
open rs for select stdId,comment from out_school
i := 1;
LOOP
fetch rs into stdId,comment; exit when rs%NOTFOUND;
record.stdId := stdId;
record.comment := comment;
recommentArray(i) := record;
i:=i + 1;
end LOOP;
end get_comment;
--定义数组类型myArray
create or replace package myPackage is begin
type stdInfo is record(stdId varchar(30),comment varchar(1));
type myArray is table of stdInfo index by binary_integer;
end myPackage;

字符函数——返回字符值

这些函数全都接收的是字符族类型的参数(CHR除外)并且返回字符值.

除了特别说明的之外,这些函数大部分返回 VARCHAR2 类型的数值.

字符函数的返回类型所受的限制和基本数据库类型所受的限制是相同的。

字符型变量存储的最大值：

VARCHAR2数值被限制为 2000字符(ORACLE 8 中为4000 字符)

CHAR数值被限制为 255字符(在 ORACLE8中是 2000)

long类型为 2GB

Clob类型为 4GB

1、 CHR

语法: chr(x)

功能： 返回在数据库字符集中与X拥有等价数值的字符。 CHR和ASCII是一对反函数。 经过

CHR转换后的字符再经过ASCII转换又得到了原来的字

符。

使用位置：过程性语句和SQL语句。

2、 CONCAT

语法： CONCAT（ string1,string2）

功能：返回string1，并且在后面连接string2。

使用位置：过程性语句和SQL语句。

3、 INITCAP

语法： INITCAP（ string）

功能：返回字符串的每个单词的第一个字母大写而单词中的其他字母小写的string。单词

是用.空格或给字母数字字符进行分隔。不是字母的

字符不变动。

使用位置：过程性语句和SQL语句。

4、 LTRIM

语法： LTRIM（ string1,string2）

功能：返回删除从左边算起出现在string2中的字符的string1。 String2被缺省设置为单

个的空格。数据库将扫描string1，从最左边开始。当

遇到不在string2中的第一个字符，结果就被返回了。 LTRIM的行为方式与RTRIM很相似。

使用位置：过程性语句和SQL语句。

5、 NLS_INITCAP

语法： NLS_INITCAP（ string[,nlsparams]）

功能： 返回字符串每个单词第一个字母大写而单词中的其他字母小写的string， nlsparams

指定了不同于该会话缺省值的不同排序序列。如果不指定参数，则功能和INITCAP相同。 N

lsparams可以使用的形式是：

‘ NLS_SORT=sort’

这里sort制订了一个语言排序序列。

使用位置：过程性语句和SQL语句。

6、 NLS_LOWER

语法： NLS_LOWER（ string[,nlsparams]）

功能：返回字符串中的所有字母都是小写形式的string。不是字母的字符不变。

Nlsparams参数的形式与用途和NLS_INITCAP中的nlsparams参数是相同的。

如果nlsparams没有被包含，那么NLS_LOWER所作的处理和

LOWER相同。

使用位置；过程性语句和SQL语句。

7、 NLS_UPPER

语法： nls_upper（ string[,nlsparams]）

功能： 返回字符串中的所有字母都是大写的形式的string。 不是字母的字符不变。 nlspara

ms参数的形式与用途和NLS_INITCAP中的相同。如果

没有设定参数，则NLS_UPPER功能和UPPER相同。

使用位置：过程性语句和SQL语句。

8、 REPLACE

语法： REPLACE（ string， search_str[,replace_str]）

功能： 把string中的所有的子字符串search_str用可选的replace_str替换， 如果没有指

定replace_str，所有的string中的子字符串

search_str都将被删除。 REPLACE是TRANSLATE所提供的功能的一个子集。

使用位置：过程性语句和SQL语句。

9、 RPAD

语法： RPAD（ string1,x[,string2]）

功能： 返回在X字符长度的位置上插入一个string2中的字符的string1。 如果string2的

长度要比X字符少，就按照需要进行复制。如果string2

多于X字符， 则仅string1前面的X各字符被使用。 如果没有指定string2， 那么使用空格

进行填充。 X是使用显示长度可以比字符串的实际长度

要长。 RPAD的行为方式与LPAD很相似，除了它是在右边而不是在左边进行填充。

使用位置：过程性语句和SQL语句。

10、 RTRIM

语法: RTRIM（ string1,[,string2]）

功能: 返回删除从右边算起出现在string1中出现的字符string2. string2被缺省设置

为单个的空格.数据库将扫描string1,从右边开始.当遇

到不在string2中的第一个字符,结果就被返回了RTRIM的行为方式与LTRIM很相似.

使用位置：过程性语句和SQL语句。

11、 SOUNDEX

语法: SOUNDEX（ string）

功能: 返回string的声音表示形式.这对于比较两个拼写不同但是发音类似的单词而言

很有帮助.

使用位置：过程性语句和SQL语句。

12、 SUBSTR

语法: SUBSTR（ string,a[,b]）

功能: 返回从字母为值a开始b个字符长的string的一个子字符串.如果a是0,那么它

就被认为从第一个字符开始.如果是正数,返回字符是从左

边向右边进行计算的.如果b是负数,那么返回的字符是从string的末尾开始从右向左进行

计算的.如果b不存在,那么它将缺省的设置为整个字符

串.如果b小于1,那么将返回NULL.如果a或b使用了浮点数,那么该数值将在处理进行以前

首先被却为一个整数.

使用位置：过程性语句和SQL语句。

13、 TRANSLATE

语法: TRANSLATE(string,from_str,to_str)

功能: 返回将所出现的from_str中的每个字符替换为to_str中的相应字符以后的

string. TRANSLATE是REPLACE所提供的功能的一个超集.

如果from_str比to_str长,那么在from_str中而不在to_str中而外的字符将从string

中被删除,因为它们没有相应的替换字符. to_str不能为空

.Oracle把空字符串认为是NULL,并且如果TRANSLATE中的任何参数为NULL,那么结果也是N

ULL.

使用位置：过程性语句和SQL语句。

14、 UPPER

语法: UPPER（ string）

功能: 返回大写的string.不是字母的字符不变.如果string是CHAR数据类型的,那么结果

也是CHAR类型的.如果string是VARCHAR2类型的,那么

结果也是VARCHAR2类型的.

使用位置: 过程性语句和SQL语句。

字符函数——返回数字

这些函数接受字符参数回数字结果.参数可以是CHAR或者是VARCHAR2类型的.尽管实际下许

多结果都是整数值,但是返回结果都是简单的NUMBER

类型的,没有定义任何的精度或刻度范围.

16、 ASCII

语法: ASCII（ string）

功能: 数据库字符集返回string的第一个字节的十进制表示.请注意该函数仍然称作为ASC

II.尽管许多字符集不是7位ASCII.CHR和ASCII是互为

相反的函数.CHR得到给定字符编码的响应字符. ASCII得到给定字符的字符编码.

使用位置: 过程性语句和SQL语句。

17、 INSTR

语法: INSTR（ string1, string2[a,b]）

功能: 得到在string1中包含string2的位置. string1时从左边开始检查的,开始的位

置为a,如果a是一个负数,那么string1是从右边开始进行

扫描的.第b次出现的位置将被返回. a和b都缺省设置为1,这将会返回在string1中第一

次出现string2的位置.如果string2在a和b的规定下没有

找到,那么返回0.位置的计算是相对于string1的开始位置的,不管a和b的取值是多少.

使用位置: 过程性语句和SQL语句。

18、 INSTRB

语法: INSTRB（ string1, string2[a,[b]]）

功能: 和INSTR相同,只是操作的对参数字符使用的位置的是字节.

使用位置: 过程性语句和SQL语句。

19、 LENGTH

语法: LENGTH（ string）

功能: 返回string的字节单位的长度.CHAR数值是填充空格类型的,如果string由数据

类型CHAR,它的结尾的空格都被计算到字符串长度中间.

如果string是NULL,返回结果是NULL,而不是0.

使用位置: 过程性语句和SQL语句。

20、 LENGTHB

语法: LENGTHB（ string）

功能: 返回以字节为单位的string的长度.对于单字节字符集LENGTHB和LENGTH是一样

的.

使用位置: 过程性语句和SQL语句。

21、 NLSSORT

语法: NLSSORT（ string[,nlsparams]）

功能: 得到用于排序string的字符串字节.所有的数值都被转换为字节字符串,这样在不同

数据库之间就保持了一致性. Nlsparams的作用和

NLS_INITCAP中的相同.如果忽略参数,会话使用缺省排序.

9.Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。

（1）无参存储过程语法

create or replace procedure NoParPro  
 as  //声明  
 ;  
 begin // 执行  
 ;  
 exception//存储过程异常  
 ;  
 end;

 

（2）带参存储过程实例

create or replace procedure queryempname(sfindno emp.empno%type)   
as 
   sName emp.ename%type;  
   sjob emp.job%type;  
begin 
       ....  
exception  
       ....  
end;

（3）带参数存储过程含赋值方式

create or replace procedure runbyparmeters    
    (isal in emp.sal%type,   
     sname out varchar,  
     sjob in out varchar)  
 as  
    icount number;  
 begin 
      select count(*) into icount from emp where sal>isal and job=sjob;  
      if icount=1 then 
        ....  
      else 
       ....  
     end if;  
exception  
     when too_many_rows then 
     DBMS_OUTPUT.PUT_LINE('返回值多于1行');  
     when others then 
     DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');  
end;

其中参数IN表示输入参数，是参数的默认模式。

OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。

OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程

IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去。

10.存储过程中游标的定义和使用

准备环境

CREATE TABLE EMP(
 EMPNO NUMBER(4) NOT NULL,  
 ENAME VARCHAR2(10),  
 JOB VARCHAR2(9),  
 MGR NUMBER(4),  
 HIREDATE DATE,  
 SAL NUMBER(7, 2),  
 COMM NUMBER(7, 2),  
 DEPTNO NUMBER(2)
);  

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, SYSDATE, 800, NULL, 20); 
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, SYSDATE, 1600, 300, 30); 
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN',7698, SYSDATE, 1250, 500, 30); 
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839,SYSDATE, 2975, NULL, 20); 
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, SYSDATE, 1250, 1400, 30); 
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, SYSDATE, 2850, NULL, 30); 
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, SYSDATE, 2450, NULL, 10); 
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, SYSDATE, 3000, NULL, 20); 
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, SYSDATE, 5000, NULL, 10); 
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, SYSDATE, 1500, 0, 30); 
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, SYSDATE, 1100, NULL, 20); 
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, SYSDATE, 950, NULL, 30); 
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, SYSDATE, 3000, NULL, 20); 
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, SYSDATE, 1300, NULL, 10); 
COMMIT;


案例一 使用游标查询部门编号为10的所有人姓名和薪水

create or replace procedure test2 is
begin
 declare
   type c is ref cursor;    
   emp_sor c;      
   cname emp.ename%type;     
   csal emp.sal%type;
begin
  open emp_sor for select ename,sal from emp where deptno=10;       
  loop        
    fetch emp_sor into cname,csal;  --取游标的值给变量。             
    dbms_output.put_line('ename:'||cname||'sal'||csal);        
    exit when emp_sor%notfound;        
   end loop;         
   close emp_sor;     
end;
end test2;

案例二  直接定义游标

create or replace procedure test3 is
begin
 declare
   cursor emp_sor  is select ename,sal from emp where deptno=10;
   cname emp.ename%type;
   csal emp.sal%type;
begin
  open emp_sor;
  loop
    fetch emp_sor into cname,csal;  --取游标的值给变量。
    dbms_output.put_line('ename:'||cname||'sal'||csal);
    exit when emp_sor%notfound;
   end loop;
   close emp_sor;
end;
end test3;

 

案例三  使用记录变量来接受游标指定的表的数据

create or replace procedure test4 is
begin
 declare
   cursor emp_sor is
     select ename, sal from emp where deptno = 10;
   --使用记录变量来接受游标指定的表的数据
   type emp_type is record(
     v_ename emp.ename%type,
     v_sal   emp.sal%type);
   --用emp_type声明一个与emp_type类似的记录变量。该记录有两列，与emp表的ename，sal同类型的列。
   emp_type1 emp_type;
 begin
   open emp_sor;
   loop
     fetch emp_sor into emp_type1; --取游标的值给变量。
     dbms_output.put_line(emp_type1.v_ename || ',' || emp_type1.v_sal);
     exit when emp_sor%notfound;
   end loop;
   close emp_sor;
 end;
end test4;

案例四  for循环从游标中取值

create or replace procedure test5 is
begin
 declare
   cursor emp_sor is select a.ename from emp a;
   type ename_table_type is table of varchar2(20);
   ename_table ename_table_type;
 begin
  ----用for游标取值
   open emp_sor;
   ---通过bulk collect减少loop处理的开销，使用Bulk Collect提高Oracle查询效率
   ---Oracle8i中首次引入了Bulk Collect特性，该特性可以让我们在PL/SQL中能使用批查询，批查询在----某--些情况下能显著提高查询效率。
   --采用bulk collect可以将查询结果一次性地加载到collections中。
   --而不是通过cursor一条一条地处理。
   --可以在select into,fetch into,returning into语句使用bulk collect。
   --注意在使用bulk collect时，所有的into变量都必须是collections
     fetch emp_sor bulk collect into ename_table;
     for i in 1 ..ename_table.count loop
       dbms_output.put_line(ename_table(i));
   end loop;
   close emp_sor;
 end;
end test5;

案例五  用for取值，带隐式游标会自动打开和关闭

create or replace procedure test6 is
begin
 declare
   cursor emp_sor is select a.ename from emp a;
   type emp_table_type is table of varchar(20);
   begin 
   for emp_record in emp_sor
     loop
       dbms_output.put_line('第'||emp_sor%rowcount||'雇员名:'||emp_record.ename);
     end loop;
 end;
end test6;

案例六  判断游标是否打开


create or replace procedure test7 is
begin
 declare
   cursor emp_sor is select a.ename from emp a;
   type emp_table_type is table of varchar(20);
   emp_table emp_table_type;
 begin
  --用for取值，判断游标是否打开
   if not emp_sor%isopen then
     open emp_sor;
   end if;
   fetch emp_sor bulk collect into emp_table;
   dbms_output.put_line(emp_sor%rowcount);
   close emp_sor;
 end;
end test7;

案例七  使用游标变量取值

create or replace procedure test8 is
begin
  --使用游标变量取值
 declare
   cursor emp_sor is select a.ename,a.sal from emp a;
   emp_record emp_sor%rowtype;
 begin
  open emp_sor;
  loop
    fetch emp_sor into emp_record;
    exit when emp_sor%notfound;
     --exit when emp_sor%notfound放的位置不一样得到的结果也不一样。如果放到dbms_....后，
     --结果会多显示一行数据，即查询结果的最后一行显示了两次。
    dbms_output.put_line('序号'||emp_sor%rowcount||'名称:'||emp_record.ename||'薪水:'||emp_record.sal);
  end loop;
  close emp_sor;
 end;
end test8;

案例八 带参数的游标，在打开游标的时候传入参数


create or replace procedure test9 is
begin
  --带参数的游标，在打开游标的时候传入参数
 declare
   cursor emp_sor(no number) is select a.ename from emp a where a.deptno=no;
   emp_record emp_sor%rowtype;
 begin
  open emp_sor(10);
  loop
    fetch emp_sor into emp_record;
    exit when emp_sor%notfound;
    dbms_output.put_line('序号'||emp_sor%rowcount||'名称:'||emp_record.ename);
  end loop;
  close emp_sor;
 end;
end test9;

 案例九  使用游标做更新操作


create or replace procedure test10 is
begin
  --使用游标做更新、删除操作，必须在定义游标的时候加上for update
  --当然也可以用for update nowait
 declare
   cursor emp_sor is select a.ename,a.sal from emp a for update;
   cname emp.ename%type;
   csal emp.sal%type;
 begin
  open emp_sor;
  loop
    fetch emp_sor into cname,csal;
    exit when emp_sor%notfound;
    dbms_output.put_line('名称:'||cname||','||'薪水:'||csal);
    if csal < 2000 then
      update emp set sal = sal+200 where current of emp_sor;
    end if;
  end loop;
  close emp_sor;
  --要查看更新后的数据，必须得重新打开游标去查询
  open emp_sor;
  loop
    fetch emp_sor into cname,csal;
     exit when emp_sor%notfound;
     dbms_output.put_line('名称:'||cname||','||'new薪水:'||csal);
  end loop;
  close emp_sor;
 end;
end test10;

案例十  使用游标做删除操作


create or replace procedure test11 is
begin
  --使用游标做更新、删除操作，必须在定义游标的时候加上for update
 declare
   cursor emp_sor is select a.empno from emp a for update;
   pempno emp.empno%type;
 begin
  open emp_sor;
  loop
    fetch emp_sor into pempno;
    exit when emp_sor%notfound;
    dbms_output.put_line('旧的empno:'||pempno);
    if pempno = 2009 then
       delete emp where current of emp_sor;
    end if;
  end loop;
  close emp_sor;
  --要查看删除后的数据，必须得重新打开游标去查询
  open emp_sor;
  loop
    fetch emp_sor into pempno;
    exit when emp_sor%notfound;
    dbms_output.put_line('新的empno:'||pempno);
  end loop;
  close emp_sor;
 end;
end test11;

案例十一 直接使用游标而不用去定义


create or replace procedure test12 is
 begin
   for emp_record in(select empno,sal,deptno from emp)
     loop
       dbms_output.put_line('员工编号:'||emp_record.empno||',薪水:'||emp_record.sal||',部门编号'||emp_record.deptno);
     end loop;
end test12;

案例十二 带sql 的统计查询


create or replace procedure test13 is
begin
 declare
   type test_cursor_type is ref cursor;
   test_cursor test_cursor_type;
   v_name user_tables.TABLE_NAME%type;
   v_count number;
   str_sql varchar2(100);
 begin
  open test_cursor for select table_name from user_tables;
  loop
    fetch test_cursor into v_name;
    if v_name is not null then
      str_sql := 'select count(*) from '|| v_name;
      execute immediate str_sql into v_count;
    end if;
    exit when test_cursor%notfound;
    dbms_output.put_line(v_name||','||v_count);
  end loop;
  close test_cursor;
 end;
end test13;

 

当我们写完存储过程之后，我们可以在 command window下执行，oracle默认是不显示输出的， 

所以我们要 set serveroutput on 命令来显示输出结果，然后exec test1()即可输出结果。

 

以上内容比较杂乱 ，有的是借鉴别人的 ，为了对自己不熟悉的地方做个记录 一并归入到了本文章中，希望你看了后能有所收获！

如果你有好的案例可以发给我，有错误的地方敬请指正！

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

存储过程

存储过程可以有多个或者零个输入输出参数，但通常没有返回值。存储过程是已经编译好的代码，所以执行效率非常高

存储过程创建语句

 

create [or replace] procedure 存储过程名 [(输入、输出参数、in/out模式参数)]
is/as
begin
sentences;
[exception
 sentences;]
end 存储过程名;



举例一个没有参数的存储过程

 

create or replace procedure pro_no_par is
begin
update employees set salary=salary+100 where employee_id=100;
commit;
dbms_output.put_line('工资已经调整！！');
end pro_no_par;


结果：

 

begin
pro_no_par;
end;
匿名块已完成
工资已经调整！！




举例一个带in模式参数的存储过程

参数的方式有三种：指定名称传递；按位置传递；混合方式传递

注意混合方式传递从使用【指定名称传递】的位置开始，后面的参数必须使用【指定名称传递】

 

create or replace procedure pro_in_par
( var_1 in nvarchar2,
  var_2 in number)   is
begin
update employees set salary=salary+var_2 where first_name=var_1;
commit;
dbms_output.put_line(var_1||'的工资已经增加了'||var_2||'!');
end pro_in_par;


结果：

 

begin
execute pro_in_par(var_1=>'Steven',var_2=>100);
end
匿名块已完成
Steven的工资已经增加了100!
begin
execute pro_in_par('Steven',var_2=>100);
end
匿名块已完成
Steven的工资已经增加了100!

pl/sql developer下的调用语法
begin
pro_in_par('Steven',100);
end

--举例混合参数输入，后面的参数没有使用【指定名称传递】
execute pro_in_par(var_1=>'Steven',100);
在行 42 上开始执行命令时出错:
execute pro_in_par(var_1=>'Steven',100)
错误报告:
ORA-06550: 第 1 行, 第 34 列: 
PLS-00312: 一个定位相关参数没有说明其相关性
ORA-06550: 第 1 行, 第 7 列: 
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:



观察存储过程中变量的使用（存储过程的输入输入参数，字符型不用定义长度，内部的字符型型变量是需要带长度的）

内部的变量不需要用declare，内部变量的定义在as之后，

 begin end 之前

--表准备
create table chl_simple(fid int,fname  varchar2(200));
--存储过程
create or replace procedure chl_ztablecreate01
(var_fname in varchar2)
as
var_sql varchar2(200);
begin
var_sql:=var_fname;
insert into chl_simple(fid,fname) values(1,var_sql);
end;
--存过程调用
begin
chl_ztablecreate01('12345');
end;


举例一个带out模式和in out模式参数存储过程

create procedure pro_out_par
( var_1 in out number,
  var_2 out employees.first_name%type,
  var_3 out employees.phone_number%type) is
begin
select first_name,phone_number into var_2,var_3 from employees where employee_id=var_1;
end pro_out_par;
--执行
declare 
ex_var_1 number;
ex_var_2  employees.first_name%type;
ex_var_3  employees.phone_number%type;
begin
ex_var_1:=102;
pro_out_par(ex_var_1,ex_var_2,ex_var_3);
dbms_output.put_line('员工号为'||ex_var_1||'的员工姓名是;'||ex_var_2||',联系电话是：'||ex_var_3);
end;

结果;

匿名块已完成
员工号为102的员工姓名是;Lex,联系电话是：515.123.4569




只有努力不会背叛

博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。

 

一，oracle存储过程语法
 
1.oracle存储过程结构 
CREATE OR REPLACE PROCEDURE oracle存储过程名字
(
    参数1 IN NUMBER,
    参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
END oracle存储过程名字



 
2.无返回值的oracle存储过程
create or replace procedure xs_proc_no is
begin
  insert into xuesheng values (3, 'wangwu', 90, 90);
  commit;
end xs_proc_no;



 
3.有单个数据值返回的oracle存储过程
create or replace procedure xs_proc(temp_name in varchar2,
                                    temp_num  out number) is
  num_1 number;
  num_2 number;
begin
  select yu_wen, shu_xue
    into num_1, num_2
    from xuesheng
   where xing_ming = temp_name;
  --dbms_output.put_line(num_1 + num_2);
  temp_num := num_1 + num_2;
end;



其中，以上两种与sql server基本类似，而对于返回数据集时，上述方法则不能满足我们的要求。在Oracle中，一般使用ref cursor来返回数据集。示例代码如下：
 
4.有返回值的oracle存储过程(列表返回)
首先，建立我们自己的包。并定义包中的一个自定义ref cursor
create or replace package mypackage as
  type my_cursor is ref cursor;
end mypackage;



在定义了ref cursor后，可以书写我们的程序代码
create or replace procedure xs_proc_list(shuxue   in number,
                                         p_cursor out mypackage.my_cursor) is
begin
  open p_cursor for
    select * from xuesheng where shu_xue > shuxue;
end xs_proc_list;



 
5.SELECT INTO STATEMENT
  将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
  记录，否则抛出异常(若没有记录则抛出NO_DATA_FOUND)
  例子： 
  BEGIN
  SELECT name,age into 变量1,变量2 FROM user where xxx;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      xxxx;
  END;
  ...
 
6.IF 判断
  IF V_TEST=1 THEN
    BEGIN 
       do something
    END;
  END IF;
 
7.WHILE 循环
  WHILE V_TEST=1 LOOP
  BEGIN
 XXXX
  END;
  END LOOP;
 
8.变量赋值
  V_TEST := 123;
 
9.用FOR IN 使用cursor
  .
..
  IS
  CURSOR cur IS SELECT * FROM xxx;
  BEGIN
 FOR cur_result in cur LOOP
  BEGIN
   V_SUM :=cur_result.列名1+cur_result.列名2
  END;
 END LOOP;
  END;


 
10.带参数的CURSOR
  
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
  OPEN C_USER(变量值);
  LOOP
 FETCH C_USER INTO V_NAME;
 EXIT FETCH C_USER%NOTFOUND;
    do something
  END LOOP;
  CLOSE C_USER;


 
11.用pl/sql developer debug
  连接数据库后建立一个Test WINDOW
  在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试
 
二，oracle存储过程的若干问题备忘
 
1.在oracle中，表别名不能加as，如：
select a.name from user a;-- 正确
select a.name from user as a;-- 错误

应该是防止和oracle存储过程中的关键字as有冲突，才会这样规定吧！
2.oracle存储过程中select某一字段时，后面必须要跟into，如果select整个记录，利用游标的话就另当别论了。
  select u.name into kn from user u where u.userid=uid and u.fatherid=fid;-- 有into，正确编译
  select u.name from user u where u.userid=uid and u.fatherid=fid;-- 没有into，编译报错，提示：Compilation 
  Error: PLS-00428: an INTO clause is expected in this SELECT statement
 
 
3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。
   可以在该语法前，先利用select count(*) from 查看数据库中是否存在该记录，如果存在，再利用select...into...
4.oracle在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错
select name into kn from user where userid=aid and fatherid=fid;-- 正确运行
select u.name into kn from user u where u.userid=userid and u.fatherid=fatherid;-- 运行时报错，提示
ORA-01422:exact fetch returns more than requested number of rows
5.在oracle存储过程中，关于出现null的问题
假设有一个表A，定义如下：
create table user(
id varchar2(50) primary key not null,
age number(5) not null,
fid varchar2(50) not null -- 外键 
);
如果在oracle存储过程中，使用如下语句：
select sum(age) into fage from user where fid='xxxxxx';
如果user表中不存在fid="xxxxxx"的记录，则fage=null(即使fage定义时设置了默认值，如：fage number(5):=0依然无效，fage还是会变成null)，这样以后使用fage时就可能有问题，所以在这里最好先判断一下：
if fage is null then
    fage:=0;
end if;
这样就一切ok了。
 
 
6.java调用oracle存储过程。其中，关键是使用CallableStatement这个对象，代码如下：
 
String oracleDriverName = "oracle.jdbc.driver.OracleDriver";
 
        // 以下使用的Test就是Oracle里的表空间
        String oracleUrlToConnect = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        Connection myConnection = null;
        try {
            Class.forName(oracleDriverName);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
        try {
            myConnection = DriverManager.getConnection(oracleUrlToConnect,
                    "xxxx", "xxxx");//此处为数据库用户名与密码
 
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        try {
             
            CallableStatement proc=null;
            proc=myConnection.prepareCall("{call xs_proc(?,?)}");
            proc.setString(1, "lisi");
            proc.registerOutParameter(2, Types.NUMERIC);
            proc.execute();
            String teststring=proc.getString(2);
            System.out.println(teststring);
 
        } catch (Exception ex) {
            ex.printStackTrace();
        }



对于列表返回值的oracle存储过程，在上述代码中做简单修改。如下
CallableStatement proc=null;
            proc=myConnection.prepareCall("{call getdcsj(?,?,?,?,?)}");
            proc.setString(1, strDate);
   proc.setString(2, jzbh);
            proc.registerOutParameter(3, Types.NUMERIC);
            proc.registerOutParameter(4, OracleTypes.CURSOR);
            proc.registerOutParameter(5, OracleTypes.CURSOR);
            proc.execute();
            ResultSet rs=null;
            int total_number=proc.getInt(3);
            rs=(ResultSet)proc.getObject(4);



上述oracle存储过程修改完毕
 
7.Hibernate调用oracle存储过程
       
 this.userManager.getHibernateTemplate().execute(
                new HibernateCallback() {
                    public Object doInHibernate(Session session)
                            throws HibernateException, SQLException {
                        CallableStatement cs = session
                                .connection()
                                .prepareCall("{call modifyapppnumber_remain(?)}");
                        cs.setString(1, foundationid);
                        cs.execute();
                        return null;
                    }
                });