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

--获取系统记录行数

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(sqlerrmsqlcode,: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;

--调用值列表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

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_userto_char(sysdate,'yy')to_char(sysdate,'mm')lpad(maxno,vlpadnum,'0');

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

v_sysdate:=to_char(sysdate,'YYMMDD');

--获取满足条件的记录数

select count(*)+1 into n_num

from bas_prtbasdef

where prtno like v_DbodNov_sysdate'%';

v_num := to_char(n_num);

--填充

v_count:=lpad(v_num,'5','0');

--合成为物料号

v_Svdictateno:=v_DbodNov_sysdatev_count;

--判定是否存在该号码

select count(*) into n_count from bas_prtbasdef

where prtno=v_Svdictateno;

--不存在

if n_count=0 then

--把新物料号赋给参数p_prtno

:parameter.p_prtno := v_Svdictateno;

--若存在

elsif n_count>0 then

--递增

n_count1:=v_count+1;

loop

v_count2:=lpad(n_count1,'5','0');

v_Svdictateno:=v_DbodNov_sysdatev_count2;

select count(*) into n_count2 from bas_prtbasdef

where prtno=v_Svdictateno;

if n_count2=0 then

:parameter.p_prtno := v_Svdictateno;

exit;--退出循环

end if;

n_count1:=n_count1+1;

end loop;

end if;

exception when others then

display_note(sqlerrmsqlcode,:global.logon_user);

END;

--当保存记录时控制：同一物料质量编号不能相同。

DECLARE

row_count NUMBER; --记录行数

row_num number; --所新建或修改记录行数

V_PRTLOTNO t_srmchkproject.prtlotno%type; --质量编号

BEGIN

--获取当前记录行数

row_num := :system.trigger_record;

first_record;

loop

row_count:=:system.trigger_record;

V_PRTLOTNO := :t_srmchkproject.prtlotno;

GO_RECORD(ROW_COUNT + 1);

loop

--若质量编号相同

if v_prtlotno = :t_srmchkproject.prtlotno then

display_note('同种物料的质量编号不能相同！请改正。',:global.logon_user);

go_item('t_srmchkproject.prtlotno');

return;--返回

--exit;

end if;

exit when :system.last_record = 'TRUE';

next_record;

end loop;

GO_RECORD(ROW_COUNT+1); --用以最后一行记录判定

exit when :system.last_record = 'TRUE';

--返回外层循环记录

GO_RECORD(ROW_COUNT);

next_record;

end loop;

commit_form;

--返回当前记录

go_record(row_num);

EXCEPTION WHEN OTHERS THEN NULL;

END;

--自动定位树结构的焦点

DECLARE

htree item;

find_node Ftree.NODE;

begin

htree:= Find_Item('T_BOM1.BOM');

find_node := FTREE.FIND_TREE_NODE(htree,:T_PRTBASDEF_CREATE.PRTNO,FTREE.FIND_NEXT,FTREE.NODE_VALUE,FTREE.ROOT_NODE,FTREE.ROOT_NODE);

IF NOT Ftree.ID_NULL(find_node) then --假如存在对应的节点

--pause;

FTREE.SET_TREE_SELECTION(htree, FIND_NODE, FTREE.SELECT_TOGGLE);

end if;

end;

--杀死死进程

select SID, serial#, USERNAME, SCHEMANAME, STATUS, OSUSER, OSUSER,TERMINAL, PROGRAM, LOGON_TIME from v$session;

ALTER SYSTEM KILL SESSION '';

select func.DECODEPASSWD(passWord) from t_user where username = '';

select func.ENCODEPASSWD(password) from t_user where username = '';

--用户权限

(:GLOBAL.LOGON_USER in (select object from t_roleuser where role=MODIFER and flag='0') or MODIFER=:GLOBAL.LOGON_USER)

--列表项

DECLARE

v_n Number;

BEGIN

v_n := populate_group('rec_ptype');

populate_list('t_freesb.prntno','rec_ptype');

END;

--提示对话框

set_alert_property('note_alert',alert_message_text,'口令更改成功！

');

return_value := show_alert('note_alert');

--改变按钮标签文本

DECLARE

v_Count NUMBER;

v_Label VARCHAR2(100);--记录“全选按钮”的标签文本

BEGIN

Go_Block('user_col_comments');

v_Label := Get_Item_Property('Blk_Control.BTn_Select_Col', Label);

First_Record;

IF v_Label = '+' THEN

LOOP

/*SELECT COUNT(*)

INTO v_Count

FROM t_Fieldrole

WHERE Table_Name = :User_Col_Comments.Table_Name

AND Column_Name = :User_Col_Comments.Column_Name

AND Role = :t_Fieldrole.Role;*/

--IF v_Count = 0 THEN

:User_Col_Comments.Chk_Select := 1;

--END IF;

EXIT WHEN :System.Last_Record = 'TRUE';

NEXT_RECORD;

END LOOP;

First_Record;

Set_Item_Property('Blk_Control.Btn_Select_Col', Label,'-');

Set_Item_Property('Blk_Control.Btn_Select_Col', TOOLTIP_TEXT,'取消选择');

ELSIF v_Label = '-' THEN

First_Record;

LOOP

:User_Col_Comments.Chk_Select := 0;

EXIT WHEN :System.Last_Record = 'TRUE';

NEXT_RECORD;

END LOOP;

First_Record;

Set_Item_Property('Blk_Control.Btn_Select_Col', Label,'+');

Set_Item_Property('Blk_Control.Btn_Select_Col', TOOLTIP_TEXT,'选取全部');

END IF;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;