PL/SQL 训练05--游标

--隐式游标
--通过一个简单的SELECT ...INTO 语句提取一行数据，并放在一个局部变量中，最简单获取数据的途径

--显示游标
--可以在声明单元明确的声明一个查询，这样可以在一个或多个程序中打开游标并且提取数据

--游标变量
--使用游标变量，可以把指向一个查询结果集的指针从一个程序传递到另一个程序，灵活度较高
--任何程序只要能访问这个变量，就可以打开这个游标，提取数据，关闭游标

--游标表达式
--CURSOR表达式可以把一个SELECT 语句转换成REF CURSOR结果集，可以用于
--表函数中提升应用程序的性能

--游标基础

--最简单的游标，可以当成一个指针，指针指向一个表或者多个表查询得到的记录集。比如
cursor cur_users is select * from ma_users ;

--声明了一个游标，可以打开游标
OPEN cur_users ;

--从游标中提取数据
fetch cur_users into user_rec;

--最后关闭游标
close cur_users;

--术语
--静态SQL：当一个SQL语句所在的代码块被编译时，这个语句就完全指定的或者是固定的。
--动态SQL：如果一个SQL语句直到运行时刻才被构造出来并执行，这个语句就是动态SQL语句
--结果集: sql语句所请求的行集的和，结果集中会被缓存在SGA以提升访问数据和修改数据的性能
--隐式游标：每一个SQL DML语句或者SELECT INTO PL/SQL都会声明和使用一个隐式游标
--显示游标：明确的把一个SELECT语句声明成一个游标
--游标变量：指向或者引用游标对象的变量
--游标属性:游标名或者游标变量%属性名
--SELECT FOR UPDATE:标准select的一个特殊变体，这个语句对查询出来的每一行数据预先加上一个行级锁
--批量处理 BULK COLLECT

--典型的查询操作

--解析：SQL语句处理过程的第一步分析，以确保这个语句是有效的并生成执行计划
--绑定：程序中的值替换SQL语句的占位符
--打开：当打开一个游标时，SQL语句的结果集就根据设置的绑定的值确定下来了，
-- 指向活跃行或者当前行的指针就被设置到第一行
--执行：在执行阶段，语句被SQL引擎执行
--提取：如果我们执行的是一个查询语句，FETCH命令会从结果集中取出下一行记录。
--- 每执行一次fetch操作，PL/SQL就把记录集的指针向前移动一下
--关闭：关闭游标这一步会释放游标使用的全部内存资源，一旦游标关闭后，游标就不再有结果集了

--游标属性

--%found 
--反映的是游标最后一次数据提取操作的操作状态。如果游标最后一次提取返回了一行，这个属性结果就是TRUE
--如果没有取到任何行，就返回FALSE
declare
  cursor cur_users is
    select * from ma_users;
begin

  --如果没有打开游标就使用这个属性，就会报invalid_cursor异常
  /* if cur_users%found then
    dbms_output.put_line('更新积分');
  end if; */

  for v in cur_users loop
    update ma_users t
       set t.user_point = 1000
     where t.id_ma_users = v.id_ma_users;
    if cur_users%found then
      dbms_output.put_line(v.user_name || '更新了积分');
    end if;
  end loop;
end;
/
--%notfound
--跟%found属性相反，如果游标的最后一次提取数据操作没有取到任何记录，这个属性就返回TRUE
declare
  cursor cur_users is
    select * from ma_users;

  user_rec ma_users%rowtype;
begin

  --如果没有打开游标就使用这个属性，就会报invalid_cursor异常
  /* if cur_users%notfound then
    dbms_output.put_line('更新积分');
  end if; */
  open cur_users;
  loop
    fetch cur_users
      into user_rec;
    exit when cur_users%notfound;
    update ma_users t
       set t.user_point = 1000
     where t.id_ma_users = user_rec.id_ma_users;
  end loop;
  close cur_users;

  open cur_users;
  loop
    fetch cur_users
      into user_rec;
    exit when not cur_users%found;
    dbms_output.put_line(user_rec.user_name || '积分值是' ||
                         user_rec.user_point);
  end loop;
  close cur_users;

end;
/

--%rowcount
--返回的是，在请求这个属性的那个时刻，已经从这个游标中取出的记录数量
begin 
   
   update ma_users r 
   set r.user_point = r.user_point+100
   where r.user_name ='test001';
   dbms_output.put_line(sql%rowcount);

end ;
/

declare
  cursor cur_users is
    select * from ma_users;

  user_rec ma_users%rowtype;
begin


  open cur_users;
  loop
    fetch cur_users
      into user_rec;
    exit when not cur_users%found;
      dbms_output.put_line(cur_users%rowcount);
  end loop;
  dbms_output.put_line(cur_users%rowcount);
  close cur_users;
  --  dbms_output.put_line(cur_users%rowcount);

