PL/SQL比較有用小結整理

PL/SQL整理

--获取系统记录行数

 go_block('T_WARITEMPRT');

 last_record;

 row_count:=:system.trigger_record; --system.cursor_record

--execute_trigger('POST-CHANGE');

--设置鼠标状态

set_application_property(cursor_style,'BUSY');

--raise form_trigger_failure;

--显示错误信息

 exception when others then

  display_note(sqlerrm||sqlcode,:global.logon_user);

--decode

decode(a.freightmode,'0','企业自付','1','供应商付费','2','企业代付','3','供应商代付',null)

decode(qtyonhand - qtyinit,invtranqty,invtranqty,null)

--导出路径

select * from t_parameter where f_name='LeanERPTempDir';

select f_c_value from t_parameter where f_name ='WebServer1';

--合同附件

select f_c_value from t_parameter where f_name='purorderaccesspath';

select f_c_value from t_parameter where f_name='purorderaccessurl';

--设置值列表    

SET_ITEM_PROPERTY('top.ORIGINALNO',LOV_NAME,'LOV_MSO');

LIST_VALUES;

--下拉列表

 n:=populate_group('rec_lb');

 populate_list('BLK_BUT.APPTYPE','rec_lb');

 :bLK_BUT.APPTYPE:='0';

--调用另一模块

DECLARE

  pl_id  ParamList;

  v_string varchar2(1000);

  v_itemname varchar2(1000);

BEGIN

 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_itemname:=:system.current_block||'.'||:system.current_item;

 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);

 copy(:global.str,v_itemname); Erase(:global.str);

END;

--批量处理

 set_application_property(cursor_style,'BUSY');

 SYNCHRONIZE;

 first_record;

 loop

   null;

 exit when :system.last_record = 'TRUE';

 next_record;

 end loop;

 set_application_property(cursor_style,'DEFAULT');

 bell;

 display_note('SRMP035',:global.logon_user);

 -- 采购计划关闭完毕!

--对话框

IF display_stop('INV081', :GLOBAL.logon_user) <> alert_button1 THEN

 RETURN;

END IF;

--取得货币名称

begin

 select currencydesc

 into v_currencydesc

 from t_currency

 where currencycode = c1.splymoneyflag;

 exception when others then v_currencydesc := '人民币（元）';

end;

--询问对话框-1

declare

 return_value number;

begin

 set_alert_property('stop_alert',alert_message_text,'确认送审该合同吗?');

 return_value := show_alert('stop_alert');

 if return_value != alert_button1 then

    return;

 end if;

end;

--询问对话框-2

DECLARE

 return_value number;

BEGIN

 return_value := display_stop('mdm011',:global.logon_user);

 IF return_value = ALERT_BUTTON1 THEN

    COMMIT_FORM;

 END IF;

END; 

--取下周一

 select trunc(sysdate,'d')+8 from dual;

--message_level

oldmsg := :System.Message_Level;

 IF reldef = 'FALSE' THEN

   Go_Block(detail);

   Check_Package_Failure;

   :System.Message_Level := '10';

   Execute_Query;

   :System.Message_Level := oldmsg;

 ELSE

--取当前年度、期段号

begin

 select year,period

 into v_year,v_period

 from t_accperiod

 where sysdate >= startdate

 and to_char(sysdate,'yyyy/mm/dd')<=to_char(enddate,'yyyy/mm/dd');

exception when others then null;

end;

--设置画布属性

vCan2:=FIND_VIEW('CAN_PRTNO2');

SET_VIEW_PROPERTY(vCan2,VISIBLE,property_true);

vCan1:=FIND_VIEW('CAN_PRTNO1');

SET_VIEW_PROPERTY(vCan1,VISIBLE,property_false);

--设备提示信息

elsif :top.opsrc = 'X' or :top.opsrc='T' then

  set_item_property('top.tranobj',prompt_text,'客户代码');

else

  set_item_property('top.tranobj',prompt_text,'部门代码');

end if;

--设置值列表KEY-LISTVAL

ELSIF :top.OPSRC='I' THEN --零星出库

 SET_ITEM_PROPERTY('top.ORIGINALNO',LOV_NAME,'LOV_MSI');

LIST_VALUES;

 IF :top.ORIGINALNO IS NOT NULL THEN

    PRTNO_MSI;

 END IF;

