如何为APEX流程将SQL代码转换为PL/SQL

本文介绍了如何为APEX流程将SQL代码转换为PL/SQL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有下面的代码在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)