end;
/

--%isopen
--如果一个游标已经打开，返回TURE,否则返回FALSE

declare
  cursor cur_users is
    select * from ma_users;

  user_rec ma_users%rowtype;
begin

  begin
  
    open cur_users;
    loop
      fetch cur_users
        into user_rec;
      exit when not cur_users%found;
      update ma_users t
         set t.user_point = 1000
       where t.id_ma_users = user_rec.id_ma_users;
    end loop;
    close cur_users;
  EXCEPTION
    WHEN OTHERS THEN
      IF cur_users%ISOPEN THEN
        close cur_users;
      END IF;
    
  end;

end;
/
--%bulk_rowcount
--%bulk_exceptions
--这两个属性是在FORALL语句中使用的，后续再介绍PL/SQL性能优化的时候再介绍
--注意以上这些属性不能使用在SQL语句中

--隐式游标OR显示游标？

--使用隐式游标

--前面已提及dml语句或者SELECT INTO 语句，PL/SQL都会声明一个隐式游标并管理这个游标
--数据库隐式或自动的执行了许多和游标相关的操作。如打开游标，提取数据等

--select语句的隐式游标

--SELECT 语句出现在代码块的执行单元，而不是在声明单元
--查询带有一个INTO语句，into语句是PL/SQL语言的一部分，这种用于把数据库的数据传送到本地PL/SQL数据结构中

--不用打开、提取或者关闭SELECT语句，这些操作由数据库完成

--隐式查询的结构

SELECT column_list [bulk collect] into PL/SQL variable_list from.. .where...

declare
  v_phone ma_users.user_phone%type;
begin

  select r.user_phone
    into v_phone
    from ma_users r
   where r.user_name = 'test001';
  dbms_output.put_line(v_phone);
  dbms_output.put_line(sql%rowcount);
end;
/
--两种异常
-- no_data_found :什么都没有
-- too_many_rows ：太多了

declare
  v_phone ma_users.user_phone%type;
begin

  select r.user_phone
    into v_phone
    from ma_users r
   where r.user_name = 'test0001';
  dbms_output.put_line(v_phone);
exception
  when no_data_found then
    dbms_output.put_line(sql%rowcount);
end;
/
declare
  v_phone ma_users.user_phone%type;
begin

  select r.user_phone
    into v_phone
    from ma_users r
   where r.real_name = 'xinyuan';
  dbms_output.put_line(v_phone);
exception
  when no_data_found then
    dbms_output.put_line(sql%rowcount);
  when too_many_rows then
    dbms_output.put_line(sql%rowcount);
end;
/

--sql%rowcount
--sql%found
--sql%notfound
--sql%isopen
--如果会话还没有执行过隐式游标，所有的隐式游标属性都会返回NULL
--否则返回的是最后一次执行SQL语句的属性值，而不管这个SQL语句在哪个代码块或程序中执行的
declare
  v_phone ma_users.user_phone%type;
begin
  select r.user_phone
    into v_phone
    from ma_users r
   where r.user_name = 'test001';
  dbms_output.put_line(sql%rowcount);
  if sql%found then
    dbms_output.put_line('found');
  end if;
  if sql%notfound then
    dbms_output.put_line('notfound');
  end if;
  if sql%isopen then
    dbms_output.put_line('isopen');
  end if;
end;
/

--使用显示游标

--显示游标就是在声明单元明确定义的SELECT语句，并同时指定了一个名字
--dml语句没有显示游标一说
--声明显示游标

CURSOR CURSOR_NAME [(PARAM1,PARAM2)]
[RETURN return_specificaiton] --return子句
is select_statement [for update [of [column_list]]];

--不带有参数的游标
CURSOR CUR_USERS IS 
SELECT * FROM MA_USERS ;

--带有参数的游标
CURSOR CUR_USERS(I_USER_NAME IN VARCHAR2) IS 
SELECT * FROM MA_USERS WHERE USER_NAME = I_USER_NAME;

--带有RETURN语句的游标,游标的结构和底层表结构相同
CURSOR CUR_USERS RETURN MA_USERS%ROWTYPE IS 
SELECT * FROM MA_USERS T WHERE T.REAL_NAME ='xinyuan';

--游标的名字非PL/SQL变量，不能赋值

--可以在包中声明显示游标
create or replace package user_info is
  CURSOR CUR_USERS IS
    SELECT * FROM MA_USERS;

  CURSOR CUR_USERS_P(I_USER_NAME IN VARCHAR2) return ma_users%rowtype IS
    SELECT * FROM MA_USERS WHERE USER_NAME = I_USER_NAME;
end user_info;

