oracle 存储过程以及plsql语句块

分手后的思念是犯贱 2022-07-28 09:44 384阅读 0赞

1、 PLSQL语法

1.1、程序结构

PL/SQL程序都是以块（block）为基本单位。整个PL/SQL块分三部分：声明部分（用declare开头）、执行部分（以 begin开头）和异常处理部分（以exception开头）。其中执行部分是必须的，其他两个部分可选。无论PL/SQL程序段的代码量有多大，其基本结构就是由这三部分组成。而且每条语句均由分号隔开。

<table style="background:#F3F3F3"> 
 <tbody> 
 <tr> 
 <td> /*声明部分，以declare开头*/ DECLARE &nbsp;&nbsp;&nbsp; --declare statement&nbsp; /*执行部分，以begin开头,以end结束*/ BEGIN --execute statement /*异常处理部分，以exception开始*/ EXCEPTION &nbsp;&nbsp;&nbsp; WHEN exceptioncase THEN &nbsp;&nbsp;&nbsp; --Exception handle END;&nbsp; </td> 
 </tr> 
 </tbody> 
</table>

例子：

<table style="background:#F3F3F3"> 
 <tbody> 
 <tr> 
 <td> /*声明部分，以declare开头*/ DECLARE &nbsp;&nbsp;&nbsp; v_job NUMBER(10); &nbsp; /*执行部分，以begin开头*/ BEGIN &nbsp;&nbsp;&nbsp; DELETE FROM employee; &nbsp;&nbsp;&nbsp; INSERT INTO employee &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (depno, job &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUES (20, 30 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ); &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; SELECT job &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INTO v_job &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM employee &nbsp;&nbsp;&nbsp;&nbsp; WHERE depno = 20; &nbsp;&nbsp; dbms_output.PUT_LINE(v_job); /*异常处理部分，以exception开始*/ EXCEPTION &nbsp;&nbsp;&nbsp; WHEN no_data_found THEN &nbsp;&nbsp;&nbsp; dbms_output.PUT_LINE('No data.'); END;&nbsp; </td> 
 </tr> 
 </tbody> 
</table>

执行结果：

今天做项目突然用到了oracle的declare，本以为和SqlServer一样，最后发现还是有一定的语法区别的，现特此总结下：

declare --声明变量的关键字 
v\_c  varchar(200); 
abc varchar(20); 
begin 
abc := '22';   --给声明的变量赋值，不支持声明时就赋值 
select GAMECODE into v\_c from LOTTERY.t\_lot\_darwnumber t where t.gamecode = abcand rownum <2; 
dbms\_output.put\_line(v\_c); --输出变量值

end;

\-学习块的结构  
\--学习定义变量（了解数据类型）及赋值  
\--了解：各种PL/SQL块的类型

\--块的结构  
\--案例：根据工号，输出员工姓名  
SELECT \* FROM emp;

\--块！  
DECLARE  
  v\_empno NUMBER(4);  
  v\_ename VARCHAR2(10);  
BEGIN  
   v\_empno := &请输入工号;  
   --必须要将查询结果放到变量  
   SELECT ename INTO v\_ename FROM emp WHERE empno=v\_empno;  
   dbms\_output.put\_line(v\_ename);  
END;

\--异常  
DECLARE  
  v\_empno NUMBER(4);  
  v\_ename VARCHAR2(10);  
BEGIN  
   v\_empno := &请输入工号;  
   --必须要将查询结果放到变量  
   SELECT ename INTO v\_ename FROM emp WHERE empno=v\_empno;  
   dbms\_output.put\_line(v\_ename);  
    
   EXCEPTION  
      WHEN OTHERS THEN  
        dbms\_output.put\_line('执行出错了，老板！');  
END;

\--小结  
DECLARE  
   --变量区(不能在begin..end中定义）  
BEGIN  
    --业务代码区  
    excetipn  
    --异常捕获区  
END;

\--定义变量（了解数据类型）  
\--2种：保存一个值＝简单变量；保存多个值＝复合变量

\--简单变量类型:char,varchar2,number,date(同时也列类型),boolean,表.字段%type  
\--复合变量:表%rowtype,record  
DECLARE  
  v\_empno NUMBER(4);  
  v\_ename VARCHAR2(10);  
  v\_job emp.job%TYPE; --如果字段类型改变，脚本不需要改变  
BEGIN  
   v\_empno := 7566;  
   SELECT ename,job INTO v\_ename,v\_job FROM emp WHERE empno=v\_empno;  
   dbms\_output.put\_line(v\_ename);  
   IF(v\_job='MANAGER')THEN  
      dbms\_output.put\_line('经理，吃过饭没。');  
   END IF;  
END;

\--需要同上，将多个变量放到一个记录对象中  
DECLARE  
  v\_empno NUMBER(4);  
  my\_row emp%ROWTYPE;  
BEGIN  
   v\_empno := 7566;  
   SELECT \* INTO my\_row FROM emp WHERE empno=v\_empno;  
   dbms\_output.put\_line(my\_row.ename||','||my\_row.sal);  
END;

\--有时不需要一整列，只需要其中几个列。此时用record  
DECLARE  
    --自定义复合类型 my\_emp  
   TYPE my\_emp\_type IS RECORD(  
      yg\_name emp.ename%TYPE,  
      yg\_sal emp.sal%TYPE  
   );  
   my\_row my\_emp\_type; --变量名  数据类型  
BEGIN  
   SELECT ename,sal INTO my\_row FROM emp WHERE empno=7788;  
   dbms\_output.put\_line(my\_row.yg\_name||','||my\_row.yg\_sal);  
END;

\--扩展：Oracle没有boolean类型？  
\--思考：性别 true=男,false=女。缺点？性别＝保密。所以Oracle建立用char(1)替代，1＝男2＝女3＝保密4＝人妖

\--Oracle的各种集合类型，对应 java的数组。  
\--引用变量：在讲解存储过程以后再讲解。

\--绑定变量(命令窗口中执行，绑定变量用了 :）  
SQL> var name varchar2(10)  
SQL> execute :name :='hello';  
PL/SQL procedure successfully completed  
name  
\---------  
hello

SQL> print name;  
name  
\---------  
hello

## [Oracle存储过程学习][Oracle] ##

存储过程创建语法：

create or replace procedure 存储过程名（param1 in type，param2 out type）

as 【这个as类似declare】

变量1 类型（值范围）;

变量2 类型（值范围）;

Begin

Select count(\*) into 变量1 from 表A where列名=param1；

If (判断条件) then

Select 列名 into 变量2 from 表A where列名=param1；

Dbms\_output。Put\_line(‘打印信息’);

Elsif (判断条件) then

Dbms\_output。Put\_line(‘打印信息’);

Else

Raise 异常名（NO\_DATA\_FOUND）;

End if;

Exception

When others then

Rollback;

End;

注意事项：

1，  存储过程参数不带取值范围，in表示传入，out表示输出

2，  变量带取值范围，后面接分号

3，  在判断语句前最好先用count（\*）函数判断是否存在该条操作记录

4，  用select 。。。into。。。给变量赋值

5，  在代码中抛异常用 raise+异常名

以命名的异常

命名的系统异常                          产生原因

ACCESS\_INTO\_NULL                   未定义对象

CASE\_NOT\_FOUND                     CASE 中若未包含相应的 WHEN ，并且没有设置

ELSE 时

COLLECTION\_IS\_NULL                集合元素未初始化

CURSER\_ALREADY\_OPEN          游标已经打开

DUP\_VAL\_ON\_INDEX                   唯一索引对应的列上有重复的值

INVALID\_CURSOR                 在不合法的游标上进行操作

INVALID\_NUMBER                       内嵌的 SQL 语句不能将字符转换为数字

NO\_DATA\_FOUND                        使用 select into 未返回行，或应用索引表未初始化的

TOO\_MANY\_ROWS                      执行 select into 时，结果集超过一行

ZERO\_DIVIDE                              除数为 0

SUBSCRIPT\_BEYOND\_COUNT     元素下标超过嵌套表或 VARRAY 的最大值

SUBSCRIPT\_OUTSIDE\_LIMIT       使用嵌套表或 VARRAY 时，将下标指定为负数

VALUE\_ERROR                             赋值时，变量长度不足以容纳实际数据

正确的用户名或密码

NOT\_LOGGED\_ON                       PL/SQL 应用程序在没有连接 oralce 数据库的情况下

访问数据

PROGRAM\_ERROR                       PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL

系统包

ROWTYPE\_MISMATCH                宿主游标变量与 PL/SQL 游标变量的返回类型不兼容

SELF\_IS\_NULL                             使用对象类型时，在 null 对象上调用对象方法

STORAGE\_ERROR                        运行 PL/SQL 时，超出内存空间

SYS\_INVALID\_ID                         无效的 ROWID 字符串

TIMEOUT\_ON\_RESOURCE         Oracle 在等待资源时超时    
\---------------------------------------------------------------------------------------------------------------------

[oracle 存储过程的基本语法 及注意事项][oracle _]

# oracle 存储过程的基本语法 #

**1.基本结构**   
CREATE OR REPLACE PROCEDURE 存储过程名字  
(  
    参数1 IN NUMBER,  
    参数2 IN NUMBER  
) IS  
变量1 INTEGER :=0;  
变量2 DATE;  
BEGIN

END 存储过程名字  
  
**2.SELECT INTO STATEMENT**  
  将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条  
  记录，否则抛出异常(如果没有记录抛出NO\_DATA\_FOUND)  
  例子：   
  BEGIN  
  SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;  
  EXCEPTION  
  WHEN NO\_DATA\_FOUND THEN  
      xxxx;  
  END;  
  ...  
  
**3.IF 判断**  
  IF V\_TEST=1 THEN  
    BEGIN   
       do something  
    END;  
  END IF;  
  
**4.while 循环**  
  WHILE V\_TEST=1 LOOP  
  BEGIN  
 XXXX  
  END;  
  END LOOP;  
  
**5.变量赋值**  
  V\_TEST := 123;  
  
**6.用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;  
  
**7.带参数的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;  
  
**8.用pl/sql developer debug**  
  连接数据库后建立一个Test WINDOW  
  在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

### 关于oracle存储过程的若干问题备忘 ###

1.在oracle中，数据表别名不能加as，如：

![None.gif][] select a.appname  from appinfo a; \--  正确   
![None.gif][] select a.appname  from appinfo  as a; \--  错误

也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧

2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。

![None.gif][]   select af.keynode  into kn  from APPFOUNDATION af  where af.appid =aid  and af.foundationid =fid; \--  有into，正确编译   
![None.gif][]   select af.keynode  from APPFOUNDATION af  where af.appid =aid  and af.foundationid =fid; \--  没有into，编译报错，提示：Compilation    
![None.gif][]  Error: PLS \- **00428**: an  INTO clause  is expected  in this  SELECT statement  
![None.gif][]  
![None.gif][]

3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。

可以在该语法之前，先利用 select count(\*) from 查看数据库中是否存在该记录，如果存在，再利用select...into...

4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错

![None.gif][]  select keynode  into kn  from APPFOUNDATION  where appid =aid  and foundationid =fid; \--  正确运行   
![None.gif][] select af.keynode  into kn  from APPFOUNDATION af  where  af.appid = appid  and  af.foundationid = foundationid; \--  运行阶段报错，提示   
![None.gif][]ORA \- **01422**:exact  fetch  returns more than requested  **number**  of rows ![None.gif][]

5.在存储过程中，关于出现null的问题

假设有一个表A，定义如下：

![None.gif][] create  table A(  
![None.gif][]id  **varchar2**( **50**)  primary  key  not  null,  
![None.gif][]vcount  **number**( **8**)  not  null,  
![None.gif][]bid  **varchar2**( **50**)  not  null  \--  外键    
![None.gif][]);

如果在存储过程中，使用如下语句：

![None.gif][]select sum(vcount) into fcount from A  where bid='xxxxxx';

如果A表中不存在bid="xxxxxx"的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：

![None.gif][] if fcount  is  null then  
![None.gif][]    fcount:=0;  
![None.gif][]end  if;

这样就一切ok了。

6.Hibernate调用oracle存储过程

![None.gif][]         this.pnumberManager.getHibernateTemplate().execute(  
![ExpandedBlockStart.gif][]                 new HibernateCallback()  \{  
![InBlock.gif][]                    public Object doInHibernate(Session session)  
![ExpandedSubBlockStart.gif][]                            throws HibernateException, SQLException \{  
![InBlock.gif][]                        CallableStatement cs = session  
![InBlock.gif][]                                .connection()  
![InBlock.gif][]                                .prepareCall("\{call modifyapppnumber\_remain(?)\}");  
![InBlock.gif][]                        cs.setString(1, foundationid);  
![InBlock.gif][]                        cs.execute();  
![InBlock.gif][]                        return null;  
![ExpandedSubBlockEnd.gif][]                    \}  
![ExpandedBlockEnd.gif][]                \});

## oracle存储过程和游标的使用 ##

(2011-04-19 14:52:47)

![sg_trans.gif][]转载▼

<table style="margin:0px; padding:0px"> 
 <tbody> 
 <tr> 
 <td style="margin:0px; padding:0px 10px 0px 0px; font-family:宋体; vertical-align:top"> 标签：&nbsp; <h3 style="margin:0px 5px 0px 0px; padding:0px; border:0px; list-style:none; display:inline; font-size:12px; font-weight:normal"> <a href="http://search.sina.com.cn/?c=blog&amp;q=%B2%BC%B6%FB&amp;by=tag" style="text-decoration:none; color:rgb(62,115,160); white-space:nowrap" rel="nofollow">布尔</a></h3> &nbsp; <h3 style="margin:0px 5px 0px 0px; padding:0px; border:0px; list-style:none; display:inline; font-size:12px; font-weight:normal"> <a href="http://search.sina.com.cn/?c=blog&amp;q=%D3%CE%B1%EA&amp;by=tag" style="text-decoration:none; color:rgb(62,115,160); white-space:nowrap" rel="nofollow">游标</a></h3> &nbsp; <h3 style="margin:0px 5px 0px 0px; padding:0px; border:0px; list-style:none; display:inline; font-size:12px; font-weight:normal"> <a href="http://search.sina.com.cn/?c=blog&amp;q=%D3%EF%BE%E4&amp;by=tag" style="text-decoration:none; color:rgb(62,115,160); white-space:nowrap" rel="nofollow">语句</a></h3> &nbsp; <h3 style="margin:0px 5px 0px 0px; padding:0px; border:0px; list-style:none; display:inline; font-size:12px; font-weight:normal"> <a href="http://search.sina.com.cn/?c=blog&amp;q=%B1%E4%C1%BF&amp;by=tag" style="text-decoration:none; color:rgb(62,115,160); white-space:nowrap" rel="nofollow">变量</a></h3> &nbsp; <h3 style="margin:0px 5px 0px 0px; padding:0px; border:0px; list-style:none; display:inline; font-size:12px; font-weight:normal"> <a href="http://search.sina.com.cn/?c=blog&amp;q=oracle%CA%FD%BE%DD%BF%E2&amp;by=tag" style="text-decoration:none; color:rgb(62,115,160); white-space:nowrap" rel="nofollow">oracle数据库</a></h3> &nbsp; <h3 style="margin:0px 5px 0px 0px; padding:0px; border:0px; list-style:none; display:inline; font-size:12px; font-weight:normal"> <a href="http://search.sina.com.cn/?c=blog&amp;q=oracle%B4%E6%B4%A2%B9%FD%B3%CC&amp;by=tag" style="text-decoration:none; color:rgb(62,115,160); white-space:nowrap" rel="nofollow">oracle存储过程</a></h3> &nbsp; <h3 style="margin:0px 5px 0px 0px; padding:0px; border:0px; list-style:none; display:inline; font-size:12px; font-weight:normal"> <a href="http://search.sina.com.cn/?c=blog&amp;q=%D4%D3%CC%B8&amp;by=tag" style="text-decoration:none; color:rgb(62,115,160); white-space:nowrap" rel="nofollow">杂谈</a></h3> </td> 
 <td style="margin:0px; padding:0px; font-family:宋体; vertical-align:top; width:220px; white-space:nowrap"> &nbsp;</td> 
 </tr> 
 </tbody> 
</table>

游标：

用来查询数据库，获取记录集合（结果集）的指针，我们所说的游标通常是指显式游标，因此从现在起没有特别指明的情况，我们所说的游标都是指显式游标。要在程序中使用游标，必须首先声明游标分类：

静态游标：  
分为显式游标和隐式游标。

REF游标：  
是一种引用类型，类似于指针。

显式游标：

CURSOR 游标名 ( 参数 ) \[返回值类型\] IS  
  Select 语句

生命周期：

在大多数时候我们在设计程序的时候都遵循下面的步骤：  
　1、打开游标 open cs1;  
　2、开始循环 while cs1%found loop | for column\_name in .. LOOP  
　3、从游标中取值 fetch .. into.. |  
　4、检查那一行被返回  
　5、处理  
　6、关闭循环 end loop;  
　7、关闭游标 if cs1&isopen then close cs1;

选项：参数和返回类型

set serveroutput on  
declare  
 cursor emp\_cur ( p\_deptid in number) is  
select \* from employees where department\_id = p\_deptid;

l\_emp employees%rowtype;  
begin  
 dbms\_output.put\_line('Getting employees from department 30');  
open emp\_cur(30);  
 loop  
  fetch emp\_cur into l\_emp;  
  exit when emp\_cur%notfound;  
  dbms\_output.put\_line('Employee id '|| l\_emp.employee\_id || ' is ');  
  dbms\_output.put\_line(l\_emp.first\_name || ' ' || l\_emp.last\_name);  
 end loop;  
 close emp\_cur;

dbms\_output.put\_line('Getting employees from department 90');  
open emp\_cur(90);  
 loop  
  fetch emp\_cur into l\_emp;  
  exit when emp\_cur%notfound;  
  dbms\_output.put\_line('Employee id '|| l\_emp.employee\_id || ' is ');  
  dbms\_output.put\_line(l\_emp.first\_name || ' ' || l\_emp.last\_name);  
 end loop;  
 close emp\_cur;  
end;  
/

隐式游标：

不用明确建立游标变量，分两种：  
1.在PL/SQL中使用DML语言，使用ORACLE提供的名为SQL的隐示游标  
2.CURSOR FOR LOOP，用于for loop 语句

1举例：

declare  
begin  
 update departments set department\_name=department\_name;  
 \--where 1=2;  
   
 dbms\_output.put\_line('update '|| sql%rowcount ||' records');  
end;  
/

2举例：

declare  
begin  
 for my\_dept\_rec in ( select department\_name, department\_id from departments)  
 loop  
  dbms\_output.put\_line(my\_dept\_rec.department\_id || ' : ' || my\_dept\_rec.department\_name);  
 end loop;  
end;  
/

3举例：

单独select

declare  
 l\_empno emp.EMPLOYEE\_ID%type;  
\-- l\_ename emp.ename%type;  
begin  
 select EMPLOYEE\_ID     
  into l\_empno  
 from emp;  
 \--where rownum =1;  
 dbms\_output.put\_line(l\_empno);  
end;  
/  
使用INTO获取值，只能返回一行。

游标属性：

%FOUND：变量最后从游标中获取记录的时候，在结果集中找到了记录。  
%NOTFOUND：变量最后从游标中获取记录的时候，在结果集中没有找到记录。  
%ROWCOUNT：当前时刻已经从游标中获取的记录数量。  
%ISOPEN：是否打开。

Declare 
 Cursor emps is 
 Select \* from employees where rownum<6 order by 1; 
  
 Emp employees%rowtype; 
 Row number :=1; 
Begin 
 Open emps; 
 Fetch emps into emp; 
  
 Loop 
  If emps%found then 
   Dbms\_output.put\_line('Looping over record '||row|| ' of ' || emps%rowcount); 
   Fetch emps into emp; 
   Row := row + 1; 
  Elsif emps%notfound then 
   Exit;  ---exit loop, not IF 
  End if; 
 End loop; 
  
 If emps%isopen then 
  Close emps; 
 End if; 
End; 
/

显式和隐式游标的区别：

尽量使用隐式游标，避免编写附加的游标控制代码（声明，打开，获取，关闭），也不需要声明变量来保存从游标中获取的数据。

REF CURSOR游标：

动态游标，在运行的时候才能确定游标使用的查询。分类：  
强类型（限制）REF CURSOR，规定返回类型  
弱类型（非限制）REF CURSOR，不规定返回类型，可以获取任何结果集。

TYPE ref\_cursor\_name IS REF CURSOR \[RETURN return\_type\]

Declare  
 Type refcur\_t is ref cursor;  
   
 Type emp\_refcur\_t is ref cursor return employee%rowtype;  
Begin  
 Null;  
End;  
/

强类型举例：

declare  
 \--声明记录类型  
 type emp\_job\_rec is record(  
  employee\_id number,  
  employee\_name varchar2(50),  
  job\_title varchar2(30)  
 );  
 \--声明REF CURSOR，返回值为该记录类型  
 type emp\_job\_refcur\_type is ref cursor  
  return emp\_job\_rec;  
 \--定义REF CURSOR游标的变量  
 emp\_refcur emp\_job\_refcur\_type;

emp\_job emp\_job\_rec; 
begin 
 open emp\_refcur for 
  select e.employee\_id, 
    e.first\_name || ' ' ||e.last\_name "employee\_name", 
    j.job\_title 
  from employees e, jobs j 
  where e.job\_id = j.job\_id and rownum < 11 order by 1;

fetch emp\_refcur into emp\_job;  
 while emp\_refcur%found loop  
  dbms\_output.put\_line(emp\_job.employee\_name || '''s job is ');  
  dbms\_output.put\_line(emp\_job.job\_title);  
  fetch emp\_refcur into emp\_job;  
 end loop;  
end;  
\----------------------------------------------------------------------------------------

Oracle数据库游标使用大全  
SQL是用于访问ORACLE数据库的语言，PL/SQL扩展和加强了SQL的功能，它同时引入了更  
强的程序逻辑。 PL/SQL支持DML命令和SQL的事务控制语句。DDL在PL/SQL中不被支持，这就  
意味作在PL/SQL程序块中不能创建表或其他任何对象。较好的PL/SQL程序设计是在PL/SQL块中  
使用象DBMS\_SQL这样的内建包或执行EXECUTE IMMEDIATE命令建立动态SQL来执行DDL命令，  
　下面我们将讨论各种用于访问ORACLE数据库的DDL和TCL语句。  
　查询  
　SELECT语句用于从数据库中查询数据，当在PL/SQL中使用SELECT语句时，要与INTO子  
句一起使用，查询的返回值被赋予INTO子句中的变量，变量的声明是在DELCARE中。SELECT  
　　SELECT \[DISTICT|ALL\]\{\*|column\[,column,...\]\}

INTO (variable\[,variable,...\] |record)  
　　FROM \{table|(sub-query)\}\[alias\]  
　　WHERE............  
　PL/SQL中SELECT语句只返回一行数据。如果超过一行数据，那么就要使用显式游标（  
对游标的讨论我们将在后面进行），INTO子句中要有与SELECT子句中相同列数量的变量。INTO  
　%TYPE属性  
　在PL/SQL中可以将变量和常量声明为内建或用户定义的数据类型，以引用一个列名，  
同时继承他的数据类型和大小。这种动态赋值方法是非常有用的，比如变量引用的列的数据类  
型和大小改变了，如果使用了%TYPE,那么用户就不必修改代码，否则就必须修改代码。  
例：  
　　v\_empno SCOTT.EMP.EMPNO%TYPE;  
　　v\_salary EMP.SALARY%TYPE;  
　不但列名可以使用%TYPE,而且变量、游标、记录，或声明的常量都可以使用%TYPE。  
　　DELCARE  
　　V\_A NUMBER(5):=10;  
　　V\_B V\_A%TYPE:=15;  
　　V\_C V\_A%TYPE;  
　　BEGIN  
　　DBMS\_OUTPUT.PUT\_LINE  
　　('V\_A='||V\_A||'V\_B='||V\_B||'V\_C='||V\_C);  
　　END  
　　SQL>/  
　　V\_A=10 V\_B=15 V\_C=  
　　PL/SQL procedure successfully completed.

其他DML语句  
　其它操作数据的DML语句是:INSERT、UPDATE、DELETE和LOCK TABLE,这些语句在  
PL/SQL中的语法与在SQL中的语法相同。我们在前面已经讨论过DML语句的使用这里就不再重  
复了。在DML语句中可以使用任何在DECLARE部分声明的变量，如果是嵌套块，那么要注意变  
　例：  
　　CREATE OR REPLACE PROCEDURE FIRE\_EMPLOYEE (pempno in number)  
AS  
　v\_ename EMP.ENAME%TYPE;  
BEGIN  
　SELECT ename INTO v\_ename  
　FROM emp  
　WHERE empno=p\_empno;  
　INSERT INTO FORMER\_EMP(EMPNO,ENAME)  
　VALUES (p\_empno,v\_ename);  
　DELETE FROM emp  
　WHERE empno=p\_empno;  
　UPDATE former\_emp  
　SET date\_deleted=SYSDATE  
　WHERE empno=p\_empno;  
　EXCEPTION  
　　WHEN NO\_DATA\_FOUND THEN  
　　DBMS\_OUTPUT.PUT\_LINE('Employee Number Not Found!');  
END  
　DML语句的结果  
　当执行一条DML语句后，DML语句的结果保存在四个游标属性中，这些属性用于控制程  
序流程或者了解程序的状态。当运行DML语句时，PL/SQL打开一个内建游标并处理结果，游标  
是维护查询结果的内存中的一个区域，游标在运行DML语句时打开，完成后关闭。隐式游标只  
使用SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT三个属性.SQL%FOUND,SQL%NOTFOUND是布尔值，  
　SQL%FOUND和SQL%NOTFOUND  
　在执行任何DML语句前SQL%FOUND和SQL%NOTFOUND的值都是NULL,在执行DML语句后，  
　. TRUE :INSERT  
　. TRUE :DELETE和UPDATE，至少有一行被DELETE或UPDATE.  
　. TRUE :SELECT INTO至少返回一行  
　当SQL%FOUND为TRUE时,SQL%NOTFOUND为FALSE。  
　SQL%ROWCOUNT  
　在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL,对于SELECT INTO语句，如果  
执行成功，SQL%ROWCOUNT的值为1,如果没有成功，SQL%ROWCOUNT的值为0，同时产生一个异常  
　SQL%ISOPEN  
　SQL%ISOPEN是一个布尔值，如果游标打开，则为TRUE, 如果游标关闭，则为FALSE.对  
于隐式游标而言SQL%ISOPEN总是FALSE，这是因为隐式游标在DML语句执行时打开，结束时就  
　事务控制语句  
　事务是一个工作的逻辑单元可以包括一个或多个DML语句，事物控制帮助用户保证数  
据的一致性。如果事务控制逻辑单元中的任何一个DML语句失败，那么整个事务都将回滚，在  
PL/SQL中用户可以明确地使用COMMIT、ROLLBACK、SAVEPOINT以及SET TRANSACTION语句。  
　COMMIT语句终止事务，永久保存数据库的变化，同时释放所有LOCK,ROLLBACK终止现  
行事务释放所有LOCK，但不保存数据库的任何变化,SAVEPOINT用于设置中间点，当事务调用过  
多的数据库操作时，中间点是非常有用的，SET TRANSACTION用于设置事务属性，比如  
　显式游标  
　当查询返回结果超过一行时，就需要一个显式游标，此时用户不能使用select into  
语句。PL/SQL管理隐式游标，当查询开始时隐式游标打开，查询结束时隐式游标自动关闭。显  
式游标在PL/SQL块的声明部分声明，在执行部分或异常处理部分打开，取数据,关闭。下表显  
　表1 隐式游标和显式游标  
　　\[\[The No.1 Picture.\]\]  
　　使用游标  
　这里要做一个声明，我们所说的游标通常是指显式游标，因此从现在起没有特别指明  
的情况，我们所说的游标都是指显式游标。要在程序中使用游标，必须首先声明游标。  
　声明游标  
　语法：  
　　CURSOR cursor\_name IS select\_statement;

在PL/SQL中游标名是一个未声明变量，不能给游标名赋值或用于表达式中。  
　例：  
　　DELCARE  
　　CURSOR C\_EMP IS SELECT empno,ename,salary

FROM emp 　　WHERE salary>2000  
　　ORDER BY ename;  
　　........  
　　BEGIN  
　在游标定义中SELECT语句中不一定非要表可以是视图，也可以从多个表或视图中选择  
　打开游标  
　使用游标中的值之前应该首先打开游标，打开游标初始化查询处理。打开游标的语法  
　　OPEN cursor\_name  
　cursor\_name是在声明部分定义的游标名。  
　例：  
　　OPEN C\_EMP;  
　关闭游标  
　语法：  
　　CLOSE cursor\_name  
　例：  
　　CLOSE C\_EMP;  
　从游标提取数据  
　从游标得到一行数据使用FETCH命令。每一次提取数据后，游标都指向结果集的下一  
　　FETCH cursor\_name INTO variable\[,variable,...\]  
　对于SELECT定义的游标的每一列，FETCH变量列表都应该有一个变量与之相对应，变  
　例：  
　　SET SERVERIUTPUT ON  
　　DECLARE  
　　v\_ename EMP.ENAME%TYPE;  
　　v\_salary EMP.SALARY%TYPE;  
　　CURSOR c\_emp IS SELECT ename,salary FROM emp;

BEGIN  
　　OPEN c\_emp;  
　　FETCH c\_emp INTO v\_ename,v\_salary;  
　　DBMS\_OUTPUT.PUT\_LINE('Salary of Employee'|| v\_ename  
　　||'is'|| v\_salary);  
　　FETCH c\_emp INTO v\_ename,v\_salary;  
　　DBMS\_OUTPUT.PUT\_LINE('Salary of Employee'|| v\_ename  
　　||'is'|| v\_salary);  
　　FETCH c\_emp INTO v\_ename,v\_salary;  
　　DBMS\_OUTPUT.PUT\_LINE('Salary of Employee'|| v\_ename  
　　||'is'|| v\_salary);  
　　CLOSE c\_emp;  
　　END  
　这段代码无疑是非常麻烦的，如果有多行返回结果，可以使用循环并用游标属性为结  
束循环的条件，以这种方式提取数据，程序的可读性和简洁性都大为提高，下面我们使用循环  
　　SET SERVERIUTPUT ON  
　　DECLARE  
　　v\_ename EMP.ENAME%TYPE;  
　　v\_salary EMP.SALARY%TYPE;  
　　CURSOR c\_emp IS SELECT ename,salary FROM emp;

BEGIN 
　　OPEN c\_emp; 
　　LOOP 
　　FETCH c\_emp INTO v\_ename,v\_salary; 
　　EXIT WHEN c\_emp%NOTFOUND; 
　　DBMS\_OUTPUT.PUT\_LINE('Salary of Employee'|| v\_ename 
　　||'is'|| v\_salary); 
　　END 
　记录变量 
　定义一个记录变量使用TYPE命令和%ROWTYPE，关于%ROWsTYPE的更多信息请参阅相关 
　记录变量用于从游标中提取数据行，当游标选择很多列的时候，那么使用记录比为每 
　当在表上使用%ROWTYPE并将从游标中取出的值放入记录中时，如果要选择表中所有列 
，那么在SELECT子句中使用\*比将所有列名列出来要安全得多。 
　例： 
　　SET SERVERIUTPUT ON 
　　DECLARE 
　　R\_emp EMP%ROWTYPE; 
　　CURSOR c\_emp IS SELECT \* FROM emp; 
　　BEGIN 
　　OPEN c\_emp; 
　　LOOP 
　　FETCH c\_emp INTO r\_emp; 
　　EXIT WHEN c\_emp%NOTFOUND; 
　　DBMS\_OUT.PUT.PUT\_LINE('Salary of Employee'||r\_emp.ename||'is'|| r\_emp.salary); 
　　END LOOP; 
　　CLOSE c\_emp; 
　　END; 
　%ROWTYPE也可以用游标名来定义，这样的话就必须要首先声明游标： 
　　SET SERVERIUTPUT ON 
　　DECLARE 
　　CURSOR c\_emp IS SELECT ename,salary FROM emp; 
　　R\_emp c\_emp%ROWTYPE; 
　　BEGIN 
　　OPEN c\_emp; 
　　LOOP 
　　FETCH c\_emp INTO r\_emp; 
　　EXIT WHEN c\_emp%NOTFOUND; 
　　DBMS\_OUT.PUT.PUT\_LINE('Salary of Employee'||r\_emp.ename||'is'|| r\_emp.salary); 
　　END LOOP; 
　　CLOSE c\_emp; 
　　END; 
　带参数的游标 
　与存储过程和函数相似，可以将参数传递给游标并在查询中使用。这对于处理在某种 
条件下打开游标的情况非常有用。它的语法如下： 
　　CURSOR cursor\_name\[(parameter\[,parameter\],...)\] IS select\_statement; 
　定义参数的语法如下： 
　　Parameter\_name \[IN\] data\_type\[\{:=|DEFAULT\} value\] 
　与存储过程不同的是，游标只能接受传递的值，而不能返回值。参数只定义数据类型 
　另外可以给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。游标 
中定义的参数只是一个占位符，在别处引用该参数不一定可靠。 
　在打开游标时给参数赋值，语法如下： 
　　OPEN cursor\_name\[value\[,value\]....\]; 
　参数值可以是文字或变量。 
　例： 
　　DECALRE 
　　CURSOR c\_dept IS SELECT \* FROM dept ORDER BY deptno; 
　　CURSOR c\_emp (p\_dept VARACHAR2) IS 
　　SELECT ename,salary 
　　FROM emp 
　　WHERE deptno=p\_dept 
　　ORDER BY ename 
　　r\_dept DEPT%ROWTYPE; 
　　v\_ename EMP.ENAME%TYPE; 
　　v\_salary EMP.SALARY%TYPE; 
　　v\_tot\_salary EMP.SALARY%TYPE; 
　　BEGIN 
　　OPEN c\_dept; 
　　LOOP 
　　FETCH c\_dept INTO r\_dept; 
　　EXIT WHEN c\_dept%NOTFOUND; 
　　DBMS\_OUTPUT.PUT\_LINE('Department:'|| r\_dept.deptno||'-'||r\_dept.dname); 
　　v\_tot\_salary:=0; 
　　OPEN c\_emp(r\_dept.deptno); 
　　LOOP 
　　FETCH c\_emp INTO v\_ename,v\_salary; 
　　EXIT WHEN c\_emp%NOTFOUND; 
　　DBMS\_OUTPUT.PUT\_LINE('Name:'|| v\_ename||' salary:'||v\_salary); 
　　v\_tot\_salary:=v\_tot\_salary+v\_salary; 
　　END LOOP; 
　　CLOSE c\_emp; 
　　DBMS\_OUTPUT.PUT\_LINE('Toltal Salary for dept:'|| v\_tot\_salary); 
　　END LOOP; 
　　CLOSE c\_dept; 
　　END; 
　　游标FOR循环 
　在大多数时候我们在设计程序的时候都遵循下面的步骤： 
　1、打开游标 
　2、开始循环 
　3、从游标中取值 
　4、检查那一行被返回 
　5、处理 
　6、关闭循环 
　7、关闭游标 
　可以简单的把这一类代码称为游标用于循环。但还有一种循环与这种类型不相同，这 
就是FOR循环，用于FOR循环的游标按照正常的声明方式声明，它的优点在于不需要显式的打开 
、关闭、取数据，测试数据的存在、定义存放数据的变量等等。游标FOR 循环的语法如下： 
　　FOR record\_name IN 
　　(corsor\_name\[(parameter\[,parameter\]...)\] 
　　| (query\_difinition) 
　　LOOP 
　　statements 
　　END LOOP; 
　下面我们用for循环重写上面的例子： 
　　DECALRE 
　　CURSOR c\_dept IS SELECT deptno,dname FROM dept ORDER BY deptno; 
　　CURSOR c\_emp (p\_dept VARACHAR2) IS 
　　SELECT ename,salary 
　　FROM emp 
　　WHERE deptno=p\_dept 
　　ORDER BY ename 
　　v\_tot\_salary EMP.SALARY%TYPE; 
　　BEGIN 
　　FOR r\_dept IN c\_dept LOOP 
　　DBMS\_OUTPUT.PUT\_LINE('Department:'|| r\_dept.deptno||'-'||r\_dept.dname); 
　　v\_tot\_salary:=0; 
　　FOR r\_emp IN c\_emp(r\_dept.deptno) LOOP 
　　DBMS\_OUTPUT.PUT\_LINE('Name:'|| v\_ename||' salary:'||v\_salary); 
　　v\_tot\_salary:=v\_tot\_salary+v\_salary; 
　　END LOOP; 
　　DBMS\_OUTPUT.PUT\_LINE('Toltal Salary for dept:'|| v\_tot\_salary); 
　　END LOOP; 
　　END; 
　在游标FOR循环中使用查询 
　在游标FOR循环中可以定义查询，由于没有显式声明所以游标没有名字，记录名通过 
　　DECALRE 
v\_tot\_salary EMP.SALARY%TYPE; 
　　BEGIN 
FOR r\_dept IN (SELECT deptno,dname FROM dept ORDER BY deptno) LOOP 
　DBMS\_OUTPUT.PUT\_LINE('Department:'|| r\_dept.deptno||'-'||r\_dept.dname); 
　v\_tot\_salary:=0; 
　FOR r\_emp IN (SELECT ename,salary 
　　FROM emp 
　　WHERE deptno=p\_dept 
　　ORDER BY ename) LOOP 
　DBMS\_OUTPUT.PUT\_LINE('Name:'|| v\_ename||' salary:'||v\_salary); 
　v\_tot\_salary:=v\_tot\_salary+v\_salary; 
　END LOOP; 
　DBMS\_OUTPUT.PUT\_LINE('Toltal Salary for dept:'|| v\_tot\_salary); 
END LOOP; 
　　END; 
　游标中的子查询 
　语法如下： 
　　CURSOR C1 IS SELECT \* FROM emp 
　　WHERE deptno NOT IN (SELECT deptno 
　　FROM dept 
　　WHERE dname!='ACCOUNTING'); 
　可以看出与SQL中的子查询没有什么区别。 
　游标中的更新和删除 
　在PL/SQL中依然可以使用UPDATE和DELETE语句更新或删除数据行。显式游标只有在需 
要获得多行数据的情况下使用。PL/SQL提供了仅仅使用游标就可以执行删除或更新记录的方法 
　UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作 
的表中取出的最近的数据。要使用这个方法，在声明游标时必须使用FOR UPDATE子串，当对话 
使用FOR UPDATE子串打开一个游标时，所有返回集中的数据行都将处于行级（ROW-LEVEL)独占 
式锁定，其他对象只能查询这些数据行，不能进行UPDATE、DELETE或SELECT...FOR UPDATE操 
　语法： 
　　FOR UPDATE \[OF \[schema.\]table.column\[,\[schema.\]table.column\].. 
　　\[nowait\] 
　在多表查询中，使用OF子句来锁定特定的表,如果忽略了OF子句，那么所有表中选择 
的数据行都将被锁定。如果这些数据行已经被其他会话锁定，那么正常情况下ORACLE将等待， 
　在UPDATE和DELETE中使用WHERE CURRENT OF子串的语法如下： 
　　WHERE\{CURRENT OF cursor\_name|search\_condition\} 
　例： 
　　DELCARE 
　　CURSOR c1 IS SELECT empno,salary 
　　FROM emp 
　　WHERE comm IS NULL 
　　FOR UPDATE OF comm; 
　　v\_comm NUMBER(10,2); 
　　BEGIN 
　　FOR r1 IN c1 LOOP 
　　IF r1.salary<500 THEN 
　　v\_comm:=r1.salary\*0.25; 　　ELSEIF r1.salary<1000 THEN 
　　v\_comm:=r1.salary\*0.20; 　　ELSEIF r1.salary<3000 THEN 
　　v\_comm:=r1.salary\*0.15; 
　　ELSE 
　　v\_comm:=r1.salary\*0.12; 
　　END IF; 
　　UPDATE emp; 
　　SET comm=v\_comm 
　　WHERE CURRENT OF c1l; 
　　END LOOP; 
　　END

附\[WHERE CURRENT OF \]:

where   current   of   子句只能在定义游标的时候使用了   for   update语句才可以使用。   
        for   v\_sor   in   t\_sor   loop    
        update   aa   set   value='3'   where   current   of   v\_sor;       --已限制了条件了，更新只是当前记录集    
        end   loop;     
      上面的语句中   v\_sor   是   t\_sor中的一行数据，更新的时候应该用   current   of   t\_sor吧，因为v\_sor只是一个隐式游标，它本身不是通过   for   update定义的，只是代表了循环中t\_sor的当前记录。这样，你的update语句或者delete语句的作用范围就只在你循环的当前行的范围中了。    
      要注意的是，用for   update定义的游标会让数据库对涉及的行加锁，别的会话如果要访问该游标中的行便会进入等待状态。你也可以明确指明要加锁的列，用   for   update   of   "列名"     就可以了。    
      如果你的select   for   update选中的行已经被别的会话加锁的话,会话就需要等待解锁，如果别的会话一直不解锁，那么你的select就会一直等待下去，如果你不想等,只需在for   update后面加上nowait就可以解决这个问题了,这样你的选择会立即返回。

[Oracle]: http://www.cnblogs.com/chuncn/archive/2009/01/29/1381291.html
[oracle _]: http://www.cnblogs.com/happyday56/archive/2007/07/05/806830.html
[None.gif]: /images/20220724/d7beeba8447949f2bd8cdbd91379b630.png
[ExpandedBlockStart.gif]: /images/20220724/7afc43f48a3045d3b814d6b5cf932bed.png
[InBlock.gif]: /images/20220724/ff9574e1e3a04ea4942cfe39069a3cfe.png
[ExpandedSubBlockStart.gif]: /images/20220724/daa54b911f684f8cb16d6211eb4cbe8b.png
[ExpandedSubBlockEnd.gif]: /images/20220724/bc433f59647942cda82e65324e905d6e.png
[ExpandedBlockEnd.gif]: /images/20220724/96a2d818814e48ac956df0f70fe90392.png
[sg_trans.gif]: /images/20220724/ba717981c88441e58b2daa1ce7caddaa.png