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_