7.PL_SQL——在PL_SQL程序中内嵌查询语句、DML语句、事物处理语句和游标属性

wxin

　　在PL/SQL中可以使用的SQL语句主要有以下几类：
　　SELECT 查询语句，DML语句，Transaction 事物处理语句以及游标的属性，本文将对这几类语句在PL/SQL中的用法逐一介绍。
　　一、查询语句—SELECT
　　SELECT 语句用来查询一条或多条语句。虽然SELECT 语句也属于DML语句，但SELECT是只读的，所以单独列出。
　　在PL/SQL中使用SELECT 语句的格式如下：
　　
　　SELECT select_list
　　INTO {variable_name[,variable_name]...
　　| record_name}
　　FROM table
　　[WHERE condition];
　　}
　　
　　这里INTO是必选项，是指将查询出来的结果导入到变量中，因此INTO 后面跟的变量个数、数据类型和顺序必须和SELECT 后面跟的选项个数、数据类型以及顺序一致。
　　如果使用record这种变量类型，可以一次性导入而不需要逐一指明（后文将详解）。
　　要使用INTO，查询结果只能有一条（行）记录，否则会发生混淆。下面看这个例子：
　　
　　SQL> edit
　　DECLARE
--      v_fname VARCHAR2(25);　　
        v_fname employees.first_name%TYPE;
　　
BEGIN
　　
        SELECT first_name INTO v_fname
　　
        FROM employees WHERE employee_id = 200;
　　

　　
        DBMS_OUTPUT.PUT_LINE('First Name is: '|| v_fname);
　　
END;
　　
/
　　
　　SQL> /
FirstName is: Jennifer　　
PL/SQLprocedure successfully completed.
　　如果现在要查询的employee_id 大于等于200，那么会出现什么结果呢？
　　SQL> edit
DECLARE　　
       v_fname employees.first_name%TYPE;
　　
BEGIN
　　
        SELECT first_name INTO v_fname
　　
        FROM employees WHERE employee_id >=200;
　　

　　
        DBMS_OUTPUT.PUT_LINE('First Name is: '|| v_fname);
　　
END;
　　
/
　　SQL> /
DECLARE　　
*
　　
ERRORat line 1:
　　
ORA-01422:exact fetch returns more than requested number of rows
　　
ORA-06512:at line 5
　　
-- 报错了，提示查询结果多余1条
　　
　　现在再来看一个查询多列的例子（注意，仍然是1行结果）：
　　SQL> edit
DECLARE　　
        v_emp_hiredateemployees.hire_date%TYPE;
　　
        v_emp_salary   employees.salary%TYPE;
　　
BEGIN
　　
        SELECT hire_date, salary
　　
        INTO v_emp_hiredate, v_emp_salary
　　
                -- 查询了两列记录，需要注意的是，数据个数、数据类型和顺序必须一一对应
　　
        FROM employees
　　
        WHERE employee_id = 100;
　　

　　
        DBMS_OUTPUT.PUT_LINE('Hire date is :'|| v_emp_hiredate);
　　
        DBMS_OUTPUT.PUT_LINE('Salary is : ' ||v_emp_salary);
　　
END;
　　
/
　　SQL> /
Hiredate is : 17-JUN-03　　
Salaryis : 24000
　　
PL/SQLprocedure successfully completed.
　　除了基本的查询函数外，一些聚合函数也可以直接在PL/SQL中使用，只要保证这些聚合函数的运行结果仍然是一行记录即可：
　　SQL> edit
DECLARE　　
        v_sum_sal NUMBER(10,2);
　　
        v_deptno  NUMBER NOT NULL := 60;
　　
BEGIN
　　
        SELECT SUM(salary) -- group function
　　
                                -- 使用了SQL中的聚合函数SUM()
　　
                                --SUM（）的结果仍然是一行
　　
        INTO  v_sum_sal
　　
                                -- 将SUM()函数的运行结果导入到变量v_sum_sal中
　　
        FROM  employees
　　
        WHERE department_id = v_deptno;
　　
        DBMS_OUTPUT.PUT_LINE('The sum of salaryis '|| v_sum_sal);
　　