begin

  for v in user_info.CUR_USERS loop
    dbms_output.put_line(v.user_name);
  end loop;

  for v in user_info.CUR_USERS_P('test001') loop
    dbms_output.put_line(v.user_phone);
  end loop;

end;
/
--return子句 类型
--基于某个数据库表定义的记录类型，使用ROWTYPE属性定义的
--基于另外一个之前定义好的游标定义的记录类型，使用%ROWTYPE定义的
--自定义的记录类型

--为什么要在包中定义游标呢?
create or replace package user_info_test is

  CURSOR CUR_USERS_P(I_USER_NAME IN VARCHAR2) return ma_users%rowtype;
end user_info_test;
/
create or replace package body user_info_test is

  CURSOR CUR_USERS_P(I_USER_NAME IN VARCHAR2) return ma_users%rowtype IS
    SELECT * FROM MA_USERS WHERE USER_NAME = I_USER_NAME;
end user_info_test;
/

--隐藏信息
--最小化重编译

--打开显示游标

OPEN CURSOR_NAME[(PARAM1,PARAM2)];

--打开游标时，PLSQL会执行这个游标的查询语句，同时标识出活跃数据集，即符合where语句条件和连接
--条件的数据行，但不会提取任何一行数据

--无论什么情况下取数，ORACLE的读一致性模型会保证取数操作反映的是游标打开那一刻的数据状态

--不能打开一个已经打开的游标
--打开之前可以使用一个%isopen来判断

declare

  CURSOR CUR_USERS IS
    SELECT * FROM MA_USERS;
  user_rec cur_users%rowtype;
begin
  if not CUR_USERS%isopen then
    open cur_users;
  end if;
  loop
    fetch cur_users
      into user_rec;
    exit when not cur_users%found;
    dbms_output.put_line(user_rec.user_name);
  end loop;
  --open cur_users;
  close cur_users;

end;
/
--从显示游标中提取数据

fetch cursor_name into record_or_variable_list;

declare

  CURSOR CUR_USERS IS
    SELECT user_phone FROM MA_USERS;
    
   CURSOR CUR_USERS1 IS
    SELECT user_name,user_phone FROM MA_USERS;
  user_rec cur_users%rowtype;
  v_phone  ma_users.user_phone%type;
  v_user_name ma_users.user_name%type;
begin
  open cur_users;
  loop
    fetch cur_users
      into user_rec;
    exit when not cur_users%found;
    dbms_output.put_line(user_rec.user_phone);
  end loop;
  close cur_users;
  dbms_output.put_line('----分割线---');
  open cur_users;
  loop
    fetch cur_users
      into v_phone;
    exit when not cur_users%found;
    dbms_output.put_line(v_phone);
  end loop;
  close cur_users;
  
  dbms_output.put_line('----分割线---');
  open cur_users1;
  loop
    fetch cur_users1
      into v_user_name,v_phone;
    exit when not cur_users1%found;
    dbms_output.put_line(v_user_name||'手机号'||v_phone);
  end loop;
  close cur_users1;

end;
/

--游标中可以使用列的别名
declare

  CURSOR CUR_USERS IS
    SELECT t.user_name, sum(t.user_point) points
      FROM MA_USERS t
     group by t.user_name;
  user_rec cur_users%rowtype;
begin
  if not CUR_USERS%isopen then
    open cur_users;
  end if;
  loop
    fetch cur_users
      into user_rec;
    exit when not cur_users%found;
    dbms_output.put_line(user_rec.user_name || user_rec.points);
  end loop;
  --open cur_users;
  close cur_users;

end;
/

--关闭游标

close cursor_name ;

--如果打开了一个游标，要确保在程序结束时关闭这个游标，否则就会出现内存泄漏

--函数、块或过程块，结尾自动检测打开的游标，并隐式关闭。开销不少


---包级别的游标，如果打开了，除非显示的关闭，否则一直保持打开直到会话结束

--只能关闭一个已经打开的游标，否则就会报INVALID_CURSOR

declare

  CURSOR CUR_USERS IS
    SELECT * FROM MA_USERS;
  user_rec cur_users%rowtype;
  EXP_TMP EXCEPTION;
begin

  open cur_users;
  BEGIN
    loop
      fetch cur_users
        into user_rec;
      exit when not cur_users%found;
      dbms_output.put_line(user_rec.user_name);
      RAISE EXP_TMP;
    end loop;
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
  fetch cur_users   into user_rec;
  dbms_output.put_line(user_rec.user_name);
  /*FOR V IN CUR_USERS LOOP
    dbms_output.put_line(V.user_name);
  END LOOP;*/

end;
/

--显示游标属性
--%found
--%notfound
--%rowcount
--%isopen

declare

  v_user_rec user_info.CUR_USERS%rowtype;
