postgresql存储过程中quote_literal函数的使用

由于业务需要,在写一个存储过程的时候,碰到一个问题折腾了比较长的时间才解决,希望大家在碰到类似的问题后不再浪费时间。【场景回顾】
大家看下面这个存储过程,编译是能通过的,但是调用它执行的时候返回false,这说明该过程抛异常了。

CREATE OR REPLACE FUNCTION fun_appid_insert_test(_start integer, _end integer)
RETURNS boolean AS
$BODY$
DECLARE

v_app_name character varying; -- 应用名称
v_id numeric; -- 主键ID
v_index numeric; -- 序号
v_sql character varying; -- 需要执行的sql
v_num numeric; -- 记录条数

BEGIN

v_index = 0;
for _start in _start.._end
loop

-- 查询app_id是否分配过
select count(1) into v_num from appid_test where app_id = _start;

-- 若app_id没有分配,执行插入语句,注意,这里使用等号
if (v_num = 0) then
-- 获取序列的值作为主键ID
select nextval('seq_appid_test') into v_id;

-- app_name的值都是pic+数字
v_app_name = 'pic' || v_index;

-- 拼接需要执行的插入语句
v_sql = 'insert into appid_test(id, app_id, app_name) values(' || v_id || ',' || _start || ',' || v_app_name || ')';

Execute v_sql;
end if;

-- 序号自增1,app_name名称需要
v_index = v_index + 1;

end loop;
RETURN true;
EXCEPTION
when others then
return false ;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;


【问题分析】
经过排除法进行定位,问题可以锁定到这一行:v_sql = 'insert into xwq_appid_test(id, app_id, app_name) values(' || v_id || ',' || _start || ',' || v_app_name || ')';

将 v_id ,_start都换成常量后还是抛异常,那么问题应该出现在v_app_name这里,果然将v_app_name 换成常量数字字符串后,该过程能正常执行,注意是:常量数字字符串,

若换成中文汉字或者其它非数字类型字符串,过程还是不能执行,那么这里是不是需要转换一下呢?顺着这个思路,在网上查了一些资料,找到一个函数 quote_literal(),资料蛮少,

找到一博主对它的解释是:在字符串的前后加单引号,于是就拿来试试吧,于是将v_sql 的值改为: 'insert into xwq_appid_test(id, app_id, app_name) values(' || v_id || ',' || _start || ',' ||quote_literal(v_app_name) || ')';

然后编译并调用且执行该过程,返回值为true,成功了!

【存储过程最终版本】
CREATE OR REPLACE FUNCTION fun_appid_insert(_start integer, _end integer)
RETURNS boolean AS
$BODY$
DECLARE

v_app_name character varying; -- 应用名称
v_id numeric; -- 主键ID
v_index numeric; -- 序号
v_sql character varying; -- 需要执行的sql
v_num numeric; -- 记录条数

BEGIN

v_index = 0;
for _start in _start.._end
loop

-- 查询app_id是否分配过
select count(1) into v_num from apply_appid_mng where app_id = _start;

-- 若app_id没有分配,执行插入语句,注意,这里使用等号
if (v_num = 0) then
-- 获取序列的值作为主键ID
select nextval('seq_apply_appid_mng') into v_id;

-- app_name的值都是pic+数字
v_app_name = 'pic' || v_index;

-- 拼接需要执行的插入语句
v_sql = 'insert into apply_appid_mng(id, app_id,app_name,status,approve_status,is_free,dept_name,first_level_type, app_type, run_type,
is_balance, online_type,app_desc, modify_time) values('|| v_id || ',' || _start || ',' || quote_literal(v_app_name) || ',3 ,1' || ',' || quote_literal('是')
|| ',' || quote_literal('多媒体事业部') || ',' || quote_literal('MVPN') || ',' || quote_literal('图片') || ',' || quote_literal('WAP') || ','
|| quote_literal('是') || ',' || quote_literal('非联网') || ',' || quote_literal('多媒体事业部图片资源') || ',' || quote_literal('2012-6-1 12:00:00.000') ||')';

Execute v_sql;
end if;

-- 若app_id已经分配,将状态置为删除
Execute 'update appid_mng set status = 1 where app_id = ' || _start;
-- 序号自增1,app_name名称需要
v_index = v_index + 1;

end loop;
RETURN true;
EXCEPTION
when others then
return false ;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;


【思考】
如果quote_literal函数真如那位博所述,仅仅是在字符串前后加单引号的话,那么我换成非数字类型的字符串为什么就不行了呢?

【补充】
postgresql官方说明文档对quote_literal的说明:返回给出字串的一个适用于在sql语句字串里当做文本使用的形式。嵌入的引号和反斜杠被恰当地写了双份。如quote_literal('0\'Reilly'),结果是'0''Reilly'。quote_literal一般动态sql中用的比较多,在拼接sql的时候,需要对sql语句字串内部的引号进行转义。
之所以数字类型的字符串不需要转义,我想是因为数据库中有一个自动类型转换的问题。
这样的话,所有的疑问都解释得通了,希望对大家有帮助。
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值