col02 number(10,1) not null default 10;
过程例子
create or replace procedure
(para1 in number,
para2 out number)
is
cursor c1 is select count(*) from user_tables;
begin
open c1;
fetch c1 into para2;
close c1;
para2=para2+para1;
exception
when others then
raise;
end;
过程例子
create or replace procedure
(para1 in number,
para2 out number)
is
cursor c1 is select count(*) from user_tables;
begin
open c1;
fetch c1 into para2;
close c1;
para2=para2+para1;
exception
when others then
raise;
end;
解决方案 »
- 存储过程的牛人过来看下!
- 有谁知道Xmldom都有什么方法?或者给个链接,类似帮助文档的东西
- SATA 硬盘是否可以做 Oracle 10G RAC ?
- 如何避免oracle导入时出现死锁
- 索引问题
- 初始化提供程序时发生错误,测试连接失败.发生了一个oracle错误,但无法从oracle中检索错误信息?
- asp连接oracle9i的问题,立等就10分了这几天CSDN不涨分可怜
- 高分求表内消重统计,运算的sql语句(内容内详,高手请进)!!!
- 游标是不是在读完一行记录后会自动下移一行的呢?
- 原系统ORACLE10g换成ORACLE11g后,登陆时报错。
- 触发器问题,急急急!
- 数据字典的问题,高分求救!!!
但是总告诉我少一个右括号是什么意思?(defalut 10)
应该如何做?
create table NewJb1(
jxlb char(4) not null, /*机车类别*/
lbmc varchar(40) not null , /*项目名称*/
trans_head char(4) not null default 0, /*传输文件抬头*/这里报错说没有右括号
col01 number(10,1) not null default 0, /*配属机车 col01=sum(col02+col03+col05+col06-col04)*/
col02 number(10,1) not null , /*出租*/
col03 number(10,1) not null, /*助勤出*/
修*/
)/*已经创建好机报一文件*/
if exists(select name from sysobjects where name='GetNewJb1' and type='P')
drop proc GetNewJb1
go
create proc GetNewJb1
@startrq datetime,
@endrq datetime,
@jxlb char(4)
as
delete from NewJb1
if @jxlb is null select @jxlb=''
insert into NewJb1(jxlb,lbmc,trans_head,col01,col02,col03,col04,
col05,col06,col07,col08,col09,col10,col11,col12,col13,col14,
col15,col16,col17,col18,col19,col20_H,col20,col21_H,
col21,col22_H,col22,col23_H,col23,col24_H,col24,col25_H,col25)
select
jxlb,lbmc,trans_head,col01=sum(col01),col02=sum(col02),col03=sum(col03),col04=sum(col04),
col05=sum(col05),col06=sum(col06),col07=sum(col07),col08=sum(col08),col09=sum(col09),
col10=sum(col10),col11=sum(col11),col12=sum(col12),col13=sum(col13),col14=sum(col14),
col15=sum(col15),col16=sum(col16),col17=sum(col17),col18=sum(col18),
col19=sum(col19),col20_H=sum(col20_H),col20=sum(col20),col21_H=sum(col21_H),
col21=sum(col21),col22_H=sum(col22_H),col22=sum(col22),col23_H=sum(col23_H),
col23=sum(col23),col24_H=sum(col24_H),col24=sum(col24),col25_H=sum(col25_H),
col25=sum(col25)
from NewJb1Rj where jxlb=@jxlb and tjrq>=@startrq and tjrq<=@endrq
group by jxlb,lbmc,trans_head
order by jxlb,trans_head /*计算其他*/
update NewJb1 set col19=col20+col21+col22+col23+col24+col25
update NewJb1 set col07=col08+col09+col10+col11+col12+col13+col14+col15
update NewJb1 set col06=col07+col17+col18+col19
update NewJb1 set col01=col02+col03+col05+col06-col04
update NewJb1 set col19_h=case
when col06 is null then 0
when col06=0 then 0
else round((col19/col06)*100,1)
end,
col20_h=case
when col06 is null then 0
when col06=0 then 0
else round((col20/col06)*100,1)
end,
col21_h=case
when col06 is null then 0
when col06=0 then 0
else round((col21/col06)*100,1)
end,
col22_h=case
when col06 is null then 0
when col06=0 then 0
else round((col22/col06)*100,1)
end,
col23_h=case
when col06 is null then 0
when col06=0 then 0
else round((col23/col06)*100,1)
end,
col24_h=case
when col06 is null then 0
when col06=0 then 0
else round((col24/col06)*100,1)
end,
col25_h=case
when col06 is null then 0
when col06=0 then 0
else round((col25/col06)*100,1)
end
select * from NewJb1
go
grant all on GetNewJb1 to public
go
insert into NewJb1(jxlb,lbmc,trans_head,col01,col02,col03,col04,
col05,col06,col07,col08,col09,col10,col11,col12,col13,col14,
col15,col16,col17,col18,col19,col20_H,col20,col21_H,
col21,col22_H,col22,col23_H,col23,col24_H,col24,col25_H,col25)对上面语句不熟,请解释,怎样得col01,...col25的值
col05,col06,col07,col08,col09,col10,col11,col12,col13,col14,
col15,col16,col17,col18,col19,col20_H,col20,col21_H,
col21,col22_H,col22,col23_H,col23,col24_H,col24,col25_H,col25)
select
jxlb,lbmc,trans_head,col01=sum(col01),col02=sum(col02),col03=sum(col03),col04=sum(col04),
col05=sum(col05),col06=sum(col06),col07=sum(col07),col08=sum(col08),col09=sum(col09),
col10=sum(col10),col11=sum(col11),col12=sum(col12),col13=sum(col13),col14=sum(col14),
col15=sum(col15),col16=sum(col16),col17=sum(col17),col18=sum(col18),
col19=sum(col19),col20_H=sum(col20_H),col20=sum(col20),col21_H=sum(col21_H),
col21=sum(col21),col22_H=sum(col22_H),col22=sum(col22),col23_H=sum(col23_H),
col23=sum(col23),col24_H=sum(col24_H),col24=sum(col24),col25_H=sum(col25_H),
col25=sum(col25)
from NewJb1Rj where jxlb=@jxlb and tjrq>=@startrq and tjrq<=@endrq
group by jxlb,lbmc,trans_head
order by jxlb,trans_head
这一整段是实现向NEWJB1中插入从NEWJB1RJ中按照条件选取的记录值,
我向问一下怎么把这个转成ORACLE下的存储过程啊?
WHERE 条件后面jxlb=@jxlb 改为JXLB=变量。例如:
create or replace procedure
(para1 in number,
para2 out number)aaa number;
bbb varchar2(10);
CCC NUMBER;
is
cursor c1 is select A,B,C from TABLENAME WHERE
WHERE A=PARA1;
begin
open c1;
EXIT WHEN C1%NOTFOUND
fetch c1 into AAA,BBB,CCC; close c1;
para2=para2+para1;
exception
when others then
raise;
end;
这里自己回复的帖子发错了要怎么能够才能够修改,好象没有这个功能吧。
startrq date,
endrq datet,
jxlb varchar2)
as
str varchar2(30);
begin
str:='delete from NewJb1';
execute immediate str;
if jxlb is null then
jxlb=null;
end if;
insert into NewJb1(jxlb,lbmc,trans_head,col01,col02,col03,col04,
col05,col06,col07,col08,col09,col10,col11,col12,col13,col14,
col15,col16,col17,col18,col19,col20_H,col20,col21_H,
col21,col22_H,col22,col23_H,col23,col24_H,col24,col25_H,col25)
(select
jxlb,lbmc,trans_head,sum(col01),sum(col02),sum(col03),sum(col04),
sum(col05),sum(col06),sum(col07),sum(col08),sum(col09),
sum(col10),sum(col11),sum(col12),sum(col13),sum(col14),
sum(col15),sum(col16),sum(col17),sum(col18),
sum(col19),sum(col20_H),sum(col20),sum(col21_H),
sum(col21),sum(col22_H),sum(col22),sum(col23_H),
sum(col23),sum(col24_H),sum(col24),sum(col25_H),
sum(col25)
from NewJb1Rj where jxlb=jxlb and tjrq>=startrq and tjrq<=endrq
group by jxlb,lbmc,trans_head
order by jxlb,trans_head);
.......
下面要进一步解释
create or replace procedure test_proc
(
para1 in number,
para2 out number
)
is
cursor c1 is select count(*) from user_tables;
begin
open c1;
fetch c1 into para2;
close c1;
para2=para2+para1;
exception
when others then
raise;
end test_proc;
/警告:创建过程带有编译错误。
帮忙看一下。
帮忙看一下!
打扰!
祝你眼角创伤早日痊愈!
OWEN