begin

  if user_info.CUR_USERS%isopen then
    close user_info.CUR_USERS;
  end if;
  open user_info.CUR_USERS;
  loop
    fetch user_info.CUR_USERS
      into v_user_rec;
    exit when user_info.CUR_USERS%notfound or user_info.CUR_USERS%rowcount > 4;
    dbms_output.put_line(v_user_rec.user_name);
  end loop;
  close user_info.CUR_USERS;

end;
/

--游标参数化
--为什么要使用游标参数化？
--重用
--避免作用域的问题
--什么时候使用游标参数化？想想函数
--如果要在多个地方使用一个游标，每次只是WHERE语句中值不同，则建议创建一个带参数的游标

declare
  v_user_info user_info.CUR_USERS_P%rowtype;
begin
  open user_info.CUR_USERS_P('test001');
  loop
    fetch user_info.CUR_USERS_P
      into v_user_info;
    exit when user_info.CUR_USERS_P%notfound;
    dbms_output.put_line(v_user_info.user_phone);
  end loop;
  close user_info.CUR_USERS_P;
end;
/
--游标的参数作用范围仅限于游标，不能在游标关联的SELECT语句之外引用游标参数
declare

  CURSOR CUR_USERS_P(I_USER_NAME IN VARCHAR2) IS
    SELECT * FROM MA_USERS WHERE USER_NAME = I_USER_NAME;

begin
  I_USER_NAME := 'test';
  open CUR_USERS_P(I_USER_NAME);
  
end;
/

--游标参数的模式，只能是一个IN，不能使用OUT或者in out

--游标参数可以有缺省值；比如
declare

  CURSOR CUR_USERS_P(I_USER_NAME IN VARCHAR2 := 'test001') 
  IS SELECT * FROM MA_USERS WHERE USER_NAME = I_USER_NAME;
  v_user CUR_USERS_P%rowtype;
begin
  open CUR_USERS_P('幸运小子1');
  loop
    fetch CUR_USERS_P
      into v_user;
    exit when CUR_USERS_P%notfound;
    dbms_output.put_line(v_user.user_phone);
  end loop;
  close CUR_USERS_P;
end;
/

--select ... for update 
--单纯使用SELECT从数据库查询数据，数据库不会对选择的行添加任何锁
--有时后，我们希望能在程序修改记录之前就锁住它们，可以使用
SELECT * from ma_users FOR UPDATE ;

declare

  CURSOR CUR_USERS_P(I_USER_NAME IN VARCHAR2) IS
    SELECT * FROM MA_USERS WHERE USER_NAME = I_USER_NAME FOR UPDATE;
  v_user CUR_USERS_P%rowtype;
begin
  open CUR_USERS_P('test001');
  loop
    fetch CUR_USERS_P
      into v_user;
    exit when CUR_USERS_P%notfound;
    dbms_output.put_line(v_user.user_phone);
  end loop;
  close CUR_USERS_P;
end;
/
declare

  CURSOR CUR_USERS_P(I_USER_NAME IN VARCHAR2) IS
    SELECT t.USER_PHONE, tt.trace_mark
      FROM MA_USERS t, ma_user_log tt
     WHERE t.USER_NAME = I_USER_NAME
       and t.user_name = tt.user_name
       FOR UPDATE OF t.USER_PHONE; --指定锁住某列
  v_user CUR_USERS_P%rowtype;
begin
  open CUR_USERS_P('乱世佳人');
  loop
    fetch CUR_USERS_P
      into v_user;
    exit when CUR_USERS_P%notfound;
    dbms_output.put_line(v_user.user_phone || v_user.trace_mark);
  end loop;
  close CUR_USERS_P;
end;
/
--当遭遇COMMIT,或rollback,结果集所有行的锁就会被释放
--但也不能用FETCH从FOR UPDATE的游标中提取数据了

declare

  CURSOR CUR_USERS IS
    SELECT * FROM MA_USERS for update;
  user_rec cur_users%rowtype;
begin

  FOR V IN CUR_USERS LOOP
    dbms_output.put_line(V.user_name);
    commit;--ORA-01002: 提取违反顺序
  END LOOP;

end;
/
--使用 WHERE CURRENT OF ,代码冗余最小化
declare

  CURSOR CUR_USERS IS
    SELECT * FROM MA_USERS for update;
  user_rec cur_users%rowtype;
begin
  FOR V IN CUR_USERS LOOP
    dbms_output.put_line(V.USER_NAME || V.USER_POINT);
    UPDATE MA_USERS T SET T.USER_POINT = 1000 WHERE CURRENT OF CUR_USERS;
  END LOOP;

end;
/
--游标变量和REF CURSOR
--游标变量是一个指向或者引用底层游标的变量
--游标变量可以用于打开任何一个查询，甚至在一个程序中执行多个不同的查询

