收集PL/SQL中的有用语句

数据库脚本导出：
1.首先导出:

exp user/pass@database file=d:/datadump.dmp full=y

2.按照如下方法导入就可以得到脚本:

imp user/pass@database file=d:/datadump.dmp full=y indexfile=d:/creatdatabase.txt

 http://topic.csdn.net/t/20050119/17/3738712.html

[使用游标：]  
   
  1:最基本的游标  
  declare    
      wldm   erp.itm_m.itm_wldm%type;  
      wlpm   erp.itm_m.itm_wlpm%type;  
      cursor   cur   is  
          select   itm_wldm,itm_wlpm   from   erp.itm_m     where   itm_wldm   =   '10001';  
  begin  
      open   cur;  
      fetch   cur   into   wldm,wlpm;  
      close   cur;  
      dbms_output.put_line(wldm||wlpm);  
   
  end;  
   
  2:   加参数的游标  
  declare    
      wldm   erp.itm_m.itm_wldm%type;  
      wlpm   erp.itm_m.itm_wlpm%type;  
      cursor   cur(v_wldm   varchar2)   is  
          select   itm_wldm,itm_wlpm   from   erp.itm_m     where   itm_wldm   =   v_wldm;  
  begin  
      open   cur('10001');  
      fetch   cur   into   wldm,wlpm;  
      close   cur;  
      dbms_output.put_line(wldm||wlpm);  
  end;  
   
  3:循环  
  set   serveroutput   on;  
  declare    
      wldm   erp.itm_m.itm_wldm%type;  
      wlpm   erp.itm_m.itm_wlpm%type;  
      cursor   cur   is  
          select   itm_wldm,itm_wlpm   from   erp.itm_m     ;  
  begin  
    /*   以下为   Loop   ...   End   Loop循环   */  
      open   cur;  
      loop  
          fetch   cur   into   wldm,wlpm;  
          exit   when   cur%notfound   or   cur%notfound   is   null;  
          dbms_output.put_line(cur%rowcount   ||'   '||wldm||'   '||wlpm);  
      end   loop;  
      close   cur;  
   
  /*   以下为while   循环*/  
      open   cur;  
      fetch   cur   into   wldm,wlpm;  
      while   cur%found   loop  
          dbms_output.put_line(cur%rowcount   ||'   '||wldm||'   '||wlpm);  
          fetch   cur   into   wldm,wlpm;  
      end   loop;  
      close   cur;      
  /*   以下为For   循环*/      
      for   c_cur   in   cur   loop  
            dbms_output.put_line(cur%rowcount   ||'   '||wldm||'   '||wlpm);  
      end   loop;      
  end;  
   
  4:   使用bulk   collect  
  declare  
      type   tp1   is   table   of   erp.itm_m.itm_wlpm%type;  
      type   tp2   is   table   of   erp.itm_m.itm_wlgg%type;  
      v1   tp1;  
      v2   tp2;  
      cursor   cur   is  
          select   itm_wlpm,itm_wlgg   from   erp.itm_m;  
  begin  
      open   cur;  
      fetch   cur   bulk   collect   into   v1,v2;  
      close   cur;  
      for   i   in   1..v1.count   loop  
          dbms_output.put_line(v1(i)||'   '   ||v2(i));  
      end   loop;  
  end;  
   
  5:   for   Update  
  set   serveroutput   on;  
  declare    
      wldm   erp.itm_m.itm_wldm%type;  
      wlpm   erp.itm_m.itm_wlpm%type;  
      cursor   cur   is  
          select   itm_wldm,itm_wlpm   from   erp.itm_m   for   update   ;  
  begin  
      for   c_cur   in   cur   loop  
          update   erp.itm_m   set   itm_wlpm   =   itm_wlpm   ||   '1'  
              where   current   of   cur;  
      end   loop;  
  end;  
   
   
   
   
   
   
   
  [if-else-endif]  
  set   serveroutput   on;  
  declare   a   int   :=5;  
    v1   erp.itm_m%rowtype;  
  begin  
      select   *   into   v1   from   erp.itm_m   where   itm_wldm   =   '10001';  
      if   a<3   then  
          dbms_output.put_line(v1.itm_wlpm);  
      elsif   a<5   then                                                                 --   是elsif   不是   elseif  
          dbms_output.put_line(v1.itm_wlgg);  
      else  
          dbms_output.put_line(v1.itm_wldm);  
      end   if;  
  end;  

