ATM
来源：互联网 发布：linux重启tomcat 编辑：程序博客网 时间：2024/04/27 22:31
import java.util.*;
import javax.sound.midi.SysexMessage;
public class Qukuan {
static String name="张三";
static String pwd="123";
static double money=10000;
int i;
//验证用户名密码
public void check()
{
for(int k=0;k<3;k++)
{
Scanner ss=new Scanner(System.in);
System.out.println("请输入您的帐号");
String a=ss.nextLine();
Scanner ss1=new Scanner(System.in);
System.out.println("请输入您的密码");
String b=ss.nextLine();
if(a.equals(name))
{
if(b.equals(pwd))
{
i=0;
caozuo();
break;
}
}
++i;
System.out.println("您的用户名或密码不正确");
System.out.println("您一共有三次机会,已经"+i+"次机会了");
}
}
//在用户名和密码都正确的情况下进入系统
public void caozuo()
{
System.out.println("*****************************");
System.out.println("*1.取款 2.查询余额*");
System.out.println("*3.转账 4.存款 *");
System.out.println("*5.修改密码 6.退出 *");
System.out.println("*****************************");
Scanner s=new Scanner(System.in);
System.out.println("请输入你要进行的操作数");
int i=s.nextInt();
switch (i)
{
case 1:
{
qukuan();
again();
break;
}
case 2:
{
chaxun();
again();
break;
}
case 3:
{
zhuanzhang();
again();
break;
}
case 4:
{
cunkuan();
again();
break;
}
case 5:
{
xiugaimima();
again();
break;
}
case 6:
{
System.exit(0);
}
default :
{
System.out.println("您只能输入1-6之间的数字,请重新输入:");
caozuo();
}
}
}
//定义取款的方法
public void qukuan()
{
Scanner s=new Scanner(System.in);
System.out.println("请输入您要取出的金额:");
double i=s.nextDouble();
if(i<=money)
{
this.money=this.money-i;
System.out.println("您的帐户余额为:"+this.money+"元,您取出的金额为:"+i+"元");
}
else
{
System.out.println("帐户余额不足");
}
}
//定义查询余额的方法
public void chaxun()
{
System.out.println("您账户上的余额为:"+this.money+"元");
}
//定义转账的方法
public void zhuanzhang()
{
Scanner s=new Scanner(System.in);
System.out.println("请输入您要转帐的金额:");
double i=s.nextDouble();
if(i<=money)
{
System.out.println("请输入您要转进的帐户的户名:");
Scanner s1=new Scanner(System.in);
String a=s1.nextLine();
System.out.println("请输入您要转进的帐号:");
Scanner s2=new Scanner(System.in);
String b=s1.nextLine();
System.out.println("您的钱款已转入:"+a+",卡号为:"+b+"的账户中");
this.money = this.money-i;
}
else
{
System.out.println("您输入的数值超出范围");
}
}
//定义存款的方法
public void cunkuan()
{
Scanner s=new Scanner(System.in);
System.out.println("请输入您要存入的金额:");
double i=s.nextDouble();
this.money = this.money+i;
System.out.println("您存入的金额为:"+i+"元");
}
//定义修改密码的方法
public void xiugaimima()
{
Scanner s=new Scanner(System.in);
System.out.println("请输入原始密码:");
String i=s.nextLine();
if(i.equals(pwd))
{
Scanner s1=new Scanner(System.in);
System.out.println("请输入您的新密码:");
String j=s1.nextLine();
Scanner s2=new Scanner(System.in);
System.out.println("请确认您的密码:");
String k=s2.nextLine();
if(j.equals(k))
{
pwd=j;
System.out.println("修改成功");
check();
}
else
{
System.out.println("两次密码不符");
}
}
else
{
System.out.println("密码错误");
}
}
//定义一个让用户再次输入的方法
public void again()
{
System.out.println("您还要继续操作吗？Y/N");
Scanner s=new Scanner(System.in);
String i=s.nextLine();
if(i.equalsIgnoreCase("Y"))
{
caozuo();
}
else
{
System.exit(0);
}
}
public static void main(String[] args) {
Qukuan z=new Qukuan();
z.check();
}
}
、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、
、
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import javax.swing.event.*;
import java.sql.*;
import java.net.URL;
import java.util.Date;
import java.lang.*;
public class DBAccess extends JFrame implements ActionListener{
private String strDBDriver="com.microsoft.jdbc.sqlserver.SQLServerDriver"; //JDBC驱动器名称
private String strUrl="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=soft";
//数据库的URL地址
private String strDBName="soft"; //指定访问的数据库名称
private String strUserName="sa"; //数据库用户名称
private String strPassWord="123456"; //数据库用户密码
private Connection conTemp=null; //数据库连接对象,初始化为空
private Statement stmtTemp=null; //连接的容器对象,初始化为空
private ResultSet rsTemp=null; //结果集对象,初始化为空
private int intOperateNum=0;
private String ATM_id="123456789"; //操作记录行数
public static void main(String[] args) { //主方法
DBAccess d=new DBAccess();
d.setSize(400,300);
d.setLocation(300,200);
d.setTitle("欢迎使用ATM自动取款机");
d.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
//d.pack();
d.show();
}
public DBAccess() //构造方法
{
try{
Class.forName(strDBDriver).newInstance();
}
catch(Exception e)
{ System.out.print("数据库驱动出错或不存在！");
e.printStackTrace();
}
showCardLayout();
}
public boolean createConnection(){ //创建与数据库的连接
boolean blConnect=false;
try{ conTemp=DriverManager.getConnection(strUrl,strUserName,strPassWord);
//stmtTemp=conTemp.createStatement
(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
stmtTemp=conTemp.createStatement
(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
// conTemp.setCatalog(strDBName);
//conTemp.setAutoCommit(false);
blConnect=true;}
catch(Exception e){
System.out.print("连接出错！");
e.printStackTrace();}
return blConnect;
}
public ResultSet executeQuery(String strSQL){ //查询
try{
if(conTemp==null)
{
createConnection();
}
stmtTemp=conTemp.createStatement();
rsTemp=stmtTemp.executeQuery(strSQL);
return rsTemp;
}
catch(Exception e)
{System.out.print("查询出错~~~~！");
e.printStackTrace();
return null;
}
}
public boolean executeUpdate(String strSQL){ //更新操作
try{
if(conTemp==null){
createConnection();
stmtTemp=conTemp.createStatement();
}
intOperateNum=stmtTemp.executeUpdate(strSQL);
return true;}
catch(Exception e){
System.out.print("更新出错！");
e.printStackTrace();
return false;
}
}
public int getDBOperateNum(){ //返回操作记录行数
return intOperateNum;
}
public void commit(){ //提交事务
try{
conTemp.commit();
}
catch(Exception e)
{System.out.print("事务提交出错！");
e.printStackTrace();
}
}
public void closeDBConnection(){ //关闭与数据库的连接
try{
if(rsTemp!=null)
{
rsTemp=null;
}
if(conTemp!=null)
{
conTemp=null;
}
if(stmtTemp!=null)
{stmtTemp=null;}
}
catch(Exception e){
System.out.print("数据库断开出错！");
e.printStackTrace();
}
}
public float getmoney(String getIC){ //查询余额
float mone=0;
DBAccess d=new DBAccess();
try {
if(conTemp==null){
createConnection();
stmtTemp=conTemp.createStatement();
}
String strSQLSelect="SELECT money FROM IC WHERE IC_ID='"+getIC+"'";
ResultSet rsSQLSelect=d.executeQuery(strSQLSelect);
if(rsSQLSelect.next())
{
mone=rsSQLSelect.getFloat("money");
d.commit();
}
}
catch(Exception e)
{
System.out.print("查询余额出错！");
}
return mone;
}
public boolean login (String IC_Num, String pword) //登录
{
DBAccess d=new DBAccess();
try{
if(conTemp==null){
createConnection();
stmtTemp=conTemp.createStatement();
}
String strSQLSelect="SELECT IC_pw from IC WHERE IC_ID='"+IC_Num+"'";
ResultSet rsSQLSelect=d.executeQuery(strSQLSelect);
if(rsSQLSelect.next())
{ String pwd=rsSQLSelect.getString("IC_pw");
d.commit();
if(pword.equals(pwd))
{
return true;
}
}
}
catch(Exception e)
{
System.out.print("登录出错！");
}
return false;
}
public boolean alterpassword(String pword,String IC_Num) //修改密码
{
DBAccess d=new DBAccess();
try{
if(conTemp==null){
createConnection();
stmtTemp=conTemp.createStatement();
}
}
catch(Exception e){}
String strSQLUpdate="update IC set IC_pw="+pword+" WHERE IC_ID='"+IC_Num+"'";
boolean bol=d.executeUpdate(strSQLUpdate);
d.commit();
return bol;
}
public boolean setmoney(float moneys,String IC_Num) //取款
{
DBAccess d=new DBAccess();
float m;
try{
if(conTemp==null){
createConnection();
stmtTemp=conTemp.createStatement();
}
}
catch(Exception e){}
m=getmoney(IC_Num);
if(m>moneys)
{
m=m-moneys;
String strSQLUpdate="UPDATE IC Set money="+m+"WHERE IC_ID='"+IC_Num+"'";
d.executeUpdate(strSQLUpdate);
d.commit();
return true;
}
else
return false;
}
public boolean getname(String IC_Num) //用户信息查询
{
String aname="";
DBAccess d=new DBAccess();
try{
if(conTemp==null){
createConnection();
stmtTemp=conTemp.createStatement();
}
String strSQLSelect="SELECT IC_ID FROM IC WHERE IC_ID='"+IC_Num+"'";
ResultSet rsSQLSelect=d.executeQuery(strSQLSelect);
if(rsSQLSelect.next())
{
aname=rsSQLSelect.getString("IC_ID");
if(IC_Num.equals(aname))
return true;
}
}
catch(Exception e)
{
System.out.print("用户信息查询出错！");
}
return false;
}
JLabel t1=new JLabel("欢迎使用ATM机");
JLabel t2=new JLabel("请输入您的卡号:");
JLabel t3=new JLabel("");
JLabel t4=new JLabel("请输入您的密码:");
JLabel t5=new JLabel("");
JLabel t6=new JLabel("请选择您要的服务:");
JLabel t7=new JLabel("请选择您的取款金额:");
JLabel t8=new JLabel("请输入取款金额:");
JLabel t9=new JLabel("");
JLabel t10=new JLabel("操作成功!");
JLabel t11=new JLabel("您的取款额为:");
JLabel t12=new JLabel("");
JLabel t13=new JLabel("操作流水帐号:");
JLabel t14=new JLabel("");
JLabel t15=new JLabel("IC卡号:");
JLabel t16=new JLabel("");
JLabel t17=new JLabel("取款额:");
JLabel t18=new JLabel("");
JLabel t19=new JLabel("时间:");
JLabel t20=new JLabel("");
JLabel t21=new JLabel("请输入转入的帐号:");
JLabel t22=new JLabel("输入转帐金额:");
JLabel t23=new JLabel("");
JLabel t24=new JLabel("您的帐号为:");
JLabel t25=new JLabel("您的余额为:");
JLabel t26=new JLabel("请输入新密码:");
JLabel t27=new JLabel("确认新密码:");
JLabel t28=new JLabel("");
JLabel t29=new JLabel("");
JLabel t30=new JLabel("");
JLabel t31=new JLabel("所属银行:");
JLabel t32=new JLabel("");
JLabel t33=new JLabel("");
JButton z1=new JButton("进入");
JButton z2=new JButton("关闭");
JButton z3=new JButton("确认");
JButton z4=new JButton("退卡");
JButton z5=new JButton("确认");
JButton z6=new JButton("退卡");
JButton z7=new JButton("取款");
JButton z8=new JButton("余额查询");
JButton z9=new JButton("转帐");
JButton z10=new JButton("修改密码");
JButton z11=new JButton("退卡");
JButton z12=new JButton("确认");
JButton z13=new JButton("返回");
JButton z14=new JButton("打印");
JButton z15=new JButton("返回");
JButton z16=new JButton("退卡");
JButton z17=new JButton("返回");
JButton z18=new JButton("退卡");
JButton z19=new JButton("确认");
JButton z20=new JButton("返回");
JButton z21=new JButton("返回");
JButton z22=new JButton("退卡");
JButton z23=new JButton("100");
JButton z24=new JButton("200");
JButton z25=new JButton("300");
JButton z26=new JButton("500");
JButton z27=new JButton("其它");
JButton z28=new JButton("确认");
JButton z29=new JButton("返回");
JButton z30=new JButton("返回");
JPanel p1=new JPanel(); //主界面
JPanel a1=new JPanel();
JPanel a2=new JPanel();
JPanel a3=new JPanel();
JPanel p2=new JPanel(); //帐号输入界面
JPanel b1=new JPanel();
JPanel b2=new JPanel();
JPanel b3=new JPanel();
JPanel b4=new JPanel();
JPanel p3=new JPanel(); //密码输入界面
JPanel c1=new JPanel();
JPanel c2=new JPanel();
JPanel c3=new JPanel();
JPanel c4=new JPanel();
JPanel p4=new JPanel(); //选择服务界面
JPanel d1=new JPanel();
JPanel d2=new JPanel();
JPanel d3=new JPanel();
JPanel d4=new JPanel();
JPanel p5=new JPanel(); //选择金额界面
JPanel y1=new JPanel();
JPanel y2=new JPanel();
JPanel y3=new JPanel();
JPanel y4=new JPanel();
JPanel y5=new JPanel();
JPanel p6=new JPanel(); //取款界面
JPanel e1=new JPanel();
JPanel e2=new JPanel();
JPanel e3=new JPanel();
JPanel e4=new JPanel();
JPanel p7=new JPanel(); //取款成功界面
JPanel f1=new JPanel();
JPanel f2=new JPanel();
JPanel f3=new JPanel();
JPanel p8=new JPanel(); //打印界面
JPanel g1=new JPanel();
JPanel g2=new JPanel();
JPanel g3=new JPanel();
JPanel g4=new JPanel();
JPanel g5=new JPanel();
JPanel p9=new JPanel(); //转帐界面
JPanel h1=new JPanel();
JPanel h2=new JPanel();
JPanel h3=new JPanel();
JPanel h4=new JPanel();
JPanel p10=new JPanel(); //余额查询
JPanel m1=new JPanel();
JPanel m2=new JPanel();
JPanel m3=new JPanel();
JPanel m4=new JPanel();
JPanel p11=new JPanel(); //修改密码
JPanel n1=new JPanel();
JPanel n2=new JPanel();
JPanel n3=new JPanel();
JPanel n4=new JPanel();
JTextField text1=new JTextField(10); //卡号输入
JTextField text2=new JTextField(6); //取款金额
JTextField text3=new JTextField(10); //转帐卡号输入
JTextField text4=new JTextField(6); //转帐金额输入
JTextArea te=new JTextArea(3,24);
JPasswordField pw1=new JPasswordField(6); //卡密码输入
JPasswordField pw2=new JPasswordField(6); //输入新密码
JPasswordField pw3=new JPasswordField(6); //确认新密码
Container c=this.getContentPane();
CardLayout cl=new CardLayout();
GridLayout gl=new GridLayout(5,1);
int fetchmoney=0;
long opNum=1203101;
int n=2; //密码错误次数控制
。。。。。。。。。。。。。。。。
--建库
use master
go
exec xp_cmdshell 'md d:/bank', no_output
if exists(select * from sys.databases where name='bankDB')
drop database bankDB
go
create database bankDB
on
(
name='bankDB_data',
filename='d:/bank/bankDB_data.mdf',
size=3mb,
filegrowth=15%
)
log on
(
name='bankDB_log',
filename='d:/bank/bankDB_log.ldf',
size=3mb,
filegrowth=15%
)
go
--建表
use bankDB
go
if exists(select * from sys.objects where name='userInfo' )
drop table userInfo
create table userInfo--用户信息表
(
customerID int identity(1,1),
customerName char(8) not null,
PID char(18) not null,
telephone char(13) not null,
address varchar(50)
)
go
if exists(select * from sys.objects where name='cardInfo')
drop table cardInfo
go
create table cardInfo--银行卡信息表
(
cardID char(19) not null,
curType char(5) 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 bit not null,
customerID int not null
)
go
if exists(select * from sys.objects where name='transInfo')
drop table transInfo--交易信息表
go
create table transInfo
(
transDate datetime not null,
transType char(4) not null,
cardID char(19) not null,
transMoney money not null,
remark text
)
go
--加约束
/* userInfo表的约束
customerID 顾客编号 自动编号（标识列），从1开始，主键
customerName 开户名 必填
PID 身份证号 必填，只能是18位或15位，身份证号唯一约束
telephone 联系电话 必填，格式为xxxx-xxxxxxxx或手机号13位
address 居住地址 可选输入
*/
alter table userInfo
add constraint PK_customerID primary key(customerID),
constraint CK_PID check(len(PID)=18 or len(PID)=15),
constraint UQ_PID unique(PID),
constraint CK_telephone check(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][0-9][0-9]'
or len(telephone)=13)
go
--exec sp_helpconstraint userInfo
/*cardInfo表的约束
cardID 卡号 必填，主健 , 银行的卡号规则和电话号码一样，一般前8位代表特殊含义，
如某总行某支行等。假定该行要求其营业厅的卡号格式为：1010 3576 xxxx xxx开始
curType 货币 必填，默认为RMB
savingType 存款种类 活期/定活两便/定期
openDate 开户日期 必填，默认为系统当前日期
openMoney 开户金额 必填，不低于1元
balance 余额 必填，不低于1元,否则将销户
pass 密码 必填，6位数字，默认为6个8
IsReportLoss 是否挂失 必填，是/否值，默认为”否”
customerID 顾客编号 必填，表示该卡对应的顾客编号，一位顾客可以办理多张卡
*/
alter table cardInfo
add constraint PK_cardID primary key(cardID),
constraint CK_cardID check(cardID like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'),
constraint DF_curType default('RMB') for curType,
constraint CK_savingType check(savingType in ('活期','定活两便','定期')),
constraint DF_openDate default(getDate()) for openDate,
constraint CK_openMoney check(openMoney>=1),
constraint CK_balance check(balance>=1),
constraint CK_pass check(pass like '[0-9][0-9][0-9][0-9][0-9][0-9]'),
constraint DF_pass default('888888') for pass,
constraint DF_IsReportLoss default(0) for IsReportLoss,
constraint FK_customerID foreign key(customerID) references userInfo(customerID)
go
--exec sp_helpconstraint cardInfo
/* transInfo表的约束
transType 必填，只能是存入/支取
cardID 卡号 必填，外健，可重复索引
transMoney 交易金额 必填，大于0
transDate 交易日期 必填，默认为系统当前日期
remark 备注 可选输入，其他说明
*/
alter table transInfo
add constraint CK_transType check(transType in('存入','支取')),
constraint FK_cardID foreign key(cardID) references cardInfo(cardID),
constraint CK_transMoney check(transMoney>0),
constraint DF_transDate default(getDate())for transDate
go
--exec sp_helpconstraint transInfo
--插入测试数据
/*
张三开户，身份证：123456789012345，电话：010-67898978，地址：北京海淀
开户金额：1000 活期 卡号：1010 3576 1234 5678
李四开户，身份证：321245678912345678，电话：0478-44443333，
开户金额： 1 定期 卡号：1010 3576 1212 1134
*/
set nocount on
insert into userInfo(customerName,PID,telephone,address)
values('张三','123456789012345','0010-67898978','北京海淀')
insert into cardInfo(cardID,savingType,openMoney,balance,customerID)
values('1010 3576 1234 5678','活期',1000,1000,1)
insert into userInfo(customerName,PID,telephone)
values('李四','321245678912345678','0478-44443333')
insert into cardInfo(cardID,savingType,openMoney,balance,customerID)
values('1010 3576 1212 1134','定期',1,1,2)
select * from userInfo
select * from cardInfo
go
/*
张三的卡号（1010 3576 1234 5678）取款900元，李四的卡号（1010 3576 1212 1134）存款5000元，要求保存
交易记录，以便客户查询和银行业务统计。
说明：当存钱或取钱（如300元）时候，会往交易信息表（transInfo）中添加一条交易记录，
同时应更新银行卡信息表（cardInfo）中的现有余额（如增加或减少500元）
*/
/*--------------交易信息表插入交易记录--------------------------*/
insert into transInfo(transType,cardID,transMoney)
values('支取','1010 3576 1234 5678',900)
/*-------------更新银行卡信息表中的现有余额-------------------*/
update cardInfo set balance=balance-900 where cardID='1010 3576 1234 5678'
/*--------------交易信息表插入交易记录--------------------------*/
insert into transInfo(transType,cardID,transMoney)
values('存入','1010 3576 1212 1134',5000)
/*-------------更新银行卡信息表中的现有余额-------------------*/
update cardInfo set balance=balance+5000 where cardID='1010 3576 1212 1134'
/*--------检查测试数据是否正确---------*/
select * from transInfo
select * from cardInfo
go
/*---------------------------常规操作-----------------------------*/
/*--------修改密码--------*/
use bankDB
go
--1.张三（卡号为1010 3576 1234 5678）修改银行卡密码为123456
--2.李四（卡号为1010 3576 1212 1134）修改银行卡密码为123123
update cardInfo set pass='123456' where cardID='1010 3576 1234 5678'
update cardInfo set pass='123123' where cardID='1010 3576 1212 1134'
select * from cardInfo
/*---------挂失帐号---------*/
--李四（卡号为1010 3576 1212 1134）因银行卡丢失，申请挂失
update cardInfo set IsReportLoss=1 where cardID='1010 3576 1212 1134'
select * from cardInfo
/*--------查询余额3000~6000之间的定期卡号,显示该卡相关信息-----------------*/
select * from cardInfo where (balance between 3000 and 6000) and (savingType='定期')
/*--------统计银行的资金流通余额和盈利结算------------------------------*/
--统计说明:存款代表资金流入,取款代表资金.假定存款利率为千分之3,贷款利率为千分之8
declare @inMoney money,@outMoney money
select @inMoney=sum(transMoney) from transInfo where transType='存入'
select @outMoney=sum(transMoney) from transInfo where transType='支取'
print '银行流通余额总计为：'+convert(varchar(20),@inMoney-@outMoney)+'RMB'
print '盈利结算为：'+convert(varchar(20),@outMoney*0.008-@inMoney*0.003)+'RMB'
go
/*--------查询本周开户的卡号,显示该卡相关信息-----------------*/
/*--DATEDIFF ( datepart , startdate , enddate )
datepart
指定对日期的哪一部分计算差异的参数
DATEPART ( datepart , date )
datepart
指定要返回的日期部分的参数
*/
select * from cardInfo where datediff(day,getdate(),openDate)<datepart(weekday,openDate)
/*---------查询本月交易金额最高的卡号----------------------*/
select distinct cardID from transInfo where transMoney in (select max(transMoney) from transInfo)
/*---------查询挂失帐号的客户信息---------------------*/
select 客户姓名=customerName,telephone as 联系电话 from userInfo where customerID=(select
customerID from cardInfo where IsReportLoss=1)
/*------催款提醒：例如某种业务的需要，每个月末，如果发现用户帐上余额少于200元，将致电催款。---*/
select customerName as 客户姓名 , telephone as 联系电话, balance as 账上余额 from userInfo inner
join cardInfo on userInfo.customerID=cardInfo.customerID
where balance<200
/*-------------------------------------索引和视图-----------------------------------------------
----*/
--1.创建索引：给交易表的卡号cardID字段创建重复索引
create nonclustered index index_cardID on transInfo(cardID) with fillfactor=70
go
--2.按指定索引查询 张三（卡号为1010 3576 1212 1134）的交易记录
--错误一，要调用指定字段的索引，要加上with关键字
SELECT * FROM transInfo with(INDEX=index_cardID) WHERE cardID='1010 3576 1234 5678'
GO
--3.创建视图：为了向客户显示信息友好,查询各表要求字段全为中文字段名。
create view view_userInfo
as
select customerID as 客户编号,customerName as 开户,身份证=PID,电话号码=telephone,居住地址=address
from userInfo
go
create view view_cardInfo
as
select 卡号=cardID,货币类型=curType,存款类型=savingType,开户日期=openDate,余额=balance,密码=pass,
是否挂失=IsReportLoss,客户编号=customerID
from cardInfo
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
/*------------------------------------------存储过程--------------------------------------------
----------*/
--1.取钱或存钱的存储过程
if exists(select * from sys.objects where name='proc_takeMoney' )
drop procedure proc_takeMoney
go
create procedure proc_takeMoney
@card char(19),
@m money,
@type char(4),
@inputPass char(6)=''
as
print '交易正在进行，请稍候.......'
if(@type='支取')
if((select pass from cardInfo where cardID=@card)<>@inputPass)
begin
raiserror ('密码错误',16,1)
return
end
else
declare @myTransType char(4),@outMoney money ,@myCardID char(19)
select @myTransType=transType,@outMoney=transMoney,@myCardID=cardID from transInfo where
cardID=@card
declare @mybalance money
select @mybalance=balance from cardInfo where cardID=@card
if(@type='支取')
begin
if(@mybalance>=@m+1)
update cardInfo set balance=balance-@m where cardID=@card
else
begin
raiserror('交易失败！余额不足！',16,1)
print '卡号'+@card+' 余额：'+convert(varchar(20),@mybalance)
return
end
end
else
update cardInfo set balance=balance+@m where cardID=@card
print '交易成功！交易金额为：'+convert(varchar(20),@m)
select @mybalance=balance from cardInfo where cardID=@card
print '卡号：'+@card+' '+'余额:'+convert(varchar(20),@mybalance)
go
--2.调用存储过程取钱或存钱 张三取30，李四存500
--现实中的取款机依靠读卡器读出张三的卡号,这里根据张三的名字查出卡号来模拟
declare @card char(19)
select @card=cardID from userInfo inner join cardInfo on userInfo.customerID=cardInfo.customerID
where customerName='张三'
exec proc_takeMoney @card,30,'支取','123456'
go
declare @card char(19)
select @card=cardID from userInfo inner join cardInfo on userInfo.customerID=cardInfo.customerID
where customerName='李四'
exec proc_takeMoney @card,500,'存入'
go
--select * from view_cardInfo
--select * from view_transInfo
/*----------------------------------触发器------------------------------------------------------
*/
--改进上述的存款或取款语句，当存钱或取钱（如500元）时候，会往交易信息表transInfo中添加一条交易记
录，同时会自动更新用户信息表：userInfo中的现有金额的变化（如增加/减少500元）
--drop trigger trig_trans
CREATE TRIGGER trig_trans ON transInfo FOR INSERT/*为插入数据在交易信息表中创建触发器*/
AS
DECLARE @myTransType char(4),@outMoney MONEY,@myCardID char(19)
SELECT @myTransType=transType,@outMoney=transMoney ,@myCardID=cardID FROM inserted
DECLARE @mybalance money
SELECT @mybalance=balance FROM cardInfo WHERE cardID=@myCardID
if (@myTransType='支取')
if (@mybalance>=@outMoney+1)//余额保证不少于1块，否则认为销户。
update cardInfo set balance=balance-@outMoney WHERE cardID=@myCardID
else
begin
raiserror ('交易失败！余额不足！',16,1)
rollback tran
print '卡号'+@myCardID+' 余额：'+convert(varchar(20),@mybalance)
--return
end
else
update cardInfo set balance=balance+@outMoney WHERE cardID=@myCardID
print '交易成功！交易金额：'+convert(varchar(20),@outMoney)
SELECT @mybalance=balance FROM cardInfo WHERE cardID=@myCardID
print '卡号'+@myCardID+' 余额：'+convert(varchar(20),@mybalance)
GO
--测试触发器：张三的卡号支取1000，李四的卡号存入200
--现实中的取款机依靠读卡器读出张三的卡号,这里根据张三的名字查出考号来模拟
declare @card char(19)
select @card=cardID from cardInfo Inner Join userInfo ON
cardInfo.customerID=userInfo.customerID where customerName='张三'
INSERT INTO transInfo(transType,cardID,transMoney) VALUES('支取',@card,1000)
GO
declare @card char(19)
select @card=cardID from cardInfo Inner Join userInfo ON
cardInfo.customerID=userInfo.customerID where customerName='李四'
INSERT INTO transInfo(transType,cardID,transMoney) VALUES('存入',@card,200)
GO
--3.产生随机卡号的存储过程(一般用当前月份数/当前秒数/当前毫秒数乘以一定的系数作为随机种子)
create procedure proc_randCardID
@randCardID char(19) output
as
declare @r numeric(15,8)--15位数，保留8位小数
declare @tempStr char(10)
SELECT @r=RAND((DATEPART(mm, GETDATE()) * 100000 )+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
set @tempStr=convert(char(10),@r)--产生0.xxxxxxxx的数字,我们需要小数点后的八位数字
set @randCardID='1010 3576'+' '+substring(@tempStr,3,4)+' '+substring(@tempStr,7,4)--组合为规定格
式的卡号
go
--4.测试产生随机卡号
declare @myCardID char(19)
exec proc_randCardID @myCardID output
print '产生的随机卡号为：'+convert(char(19),@myCardID)
go
/*演示随机数和子字符串
DECLARE @C CHAR(10)
SET @C='1234567890'
select RAND()
SELECT SUBSTRING(@C,3,4)
*/
--5.开户的存储过程
create procedure proc_openAccount
@customerName char(8),
@PID char(18),
@telephone char(13),
@openMoney money,
@savingType char(8),
@address varchar(50)=' '
as
declare @myCardID char(19),@cur_customerID int
--调用产生随机卡号的存储过程获得随机卡号
exec proc_randCardID @myCardID output
while exists(select * from cardInfo where cardID=@myCardID)
execute proc_randCardID @myCardID output
print '尊敬的客户,开户成功!系统为您产生的随机卡号为:'+@mycardID
print '开户日期'+convert(char(10),getdate(),111)+' 开户金额:'+convert(varchar(20),@openMoney)
if not exists(select * from userInfo where PID=@PID)
insert into userInfo(customerName,PID,telephone,address)
values(@customerName,@PID,@telephone,@address)
select @cur_customerID=customerID from userInfo where PID=@PID
insert into cardInfo(cardID,savingType,openMoney,balance,customerID)
values(@myCardID,@savingType,@openMoney,@openMoney,@cur_customerID)
go
--6.调用存储过程重新开户
EXEC proc_openAccount '王五','334456889012678','2222-63598978',1000,'活期','河南新乡'
EXEC proc_openAccount '赵二','213445678912342222','0760-44446666',1,'定期'
select * from view_userInfo
select * from view_cardInfo
GO
/*------------------------------------------事 务---------------------------------------------
--*/
--1.转帐的事务存储过程
create procedure proc_transer @card1 char(19),@card2 char(19),@outMoney money
as
begin transaction
print '转账开始，请稍候......'
declare @error int
set @error=0
exec proc_takeMoney @card1,@outMoney,'支取','123123'
set @error=@error+@@error
exec proc_takeMoney @card2,@outMoney,'存入'
set @error=@error+@@error
if @error<>0
begin
print '转账失败'
rollback transaction
end
else
begin
print '转账成功'
commit transaction
end
go
--2.测试上述事务存储过程
--从李四的帐户转帐2000到张三的帐户
--同上一样,现实中的取款机依靠读卡器读出张三/李四的卡号,这里根据张三/李四的名字查出考号来模拟
declare @card1 char(19),@card2 char(19)
select @card1=cardID from userInfo inner join cardInfo
on userInfo.customerID=cardInfo.customerID
where customerName='李四'
select @card2=cardID from userInfo Inner Join cardInfo
on cardInfo.customerID=userInfo.customerID
where customerName='张三'
--调用上述事务过程转帐
exec proc_transer @card1,@card2,2000
/*---------------------------------------安 全----------------------------------------------*/
--1.添加SQL登录帐号
if not exists(select * from master.dbo.syslogins where loginname='sysAdmins')
begin
exec sp_addlogin 'sysAdmins','1234'--添加SQL登录帐号
exec sp_defaultdb 'sysAdmins','bankDB'--修改登录的默认数据库为bankDB
end
go
--2.创建数据库用户
exec sp_grantdbaccess 'sysAdmins','sysAdminDBUser'
go
--3.--------给数据库用户授权
--为sysAdminDBUser分配对象权限(增删改查的权限)
grant select,insert,update,delete,select on transInfo to sysAdminDBUser
grant select,insert,update,delete,select on userInfo to sysAdminDBUser
grant select,insert,update,delete,select on cardInfo to sysAdminDBUser