END;
　　SQL> /
Thesum of salary is 28800　　
PL/SQLprocedure successfully completed.
　　如果要查询多条记录并导入变量，则需要涉及游标的概念，游标将在后文中详解。
　　二、在PL/SQL中使用DML语句
　　PL/SQL中也可以使用DML语句来修改数据库中的表，DML语句主要指的是INSERT, UPDATE,DELETE和MERGE等语句，其中INSERT,UPDATE,DELETE  多半只涉及一张表，而MERGE则涉及两张表，如果两张表的数据不一致，就使用其中一张表来更新另外一张。

　　下面来看DML语句中INSERT使用的例子：
　　SQL> edit
BEGIN　　
        INSERT INTO employees
　　
        (employee_id, first_name,last_name,email,
　　
        hire_date,job_id,salary)
　　
        VALUES(employees_seq.NEXTVAL,'Ruth','Cores',       'RCORES',CURRENT_DATE,'AD_ASST',4000);
　　
END;
　　
/
　　SQL> /
PL/SQLprocedure successfully completed.　　SQL> select * from employees wherefirst_name like 'Ruth';
EMPLOYEE_ID FIRST_NAME           LAST_NAME　　
------------------------------- -------------------------
　　
EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY
　　
--------------------------------------------- --------- ---------- ----------
　　
COMMISSION_PCTMANAGER_ID DEPARTMENT_ID
　　
------------------------ -------------
　　
        207 Ruth                 Cores
　　
RCORES                                                                14-AUG-14 AD_ASST          4000
　　再来看一个UPDATE的例子：
　　SQL> edit
DECLARE　　
        sal_increase employees.salary%TYPE :=800;
　　
BEGIN
　　
        UPDATE employees
　　
        SET   salary = salary + sal_increase
　　
        WHERE job_id = 'ST_CLERK';
　　
END;
　　
/
　　SQL> /
PL/SQLprocedure successfully completed.　　UPDATE语句中也可以使用INTO关键字，来看下面这个例子：
　　SQL> edit
DECLARE　　
       myname emp.last_name%TYPE;
　　
        mysal emp.salary%TYPE;
　　
BEGIN
　　
        FOR rec IN (SELECT * FROM emp)
　　
        LOOP
　　
-- 这里不用理解loop循环的含义，后文会详解loop的用法
　　
            UPDATE emp SET salary = salary *1.5
　　
            WHERE employee_id = rec.employee_id
　　
            RETURNING salary, last_name
　　
            INTO mysal, myname;
　　
-- 在UPDATE语句中使用INTO，将更新后的值导入到变量中
　　
            DBMS_OUTPUT.PUT_LINE('New salaryfor ' || myname || ' = ' || mysal);
　　
        END LOOP;
　　
END;
　　
/
　　SQL> /
Newsalary for OConnell = 3900　　
Newsalary for Dellinger = 5100
　　
Newsalary for Cabrio = 4500
　　
Newsalary for McCain = 4800
　　
Newsalary for Jones = 4200
　　
Newsalary for Walsh = 4650
　　
Newsalary for Feeney = 4500
　　
PL/SQLprocedure successfully completed.
　　在PL/SQL中使用DELETE也很简单：
　　SQL> select * from tt；
  -- 首先使用SQL语句查询所有的信息　　
        ID
　　
----------
　　
         0
　　
         1
　　SQL> edit
DECLARE　　
        v_id tt.id%TYPE := 1;
　　
BEGIN
　　
        DELETE FROM tt
　　
        WHERE id = v_id;
　　
END;
　　
/
　　SQL> /
PL/SQL procedure successfully completed.　　SQL> select * from tt;
        ID　　