[loop]  
   
  declare   i   int:=0;  
  begin    
  loop    
      i   :=i+1;  
      dbms_output.put_line(i);  
      if   i>=3   then  
          exit;  
      end   if;  
  end   loop;  
   
  loop  
      i:=i-1;  
      dbms_output.put_line(i);  
      exit   when   i<=0;  
  end   loop;  
   
  while   i<=3   loop  
      i:=i+1;  
      dbms_output.put_line(i);  
  end   loop;  
  dbms_output.put_line('i='||i);  
   
  for   i   in   1..3   loop  
  --     此处不能赋值i:=i+1;  
      dbms_output.put_line(i);  
  end   loop;  
  dbms_output.put_line('i='||i);  
   
  for   i   in   reverse   1..3   loop  
  --     此处不能赋值i:=i+1;  
      dbms_output.put_line(i);  
  end   loop;  
  dbms_output.put_line('i='||i);  
  end;  
   
   
   
   
   
   
   
   
   
   
   
  [goto]  
  declare   i   int:=0;  
  begin    
      dbms_output.put_line(i);  
      loop  
          i:=i+1;  
          dbms_output.put_line(i);  
          exit   when   i>=5;  
          if   i=3   then  
              goto   xxx;  
          end   if;  
      end   loop;  
      return;  
          <<xxx>>  
      begin  
          dbms_output.put_line('end     '||i);  
      end   xxx;  
   
      dbms_output.put_line('end     '||i);  
  end;  

 [exception]  
  --   Exception  
  declare   x   numeric:=10;y   numeric:=0;z   numeric;  
  begin  
      z:=x/y;  
      dbms_output.put_line(z);  
      exception  
          when   others   then  
          dbms_output.put_line('不能被0除');  
  end;  
   
   
  --   Raise   自定义Exception  
  declare   my_exception   exception;  
    x   numeric:=10;y   numeric:=20;z   numeric;  
  begin  
      if     x<y   then  
          raise   my_exception;  
      end   if;  
      z:=x/y;  
      dbms_output.put_line(z);  
      exception  
          when   my_exception   then  
              dbms_output.put_line('被除数太小');  
          when   zero_divide   then  
              dbms_output.put_line('不能被0除');  
  end;  
   
   
   
   
   
  [top]  
  --   我們想把最先進入公司的5個人找出來  
  SELECT   *   FROM   emp   WHERE   ROWNUM   <=   5   ORDER   BY   hiredate;  
  或  
  SELECT   *   FROM   (SELECT   *   FROM   emp   ORDER   BY   hiredate)  
        WHERE   ROWNUM   <=   5;  
   
  --   我們想把最先進入公司的6~10個人找出來!!!!!!!!!!!!!!  
  SELECT   *   FROM   (SELECT   *   FROM   emp   ORDER   BY   hiredate)  
        WHERE   ROWNUM   <=   10    
  MINUS  
  SELECT   *   FROM   (SELECT   *   FROM   emp   ORDER   BY   hiredate)  
        WHERE   ROWNUM   <=   5;  
   
   
  --测试：  
  create   table   test(depID   varchar2(1),Name   varchar2(10));  
  insert   into   test   values('A',             '一');  
  insert   into   test   values('A'   ,           '二');  
  insert   into   test   values('A'   ,           '三');  
  insert   into   test   values('B'     ,         '四');  
  insert   into   test   values('B'       ,       '五');  
  insert   into   test   values('B'   ,           '六');  
  insert   into   test   values('C'         ,     '七');  
  insert   into   test   values('C'           ,   '八');  
  insert   into   test   values('C'   ,           '九');  
  --语句：  
  select   *   from   test   a  
  where   rowid   in   (select   rowid   from   test   where   depid   =   a.depid   and   rownum<3);  
  /*结果  
  D   NAME  
  -   ----------  
  A   一  
  A   二  
  B   四  
  B   五  
  C   七  
  C   八  
   
  已选择6行。  
  */  
   
   
   
   
  rownum是随着结果集生成的，一旦生成，就不会变化了；  
  同时,生成的结果是依次递加的，没有1就永远不会有2!      
  rownum   是在   查询集合产生的过程中产生的伪列，并且如果where条件中存在   rownum   条件的话，则:      
  1：   假如   判定条件是常量，则：      
  只能   rownum   =   1,   <=   大于1   的自然数，   =   大于1   的数是没有结果的，   大于一个数也是没有结果的      
  即   当出现一个   rownum   不满足条件的时候则   查询结束  

