create or replace procedure CRD_DIM_JAFS is
begin
/**************************
2007-8-13 修改
***************************/
create table temp(
DLDM varchar(20),
DLMC varchar(20),
ZLDM varchar(20),
ZLMC varchar(20)
);
COMMIT;
--刑事:
insert into temp(
select
substr(code,0,3) DLDM ,
'刑事' DLMC ,
kind ZLDM ,
ltrim(rtrim(ltrim(name,'刑事'),'案件结案方式代码'),'--') ZLMC
from SS_BZDM
where
name like '%结案方式%'
and name like '刑事%'
);
commit;
--民事:
insert into temp(
select
'2-3' DLDM ,
'民事' DLMC ,
kind ZLDM ,
ltrim(rtrim(ltrim(name,'民事'),
'案件结案方式代码'),'--') ZLMC
from SS_BZDM
where
(name like '%结案方式%' and code like '2-3-%' )
or code='2-8-1-X'
);
COMMIT;
--行政:
insert into temp_table(
select
substr(code,0,3) DLDM ,
'行政' DLMC ,
kind ZLDM ,
ltrim(rtrim(ltrim(name,'行政'),'案件结案方式代码'),'--') ZLMC
from SS_BZDM
where
name like '%结案方式%' and code like '2-4-%'
);
COMMIT;
--赔偿:
insert into temp(
select
substr(code,0,3) DLDM ,
'赔偿' DLMC ,
kind ZLDM ,
'赔偿' ZLMC
from SS_BZDM
where
name like '%结案方式%'
and code like '2-5-%'
);
COMMIT;
--执行:
insert into temp_table (
select
substr(code,0,3) DLDM ,
'执行' DLMC ,
kind ZLDM ,
'执行' ZLMC
from SS_BZDM
where code='2-6-4-X'
);
COMMIT;
--信访:
insert into temp_table(
select
substr(code,0,3) DLDM ,
'信访' DLMC ,
kind ZLDM ,
rtrim(name,'信访结案方式') ZLMC
from SS_BZDM
where code='2-8-5-X'
or code='2-8-6-X'
);
COMMIT;
insert into dw.dim_jafs(
select
a.*,
b.code XLDM,
b.name XLMC
from
temp a left join SS_BZDM b
on
a.zldm=b.kind
);
commit;
drop table temp;
commit
end CRD_DIM_JAFS;
提示
Compilation errors for PROCEDURE ETL.TEST2
Error: PLS-00103: 出现符号 "CREATE"在需要下列之一时:
begin case declare exit
for goto if loop mod null pragma raise return select update
while with
pipe
Line: 6
Text: create table testtable(id varchar(20);
Error: Hint: Parameter 'maxnum' is declared but never used in 'test2'
Line: 1
Text: create or replace procedure test2(maxnum in integer,i in integer) is
Error: Hint: Parameter 'i' is declared but never used in 'test2'
Line: 1
Text: create or replace procedure test2(maxnum in integer,i in integer) is
Error: Hint: Variable 'ss' is declared but never used in 'test2'
Line: 4
Text: declare ss integer :=4322;
|