--觸發(fā)器失效
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;
--拋出錯(cuò)誤
Raise_application_Error(-20100,
:New.Prtno
'已經(jīng)登帳的庫(kù)存事務(wù)不能刪除，必須進(jìn)行調(diào)錯(cuò)處理!');
--不同用戶之間共享數(shù)據(jù)
--在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中創(chuàng)建后臺(tái)job
DECLARE
Alert_Button NUMBER;
v_Job NUMBER;
BEGIN
Alert_Button := Display_Stop('是否確認(rèn)結(jié)轉(zhuǎn)全部庫(kù)房？',
:Global.Logon_User);
--是否確認(rèn)對(duì)截至日期內(nèi)所選定庫(kù)房的流水進(jìn)行轉(zhuǎn)移？
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('有限能力計(jì)劃將在一分鐘后開始生成，請(qǐng)耐心等待……');
EXCEPTION
WHEN OTHERS THEN
Display_Note(SQLCODE SQLERRM, :Global.Logon_User);
END;
--獲取查詢條件
Tmp_Query01 := Get_Block_Property('T_PK', Last_Query);
--查找某庫(kù)房、物料最后一條流水
--
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
--查找流水錯(cuò)誤（當(dāng)前庫(kù)存數(shù)量與臺(tái)帳不一致）
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語句實(shí)現(xiàn)查找一列中第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；
--更新配套單狀態(tài)
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;
--游標(biāo)變量
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;
--轉(zhuǎn)移數(shù)據(jù)
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,
'確認(rèn)把監(jiān)控記錄導(dǎo)入到歷史數(shù)據(jù)庫(kù)？導(dǎo)出后，當(dāng)前監(jiān)控記錄將被清空！');
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('正在導(dǎo)出到歷史記錄，請(qǐng)稍等...',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;
--更新部門不一致的數(shù)據(jù)
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,'&'));
--塊中項(xiàng)目的控制.
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
--月終統(tǒng)計(jì)
--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;
--
--獲取復(fù)選框的值
IF Checkbox_Checked(:SYSTEM.CURRENT_ITEM) THEN
null;
ELSE
NULL;
END IF;
--庫(kù)存差錯(cuò)
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);
--查詢子項(xiàng)皆為完成而主單仍為正常的申請(qǐng)單
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'
--動(dòng)態(tài)構(gòu)造值列表
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(sqlcodesqlerrm, :global.logon_user);
END;
--調(diào)用的FORM僅答應(yīng)查詢。
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') --找出當(dāng)前月份的周五的日期
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，即為查找當(dāng)前月份的前三個(gè)月中的每周五的日期。
--rownum的用法:
只能用以上符號(hào)(<、<=、!=)。
select * from tablename where rownum != 10;返回的是前９條記錄。
不能用：>,>=,=,Between...and。由于rownum是一個(gè)總是從1開始的偽列，Oracle 認(rèn)為這種條件 不成立，查不到記錄.
另外，這個(gè)方法更快：
select * from (
select rownum r,a from yourtable
where rownum <= 20
order by name )
where r > 10
這樣取出第11-20條記錄!(先選再排序再選)
要先排序再選則須用select嵌套：內(nèi)層排序外層選。
rownum是隨著結(jié)果集生成的，一旦生成，就不會(huì)變化了；同時(shí),生成的結(jié)果是依次遞加的，沒有1就永遠(yuǎn)不會(huì)有2!
rownum 是在 查詢集合產(chǎn)生的過程中產(chǎn)生的偽列，并且假如where條件中存在 rownum 條件的話，則:
1： 假如 判定條件是常量，則：
只能 rownum = 1, <= 大于1 的自然數(shù)， = 大于1 的數(shù)是沒有結(jié)果的， 大于一個(gè)數(shù)也是沒有結(jié)果的
即 當(dāng)出現(xiàn)一個(gè) rownum 不滿足條件的時(shí)候則 查詢結(jié)束 this is stop key!
2: 當(dāng)判定值不是常量的時(shí)候
若條件是 = var , 則只有當(dāng) var 為1 的時(shí)候才滿足條件，這個(gè)時(shí)候不存在 stop key ,必須進(jìn)行 full scan ,對(duì)每個(gè)滿足其他where條件的數(shù)據(jù)進(jìn)行判定
選出一行后才能去選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;