[transaction]  
   
  set   serveroutput   on;  
  declare   n   int;  
  begin  
      savepoint   sp1;  
      update   erp.inv_m   set   inv_kcsl   =   inv_kcsl+10;  
      n:=SQL%rowcount;  
      dbms_output.put_line(n);  
       
      savepoint   sp2;  
      update   erp.inv_m   set   inv_kcsl   =   inv_kcsl+10;  
      n:=SQL%rowcount;  
      dbms_output.put_line(n);  
       
      rollback   to   savepoint   sp2;      
  end;  
   
   
   
   
  [trigger]  
  简单的：  
  create   or   replace   trigger   t_itm_m  
  after   insert   on   erp.itm_m  
  for   each   row  
  begin  
      insert   into   erp.inv_m   select   :new.itm_wldm,100   from   dual;    
  end;  
   
  create   or   replace   trigger   t_inv_m  
  before   update   on   erp.inv_m  
  for   each   row  
  begin  
      dbms_output.put_line(:old.inv_wldm||'   更改前：':old.inv_kcsl||'   更改后:'||:new.inv_kcsl);      
  end;  
   
   
  系统触发器：  
  create   table   erp.log_table  
  (  
      log_user   varchar2(30),  
      database_name   varchar2(30),  
      event_name   varchar2(20),  
      log_time   date  
  );  
  1:登录  
  create   or   replace   trigger   user_logon  
  after   logon   on   database  
  begin  
      insert   into   erp.log_table   (log_user,database_name,event_name,log_time)  
          values(sys.login_user,sys.database_name,sys.sysevent,sysdate);  
  end;  
   
  2：退出登录  
  create   or   replace   trigger   user_logoff  
  before   logoff   on   database  
  begin  
      insert   into   erp.log_table   (log_user,database_name,event_name,log_time)  
          values(sys.login_user,sys.database_name,sys.sysevent,sysdate);  
  end;  
   
  3：启动数据库  
  create   or   replace   trigger   db_startup  
  after   startup   on   database  
  begin  
      insert   into   erp.log_table   (log_user,database_name,event_name,log_time)  
          values(sys.login_user,sys.database_name,sys.sysevent,sysdate);  
  end;  
   
   
  4:关闭数据库  
  create   or   replace   trigger   db_startup  
  before   shutdown   on   database  
  begin  
      insert   into   erp.log_table   (log_user,database_name,event_name,log_time)  
          values(sys.login_user,sys.database_name,sys.sysevent,sysdate);  
  end;  
   
  5:   记录创建表时的各类信息  
  create   table   erp.object_created  
  (  
      object_owner   varchar2(30),  
      object_name   varchar2(30),  
      object_type   varchar2(20),  
      create_user   varchar2(30),  
      create_time   date  
  );  
   
  create   or   replace   trigger   t_create_object  
  after   create   on   erp.schema  
  begin  
      insert   into   erp.object_created(  
          object_owner,object_name,object_type,create_user,create_time)  
      values(sys.dictionary_obj_owner,sys.dictionary_obj_name,sys.dictionary_obj_type,sys.login_user,sysdate);  
  end;  
   
   
   
   
  [function]  
  create   or   replace   function   f_getSum(wldm   varchar2)  
  return   numeric   as  
      v_num   numeric(10);  
  begin  
      select   sum(inv_kcsl)   into   v_num  
          from   erp.inv_m   where   inv_wldm   like   wldm   ||   '%';  
      return   v_num;  
  end;  
   
   
  --调用1：  
  set   serveroutput   on;  
  declare   v_nm   numeric(10);  
  begin  
      v_nm   :=   f_getsum('101');  
      dbms_output.put_line(v_nm);  
  end;  
   
  --调用2：  
  select   f_getSum('101')   from   dual;  
   
   
   
  [procedure]  
  create   or   replace   procedure   sp_inv_sl(v_sl   in   out   numeric,   v_flag   in   numeric   :=0)  
  as  
  begin  
      if   v_flag   =   -1   then  
          v_sl   :=   -v_sl;  
      else  
          v_sl   :=   v_sl;  
      end   if;  
      update   erp.inv_m   set   inv_kcsl   =   inv_kcsl   +   v_sl;  
  end;  
   
  --调用:  
  declare   sl   numeric(10)   ;  
  begin  
  sl   :=10;  
  sp_inv_sl(sl,-1);  
  end;  
   

    [package]  
   
  /*包头*/  
   
  create   or   replace   package   my_pack  
  as  
  function   f_test(n   integer)   return   integer;  
  end   my_pack;  
   
  /*包主体*/  
  create   or   replace   package   body   my_pack   as  
   
  function   f_test(n   integer)   return   integer   is  
  begin  
      return   1;  
  end   f_test;  
   
  end   my_pack;  
  /*     注意：   包头和包主体需分开编译*/  
   
   
  eg:  
  create   or   replace   package   my_pack   as  
      type   staff   is   table   of   varchar2(10);  
      procedure   proc1(members   in   staff);  
  end   my_pack;  
   
  create   or   replace   package   body   my_pack   as  
      procedure   proc1(members   in   staff)   is  
      begin  
          if   members.count   >   10   then  
              dbms_output.put_line('第一个为'   ||   members(members.first));  
          else  
              dbms_output.put_line('最后一个为'   ||   members(members.last));  
          end   if;  
      end;  
  end   my_pack;  
   
  declare  
      my_staff   my_pack.staff;  
      begin  
          my_staff   :=my_pack.staff('张三','李四','王五');  
          my_pack.proc1(my_staff);  
      end;  

[嵌套表]  
   
   
  1:定义  
  create   or   replace   type   student   as   object  
  (  
      vid   number(4),  
      vname   varchar2(10),  
      vsex   varchar2(2),  
      vaddress   varchar2(40)  
  );  
   
  create   type   studentlist   as   table   of   student;  
   
  create   table   nest_table1  
  (  
      vid   number(4),  
      vbj   varchar2(14),  
      students   studentlist  
  )  
  nested   table   students   store   as   students_tab;  
   
  2:   Insert  
  declare   v_student   studentlist   :=studentlist(student(12,'张学友','男','深圳'));  
  begin  
      insert   into   nest_table1   values(1002,'歌星',v_student);  
      insert   into   nest_table1   values(1003,'球星',studentlist(student(13,'贝利','男','巴西')));  
  end;  
   
  3:   Update  
  update   nest_table1   set   students   =   studentlist(student(12,'张学友','男','香港'))  
  where   vid   =   1002;  
   
  update   the(select   students   from   nest_table1   where   vid   =   1001)  
  set   vaddress   =   '香港';  
   
  4:   Select  
  select   *   from   nest_table1;  
   
  select     the(select   students   from   nest_table1   where   vid   =   1001)  
  from   dual;  

