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 --使用這方式遍歷游標(biāo)不能先打開游標(biāo)變量
???????? dbms_output.put_line(idx.al_id||'----'||idx.al_name);
???? end loop;
???? close cur_sel_addrAlias;
???? */
???? dbms_output.put_line('....避免顯式身明游標(biāo)....');
???? 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開發(fā)人同指南，好久沒寫pl/sql了，今天主要看游標(biāo)這一章。
顯式游標(biāo)有4個(gè)屬性
???? %found? 指明是否取到了指定的記錄行
?????????用于判定是否取到一條記錄，取到返回true，如果fetch沒取到任何行，就返回false。
???? %isopen 指明游標(biāo)是打開的還是關(guān)閉的
?????????用于檢查游標(biāo)是否打開，游標(biāo)打開了就返回true,未打開返回false。
???? %notfound
?????????? 指示fetch是否失敗或是否還有可取的記錄行
???? %rowcount 指明總共取得多少行記錄


參數(shù)游標(biāo)：
定義
? 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 游標(biāo)
?? 用來更新游標(biāo)所檢索到的記錄如：
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;???