PB9写的一个拆解SQL语句的通用函数

最新推荐文章于 2022-07-06 12:46:10 发布

秋高工作室

最新推荐文章于 2022-07-06 12:46:10 发布

阅读量408

点赞数

文章标签： powerbuilder function select

将以下内容复制，另存为 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)