[索引表]  
  1:基本索引表    
  set   serveroutput   on;  
  declare    
      type   my_table   is   table   of   varchar(6)   index   by   binary_integer;  
      v_mytable   my_table;  
  begin  
      v_mytable(1)   :=   'a';  
      v_mytable(2)   :=   'b';  
      v_mytable(3)   :=   'c';  
      dbms_output.put_line(v_mytable(1)||v_mytable(2)||v_mytable(3));  
  end;  
   
  2：  
  declare  
      type   my_table   is   table   of   erp.itm_m%rowtype   index   by   binary_integer;  
      v_mytable   my_table;  
  begin  
      select   itm_wldm,itm_wlpm,itm_wlgg   into   v_mytable(1)  
          from   erp.itm_m   where   itm_wldm   =   '10001';  
      dbms_output.put_line(v_mytable(1).itm_wldm   ||v_mytable(1).itm_wlpm||v_mytable(1).itm_wlgg);      
  end;  
   
  3:  
  declare  
      type   my_table   is   table   of   erp.itm_m%rowtype   index   by   binary_integer;  
      v_mytable   my_table;  
  begin  
      select   itm_wldm,itm_wlpm,itm_wlgg   into   v_mytable(1)  
          from   erp.itm_m   where   itm_wldm   =   '10001';  
      if   v_mytable.exists(1)   then  
          dbms_output.put_line(v_mytable(1).itm_wldm   ||v_mytable(1).itm_wlpm||v_mytable(1).itm_wlgg);      
      else  
          dbms_output.put_line('不存在');  
      end   if;  
  end;  
   
  4:  
  declare  
      type   my_table   is   table   of   erp.itm_m%rowtype   index   by   binary_integer;  
      v_mytable   my_table;  
  begin  
      select   itm_wldm,itm_wlpm,itm_wlgg   into   v_mytable(2)  
          from   erp.itm_m   where   itm_wldm   =   '10001';  
      if   v_mytable.exists(1)   then  
          dbms_output.put_line(v_mytable(1).itm_wldm   ||v_mytable(1).itm_wlpm||v_mytable(1).itm_wlgg);      
      else  
          dbms_output.put_line('不存在');  
      end   if;  
           
      dbms_output.put_line(v_mytable(1).itm_wldm   ||v_mytable(1).itm_wlpm||v_mytable(1).itm_wlgg);      
      exception  
          when   no_data_found   then  
              dbms_output.put_line('找不到');  
  end;  

