创建数据表a
CREATE TABLE a
(
dt varchar2(10),
num number(10),
sum_num number(10)
)
向表a中插入5条记录
insert into a values('201201',10,10);
insert into a values('201202',20,20);
insert into a values('201203',30,30);
insert into a values('201204',40,40);
insert into a values('201205',50,50);
查询a表记录:
创建函数sum_num()
create or replace function sum_num(dt VARCHAR2)
return number
IS
--PRAGMA AUTONOMOUS_TRANSACTION;
n_Sum NUMBER;
begin
select sum(nvl(b.num,0))
into n_Sum
from a b
where b.dt<dt;
return n_Sum;
end;
向表中插入一条记录:
方法一:
insert into a values('201206',60,sys.sum_num('201206'));
结果如下:
方法二:
insert into a values('201206',60,(select sum(b.num) from a b where b.dt<'201206'));
为什么会出现不同的结果呢??
CREATE TABLE a
(
dt varchar2(10),
num number(10),
sum_num number(10)
)
向表a中插入5条记录
insert into a values('201201',10,10);
insert into a values('201202',20,20);
insert into a values('201203',30,30);
insert into a values('201204',40,40);
insert into a values('201205',50,50);
查询a表记录:
创建函数sum_num()
create or replace function sum_num(dt VARCHAR2)
return number
IS
--PRAGMA AUTONOMOUS_TRANSACTION;
n_Sum NUMBER;
begin
select sum(nvl(b.num,0))
into n_Sum
from a b
where b.dt<dt;
return n_Sum;
end;
向表中插入一条记录:
方法一:
insert into a values('201206',60,sys.sum_num('201206'));
结果如下:
方法二:
insert into a values('201206',60,(select sum(b.num) from a b where b.dt<'201206'));
为什么会出现不同的结果呢??
解决方案 »
- oracle中查询的结果横,竖混合着排
- powerdesigner9.5 拷贝表后,表名后边都加了2,怎样不出现这中情况.
- DAT数据文件问题
- 请问一个权限问题
- 请问各位大虾,建立索引的时候是不是只能产生EXCLUSIVE LOCK?
- 用批处理写热备份时碰到一个问题!
- 触发器使数据不能插入的问题,
- 请问oracle 8.0.5支持ADO的连接方式吗?
- oracle9i pro*c on Windows2000 server 请教高手
- Enterprise Manager ,system/manager ,SYSDBA 为何登陆不了,Normal 就可以
- oracle大表联合查询,速度超级慢
- 急急急!求解
create or replace function sum_num(v_dt VARCHAR2) --参数改个名字
return number
IS
--PRAGMA AUTONOMOUS_TRANSACTION;
n_Sum NUMBER;
begin
select sum(nvl(b.num,0))
into n_Sum
from a b
where b.dt<v_dt; --这里如果写dt 数据库会把它当做字段名,并不会当做你的入参,结果自然就不一样了
return n_Sum;
end;