问题描述

存储过程中SELECT赋值报错什么原因？

CREATE OR REPLACE PROCEDURE PROC_DICTABLE_TBYSFL(p_ANetUser VARCHAR)
IS
vc_DICTABLEID varchar2(36);
vc_DICTABLECOLID varchar2(36);
vc_DICCOL1 VARCHAR2(20);
vc_DICCOL2 VARCHAR2(20);
vc_DICCOL3 VARCHAR2(20);
vc_DICCOL4 VARCHAR2(20);
vc_DICCOL5 VARCHAR2(20);
vc_DICTABLENAME VARCHAR2(20);
vc_PUBLICSQL VARCHAR2(200); --提取公有数据条件
vc_AVALUE1 VARCHAR(50);
vc_AVALUE2 VARCHAR(50);
vc_BVALUE1 VARCHAR(50);
vc_BVALUE2 VARCHAR(50);
vc_SQL varchar(500);
int_num number;
--vc_SQL0 varchar(500);
--vc_SQL1 varchar(500);
--vc_SQL2 varchar(500);
--vc_SQL3 varchar(500);
type myCursor is ref Cursor;
cur myCursor;
begin
select A.DICTABLEIDA.DICTABLECOLIDA.DICCOL1A.DICCOL2A.DICCOL3A.DICCOL4A.DICCOL5B.DICTABLENAME AS DICTABLENAMEB.PUBLICSQL
into vc_DICTABLEIDvc_DICTABLECOLIDvc_DICCOL1vc_DICCOL2vc_DICCOL3vc_DICCOL4vc_DICCOL5vc_DICTABLENAMEvc_PUBLICSQL
from T_DIC_TABLE_COL A INNER JOIN T_DIC_TABLE B ON A.DICTABLEID=B.DICTABLEID WHERE A.DICTABLECOLID=55;

  if vc_DICCOL2 is not null then  OPEN cur for 'select A_VALUE_COL1A_VALUE_COL2B_VALUE_COLE1B_VALUE_COL2 FROM  T_DIC_TABLE_COL_VALUEMAP WHERE DICTABLECOLID=55 AND VALUEOK=0';  fetch cur into vc_AVALUE1vc_AVALUE2vc_BVALUE1vc_BVALUE2;  WHILE (cur % found) loop      select COUNT(*) INTO int_num FROM  || p_ANetUser || '.TBYSFL A WHERE ' ||  vc_PUBLICSQL || ' AND A.' || vc_DICCOL1 || 'A.' || vc_DICCOL2 || '=' || vc_BVALUE1 || vc_BVALUE2;      if int_num=0 then      vc_SQL :='update ' || p_ANetUser || '.TBYSFL A SET ' || vc_DICCOL1 || '=' || vc_BVALUE1 || '' || vc_DICCOL2 || '=' || vc_BVALUE2              || ' where ' || vc_PUBLICSQL || ' AND A.' || vc_DICCOL1 || 'A.' || vc_DICCOL2 || '=' || vc_BVALUE1 || vc_BVALUE2;      execute immediate vc_SQL;      commit;      end if;      fetch cur into vc_AVALUE1vc_AVALUE2vc_BVALUE1vc_BVALUE2;  END LOOP;  CLOSE cur;  end if;

END PROC_DICTABLE_TBYSFL;

解决方案

在存储过程中 给变量赋值

解决方案二：
哪位大神帮我看一下，报错在这句select COUNT(*) INTO int_num FROM || p_ANetUser || '.TBYSFL A WHERE ' || vc_PUBLICSQL || ' AND A.' || vc_DICCOL1 || 'A.' || vc_DICCOL2 || '=' || vc_BVALUE1 || vc_BVALUE2;
我想知道为什么上面也有一句SELECT赋值给变量，就没有报错。 报ORA-00903表名无效

解决方案三：
你可以把vc_SQL这个SQL输出出来看看到底是什么