[同义词]  
   
  1:如下语句  
  select   *   from   erp.tableName   where   rownum   =   1;  
  因为表tableName是建在erp这个方案上的，每次都要写   erp.tableName才行，有没有什么设置可以用erp用户登录后就不用再写erp.tableName而直接写tableName  
   
  解决办法：  
  1：  
  建同义词：  
  CREATE   PUBLIC   SYNONYM   tableName   FOR   PLAN_TABLE;  
  授权：  
  GRANT   ALL   ON   tableName   TO   PUBLIC;  
   
   
  2:    
  用ERP用户，以NORMAL身份登录，而不是以SYSDBA身份登录。  
   
   
   
   
  同义词:  
  grant   execute   on   hello_world   to   public;  
  create   public   synonym   hello_world   for   erp.hello_world;

  [动态SQL]  
   
  1:  
  set   serveroutput   on;  
  declare    
      v1   erp.itm_m.itm_wldm%type;    
  begin  
      execute   immediate   'select   itm_wldm   from   erp.itm_m   where   rownum   =1'  
          into   v1;  
          dbms_output.put_line(v1);  
  end;  
   
  2:  
  set   serveroutput   on;  
  declare    
      type   t1   is   table   of   erp.itm_m.itm_wldm%type;  
      v1   t1;  
  begin  
      execute   immediate   'select   itm_wldm   from   erp.itm_m   where   rownum   <=3'  
          bulk   collect   into   v1;  
      for   i   in   v1.first   ..   v1.last   loop  
          dbms_output.put_line(v1(i));  
      end   loop;  
  end;  
   
   
  3:改进性能  
  create   procedure   test(par   number)   as  
  begin  
  execute   immediate   ''  
  end;

  [行列转换]  
  http://community.csdn.net/Expert/topic/3222/3222974.xml?temp=.5841181  
   
  col1             col2  
  -----------------  
  0001             Tom  
  0001             Jack  
  0001             Mike  
  0002             Kate  
  0002             Rose  
  0002             Fene  
   
  如何用一条SQL语句得到如下结果：  
  col1           col2  
  -----------------  
  0001           Tom,Jack,Mike  
  0002           Kate,Rose,Fene  
   
   
   
  创建自定义函数：  
  create     or     replace     function     f_ename(jobno     in     varchar2)      
  return     varchar2     is      
         
  str_return     varchar2(200);      
  cursor     c_ename(v_jobno     varchar2)     is     select     ename     from     emp     where     job=v_jobno;      
  begin      
  for     str_temp     in     c_ename(jobno)     loop      
  str_return:=str_return||str_temp.ename;      
  end     loop;      
  return     str_return;      
  end;      
  /      
   
  调用：  
  select   job,f_ename(job)   from   (select   distinct   job   from   emp);  
   
   
   
      oracle   如何做行列动态的交叉表    
  http://community.csdn.net/Expert/topic/3093/3093861.xml?temp=.5882379  
  /*  
  Create   table   test   (name   varchar2(10),km   varchar2(10),cj   number)  
  go  
  insert   into   test   values('张三','语文',80);  
  insert   into   test   values('张三','数学',86);  
  insert   into   test   values('张三','英语',75);  
  insert   into   test   values('李四','语文',78);  
  insert   into   test   values('李四','数学',85);  
  insert   into   test   values('李四','英语',78);  
  select   *   from   test;  
   
  select   name,  
    sum(decode(km,'语文',cj,0))   as   语文,  
    sum(decode(km,'数学',cj,0))   as   数学,    
    sum(decode(km,'英语',cj,0))   as   英语  
  from   test  
  group   by   name;  
  */  
   
  CREATE   OR   REPLACE   PACKAGE   Pack_Test  
  AS  
  TYPE   QUERY_CURSOR   IS   REF   CURSOR;  
  PROCEDURE   My_cur_out(  
  result   out   QUERY_CURSOR);  
  END   Pack_Test;  
   
   
  CREATE   OR   REPLACE   PACKAGE   BODY   Pack_Test  
  AS  
  PROCEDURE   My_cur_out(  
  result   out   QUERY_CURSOR  
  )  
          AS  
                  BEGIN  
                   
  DECLARE    
        v_result   varchar2(4000);  
        v_table   varchar2(255):='TEST';--表名  
        v_col1   varchar2(255):='NAME';   --表的第一个列  
        v_col2   varchar2(255):='KM';       --表的第二个列  
        v_col3   varchar2(255):='CJ';       --表的第三个列  
        v_value   varchar2(255);  
        v_file   utl_file.file_type;  
        TYPE   cur_type   IS   REF   CURSOR;  
        cur_row       cur_type;  
  BEGIN  
      v_result   :=   'SELECT   '||v_col1;  
      OPEN   cur_row   FOR   'SELECT   DISTINCT   '||v_col2||'   FROM   '||v_table;  
      LOOP  
          FETCH   cur_row   INTO   v_value;  
          EXIT   WHEN   cur_row%NOTFOUND;      
          v_result   :=   v_result||',sum(decode('||v_col2||','''||v_value||''','||v_col3||',''''))   as   '||v_value;        
      END   LOOP;  
      CLOSE   cur_row;  
      v_result   :=   v_result||'   from   '||v_table||'   group   by   '||v_col1;  
       
      OPEN   result   FOR   v_result;  
  END;  
                       
     
                  END;  
  END   Pack_Test;  
   
   
   
   
   
  http://community.csdn.net/Expert/topic/3179/3179325.xml?temp=1.267642E-02  
  pl/sql怎么返回数据集?  
  CREATE   OR   REPLACE   PACKAGE   pkg_test  
  AS  
        TYPE   myrctype   IS   REF   CURSOR;  
   
        PROCEDURE   get   (p_id   NUMBER,   p_rc   OUT   myrctype);  
  END   pkg_test;  
  /  
   
  CREATE   OR   REPLACE   PACKAGE   BODY   pkg_test  
  AS  
        PROCEDURE   get   (p_id   NUMBER,   p_rc   OUT   myrctype)  
        IS  
              sqlstr       VARCHAR2   (500);  
        BEGIN  
              IF   p_id   =   0   THEN  
                    OPEN   p_rc   FOR  
                          SELECT   ID,   NAME,   sex,   address,   postcode,   birthday  
                              FROM   student;  
              ELSE  
                    sqlstr   :=  
                          'select   id,name,sex,address,postcode,birthday  
                        from   student   where   id=:w_id';  
                    OPEN   p_rc   FOR   sqlstr   USING   p_id;  
              END   IF;  
        END   get;  
  END   pkg_test;  
  /  

[自增]  
  oracle中怎样将字段建成象SQL2000中的ID自动+1的字段  
    两种方法  
  方法一：  
  　　用触发器  
   
  建一个序列  
        create   sequence   a_seq   increment   by   1   start   with   100;  
  建一个触发器,   自动+1  
  create   or   replace   trigger   your_seq_tri  
  before   insert   on   your_table1   for   each   row  
  declare  
      next_id   number;  
  begin  
      select   your_seq.nextval   into   next_id   from   dual;  
      :new.id   :=   next_id;  
  end;  
   
   
  方法二：  
  　　建一个序列  
            create   sequence   a_seq   increment   by   1   start   with   100;  
        在语句中+1  
  　　insert   into   tbl(id,....)  
            values   (a_seq.nextval,....)  

