Oracle-PL/SQL学习笔记

最新推荐文章于 2024-01-24 13:47:50 发布

Alan_DM

最新推荐文章于 2024-01-24 13:47:50 发布

阅读量376

点赞数 1

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

本文链接：https://blog.csdn.net/little_nai/article/details/51785770

版权

数据库专栏收录该内容

14 篇文章 1 订阅

订阅专栏

PL/SQL游标

游标分类

单个游标
FOR游标
隐式游标
REF游标

游标概念

游标原则上充当指向结果集的指针。
应用程序可以在结果集中移动指标，处理每个记录，直到结果集结束。

与游标相关的语法类型

创建游标
使用游标取数据
关闭游标
可用于逻辑比较的游标属性

创建游标
—如同声明任何其他变量一样

cursor cursor_name is sql_statement;

cursor_name是游标的名称（类似变量名称）；
sql_statement是与游标相关联的SQL语句。
游标在声明时，Oracle就为该游标分配了内存，并且将其与对应的SQL语句相关联。
游标是一种特殊的变量，只能进行声明、创建、使用和关闭，不能对其进行赋值。

打开与关闭游标

打开游标

OPEN cursor [arg [, arg … ]];

关闭游标

CLOSE cursor;

取数据

FETCH cursor INTO variable[, variable];

游标属性
通过这些属性，可以用于考察和了解游标在任何时刻的状态，从而来决定应用程序的程序结构或程序流程。

%ISOPEN
%FOUND
%NOTFOUND
%ROWCOUNT

cursor_name.attribute_name

比如：查询所有员工以前在某个工作岗位上的工作时间。
步骤：
》声明变量
》声明游标
》打开游标
》取数据
》资源释放（关闭游标）
》执行
注意

变量的声明：游标关联SQL语句中涉及的列，必须有对应的变量声明。
在取数据的过程中，变量的顺序必须与关联SQL语句中列的属性相一致；
打开游标和关闭游标成对出现。必须关闭游标来释放游标所占用的内存。
在取数据的过程中，往往采用了循环结构。在循环结构中必须通过访问游标的属性来决定循环如何结束。否则将出现死循环的问题。

CREATE OR REPLACE PROCEDURE promotion_review_1 
IS
   nemployeeid   NUMBER;
   dstartdate    DATE;
   denddate      DATE;
   sjobid        VARCHAR2 (20);
   *CURSOR cselectjob
   IS
      SELECT employee_id, start_date, end_date, job_id  FROM hr.job_history;*//游标声明
BEGIN
   OPEN cselectjob;//打开游标
   LOOP
      FETCH cselectjob INTO nemployeeid, dstartdate, denddate, sjobid;
EXIT WHEN cselectjob%NOTFOUND;//访问游标属性，决定如何结束
      DBMS_OUTPUT.put_line (   'Employee ' || nemployeeid || ' had job ' || sjobid || ' for ' || (denddate - dstartdate) || ' days.'  );
   END LOOP;
   CLOSE cselectjob;//关闭游标，释放资源
END;

两种特殊的数据类型

%TYPE
%ROWTYPE

%TYPE
应用情景：我们在前面使用游标时，首先必须定义变量，在定义变量时必须保证变量的类型和原数据库中类型一致，这就加大了工作量，而且容易出现问题，ORACLE提出了%TYPE类型，由Oracle自身来保证变量和数据库表中列之间类型的一致性。
%ROWTYPE
- 而保持变量顺序和SQL语句中顺序的一致性，同样会带来很多人为的程序错误。
- 因此，Oracle进一步的扩展了数据类型的动态声明，引入了%ROWTYPE类型。
- %ROWTYPE类型：用户定义单一的变量，而该变量包含对应于数据库表的每一行中的多个变量。
- Oracle自动维护%ROWTYPE变量中列顺序和数据库表的一致性，从而使得数据库表定义在发生变化时，PL/SQL不修改或修改尽量的少。
- %TYPE和%ROWTYPE一般被称为锚类型。

用法
%TYPE

variable_name  table.column%TYPE

%ROWTYPE

variable_name table%ROWTYPE

可以直接访问各个列的值：

row_variable.column_name

不能使用%ROWTYPE数据类型初始化变量

FOR游标
语法结构：

FOR record_variable in custor
LOOP
        logic…
END LOOP;

实例：

CREATE OR REPLACE PROCEDURE promotion_review_2
IS
   CURSOR cselectjob
   IS
      SELECT employee_id, start_date, end_date, job_id
        FROM hr.job_history;
BEGIN
   FOR jh_rec IN cselectjob//循环自动执行至结果集中无记录
   LOOP
      DBMS_OUTPUT.put_line (   'Employee ' || jh_rec.employee_id || ' had job ' || jh_rec.job_id || ' for ' || (jh_rec.end_date - jh_rec.start_date) || ' days.' );

   END LOOP;

END;

FOR游标的好处：

程序代码不仅更短小而且更清楚；
从逻辑上讲更加容易理解；
当应用只需要对结果集中的记录做循环并对每个记录执行逻辑操作时，这种循环非常好用

隐式游标

— 前面的例子都是显式的处理游标，即用户需要声明和使用游标。PL/SQL允许用户在不声明游标的条件下，将SQL语句（包括SELECT）用作自己代码的一部分。

— 这种情况下，PL/SQL使用了一种称为隐式游标的游标，它在幕后充当一种被声明的游标（显式游标）。

— 为了使用隐式游标，只需要简单地将SQL代码添加至PL/SQL代码中。
实例：

CREATE OR REPLACE PROCEDURE promotion_review_2
IS
   nempno      NUMBER;

   CURSOR cselectjob
   IS
      SELECT employee_id, start_date, end_date, job_id
        FROM hr.job_history;
BEGIN
//用于打开游标
   select count(*) into nempno   from hr.job_history;

   FOR jh_rec IN cselectjob

   LOOP
      DBMS_OUTPUT.put_line (   'Employee '  || jh_rec.employee_id || ' had job '                            || jh_rec.job_id || ' for ' || (jh_rec.end_date - jh_rec.start_date) || ' days.' );
   END LOOP;

END;

隐式游标和显示游标的区别：

通过上述例子我们可以看到，隐式游标不能手动的打开，提取，关闭，在使用SELECT。。INTO 语句时会自己打开，提取，创建，关闭。显示游标在使用时需要通过OPEN语句打开游标，通过FETCH语句获取游标数据如果有多行，则在循环语句中使FETCH语句，最后关闭游标

REF游标

REF游标允许在PL/SQL程序单元之间传递一个游标引用。换言之，它允许用户创建一个变量，该变量接受一个游标并且能够访问其结果集。
为了使用REF游标，必须首先声明它为一个TYPE，然后创建该类型的一个实例。

TYPE ref_cursor_name IS REF CURSOR 
[ return_type];
rc ref_cursor_name;

— 语法中的子句return_type是可选的，可以指定或不指定该REF游标返回的数据类型。
— 如果不指定返回类型，由于该游标是弱类型的，称之为弱游标；
定义了返回类型的，称之为强游标。
— 由于弱游标容易导致在由REF游标返回的数据和应用程序所使用的变量之间的类型不匹配，因此弱游标更有可能产生程序设计问题。