如何为APEX流程将SQL代码转换为PL/SQL [英] How do I convert SQL code to PL/SQL for an APEX process
问题描述
我有下面的代码在SQL Developer中能很好地工作,但是我需要在APEX的流程块中输入代码,这只是给我一个PL/SQL选项.下面是我编写的代码:
I've got the below code which works perfectly in SQL Developer, but I need to input the code within a process block in APEX and it is only giving me a PL/SQL option. Below is the code which I've written:
BEGIN
truncate table TEMP_UPLOAD;
Merge into INVOICE b
USING (
SELECT CUSTOMER_CLASS,RULE_AGGREGATOR,BA
FROM CUSTOMER_TEMP_UPLOAD
WHERE CUSTOMER_CLASS = 'CUSTOMER88') u
ON (b.BA = u.BA)
WHEN MATCHED THEN UPDATE SET b.CUSTOMER88_DATE_UPDATED = sysdate
WHEN NOT MATCHED THEN
INSERT (b.CUSTOMER_CLASS,b.RULE_AGGREGATOR,b.BA,b.CUSTOMER88_DATE_ADDED)
VALUES (u.CUSTOMER_CLASS,u.RULE_AGGREGATOR,u.BA,sysdate);
UPDATE INVOICE a
SET a.CUSTOMER88_DATE_REMOVED = sysdate
WHERE BA IN
(select b.BA
from INVOICE b
left join CUSTOMER_temp_upload u
on b.BA = u.BA
where u.BA is null and b.CUSTOMER_CLASS = 'CUSTOMER88');
END;
出现以下错误
1 error has occurred
•ORA-06550: line 3, column 14: PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ;
推荐答案
错误消息将您指向TRUNCATE TABLE
命令.
The error message is pointing you to your TRUNCATE TABLE
command.
TRUNCATE
是DDL命令-不要从PL/SQL调用它.相反,请使用DELETE,以便您的过程是事务安全的.
TRUNCATE
is a DDL command - don't call it from PL/SQL. Instead, use a DELETE so that your process will be transaction-safe.
(P.S.从技术上讲,可以使用EXECUTE IMMEDIATE从PL/SQL运行DDL,但我不建议这样做)
(P.S. it is technically possible to run DDL from PL/SQL using EXECUTE IMMEDIATE - but I don't advise it)
这篇关于如何为APEX流程将SQL代码转换为PL/SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!