[object]  
   
  create   or   replace   type   item   as   object  
  (  
      wldm   varchar2(15),         --明确的类型和长度，不能用%type  
      wlpm   varchar2(20),  
      wlgg   varchar2(40),  
      member   function   get_wldm   return   varchar2,         --   此处不能用   %type,不能指定varchar2的位数  
      member   function   get_pmgg   return   varchar2,  
      member   procedure   set_wldm(mwldm   in   varchar2)         --   同上 (in可以不需要)  
  )   not   final;  
   
   
  create   or   replace   type   body   erp.item   as  
      member   function   get_wldm   return   varchar2   is  
      begin  
          return   wldm;  
      end;  
       
      member   function   get_pmgg   return   varchar2   is  
      begin  
          return   wlpm   ||   wlgg;  
      end;  
      member   procedure   set_wldm(mwldm   varchar2)   is  
      begin  
          wldm   :=   mwldm;  
      end;  
           
  end;  
   
   
  成员函数，如果self没被声明，它的参数缺省为IN，然而在成员过程，如果SELF没被声明，它的参数模式缺省为IN   OUT，不能指定SELF的参数模式为OUT.  
   
   
   
  ALTER   TYPE   ERP.ITEM   ADD   ATTRIBUTE(WLPM   VARCHAR2(20));  
  ALTER   TYPE   ERP.ITEM   ADD   ATTRIBUTE(WLGG   VARCHAR2(40));  
  ALTER   TYPE   ERP.ITEM   DROP   ATTRIBUTE   WLPM;  
  ALTER   TYPE   ERP.ITEM   DROP   ATTRIBUTE   WLGG;  
   
   
  ALTER   TYPE   ERP.ITEM   ADD   MEMBER   FUNCTION   get_pmgg   return   varchar2;  
   
  drop   type   body   erp.item;  
  drop   type   erp.item;  
   
  调用示例：  
  declare   itm   erp.item   :=   erp.item('1','a','b');  
  begin  
      dbms_output.put_line(itm.wlpm   ||   '     '   ||   itm.get_pmgg);  
  end;  
   
  /*************************对象存入数据表字段***************************/  
  create   table   erp.test_obj  
  (  
      id   integer,  
      itm   erp.item  
  );  
  1:  
  insert   into   erp.test_obj   values(1,   erp.item('10001','歌星','刘德华')   );  
  2:  
  declare   itm   erp.item   :=   erp.item('10002','同事','刘文宏');  
  begin  
      insert   into   erp.test_obj   values(2,itm);  
      insert   into   erp.test_obj   values(3,erp.item('10003','同事','钟俊荣'));  
  end;  
  3:  
  update   erp.test_obj   D   set   D.itm.wlgg   =   '蔡然'  
  where   id   =   3;  
   
  declare   v_itm   erp.item   :=   erp.item('10003','同事','蔡sir');  
  begin  
  update   erp.test_obj   d   set   d.itm   =   v_itm  
      where   id   =   3;  
  end;  
  /***********************对象表******************************/  
  create   table   erp.test_obj1   of   erp.item;  
  insert   into   erp.test_obj1   values('10000','同事','刘文宏');  
  insert   into   erp.test_obj1   select   *   from   erp.itm_m;  

############################复杂查询语句的使用#####################################
1.查询语句的使用
使用 select语句和子查询(subquery)可以从一个或多个表,视图,实体试图中返回数据.
 
1.1相关子查询
可以将子查询(as subquery)或in或exists当成where的一个条件的一部分,这样的查询称为子查询
  .where中可以包含一个select语句的子查询
  .where中可以包含in,exists语句
  .最多可以嵌套16层
  .层次过多会影响性能
  [例]简单子查询实例
  查询是否有的专家既以研究所的名义来申请基金项目,又以大学系为单位申请项目
  (按规定只能以一个单位来申请)
  SQL> create table univ_subject
  2    (
  3       name                 varchar2(12) not null,
  4       per_id                number     not null,
  5      dept_name       varchar2(20)            
  6    );
  SQL> insert into univ_subject  values('gaoqianjing',1001,'信息工程系');
  SQL> insert into univ_subject  values('wangbing',1002,'物理系');
  SQL> insert into univ_subject  values('liming',1003,'化学系');
  ===============
   SQL> create table  colle_subject
  2     (
  3              colle_name    varchar2(20),
  4              per_id              number
  5     );
  SQL> insert into colle_subject values('电子研究所',1001);
  SQL>  insert into colle_subject values('物理研究所',1005);
  ================
  SQL> select name,per_id,dept_name from univ_subject where per_id in
  2    (select per_id from colle_subject);

  NAME            PER_ID   DEPT_NAME
  ------------          ---------     --------------------
  gaoqianjing  1001      信息工程系

1.2外连接
 [例]外连接实例
 招生中所有学生的信息放在students表中,而部分有特长的学生在另一个表中stuent_skill中同样有该学生
 的信息。现在要全部列出所有学生,如果某个学生在表student_skill中就有其特长信息,并显示特长信息,如果
 某个学生没有特长就显示特长问空.
 SQL>  create table students
  2    (
  3       st_id    varchar2(20),
  4       name  varchar2(10),
  5       age      number(2),
  6       tol_score   number(3)
  7    ) ;
SQL>   insert into students values('973231','wangbindu',22,501);
SQL>   insert into students values('973232','zhuzhijing',21,538);
SQL>  insert into students values('973233','gaojing',21,576);
===================

SQL>  create table student_skill
  2   (
  3      st_id  varchar2(20),
  4      skill    varchar2(20)
  5  );
SQL>  insert into student_skill values('973231','篮球');
SQL>  insert into student_skill(st_id) values('973232');
SQL>  insert into student_skill values('973233','足球');
===================

SQL>   select a.* , b.skill from students a,student_skill b where a.st_id=b.st_id(+)
order by a.st_id;

