create or replace procedure PROC_PM002
(
sStartDate VARCHAR,
sEndDate VARCHAR,
sDeptID VARCHAR
)
is
nTemp NUMBER;
begin
delete from GT_PM002;
If sDeptID = '0' Then
insert into GT_PM002
select
t1.DIC_ITEM_NAME,
(select count(*)
from TB_DEMAND t2
where t2.dem_applydeptname like '%' || t1.dic_item_name || '%'),0,0,0,0,0,0
from TB_PUBLIC_DIC t1
where t1.dic_name = '需求提出单位';
End If;
end PROC_PM002;
(
sStartDate VARCHAR,
sEndDate VARCHAR,
sDeptID VARCHAR
)
is
nTemp NUMBER;
begin
delete from GT_PM002;
If sDeptID = '0' Then
insert into GT_PM002
select
t1.DIC_ITEM_NAME,
(select count(*)
from TB_DEMAND t2
where t2.dem_applydeptname like '%' || t1.dic_item_name || '%'),0,0,0,0,0,0
from TB_PUBLIC_DIC t1
where t1.dic_name = '需求提出单位';
End If;
end PROC_PM002;
不知道楼主说的错误信息是什么?
另建议楼主把
delete from GT_PM002;改成
execute immediate 'truncate table tb';
这样执行速度会快很多。
更正一下,ORACLE中有String和IntegerSQL817> declare a string(9);
2 b integer;
3 begin
4 a:='opi';
5 b:=89;
6 dbms_output.put_line(a);
7 dbms_output.put_line(b);
8 end;
9 /
opi
89PL/SQL 过程已成功完成。SQL817>
在PLSQL Developer里错误提示如下:Compilation errors for PROCEDURE PROMANAGER.PROC_PM002Error: PLS-00103: 出现符号 "SELECT"在需要下列之一时:
(-+modnotnullothers
<an identifier><a double-quoted delimited-identifier>
<a bind variable>avgcountcurrentexistsmaxminpriorsqlstddev
sumvarianceexecuteforalltimetimestampintervaldate
<a string literal with character set specification>
<a number><a single-quoted SQL string>
Line: 16
Text: (select count(*)Error: PLS-00103: 出现符号 ","在需要下列之一时:
;returnreturningandor
Line: 18
Text: where t2.dem_applydeptname like '%' || t1.dic_item_name || '%'),0,0,0,0,0,0Error: PLS-00103: 出现符号 "FROM"在需要下列之一时:
),
Line: 19
Text: from TB_PUBLIC_DIC t1
execute immediate 'truncate table tb';上面建议不好
如果出错不能回滚.
而且会commit之前的语句,非常不妙.
除非楼主没有事务处理
(
sStartDate VARCHAR,
sEndDate VARCHAR,
sDeptID VARCHAR
)
is
nTemp NUMBER;
begin
delete from GT_PM002;
If sDeptID = '0' Then
insert into GT_PM002
select
t1.DIC_ITEM_NAME,t2.countnum
,0,0,0,0,0,0
from TB_PUBLIC_DIC t1,
(select count(*) as countnum
from TB_DEMAND t2
where t2.dem_applydeptname like '%' || t1.dic_item_name || '%') t2
where t1.dic_name = '需求提出单位';
End If;
end PROC_PM002;可以吗?!
from TB_DEMAND t2
where t2.dem_applydeptname like '%' || t1.dic_item_name || '%') t2 里的t1是无法识别的!
select
t1.DIC_ITEM_NAME,t2.countnum
,0,0,0,0,0,0
from TB_PUBLIC_DIC t1,
(select count(*) as countnum
from TB_DEMAND
) t2
where t1.dic_name = '需求提出单位'
and
t2.dem_applydeptname like '%' || t1.dic_item_name || '%';ok?
(
sStartDate String,
sEndDate String,
sDeptID String
)
is
nTemp Integer;
begin
delete from GT_PM002;
If sDeptID = '0' Then
insert into GT_PM002
select t1.dic_item_name,count(0),0,0,0,0,0,0 from tb_public_dic t1,tb_demand t2
where t2.dem_applydeptname like '%'||t1.dic_item_name||'%' and
t1.dic_name='需求提出单位'
group by t1.dic_item_name;
End If;
end PROC_PM002;
(
sStartDate VARCHAR,
sEndDate VARCHAR,
sDeptID VARCHAR
)
is
nTemp NUMBER;
begin
delete from GT_PM002;
If sDeptID = '0' Then
insert into GT_PM002
select
t1.DIC_ITEM_NAME,t2.countnum
,0,0,0,0,0,0
from TB_PUBLIC_DIC t1,
(select count(*) as countnum
from TB_DEMAND,TB_PUBLIC_DIC
where TB_DEMAND.dem_applydeptname like '%' || TB_PUBLIC_DIC.dic_item_name || '%') t2
where t1.dic_name = '需求提出单位';
End If;
end PROC_PM002;
create or replace procedure PROC_PM002
(
sStartDate in VARCHAR,
sEndDate in VARCHAR,
sDeptID in VARCHAR
) is
.....
create or replace procedure PROC_PM002
(
sStartDate varchar2, --数据库类型为什么?如是日期型要定义成date型的。
sEndDate varchar2, --或用 GT_PM002.sEndDate%Type (数据库是什么类型的,那么它就是什么类型的)
sDeptID varchar2
)
is
nTemp Integer;
begin
delete from GT_PM002;
If sDeptID = '0' Then
strSql = 'insert into GT_PM002
select
t1.DIC_ITEM_NAME,
(select count(*)
from TB_DEMAND t2
where t2.dem_applydeptname like ''%'' || t1.dic_item_name || ''%''),0,0,0,0,0,0
from TB_PUBLIC_DIC t1
where t1.dic_name = ''需求提出单位'';' execute immediate strSql;
End If;end PROC_PM002;--***说明:1 在oracle 中String类型为 varchar2,而你的sStartDate的数据库类型为什么?如是日期型要定义成date型的。
2 先把待执行的拼好SQL,再对数据库处理;如strSQL = 'xxxxSQL语句' 中含单引号,要把单引号变成双引号的。
create or replace procedure PROC_PM002
(
sStartDate varchar2, --数据库类型为什么?如是日期型要定义成date型的。
sEndDate varchar2, --或用 GT_PM002.sEndDate%Type (数据库是什么类型的,那么它就是什么类型的)
sDeptID varchar2,
strSql varchar2(2000)
)
is
nTemp Integer;
begin
delete from GT_PM002;
If sDeptID = '0' Then
strSql := 'insert into GT_PM002
select
t1.DIC_ITEM_NAME,
(select count(*)
from TB_DEMAND t2
where t2.dem_applydeptname like ''%'' || t1.dic_item_name || ''%''),0,0,0,0,0,0
from TB_PUBLIC_DIC t1
where t1.dic_name = ''需求提出单位'';' execute immediate strSql;
End If;end PROC_PM002;--***说明:1 在oracle 中String类型为 varchar2,而你的sStartDate的数据库类型为什么?如是日期型要定义成date型的。
2 先把待执行的拼好SQL,再对数据库处理;如strSQL = 'xxxxSQL语句' 中含单引号,要把单引号变成双引号的。