时间：2021-07-01 10:21:17 帮助过：32人阅读
步骤一：创建表空间,创建用户【用户默认操作新建的表空间】,创建表，并为各表添加约束
用户表：用户ID，姓名，身份证，联系电话，联系地址
要求：
银行卡信息表：卡号，币种（人民币），存储类型（定期，活期），开户时间，开户金额，当前账目余额，密码，是否挂失，用户的ID
要求：
交易信息表：交易日期，卡号，交易类型，交易金额，备注
要求：
注意：对于三张表都要创建公有同义词，便于以后的查询。
测试：分别添加两条用户表，银行卡信息表
步骤二：创建视图,要求如下：
1.创建 用户表表视图
2.创建银行卡信息表视图
3.创建交易信息表视图
4.创建查询挂失的客户信息视图
5.创建 查询本周开户的卡号 显示相关信息视图
6.创建 查询本月交易金额最高的卡号 的视图
测试：分别测试上面个视图是否显示正常
步骤三：创建触发器，要求如下：
1.不允许修改卡号：当更新的是银行卡信息表的卡号时提示“此列不允许修改”
2.当交易信息表有插入或者修改记录，银行卡信息表跟随改变：如交易信息表新增一条支取，那么对应的银行信息卡余额应该减少对应支取的金额。同理如交易信息表新增一条存入，那么对应的银行信息卡余额应该增加对应存入的金额。注意：如果支取先判断余额，不足则提示。如交易成功打印恭喜信息。
测试：分别针对交易信息表做支取和存入的操作。
步骤四：创建针对用户信息的程序包及程序包主体内容
测试：执行开户的存储过程，执行修改密码的存储过程，执行挂失的存储过程
步骤五：创建针对银行卡信息表的程序包及程序包主体内容
测试：执行取钱和存钱的存储过程，执行查询余额的存储过程，执行转账的存储过程，执行汇总的存储过程，执行销户的存储过程
实现:
1 /*--------------------------------------------- 2 *ATM项目练习--表空间-用户管理 3 *站在西瓜上的猪 4 *操作用户:scott 5 *2017/09/25 08:50 6 -----------------------------------------------*/ 7 --创建表空间 8 CREATE TABLESPACE ATM_TEST 9 Datafile ‘F:\tablespace\ATM_TEST.DBF‘ 10 size 1M 11 autoextend on 12 next 5m maxsize 100m ; 13 --创建表用户 14 CREATE USER atm_dba IDENTIFIED BY 123456 DEFAULT TABLESPACE ATM_TEST; 15 --用户授权给予开发者角色,允许创建用户,允许权限传递 16 GRANT RESOURCE,CREATE user,CREATE SESSION TO atm_dba WITH ADMIN OPTION; 17 --查看用户权限 18 select * from role_sys_privs; 19 select * from user_sys_privs; 20 select * from user_tab_privs;
1 /*--------------------------------------------- 2 *ATM项目练习--表,视图管理 3 *站在西瓜上的猪 4 操作用户:atm_dba 5 *2017/09/25 09:10 6 -----------------------------------------------*/ 7 --用户表：用户ID，姓名，身份证，联系电话，联系地址 8 /*------------------------------------ 9 *要求： 10 *1. 用户ID为主键 11 *2. 身份证号码唯一，15或18位 12 *3. 为用户ID创建序列，用于用户ID自增 13 -------------------------------------*/ 14 create TABLE user_atm( 15 u_id NUMBER NOT NULL PRIMARY KEY , 16 u_name varchar2(20) NOT NULL, 17 u_card_Num varchar2(18) NOT NULL, 18 u_contact_num varchar2(12) NOT NULL, 19 u_contact_address varchar2(50) , 20 --创建唯一索引 21 CONSTRAINT u_card_num_unique UNIQUE(u_card_Num) 22 )TABLESPACE ATM_TEST; 23 24 --银行卡信息表：卡号，币种（人民币），存储类型（定期，活期），开户时间，开户金额，当前账目余额，密码，是否挂失，用户的ID 25 /*要求： 26 27 1. 卡号主键，卡号必须是0-9的数字，格式xxxx xxxx xxxx xxxx以1010 3576开头 28 2. 存储类型只能是下面三种之一：活期，定期，活定两便 29 3. 开户金额必须大于1元 30 4. 密码必须大于6位 31 5. 是否挂失只能是下面两种之一：是，否 32 6. 用户的ID是外键，关联用户表的主键。 33 7. 币种默认”RMB” 34 8. 开户时间默认当前时间 35 9. 密码默认888888 36 10. 是否挂失默认：否 37 11. 根据卡号创建银行卡信息表的索引优化表查询。 38 */ 39 --Bank card 40 /*Card number, currency (RMB), storage type (regular, current account), 41 account opening time, account opening amount, current account balance, 42 password, whether to report loss, user ID*/ 43 44 create TABLE Bank_card_atm( 45 card_id CHAR(19) NOT NULL PRIMARY Key, 46 card_currency varchar2(20) DEFAULT‘RMB‘ NOT null , 47 card_STORAGE varchar2(10) DEFAULT‘活期‘ NOT NULL, 48 card_ao_time DATE DEFAULT SYSDATE NOT NULL, 49 card_ao_amount NUMBER DEFAULT 1.1 NOT NULL, 50 card_current_balance NUMBER NOT NULL, 51 card_PASSWORD varchar2(20) DEFAULT ‘888888‘ NOT NULL, 52 card_report_loss varchar2(2) DEFAULT ‘否‘ NOT NULL, 53 u_id NUMBER NOT NULL, 54 CONSTRAINT card_id_check Check(REGEXP_LIKE(card_id,‘^(1010 3576) \d{4} \d{4}$‘)), 55 CONSTRAINT card_STORAGE_check CHECK(card_STORAGE IN(‘活期‘,‘定期‘,‘活定两便‘)), 56 CONSTRAINT card_ao_amount_check CHECK(card_ao_amount>=1), 57 CONSTRAINT card_PASSWORD_check CHECK(REGEXP_LIKE(card_PASSWORD,‘^[[:alnum:]]{6,20}$‘)), 58 CONSTRAINT bc_u_fk FOREIGN KEY (u_id) REFERENCES user_atm(u_id) ON DELETE CASCADE 59 )TABLESPACE ATM_TEST; 60 61 62 --交易信息表：交易日期，卡号，交易类型，交易金额，备注 63 /*要求： 64 1. 卡号为外键，关联银行卡信息表主键 65 2. 交易类型只能是下面两种之一：存入，支取 66 3. 交易金额必须大于0 67 4. 交易时间默认为当前时间*/ 68 create TABLE Transaction_information_atm( 69 Tr_DATE DATE DEFAULT SYSDATE NOT NULL, 70 card_id CHAR(19) NOT null, 71 Tr_type varchar2(4) DEFAULT ‘存入‘ NOT NULL, 72 Tr_amount NUMBER DEFAULT 0.1 NOT NULL, 73 Tr_remark varchar2(100), 74 CONSTRAINT tri_c_fk FOREIGN KEY (card_id) REFERENCES Bank_card_atm(card_id) ON DELETE CASCADE, 75 CONSTRAINT Tr_type CHECK(Tr_type IN (‘存入‘,‘支出‘)), 76 CONSTRAINT Tr_amount_check CHECK(Tr_amount>=0) 77 )TABLESPACE ATM_TEST; 78 79 80 81 /*DROP TABLE ATM_TEST.user_atm; 82 DROP TABLE Bank_card_atm ; 83 DROP TABLE Transaction_information_atm;*/ 84 select * from user_atm 85 select * FROM atmsuer; 86 select * FROM atmbc; 87 SELECT * FROM atmtrin; 88 89 INSERT INTO ATMSUER 90 VALUES 91 (U_ID_SEQ.NEXTVAL, ‘骆武辉‘, ‘450921199601042456‘, ‘18877572911‘, ‘广西玉林‘); 92 INSERT INTO ATMBC 93 VALUES 94 (‘1010 3576 4000 0000‘, ‘RMB‘, ‘活期‘, SYSDATE, 200, 200, ‘12345678‘, ‘否‘, 2); 95 INSERT INTO ATMBC 96 VALUES 97 (‘1010 3576 4000 5856‘, ‘RMB‘, ‘活期‘, SYSDATE, 200, 200, ‘12345dashg678‘, ‘否‘, 2); 98 INSERT INTO ATMBC 99 (CARD_ID, U_ID) 100 VALUES 101 (‘1010 3576 4000 5890‘, 3); 102 103 INSERT INTO atmtrin 104 VALUES 105 (SYSDATE,‘1010 3576 4000 5890‘,‘支出‘,300,‘test‘); 106 107 108 --1.创建 用户表表视图 109 CREATE or replace VIEW user_view_atm AS SELECT * FROM user_atm; 110 SELECT * FROM user_view_atm; 111 --2.创建银行卡信息表视图 112 CREATE or replace VIEW bc_view_atm AS SELECT * FROM Bank_card_atm; 113 SELECT * FROM bc_view_atm; 114 --3.创建交易信息表视图 115 CREATE or replace VIEW trin_view_atm AS SELECT * FROM Transaction_information_atm; 116 SELECT * FROM trin_view_atm; 117 --4.创建查询挂失的客户信息视图 118 CREATE or replace VIEW report_loss_atm AS SELECT user_atm.*,Bank_card_atm.Card_Id,Bank_card_atm.Card_Current_Balance FROM Bank_card_atm,user_atm WHERE Bank_card_atm.Card_Report_Loss=‘是‘ AND Bank_card_atm.u_Id=user_atm.u_id; 119 SELECT * FROM report_loss_atm; 120 --5.创建 查询本周开户的卡号 显示相关信息视图 121 CREATE or replace VIEW bc_iw_atm as 122 select * FROM Bank_card_atm where to_char(card_ao_time,‘iw‘)=to_char(sysdate,‘iw‘) ; 123 SELECT * FROM bc_iw_atm; 124 --6.创建 查询本月交易金额最高的卡号 的视图 125 CREATE or replace VIEW bc_mmmax_atm AS 126 SELECT * 127 FROM BANK_CARD_ATM, 128 (SELECT B.CARD_ID CID 129 FROM BANK_CARD_ATM B, 130 (SELECT CARD_ID, 131 SUM(TR_AMOUNT) SUMM 132 FROM TRANSACTION_INFORMATION_ATM 133 WHERE TO_CHAR(TR_DATE, ‘mm‘) = TO_CHAR(SYSDATE, ‘mm‘) 134 GROUP BY CARD_ID) SS 135 WHERE B.CARD_ID = SS.CARD_ID 136 ORDER BY SS.SUMM DESC) TT 137 WHERE ROWNUM = 1 138 AND BANK_CARD_ATM.CARD_ID = TT.CID; 139 --测试 140 SELECT * FROM bc_mmmax_atm; 141 --创建同义词 142 --表同义词 143 CREATE OR REPLACE SYNONYM atmsuer FOR user_atm; 144 CREATE OR REPLACE SYNONYM atmbc FOR Bank_card_atm; 145 CREATE OR REPLACE SYNONYM atmtrin FOR Transaction_information_atm; 146 --视图同义词 147 CREATE OR REPLACE SYNONYM atmviewuser FOR user_view_atm; 148 SELECT * FROM atmviewuser; 149 CREATE OR REPLACE SYNONYM atmviewbcmmmax FOR bc_mmmax_atm; 150 CREATE OR REPLACE SYNONYM atmviewbc FOR bc_view_atm; 151 CREATE OR REPLACE SYNONYM atmviewtrin FOR trin_view_atm; 152 CREATE OR REPLACE SYNONYM atmviewrl FOR report_loss_atm; 153 CREATE OR REPLACE SYNONYM atmviewbciw FOR bc_iw_atm; 154 SELECT * FROM USER_VIEWS;
1 /*--------------------------------------------- 2 *ATM项目练习--触发器,序列,函数 3 *站在西瓜上的猪 4 操作用户:atm_dba 5 *2017/09/25 09:10 6 -----------------------------------------------*/ 7 --序列 -------------------- S 8 --创建序列 用于u_id 自动递增 --------------------user 9 -- Create sequence 10 create sequence u_id_seq 11 start with 1 12 increment by 1 13 NOMINVALUE 14 NOMAXVALUE 15 cache 20; 16 --序列 -------------------- E 17 --触发器 -------------------S 18 --创建触发器 实现id自增长,检查身份证号码--------------------user BEFORE INSERT 19 CREATE OR REPLACE TRIGGER ATM_USER_TR 20 BEFORE INSERT ON USER_ATM 21 FOR EACH ROW 22 BEGIN 23 --判断长度 24 IF NOT (REGEXP_LIKE(:new.U_CARD_NUM, ‘^\d{15}$‘) OR REGEXP_LIKE(:new.U_CARD_NUM, ‘^\d{18}$‘)) 25 26 THEN 27 RAISE_APPLICATION_ERROR(-20001, ‘字符格式错误‘); 28 END IF; 29 --自增 30 IF :new.U_ID IS NULL 31 THEN 32 SELECT U_ID_SEQ.NEXTVAL 33 INTO :NEW.U_ID 34 FROM DUAL; 35 END IF; 36 END; 37 38 --BC BEFORE INSERT 39 CREATE OR REPLACE TRIGGER ATM_BC_TR 40 BEFORE INSERT ON Bank_card_atm 41 FOR EACH ROW 42 BEGIN 43 -- 44 IF :new.card_current_balance IS NULL 45 THEN 46 :new.card_current_balance:=:new.card_ao_amount; 47 END IF; 48 END; 49 --1.不允许修改卡号：当更新的是银行卡信息表的卡号时提示“此列不允许修改” 50 CREATE OR REPLACE TRIGGER ATM_BC_restrict_TR 51 BEFORE update ON Bank_card_atm 52 FOR EACH ROW 53 BEGIN 54 -- 55 IF updating(‘card_id‘) 56 THEN 57 RAISE_APPLICATION_ERROR(-20002, ‘此列不允许修改‘); 58 END IF; 59 END; 60 61 CREATE OR REPLACE TRIGGER ATM_BC_restrict_TR 62 BEFORE UPDATE OF card_id ON Bank_card_atm 63 FOR EACH ROW 64 BEGIN 65 -- 66 IF updating 67 THEN 68 RAISE_APPLICATION_ERROR(-20002, ‘此列不允许修改‘); 69 END IF; 70 END; 71 UPDATE Bank_card_atm SET card_id=500 ; 72 73 74 /*2.当交易信息表有插入或者修改记录，银行卡信息表跟随改变： 75 如交易信息表新增一条支取，那么对应的银行信息卡余额应该减少对应支取的金额。 76 同理如交易信息表新增一条存入，那么对应的银行信息卡余额应该增加对应存入的金额。 77 注意：如果支取先判断余额，不足则提示。如交易成功打印恭喜信息。 78 */ 79 CREATE OR REPLACE TRIGGER ATM_TRIN_BC_TR 80 BEFORE UPDATE OR INSERT ON TRANSACTION_INFORMATION_ATM 81 FOR EACH ROW 82 DECLARE 83 CUNUM NUMBER; 84 BEGIN 85 CASE 86 WHEN UPDATING THEN 87 IF :OLD.TR_AMOUNT <= :NEW.TR_AMOUNT 88 THEN 89 UPDATE BANK_CARD_ATM 90 SET CARD_CURRENT_BALANCE = CARD_CURRENT_BALANCE + 91 (:NEW.TR_AMOUNT - :OLD.TR_AMOUNT) 92 WHERE CARD_ID = :new.CARD_ID; 93 94 ELSE 95 UPDATE BANK_CARD_ATM 96 SET CARD_CURRENT_BALANCE = CARD_CURRENT_BALANCE - 97 (:OLD.TR_AMOUNT - :NEW.TR_AMOUNT) 98 WHERE CARD_ID = :new.CARD_ID; 99 100 END IF; 101 WHEN INSERTING THEN 102 103 IF :NEW.TR_TYPE = ‘存入‘ 104 THEN 105 UPDATE BANK_CARD_ATM 106 SET CARD_CURRENT_BALANCE = CARD_CURRENT_BALANCE +:NEW.TR_AMOUNT 107 WHERE CARD_ID =:NEW.CARD_ID; 108 DBMS_OUTPUT.PUT_LINE(‘恭喜存款成功!‘); 109 ELSE 110 IF CUNUM < :NEW.TR_AMOUNT 111 THEN 112 RAISE_APPLICATION_ERROR(-20003, ‘余额不足!你的余额为:‘ + CUNUM); 113 ELSE 114 UPDATE BANK_CARD_ATM 115 SET CARD_CURRENT_BALANCE = CARD_CURRENT_BALANCE - 116 :NEW.TR_AMOUNT 117 WHERE CARD_ID = :NEW.CARD_ID; 118 119 DBMS_OUTPUT.PUT_LINE(‘恭喜取款成功!‘); 120 END IF; 121 END IF; 122 END CASE; 123 124 EXCEPTION 125 126 WHEN OTHERS THEN 127 RAISE_APPLICATION_ERROR(-20003,‘未知错误‘); 128 ROLLBACK; 129 END; 130 131 --触发器 -------------------E 132 --函数,存储过程--s 133 -- 1. 创建函数：卡号随机生成，注意卡号的格式1010 3576 XXXX XXXX 134 CREATE OR REPLACE FUNCTION USER_UID_AUTOMATION_ATM RETURN VARCHAR2 AS 135 CARD_ID VARCHAR2(19) := ‘1010 3576 ‘; 136 BEGIN 137 CARD_ID := (CARD_ID, TO_CHAR(FLOOR(DBMS_RANDOM.VALUE * 10000))); 138 CARD_ID := CONCAT(CARD_ID, ‘ ‘); 139 CARD_ID := CONCAT(CARD_ID, TO_CHAR(FLOOR(DBMS_RANDOM.VALUE * 10000))); 140 RETURN CARD_ID; 141 END; 142 SELECT USER_UID_AUTOMATION_ATM FROM DUAL; 143 144 145 ---2. 创建开户的存储过程：注意卡号调用上面函数，开户过程包含插入信息到用户表及银行卡信息表 146 CREATE OR REPLACE PROCEDURE OPEN_ACCOUNTS_ATM(PNAME USER_ATM.U_NAME%TYPE, --用户名 147 PCARD USER_ATM.U_CARD_NUM%TYPE, --身份证号码 148 PCONTACT USER_ATM.U_CONTACT_NUM%TYPE, --联系电话 149 PCOADDRESS USER_ATM.U_CONTACT_ADDRESS%TYPE, --联系地址 150 PCARD_CU VARCHAR2, --币种 151 PCARD_ST VARCHAR2, --存款类型 152 PCARD_AM NUMBER, --金额 153 PCARD_PAW VARCHAR2, --密码 154