--声明游标变量
--创建一个引用游标类型
--基于这个类型声明真正的游标变量
TYPE cursor_type_name is ref cursor [return return_type];--创建一个引用类型
--比如
type cur_user_typ is ref cursor return ma_users%rowtype; --强类型，提取数据必须用指定的类型
type cur_log_type is ref cursor ;--弱类型

--sys_refcusor
my_cursor sys_refcursor; --系统预定义弱类型
 
--声明游标变量
cursor_name cursor_type_name
declare 
 
   type my_cur_type is ref cursor ;
   v_cur my_cur_type;
begin 

end ;
/
--声明一个游标变量和创建一个真正的游标对象
--后者是通过游标的SQL语句查询出来的结果集，相当于常量，不过游标变量引用或者指向一个游标对象
--声明一个游标变量并不会创建一个游标对象。

--打开游标变量
open cursor_name for select_statement;

--弱类型的和强类型的打开方式

declare 
 
   type my_cur_type is ref cursor return ma_users%rowtype;
   v_cur my_cur_type;
begin 
   dbms_output.put_line('test');
   open v_cur for select * from ma_users;
   open v_cur for select * from ma_user_log;
end ;
/
--从一个游标变量中获取数据
fetch cursor_variable_name into record_name;
fetch cursor_variable_name into variable_name1,variable_name2
--注意弱类型和强类型之分
--弱是强类型，into变量必须 与return的类型相匹配，
--兼容性检查，在运行时刻发生,如果类型不匹配就会报ROWTYPE_MISMATCH异常

create or replace procedure test_ref(i_type in varchar2,io_ref in out sys_refcursor )is 

begin 
   if i_type = 'user' then 
      open io_ref for select * from ma_users;
   end if;
   
   if i_type = 'log' then 
      open io_ref for select * from ma_user_log;
   end if;
end test_ref;
/

declare 
 
   v_cur  sys_refcursor;
   v_type varchar2(10); 
   v_user_log ma_user_log%rowtype;
   v_user ma_users%rowtype;
begin 
  
   v_type := 'user';
   test_ref(v_type,v_cur);
   fetch v_cur into v_user_log ;
   dbms_output.put_line(v_user_log.TRACE_MARK);
   --onw row
 exception
    when rowtype_mismatch then 
       fetch v_cur into v_user ;
       dbms_output.put_line(v_user.user_name);
end ;
/

declare 
 
   v_cur  sys_refcursor;
   v_type varchar2(10); 
   v_user_log ma_user_log%rowtype;
   v_user ma_users%rowtype;
begin 
  
   v_type := 'log';
   test_ref(v_type,v_cur);
   loop
   fetch v_cur into v_user_log ;
   dbms_output.put_line('TRACE_MARK=='||v_user_log.TRACE_MARK);
   exit when v_cur%notfound;
   end loop;
   --all rows
 exception
    when rowtype_mismatch then 
       fetch v_cur into v_user ;
       dbms_output.put_line(v_user.user_name);
end ;
/

--游标变量的使用规则
--游标变量是对一个游标对象或者一个数据库中查询的引用，而不是游标变量本身
--如果游标变量为一个查询执行了OPEN语句
--用另外一个引用这个查询的游标变量对这个游标变量赋值
--其中之一为真，就可以说这个游标变量引用了一个查询

--编译时的行类型匹配规则

--1.如果以下条件为真，两个游标变量（包括过程的参数）在赋值操作和参数传递过程是兼容的
--两个变量或者参数都是强类型的REF CURSOR 且具有相同的ROWTYPE
--两个变量或者参数都是弱类型的REF CURSOR 不管名字是否相同
--一个变量或者参数是任意一种强类型的REF CURSOR 另一个是任意一种弱类型的REF CURSOR 

--2.一个强类型的游标变量或者参数，打开的查询语句返回的行结构要和声明的RETURN ROWTYPE 一致

--3.一个弱类型的游标变量或者参数，可以打开任意查询，获取的数据可以用于任何变量列或者记录类型

--运行时刻的行类型匹配规则
--一个弱类型的游标变量或者参数可以用来引用任何一种行类型的查询，而不管之前引用的是什么查询
--一个强类型的游标变量或者参数，只能引用结构上匹配RETURN的类型的查询
--如果两条记录或者变量列表能够满足一下条件，就可以认为是隐式转换后结构兼容的
--两个记录或者列表字段的数量相同
--每个字段的数据类型相同或者可以被隐式转换成另一个类型

--用在FETCH语句中的游标变量或者参数，和这个游标变量关联的查询必须在结构上能够和FETCH
--语句INTO 中的记录或者变量列相兼容

--游标变量别名

--如果把一个游标变量赋值给另外一个游标变量，它们就成为一个游标对象的别名，共享游标对象
declare
  type cur_type is ref cursor;

  v_cur1 cur_type;
  v_cur2 cur_type;
  v_user ma_users%rowtype;
