將以下內容復制，另存為f_split_sql.srf 然後導入pbl中
[cpp] view plaincopy
$PBExportHeader$f_split_sql.srf
$PBExportComments$SQL操作：分割SQL語句為select、from、where、group by、order by 5個子句
global type f_split_sql from function_object
end type
forward prototypes
global subroutine replace_n (ref string input_str,readonly string replaced_str,readonly string replace_str)
global subroutine f_split_sql (string as_sql, ref string as_select, ref string as_from, ref string as_where, ref string as_group, ref string as_order)
end prototypes
global subroutine replace_n (ref string input_str,readonly string replaced_str,readonly string replace_str);string s1
string s2
long p
long n
p = 1
do while 1 = 1
p = pos(input_str,replaced_str,p)
if p = 0 then
exit
end if
s1 = mid(input_str,1,p - 1)
s2 = mid(input_str,p + len(replaced_str))
input_str = s1 + replace_str + s2
p = p + len(replace_str)
loop
end subroutine
global subroutine f_split_sql (string as_sql, ref string as_select, ref string as_from, ref string as_where, ref string as_group, ref string as_order);//====================================================================
// 過程(函數|Function): f_split_sql()
//--------------------------------------------------------------------
// 描述(Description): 拆解SQL語句
//--------------------------------------------------------------------
// 變量(Arguments):
// string as_sql 傳入的SQL語句（如：select a from t where b = 1 group by c order by c）
// ref string as_select 拆解後的select語句（如：select a）
// ref string as_from 拆解後的from 語句（如：from t）
// ref string as_where 拆解後的where 語句（如：where b = 1）
// ref string as_group 拆解後的group 語句（如：group by c）
// ref string as_order 拆解後的order 語句（如：order by c）
//--------------------------------------------------------------------
// 返回(Returns): (none)
//--------------------------------------------------------------------
// 作者(Author): yyoinge Date: 2012-02-17 13:26
//--------------------------------------------------------------------
// 修改歷史(Modify History):
//
//--------------------------------------------------------------------
as_sql = lower(trim(as_sql))
if as_sql = '' then return
as_select = ''; as_from = ''; as_group = ''; as_order = ''
replace_n(as_sql,'~r',' ')
replace_n(as_sql,'~n',' ')
replace_n(as_sql,'~r~n',' ')
replace_n(as_sql,'~t',' ')
replace_n(as_sql, '(select ',' ( select ')
replace_n(as_sql, ',select ',' , select ')
replace_n(as_sql,';',' ')
if leftw(as_sql, 7) <> 'select ' then return
long ll_gb_p, ll_ob_p, ll_fr_p, ll_se_p, ll_wh_p, ll_tmp, ll_yh_p
int li_leap = 0
//先剔除''內的內容,如：'abc'替換為@@@@@
string ls_sql
ls_sql = as_sql
do while true
ll_yh_p = posw(ls_sql, "'")
if ll_yh_p <= 0 then exit
ll_tmp = posw(ls_sql, "'", ll_yh_p + 1)
if ll_tmp <= 0 then return
ls_sql = leftw(ls_sql, ll_yh_p - 1) + fill('@', ll_tmp - ll_yh_p + 1) + midw(ls_sql, ll_tmp + 1)
loop
//******************************** select
ll_tmp = 8
li_leap = -1
do while true
ll_fr_p = posw(ls_sql, " from ", ll_tmp)
ll_se_p = posw(ls_sql, " select ", ll_tmp)
if ll_fr_p <= 0 then return
if ll_fr_p < ll_se_p or ll_se_p = 0 then
li_leap ++
else
li_leap --
end if
if li_leap = 0 then
as_select = leftw(as_sql, ll_fr_p)
exit
end if
if ll_fr_p <= 0 then ll_fr_p = 999999
if ll_se_p <= 0 then ll_se_p = 999999
ll_tmp = min(ll_fr_p, ll_se_p) + 5
loop
if as_select = '' then return
//******************************** group by
ll_gb_p = lastpos(ls_sql, " group by ")
ll_ob_p = lastpos(ls_sql, " order by ")
if ll_gb_p > 0 then
if posw(ls_sql, " from ", ll_gb_p + 1) > 0 or posw(ls_sql, " where ", ll_gb_p + 1) > 0 then
as_group = ''
ll_gb_p = 0
else
if ll_ob_p > ll_gb_p then
as_group = midw(as_sql, ll_gb_p, ll_ob_p - ll_gb_p)
else
as_group = midw(as_sql, ll_gb_p)
end if
end if
else
as_group = ''
end if
//******************************** order by
if ll_ob_p > 0 then
if posw(ls_sql, " from ", ll_ob_p + 1) > 0 or posw(ls_sql, " where ", ll_ob_p + 1) > 0 then
as_order = ''
ll_ob_p = 0
else
as_order = midw(as_sql, ll_ob_p)
end if
else
as_order = ''
end if
//取得select from where 語句的最後一個字符的位置+1
if ll_gb_p <= 0 then ll_gb_p = ll_ob_p
if ll_gb_p <= 0 then ll_gb_p = lenw(as_sql) + 1
//******************************** where
as_sql = midw(leftw(as_sql,ll_gb_p - 1), ll_fr_p)
ls_sql = midw(leftw(ls_sql,ll_gb_p - 1), ll_fr_p)
ll_wh_p = lastpos(ls_sql, " where ")
if ll_wh_p <= 0 then
as_where = ''
else
ll_tmp = 5
li_leap = 0
do while true
ll_se_p = posw(ls_sql, "(", ll_tmp)
ll_fr_p = posw(ls_sql, ")", ll_tmp)
if ll_se_p > ll_wh_p then ll_se_p = 0
if ll_fr_p > ll_wh_p then ll_fr_p = 0
if (ll_se_p = 0 and ll_fr_p = 0) then exit
if ll_fr_p < ll_se_p or ll_se_p = 0 then
li_leap ++
else
li_leap --
end if
if ll_fr_p <= 0 then ll_fr_p = 999999
if ll_se_p <= 0 then ll_se_p = 999999
ll_tmp = min(ll_fr_p, ll_se_p) + 1
loop
if li_leap <> 0 then
as_where = ''
else
as_where = midw(as_sql, ll_wh_p)
as_sql = leftw(as_sql, ll_wh_p)
end if
end if
//******************************** from
as_from = as_sql
end subroutine
調用時，這樣寫：
[cpp] view plaincopy
string ls_sql, ls_select, ls_from, ls_where, ls_group, ls_order
ls_sql = dw_1.getsqlselect()
f_split_sql(ls_sql, ls_select, ls_from, ls_where, ls_group, ls_order)
即可將SQL語句ls_sql ，拆解為5個部分，之後就可以對各個部分進行添加、修改，然後重新組成新的SQL語句了
作者 yyoinge的專欄
