create or replace procedure CULTUREQUERY AS sID VARCHAR2(20);
iID number(4);
sCol VARCHAR2(20);
sAreaCode VARCHAR2(20);
createSQL VARCHAR2(2000);
insertSQL VARCHAR2(2000);
strSQL VARCHAR2(2000); CURSOR CV_B_Culture IS Select iID,sID from B_Culture Order by sid;
begin
createSQL :=' sAreaCode Varchar2(64) ,sAreaName Varchar2(128),nSum Varchar2(64),';
insertSQL := ' sAreaCode,sAreaName,nSum,';
BEGIN
OPEN CV_B_Culture;
LOOP
FETCH CV_B_Culture
INTO iID,sID;
EXIT WHEN CV_B_Culture%NOTFOUND;
createSQL := createSQL || 'L' || sID || ' Varchar(64),';
insertSQL := insertSQL || 'L' || sID || ','; END LOOP;
CLOSE CV_B_Culture;
if length(createSQL) >0 then
createSQL := SUBSTR(createSQL,1,length(createSQL)-1);
insertSQL := SUBSTR(insertSQL,1,length(insertSQL)-1);
end if;
select count(1) into num from user_tables where table_name= 'M_CULTUREQUERY';
if num> 0 then
execute immediate 'drop table M_CULTUREQUERY';
end if; execute immediate 'create table M_CULTUREQUERY ('''|| createSQL ||''')'; execute immediate 'Insert into M_CULTUREQUERY (sAreaCode,sAreaName) select sID,sName from B_Area Where bLeaf<>0 '; declare
Cursor CV_B_Area IS select sAreaCode From M_CULTUREQUERY Order By sAreaCode;
Begin
Open CV_B_Area;
loop
Fetch CV_B_Area Into sAreaCode;
Exit when cv_B_Area%notfound;
OPEN CV_B_Culture;
LOOP
FETCH CV_B_Culture INTO iID,sID;
EXIT WHEN CV_B_Culture%NOTFOUND;
if iid=1 then
--strsql:='Update M_CULTUREQUERY set nSum=nSum+(select count(1) from ev_M_Base Where sArea like ''|| sAreaCode || '%'' and (istate<> 4 and istate<>-2 and istate<>6) and iLearn <= 1,'|| sCol || '= (select count(1) from ev_M_Base Where sArea like '' || sAreaCode || '%'' and (istate<> 4 and istate<>-2 and istate<>6) and iLearn <= 1)'
strSQL :='Update M_CULTUREQUERY set nSum=nSum+(select count(1) from ev_M_Base Where sArea like ''|| sAreaCode || '%'' and (istate<> 4 and istate<>-2 and istate<>6) and iLearn <= 1),'|| sCol ||'= (select count(1) from ev_M_Base Where sArea like '' || sAreaCode || '%'' and (istate<> 4 and istate<>-2 and istate<>6) and iLearn <= 1) where sAreaCode ='''|| sAreaCode ||'''';
else
strSQL :='Update M_CULTUREQUERY set nSum=nSum+(select count(1) from ev_M_Base Where sArea like ''|| sAreaCode || '%'' and (istate<> 4 and istate<>-2 and istate<>6) and iLearn ='|| iID || '),'|| sCol || '= (select count(1) from ev_M_Base Where sArea like '' || sAreaCode || '%'' and (istate<> 4 and istate<>-2 and istate<>6) and iLearn ='|| iID || ') where sAreaCode =''|| sAreaCode ||''';
end if;
execute immediate strSQL ;
END LOOP;
CLOSE CV_B_Culture;
end loop;
close cv_b_area;
end;
END;
end;
strSQL :='Update M_CULTUREQUERY set nSum=nSum+(select count(1) from ev_M_Base Where sArea like ''|| sAreaCode || '%'' and (istate<> 4 and istate<>-2 and istate<>6) and iLearn <= 1),'|| sCol ||'= (select count(1) from ev_M_Base Where sArea like '' || sAreaCode || '%'' and (istate<> 4 and istate<>-2 and istate<>6) and iLearn <= 1) where sAreaCode ='''|| sAreaCode ||'''';
编译时,此句有错,急
iID number(4);
sCol VARCHAR2(20);
sAreaCode VARCHAR2(20);
createSQL VARCHAR2(2000);
insertSQL VARCHAR2(2000);
strSQL VARCHAR2(2000); CURSOR CV_B_Culture IS Select iID,sID from B_Culture Order by sid;
begin
createSQL :=' sAreaCode Varchar2(64) ,sAreaName Varchar2(128),nSum Varchar2(64),';
insertSQL := ' sAreaCode,sAreaName,nSum,';
BEGIN
OPEN CV_B_Culture;
LOOP
FETCH CV_B_Culture
INTO iID,sID;
EXIT WHEN CV_B_Culture%NOTFOUND;
createSQL := createSQL || 'L' || sID || ' Varchar(64),';
insertSQL := insertSQL || 'L' || sID || ','; END LOOP;
CLOSE CV_B_Culture;
if length(createSQL) >0 then
createSQL := SUBSTR(createSQL,1,length(createSQL)-1);
insertSQL := SUBSTR(insertSQL,1,length(insertSQL)-1);
end if;
select count(1) into num from user_tables where table_name= 'M_CULTUREQUERY';
if num> 0 then
execute immediate 'drop table M_CULTUREQUERY';
end if; execute immediate 'create table M_CULTUREQUERY ('''|| createSQL ||''')'; execute immediate 'Insert into M_CULTUREQUERY (sAreaCode,sAreaName) select sID,sName from B_Area Where bLeaf<>0 '; declare
Cursor CV_B_Area IS select sAreaCode From M_CULTUREQUERY Order By sAreaCode;
Begin
Open CV_B_Area;
loop
Fetch CV_B_Area Into sAreaCode;
Exit when cv_B_Area%notfound;
OPEN CV_B_Culture;
LOOP
FETCH CV_B_Culture INTO iID,sID;
EXIT WHEN CV_B_Culture%NOTFOUND;
if iid=1 then
--strsql:='Update M_CULTUREQUERY set nSum=nSum+(select count(1) from ev_M_Base Where sArea like ''|| sAreaCode || '%'' and (istate<> 4 and istate<>-2 and istate<>6) and iLearn <= 1,'|| sCol || '= (select count(1) from ev_M_Base Where sArea like '' || sAreaCode || '%'' and (istate<> 4 and istate<>-2 and istate<>6) and iLearn <= 1)'
strSQL :='Update M_CULTUREQUERY set nSum=nSum+(select count(1) from ev_M_Base Where sArea like ''|| sAreaCode || '%'' and (istate<> 4 and istate<>-2 and istate<>6) and iLearn <= 1),'|| sCol ||'= (select count(1) from ev_M_Base Where sArea like '' || sAreaCode || '%'' and (istate<> 4 and istate<>-2 and istate<>6) and iLearn <= 1) where sAreaCode ='''|| sAreaCode ||'''';
else
strSQL :='Update M_CULTUREQUERY set nSum=nSum+(select count(1) from ev_M_Base Where sArea like ''|| sAreaCode || '%'' and (istate<> 4 and istate<>-2 and istate<>6) and iLearn ='|| iID || '),'|| sCol || '= (select count(1) from ev_M_Base Where sArea like '' || sAreaCode || '%'' and (istate<> 4 and istate<>-2 and istate<>6) and iLearn ='|| iID || ') where sAreaCode =''|| sAreaCode ||''';
end if;
execute immediate strSQL ;
END LOOP;
CLOSE CV_B_Culture;
end loop;
close cv_b_area;
end;
END;
end;
strSQL :='Update M_CULTUREQUERY set nSum=nSum+(select count(1) from ev_M_Base Where sArea like ''|| sAreaCode || '%'' and (istate<> 4 and istate<>-2 and istate<>6) and iLearn <= 1),'|| sCol ||'= (select count(1) from ev_M_Base Where sArea like '' || sAreaCode || '%'' and (istate<> 4 and istate<>-2 and istate<>6) and iLearn <= 1) where sAreaCode ='''|| sAreaCode ||'''';
编译时,此句有错,急
解决方案 »
- 存储过程怎样返回多个值?
- [求教]编写触发器报错(附SQL语句)
- 菜鸟入行,发现这些个问题都是在学校里木有遇到滴
- 美资公司招Oracle DBA工程师(上海)
- 请问查一个用户下所有对象所占用的空间多少该怎么做.在线等.
- oracle有没有复制一行数据并插入另一张表的语句
- 菜鸟问,Oracle的入门级入门级问题,求各位不要帮帮我,想学习ORACLE
- 我在DBA STUDIO里面建了表,但是用GOLDEN查不到,是为什么呀?
- 关于创建用户的问题,请教了!
- create table image(day date default to_date('01-02-2002','DD-MON-YYYY'))
- 求助:aix下ORACLE数据库移植至WIN2003
- 急!物化视图刷新的问题
'|| sCol ||'
'''|| sAreaCode ||''''
这个的处理好像不对吧
如果数字型的'''|| sAreaCode ||'''' 就不对
'|| sCol ||' 这个不敢确定
strSQL :='Update M_CULTUREQUERY set nSum=nSum+(select count(1) from ev_M_Base Where sArea like '''|| sAreaCode || '%'' and (istate
如果执行有错误,可以将strsql先拼好输出检查一遍
wildwave!!!!!!!!!!!!!!!!!
问题已解决!jietie