PL/SQL 游标的使用(四)

 

介绍：

DML / Select执行时的内存空间

游标(cursor)是指向这个内存空间的指针.

 

属性：

%ROWCOUNT %FOUND %NOTFOUNF %ISOPEN

 

隐式游标 / 显式游标(自定义游标名称)

SQL%ROWCOUNT / mycur%ROWCOUNT

 

显示游标的使用步骤：

1.定义游标

2.打开游标

3.操作游标

4.关闭游标

 

先建立一张表test_table：userid,user_name,password 3个字段

 

 

--显示打印，这里用的是显示游标
SET serveroutput ON;
--1.游标使用
DECLARE
  v_name test_table.user_name%TYPE;
  v_pwd test_table.PASSWORD%TYPE;
  -- 1. 定义游标，把名字和SQL语句绑定
  -- sql : select user_name,PASSWORD from test_table;
  CURSOR test_cursor IS
    SELECT user_name,PASSWORD FROM test_table;
BEGIN
  -- 2. 打开游标：执行对应的SQL.取出记录
  -- 等同于：rs = stmt.excuteQuery(sql);
  OPEN test_cursor;
  -- 3.循环取出游标中的结果，每次操作取一条，并使指针下移一条
  loop
    fetch test_cursor INTO v_name,v_pwd;
    exit WHEN test_cursor%notfound;
    dbms_output.put_line('name :'||v_name||' pwd :'||v_pwd);
  END loop;
    dbms_output.put_line('totle:'||test_cursor%rowcount);
    -- 关闭游标，释放内存
    CLOSE test_cursor;
end;

 

--2.定义有参数的游标
DECLARE
  v_name test_table.user_name%TYPE;
  v_pwd test_table.PASSWORD%TYPE;
  -- 1. 定义游标
  CURSOR test_cursor(v_id NUMBER) IS
    SELECT user_name,PASSWORD FROM test_table where user_id = v_id;
BEGIN
  -- 2. 打开游标,这里可以传入参数
  OPEN test_cursor(2);
  -- 3.操作游标，这里用主键查询，其实只有一条记录，你可以尝试其他的条件查询
  loop
    fetch test_cursor INTO v_name,v_pwd;
    exit WHEN test_cursor%notfound;
    dbms_output.put_line('name :'||v_name||' pwd :'||v_pwd);
  END loop;
    dbms_output.put_line('totle:'||test_cursor%rowcount);
    -- 关闭游标，释放内存
    CLOSE test_cursor;
end;

 

-- 3.游标类型和游标变量的使用
--类型语法：TYPE ref_type_name IS REF CURSOR [RETURN return_type];
--变量语法：cursor_name ref_type_name , 自定义变量名 + 游标类型

DECLARE
  -- 定义一个动态（ref）游标类型，强类型（有RETURN）
  TYPE test_cursor_type
    IS  REF CURSOR RETURN test_table%rowtype;
  -- 定义一个游标变量，并使用刚才的类型
  mycursor test_cursor_type;
  -- 定义一个 行 类型,存放数据
  myrecord test_table%rowtype;
BEGIN
  -- 打开游标时，定义对应的SQL 语句。
  OPEN mycursor FOR SELECT * FROM test_table;
  loop
    fetch mycursor INTO myrecord;
    exit WHEN mycursor%notfound;
    dbms_output.put_line(myrecord.user_id||' '||myrecord.user_name);
  END loop;
  CLOSE mycursor;
end;

 

--3.1 这是弱类型实例

DECLARE
  input CHAR(1) := upper('&input');
  --定义记录类型 和 记录变量
  TYPE record_type IS record(
    v_id NUMBER,
    v_char varchar2(100)
  );
  myrecord record_type;
  
  -- 定义游标类型和游标变量
  TYPE cursor_type IS REF CURSOR; -- 无返回值，弱类型
  mycursor cursor_type;
  
BEGIN
  IF input = 'A' THEN
    OPEN mycursor
      FOR SELECT user_id,user_name FROM test_table;
      
  elsif input = 'B' THEN
    OPEN mycursor
      FOR SELECT user_id,PASSWORD FROM test_table;
    
  END IF;
  
  
  fetch mycursor INTO myrecord;
  while mycursor%found loop
    dbms_output.put_line(myrecord.v_id||' '||myrecord.v_char);
    fetch mycursor INTO myrecord;
  --可以使用下面的循环方式
  --loop


     --fetch mycursor INTO myrecord;
     --dbms_output.put_line(myrecord.v_id||' '||myrecord.v_char);
     --exit WHEN mycursor%notfound;
     
  END loop;
     dbms_output.put_line('total :'||mycursor%rowcount);
  CLOSE mycursor;
end;

 

--4 下面尝试一个简单的隐式游标：
  BEGIN 
  	INSERT INTO test_table VALUES(999,'123','123');
 	 dbms_output.put_line(sql%rowcount);
  end;
  --或者
  DECLARE
  	name varchar2(100);
  BEGIN 
 	 SELECT user_name INTO NAME FROM test_table WHERE user_id = 999;
  	dbms_output.put_line(SQL%rowcount);
  end;

 

--5 这个静态游标的使用，提前编译，速度更快。
DECLARE
  CURSOR mycursor IS
    SELECT * FROM test_table;
BEGIN 
  FOR myrecord  IN mycursor loop
    dbms_output.put_line(myrecord.user_id);
  END loop;
end;

--5.1 和上面的一样，利用for 进行嵌套
BEGIN 
  FOR myrecord  IN(SELECT * FROM test_table) loop
    dbms_output.put_line(myrecord.user_id);
  END loop;
end;

 

提示：

1. 游标相当于指针在数据空间排列的数据之间移动

 

2. 强弱类型只是看你是否要强制指定返回的类型，这里举例可能不好，可以根据参数查询不同的表

 

3. 显示游标都要用open cursor,而隐式不用，比如insert update DELETE select..into 等等

   隐式游标不能用open fetch 等操作，属性%ISOPEN 会一直打开，语句结束就关闭

 

4.静态 动态游标的使用关键看你是否有不同的语句 或者参数。相当于更加灵活。 如果你仅仅一条SQL查询，就用静态游标就行了

 

5.into  可以用 BULK COLLECT INTO代替，前者是一条一条的放，后者的批量存放,但是要求存放必须是集合类型（is table of）可参考：http://blog.csdn.net/robinson_0612/article/details/7545597