Oracle数据库-PL/SQL整理(2)

发表于:2007-06-22来源:作者:点击数: 标签:
--触发器失效 Forms_Ddl('alter trigger cascade_warprt_qty disable'); alter table t_Prthiswarinv disable all triggers; alter table t_Prthiswarinv enable all triggers; alter table T_PURORDER disable all triggers; alter table T_PURORDER enable

   

--触发器失效
Forms_Ddl('alter trigger cascade_warprt_qty disable');
alter table t_Prthiswarinv disable all triggers;
alter table t_Prthiswarinv enable all triggers;
alter table T_PURORDER disable all triggers;

alter table T_PURORDER enable all triggers;
--
--注意null与任何值比较皆为空。
 select decode(instr(null,'a'),0,0,null,2,1) from dual;
--抛出错误
      Raise_Application_Error(-20100,
               :New.Prtno ||
               '已经登帐的库存事务不能删除,必须进行调错处理!');
--不同用户之间共享数据
--在cpc下
grant select on T_LACKITEM to jh7;
revoke select on T_LACKITEM from jh7;
--在jh7下
create table t_lackitem as select * from cpc.t_lackitem
--在form中创建后台job
DECLARE
 Alert_Button  NUMBER;
 v_Job         NUMBER;
BEGIN
 Alert_Button := Display_Stop('是否确认结转全部库房?',
                :Global.Logon_User);
 --是否确认对截至日期内所选定库房的流水进行转移?
 IF Alert_Button <> Alert_Button1 THEN
  --raise form_trigger_failure;
  RETURN;
 END IF;
 v_Job := 489;
 Sys.Dbms_Job.Submit(v_Job,
           'cpcfcpgen;',
           SYSDATE + 1 / (24 * 60),
           'sysdate + 1000');
 COMMIT;
 Go_Item('t_mo.mono');
 Set_Item_Property('GEN_MRP.CONTINUE', Enabled, Property_False);
 Message('有限能力计划将在一分钟后开始生成,请耐心等待……');
EXCEPTION
 WHEN OTHERS THEN
  Display_Note(SQLCODE || SQLERRM, :Global.Logon_User);
END;
--获取查询条件
Tmp_Query01 := Get_Block_Property('T_PK', Last_Query);
--查找某库房、物料最后一条流水
--
create or replace view v_prtio as
Select invtranno,substr(invtranno,1,4)invtran,invstatus,prtno,warno,wardate,qtyonhand,1 columnno
    From t_prtio
--
Select invtranno
  From (Select invtranno,
               prtno,
               Sum(columnno) over(Partition By prtno,warno Order By  wardate Desc,substr(invtranno,instr(invtranno,'0'))desc,Rowid Desc ) n
         From v_prtio)
 Where n = 1
--查找流水错误(当前库存数量与台帐不一致)
Select a.prtno,invtranno From (Select invtranno, prtno,warno,qtyonhand From t_prtio Where  invtranno In(Select invtranno From (Select invtranno, prtno,Sum(columnno) over(Partition By prtno,warno Order By  wardate Desc,substr(invtranno,instr(invtranno,'0'))desc,Rowid Desc ) n From v_prtio Where ((invtran = 'JZRK' And qtyonhand != 0 ) Or (invtran != 'JZRK'))And invstatus != 'B') Where n = 1))a,t_warprt b Where a.prtno = b.prtno And a.warno = b.warno And a.qtyonhand != b.onhandqty;
--用Sql语句实现查找一列中第N大值
--
select * from
(select t.*,dense_rank() over (order by sal) rank from employee)
where rank = N;
--
select * from
(select prtno,dense_rank() over (order by qtyonhand) n,qtyonhand from t_prtio)
Where n = 1
--
--查询2005年全年日期
select to_date('20050101','yyyymmdd')+rownum-1
from all_objects
where rownum <= to_char(to_date('20051231','yyyymmdd'),'ddd');
--
--判断是否可以输入
DECLARE
  pl_id   ParamList;
  v_string varchar2(1000);
  v_itemname varchar2(1000);
  enter_enabled VARCHAR2(100); --是否可以输入
