Parts: | ||||
No. | Location | Name | Comon | Function |
A | Application Item | gx_01,gx_02,gx_03 | Common | 用来容纳SQL语句的变量。APEX自定义变量。 |
B | Application | UpdateTable | Common | 执行SQL语句的Process |
C | Application | JsonAarray | Common | 产生JSON数组的Process。根据Table名和App_ID以及PAGE_ID计算。 |
D | Page javascript | jsonArray() | Common | 调用C的Javascript |
E | Page javascript | updateTable() | Common | 调用D的Javascript |
F | Page javascript | makeSqlString() | Common | 生成SQL语句的Javascript。 |
G | Button javascript | - | Uniqe | 调用C和D。 |
FLOW:
按下页面上的按钮(比如Update)之后,
通过javascript呼出D和E。在此,
D可以生成所需的JSON格式的数组,此数组中容纳了生成SQL语句所需的Item及其属性;
E可以生成SQL语句。
代码:
A | gx_01,gx_02,gx_03 |
这些是系统自定义的Item。无需重新定义。
B | UpdateTable |
declare
sql_str varchar2(32767) := apex_application.g_x01;
fst_word varchar2(3200);
l_msg varchar2(100):= 'Error: Only INSERT, DELETE, UPDATE, SELECT are allowed.';
user_expt exception;
begin
--sql_str := UTL_URL.UNESCAPE(:SQL_STRING, 'UTF-8');
apex_application.g_x01 := NULL;
sql_str := UTL_URL.UNESCAPE(sql_str, 'UTF-8');
fst_word:= upper(regexp_substr(sql_str,'[^ ]+',1));
if fst_word in ('INSERT', 'UPDATE', 'DELETE', 'SELECT') then
execute immediate sql_str;
commit;
htp.p(fst_word||' Finished.');
else
RAISE user_expt;
end if;
/** URL for test
f?p=application_id:0:session:APPLICATION_PROCESS=process_name:::item-names:item-values
e.g.
f?p=104:0::APPLICATION_PROCESS=UpdateTable:::x01:11223344
**/
EXCEPTION
WHEN user_expt THEN
htp.p(l_msg||' '||sql_str);
WHEN OTHERS THEN
HTP.p('Error: '||SQLERRM);
end;
C | JsonAarray |
declare
-- create JSON array in javascript for AJAX updateTable!!!
P_TABLE_NAME varchar2(250) := apex_application.g_x01;
P_APP_ID number := apex_application.g_x02;
P_PAGE_NO number:= apex_application.g_x03;
cnt number := 0;
l_msg varchar2(100):= 'Error: no data found for item properties.';
user_expt exception;
BEGIN
apex_application.g_x01 := NULL;
apex_application.g_x02 := NULL;
apex_application.g_x03 := NULL;
htp.p('<!-- TableName: '||P_TABLE_NAME||'-->');
htp.p('<!-- Application ID: '||P_APP_ID||'-->');
htp.p('<!-- Page ID: '||P_PAGE_NO||'-->');
htp.p('<script>');
htp.p('var elm = [');
FOR b IN (
SELECT i.ITEM_NAME, i.ITEM_SOURCE, t.DATA_TYPE, i.FORMAT_MASK
FROM apex_application_page_items i, user_tab_columns t
WHERE i.application_id = P_APP_ID
and page_id = P_PAGE_NO
and ITEM_SOURCE_TYPE = 'Database Column'
and ITEM_SOURCE is not null
and t.table_name = P_TABLE_NAME
and i.ITEM_SOURCE = t.COLUMN_NAME
) LOOP
cnt := cnt +1;
htp.p('{ "Item" : "'||b.ITEM_NAME||'" , "Source" : "'||b.ITEM_SOURCE
||'" , "Type" : "'||b.DATA_TYPE||'" , "Mask" : "'||b.FORMAT_MASK||'" },');
END LOOP;
htp.p('];');
htp.p('</script>');
if cnt=0 then
RAISE user_expt;
end if;
EXCEPTION
WHEN user_expt THEN
htp.p(l_msg);
WHEN OTHERS THEN
HTP.p('Error: '||SQLERRM);
end;
D | jsonArray() |
function jsonArray(tableName){
if (typeof jsonarrayofitems != "undefined") $("#jsonarrayofitems").remove();
//if (typeof jsonarrayofitems != "undefined") alert($("#jsonarrayofitems").html());
var ajaxReq = new htmldb_Get(null, $v('pFlowId'), 'APPLICATION_PROCESS=JsonArray', $v('pFlowStepId'));
//var ajaxReq = new htmldb_Get(null, null, 'APPLICATION_PROCESS=JsonArray', null); //<- Works OK.
ajaxReq.addParam('x01',tableName);
ajaxReq.addParam('x02',$v('pFlowId'));
ajaxReq.addParam('x03',$v('pFlowStepId'));
var ajaxRtn = ajaxReq.get();
if (ajaxRtn.indexOf("Error:")!== -1){
alert("Jason array for item properties was not created.");
return false;}
// No error, add to body
newdiv = $('<div id="jsonarrayofitems"/>');
$('body').append(newdiv);
$('#jsonarrayofitems').html(ajaxRtn);
}
E | updateTable() |
function updateTable(sqlStr,msg){
//msg, true/false. Show success message or not.
sqlStr = encodeURI(sqlStr);
var ajaxReq = new htmldb_Get(null, $v('pFlowId'), 'APPLICATION_PROCESS=UpdateTable', $v('pFlowStepId'));
ajaxReq.addParam('x01',sqlStr);
var ajaxRtn = ajaxReq.get();
if (ajaxRtn.indexOf("Error:")== -1){
if (msg) alert(ajaxRtn);
return true;
}else{
alert(ajaxRtn);
return false;
}
}
F | makeSqlString() |
function makeSqlString(){
var sqlStr = new Array;
var itemVal = "";
// remove undefined element of the array.
elm = $.grep(elm, function(v,i){ return typeof v != "undefined"});
$.each(elm,function(){
itemVal = $v(this.Item);
switch (this.Type){
case "NUMBER":
itemVal = ((itemVal=="") ? "NULL" : itemVal);
itemVal = itemVal.replace(/,/g,"");
break;
case "DATE":
//itemVal = itemVal.replace(/,/g,"");
if (this.Mask=="") {this.Mask = 'yyyy-mm-dd hh24:mi:ss';}
itemVal = "to_date('" + itemVal + "', '" + this.Mask + "')"
break;
default:
itemVal = "'" + itemVal.replace(/'/g,"''") + "'";
//itemVal = itemVal.replace(/'/g,"''");
break;
}
sqlStr.push(this.Source + "=" + itemVal);
//alert(this.Source + "=" + itemVal );
itemVal = "";
})
var sqlString = sqlStr.join(",\n");
return sqlString;
} // end of function makeSqlStr
G1 | Javascript for SUBMIT (Update) button |
javascript:{
var tableName = "APEX_ITEMS";
var whereClause = "NO = " + $v("P7_NO");
jsonArray(tableName);
var sqlStr = "Update "+tableName+"\nSET " + makeSqlString() + "\nWhere " + whereClause;
if (updateTable(sqlStr,true)) document.location.reload(true);
}
G2 | Javascript for SUBMIT (Insert) button |
javascript:{
var sqlStr = new String;
var tableName = "APEX_ITEMS";
var whereClause = "NO = " + $v("P7_NO");
//1. insert, with required colums only.
sqlStr = "Insert into " + tableName + "(NO) values("+ $v("P7_NO") +")";
if (updateTable(sqlStr,false)) { // no error...
jsonArray(tableName);
// 2. then, update again.
sqlStr = "Update "+tableName+"\nSET " + makeSqlString() + "\nWhere " + whereClause;
if (updateTable(sqlStr,true)) document.location.reload(true);
}
}
G3 | Javascript for SUBMIT (Delete) button |
javascript:{
var sqlStr = new String;
var tableName = "APEX_ITEMS";
var whereClause = "NO = " + $v("P7_NO");
//Delete, with required colums only.
sqlStr = "Delete " + tableName + " where " + whereClause;
if (updateTable(sqlStr,true)) { // no error...
history.go(-1);
}
}
例 | Javascript to update 2 tables |
function btnUpdate2Tables(){
var tableName = new String;
var whereClause = new String;
//1st table
tableName = "SUM_BCASE_INIT";
whereClause = "BCASE_UKETSUKE_NO = " + $v("P91_BCASE_UKETSUKE_NO");
jsonArray(tableName);
var sqlstr = makeSqlString() ;
sqlStr = "Update "+tableName+"\nSET " + sqlstr + "\nWhere " + whereClause;
var rtn1 = updateTable(sqlStr,false);
// 2nd table
tableName = "SUM_BCASE_MONTH";
whereClause = "BCASE_UKETSUKE_NO = " + $v("P91_BCASE_UKETSUKE_NO");
whereClause = whereClause + " AND NENDO = " + $v("P91_NENDO");
jsonArray(tableName);
var sqlstr = makeSqlString() ;
sqlStr = "Update "+tableName+"\nSET " + sqlstr + "\nWhere " + whereClause;
var rtn2 = updateTable(sqlStr,true);
if (rtn1 && rtn2) document.location.reload(true);
}