数据库隐式游标和显示游标

最新推荐文章于 2023-05-08 16:28:12 发布

flqchl

最新推荐文章于 2023-05-08 16:28:12 发布

阅读量1k

点赞数

分类专栏：数据库文章标签： plsql

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

隐式游标：查询单行记录。
Oracle自动在后台使用的游标，用户不参与任何操作！所以对客户是隐藏的不显示的！
特征：
--不能手动的打开，提取，关闭，
--在使用SELECT。。INTO 语句时会自己打开，提取，创建，关闭
通过使用隐式游标属性，可以获得最近执行的SELECT单行语句以及DML语句

显示游标：查询多行记录。
在PL/SQL如何返回进行多行记录并族行进行读取，

显式游标使用步骤
定义游标名称和SELECT语句
通过OPEN语句打开游标
通过FETCH语句获取游标数据
如果有多行，则在循环语句中使用FETCH语句
关闭游标

为什么要使用游标变量
游标变量提供将PL/SQL块中的多行数据传递到外部程序的一种方法。
本质就是传递数据所在的内存地址。
游标变量
游标就是指向PL/SQL结果集的工作区名称，而游标变量就是对这个工作区的引用。
或：游标变量是一种数据类型，里面保存的值就是各个具体的游标 (所对应的内存地址)。

在PL/SQL块中执行SELECT、INSERT、DELETE和UPDATE语句时，ORACLE会在内存中为其分配上下文区（Context Area），即缓冲区。游标是指向该区的一个指针，或是命名一个工作区（Work Area），或是一种结构化数据类型。

在每个用户会话中，可以同时打开多个游标，其数量由数据库初始化参数文件中的OPEN_CURSORS参数定义。

对于不同的 SQL 语句，游标的使用情况不同：

SQL语句	游标
非查询语句	隐式的
结果是单行的查询语句	隐式的或显示的
结果是多行的查询语句	显示的

处理显式游标

例：

[sql] view plain copy print ?

DECLARE
CURSOR c4(dept_id NUMBER, j_id VARCHAR2) --1、声明游标,有参数没有返回值
IS
SELECT first_name f_name, hire_date FROM employees
WHERE department_id = dept_id AND job_id = j_id;
--基于游标定义记录变量，比声明记录类型变量要方便，不容易出错
v_emp_record c4%ROWTYPE;
BEGIN
OPEN c4(90, 'AD_VP'); --2、打开游标,传递参数值
LOOP
FETCH c4 INTO v_emp_record; --3、提取游标fetch into
IF c4%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇佣日期是'
||v_emp_record.hire_date);
ELSE
DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
EXIT;
END IF;
END LOOP;
CLOSE c4; --4、关闭游标
END;

DECLARE
   CURSOR c4(dept_id NUMBER, j_id VARCHAR2) --1、声明游标,有参数没有返回值
   IS
      SELECT first_name f_name, hire_date FROM employees
      WHERE department_id = dept_id AND job_id = j_id;

    --基于游标定义记录变量，比声明记录类型变量要方便，不容易出错
    v_emp_record c4%ROWTYPE;
BEGIN
   OPEN c4(90, 'AD_VP');             --2、打开游标,传递参数值
   LOOP
      FETCH c4 INTO v_emp_record;    --3、提取游标fetch into
      IF c4%FOUND THEN
         DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇佣日期是'
                            ||v_emp_record.hire_date);
      ELSE
         DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
         EXIT;
      END IF;
   END LOOP;
   CLOSE c4;                         --4、关闭游标
END;

退出LOOP或者用：

EXIT WHEN c4%NOTFOUND;

游标属性：

Cursor_name%FOUND 布尔型属性，当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE；

Cursor_name%NOTFOUND 布尔型属性，与%FOUND相反；——注意区别于DO_DATA_FOUND(select into抛出异常)

Cursor_name%ISOPEN 布尔型属性，当游标已打开时返回 TRUE；