BEGIN
  v_itemname:=:system.current_block||'.'||:system.current_item;
  enter_enabled:=get_item_property(v_itemname,ENTERABLE);
  pl_id := Get_Parameter_List('calendar');
  IF NOT Id_Null(pl_id) THEN
    Destroy_Parameter_List(pl_id);
  END IF;
  pl_id := Create_Parameter_List('calendar');
  v_string:=name_in(v_itemname);
  Add_Parameter(pl_id, 'p_str',TEXT_PARAMETER,v_string);
  Call_Form('input_str',no_hide,no_replace,no_query_only,pl_id);
  if enter_enabled='TRUE' then 
  copy(:global.str,v_itemname);
  else
    null;
  END IF;
  Erase(:global.str);
END;
--保持同步
Synchronize;
--更新配套单状态
declare
  cursor c_pk is select pkno from t_pk;
begin
  for c_1 in c_pk loop
   update t_pkitem2 set  pkno=pkno where pkno=c_1.pkno
and rownum = 1;
 end loop;
end;
--游标变量
declare
  type v_cursor is ref cursor;
  cur_pknew v_cursor;
begin
  if :blk_pk.pkrule=1 then
    open cur_pknew for select ……
  elsif :blk_pk.pkrule=2 then
    open cur_pknew for select ……
  end if;
  loop
    fetch cur_pknew into ……
    exit when cur_pknew%notfound;
  end loop;
  close cur_pknew;
end;
--转移数据
DECLARE
  sql_cursor   integer;
  retu         integer;
  n_position   integer;
  n_len        integer;
  button_value number;
  v_where      varchar2(1000):= :system.last_query;
BEGIN
  set_alert_property('stop_alert',alert_message_text,
  '确认把监控记录导入到历史数据库?导出后,当前监控记录将被清空!');
  bell;
  button_value := show_alert('stop_alert');
  IF button_value = alert_button1 then
     n_position:= INSTR(v_where,'WHERE');
     if n_position > 0 then
        v_where := SUBSTR(v_where, n_position);
        n_position:= INSTR(v_where,'order');
        v_where := SUBSTR(v_where,1, n_position-1);
     else
        v_where := ' ';
     end if;
     message('正在导出到历史记录,请稍等...',NO_ACKNOWLEDGE);
     sql_cursor := dbms_sql.open_cursor;
     dbms_sql.parse(sql_cursor,'insert into t_monitor_his select * from  t_monitor '||v_where,1);
     retu := dbms_sql.execute(sql_cursor);
     dbms_sql.parse(sql_cursor,'delete from  t_monitor '||v_where,1);
     retu := dbms_sql.execute(sql_cursor);
     dbms_sql.close_cursor(sql_cursor);
     commit work;
     message('保存完毕!',NO_ACKNOWLEDGE);
     go_block('t_monitor');
     --clear_block;
     execute_query;
  END IF; 
END;
--显示终端用户
select userenv('terminal') from dual;
--更新部门不一致的数据
Update jh7.lean_user a Set a.deptno=(Select 
quality.t_user.deptno  from quality.t_user Where
quality.t_user.username = a.username)
Where Exists (Select 'a' From quality.t_user Where username = a.username And deptno != nvl(a.deptno,'&'));
--块中项目的控制.
Declare
 vItemName t_fundef.funcode%TYPE;
 vFunflag varchar2(1);
 vBlock varchar2(30);
