Oracle 游标学习

Oracle 游标学习

一、游标的概念：
游标是SQL的一个内存工作区，由系统或用户以变量的是形式定义。
作用：用于临时存储从数据库中提取的数据块。某些情况下需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理的结果显示出来或者写回数据库。
栗子：现有三步计算，A+(B+C)*N，如果每个操作都要从数据库中取数据，然后写回数据库，那么就需要多次进行磁盘数据交换，这样效率很低。但是如果数据一次读取，放在内存中，所有操作结束后，写入数据库，速度会高很多。

二、游标的类型：

• 显式游标：提取多行数据。需要程序员自己定义一个显式游标。显式游标对应一个返回结果为多行多列的select语句。
• 隐实游标：一次只能从数据库中提取一行数据。像select …into …查询语句和DML操作（即INSERT、UPDATE、SELETE），系统都会使用一个隐式游标
  【注】：游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。
  三、隐式游标的属性：
  当系统使用隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，从而控制程序的流程。隐式游标使用名字SQL来访问，通过SQL游标名总是只能访问前一个DML操作或者单行select操作的游标属性。即在刚刚执行完操作之后，立即使用SQL游标名来访问属性。
  隐式游标的种类：
• SQL%ROWCOUNT 返回整型 ，表示DML语句成功执行的数据行数
• SQL%FOUND 返回布尔型 ，值为true 表示插入、删除、更新或单行查询操作成功
• SQL%NOTFOUND 返回布尔型 ，与上相反
• SQL%ISOPEN 返回布尔型 ，DML执行过程中为真，结束后为false.
  栗子：
  create or REPLACE PROCEDURE myprocedure(in_name VARCHAR) is
  v_name varchar(40);
  v_id number;
  begin
  update tbl_stu set name=‘无暇’ where id=1;
  IF SQL%FOUND THEN
  DBMS_output.put_line(‘修改姓名成功’);
  commit;
  ELSE
  DBMS_output.put_line(‘修改姓名失败’);
  end if;
  end;

四、显示游标的定义和操作：
游标的使用分为4步：

• 1.声明游标：
  格式：cursor 游标名 [(参数1 数据类型 [，参数2 数据类型， …])] is select 语句；
• 2.打开游标：
  格式：open 游标名[(实际参数1 [实际参数2…])]
  打开游标时，select语句的查询结果就被传送到了游标工作区。
• 3.提取数据：（需在打开游标之后）
  格式： fetch 游标名 into 记录变量； 或者fetch 游标名 into 变量名1 [,变量名2…]；
  游标打开后有一个指针指向数据区，fetch 语句一次返回指针所指的一行数据，要返回多行需要重复执行，用循环语句来实现。
  对2种格式说明：
  a.一次将数据提取到记录变量中，需要使用%ROWTYPE事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量。
  定义记录变量的格式：变量名 表名|游标名%ROWTYPE；
  栗子：
  CURSOR emp_cursor IS SELECT ename,job,sal FROM emp WHERE empno=1;
  emp_record emp_cursor%ROWTYPE;
  BEGIN
  OPEN emp_cursor;
  FETCH emp_cursor INTO emp_record;
  DBMS_OUTPUT.PUT_LINE(emp_record.ename||‘,’|| emp_record.job||‘,’|| emp_record.sal);
  CLOSE emp_cursor;
  END;
  【注】记录变量由游标定义，需出现在游标定义之后。通过==记录变量名.字段名 == 获得记录变量的内容。
  b.变量名用来从游标中接收数据的变量，需事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。
• 4.关闭游标：
  格式：close 游标名；
  显示打开游标后，必须显示关闭。关闭游标意味着释放游标占用的资源。
  五、栗子：
  1.使用特殊的for 循环形式显示员工的编号和名称：
  DECLARE
  CURSOR emp_cursor IS
  SELECT empno, ename FROM emp;
  BEGIN
  FOR Emp_record IN emp_cursor LOOP
  DBMS_OUTPUT.PUT_LINE(Emp_record.empno|| Emp_record.ename);
  END LOOP;
  END;
  【注】该循环形式非常简单，隐含了记录变量的定义、游标的打开、提取和关闭过程。Emp_record为隐含定义的记录变量，循环的执行次数与游标取得的数据的行数相一致。
  2.动态select 查询：
  DECLARE
  str varchar2(100);
  v_ename varchar2(10);
  begin
  str:=‘select ename from scott.emp where empno=1’;
  execute immediate str into v_ename;
  dbms_output.put_line(v_ename);
  END;
  【注】：SELECT…INTO…语句存放在STR字符串中，通过EXECUTE语句执行。
  在变量声明部分定义的游标是静态的，不能在程序运行过程中修改。虽然可以通过参数传递来取得不同的数据，但还是有很大的局限性。通过采用动态游标，可以在程序运行阶段随时生成一个查询语句作为游标。要使用动态游标需要先定义一个游标类型，然后声明一个游标变量，游标对应的查询语句可以在程序的执行过程中动态地说明。
  定义游标类型的语句如下：
  TYPE 游标类型名 REF CURSOR;
  声明游标变量的语句如下：
  游标变量名 游标类型名;
  在可执行部分可以如下形式打开一个动态游标：
  OPEN 游标变量名 FOR 查询语句字符串;
  六、oracle执行存储过程：
  select * from table(GET_ZWDXX(‘2024-01-17’));