存储过程中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.DICTABLEID,A.DICTABLECOLID,A.DICCOL1,A.DICCOL2,A.DICCOL3,A.DICCOL4,A.DICCOL5,B.DICTABLENAME AS DICTABLENAME,B.PUBLICSQL
into vc_DICTABLEID,vc_DICTABLECOLID,vc_DICCOL1,vc_DICCOL2,vc_DICCOL3,vc_DICCOL4,vc_DICCOL5,vc_DICTABLENAME,vc_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_COL1,A_VALUE_COL2,B_VALUE_COLE1,B_VALUE_COL2 FROM  T_DIC_TABLE_COL_VALUEMAP WHERE DICTABLECOLID=55 AND VALUEOK=0';
  fetch cur into vc_AVALUE1,vc_AVALUE2,vc_BVALUE1,vc_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_AVALUE1,vc_AVALUE2,vc_BVALUE1,vc_BVALUE2;
  END LOOP;
  CLOSE cur;
  end if;

END PROC_DICTABLE_TBYSFL;

查看全部
u012063243
IOS潘
2015/08/21 09:22
  • select
  • 存储
  • 点赞
  • 收藏
  • 回答
    私信

2个回复