我在下面的存储过程中得到了一个错误,因为缺少关键字。我试图通过将它们放入变量来运行sql语句,因为我在sql查询中动态使用了数据库链接。在合并查询中使用case statement
时出现错误。当我使用dbms输出行打印合并查询时,它会打印完整的case语句,而不是只打印ID。
PROCEDURE "EXT_SOAP_MONITORING"(IN_DB_LINK IN varchar2) AS
LAST_SM_ID Number := 0;
LAST_CAPT_DATE DATE;
LAST_SM_ID_MB Number := 0;
LAST_CAPT_DATE_MB DATE;
l_sql VARCHAR2(5000);
l_sql1 VARCHAR2(5000);
DB_CONNECTION_NAME VARCHAR2(100);
BEGIN
Select DB_LINK INTO DB_CONNECTION_NAME FROM RATOR_MONITORING_CONFIGURATION.DB_CONNECTION WHERE DB_LINK = IN_DB_LINK;
--DELETE FROM TEMP_SOAP_MONITORING table before inserting new data into this table
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_SOAP_MONITORING';
-- first retrieve the last id (of the newest record) which has been imported at last extraction
--FONIC
SELECT LAST_TASK_ID INTO LAST_SM_ID FROM CAPTURING where DB_TABLE='TEMP_SOAP_MONITORING' and DB ='FONIC_RETAIL';
SELECT CAPTURING_DATE INTO LAST_CAPT_DATE from CAPTURING WHERE DB_TABLE='TEMP_SOAP_MONITORING' and DB ='FONIC_RETAIL';
--MB
SELECT LAST_TASK_ID INTO LAST_SM_ID_MB FROM CAPTURING where DB_TABLE='TEMP_SOAP_MONITORING' and DB ='MB_RETAIL';
SELECT CAPTURING_DATE INTO LAST_CAPT_DATE_MB from CAPTURING WHERE DB_TABLE='TEMP_SOAP_MONITORING' and DB ='MB_RETAIL';
l_sql:=
'merge into TEMP_SOAP_MONITORING TSM
using (
select * from
(select ID,REQUEST_XML,RESPONSE_XML,WEB_SERVICE_NAME,WEB_METHOD_NAME,CREATE_DATE,ERROR_CODE,ERROR_MESSAGE from
SOAP_MONITORING@'||DB_CONNECTION_NAME||' WHERE
ID > (CASE WHEN '||IN_DB_LINK||' = ''FONIC_RETAIL'' THEN ' || LAST_SM_ID || ' ELSE ' || LAST_SM_ID_MB ||' END) AND
WEB_SERVICE_NAME =''RatorWebShopService'' and WEB_METHOD_NAME = ''placeShopOrder'') where rownum <=1000
) data
ON (TSM.ID = data.ID)
when not matched then
insert(ID,REQUEST_XML,RESPONSE_XML,WEB_SERVICE_NAME,WEB_METHOD_NAME,CREATE_DATE,ERROR_CODE,ERROR_MESSAGE,DB_LINK)
values(data.ID,data.REQUEST_XML,data.RESPONSE_XML,data.WEB_SERVICE_NAME,data.WEB_METHOD_NAME,data.CREATE_DATE,data.ERROR_CODE,data.ERROR_MESSAGE,'||DB_CONNECTION_NAME ||')';
DBMS_OUTPUT.PUT_LINE('lsql' || l_sql);
DBMS_OUTPUT.PUT_LINE('lsql' || l_sql);
execute immediate l_sql;
END EXT_SOAP_MONITORING;
以下是我的DBMS结果:
merge into TEMP_SOAP_MONITORING TSM
using (
select * from
(select ID,REQUEST_XML,RESPONSE_XML,WEB_SERVICE_NAME,WEB_METHOD_NAME,CREATE_DATE,ERROR_CODE,ERROR_MESSAGE from
SOAP_MONITORING@FONIC_RETAIL WHERE
ID > (CASE WHEN FONIC_RETAIL = 'FONIC_RETAIL' THEN 201601071130573261 ELSE 201601071130573261 END) AND
WEB_SERVICE_NAME ='RatorWebShopService' and WEB_METHOD_NAME = 'placeShopOrder') where rownum <=1000
) data
ON (TSM.ID = data.ID)
when not matched then
insert(ID,REQUEST_XML,RESPONSE_XML,WEB_SERVICE_NAME,WEB_METHOD_NAME,CREATE_DATE,ERROR_CODE,ERROR_MESSAGE,DB_LINK)
values(data.ID,data.REQUEST_XML,data.RESPONSE_XML,data.WEB_SERVICE_NAME,data.WEB_METHOD_NAME,data.CREATE_DATE,data.ERROR_CODE,data.ERROR_MESSAGE,FONIC_RETAIL)
除了@KevinEsche指出的THEN
后面缺少空白之外,case语句后面也缺少AND(或or):
<snip>
|| ' END)
WEB_SERVICE_NAME =''RatorWebShopService'' <snip> -- <---- missing and/or before web_service_name
因此,看看您的过程输出的merge语句,有两个问题会突然出现,巧合的是在同一行:
ID > (CASE WHEN DB_LINK = 'FONIC_RETAIL' THEN201601071130573261 ELSE END) AND
您忘记在THEN' || LAST_SM_ID
中的THEN
后面添加空格,而且您还没有考虑到LAST_SM_ID_MB
为空。
以下是我如何编写程序
procedure ext_soap_monitoring (in_db_link in varchar2)
as
last_sm_id number := 0;
last_capt_date date;
last_sm_id_mb number := 0;
last_capt_date_mb date;
l_sql varchar2(5000);
l_sql1 varchar2(5000);
db_connection_name varchar2(100);
begin
select db_link
into db_connection_name
from rator_monitoring_configuration.db_connection
where db_link = in_db_link;
--DELETE FROM TEMP_SOAP_MONITORING table before inserting new data into this table
execute immediate 'TRUNCATE TABLE TEMP_SOAP_MONITORING';
-- first retrieve the last id (of the newest record) which has been imported at last extraction
--FONIC
select last_task_id, capturing_date
into last_sm_id, last_capt_date
from capturing
where db_table = 'TEMP_SOAP_MONITORING'
and db = 'FONIC_RETAIL';
--MB
select last_task_id, capturing_date
into last_sm_id_mb, last_capt_date_mb
from capturing
where db_table = 'TEMP_SOAP_MONITORING'
and db = 'MB_RETAIL';
l_sql:=
'merge into temp_soap_monitoring tsm
using (select *
from (select id,
request_xml,
response_xml,
web_service_name,
web_method_name,
create_date,
error_code,
error_message
from soap_monitoring@'||db_connection_name||'
where id > (case when :db_connection_name = ''FONIC_RETAIL'' then ' || last_sm_id || ' else ' || last_sm_id_mb ||' end)
and web_service_name = ''RatorWebShopService''
and web_method_name = ''placeShopOrder'')
where rownum <= 1000) data
on (tsm.id = data.id)
when not matched then
insert (id,
request_xml,
response_xml,
web_service_name,
web_method_name,
create_date,
error_code,
error_message,
db_link)
values (data.id,
data.request_xml,
data.response_xml,
data.web_service_name,
data.web_method_name,
data.create_date,
data.error_code,
data.error_message,
:db_connection_name)';
dbms_output.put_line('lsql' || l_sql);
execute immediate l_sql using db_connection_name, db_connection_name;
end ext_soap_monitoring;
/
请注意,使用绑定变量可以删除sql本身中变量的文字位置。我知道在从表中进行选择时,您无法避免使用动态sql来添加数据库链接名称,但您可以在其他地方使用绑定变量,这应该会使您的代码不太容易注入sql。
另外,看看我是如何格式化merge语句的——是的,它在变量中占据了更多的空间,但现在它的可读性大大提高了!