- 浏览: 303385 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
ae6623:
ae6623 写道大哥,你是怎么知道它对临时文件有限制的,我也 ...
导出excel2007 poi3.8 -
ae6623:
大哥,你是怎么知道它对临时文件有限制的,我也发现这个bug了, ...
导出excel2007 poi3.8 -
coralandbill:
下载不了啊 能不能给我发一个simpleProj.war包啊 ...
jqgrid使用步骤及说明 -
maojin:
这是jqgrid几?那个电话号码校验的函数能调到吗?
jqgrid使用步骤及说明 -
qingyezhu:
请问,用poi3.8中的wordtohtmlconver类将d ...
导出excel2007 poi3.8
- -----------创建数据库----------------
- user master
- go
- if exists (select * from sysdatabases where name= 'bankSystem' )
- drop database bankSystem
- go
- create database bankSystem
- on primary
- (
- name='bank_data' ,
- filename='D:\bank\bank_data.mdf' ,
- size=5,
- filegrowth=15%
- )
- log on
- (
- name='bank_log' ,
- filename='D:\bank\bank_log.ldf' ,
- size=5,
- filegrowth=15%
- )
- go
- ----------------建表并添加约束----------------
- use bankSystem
- go
- ---------------------表userInfo--------------
- if exists (select * from sysObjects where name= 'userInfo' )
- drop table userInfo
- go
- create table userInfo
- (
- customerID int identity(1,1) not null ,
- customerName varchar(30) not null ,
- PID varchar(18) not null ,
- telephone varchar(13) not null ,
- address varchar(50)
- )
- go
- alter table userInfo
- add constraint PK_customID primary key (customerID)
- alter table userInfo
- add constraint UQ_pid unique (PID)
- alter table userInfo
- add constraint CK_pid check (pid like '4206[0-9][0-9][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
- pid like '4206[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' )
- alter table userInfo
- add constraint CK_telephone check (telephone like '13[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]' )
- ------------表cardInfo---------------
- if exists (select * from sysObjects where name= 'cardInfo' )
- drop table cardInfo
- go
- create table cardInfo
- (
- cardID varchar(18) not null ,
- curType varchar(10) not null ,
- savingType varchar(10) not null ,
- openDate datetime not null ,
- openMoney money not null ,
- balance money not null ,
- pass varchar(6) not null ,
- IsReportLoss bit not null ,
- customerID int not null
- )
- go
- alter table cardInfo
- add constraint PK_cardID primary key (cardID)
- alter table cardInfo
- add constraint CK_cardID check (cardID like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9]' )
- alter table cardInfo
- add constraint DF_curType default ( 'RMB' ) for curType
- alter table cardInfo
- add constraint DF_openDate default (getDate()) for openDate
- alter table cardInfo
- add constraint CK_openMoney check (openMoney >=1)
- alter table cardInfo
- add constraint CK_balance check (balance>=1)
- alter table cardInfo
- add constraint CK_pass check (pass like '[0-9][0-9][0-9][0-9][0-9][0-9]' )
- alter table cardInfo
- add constraint DF_pass default (888888) for pass
- alter table cardInfo
- add constraint DF_IsReportLoss default (0) for IsReportLoss
- alter table cardInfo
- add constraint FK_customerID foreign key (customerID) references userInfo (customerID)
- alter table cardInfo
- add constraint CK_savingType check (savingType like '活期' or savingType like '定活两期' or savingType like '定期' )
- ---------------表transInfo-------------
- if exists (select * from sysObjects where name= 'transInfo' )
- drop table transInfo
- go
- create table transInfo
- (
- transDate datetime not null ,
- cardID varchar(18) not null ,
- transType varchar(4) not null ,
- transMoney money not null ,
- remark text
- )
- go
- alter table transInfo
- add constraint DF_transDate default (getDate()) for transDate
- alter table transInfo
- add constraint FK_cardID foreign key (cardID) references cardInfo (cardID)
- alter table transInfo
- add constraint CK_transType check (transType like '存入' or transType like '支取' )
- alter table transInfo
- add constraint CK_transMoney check (transMoney >0)
- go
- --------------插入数据----------------
- insert into userInfo values ('张三' , '420656789012345' , '010-67898978' , '北京海淀' )
- insert into userInfo values ('李四' , '420645678912345678' , '0478-44443333' , default )
- insert into cardInfo values ('1010 3576 1212 113' , default , '定期' , '2007-10-10 11:54:36.812' ,1,1, default , default ,2)
- insert into cardInfo values ('1010 3576 1234 567' , default , '活期' , '2007-10-10 11:58:45.352' ,1000,1000, default , default ,1)
- insert into transInfo (transDate,transType,cardID,transMoney) values ('2007-10-24 11:56:36.812' , '支取' , '1010 3576 1234 567' ,900)
- update cardInfo set balance=balance-900 where cardID= '1010 3576 1234 567'
- insert into transInfo (transDate,transType,cardID,transMoney) values ('2007-10-24 11:56:54.245' , '存入' , '1010 3576 1212 113' ,5000)
- update cardInfo set balance=balance+5000 where cardID= '1010 3576 1212 113'
- ---------------修改密码--------------
- update cardInfo set pass= '123456' where cardID= '1010 3576 1234 567'
- update cardInfo set pass= '123123' where cardID= '1010 3576 1212 113'
- -------------是否挂失------------
- update cardInfo set IsReportLoss=1 where cardID= '1010 3576 1212 113'
- ---------------统计银行资金流通余额和盈利结算--------------
- declare @inMoney money,@outMoney money,@sumMoney money,@rateEnd money
- select @inMoney=sum(transMoney) from transInfo where transType='存入'
- select @outMoney=sum(transMoney) from transInfo where transType='支取'
- set @sumMoney=@inMoney-@outMoney
- set @rateEnd=@outMoney*0.008-@inMoney*0.003
- print '银行流余额总计为:' +convert(varchar(20),@sumMoney)+ 'RMB'
- print '盈利结算结果为:' +convert(varchar(20),@rateEnd)+ 'RMB'
- ---------------查询本周开户卡号----------------
- select cardID from cardInfo where datepart(wk,openDate)=datepart(wk,getDate())
- ----------------查询本月交易金额最高卡号------------
- select * from userInfo where customerID in
- (select customerID from cardInfo where cardID in
- (select cardID from cardInfo where datepart(wk,openDate)=datepart(wk,getDate())))
- ----------------本月交易金额最大的卡号为-----------------
- select cardID from transInfo where transMoney=(
- select max(transMoney) from transInfo where datepart(mm,transDate)=datepart(mm,getDate()))
- ---------------------挂失帐号客户信息----------------
- select customerName as 客户姓名 telephone as 电话 from userInfo where customerID in
- (select customerID from cardInfo where isreportloss = 1)
- -------------------------催款提醒业务-------------
- select customerName as 客户姓名,telephone as 电话,balance as 卡上余额 from userInfo inner join cardInfo
- on userInfo.customerID=cardInfo.customerID where userInfo.customerID in
- (select customerID from cardInfo where balance < 200)
- ------------创建索引--------------
- if exists (select * from sysindexes where name= 'IX_cardid' )
- drop index transInfo.IX_cardid
- go
- create nonclustered index IX_cardid
- on transInfo (cardID)
- with fillfactor=70
- go
- select * from transInfo with(index=IX_cardid) where cardID ='1010 3576 1212 113'
- ------------创建视图-------------
- if exists (select * from sysobjects where name= 'view_userInfo' )
- drop view view_userInfo
- go
- create view view_userInfo
- as
- select 客户编号=customerID,开户姓名=customerName,身份证号=PID,电话=telephone,地址=address from userinfo
- go
- if exists (select * from sysobjects where name= 'view_cardInfo' )
- drop view view_cardInfo
- go
- create view view_cardInfo
- as
- select 卡号=cardID,货币=curType,存款类型=savingType,开户日期=openDate,
- 余额=balance,密码=pass,是否挂失=isreportloss,客户编号=customerID from cardInfo
- go
- if exists (select * from sysobjects where name= 'view_transInfo' )
- drop view view_transInfo
- go
- create view view_transInfo
- as
- select 交易日期=transDate,交易类型=transType,卡号=cardID,交易金额=transMoney,备注=remark
- from transInfo
- go
- select * from view_userInfo
- select * from view_cardInfo
- select * from view_transInfo
- go
- ---------------创建存储过程----------------
- if exists (select * from sysobjects where name= 'proc_getAndoutMoney' )
- drop proc proc_getAndoutMoney
- go
- create proc proc_getAndoutMoney
- @cardID varchar(19),
- @money money,
- @type char (4),
- @inputpass char (6)
- as
- if (@type= '支取' )
- begin
- begin transaction
- declare @error int
- set @error = 0
- if (@inputpass=(select pass from cardInfo where cardID=@cardID))
- begin
- update cardInfo set balance=balance-@money where cardID=@cardID
- set @error=@error+@@error
- insert into transInfo values (getDate(),@cardID,@type,@money,default )
- set @error=@error+@@error
- end
- else
- print '密码错误'
- if (@error<>0)
- rollback transaction
- else
- commit transaction
- end
- else
- begin
- update cardInfo set balance=balance+@money where cardID=@cardID
- insert into transInfo values (getDate(),@cardID,@type,@money,default )
- end
- go
- exec proc_getAndoutMoney '1010 3576 1212 113' ,500, '存入' , '000000'
- exec proc_getAndoutMoney '1010 3576 1234 567' ,300, '支取' , '123446'
- select * from cardInfo where cardID='1010 3576 1212 113'
- -----------随机产生卡号----------
- use bankSystem
- go
- if exists (select * from sysobjects where name= 'proc_randCardID' )
- drop proc proc_randCardID
- go
- create proc proc_randCardID
- @randCardID varchar(18) output
- as
- declare @rand numeric(15,8)
- declare @tempStr varchar(16)
- select @rand=rand((datepart(mm,getDate())*100000+datepart(ss,getDate())*1000+datepart(ms,getDate())))
- set @tempStr=convert(varchar(16),@rand)
- set @randCardID= '1010 3576 ' +subString(@tempStr,3,4)+ ' ' +subString(@tempStr,7,3)
- go
- declare @mycardID varchar(18)
- exec proc_randCardID @mycardID output
- print '随机产生卡号为:' +@mycardID
- -------------开户存储过程--------------
- set nocount on
- if exists (select * from sysobjects where name= 'proc_openAccount' )
- drop proc proc_openAccount
- go
- create proc proc_openAccount
- @customerName varchar(8),
- @PID varchar(18),
- @telephone char (13),
- @openMoney money,
- @savingType char (8),
- @address varchar(50)=' '
- as
- declare @cardID varchar(18)
- declare @customerID int
- while (1=1)
- begin
- exec proc_randCardID @cardID output
- if not exists (select * from cardInfo where cardID=@cardID)
- break
- else
- continue
- end
- if (@openMoney<1)
- return
- else
- begin
- begin transaction
- declare @error int
- set @error=0
- insert into userInfo values (@customerName,@PID,@telephone,@address)
- set @error=@error+@@error
- select @customerID=customerID from userInfo where customerName=@customerName
- set @error=@error+@@error
- insert into cardInfo (cardID,savingType,openMoney,balance,customerID)
- values (@cardID,@savingType,@openMoney,@openMoney,@customerID)
- set @error=@error+@@error
- if (@error<>0)
- rollback transaction
- else
- commit transaction
- end
- print '尊敬的客户,开户成功!系统为您产生的随机卡号为:' +@cardID+ ' 开户日期 ' +
- convert(varchar(30),getDate(),111)+' 开户金额 ' +convert(varchar(10),@openMoney)
- go
- exec proc_openAccount '王五' , '420656889012678' , '2222-63598978' ,1000, '活期' , '河南新乡'
- -----------------转账事务------------------
- if exists (select * from sysobjects where name= 'proc_transfer' )
- drop proc proc_transfer
- go
- create proc proc_transfer
- @card1 char (18),
- @card2 char (18),
- @outMoney money
- as
- begin transaction
- declare @error int
- set @error=0
- update cardInfo set balance=balance-@outMoney where cardID=@card1
- set @error=@error+@@error
- insert into transInfo values (getDate(),@card1,'支取' ,@outMoney, default )
- set @error=@error+@@error
- update cardInfo set balance=balance+@outMoney where cardID=@card2
- set @error=@error+@@error
- insert into transInfo values (getDate(),@card2,'存入' ,@outMoney, default )
- set @error=@error+@@error
- if (@error<>0)
- rollback transaction
- else
- commit transaction
- go
- exec proc_transfer '1010 3576 1212 113' , '1010 3576 1234 567' ,2000
- select * from transInfo where cardID='1010 3576 1212 113'
- select * from transInfo where cardID='1010 3576 1234 567'
- -------------------创建登录帐号和数据库用户---------------------
- exec sp_addlogin 'sysAdmin' , '1234'
- exec sp_grantdbaccess 'sysAdmin' , 'sysAdminBankUser'
-
grant insert,update,delete,select on userInfo,cardInfo,transInfo to sysAdminBankUser
补上触发器:
- --update触发器
- set nocount on
- if exists (select * from sysobjects where name = 'trig_ppr_Update' )
- drop trigger trig_ppr_Update
- go
- create trigger trig_ppr_Update
- on PlanPropertyRelation
- --with encryption --加密
- for update
- as
- declare @Plans_Id int ,@currentId int ,@childId int ,@childNewLevel int
- declare @oldFatherId int ,@newFatherId int
- select @oldFatherId=PPR_ProExtend_ID from deleted
- select @newFatherId=PPR_ProExtend_ID from inserted
- if (@oldFatherId=@newFatherId)
- return ;
- else
- begin
- select @Plans_Id=Plans_ID,@childId=Property_ID,@currentId=PlanPropertyRelation_ID from inserted
- delete from PropertyValuesRelation where PlanPropertyRelation_ID=@currentId
- end
- go
- --删除触发器,删除前触发
- set nocount on
- if exists (select * from sysobjects where name = 'trig_ppr_BeforeDelete' )
- drop trigger trig_ppr_BeforeDelete
- go
- create trigger trig_ppr_BeforeDelete
- on PlanPropertyRelation
- --with encryption --加密
- INSTEAD OF delete
- as
- declare @oldId int ,@error int
- select @oldId=PlanPropertyRelation_ID from deleted
- delete from PropertyValuesRelation where PlanPropertyRelation_ID=@oldId
- delete from PlanPropertyRelation where PlanPropertyRelation_ID=@oldId
-
go
-
发表评论
-
oracle csv存储过程
2011-11-29 22:46 1189CREATE OR REPLACE ... -
sqlplus Set常用设置
2011-11-29 22:37 1077SqlPlus Set常用设置 ... -
sqlplus 导出CSV
2011-11-29 22:35 41021、 首先连接数据库: sqlplus sys/pass ... -
oracle sql*plus登录方式
2011-11-23 16:09 1421sqlplus登陆方式 sqlpl ... -
oracle相关网站
2011-11-14 12:41 2255国内ORACLE相关站点 Oracle中国公 ...
相关推荐
SqlServer数据库字典--表.视图.函数.存储过程.触发器.主键.外键.约束.规则
关于数据库的视图、索引、存储过程和触发器的使用
详细讲解你:程序设计、视图、索引、游标、事务、触发器、锁、存储过程、XML、权限管理…… 并加以注释!
(2)使用“实验一”中的数据库“abc”,创建一个带有输入参数的存储过程proc_abc,查询指定职工的销售记录,用户输入职工编号,存储过程返回职工名称、产品名称、销售日期、销售数量,假如执行存储过程时所提供的...
SQL Server 数据库基础.pdf,SQL Server 数据管理(常用函数).pdf,SQL Server 数据查询(表的关联).pdf,SQL Server 事务索引视图.pdf,SQL Server 存储过程及触发器.pdf,SQL Server 编程及高级查询.pdf,让你从入门...
SQL2005 创建索引,视图,存储过程,触发器
创建了计算费用、将单号集分隔得到单号数量的函数,模糊查询订单的存储过程,还有视图、索引及触发器。 具体要求如下: 1.至少3张表 2.要定义表约束(例如主码、外码、非空等) 3.每张表至少插入10条记录 4.至少定义...
在神通数据库系统中,触发器是一种特殊类型的存储过程,它在指定的表中的数据发生变化时自动生效 被触发执行。唤醒调用触发器以响应 INSERT、UPDATE 或 DELETE 语句。触发器可以查询其它表,并 9 可以包含复杂的 ...
SQL的存储过程、触发器等建立视图存储过程触发器函数(自定义函数)索引 视图 视图是从一个或几个基本表(或视图)导出的表。不同的是,它是一虚表,数据库中只存放视图的定义,而不存放视图对应的数据,这些数据...
浙江大学数据库系统管理和维护(SQL server)讲稿 employee.sql 第01章 数据库基础.ppt 第02章 SQL Server 关系数据库概述.ppt 第03章 SQL 语言基础.ppt 第04章 T-SQL程序设计基础.ppt 第05章 管理事务.ppt 第...
学习sql存储过程,一则关于存储过程的学习笔记
6.12 Sql(基础) 1 6.15 sql(数据库完整性) 2 6.20 sql(索引) 3 6.21 sql(软件项目流程) 3 ...7.9存储过程、游标、触发器 27 7.11 函数 32 7.12数据库管理备份与恢复,数据导入与导出 35 7.13数据库的权限设置 35
1.涉及数据库表、约束定义、数据更新、存储过程、视图、索引及触发器。 2.数据库表有三张:车票信息表、汽车信息表及线路信息表,每张表都有写入数据脚本,执行后数据就有了测试数据。 3.包含统计成人票数量及计算...
SQL Server 2000概述、SQL Server 2000安装和配置、SQL Server 2000工具、数据库系统基础、SQL Server 2000数据类型、SQL Server 2000数据库创建与管理、SQL Server 2000数据库表的创建和管理、SQL技术、Transact ...
数据库、登录、用户、模式、基表、视图、索引、序列、全文索引、存储过程和触发器 的定义和删除语句,登录、基表、视图、仝文索引的修改语句,对象的更名语句; 査询(含全文检索)、插入、删除、修改语句; 数据库安全...
数据库的基本查询语句,视图、数据库的基本使用方法。 涵盖大学数据库课程基本语法。
什么是数据库存储过程和触发器?它们的作用和使用场景是什么? 什么是数据库视图?它的作用和使用场景是什么? 什么是数据库分表和分库?为什么需要进行分表和分库操作? 什么是数据库锁?列举一些常见的数据库锁...
必要的存储过程和触发器设计都要写全说明,图可以是一个表的完整存储过程或触发器。 7.备份与恢复设计 根据系统需求作必要的备份与恢复设计,如需要对那些内容备份,备份策略、由谁来做备份、什么时间做备份等。 8...
数据库mysql、SQL server压缩包包含内容: 1.数据库的创建修改批量插入等基本操作及sql文件 2.数据表的约束、查询及sql文件 3.SQL server函数及sql文件 ...7.触发器 存储过程 索引 视图 知识点及sql文件 8.事务ACID理解