一个用参数作表名的问题 急……

[vampire1129]

我写了个存储过程，代码如下：
CREATE OR REPLACE PROCEDURE testpro
as
pk_value varchar2(32);--主键值
table_na varchar2(50);--表名
pk_name varchar2(50);--主键字段名
CURSOR cusr IS
select A.BUSINESS_ID,B.table_name,B.PKROW_NAME from history A,table_object B where
substr(A.OPERATION_NOTE,INSTR(A.OPERATION_NOTE,':',1,1)+1)='Delete' and
substr(A.OPERATION_NOTE,0,INSTR(A.OPERATION_NOTE,'@',1,1)-1)=B.object_name;
BEGIN
open cusr;
loop
fetch cusr into pk_value,table_na,pk_name;
exit when cusr%notFound;
exec ('delete from '||table_na||' where '||pk_name||'='||pk_value||';'); end loop;
close cusr;
---------- 异常处理 ----------
EXCEPTION
WHEN OTHERS THEN ROLLBACK;
END testpro;
/
编译时报错，错误存在于exec ('delete from '||table_na||' 我here '||pk_name||'='||pk_value||';');
因为当我把这句删了后编译通过，怀疑是这句中我用参数table_na传表名的缘故。
存储过程用到的表的建表语句如下：
CREATE TABLE HISTORY
(
HISTORY_ID VARCHAR2(32 BYTE) NOT NULL,
BUSINESS_ID VARCHAR2(32 BYTE),
USER_ID VARCHAR2(32 BYTE),
OPERATION_TIME DATE,
OPERATION_NOTE VARCHAR2(1000 BYTE),
USER_NAME VARCHAR2(50 BYTE),
SJTBBZ NUMBER,
IS_USED NUMBER(1)
)
CREATE TABLE TABLE_OBJECT
(
TABLE_NAME VARCHAR2(50 BYTE),
OBJECT_NAME VARCHAR2(200 BYTE),
PKROW_NAME VARCHAR2(50 BYTE)
)
表里的数据如下：
INSERT INTO HISTORY ( HISTORY_ID, BUSINESS_ID, USER_ID, OPERATION_TIME, OPERATION_NOTE, USER_NAME,
SJTBBZ, IS_USED ) VALUES (
'rydsOwfcsYWCKPnsSRjUWFebcafdbEum', '402885811637887a011637b5a58a001e', '4028858b15cf8c8e0115cfb505bb0003'
, TO_Date( '11/19/2007 02:39:39 下午', 'MM/DD/YYYY HH:MI:SS AM'), 'cn.com.intellitrans.zyhddb.system.CZzjghjqk@6943632f:Delete'
, 'user', NULL, NULL);
INSERT INTO HISTORY ( HISTORY_ID, BUSINESS_ID, USER_ID, OPERATION_TIME, OPERATION_NOTE, USER_NAME,
SJTBBZ, IS_USED ) VALUES (
'CVZixXPTuMYoOmzwTllGskvwzKBqMnxU', '402885811637887a011637b5a58a001e', '4028858b15cf8c8e0115cfb505bb0003'
, TO_Date( '11/19/2007 02:31:04 下午', 'MM/DD/YYYY HH:MI:SS AM'), 'cn.com.intellitrans.zyhddb.system.CZzjghjqk@6943632f:Delete'
, 'user', NULL, NULL);
commit;

INSERT INTO TABLE_OBJECT ( TABLE_NAME, OBJECT_NAME, PKROW_NAME ) VALUES (
'c_zzjghjqk', 'cn.com.intellitrans.zyhddb.system.CZzjghjqk', 'hjqkpk');
commit;

在线等，各位高手救命啊……

[bobfang]

exec ( 'delete from ' ¦ ¦table_na ¦ ¦ ' where ' ¦ ¦pk_name ¦ ¦ '= ' ¦ ¦pk_value ¦ ¦ '; ');
改为
execute immediate 'delete from '||table_na||' where '||PK_NAME||'='''||pk_value||'''';

[vampire1129]

好像还是有编译错误啊

[vampire1129]

编译通过了，多谢了，立马给分