问一下mysql支持甲骨文数据的存储过程语句吗？想用自己的电脑测试一下写的甲骨文数据库存储过程语句
就是想用mysql检测一下下面的shell脚本能不能正常运行，有没有人可以帮我看看我写的有没有问题？
#!/bin/bash
. /pmtech/.bash_profile
read -p "请输入数据库名称:" in_datebase
read -p "请输入单价临时表名称:" in_cbusithrcfg
read -p "请输入规则临时表名称:" in_crulecfg
read -p "请输入分次核发临时表名称:" in_cfccfg
read -p "请输入地市代码:" in_region_code
read -p "请输入时间(yyyymm):" in_date
$in_datebase<<!
DECLARE
vYm varchar2(100);
vnYm varchar2(100);
vLYm varchar2(100);
vcbusithrcfg varchar2(100);
vcrulecfg varchar2(100);
vcfccfg varchar2(100);
vRegion_code varchar2(100);
vSql1 varchar2(4000);
vSql2 varchar2(4000);
vSql3 varchar2(4000);
vNum number(10);
vNum2 number(10);
BEGIN
vYm:='$in_date';
vnYm:=vYm+1;
vLYm:=vYm-1;
vcbusithrcfg:='$in_cbusithrcfg';
vcrulecfg:='$in_crulecfg';
vcfccfg:='$in_cfccfg';
vRegion_code:='$in_region_code';
-------临时单价表自己跟自己去重
vSql1:='delete from '||vcbusithrcfg||' a where rowid<(
select max(rowid) from '||vcbusithrcfg||' b
where a.region_code=b.region_code
and a.busi_code=b.busi_code)
';
EXECUTE IMMEDIATE vSql1;
------跟下月单价表数据表去重
vSql1:='delete from cbusithrcfg'||vnYm||' c
where c.busi_code in (
select a.busi_code
from cbusithrcfg'||vnYm||' a
where a.busi_code in (
select b.busi_code
from '||vcbusithrcfg||' b
where a.region_code = b.region_code
and a.busi_code = b.busi_code
)
and a.region_code =''||vRegion_code||''
)
and c.region_code =''||vRegion_code||''
';
EXECUTE IMMEDIATE vSql1;
-------跟当月数据表去重
vSql1:='delete from cbusithrcfg'||vYm||' c
where c.busi_code in (
select a.busi_code
from cbusithrcfg'||vYm||' a
where a.busi_code in (
select b.busi_code
from bonus.'||vcbusithrcfg||' b
where a.region_code = b.region_code
and a.busi_code = b.busi_code
)
and a.region_code =''||vRegion_code||''
)
and c.region_code =''||vRegion_code||''
';
EXECUTE IMMEDIATE vSql1;
--跟没有月份的数据表去重
vSql1:='delete from cbusithrcfg c
where c.busi_code in (
select a.busi_code
from cbusithrcfg a
where a.busi_code in (
select b.busi_code
from bonus.'||vcbusithrcfg||' b
where a.region_code = b.region_code
and a.busi_code = b.busi_code
)
and a.region_code =''||vRegion_code||''
)
and c.region_code =''||vRegion_code||''
';
EXECUTE IMMEDIATE vSql1;
-------------------------------------------------------------------------规则表去重
-----判断山个月的规则是否与当月的规则数量相等
vSql2:='select count(*) from crulecfg'||vRegion_code|' where bill_ym=''||vYm||'' ';
EXECUTE IMMEDIATE vSql2 into vNum;
vSql2:='select count(*) from crulecfg'||vRegion_code|' where bill_ym=''||vLYm||'' ';
EXECUTE IMMEDIATE vSql2 into vNum2;
if [ vNum -eq 0 ];then
vSql1:='insert into crulecfg'||vRegion_code||'
select ''||vYm||'',
FIR_CODE,
BUSI_CODE,
CASH_TYPE,
RATE,
MAX_FEE,
RULES
from crulecfg791
where bill_ym =''||vLYm||'' ';
elif [ vNum -eq vNum2 ];then
--自己跟自己去重
vSql1:='delete from bonus.'||vcrulecfg||' a where rowid<(
select max(rowid) from bonus.'||vcrulecfg||' b
where a.bill_ym=b.bill_ym
and a.busi_code=b.busi_code)
';
EXECUTE IMMEDIATE vSql1;
--临时规则表数据跟规则表12月791地市数据进行去重
vSql1:='delete from crulecfg'||vRegion_code||' c
where c.busi_code in (
select a.busi_code
from crulecfg'||vRegion_code||' a
where a.busi_code in (
select b.busi_code
from bonus.'||vcrulecfg||' b
where a.bill_ym = b.bill_ym
and a.busi_code = b.busi_code
)
and a.bill_ym =''||vRegion_code||''
)
and c.bill_ym =''||vRegion_code||''
';
EXECUTE IMMEDIATE vSql1;
else
vSql1:='rollback';
EXECUTE IMMEDIATE vSql1;
echo "当月规则表数据数量与上月不相等"
exit 1
fi
----------------------------------------------------------------分次核发表去重
--自己跟自己去重
vSql1:='delete from bonus.'||vcfccfg||' a where rowid<(
select max(rowid) from bonus.'||vcfccfg||' b
where a.region_code=b.region_code
and a.busi_code=b.busi_code
and a.bill_ym=b.bill_ym)
';
EXECUTE IMMEDIATE vSql1;
--最后分次核发临时数据表跟cfccfg表进行去重
vSql1:='delete from cfccfg c
where c.busi_code in (
select a.busi_code
from cfccfg a
where a.busi_code in (
select b.busi_code
from bonus.'||vcfccfg||' b
where a.bill_ym = b.bill_ym
and a.busi_code = b.busi_code
and a.region_code = b.region_code
)
and a.bill_ym =''||vYm||''
)
and c.bill_ym =''||vYm||''
and c.region_code =''||vRegion_code||''
';
EXECUTE IMMEDIATE vSql1;
------------------------------------将处理好的临时单价数据表的数据插入到原单价表中
vSql1:='insert all
into cbusithrcfg'||vYm||' select * from bonus.'||vcbusithrcfg||'
into cbusithrcfg'||vnYm||' select * from bonus.'||vcbusithrcfg||'
into cbusithrcfg select * from bonus.'||vcbusithrcfg||'
select count(*) from dual
';
EXECUTE IMMEDIATE vSql1;
-----------------------------------将处理好的规则临时数据插入到原表中
vSql1:='insert into bonus.crulecfg'||vRegion_code||' select * from bonus.'||vcrulecfg||' ';
EXECUTE IMMEDIATE vSql1;
-----------------------------------将处理好的分次核发临时数据插入到原表中
vSql1:='insert into bonus.cfccfg select * from bonus.'||vcfccfg||' ';
EXECUTE IMMEDIATE vSql1;
COMMIT;
END;
EXIT;
!
