以下是存储过程--开始
create or replace procedure ZHUYE as
beginbegin --如果表存在则删除
execute immediate ' drop table zhuyecompany';
exception when others then
null;
end;
create table zhuyecompany( ---创建表pk_costsubj varchar(50),
costcode varchar(50),
costname varchar(50),
zyjg float, --主业机关
yisc float,
hj float
)
create table sum_costsubj as
select b.settleunitname,f.pk_settleunit,fb.pk_costsubj,sum(plan_money) as plan_money
from fp_planbill f left join bd_settleunit b on f.pk_settleunit=b.pk_settleunit
left join fp_planbill_b fb on f.pk_planbill=fb.pk_planbill
where 1 = 1 and f.pk_corp = '1051' and f.vbillstatus >= '3'
group by b.settleunitname,f.pk_settleunit,fb.pk_costsubj
insert into zhuyecompany(pk_costsubj,costcode,costname)
select pk_costsubj,costcode,costname from bd_costsubj where pk_corp='1051' and costcode not in('5','9','901','902') order by costcodeupdate zhuyecompany set zyjg=(select plan_money from sum_costsubj where zhuyecompany.pk_costsubj=sum_costsubj.pk_costsubj and sum_costsubj.settleunitname='石家庄东方龙供水有限责任公司')update zhuyecompany set yisc=(select plan_money from sum_costsubj where zhuyecompany.pk_costsubj=sum_costsubj.pk_costsubj and sum_costsubj.settleunitname='石家庄东方龙供水有限责任公司一水厂')update zhuyecompany set hj=zyjg+yisc+ersc+sansc+sisc+wusc+liusc+qisc+basc+jxfugs+gxglc+wxgs+jlglzx+jyysbgs+zhjds+yhfwzx+szjcz+gsjls+hqglzx+jsjc+zgyy+ymyey where 1=1end;
EXECCUTE ZHUYE
create or replace procedure ZHUYE as
beginbegin --如果表存在则删除
execute immediate ' drop table zhuyecompany';
exception when others then
null;
end;
create table zhuyecompany( ---创建表pk_costsubj varchar(50),
costcode varchar(50),
costname varchar(50),
zyjg float, --主业机关
yisc float,
hj float
)
create table sum_costsubj as
select b.settleunitname,f.pk_settleunit,fb.pk_costsubj,sum(plan_money) as plan_money
from fp_planbill f left join bd_settleunit b on f.pk_settleunit=b.pk_settleunit
left join fp_planbill_b fb on f.pk_planbill=fb.pk_planbill
where 1 = 1 and f.pk_corp = '1051' and f.vbillstatus >= '3'
group by b.settleunitname,f.pk_settleunit,fb.pk_costsubj
insert into zhuyecompany(pk_costsubj,costcode,costname)
select pk_costsubj,costcode,costname from bd_costsubj where pk_corp='1051' and costcode not in('5','9','901','902') order by costcodeupdate zhuyecompany set zyjg=(select plan_money from sum_costsubj where zhuyecompany.pk_costsubj=sum_costsubj.pk_costsubj and sum_costsubj.settleunitname='石家庄东方龙供水有限责任公司')update zhuyecompany set yisc=(select plan_money from sum_costsubj where zhuyecompany.pk_costsubj=sum_costsubj.pk_costsubj and sum_costsubj.settleunitname='石家庄东方龙供水有限责任公司一水厂')update zhuyecompany set hj=zyjg+yisc+ersc+sansc+sisc+wusc+liusc+qisc+basc+jxfugs+gxglc+wxgs+jlglzx+jyysbgs+zhjds+yhfwzx+szjcz+gsjls+hqglzx+jsjc+zgyy+ymyey where 1=1end;
EXECCUTE ZHUYE
解决方案 »
- 有没有避免每次表名都写"用户名."的办法?
- ORACLE备份策略(ORACLE BACKUP STRATEGY)
- 咨询一个字符串的问题
- 调用xmldom.writetofile方法以后,为什么生成的xml文件却没有<?xml version="1.0" enconding="GB-2312">这一项?
- 如何让a用户访问B用户的表而不在表前面加前缀?
- oracle中如何比较两个时间相差几个月?
- SQL语句错误,急!!!
- 请教
- 如何得到每类数据的第一条.[难]
- pl/sql developer 时间加减组合
- oracle 8 導入 oracle 10
- 为什么运行exec无法执行存储过程,提示无效sql语句
2.下面的insert,update语句最后要加分号";"
2 v_num number(10);
3 begin --如果表存在则删除
4 select count(*) into v_num from user_tables t where lower(t.table_name )= 'zhuyecompany';
5 if v_num=1 then
6 execute immediate ' drop table zhuyecompany';
7 end if;
8 exception when others then
9 null;
10 end;
11 /
Procedure created
SQL> create table zhuyecompany( ---创建表
2 pk_costsubj varchar(50),
3 costcode varchar(50),
4 costname varchar(50),
5 zyjg float, --主业机关
6 yisc float,
7 hj float
8 );
Table created
SQL> exec zhuye;
PL/SQL procedure successfully completed
SQL> select * from zhuyecompany;
select * from zhuyecompany
ORA-00942: table or view does not exist
SQL>
create or replace procedure ZHUYE as
v_num number(10);
begin
--如果表存在则删除
select count(*)
into v_num
from user_tables t
where lower(t.table_name) = 'zhuyecompany';
if v_num = 1 then
execute immediate ' drop table zhuyecompany';
end if;
execute immediate 'create table zhuyecompany(
pk_costsubj varchar(50),
costcode varchar(50),
costname varchar(50),
zyjg float,
yisc float,
hj float)';execute immediate 'create table sum_costsubj as
select b.settleunitname,f.pk_settleunit,fb.pk_costsubj,sum(plan_money) as plan_money
from fp_planbill f left join bd_settleunit b on f.pk_settleunit=b.pk_settleunit
left join fp_planbill_b fb on f.pk_planbill=fb.pk_planbill
where 1 = 1 and f.pk_corp = ''1051'' and f.vbillstatus >= ''3''
group by b.settleunitname,f.pk_settleunit,fb.pk_costsubj';execute immediate
'insert into zhuyecompany
(pk_costsubj, costcode, costname)
select pk_costsubj, costcode, costname
from bd_costsubj
where pk_corp = ''1051''
and costcode not in (''5'', ''9'', ''901'', ''902'')
order by costcode';
execute immediate
'update zhuyecompany
set zyjg = (select plan_money
from sum_costsubj
where zhuyecompany.pk_costsubj = sum_costsubj.pk_costsubj
and sum_costsubj.settleunitname = ''石家庄东方龙供水有限责任公司'')';
execute immediate
'update zhuyecompany
set yisc = (select plan_money
from sum_costsubj
where zhuyecompany.pk_costsubj = sum_costsubj.pk_costsubj
and sum_costsubj.settleunitname = ''石家庄东方龙供水有限责任公司一水厂'')';
execute immediate
'update zhuyecompany
set hj = zyjg + yisc + ersc + sansc + sisc + wusc + liusc + qisc + basc +
jxfugs + gxglc + wxgs + jlglzx + jyysbgs + zhjds + yhfwzx +
szjcz + gsjls + hqglzx + jsjc + zgyy + ymyey
where 1 = 1';exception
when others then
null;
end;
存储过程中DDL语句要用execute immediate 'sql';这样的方式来执行
或者采用dbms_utility.exec_ddl_statement(sql);
create or replace procedure ZHUYE as
v_count number;
begin
select count(1) into v_count from user_tables where table_name=upper('zhuyecompany');if v_count>0 then
execute immediate ' drop table zhuyecompany';
elseexecute immediate 'create table zhuyecompany( pk_costsubj varchar(50),costcode varchar(50),
costname varchar(50),zyjg float, yisc float,hj float)';end if;
end ;
--写了一部分 下面的建表你自己在手动运行 或者用上面的也一样 放在else 里面 用动态执行
来执行!