车轮再造（2）： 所需部件及源代码的全部

Parts:
No.	Location	Name	Comon or Unique	Function
A	Application Item	gx_01,gx_02,gx_03	Common	用来容纳SQL语句的变量。APEX自定义变量。
B	Application on-demand process	UpdateTable	Common	执行SQL语句的Process
C	Application on-demand process	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);
}