Oracle转义符号

[pengzhistar]

CREATE OR REPLACE PROCEDURE proc_ct_11(
quhao in varchar
)
IS
CURSOR ct_filter
IS
SELECT br_number, br_company
FROM ct_lsb
FOR UPDATE OF br_count;

v_br_number VARCHAR2 (128);
v_count PLS_INTEGER;
v_error VARCHAR2 (150);
v_br_company VARCHAR2 (150);
sub_br_number VARCHAR2 (128);
v_exit int;
BEGIN
OPEN ct_filter;
LOOP
FETCH ct_filter INTO v_br_number, v_br_company;
EXIT WHEN ct_filter%NOTFOUND;
IF instr(v_br_number,'/')=0
THEN
v_br_number:=SUBSTR (v_br_number, 6,7);
EXECUTE IMMEDIATE 'SELECT COUNT (*) INTO v_count FROM br_'||quhao ||' WHERE instr(br_number, '||v_br_number||')>0 OR br_company ='|| v_br_company;

……

最后的 EXECUTE IMMEDIATE 那个sql语句 由于v_br_number，v_br_company是varchar型 要执行的sql要加单引号 请问要怎么写才对？

[pengzhistar]

放到外面 每次只能运行到一半 然后就会报错

[alice鑫鑫]

那不是这个地方的错，应该放到外面，放在里面不对的，你把错误说一下~~~~

[pengzhistar]

ORA-06502: PL/SQL: 数字或值错误

[alice鑫鑫]

在oracle里面 v_count number;

[pengzhistar]

谢谢xinxin100198 找到问题所在了

[pengzhistar]

怎么没人啊

[alice鑫鑫]

EXECUTE IMMEDIATE 'SELECT COUNT (*) INTO v_count FROM br_'||quhao ||' WHERE instr(br_number, '||v_br_number||')>0 OR br_company ='|| v_br_company||';'

[小灰狼W]

EXECUTE IMMEDIATE 'SELECT COUNT (*) FROM br_'||quhao ||' WHERE instr(br_number, '''
||v_br_number||''')>0 OR br_company ='''|| v_br_company||''''
INTO v_count ;

[qin_phoenix]

chr(39)

[alice鑫鑫]

学习啦，呵呵，楼上的是对的~~~

[vinsonshen]

要执行的sql要加单引号 请问要怎么写才对？

-----------------
这个是动态语句里面的写法问题，用2个单引号来表示即可,如想查询值为 field1='aaa' -->field1=''aaa''

[pengzhistar]

EXECUTE IMMEDIATE 'SELECT COUNT (*) INTO v_count FROM br_'||quhao ||' WHERE instr(br_number, '||chr(39)||v_br_number||chr(39)||')>0 OR br_company ='||chr(39)|| v_br_company||chr(39);
执行报错！ORA-00905: 缺少关键譭

[alice鑫鑫]

晕，我说的是#3楼

[alice鑫鑫]

网速问题，我说的时候没看见已经有一个人了~~~~~~~

[pengzhistar]

3楼的 那样拼写的sql有问题啊
'SELECT COUNT (*) INTO v_count FROM br_'||quhao ||' WHERE instr(br_number, ''' ||v_br_number||''')>0 OR br_company ='''|| v_br_company||'''';

必须要把COUNT (*) 赋值给v_count 但是EXECUTE IMMEDIATE 不认识v_count 了？ 有什么方法解决吗

[小灰狼W]

看清楚..3楼不是你那么写的
into要放要外面去

[inthirties]

对

execute immediate sql into v_count;