使用oralce Cursor

使用Cursor:

  declare

RoomID       Room.RoomID%Type;

RoomName     Room.RoomName%Type;

cursor crRoom  is

  select RoomID,RoomName

     from Room;

  begin

open crRoom;

loop;

fetch crRoom into RoomID,RoomName;

exit when crRoom%notFound;

  end loop;

close crRoom;

end;

 

3.1在游标使用入口参数

SQL语句的Where 子句中恰当使用 相关语句简化逻辑,本来需要使用两个游标,把相关入口参数放入到SQL语句的Where 子句中,一个就搞定了:

cursor    crRoom    is
      select 
        distinct  楼层,房屋用途
        from      TT_没有处理的房屋  t
        where     数据级别>=
0
            and   房屋处理类别=
3
            and   产权编号=p_产权编号
            and   拆迁房屋类别=p_拆迁房屋类别
            and   面积>
0
            and   (not p_房屋用途 is null
                   and 房屋用途=p_房屋用途
                   or p_房屋用途 is null); 

另外一个例子:

CREATE OR REPLACE PROCEDURE PrintStudents(
  p_Major IN students.major%TYPE) AS
  CURSOR c_Students IS
    SELECT first_name, last_name
      FROM students
      WHERE major = p_Major;
BEGIN
  FOR v_StudentRec IN c_Students LOOP
    DBMS_OUTPUT.PUT_LINE(v_StudentRec.first_name ||
' ' ||
                         v_StudentRec.last_name);
  END LOOP;
END;

 

Oracle带的例子examp6.sql

DECLARE
    CURSOR bin_cur(part_number NUMBER) IS SELECT amt_in_bin
        FROM bins
        WHERE part_num = part_number AND
            amt_in_bin >
0
            ORDER BY bin_num
            FOR UPDATE OF amt_in_bin;
    bin_amt         bins.amt_in_bin%TYPE;
    total_so_far    NUMBER(
5) := 0;
    amount_needed   CONSTANT NUMBER(
5) := 1000;
    bins_looked_at  NUMBER(
3) := 0;
BEGIN
    OPEN bin_cur(
5469);
    WHILE total_so_far < amount_needed LOOP
        FETCH bin_cur INTO bin_amt;
        EXIT WHEN bin_cur%NOTFOUND;
            
/* If we exit, there's not enough to *
              * satisfy the order.                */

        bins_looked_at := bins_looked_at +
1;
        IF total_so_far + bin_amt < amount_needed THEN
            UPDATE bins SET amt_in_bin =
0
                WHERE CURRENT OF bin_cur; 
                   
-- take everything in the bin
            total_so_far := total_so_far + bin_amt;
        ELSE       
-- we finally have enough
            UPDATE bins SET amt_in_bin = amt_in_bin
                - (amount_needed - total_so_far)
                WHERE CURRENT OF bin_cur;
            total_so_far := amount_needed;
        END IF;
    END LOOP;
    CLOSE bin_cur;
    INSERT INTO temp VALUES (NULL, bins_looked_at,
'<- bins looked at');
    COMMIT;
END;

 

 

-- Created on 2004-8-9 by ADMINISTRATOR
declare
 
--带有变量的Cursor

cursor crBooks(c_bookTitle varchar2) is
     select *
       from books  a
       where a.title like  c_bookTitle||
'%' ;
begin
  for v_Books in crBooks(
'Oracle8' ) loop
    dbms_output.put_line(v_Books.author1);
  end loop;
end;

  • 0
    点赞
  • 0
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页
评论

打赏作者

wdz_wdz

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值