--触发器失效
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
