Oracle系列之6-Procedure-Package-Purity

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:in
drop table tb;
select * from tb;
create table tb(
  tbname varchar2(50)
)
create or replace procedure in_pro_one(columnname in varchar2)
is
v_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)
is
begin
  insert into temp values(p_tid,p_tname);
  commit;
end;
call sp_insert('5','Five');

----parameter:out
create or replace procedure pro_out(p_tid in number,p_errMsg out varchar2)
as
v_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 = 96
declare
v_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 out
create or replace procedure pro_in_out(pro_msg in out varchar2)
is
v_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;

declare
v_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 procedure
select * from user_source where lower(name) = 'pro_in_out';
delete procedure pro_in_out;

----create function
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');
select * from temp;
create or replace 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;

----call:pl/sql and command line
----call function by pl/sql way:
declare
v_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 variable
var v_tname varchar2;
----execute := v_tname
exec :v_tname :=func_temp(1);


create or replace 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;

----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_temp
is
  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_temp
is
  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:
declare
v_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 variable
var v_tname varchar2;
----execute := v_tname
exec :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 standard
create 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