begin
  open v_cur1 for
    select * from ma_users;
  v_cur2 := v_cur1;
  fetch v_cur1
    into v_user;
  dbms_output.put_line(v_user.user_name);
  fetch v_cur2
    into v_user;
  dbms_output.put_line(v_user.user_name);
   close v_cur2;
  fetch v_cur1
    into v_user;

end;
/

--游标对象的作用范围
declare
  v_cur1 sys_refcursor;
  v_user ma_users%rowtype;
begin
  declare
    v_cur2 sys_refcursor;
  begin
    open v_cur2 for
      select * from ma_users;
    v_cur1 := v_cur2;
  end;

  fetch v_cur1
    into v_user;
  dbms_output.put_line(v_user.user_name);
  close v_cur1;
end;
/

--游标量作为参数传递
--调用一个过程或者一个函数时，可以把游标变量作为一个参数传递
--使用前，必须指定参数的模式还有类型

DECLARE
  TYPE CUR_TYPE IS REF CURSOR RETURN MA_USERS%ROWTYPE;
  PROCEDURE OPEN_CUR(I_CUR OUT CUR_TYPE) IS
  
  BEGIN
    OPEN I_CUR FOR
      SELECT * FROM MA_USERS;
  END OPEN_CUR;
BEGIN
  DECLARE
    V_CUR CUR_TYPE;
  BEGIN
    OPEN_CUR(V_CUR);
    
  END;

END;
/
--或者
CREATE OR REPLACE PACKAGE USER_REF 
IS 
TYPE CUR_TYPE IS REF CURSOR RETURN MA_USERS%ROWTYPE;
END USER_REF;

DECLARE
  PROCEDURE OPEN_CUR(I_CUR OUT USER_REF.CUR_TYPE) IS
  BEGIN
    OPEN I_CUR FOR
      SELECT * FROM MA_USERS;
  END OPEN_CUR;
BEGIN
  DECLARE
    V_CUR USER_REF.CUR_TYPE;
  BEGIN
    OPEN_CUR(V_CUR);
  END;
END;
/
--设置参数模式
-- in 参数对程序是只读的
--out 参数对于程序来说是只写的
-- in out 参数对程序可读可写

--注意游标变量和游标对象的区分
--怎样改变游标变量的值
---对于游标变量进行赋值
--使用OPEN FOR 语句

PROCEDURE org_CUR(I_CUR1 in USER_REF.CUR_TYPE,
                  I_CUR2 out USER_REF.CUR_TYPE) IS
BEGIN
  I_CUR2 := I_CUR1;

END org_CUR;

--游标变量的约束
--嵌套表、关联数组、或者VARRAY的元素不能保存游标变量值，不能用REF CUROSR类型定义集合元素
--数据库的列不能保存游标变量值
--不能给一个游标变量赋值NULL
--不能用比较运算符去测试游标变量是否相等，不想等，不相等或者为空
--游标变量不能在包中声明


--游标表达式
--用CUROSR操作符表示，返回的是一个嵌套在查询语句中的游标
--这个嵌套游标结果集的每一行可以包含一个SQL查询通常允许的值范围。也可以包汗其它子查询创建的游标
--语法
CURSOR(SUBQUERY);

--怎样使用游标表达式

--把子查询作为外层查询的一列
--把一个查询转换为一个结果集，这样可以把结果集在一个流或者转换函数中传递


declare

  cursor cur_users is
    select r.user_name,
           cursor (select rr.trace_mark
                     from ma_user_log rr
                    where rr.user_name = r.user_name) userLog
      from ma_users r;
  v_user_name ma_users.user_name%type;
  v_log_cur   sys_refcursor;
  v_mark      ma_user_log.trace_mark%type;
begin

  open cur_users;
  loop
    fetch cur_users
      into v_user_name, v_log_cur;
    exit when cur_users%notfound;
    dbms_output.put_line(v_user_name);
    loop
      fetch v_log_cur
        into v_mark;
      exit when v_log_cur%notfound;
      dbms_output.put_line('----' || v_mark);
    end loop;
  end loop;

end;
/

--游标表达式的限制
--不能把游标表达式用于隐式游标，因为没有机制支持把嵌套游标的数据取到PL/SQL的数据结构中
--游标表达式只能出现在最外层的SELECT 列表中
--声明视图时不能使用游标表达式
--如果在动态SQL中使用游标表达式，不能对游标表达式进行BIND和EXECUTE操作

-------------------------------------------------

