2013-12-26
1. 用FORM_SUCCESS判断form有无错误
IF Form_Success THEN
Commit;
IF :System.Form_Status <> 'QUERY' THEN
Message('Error prevented Commit');
RAISE Form_Trigger_Failure;
END IF;
END IF;
有时候看情况是否要增加SQLCODE的判断
IF form_success or SQLCODE = 0 THEN
2. 异常
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, SQLCODE);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, SQLERRM);
END;
3. 动态LOV:
IF :mat_trx.trx_action_id = 1 THEN
set_lov_property('lot_number',group_name,'lot_issue');
ELSE
set_lov_property('lot_number',group_name,'lot_receipt');
END IF;
set_item_property('gme_batch.lot',lov_name,'lot_number');
4. 清除数据块
APP_FIND.CLEAR;
5. 设置回滚点与回滚:
issue_savepoint('gmetrxsave');
forms_ddl('rollback to gmetrx_save');
2013-12-27
6. bulk collect
通过bulk collect减少loop处理的开销,使用Bulk Collect提高Oracle查询效率,可以将查询结果一次性的加载到collection中,而不是通过cursor一条一条的处理。可以在select into,fetch into, returning into语句使用 bulk collect,注意所有的into变量都必须是collections。
--在select into语句中使用bulk collect
DECLARE
TYPE SalList IS TABLE OF emp.sal%TYPE;
sals SalList;
BEGIN
SELECT sal BULK COLLECT INTO sals FROM emp;
END;
/
--在fetch into中使用bulk collect
DECLARE
TYPE DeptRecTab IS TABLE OF dept%ROWTYPE;
dept_recs DeptRecTab;
CURSOR c1 IS
SELECT deptno, dname, loc FROM dept WHERE deptno > 10;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO dept_recs;
END;
/
--在returning into中使用bulk collect
CREATE TABLE emp2 AS SELECT * FROM employees;
DECLARE
TYPE NumList IS TABLE OF employees.employee_id%TYPE;
enums NumList;
TYPE NameList IS TABLE OF employees.last_name%TYPE;
names NameList;
BEGIN
DELETE FROM emp2 WHERE department_id = 30
RETURNING employee_id, last_name BULK COLLECT INTO enums, names;
dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN enums.FIRST .. enums.LAST
LOOP
dbms_output.put_line('Employee #' || enums(i) || ': ' || names(i));
END LOOP;
END;
/
DROP TABLE emp2;
7. 标记符号 《 》,常用来跳出循环
使用goto可以跳到标记<<outer>>的位置,举例如下:
for i in 1..100 loop
if i > 10 then
goto end_loop;
end if;
end loop;
<<outer>>
2013-12-30
8. dbms_sql.varchar2_table
定义: type Varchar2_Table is table of varchar2(2000) index by binary_integer;
l_rowid_tble dbms_sql.varchar2_table;
2014/01/10
会计期间表 : org_acct_periods