一个简单的过程通常由两部分组成:
1、声明部分:以declare开始;
2、执行部分:以begin开始,以最后一个end作为整个执行体结束的标志。
这里面也引用了游标的概念。
游标的概念有点类似于C++的指针,有显式游标和隐式游标,下面显示了显式游标的用法:
创建游标:
cursor c_subid is
select ti.subscriber_id, ti.name from tmp_whf_osdsubid ti;
打开游标:
open c_subid;
将游标指向的值取出:
fetch c_subid into id,name;
按照取出的值进行操作:
while c_subid % found loop
select * from tmp_abc t where t.id = id and t.name = name;
fetch c_subid into id, name;--将游标指向下一个值;
end loop;
关闭游标:
close c_subid;
游标的属性:
Cursor Attributes
Name Description
%FOUND Returns TRUE if record was fetched successfully, FALSE otherwise.
%NOTFOUND Returns TRUE if record was not fetched successfully, FALSE otherwise.
%ROWCOUNT Returns number of records fetched from cursor at that point in time.
%ISOPEN Returns TRUE if cursor is open, FALSE otherwise.
用法示例:
declare
tempsubid number(18) := 0;
mflag integer := 1;
mtype integer := 1;
message varchar2(200) := '';
cursor c_subid is
select ti.subscriber_id from tmp_whf_osdsubid ti;
begin
open c_subid;
fetch c_subid into tempsubid;
while c_subid%found loop
insert into tmp_whf_osdsubmessage values(tempsubid, message, mtype, mflag);
fetch c_subid into tempsubid;
end loop;
commit;
close c_subid;
end;