将以下内容复制,另存为 f_split_sql.srf 然后导入pbl中
- $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
调用时,这样写:
- 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语句了