执行IMMEDIATE，插入多行列

Question

只是想得到一个想法，如果这是正确的方式来做一个多列和行EXECUTE IMMEDIATE并将其分配给一个变量?我试着看例子，但不确定我是否正确地连接行?

sql_stmt        VARCHAR2(200);
sql_stmt:='INSERT INTO (STORECODE, TILLID, TRANSACTIONNR, TRADINGDATE, TRANSTYPE, ' ||
          'OPERATORCODE TRAININGMODE, VOIDED, VALUEGROSS, VALUENETT, VALUEDUE) ' ||
          ‘VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)’;
          EXECUTE IMMEDIATE sql_stmt USING sSTORECODE………………………………………..fGROSS_AMOUNT,
      ‘0’;   

Answer 1

一个字符串在Oracle中可以是多行。因此，您可以简单地写入:

sql_stmt := 'INSERT INTO (STORECODE, TILLID, TRANSACTIONNR, TRADINGDATE, 
                          TRANSTYPE, OPERATORCODE TRAININGMODE, VOIDED, 
                          VALUEGROSS, VALUENETT, VALUEDUE) 
                  VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)';
EXECUTE IMMEDIATE sql_stmt USING p1, p2... p10;

使用连接(||)和几个子字符串当然也可以工作。

Answer 2

set serveroutput on size unlimited;
declare
str_   varchar(1000 char);
date_ date;

begin
  --str_ := 'select sysdate' || chr(10);
  --str_ := str_ || 'from dual';
  str_ := 'select sysdate /* ''comment'' */
           from dual';
  execute immediate str_
  into date_;
  dbms_output.put_line(date_);

end;