BEGIN
 vBlock := 'BLK_MPS';
 vItemName := Get_Block_Property(vBlock,First_item);
     WHILE ( vItemName IS NOT NULL ) LOOP
       Begin
         Select to_char(f_i_value) into vFunFlag From t_Parameter
           Where f_name = vItemName;
         IF vFunFlag = '0' then
          if vItemName = 'MPSFPRMT' then
           :BLK_MPS.MPSFPRMT := '0';
          elsif vItemName = 'MPSFPRMT1' then
           :BLK_MPS.MPSFPRMT1 := '0';
          elsif vItemName = 'MPSEOMTN' then
           :BLK_MPS.MPSEOMTN := '0';
          elsif vItemName = 'SALORMTN' then
           :BLK_MPS.SALORMTN := '0';
          end if;
         END IF;
         
       Exception When NO_DATA_FOUND Then
  null;
       End;
       vItemName := Get_Item_Property(vBlock||'.'||vItemName, NEXTITEM );
    END LOOP;

exception
 when others then
  null;
END;
--example
FUNCTION Stat_Roll(p_Sign VARCHAR2) RETURN NUMBER IS
  --月终统计
  --vCurrentForm Varchar2(30);
  Vblockname  VARCHAR2(30);
  Vitemname   VARCHAR2(30);
  v_Sumsign   NUMBER := 0;
  v_Sumroll   NUMBER := 0;
  v_Itemvalue VARCHAR2(3);
BEGIN
  --vCurrentForm := upper(GET_APPLICATION_PROPERTY(CURRENT_FORM_NAME));
  --vBlockName := upper(Get_Form_Property(vCurrentForm,First_Block));
  Vblockname := 'T_ROLLBOOK';
  --Vitemname  := Upper(Get_Block_Property(Vblockname, First_Item));
  Vitemname := 'DATE01';
  WHILE (Vitemname IS NOT NULL) LOOP
    v_Itemvalue := Name_In('T_ROLLBOOK' || '.' || Vitemname);
    BEGIN
      SELECT Decode(v_Itemvalue, p_Sign, 1, NULL, 0, 0)
        INTO v_Sumsign
        FROM Dual;
    EXCEPTION
      WHEN OTHERS THEN
        v_Sumroll := 0;
    END;
 
    v_Sumroll := v_Sumroll + v_Sumsign;
    IF Vitemname = 'DATE31' THEN
      EXIT;
    END IF;
 
    Vitemname := Get_Item_Property(Vblockname || '.' || Vitemname, Nextitem);
  END LOOP;
  --vBlockName := Get_Block_Property( vBlockName, NEXTBLOCK );
  RETURN v_Sumroll;
END;
--
--获取复选框的值
IF Checkbox_Checked(:SYSTEM.CURRENT_ITEM) THEN
 null;
ELSE
 NULL;
END IF;
--库存差错
SELECT *
 FROM (SELECT Warno, SUM(Onhandqty * Prtlprc) Qty
     FROM t_Waritemprt a, t_Prtbasdef b
     WHERE a.Prtno = b.Prtno
     GROUP BY a.Warno) g,
    (SELECT Warno, SUM(Onhandqty * Prtlprc) Qty
     FROM t_Warprt a, t_Prtbasdef b
     WHERE a.Prtno = b.Prtno
     GROUP BY a.Warno) b,
    (SELECT Warno, SUM(Onhandqty * Prtlprc) Qty
     FROM t_Prthisinvitem a, t_Prtbasdef b
     WHERE a.year= 2005 And a.Period = '4' AND a.Prtno = b.Prtno
     GROUP BY a.Warno) t
 WHERE g.Warno = b.Warno AND g.Warno = t.Warno AND
    (g.Qty != b.Qty OR g.Qty != t.Qty);
--查询子项皆为完成而主单仍为正常的申请单
Select appno From t_msi Where appno in(Select Distinct appno From t_msiitem Where appno in(Select appno From (Select appno,Count(Distinct appnoflag) counts From t_msiitem  Group By appno) Where counts = 1) And appnoflag = '4') And msiflag = '0'
--动态构造值列表
DECLARE
  rg_name VARCHAR2(40) := 'group_VALUE';
  rg_id   RecordGroup;
  lov_id LOV;
  errcode NUMBER;
  V_Counts number;
