create table kehu 
(memberNo  char(18) primary key,
memberName char(8) not null,
memberPassword char(8) not null,
membersex char(3) not null,
membertel char(8) null,
memberaddr varchar(30) null,
yizushuliang int not null check((yizushuliang<7) and (yizushuliang>0))
);



create table pinzhong
(pinzhongNo char(8) primary key
,pinzhongName char(8) not null
,dailyRental Decimal(4,2) not null
);



create table video
(videoNo char(8) primary key 
,videoName char(20) not null
,price number(4,2) not null
,publishtime char(4) not null
,director char(10) not null
,mainactor char(10) not null
,leibie char(10) not null
,publishaddr char(10) not null
,amount int 
,pinzhongNo  char(8)
,constraint fk foreign key(pinzhongNo) references pinzhong(pinzhongNo)
);

create table zujiejilu
(zujieNO char(8) primary key,
videoNo char(8),
memberNo char(18),
dateout date default sysdate,
datereturn date default sysdate+7 ,
yajin decimal(4,2),
zhuangtai char(8) default '未付款',
shishou decimal(4,2) default 0.00,
foreign key(videoNo) references video(videoNo),
foreign key(memberNo) references kehu(memberNo)
);

create or replace trigger test_trigger
before insert or update on zujiejilu
for each row
declare
video_price number; --价格
shishou number;--实收
datereturn number/Date--什么类型楼主自己定
begin

if inserting then
 select video.price * 2 into video_price from video;
 insert into  tab(.....) values(:new.zujieNO,...,video_price,...);
elseif updating then
 if :new.zhuangtai == '已付款' then
   update zujiejilu set zhuangtai=:new.zhuangtai,shishou=:old.yajin;
 elseif ... then
   ...
 end if;
end if;

end;
/

--说实话，实在没看出楼主的存储过程有啥必须，都是更新自己表，又不是更新关联表，
要什么存储过程啊，你把更新语句判断好，多更新几个字段不就行了吗？

------解决方案--------------------

用一个触发器就可以搞定。
测试数据：
SQL code

create table kehu 
(memberNo  char(18) primary key,
memberName char(8) not null,
memberPassword char(8) not null,
membersex char(3) not null,
membertel char(8) null,
memberaddr varchar(30) NULL

);



create table pinzhong
(pinzhongNo char(8) primary key
,pinzhongName char(8) not null
,dailyRental Decimal(4,2) not null
);



create table video
(videoNo char(8) primary key 
,videoName char(20) not null
,price number(4,2) not null
,publishtime char(4) not null
,director char(10) not null
,mainactor char(10) not null
,leibie char(10) not null
,publishaddr char(10) not null
,amount int 
,pinzhongNo  char(8)

);

INSERT INTO Video VALUES('00000001', '碟中谍4', 20, '2012', '斯皮尔', 
'汤母', '01', 'www.dlut', 3, '03');


create table zujiejilu
(zujieNO char(8) primary key,
videoNo char(8),
memberNo char(18),
dateout date default sysdate,
datereturn date default sysdate+7 ,
yajin decimal(4,2),
zhuangtai char(20) default '未付款',
shishou decimal(4,2) default 0.00
);