三、PL/SQL中的SQL

---

一、在PL/SQL中使用DML

本章介绍在PL/SQL语句块中使用SQL语句的基本知识。

使用:=语法进行变量初始操作，本章会介绍如何使用SQL SELECT语句来更新变量的值。然后这些变量可以被用于数据库操作(DML)语句如insert,delete,update中。

在oracle中，事务把一系列SQL语句组成逻辑单元。使用事务的目的是保证数据完整性，每个应用程序(SQL plus,oracle sql developer)都为每次用户登录维护一个数据库会话，在执行commit之前，应用程序所执行的数据库变更操作并不会保存到数据库中。事务中commit之前的语句所做操作都可以被回滚

为加强事务控制，使用savepoint把大型PL/SQL语句拆分多个易于管理的小单元，本章将讨论有关事务控制的基本概念。

1.1 SELECT INTO语句

DECLARE
    v_id NUMBER := &id;
    v_ooag001 ooag_t.ooag001%TYPE;
    v_ooag011 ooag_t.ooag011%TYPE;
BEGIN
    -- 使用 SELECT INTO 语句初始化变量的语句
    SELECT ooag001,ooag011
      INTO v_ooag001,v_ooag011
      FROM ooag_t
     WHERE ooag001 = v_id
       AND ooagent = 70
       AND rownum = 1;
    DBMS_OUTPUT.PUT_LINE(v_ooag001||', '||v_ooag011);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('未查到数据');
END;

1.2 PL/SQL中使用DML

在简单的PL/SQL语句块中，数据定义语言DDL是无效的

DECLARE
    v_ooag001 ooag_t.ooag001%TYPE;
    v_ooag011 ooag_t.ooag011%TYPE;
BEGIN
    -- 使用 SELECT INTO 语句初始化变量的语句
    SELECT ooag001,ooag011
      INTO v_ooag001,v_ooag011
      FROM ooag_t
     WHERE ooag001 = '00261'
       AND ooagent = 70
       AND rownum = 1;
    DBMS_OUTPUT.PUT_LINE(v_ooag001||', '||v_ooag011);
    -- 在PL/SQL中使用DML语句
    UPDATE ooag_t
       SET ooag011 = '张三'
     WHERE ooag001 = v_ooag001;
     -- 使用 SELECT INTO 语句初始化变量的语句
    SELECT ooag001,ooag011
      INTO v_ooag001,v_ooag011
      FROM ooag_t
     WHERE ooag001 = '00261'
       AND ooagent = 70
       AND rownum = 1;
    DBMS_OUTPUT.PUT_LINE(v_ooag001||', '||v_ooag011);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('未查到数据');
END;

1.3 savepoint

在PL/SQL语句块中使用COMMIT、ROLLBACK、SAVEPOINT事务，意味着可以把程序分解成多个可管理的单元。把事务分解为多个更小的元素，可以保证应用程序只会保存正确的数据。在当用户在应用程序中执行DML语句时，其他用户看不到这个变更请求，必须等到COMMIT或ROLLBACK语句。Oracle保证数据的读一致性视图。在执行COMMIT或者ROLLBACK语句之前，所有被插入或者修改的数据都保存在内存中，只适用于当前用户。修改的数据行被当前用户锁定，在解锁之前用户不能进行更新。COMMIT或者ROLLBACK语句会释放这些锁。使用SAVEPOINT命令标识事务的多个保存点。可以更加灵活和容易控制事务。

下例demo演示事务的作用

BEGIN
    -- 将账户ID为100的账户减去100块
    -- step1:
    UPDATE account_t
       SET money = money - 100
     WHERE id = 100;
    -- 将另一个账户加上100
    -- step2:
    UPDATE account_t
       SET money = money + 100
     WHERE id = 101;

如上例，一个账户减100，一个账户加100，如果step1减了100，而另一个账户加100没有成功，那就出问题了。这个时候就需要事务了。有了事务，这两个操作要么都成功，要么都失败。

1.4 COMMIT

当向数据库提交COMMIT语句时，这个事务就结束了，并且如下结果都会发生：

• 事务所做的所有工作都会永久化
• 其他用户可以看到这个事务所做的数据变更
• 该事务拥有的所有锁被释放。
  COMMIT语句的语法如下所示：
  COMMIT [WORK]
  单词WORK是可选的，使用它的目的是改进可读性

1.5 ROLLBACK

当ROLLBACK语句提交到数据库时，这个事务就结束了，并且会出现如下结果：

• 该事务所做的所有工作被摊销，就像没有执行过这个事务一样。
• 该事务拥有的所有锁被释放。
  ROLLBACK语句的语法如下：
  ROLLBACK [WORK]

1.6 SAVEPOINT

使用SAVEPOINT可以实现只有事务的部分工作被撤销。
SAVEPOINT name;
单词name是SAVEPOINT的名称。一旦定义SAVEPOINT，程序就只能回滚到这个SAVEPOINT。ROLLBACK语句的语法如下：
ROLLBAK[WORK] to SAVEPOINT name;
当ROLLBACK to SAVEPOINT提交到数据库时，就会出现如下结果：

• 从SAVEPOINT起的所有工作被撤销。但是，SAVEPOINT仍是活跃的，直到执行完全的COMMIT或者ROLLBACK。在需要时，可以再次回滚。
• 从SAVEPOINT以来SQL语句所有的所有锁和资源被释放。
• 事务没有结束，因为SQL语句还没有最终结束。

BEGIN
    INSERT INTO person
        (id, name, age)
    VALUES
        (1, 'zs', 20);
    SAVEPOINT A;
    INSERT INTO person
        (id, name, age)
    VALUES
        (2, 'ls', 22);
    SAVEPOINT B;
    INSERT INTO person
        (id, name, age)
    VALUES
        (3, 'ww', 25);
    SAVEPOINT C;
    ROLLBACK TO B;   
END;

如果执行 select * from person where id = 3;能看到什么，答案是什么都看不到，因为回滚到了B，只有前面两条insert被提交到了数据库