PL/SQL 基础---数据交互

PL/SQL 基础—数据交互

主要讨论PL/SQL中数据任何在PL/SQL引擎和SQL引擎之间进行交互；

一、基本数据类型

包括NUMBER、VARCHAR2、BOOLEAN、CHAR、DATE，Record等，这些数据类型在SQL和PL/SQL中可以直接转换，并且一一对应；

使用方式

    --获取数据    SELECT ... INTO v1,v2...    --写入数据    INSERT INTO TABLE  VALUES(v1,v2);    UPDATE TABLE set COLUMN = v1 WHERE C=v2; 

--获取数据DECLARE  v_employee_id  NUMBER(6);  v_last_name    VARCHAR(30);BEGIN  SELECT e.EMPLOYEE_ID, e.LAST_NAME    INTO  v_employee_id,v_last_name  FROM EMPLOYEES e  WHERE e.EMPLOYEE_ID = 100;  DBMS_OUTPUT.put_line(v_employee_id);  DBMS_OUTPUT.put_line(v_last_name);END;--写入数据--写入DECLARE   v_employee_id  NUMBER(6);   v_last_name    VARCHAR(30);BEGIN  v_employee_id := 100;  v_last_name   := 'NEW NAME';  UPDATE EMPLOYEES SET LAST_NAME = v_last_name   WHERE EMPLOYEE_ID = v_employee_id;END;--SELECT * FROM EMPLOYEES e WHERE e.EMPLOYEE_ID = 100;

二、复杂数据类型(集合)

包括TABLE, VARRAY等集合类型，这些数据类型需要程序员逐条处理或者使用Oracle提供的批量处理的关键字来完成(BULK COLLECT 和 FORALL（遍历数组）)；

使用方式

--获取数据 BULK INTO 写入数组DECLARE      TYPE T_EMP_TABLE IS TABLE OF EMPLOYEES%ROWTYPE;     TYPE T_NAME_ARRAY IS VARRAY(100) OF VARCHAR(30);     v_emp  T_EMP_TABLE;     v_name T_NAME_ARRAY;BEGIN  --BULK COLLECT INTO TABLE  SELECT *   --RETURNING col1,col2 选取指定字段  BULK COLLECT INTO v_emp  FROM EMPLOYEES e;  FOR i IN 1..v_emp.COUNT LOOP      DBMS_OUTPUT.put_line(v_emp(i).LAST_NAME);  END LOOP;  --BULK COLLECT INTO VARRAY  SELECT e.LAST_NAME  BULK COLLECT INTO v_name  FROM EMPLOYEES e;  FOR i IN 1..v_name.COUNT LOOP      DBMS_OUTPUT.put_line(v_name(i));  END LOOP;END; --写入数据 FORALL遍历DECLARE       TYPE T_EMP_TABLE IS TABLE OF EMPLOYEES%ROWTYPE;       v_emp  T_EMP_TABLE;BEGIN       --BULK COLLECT INTO TABLE      SELECT *       BULK COLLECT INTO v_emp      FROM EMPLOYEES e;      FOR i IN 1..v_emp.COUNT LOOP          v_emp(i).LAST_NAME := 'RENAME';      END LOOP;      FORALL i IN 1..v_emp.COUNT              UPDATE EMPLOYEES e SET e.LAST_NAME = v_emp(i).LAST_NAME WHERE v_emp(i).EMPLOYEE_ID = e.EMPLOYEE_ID;END;--SELECT * FROM EMPLOYEES e;

隐私游标 和 显式游标

游标是一个私有的SQL工作区域， Oracle数据库中有两种游标，分别是隐式游标和显式游标，隐式游标不易被用户和程序员察觉和意识到，实际上Oracle服务器使用隐式游标来解析和执行我们提交的SQL语句； 而显式游标是程序员在程序中显式声明的；通常我们说的游标均指显式游标。

隐式游标 – SQL
在使用DML语句（INSERT, UPDATE, or DELETE）或者SELECT INTO 返回单行记录时，Oracle会隐式的完成游标的创建、开启、关闭等操作 ；

显式游标 – 自定义游标

显式游标不对应DML语句，只对应SELECT语句查询结果，且需要提前声明所对应的SELECT语句；

显式游标声明

CURSOR cursor_name [ ( [ parameter [, parameter ...] ) ]   [ RETURN return_specification ]   IS SELECT_statement       [FOR UPDATE [OF [column list]];

示例

CREATE OR REPLACE FUNCTION jealousy_level (    NAME_IN   IN   friends.NAME%TYPE) RETURN NUMBERAS    CURSOR jealousy_cur    IS       SELECT location FROM friends        WHERE NAME = UPPER (NAME_IN);    jealousy_rec   jealousy_cur%ROWTYPE;   retval         NUMBER;BEGIN   OPEN jealousy_cur;   FETCH jealousy_cur INTO jealousy_rec;   IF jealousy_cur%FOUND   THEN      IF jealousy_rec.location = 'PUERTO RICO'         THEN retval := 10;      ELSIF jealousy_rec.location = 'CHICAGO'         THEN retval := 1;      END IF;   END IF;   CLOSE jealousy_cur;   RETURN level_out;END;

游标操作

参数名 作用 OPEN 打开游标，之后才能FETCH获取记录 FETCH cursor INTO v1,v2 获取数据，保存到变量中 %ATTRIBUTE 获取游标参数值（状态） CLOSE 关闭游标

游标参数

参数名 作用 %FOUND FETCH有结果为TRUE, 否则为FALSE %NOTFOUND FETCH无结果为TRUE, 否则为FALSE %ROWCOUNT 结果集记录数 %ISOPEN 已执行OPEN为TRUE, 否则为FALSE

游标概念的描述（转）
游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。
游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。
游标有两种类型：显式游标和隐式游标。在前述程序中用到的SELECT…INTO…查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。

游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。