Oracle系列之六----Procedure--Package--Purity
Oracle系列之一----Datatype And Subprogram：http://overshit.iteye.com/admin/blogs/932585；
Oracle系列之二----Exception And Expression：http://overshit.iteye.com/admin/blogs/932605；
Oracle系列之三----Implicit Cursor：http://overshit.iteye.com/admin/blogs/932609；
Oracle系列之四----Dynamic Cursor：http://overshit.iteye.com/admin/blogs/932610；
Oracle系列之五----Cursor And Variable：http://overshit.iteye.com/admin/blogs/932612；
Oracle系列之六----Procedure--Package--Purity：http://overshit.iteye.com/admin/blogs/932615；
Oracle系列之七----Trigger：http://overshit.iteye.com/admin/blogs/932616；
?
?
----procedure:parameter type:in,out,in out;default in,we do not need appoint parameter datatype of procedure;----parameter:indrop table tb;select * from tb;create table tb( tbname varchar2(50))create or replace procedure in_pro_one(columnname in varchar2)isv_tname customer.custname%type;begin select custname into v_tname from customer where custid = columnname; dbms_output.put_line(v_tname); exception when no_data_found then raise_application_error(-20001,'no data!');end;call in_pro_one('96');drop table temp;create table temp( tid varchar2(10), tname varchar2(20))select * from temp;create or replace procedure sp_insert(p_tid varchar2,p_tname varchar2)isbegin insert into temp values(p_tid,p_tname); commit;end;call sp_insert('5','Five');----parameter:outcreate or replace procedure pro_out(p_tid in number,p_errMsg out varchar2)asv_tname customer.custname%type;----v_tname varchar(10);begin select custname into v_tname from customer where custage = p_tid; dbms_output.put(' ' || v_tname); exception when no_data_found then p_errMsg := 'no customer:' || v_tname; when too_many_rows then p_errMsg := v_tname || ' mapping too many rows!'; when others then p_errMsg := 'unknown exception!';end;----call procedure:update customer set custage = 100 where custid = 96declarev_err varchar2(200);begin dbms_output.put('Shut ' || v_err); pro_out(100,v_err); dbms_output.put_line(' Up ' || v_err);end;----parameter in outcreate or replace procedure pro_in_out(pro_msg in out varchar2)isv_msg varchar2(200);begin select custname into v_msg from customer where custid = pro_msg; pro_msg := v_msg; exception when no_data_found then pro_msg := 'no customer'; when too_many_rows then pro_msg := 'mapping too many rows!'; when others then pro_msg := 'unknown exception!';end;declarev_error varchar2(200) := '96';begin dbms_output.put('Shut '); pro_in_out(v_error); dbms_output.put_line(' Up ' || v_error);end;----look up and delete procedureselect * from user_source where lower(name) = 'pro_in_out';delete procedure pro_in_out;----create functiondrop table temp;create table temp( tid varchar2(10), tname varchar2(20))insert into temp values ('1','meilin');insert into temp values ('2','bing');insert into temp values ('3','qing');select * from temp;create or replace function func_temp(v_tid number)return varchar2isv_tname varchar2(10);begin select tname into v_tname from temp where tid = v_tid; return v_tname;end;----call:pl/sql and command line----call function by pl/sql way:declarev_tname varchar2(10);begin ----v_tname := func_temp(v_tid >= 1); select func_temp(1) into v_tname from dual; dbms_output.put_line(v_tname);end;----call function by command line way:----define variablevar v_tname varchar2;----execute := v_tnameexec :v_tname :=func_temp(1);create or replace function func_row(v_tid number)return temp%rowtypeisrowdata temp%rowtype;begin select * into rowdata from temp where tid = v_tid; return rowdata; exception when others then dbms_output.put_line('error:' || sqlerrm);end;----call func_row by pl/sql way:declare v_rowdata temp%rowtype;begin v_rowdata := func_row(3); dbms_output.put_line(v_rowdata.tid || '---' || v_rowdata.tname);end;----package:package head(such as interface) and package body(such as implementation),name should be the same;----package head:create or replace package pack_tempis procedure sp_insert(p_tid varchar2,p_tname varchar2); function func_temp(v_tid number)return varchar2; function func_row(v_tid number)return temp%rowtype;end pack_temp;----package body:create or replace package body pack_tempis procedure sp_insert(p_tid varchar2,p_tname varchar2) is begin insert into temp values(p_tid,p_tname); ----commit; rollback; end sp_insert; function func_temp(v_tid number)return varchar2 is v_tname varchar2(10); begin select tname into v_tname from temp where tid = v_tid; return v_tname; end func_temp; function func_row(v_tid number)return temp%rowtype is rowdata temp%rowtype; begin select * into rowdata from temp where tid = v_tid; return rowdata; exception when others then dbms_output.put_line('error:' || sqlerrm); end;end pack_temp;---call procedure/function in package:pl/sql way and command way:----call by pl/sql way:declarev_tname varchar2(10);begin ----v_tname := func_temp(v_tid >= 1); select pack_temp.func_temp(1) into v_tname from dual; dbms_output.put_line(v_tname);end;----call by command way:----define variablevar v_tname varchar2;----execute := v_tnameexec :v_tname :=pack_temp.func_temp(1);----package and cursor:drop table temp;create table temp( tid varchar2(10), tname varchar2(20))insert into temp values ('1','meilin');insert into temp values ('2','bing');insert into temp values ('3','qing');create or replace package pack_cur is ----weak cursor type type pack_cur_type is ref cursor; ----strong cursor type cursor mycur return temp%rowtype; procedure pro_getInfo(v_psql varchar2,v_pcur in out pack_cur_type);end pack_cur;create or replace package body pack_cur is cursor mycur return temp%rowtype is select * from temp; procedure pro_getInfo(v_psql varchar2,v_pcur in out pack_cur_type) as begin open v_pcur for v_psql; end pro_getInfo; end pack_cur; declare v_pack_cur pack_cur.pack_cur_type; rowdata temp%rowtype;begin --v_pack_cur := pack_cur.mycur; open pack_cur.mycur; loop fetch pack_cur.mycur into rowdata; exit when pack_cur.mycur%notfound; dbms_output.put_line(rowdata.tid || '---' || rowdata.tname); end loop; close pack_cur.mycur;end;----declare a cursor type in programme package standardcreate or replace package types as type cursorType is ref cursor; end;----return resultset by using cursor:(temp as tbname)create or replace function func_ListTemp(temp varchar2) return types.cursorType as v_cursor types.cursorType; begin ----temp as tbname; open v_cursor for 'select * from ' || temp; return v_cursor; end;----return resultset by using procedure:create or replace procedure pro_ListTemp(v_tid varchar2,pro_cur in out types.cursorType) as begin open pro_cur for 'select * from temp where tid = ' || v_tid; end;/*oracle package purity level: pragma restrict_references():程序辅助检验码,检查子程序纯度(purity),帮助检查子程序是否有违反规则， 一般用在函数上，但当函数调用存储过程时，也要做相应设置检查,这是为了避免在DML语句调用函数时正常执行而不至于产生错误; grammar:pragmar restrict_references(function_name | default,) RNDS,WNDS,RNPS,WNPS) | ，TRUST); RNDS,WNDS,RNPS,WNPS可同时指定，但当TRUST指定时，其它的被忽略,default指作用在该程序包上的所有子程序,函数; RNDS(read no database state):规定子程序不能读取任何数据库状态信息(即不会查询数据库任何表，包括dual虚表); RNPS(read no package state):规定子程序不能读取任何包状态信息,如变量等; WNDS(write no database state):规定子程序不能向数据库写入任何信息(即不能修改数据库表); WNPS(write no package state):规定子程序不能向程序包写入任何信息(即不能修改程序包变量值); WNDS do not write table;RNDS do not read table; WNPS do not write package variable;RNPS do not read package variable; TRUST:指出子程序是可以相信的，不会违反一个或多个规则,该选项是需要的,当用Java或C写的函数通过PL/SQL调用时，因PL/SQL在运行时对他们不能检查*/create or replace package DefaultPragma as function funcOne return number; ----package restrict ?instructions pragma restrict_references(funcOne,RNDS,RNPS); pragma restrict_references(default,WNDS,WNPS,RNDS,RNPS); function funcTwo return number; function funcThree return number;end DefaultPragma;/create or replace package body DefaultPragma as function funcOne return number is begin insert into temp(tid,tname) values ('one','funcOne'); return 1; end funcOne; function funcTwo return number is begin return 2; end funcTwo; ----this function against(violates) the default pragma function funcThree return number is begin insert into temp(tid,tname) values ('three','funcThree'); return 3; end funcThree;end DefaultPragma;/?
Oracle系列SQL及数据库下载：http://download.csdn.net/source/3046868
?
?
?
?
?
?
?
?
?
?
?
?