如何使用plsql developer将代码格式化为'‘|| chr(10) ||

Question

我需要将大量代码转换为以下格式：

'MERGE INTO employees emp' || chr(10) ||    
'USING (SELECT * FROM (SELECT (SELECT VALUE 
FROM departments 
WHERE CNTRY_CDE = ''100'' 
AND NAME = ''Scott'') BATCH_ID, SUBJECT_ID,' || chr(10) ||  

因为这段代码是作为clob数据插入到表中的。有什么办法可以做到吗？

上面给出的代码只是我拥有的示例。

Answer 1

如果我理解正确的话，您有一条SQL语句，它看起来像这样

'MERGE INTO employees emp USING (SELECT * FROM (SELECT (SELECT VALUE FROM departments WHERE CNTRY_CDE = ''100'' AND NAME = ''Scott'') BATCH_ID, SUBJECT_ID,'

你想让它看起来像

'MERGE INTO employees emp' || chr(10) || 'USING (SELECT * FROM (SELECT (SELECT VALUE FROM departments WHERE CNTRY_CDE = ''100'' AND NAME = ''Scott'') BATCH_ID, SUBJECT_ID,' || chr(10) ||

尽管我对此的实用性心存疑虑，但我认为实现它的一种方法如下所示：

declare 
  strOldstmt VARCHAR2(2000) := 'MERGE INTO employees emp USING (SELECT * FROM (SELECT (SELECT VALUE FROM departments WHERE CNTRY_CDE = ''100'' AND NAME = ''Scott'') BATCH_ID, SUBJECT_ID,';
  strNewstmt VARCHAR2(2000);
begin
  -- Test statements here

  DBMS_OUTPUT.PUT_LINE('strOldstmt=''' || strOldstmt || '''');

  strNewstmt := REPLACE(strOldstmt, 'USING', ''' || CHR(10) || '' USING') || ''' || CHR(10) || ';

  DBMS_OUTPUT.PUT_LINE('strNewstmt=''' || strNewstmt || '''');
end;

这里的问题是，这是非常特定于这个特定语句的，它可能不适用于其他语句；但是，它确实适用于您给出的示例，我无法猜测您更一般的要求是什么。

祝你好运。