mycard.txt

校园一卡同系统,包括消费 转帐,退款,创建表功能

--table of student
create table student(stu_no  varchar2(20) primary key,stu_name varchar2(10) not null,location varchar2(10) check (location in('West','South','East')))
Storage(
initial 100k            --初始区间大小100kb
next 20k                --下一个区间大小为20kb
Minextents 1            --最小要分配1个区间的磁盘空间
Maxextents 99           --该表最多有99个区间
Pctincrease 10)          --扩充的区间每个比前一个大10%
tablespace USERS         --该表的数据存放在USERS表空间
pctfree 10             --每个Oracle数据块保留10%空闲空间用于修改
pctused 40             --每个Oracle 数据块至少要利用40%的空间
initrans 2             --初始时为2个在该数据块上的并发事务预留数据操作
maxtrans 10;    --空间（最多10个）

insert into student values('0504010113','李','West');
insert into student values('0504010101','王','West');
insert into student values('0504020102','王2',  'South');
insert into student values('0504030101','张',  'East');
insert into student values('0504040301','刘',  'East');

--table of bankcard
create table bankcard(bankcard_id  varchar2(20) primary key,username varchar2(10) not null,cash number(8,2) default 0.00)
Storage(
initial 100k            --初始区间大小100kb
next 20k                --下一个区间大小为20kb
Minextents 1            --最小要分配1个区间的磁盘空间
Maxextents 99           --该表最多有99个区间
Pctincrease 10)          --扩充的区间每个比前一个大10%
tablespace USERS        --该表的数据存放在USERS表空间
pctfree 10              --每个Oracle数据块保留10%空闲空间用于修改
pctused 40              --每个Oracle 数据块至少要利用40%的空间
initrans 2              --初始时为2个在该数据块上的并发事务预留数据操作
maxtrans 10;            --空间（最多10个）

insert into bankcard values('0123456789','李',555.55);
insert into bankcard values('1234567879','王',5555.55);
insert into bankcard values('0987654321','王',  55.55);

--tablespace of my_index
conn system/system as sysdba
create tablespace my_index
datafile'%oracle_home%\database\my_index.dbf' size 1m reuse;
create index student_index on student(stu_name) tablespace my_index;
create index bankcard_index on bankcard(username) tablespace my_index;

--table of schoolcard
create table schoolcard(scard_id varchar2(10) primary key,stu_no varchar2(10),bankcard_id varchar2(20),
school_cash number(8,2) default 0.00,
constraint schoolcard_fk_scard_id foreign key(bankcard_id) references bankcard(bankcard_id) on delete cascade, 
constraint schoolcard_fk_std_no foreign key(stu_no) references student(stu_no) on delete cascade)
Storage(
initial 100k            --初始区间大小100kb
next 20k                --下一个区间大小为20kb
Minextents 1            --最小要分配1个区间的磁盘空间
Maxextents 99           --该表最多有99个区间
Pctincrease 10)          --扩充的区间每个比前一个大10%
tablespace USERS        --该表的数据存放在USERS表空间
pctfree 10             --每个Oracle数据块保留10%空闲空间用于修改
pctused 40             --每个Oracle 数据块至少要利用40%的空间
initrans 2             --初始时为2个在该数据块上的并发事务预留数据操作
maxtrans 10;           --空间（最多10个）

insert into schoolcard values('000000001','0504010113','0123456789',100.00);
insert into schoolcard values('000000002','0504010101','1234567879',100.00);
insert into schoolcard values('000000003','0504020102','0987654321',100.00);

--view of student_bank_cash
create view student_bank_cash  as select stu.stu_name 姓名,stu.location 校区,bank.cash 银行存款 from  student stu,
bankcard bank,schoolcard sch where stu.stu_no=sch.stu_no and bank.bankcard_id=sch.bankcard_id with check option;

--plsql
--procedure of savecash

create procedure savecash(p_stuno in varchar2,p_cashes in number)
is 
begin                                    
  update schoolcard set school_cash=school_cash+p_cashes where stu_no=p_stuno;   
end savecash;
/ 
  select * from schoolcard where stu_no='0504010113';
  execute savecash('0504010113',100.00);
  select * from schoolcard where stu_no='0504010113';

--procedure of transportcash
create procedure transportcash(p_bankcardid in varchar2,p_cashes in number)
is 
   procedure banktransport   -
   is 
     begin         
     update bankcard set cash=cash-p_cashes where bankcard_id=p_bankcardid;  
     end banktransport;   
begin  
   banktransport;       
   update schoolcard set school_cash=school_cash+p_cashes where bankcard_id=p_bankcardid;  
 
end transportcash;
/
  select * from schoolcard where stu_no='0504010113';   
  select * from  student_bank_cash;                  
  execute transportcash('0123456789',50.00);       
  select * from schoolcard where stu_no='0504010113';    
  select * from  student_bank_cash;  
                  
--procedure of consume
  
  create procedure consume(p_stuno in varchar2,p_cashes in number)
  is 
  begin 
     update schoolcard set school_cash=school_cash-p_cashes where stu_no=p_stuno;  
  end consume;
/
  select * from schoolcard where stu_no='0504010113';
  execute consume('0504010113',15.00);
  select * from schoolcard where stu_no='0504010113';
  
============================================================================
--procedure of giveback
create procedure giveback(p_stuno in varchar2,p_cashes in number)
  is 
  begin 
     update schoolcard set school_cash=school_cash+p_cashes where stu_no=p_stuno;  
  end giveback;
/
  select * from schoolcard where stu_no='0504010113';
  execute giveback('0504010117',15.00);
  select * from schoolcard where stu_no='0504010113';