PL/SQL学习笔记[4]-PL/SQL块中使用SQL

1 检索单行数据

在PL/SQL块中使用select语句，可以将数据库数据检索到变量中。

当在PL/SQL块中使用select语句时，必须要带有into子句。语法如下：

SELECT select_list

     INTO {variable_name[,variable_name]... | record_name}

FROM table_name

WHERE condition;

其中，select_list为指定查询列；variable_name为接收指定查询列的标量变量名；record_name为接收指定查询列的记录变量名

1.1 使用标量变量接收数据

使用标量变量接收 SELECT 语句的输出结果时，变量个数要与 SELECT 选择项相同，且变量类型和长度要匹配。

DECLARE
   v_name   USER.ename%TYPE;

   v_age    USER.age%TYPE;
BEGIN
   SELECT   name, age
     INTO   v_name, v_age
     FROM   USER
    WHERE   UID = &id;

   DBMS_OUTPUT.put_line ('姓名:' || v_name);

   DBMS_OUTPUT.put_line ('年龄:' || v_age);
END;

1.2 使用记录变量接收数据

使用记录变量接受数据时，记录成员的个数必须与选择列表项个数完全一致，且数据类型和长度要相匹配。上述例子使用记录变量可写成：

DECLARE

        TYPE user_record_type IS RECORD (

            name user.name%TYPE,

            age user.age%TYPE

        );

        user_record user_record_type;

    BEGIN

        SELECT name,age into user_record

        FROM user

        WEHRE uid = &id;

        dbms_output.put_line('姓名:'||user_record.name);

        dbms_output.put_line('年龄:'||user_record.age);

    END;

1.3 嵌入使用 SELECT ... INTO ... FROM ... [WHERE] 语句注意事项

1.3.1 在PL/SQL块中使用 SELECT INTO 语句时，该语句只能返回一条数据;

1.3.2 当3.1不成立时，会触发以下异常:

1.3.2.1 NO_DATA_FOUNT 异常:

该异常发生在 SELECT INTO 语句没有返回任何数据时

1.3.2.2 TOO_MANY_ROWS 异常:

该异常发生在 SELECT INTO 语句没有返回多条数据时

以上两个异常在没有捕获时将会将异常信息传递到调用环境中。

2 操作数据

2.1 插入数据

SQL*Plus 语法 :

INSERT INTO <table> [(column[,column,...])]
VALUES(value[,value,...])

或者

INSERT INTO <table> [(column[,column,...])] SubQuery

PL/SQL中与SQL*Plus INSERT数据相同，只不过在提供数值时需要使用PL/SQL变量。

2.1.1 在PL/SQL中使用INSERT VALUES插入数据

DECLARE
   v_name   USER.name%TYPE;

   v_age    USER.age%TYPE;
BEGIN
   v_name := '&name';

   v_age := &age;

   INSERT INTO USER (name, age)
     VALUES   (v_name, v_age);
END;

2.1.2 在PL/SQL中使用子查询插入数据

DECLARE
   v_deptno   dep.no%TYPE;
BEGIN
   INSERT INTO USER (name, age)
      SELECT   name, age
        FROM   dep
       WHERE   no = v_deptno;
END;

2.2 更新数据

SQL*Plus 语法 :

UPDATE <table|view>

SET <column> = <value> [, <column> = <value>...]

[WHERE <conditon>];

PL/SQL:

DECLARE
   v_name   USER.name%TYPE := '&name';

   v_age    USER.age%TYPE := &age;
BEGIN
   UPDATE   USER
      SET   name = v_name, age = v_age
    WHERE   UID = 1;
END;


DECLARE

   v_uid user.uid%TYPE := &id;

BEGIN

   UPDATE user SET (name,age) = (

      SELECT name,age FROM user_tmp where uid_tmp = v_uid;

    ) WHERE uid = v_uid; 
END; 

2.3 删除数据

SQL*Plus 语法 :