1. 请简要描述下游标变量和游标对象之间的关系
2。前几课的作业，有了用户模块，也有购物车，现在还缺订单模块，记录订单信息，一个订单可能含有多个商品，
不同的订单上的同种商品的价格可能还不一样，比如说折扣不同等，索引需要设计订单表来记录订单信息，
可能需要一个订单号，用户，联系方式，邮寄地址，总价格等等，还需要关联一个订单下商品表等。
一个用户可能有多个订单，一个订单可能有多个商品。请大家设计出所需要的表，并且练习使用游标表达式来展示一个用下的订单，及订单下的商品信息

1 游标变量：一个指向或者引用底层的游标的变量。可以用于打开任何一个查询，
            一个程序中执行多个不同的查询，可以再不同的Pl/sql程序间传递结果集，在客户端跟服务器端使用游标变量来传递。
  游标对象：游标变量的值是对游标对象的引用，只有2个操作可以改变游标变量的值，open for语句与变量赋值。
            声明一个游标变量并不会创建一个游标对象
  --定义游标变量
  type cus_cur_type is ref cursor return chg_test_b%rowtype;
  type cus_cur_type is ref cursor;    
  --声明游标变量
  rowList cus_cur_type;
  rowValue chg_test_b%rowtype;
  --打开一个游标变量
  open rowList for select * from ma_users;

2 
1 相关表
create table goods
(create_on date default sysdate,
create_by varchar2(20) default 'system',
update_on date default sysdate,
update_by varchar2(20) default 'system',
goods_id varchar2(32) default sys_guid() primary key,--goods id ,uk
goods_name varchar2(100) not null,--goods name
goods_photo varchar2(100),--goods photo url
goods_number number(18,0),--goods  total number
tag_price number(18,2) not null,--goods tag price
normal_price number(18,2),----goods normal price,default =tag price
goods_category varchar2(20),--goods category
goods_currency varchar2(20) default 'CNY',--default currency CNY
goods_type varchar2(20),--goods type
goods_subtype1 varchar2(20),--goods sub type 1
goods_subtype2 varchar2(20),-- goods sub type 2
goods_status number default 1 ,--0/inactive,1/active
goods_description varchar2(100),--goods description
is_selling number  default 1,--0/not selling,1/selling
selling_date date default sysdate ,--default seling date
is_promote number  default 0--0/inactive,1/active
);

create index idx_goods_name on goods(goods_name);

insert into goods(goods_name,goods_photo,goods_number,tag_price,normal_price,goods_category,goods_currency,goods_type)
   values('iphone 6s 64g','null',1000,6000,6000,'mobile','CNY','mobile');
insert into goods(goods_name,goods_photo,goods_number,tag_price,normal_price,goods_category,goods_currency,goods_type)
   values('iphone 6s 16g','null',1000,5000,5000,'mobile','CNY','mobile');
insert into goods(goods_name,goods_photo,goods_number,tag_price,normal_price,goods_category,goods_currency,goods_type)
   values('iphone 6s plus 16g','null',1000,6500,6500,'mobile','CNY','mobile');
insert into goods(goods_name,goods_photo,goods_number,tag_price,normal_price,goods_category,goods_currency,goods_type)
   values('iphone 6s plus 64g','null',1000,7000,7000,'mobile','CNY','mobile') ;
   
create table order_goods
(create_on date default sysdate,
create_by varchar2(20) default 'system',
update_on date default sysdate,
update_by varchar2(20) default 'system',
id_order_goods varchar2(32) primary key,
order_id varchar2(32) not null ,--order_info's order id
goods_id varchar2(32) not null,--reference goods id
goods_name varchar2(100) not null,--goods name
goods_photo varchar2(100),--goods photo url
goods_number number(18,0),--goods number
goods_price number(18,2),--goods  price
goods_category varchar2(20),--goods category
goods_currency varchar2(20) default 'CNY',--default currency CNY
order_status number default 1 ,--0/paying,1/paid
order_subtotal number(18,2) --A single order subtotal
);

create  index idx_og_goods_id on order_goods(goods_id);
create index idx_og_order_id on order_goods(order_id);

create table order_info
(create_on date default sysdate,
create_by varchar2(20) default 'system',
update_on date default sysdate,
update_by varchar2(20) default 'system',
order_id varchar2(32)  primary key,--order id
id_ma_user varchar2(32) not null,--reference ID_MA_USERS
session_id varchar2(32),--Mark multiple orders belong to the same time
order_status number  ,--0/UNCONFIRMED,1/CONFIRMED
pay_status  number  ,--0/paying,1/paid
order_total number(18,2),--Mark multiple orders  total
order_starttime date default sysdate,
order_endtime date,
comments varchar2(100)
);

create index idx_oi_id_ma_user on order_info(id_ma_user);