ST_ID                NAME             AGE TOL_SCORE SKILL
-------------------- ---------- --------- --------- ------------------  --
973231               wangbindu         22       501        篮球
973232               zhuzhijing           21       538
973233               gaojing                21       576        足球

1.3自我连接
自我连接是在同一个表或视图内进行条件连接.
[例]自我连接实例
查询每个雇员的名字和该雇员的经理的名字:
SQL> select e1.ename||'   work for   '||e2.ename "Employees and their Managers"
  2  from  scott.emp e1,scott.emp e2 where e1.mgr=e2.empno;

Employees and their Managers
-------------------------------------------------
SMITH      work for   FORD
ALLEN     work for   BLAKE
WARD      work for   BLAKE
JONES     work for   KING
MARTIN   work for   BLAKE
BLAKE     work for   KING
CLARK     work for   KING
SCOTT      work for   JONES
TURNER  work for   BLAKE
ADAMS     work for   SCOTT
JAMES      work for   BLAKE
FORD        work for   JONES
MILLER     work for   CLARK

1.4UNION , INTERSECT及 MINUS
UNION:            可以将两个以上的表的相类似的查询结果放在一起 (union all则表示返回所有的行)
具体语法:
select ...
union[all]
select...
==========

INTERSECT:  返回两个表中相同的信息
具体语法:
select ...
intersect
select...
==========

MINUS          :  返回一个表中出现的信息
具体语法:
select ...
minus
select...
[例1]UNION操作实例
SQL> select  st_id  from students
  2  union
  3  select  st_id  from student_skill;

ST_ID
--------------------
973231
973232
973233

[例2]INTERSECT操作实例
列出有特长的学生的学号
SQL> select st_id from students
  2  intersect
  3  select st_id from student_skill;
ST_ID
--------------------
973231
973233

[例3]MINUS操作实例
列出没有特长学生的学号
select st_id from students
minus
select st_id from student_skill;
ST_ID
--------------------
973232

2.创建复杂的视图
许多应用系统有统计等功能,建议最好把这些复杂语句写成视图.下面是几个常用的视图.
2.1分组视图
[例1]简单的分组视图
SQL> create or replace view dept_tot as
  2  select a.dname dept,sum(b.sal) total_sal from scott.dept a,scott.emp b
  3  where a.deptno=b.deptno group by a.dname;

查看已建立。
SQL> select * from dept_tot;

DEPT                           TOTAL_SAL
--------------                     ---------
ACCOUNTING          8750
RESEARCH              10875
SALES                        9400

[例2]带复杂函数视图
SQL> create or replace view itemtot as
  2  select persion,sum(amount) itemtot from ledger
  3  where actiondate between
  4  to_date('01-MAR-1901','dd-mon-yyyy') and
  5  to_date('31-MAR-1901','dd-mon-yyyy')
  6  and action in('bought','raid') group by persion;

2.2合计视图
[例]合计函数视图实例
SQL> create or replace view emp_no1  as
  2  select deptno,sum(sal) 工资和,sum(comm) 总和
  3  from scott.emp group by deptno;
SQL> select * from emp_no1;
DEPTNO    工资和      总和
--------- --------- ---------
       10      8750
       20     10875
       30      9400      2200

2.3组合视图
[例]带组合函数的视图
SQL> create or replace view byitem as
  2  select l.persion persion.item, amount, 100*amount/item bypersion,100*amount/total bytotal
  3  from ledgent l,itemtotal i,total where l.persion=i.persion where l.persion=i.persion
  4  and actiondate between
  5  to_date('01-MAR-1901','dd-mon-yyyy') and
  6  to_date('31-MAR-1901','dd-mon-yyyy')
  7   and action in('bought','raid') ;

3.家族树
语法:
select column from table_name start with column=value
connect by prior 父主键=子主键

3.1排除单一性和分枝
以ORACLE中的EMP表为例
[例]从顶到底列出各雇员的信息
SQL> select lpad(' ',4*(level-1))||ename name,empno,mgr from emp start with mgr is null
  2  connect by prior empno=mgr;

NAME                                 EMPNO       MGR
---------                                  ---------           ---------
KING                                   7839           
    JONES                           7566            7839
          SCOTT                     7788            7566
                ADAMS              7876            7788

3.2遍历至根
[例1]现在要从某个雇员开始向他的上级列出该雇员的层次结构
SQL> col ename for a30;
SQL> select lpad(' ',4*(level-1))||ename ename,mgr,empno from scott.emp
  2  start with mgr=7788 connect by prior mgr=empno;
ENAME                                MGR     EMPNO
------------------------------         ---------    ---------
ADAMS                               7788      7876
    SCOTT                           7566       7788
        JONES                       7839       7566
            KING                                        7839

[例2]列出所有雇员的层次结构
SQL> select lpad(' ',4*(level-1))||ename ename,empno,mgr from scott.emp
  2  start with mgr is not null
  3  connect by empno=prior mgr;

ENAME                              EMPNO       MGR
------------------------------       ---------           ---------
SMITH                               7369          7902
    FORD                            7902         7566
        JONES                       7566        7839
            KING                    7839
ALLEN                               7499        7698
    BLAKE                           7698        7839
        KING                        7839
WARD                                7521        7698
    BLAKE                           7698        7839
        KING                        7839
