这个PL/SQL有什么问题

Question

我有这个pl/sql代码块，

set serveroutput 
clear screen;

DECLARE
  TYPE    recPriceList is Record(comno varchar2(3),t$cpls varchar2(6),t$dsca varchar(100), Customers int,minExpiry varchar2(20),groupCount int,undefGroups int);
  comno   VARCHAR2(6)   :='010';
  sSql    VARCHAR2(1000):=' ';
  c      sys_refcursor;
  r       recPricelist;
BEGIN

  sql:=q'[select distinct  lpad('010',3,'0'), t$cpls cpls,t$dsca,count(t$cuno),null  Customers,null,null  
        from baan.ttccom010010 c  
        join baan.ttcmcs034010 p on c.t$cpls = p.t$cplt  
        where trim(t$cpls) is not null    
        group by T$CPLS,T$DSCA  order by t$cpls']';

  OPEN c FOR sSql ;
  LOOP
    FETCH c INTO r;
    DBMS_OUTPUT.PUT_LINE(r.comno||' | '||r.t$cpls|| ' dsca='|| r.t$dsca);
    EXIT   WHEN c%notfound;
  END LOOP;
END;

我不明白为什么当我运行这个代码块时会抛出下面的错误

SQLPLUS command failed - not enough arguments Error starting at line : 5 in command - DECLARE   TYPE    recPriceList is Record(comno
varchar2(3),t$cpls varchar2(6),t$dsca varchar(100), Customers
int,minExpiry varchar2(20),groupCount int,undefGroups int);   comno  
VARCHAR2(6)   :='010';   sSql    VARCHAR2(1000):=' ';   c     
sys_refcursor;   r       recPricelist; BEGIN

  sql:=q'[select distinct  lpad('010',3,'0'), t$cpls
cpls,t$dsca,count(t$cuno),null  Customers,null,null  
        from baan.ttccom010010 c  
        join baan.ttcmcs034010 p on c.t$cpls = p.t$cplt  
        where trim(t$cpls) is not null    
        group by T$CPLS,T$DSCA  order by t$cpls']';
       OPEN c FOR sSql ;   LOOP
    FETCH c INTO r;
    DBMS_OUTPUT.PUT_LINE(r.comno||' | '||r.t$cpls|| ' dsca='|| r.t$dsca);
    EXIT   WHEN c%notfound;   END LOOP; END; Error report - ORA-06550: line 9, column 6: PLS-00103: Encountered the symbol "=" when expecting
one of the following:

   %
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Answer 1

SQL*Plus命令

set serveroutput ON; 
clear screen;

SQL/块

您需要将变量设置为sSql，我还更正了赋值中的extra single quote！

增加了块，以CLOSE的光标以及！

DECLARE
  TYPE    recPriceList is Record(comno varchar2(3),t$cpls varchar2(6),t$dsca varchar(100), Customers int,minExpiry varchar2(20),groupCount int,undefGroups int);
  comno   VARCHAR2(6)   :='010';
  sSql    VARCHAR2(1000):=' ';
  c      sys_refcursor;
  r       recPricelist;
BEGIN

  sSql := q'[select distinct  lpad('010',3,'0'), t$cpls cpls,t$dsca,count(t$cuno),null  Customers,null,null  
        from baan.ttccom010010 c  
        join baan.ttcmcs034010 p on c.t$cpls = p.t$cplt  
        where trim(t$cpls) is not null    
        group by T$CPLS,T$DSCA  order by t$cpls]';

  OPEN c FOR sSql ;
  LOOP
    FETCH c INTO r;
    DBMS_OUTPUT.PUT_LINE(r.comno||' | '||r.t$cpls|| ' dsca='|| r.t$dsca);
    EXIT   WHEN c%notfound;
  END LOOP;
  CLOSE c;
END;
/

Answer 2

为不存在的变量sql赋值

  sql:=q'[select distinct  lpad('010',3,'0'), t$cpls cpls,t$dsca,count(t$cuno),null  Customers,null,null  
        from baan.ttccom010010 c  
        join baan.ttcmcs034010 p on c.t$cpls = p.t$cplt  
        where trim(t$cpls) is not null    
        group by T$CPLS,T$DSCA  order by t$cpls']';

只需将变量名更改为sSql