Oracle存储过程

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()即可输出结果。

 

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

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