----------
　　
         0
　　
-- 已经删除了一条记录
　　
　　INSERT, UPDATE,DELETE  语句基本上可以直接在PL/SQL中使用，和SQL语言并无差别。MERGE这种操作使用不多，下面举一个例子来简单介绍一下：
　　1. 先创建表ttt，并插入三条记录
　　SQL> create table ttt(t_id int,
  2 t_name varchar2(20));　　
Tablecreated.
　　SQL> insert into ttt values(0,'a');
1row created.　　SQL> insert into ttt values(1,'b');
1row created.　　SQL> insert into ttt values(2,'c');
1row created.　　SQL> select * from ttt;
      T_ID T_NAME　　
------------------------------
　　
         0 a
　　
         1 b
　　
         2 c
　　2. 再创建表t2，插入一条记录
　　SQL> create table t2(t2_id int,
　　2  t2_name varchar2(20));
　　
Table created.
　　SQL> insert into t2 values(0,'d');
1row created.　　SQL> insert into t2 values(3,'e');
1row created.　　SQL> select * from t2;
     T2_ID T2_NAME　　
------------------------------
　　
         0 d
　　
         3 e
　　3. 现在在PL/SQL中使用MERGE的方法用t2的数据来更新ttt的数据
　　SQL> edit
BEGIN　　
MERGEINTO ttt
　　
      USING t2
　　
      ON (ttt.t_id = t2.t2_id)
　　
     WHEN MATCHED THEN
　　
        UPDATE SET ttt.t_name = t2.t2_name
　　
     WHEN NOT MATCHED THEN
　　
        INSERT VALUES(t2.t2_id, t2.t2_name);
　　
END;
　　
/
　　SQL> select * from ttt;
-- 表ttt已经用t2合并更新了　　
T_ID T_NAME
　　
------------------------------
　　
         0 d
　　
         1 b
　　
         2 c
　　
         3 e
　　DML语句操作数据库时，还可以使用Records来同时插入或更新一组数据，如下面这两个例子：
DECLARE　　
        my_book books%ROWTYPE;
　　
BEGIN
　　
        my_book.isbn := '1-56592-335-9';
　　
        my_book.title := 'Oracle PL/SQLProgramming 5th';
　　
        my_book.summary := 'General userguide';
　　
        my_book.author := 'Feusertein, Steven';
　　
        my_book.page_count := 1000;
　　

　　
        INSERT INTO books VALUES my_book;
　　
END;
　　
/
DECLARE　　
        my_book books%ROWTYPE;
　　
BEGIN
　　
        my_book.isbn := '1-56592-335-9';
　　
        my_book.title := 'Oracle PL/SQLProgramming 5th';
　　
        my_book.summary := 'General userguide';
　　
        my_book.author := 'Feusertein, Steven';
　　
        my_book.page_count := 1000;
　　

　　
        UPDATE books SET ROW = my_book
　　
        WHERE isbin = my_book.isbn;
　　
END;
　　
/
　　这两个例子中各条记录都保存在变量类型为Record的变量my_book中，然后将该变量作为一个整体插入或更新到表中。
　　Records这种变量类型后文中将详解，这里只简要的介绍一下它的基本用法。
　　INDEX等DDL语句不能够直接使用，必须使用动态SQL语句的形式来使用。
　　三、事物控制语句（Transaction control ）
　　事物控制语句主要指的是commit、rollback和savepoint。
　　PL/SQL的Block 和事物（Transaction）之间没有一一对应的关系，可能一个PL/SQL的块已经结束了（如出现异常，退出程序了），但是事物（Tansaction）还没有被提交或者是回滚，就会导致数据的丢失。（相关知识后文会详解）另一方面，可能一个Transaction已经结束了，而PL/SQL 的block还在继续。一个Transaction可以跨越多个Block，如上文中INSERT 和UPDATE的例子中，虽然对表做了修改，但并没有使用commit加以提交，因此Transaction还在继续。