DELETE FROM <table|view> [WHERE <condition>];

PL/SQL:

DECLARE
   v_uid   USER.UID%TYPE := &id;
BEGIN
   DELETE FROM   USER
         WHERE   UID = v_uid;
END;

DECLARE
   v_age   USER.age%TYPE := &age;
BEGIN
   DELETE FROM   USER
         WHERE   UID IN (SELECT   tmp_uid
                           FROM   user_tmp
                          WHERE   age = v_age);
END;

2.4 SQL游标

执行 SELECT、INSERT、UPDATE、DELETE语句时，Oracle Service 会为这些SQL语句分配相应的上下文(Context Area).ORACLE使用上下文解析并执行SQL。而游标是指向上下文区的指针。

ORACLE中，游标包括隐式游标 和显式游标 。

隐式游标又被称作SQL游标，专用于处理 SELECT INTO,INSERT,UPDATE及DELETE语句；显式游标则用于处理多行SELECT语句。

在PL/SQL块中执行INSERT,UPDATE,DELETE时，为了取得DML语句作用的结果，必须要使用SQL游标属性。SQL游标属性包括：

2.4.1 SQL%ISOPEN 属性

该属性用于确定SQL游标是否已经打开。在PL/SQL块中执行SELECT INTO,INSERT,UPDATE及DELETE时，Oracle会隐含的打开游标，并且在执行完成后隐含的关闭游标。

该属性在实际的开发过程中可以不用考虑。

2.4.2 SQL%FOUND 属性

该属性用于确定SQL语句执行是否成功（是否有作用行）。当SQL语句有作用时，其属性为TRUE；否则为FALSE;

DECLARE
   v_uid   USER.UID%TYPE := &id;
BEGIN
   UPDATE   USER
      SET   age = 100
    WHERE   UID = v_uid;

   IF SQL%FOUND
   THEN
      DBMS_OUTPUT.put_line ('语句执行成功');
   ELSE
      DBMS_OUTPUT.put_line ('语句执行失败');
   END IF;
END;

2.4.3 SQL%NOTFOUND 属性

与SQL%FOUND 属性相反。有作用行时为FALSE,无作用行时为TRUE;

DECLARE
   v_uid   USER.UID%TYPE := &id;
BEGIN
   UPDATE   USER
      SET   age = 100
    WHERE   UID = v_uid;

   IF SQL%NOTFOUND
   THEN
      DBMS_OUTPUT.put_line ('用户表中不存在该用户');
   ELSE
      DBMS_OUTPUT.put_line ('语句执行成功');
   END IF;
END;

2.4.4 SQL%ROWCOUNT 属性

该属性用户统计SQL语句的作用行数。如果没有作用行，则返回0;

DECLARE
   v_uid   USER.UID%TYPE := &id;
BEGIN
   UPDATE   USER
      SET   age = 100
    WHERE   UID = v_uid;

   DBMS_OUTPUT.put_line ('修改了' || SQL%ROWCOUNT || '行');
END;

3 事务控制

PL/SQL块中的事务同SQL*Plus直接使用事务语句(COMMIT,ROLLBACK,SAVEPOINT).

• COMMIT : 提交事务
• ROLLBACK : 回滚事务
• SAVEPOINT : 设置事务保存点

DECLARE

        v_uid user.uid%TYPE :=&id;

    BEGIN

        UPDATE SET age = 100 WHERE uid = v_uid;

        COMMIT;

    EXCEPTION

        WHEN OTHERS THEN

            ROLLBACK;

    END;
    
    
    BEGIN

        INSERT INTO user(name,age) VALUEs('name1',20);

        SAVEPOINT u1;

        INSERT INTO user(name,age) VALUEs('name2',30);

        SAVEPOINT u2;

        INSERT INTO user(name,age) VALUEs('name3',40);

        SAVEPOINT u3;

        ROLLBACK TO u2; //回滚到第二条SQL,实际插入两条数据

        COMMIT;

    END;