JONES                               7566        7839
    KING                            7839
MARTIN                              7654        7698
    BLAKE                           7698         7839
        KING                        7839
BLAKE                               7698        7839
    KING                            7839
CLARK                               7782         7839
    KING                            7839
SCOTT                               7788         7566
    JONES                           7566        7839

 ==================================================

[增加顺序号]

情况:
有店铺A,B,C,记录N条,增加一个顺序号列(seq),让他们象下面的形式排列:

storecode seq name amt
 A                1        a1      1
 A                2        a2      1
 A                3        a3      1
 B                1        b1       1
 B                2        b2      1
 B                3        b3      1
 C                1        c1      1
 C                2        c2      1

 

CREATE OR REPLACE PACKAGE repPack115 as
  type resultset is ref cursor;
  procedure rep115(v_storecode varchar2,v_qsrq varchar2,v_zzrq varchar2,v_sortby varchar2,v_seq varchar2,ret_cursor out resultset);

end repPack115;
/
CREATE OR REPLACE package body repPack115
as
/*
   name:rep115   descirp:it can query the best sales product info between two period seperate by the store
   params:
       v_storecode :selected stores,let it null it want to select all stores
    v_qsrq   :the first date
    v_zzrq   :the last date
    v_sortby   :which coloumn you want to sort by
    v_seq    :asc or desc can selected
   
 ver:1.0     author:simon

*/
procedure rep115(v_storecode varchar2,v_qsrq varchar2,v_zzrq varchar2,v_sortby varchar2,v_seq varchar2,ret_cursor out resultset)
is
  tabl   number;
  sqlstr  varchar2(4000);
begin
  select count(*) into tabl from all_tables a where a.table_name='REP115_SALES';
 
  if tabl>0 then
   sqlstr := 'TRUNCATE TABLE ST1CNA.REP115_SALES';
  execute immediate sqlstr;
  sqlstr := 'INSERT INTO ST1CNA.REP115_SALES select rownum as qid,xf_storecode,itemno,chsdescription,engdescription,retailprice,saleqty,saleamount from (select c.xf_storecode,a.xf_plu as itemno,max(a.xf_desci) as chsdescription,max(a.xf_longdesc) as engdescription,max(b.xf_seluprice) as retailprice,sum(c.xf_qtysold) as saleqty,sum(xf_amtsold) as saleamount from xf_itemmas a,xf_priceh b,xf_vipitemdm c where a.xf_style=b.xf_style and a.xf_plu=c.xf_plu and c.xf_txdate<=to_date('''||v_zzrq||''',''yyyy-mm-dd'') and c.xf_txdate>=to_date('''||v_qsrq||''',''yyyy-mm-dd'') group by c.xf_storecode,a.xf_plu order by xf_storecode,'||v_sortby||' '||v_seq||') a ';
  execute immediate sqlstr;
  else
   sqlstr := 'create global temporary table ST1CNA.REP115_SALES on commit preserve rows as select rownum as qid,xf_storecode,itemno,chsdescription,engdescription,retailprice,saleqty,saleamount from (select c.xf_storecode,a.xf_plu as itemno,max(a.xf_desci) as chsdescription,max(a.xf_longdesc) as engdescription,max(b.xf_seluprice) as retailprice,sum(c.xf_qtysold) as saleqty,sum(xf_amtsold) as saleamount from xf_itemmas a,xf_priceh b,xf_vipitemdm c where a.xf_style=b.xf_style and a.xf_plu=c.xf_plu and c.xf_txdate<=to_date('''||v_zzrq||''',''yyyy-mm-dd'') and c.xf_txdate>=to_date('''||v_qsrq||''',''yyyy-mm-dd'') group by c.xf_storecode,a.xf_plu order by xf_storecode,'||v_sortby||' '||v_seq||') a ';
   execute immediate sqlstr;
 
  end if;
 
  if v_storecode is null then
   sqlstr := 'select * from (select case when (select count(*) from ST1CNA.REP115_SALES a where a.xf_storecode=ST1CNA.REP115_SALES.xf_storecode and a.qid<=ST1CNA.REP115_SALES.qid)<=30 then (select count(*) from ST1CNA.REP115_SALES a where a.xf_storecode=ST1CNA.REP115_SALES.xf_storecode and a.qid<=ST1CNA.REP115_SALES.qid) else 0 end qqid,xf_storecode,itemno,chsdescription,engdescription,retailprice,saleqty,saleamount from ST1CNA.REP115_SALES) b where qqid<>0';
  else
   sqlstr := 'select * from (select case when (select count(*) from ST1CNA.REP115_SALES a where a.xf_storecode=ST1CNA.REP115_SALES.xf_storecode and a.qid<=ST1CNA.REP115_SALES.qid)<=30 then (select count(*) from ST1CNA.REP115_SALES a where a.xf_storecode=ST1CNA.REP115_SALES.xf_storecode and a.qid<=ST1CNA.REP115_SALES.qid) else 0 end qqid,xf_storecode,itemno,chsdescription,engdescription,retailprice,saleqty,saleamount from ST1CNA.REP115_SALES) b where qqid<>0 and xf_storecode in '||v_storecode;
 
  end if;
 
  open ret_cursor for sqlstr;
 

end rep115;

end repPack115;
/