报错误:ORA-00933: SQL command not properly
ORA-06512: at \"YX_USER.DELETE_LAST_AGENT\", line 4\nORA-06512: at line create or replace procedure delete_last_agent(agent_id IN varchar2,conn_id IN varchar2)IS
sql_del varchar2(100);
begin
sql_del:='delete from yx_last_agent y where t.agent_id' ||agent_id|| 'and t.conn_id' | |conn_id;
execute immedite sql_del;
end;
ORA-06512: at \"YX_USER.DELETE_LAST_AGENT\", line 4\nORA-06512: at line create or replace procedure delete_last_agent(agent_id IN varchar2,conn_id IN varchar2)IS
sql_del varchar2(100);
begin
sql_del:='delete from yx_last_agent y where t.agent_id' ||agent_id|| 'and t.conn_id' | |conn_id;
execute immedite sql_del;
end;
改成
'delete from yx_last_agent y where t.agent_id=' ||agent_id|| 'and t.conn_id=' | |conn_id; 试试另外为啥不用using呢
这个错误代表sql命令没有正确结束,
一般是因为你自己拼接的执行sql有语法错误,你现在的sql就存在着严重的语法错误。create or replace procedure delete_last_agent(agent_id IN varchar2,conn_id IN varchar2)
IS
sql_del varchar2(100);
begin
delete from yx_last_agent
where agent_id =agent_id
and conn_id=conn_id;
end;
你这个sql有两个地方明显错了。
1、你的别名是y,后面你用的是t。
2、你拼接的是字符串,但是你的判断符号(=)跑到哪里去了?
INFO VARCHAR2(1000) := 'PFZHANG3';
TABLENAME VARCHAR2(100) := 'ACCOUNT';
BEGIN
/* INSERT INTO TABLEINFO
SELECT A.TABLE_NAME, A.COLUMN_NAME, A.DATA_TYPE, B.COMMENTS
FROM USER_TAB_COLUMNS A, USER_COL_COMMENTS B
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
AND A.TABLE_NAME = TABLENAME
ORDER BY A.COLUMN_ID;*/
INFO := 'INSERT INTO TABLEINFO --拼接字符串的方法,通过execute immediate进行执行
SELECT A.TABLE_NAME, A.COLUMN_NAME, A.DATA_TYPE, B.COMMENTS
FROM USER_TAB_COLUMNS A, USER_COL_COMMENTS B
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
AND A.TABLE_NAME =''' || TABLENAME ||
''' ORDER BY A.COLUMN_ID';
DBMS_OUTPUT.PUT_LINE(INFO);
EXECUTE IMMEDIATE INFO;
COMMIT;
END;
---sql有问题,我给的案例是正确的,你参考一下