END IF;

--调用值列表show_lov

DECLARE

 a_value_chosen BOOLEAN;

BEGIN

 a_value_chosen := Show_Lov('lov_name');

 IF a_value_chosen THEN

   set_block_property('t_efficiency',default_where,'username = :control.display_name');

   go_block('t_efficiency');

   execute_query;

   set_block_property('t_efficiency',default_where,'username in (select username from t_hrmuser where department = :global.user_dept)');

 END IF;

END;

--生成序号

declare

 vlpadnum number;

 maxno varchar2(6);

 cursor cerpcode is select sequence,lpadnum from t_erpcode

 where erpcode='PURAPPNO' and prifix=to_char(sysdate,'yy')||to_char(sysdate,'mm') for update nowait;

begin

 if :t_purapp.appno is null then

   if :SYSTEM.MODE = 'NORMAL' THEN

    begin

     begin

    for c1 in cerpcode loop    

       update t_erpcode set sequence=sequence+1 where erpcode='PURAPPNO' and prifix=to_char(sysdate,'yy')||to_char(sysdate,'mm');

    end loop;

   exception

    when others then

    display_note('SYS071',:global.logon_user);

    raise form_trigger_failure;

   end;

     select sequence,lpadnum into maxno,vlpadnum from t_erpcode where erpcode='PURAPPNO' and prifix=to_char(sysdate,'yy')||to_char(sysdate,'mm');

    exception

     when NO_DATA_FOUND then

          select count(*)+1 into maxno from t_purapp where appno like '%'||to_char(sysdate,'yy')||to_char(sysdate,'mm')||'%';

          --select max()

     vlpadnum:=4;

     insert into t_erpcode(erpcode,prifix,sequence,lpadnum)

     values ('PURAPPNO',to_char(sysdate,'yy')||to_char(sysdate,'mm'),maxno,vlpadnum);

    end;

     :t_purapp.appno := 'Q'||:global.logon_user||to_char(sysdate,'yy')||to_char(sysdate,'mm')||lpad(maxno,vlpadnum,'0');

   end if;

  end if;

end;

--遍历树

 select t_prtstrudef.prtno,

       t_prtbasdef.prtdesc,

       t_prtbasdef.mtltmrk,

       t_prtstrudef.prtpqty,

       t_prtbasdef.prtum,

       t_prtstrudef.tranprtno

  from t_prtstrudef,t_prtbasdef

 where t_prtstrudef.prtno = t_prtbasdef.prtno

connect by prior t_prtstrudef.prtno=t_prtstrudef.prntno

 start with t_prtstrudef.prntno=:BLOCK_BUTTON.PRTNO;

 --

/*

** Built-in: FORMS_DDL

** Example:  The expression can be a string literal.

*/

BEGIN

 Forms_DDL('create table temp(n NUMBER)');

 IF NOT Form_Success THEN

   Message ('Table Creation Failed');

 ELSE

   Message ('Table Created');

 END IF;

END;

--物料所属大类判断

and (exists (select ''a'' from t_prtinclass c where c.classid='''||:blk_query.classid||''' and c.prtno=v_totqry.prtno ) or :blk_query.classid is null)

--给值列表赋初值

declare

 cursor v_cur2 is select machtype A ,machtype B from t_gkdept order by machtype;

 N_INDEX NUMBER :=1;

begin

 DELETE_LIST_ELEMENT('T_TPMSCKMTN.MACHTYPE',1);

 for c2 in v_cur2 loop

   Add_List_Element('T_TPMSCKMTN.MACHTYPE',N_INDEX,C2.A,C2.B);

   N_INDEX := N_INDEX + 1;

 end loop;

end;

--

--查询重复数据

select rpno from t_purtrace group by rpno having count(rpno) >18;

--

--生成单据号

PROCEDURE GET_PRTNO is

  v_sysdate     varchar2(12);

  v_Svdictateno varchar2(24);

  v_DbodNo      varchar2(12);    

  n_count       integer;

  v_count       varchar2(10);

  n_count1      integer;

  n_count2      integer;

  v_count2      varchar2(10);

  n_num         number;

  v_num         varchar2(8);

BEGIN

 v_DbodNo:= 'ZS';