create or replace package body peidw_test as
procedure createJob as
jobid number;
v_sql varchar2(2000);
begin
v_sql:='begin
if to_char(sysdate,''HH24:MI'')=''15:30'' then
select * from test;
dbms_output.put_line(''inserted success'');
end if;
commit;
exception
when others then
rollback;
dbms_output.put_line(SQLERRM);
end;
';
dbms_job.submit(jobid,v_sql,sysdate,'sysdate+1/1440');
dbms_job.run(jobid);
dbms_output.put_line('job'||to_char(jobid)||' is running');
end createJob;
procedure selectAddr_alias(addrid in number,arecord out cur_talias ) as
begin
open arecord for
select al_id ,al_name from addr_alias where addr_id=addrid;
end selectAddr_alias;
procedure curtest as
cursor cur_sel_addrAlias is
select al_id,al_name from addr_alias ;
alias TAlias;
begin
--open cur_sel_addrAlias ;
/*
fetch cur_sel_addrAlias into alias;
loop
exit when cur_sel_addrAlias%notfound;
dbms_output.put_line(alias.id||'----'||alias.AL_NAME);
end loop;
*/
/*
fetch cur_sel_addrAlias into alias;
while cur_sel_addrAlias%found loop
dbms_output.put_line(alias.id||'----'||alias.AL_NAME);
fetch cur_sel_addrAlias into alias;
end loop;
*/
/*
for idx in cur_sel_addrAlias loop --使用这方式遍历游标不能先打开游标变量
dbms_output.put_line(idx.al_id||'----'||idx.al_name);
end loop;
close cur_sel_addrAlias;
*/
dbms_output.put_line('....避免显式身明游标....');
for idx in (select * from addr_alias) loop
dbms_output.put_line(idx.al_id||'---'||idx.al_name||'---'||idx.addr_id);
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end curtest;
end peidw_test;
---------------------------------------------------------------------
无聊今天看了一下oracle 9i开发人同指南,好久没写pl/sql了,今天主要看游标这一章。
显式游标有4个属性
%found 指明是否取到了指定的记录行
用于判定是否取到一条记录,取到返回true,如果fetch没取到任何行,就返回false。
%isopen 指明游标是打开的还是关闭的
用于检查游标是否打开,游标打开了就返回true,未打开返回false。
%notfound
指示fetch是否失败或是否还有可取的记录行
%rowcount 指明总共取得多少行记录
参数游标:
定义
cursor cur_sel_addrAliasByid(vaddr_id number) is
select al_id,al_name from addr_alias where addr_id=vaddr_id;
使用
open cur_sel_addrAliasByid(1);
fetch cur_sel_addrAliasByid into alias ;
while cur_sel_addrAliasByid%found loop
dbms_output.put_line(alias.id||'----'||alias.AL_NAME);
fetch cur_sel_addrAliasByid into alias;
end loop;
close cur_sel_addrAliasByid;
Select for Update 游标
用来更新游标所检索到的记录如:
declare
cursor cur_1 is
select al_id,al_name form addr_alias for update of al_name;
vname varchar2(50)
begin
for idx in cur_1 loop
vname:=upper(idx.al_name);
upate addr_alias set al_name=vname where current of cur_1;
end loop;
commit;
end;
posted on 2006-09-07 18:11
有猫相伴的日子 阅读(346)
评论(0) 编辑 收藏 所属分类:
pl/sql