　　现在来看下面这个例子：
　　1. 首先以SYS用户登录：
　　SQL> show user
USERis "SYS"　　SQL> desc v$transaction
Name                                      Null?    Type　　
------------------------------------------------- ----------------------------
　　
ADDR                                              RAW(4)
　　
XIDUSN                                            NUMBER
　　
XIDSLOT                                           NUMBER
　　
XIDSQN                                            NUMBER
　　
XID                                               RAW(8)
　　SQL> select xid,xidusn from v$transaction;
norows selected　　
--当前没有记录
　　
　　2. 现在切换成hr用户
　　SQL> show user
USERis "HR"　　SQL> create table tt (id int);
Tablecreated.　　SQL> insert into tt values(0);
1row created.　　3. 现在再次切换到SYS用户来查看xid这一行的记录
　　SQL> show user
USER is "SYS"　　SQL> select xid,xidusn fromv$transaction
XID                                XIDUSN　　
------------------------------   ----------
　　
040001009B020000                      4
　　4. 现在切换回hr用户，然后提交刚才的事物
　　SQL> show user
USERis "HR"　　SQL> commit;
Commitcomplete.　　SQL> select * from tt;
        ID　　
----------
　　
         0
　　5. 此时再以SYS用户查看动态性能试图（v$transaction），就会发现刚才的记录消失了：
　　SQL> show user
USERis "SYS"　　SQL> select xid,xidusn fromv$transaction;
no rows selected　　五、游标的属性
　　1.、什么是游标
　　如果要对游标Cursor有深入的了解，就必须对Oracle的体系结构有深入的了解，本章只做基本概述。
　　Cursor 是一个指针，指向的是Oracle服务器分配的一块私有内存区，用来处理SQL语句。通常这个私有的内存区在用户session的PGA里面，如果PGA是Dedicated（专有模式），则这块PGA是独立于SGA的；如果是共享服务器模式，那么这个游标是放在SGA里的（以后的文章会介绍PGA,SGA的相关概念）。持有游标就可以访问该游标所指向的内存区域。更多Cursor相关知识可查询Oracle中关于OCI的知识。
　　前文中介绍过，在PL/SQL中使用SELECT INTO语句，可以将查询到的一条结果放到变量中，而使用CURSOR就可以存放多条记录。
　　游标包括隐式游标和显式游标。其中隐式游标的创建、管理都是有Oracle其中隐式游标的创建、管理都是有Oracle 服务器完成的；而显式游标则需要程序员通过一系列的系统调用来声明、打开、执行以及关闭游标，显式游标后文中会详解，目前只涉及到隐式游标。
　　2.游标属性的作用
　　当我们使用UPDATE和DELETE（INSERT不需要）数据后，想知道影响了多少条记录
　　使用游标属性可以查询相关信息。这些主要包括以下三种：
　　A. SQL%FOUND：如果返回布尔型变量 TRUE，表示至少有1条记录被影响到了。
　　B. SQL%NOTFOUND：如果返回布尔型变量TRUE，表示一条记录都没有被影响到。
　　C. SQL%ROWCOUNT:返回的数值是多少，就表示有多少条记录被影响了。
　　下面来看一段使用游标的例子：
　　SQL> select * from ttt;
      T_ID T_NAME　　
------------------------------
　　
         0 a
　　
         1 b
　　
         2 c
　　SQL> edit
DECLARE　　
        test_id ttt.t_id%TYPE :=1;
　　
BEGIN
　　
        DELETE FROM ttt
　　
        WHERE t_id = test_id;
　　
        COMMIT;
　　
        DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || 'rows deleted!');
　　
END;
　　
/
　　SQL> /
1 rows deleted!　　

　　
PL/SQL procedure successfullycompleted.
　　SQL> select * from ttt;
      T_ID T_NAME　　
------------------------------
　　
         0 a
　　
         2 c