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 NUMBER
AS
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语句。游标一旦打开,数据就从数据库中传送到游标变量中,然后应用程序再从游标变量中分解出需要的数据,并进行处理。