oracle存储过程

使用Cursor:

 

--测试一下，今天才申请使用itpub.net 的blog

 

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;

 

3.2.使用对象的属性

 

-- Author : XJG

 

-- Created : 2002.03.01 20:52:22

 

-- Purpose : 产生拆迁数据的报表数据

 

procedure GenConCQReport (

 

p_UserID in Common.SEQType, ---0 =使用权,1 = 高级使用权,2 = 机团

 

p_UserType in Common.THundred) is

 

type TTitleArr is varray (5) of varchar2 (20);

 

v_Title TTitleArr

 

:= TTitleArr ('可以签订的财产',

 

'已经签订的财产',

 

'可能可以签定的财产',

 

'不能签订的财产',

 

'已签定的合同'

 

);

 

v_DateLevel TTitleArr := TTitleArr ('1', '2', '3', '4', '5');

 

begin

 

forall i in v_Title.first .. v_Title.last

 

update TT_拆迁数据分类 T

 

set T.数据级别 = to_number (v_DateLevel (i))

 

where (T.财产编号 is not null)

 

and (T.财产编号, T.财产类别) in (

 

select TT.财产编号, TT.财产类别

 

from TT_拆迁数据分类 TT

 

start with TT.标题 = v_Title (i)

 

connect by prior TT.拆迁数据分类编号 =

 

TT.上级编号);

 

end;

 

 

 

 

3.3匿名游标

 

匿名游标,是我自己给的一个称呼。在游标FOR循环中可以定义查询，由于没有显式声明所以游标没有名字，记录名通过游标查询来定义。

 

 

DECALRE

 

v_tot_salary EMP.SALARY%TYPE;

 

BEGIN

 

FOR r_dept IN (SELECT deptno,dname FROM dept ORDER BY deptno) LOOP

 

DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);

 

v_tot_salary:=0;

 

DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);

 

END LOOP;

 

END;

 

3.4游标指针

 

-- Author : wdz
-- Created : 2003-10-22
-- Purpose : 使用系统定义的游标指针类型作为参数

分享到：

js语法备忘 | oracle存储过程

• 2008-10-09 22:38
• 浏览 1024
• 评论(0)
• 查看更多