Cursor_name % ROWCOUNT 数字型属性，返回已从游标中读取的记录数。

游标的for循环

PL/SQL语言提供了游标FOR循环语句，自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能；

当进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据；
当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理；
当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。

格式：

FOR index_variable IN cursor_name [ (value[, value ]…)] LOOP
-- 游标数据处理代码
END LOOP;

其中：

index_variable为游标FOR 循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句返回的结构集合的结构相同。在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据，index_variable中各元素的名称与游标查询语句选择列表中所制定的列名相同。如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后才能通过游标FOR 循环语句中的索引变量来访问这些列数据。

例：

[sql] view plain copy print ?

DECLARE
CURSOR c_cursor(dept_no NUMBER DEFAULT 10)
IS
SELECT department_name, location_id FROM departments WHERE department_id <= dept_no;
BEGIN
--当dept_no参数值为30
FOR c1_rec IN c_cursor(30) LOOP
DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
END LOOP;
--使用默认的dept_no参数值10
FOR c1_rec IN c_cursor LOOP
DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
END LOOP;
END;

DECLARE
  CURSOR c_cursor(dept_no NUMBER DEFAULT 10) 
  IS
    SELECT department_name, location_id FROM departments WHERE department_id <= dept_no;
BEGIN
    --当dept_no参数值为30
    FOR c1_rec IN c_cursor(30) LOOP        
         DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
    END LOOP;
   
    --使用默认的dept_no参数值10
    FOR c1_rec IN c_cursor LOOP       
         DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
    END LOOP;
END;

或者可以在游标 FOR 循环语句中使用子查询

[sql] view plain copy print ?

BEGIN
FOR c1_rec IN(SELECT department_name, location_id FROM departments) LOOP
DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
END LOOP;
END;

BEGIN
    FOR c1_rec IN(SELECT department_name, location_id FROM departments) LOOP 
       DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
    END LOOP;
END;

处理隐式游标

显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；

而对于非查询语句，如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区，隐式游标的名字为SQL，这是由ORACLE 系统定义的。

对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条SQL 语句所包含的数据。

格式调用为： SQL%

[sql] view plain copy print ?

DECLARE
v_rows NUMBER;
BEGIN
--更新数据
UPDATE employees SET salary = 30000
WHERE department_id = 90 AND job_id = 'AD_VP';
--获取默认游标的属性值
v_rows := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'个雇员的工资');
--删除指定雇员；如果部门中没有雇员，则删除部门
DELETE FROM employees WHERE department_id=v_deptno;
IF SQL%NOTFOUND THEN
DELETE FROM departments WHERE department_id=v_deptno;
END IF;
END;

DECLARE
   v_rows NUMBER;
BEGIN
   --更新数据
   UPDATE employees SET salary = 30000
     WHERE department_id = 90 AND job_id = 'AD_VP';
   --获取默认游标的属性值
   v_rows := SQL%ROWCOUNT;
   DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'个雇员的工资');
   
    --删除指定雇员；如果部门中没有雇员，则删除部门
    DELETE FROM employees WHERE department_id=v_deptno;
    IF SQL%NOTFOUND THEN
        DELETE FROM departments WHERE department_id=v_deptno;
    END IF;
END;

更新或删除当前游标数据

游标查询语句中必须使用FOR UPDATE选项，以便在打开游标时锁定游标结果集合在表中对应数据行的所有列和部分列。

如果另一个会话已对活动集中的行加了锁，那么SELECT FOR UPDATE操作一直等待到其它的会话释放这些锁后才继续自己的操作；对于这种情况，当加上NOWAIT子句时，如果这些行真的被另一个会话锁定，则OPEN立即返回并给出：

ORA-0054 ：resource busy and acquire with nowait specified.

[sql] view plain copy print ?

