oracle编写银行管理系统,Oracle-PLSQL-银行管理系统简单实现.doc

Oracle-PLSQL-银行管理系统简单实现

--建数据库、数据表set nocount onuse mastergoif exists(select * from sysdatabases where name='bankDB')drop database bankDBgo

create database bankDBon(name='bankDB_data',fileName='D:\temp\bankDB_data.mdf',size=1,filegrowth=15%)log on (name='bankDB_log',fileName='D:\temp\bankDB_log.ldf',size=1,filegrowth=15%)go

use bankDBgoif exists(select * from sysobjects where name='userInfo')drop table userInfogo

create table userInfo(customerID int identity(1,1) not null,customerName char(10) not null,PID varchar(18) not null,telephone varchar(13) not null,address varchar(50) null)use bankDBgoif exists(select * from sysobjects where name='cardInfo')drop table cardInfogocreate table cardInfo(cardID varchar(19) not null,curType varchar(10) not null,savingType char(8) not null,openDate datetime not null,openMoney money not null,balance money not null,pass char(6) not null,IsReportLoss char(2) not null,customerID int not null )use bankDBgoif exists(select * from sysobjects where name='transInfo')drop table transInfogocreate table transInfo(transDate datetime not null,cardID varchar(19) not null,transType char(4) not null,transMoney money not null,remark varchar(50) null)

---------------------------------------------------------为数据表建约束alter table userInfo add constraint pk_customerIDprimary key(customerID)alter table userInfo add constraint ck_PIDcheck (len(PID)=15 or len(PID)=18)alter table userInfoadd constraint uq_PID unique (PID)alter table userInfoadd constraint ck_telephone check (telephone like '1[3,5][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'or telephone like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or telephone like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')go

alter table cardInfoaddconstraint pk_cardID primary key (cardID),constraint ck_cardID check(cardID like '1010 3576 ____ ____'),constraint df_curType default ('RMB') for curType,constraint ck_savingType che