BEGIN
  /* ** Make sure group doesn't already exist */
  rg_id := Find_Group(rg_name);
  /* ** If it exist, delete it.  */
 IF NOT Id_Null(rg_id) THEN
  Delete_Group( rg_id );
 END IF;
  /*create it and add the two ** necessary columns to it. */
  --IF :T_ROLEOPERATE.fieldname = 'GLOODGROUP' THEN
  SELECT COUNT(*) INTO v_Counts FROM T_listval where flags =  :T_ROLEOPERATE.fieldname;
 IF V_Counts >= 1 THEN
   rg_id := Create_Group_From_Query(rg_name,
                                    'SELECT distinct '||:T_ROLEOPERATE.fieldname||' value ' ||
                                    ' from t_hrmuser');
   --SET_ITEM_PROPERTY('T_ROLEOPERATE.fieldname',LOV_NAME,'LOV_VALUE');                                
 ELSE
   rg_id := Create_Group_From_Query(rg_name,
                                    'SELECT distinct '||:T_ROLEOPERATE.fieldname||' value ' ||
                                    ' from t_hrmuser');
   --SET_ITEM_PROPERTY('T_ROLEOPERATE.fieldname',LOV_NAME,'');                                                                   
  END IF;
 lov_id := Find_LOV('LOV_VALUE');                                   
  errcode := Populate_Group( rg_id );    
  Set_LOV_Property(lov_id,GROUP_NAME,'group_VALUE');
  :T_Roleoperate.Value := null;
  EXCEPTION WHEN others then
   display_note(sqlcode||sqlerrm, :global.logon_user); 
END;
--调用的FORM仅允许查询。
CALL_FORM('Hrmmenmtn',no_hide,no_replace,query_only,pl_id);
--赋初始值
Default_Value( '0', ptr_name );
--要找到某月中所有周五的具体日期
  select to_char(t.d,'YY-MM-DD') from (
  select trunc(sysdate, 'MM')+rownum-1 as d
  from dba_objects
  where rownum < 32) t
  where to_char(t.d, 'MM') = to_char(sysdate, 'MM') --找出当前月份的周五的日期
  and trim(to_char(t.d, 'Day')) = '星期五'
  --------
  03-05-02
  03-05-09
  03-05-16
  03-05-23
  03-05-30
  
  如果把where to_char(t.d, 'MM') = to_char(sysdate, 'MM')改成sysdate-90,即为查找当前月份的前三个月中的每周五的日期。
--rownum的用法:
    只能用以上符号(<、<=、!=)。
  select * from tablename where rownum != 10;返回的是前9条记录。
  不能用:>,>=,=,Between...and。由于rownum是一个总是从1开始的伪列,Oracle 认为这种条件 不成立,查不到记录.
  
  另外,这个方法更快:
  select * from (
  select rownum r,a from yourtable
  where rownum <= 20
  order by name )
  where r > 10
  这样取出第11-20条记录!(先选再排序再选)
  要先排序再选则须用select嵌套:内层排序外层选。
  
  rownum是随着结果集生成的,一旦生成,就不会变化了;同时,生成的结果是依次递加的,没有1就永远不会有2!
  rownum 是在 查询集合产生的过程中产生的伪列,并且如果where条件中存在 rownum 条件的话,则:
  1: 假如 判定条件是常量,则:
  只能 rownum = 1, <= 大于1 的自然数, = 大于1 的数是没有结果的, 大于一个数也是没有结果的
  即 当出现一个 rownum 不满足条件的时候则 查询结束   this is stop key!
  2: 当判定值不是常量的时候
  若条件是 = var , 则只有当 var 为1 的时候才满足条件,这个时候不存在 stop key ,必须进行 full scan ,对每个满足其他where条件的数据进行判定
  选出一行后才能去选rownum=2的行……
--显示图片  READ_IMAGE_FILE
declare
  VHOME VARCHAR2(100);
begin
  vhome := :GLOBAL.VHOME;
  READ_IMAGE_FILE(vhome||'PIC\MENU_MOVE.BMP','BMP','BLK_USERMENU_EDIT.IMG_SHOW');
end;

原文转自:http://www.ltesting.net