DECLARE
V_deptno employees.department_id%TYPE :=&p_deptno;
CURSOR emp_cursor
IS
SELECT employees.employee_id, employees.salary
FROM employees WHERE employees.department_id=v_deptno
FOR UPDATE NOWAIT; --1、for update
BEGIN
FOR emp_record IN emp_cursor LOOP
IF emp_record.salary < 1500 THEN
UPDATE employees SET salary=1500
WHERE CURRENT OF emp_cursor; --2、WHERE CURRENT OF cursor_name子句
END IF;
END LOOP;
END;

DECLARE 
    V_deptno employees.department_id%TYPE :=&p_deptno;
    CURSOR emp_cursor 
  IS 
  SELECT employees.employee_id, employees.salary 
    FROM employees WHERE employees.department_id=v_deptno
  FOR UPDATE NOWAIT;                    --1、for update
BEGIN
    FOR emp_record IN emp_cursor LOOP
      IF emp_record.salary < 1500 THEN
        UPDATE employees SET salary=1500
            WHERE CURRENT OF emp_cursor; --2、WHERE CURRENT OF cursor_name子句
      END IF;
    END LOOP;
END;

动态游标

与游标一样，动态游标（游标变量）也是一个指向多行查询结果集合中当前数据行的指针。但与游标不同的是，游标变量是动态的，而游标是静态的。

游标只能与指定的查询相连，即固定指向一个查询的内存处理区域，而游标变量则可与不同的查询语句相连，它可以指向不同查询语句的内存处理区域（但不能同时指向多个内存处理区域，在某一时刻只能与一个查询语句相连），只要这些查询语句的返回类型兼容即可。

[sql] view plain copy print ?

DECLARE
--定义一个游标数据类型
TYPE emp_cursor_type IS REF CURSOR;
--声明一个游标变量
c1 EMP_CURSOR_TYPE;
--声明两个记录变量
v_emp_record employees%ROWTYPE;
v_reg_record regions%ROWTYPE;
BEGIN
OPEN c1 FOR SELECT * FROM employees WHERE department_id = 20;
LOOP
FETCH c1 INTO v_emp_record;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name||'的雇佣日期是'
||v_emp_record.hire_date);
END LOOP;
--将同一个游标变量对应到另一个SELECT语句
OPEN c1 FOR SELECT * FROM regions WHERE region_id IN（1，2）;
LOOP
FETCH c1 INTO v_reg_record;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_reg_record.region_id||'表示'
||v_reg_record.region_name);
END LOOP;
CLOSE c1;
END;

DECLARE
   --定义一个游标数据类型
   TYPE emp_cursor_type IS REF CURSOR;
   --声明一个游标变量
   c1 EMP_CURSOR_TYPE;
   --声明两个记录变量
   v_emp_record employees%ROWTYPE;
   v_reg_record regions%ROWTYPE;

BEGIN
   OPEN c1 FOR SELECT * FROM employees WHERE department_id = 20;
   LOOP
      FETCH c1 INTO v_emp_record;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name||'的雇佣日期是'
                            ||v_emp_record.hire_date);
   END LOOP;
   --将同一个游标变量对应到另一个SELECT语句
   OPEN c1 FOR SELECT * FROM regions WHERE region_id IN（1，2）;
   LOOP
      FETCH c1 INTO v_reg_record;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_reg_record.region_id||'表示'
                            ||v_reg_record.region_name);
   END LOOP;
   CLOSE c1;
END;

flqchl

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
数据库隐式游标和显示游标

隐式游标：查询单行记录。Oracle自动在后台使用的游标，用户不参与任何操作！所以对客户是隐藏的不显示的！特征：--不能手动的打开，提取，关闭，--在使用SELECT。。INTO 语句时会自己打开，提取，创建，关闭通过使用隐式游标属性，可以获得最近执行的SELECT单行语句以及DML语句显示游标：查询多行记录。在PL/SQL如何返回进行多行记录并族行进行读取，
复制链接

扫一扫