create table shopping_address
(create_on date default sysdate,
create_by varchar2(20) default 'system',
update_on date default sysdate,
update_by varchar2(20) default 'system',
shopping_address_id varchar2(32) default sys_guid()  primary key,--shopping_address id
id_ma_user varchar2(32) not null,--reference ID_MA_USERS
address_name varchar2(100) not null,--address name
consignee varchar2(50),--consignee
email varchar2(100) ,--user email
country_name varchar2(30) ,--country_name
province_name varchar2(40) ,--province_name
city_name varchar2(40) ,--city_name
district_name varchar2(100)  ,--district_name
address_detail varchar2(100) ,--address_detail
zipcode number,
tel_phone varchar2(20) ,
mobile_phone number,
delivery_date  date);

create index idx_sd_id_ma_user on shopping_address(id_ma_user);

insert into shopping_address(id_ma_user,address_name,consignee,email,country_name,province_name,
city_name,district_name,address_detail,zipcode,tel_phone,mobile_phone,delivery_date)
values('2EAE6137499E6DC9E053BD02A8C0DDDF','home','乱世佳人','test@163.com','china','chengdu',
'wihou','1234','123256',86,'028-12345679',23333333,sysdate);
insert into shopping_address(id_ma_user,address_name,consignee,email,country_name,province_name,
city_name,district_name,address_detail,zipcode,tel_phone,mobile_phone,delivery_date)
values('2EAE613749AF6DC9E053BD02A8C0DDDF','home','幸运小子','test@163.com','china','chengdu',
'wihou','1234','123256',86,'028-12345679',223333322,sysdate);
insert into shopping_address(id_ma_user,address_name,consignee,email,country_name,province_name,
city_name,district_name,address_detail,zipcode,tel_phone,mobile_phone,delivery_date)
values('2EAE613749A06DC9E053BD02A8C0DDDF','home','乱世佳人2','test@163.com','china','chengdu',
'wihou','1234','123256',86,'028-12345679',2233333,sysdate);

commit

select d.user_name,d.id_ma_users,a.order_id,c.goods_id,c.goods_name,a.order_subtotal,e.order_total,b.mobile_phone,b.address_detail
from order_goods a,shopping_address b,goods c,ma_users d,order_info e
where a.order_id=e.order_id
and a.goods_id=c.goods_id
and a.goods_name=c.goods_name
and b.id_ma_user=d.id_ma_users
and d.user_name='乱世佳人'

2 实现

--相关设计细节，代码细节还需要优化
DECLARE
   v_user_name         ma_users.user_name%TYPE;
   v_id_ma_users       ma_users.id_ma_users%TYPE;
   v_order_id          order_goods.order_id%TYPE;
   v_goods_id          goods.goods_id%TYPE;
   v_goods_name        goods.goods_name%TYPE;
   v_order_subtotal    order_goods.order_subtotal%TYPE;
   v_order_total       order_info.order_total%TYPE;
   v_mobile_phone      shopping_address.mobile_phone%TYPE;
   v_address_detail    shopping_address.address_detail%TYPE;
   v_order_starttime   order_info.order_starttime%TYPE;
   v_order_endtime     order_info.order_endtime%TYPE;
   v_log_cur           SYS_REFCURSOR;
   v_sysdate     DATE DEFAULT SYSDATE;
     CURSOR cur_order_goods
   IS
      SELECT d.user_name,
             d.id_ma_users,
             a.order_id,
             c.goods_id,
             c.goods_name,
             a.order_subtotal,
             e.order_total,
             b.mobile_phone,
             b.address_detail,
             v_sysdate,
             v_sysdate+2
        FROM order_goods a,
             shopping_address b,
             goods c,
             ma_users d,
             order_info e
       WHERE     a.order_id = e.order_id
             AND a.goods_id = c.goods_id
             AND a.goods_name = c.goods_name
             AND b.id_ma_user = d.id_ma_users
             and d.user_name='乱世佳人';
BEGIN
   OPEN cur_order_goods;

   LOOP
      FETCH cur_order_goods
      INTO v_user_name,
           v_id_ma_users,
           v_goods_id,
           v_order_id,
           v_goods_name,
           v_order_subtotal,
           v_order_total,
           v_mobile_phone,
           v_address_detail,
           v_order_starttime,
           v_order_endtime;

      EXIT WHEN cur_order_goods%NOTFOUND;
      DBMS_OUTPUT.
       put_line (
            'username= '
         || v_user_name
         || ' orderid= '
         || v_order_id
         || ' goodsname= '
         || v_goods_name
         || ' ordersubtotal= '
         || v_order_subtotal
         || ' ordertotal= '
         || v_order_total
         || ' ordertime= '
         || TO_CHAR (v_order_starttime, 'yyyy-mm-dd hh24:mi:ss'));
   END LOOP;
END;

posted @ 2019-03-28 10:29 春困秋乏夏打盹阅读(302) 评论(0) 编辑收藏举报

刷新页面返回顶部

春困秋乏夏打盹

PL/SQL 训练05--游标

公告