Oracle总结与ATM实战演示

Oracle总结

一.用户的有关操作。

• 创建用户

1	create user scott identified by 123456;

• 给用户分配权限

1 2 3

grant connect,resource to scott; grant create view to scott; grant create synonym to scott;

• 撤销用户权限

1 2 3

revoke connect,resource from  scott; revoke create view from scott; revoke create synonym from scott;

• 删除用户

1	drop user scott cascade;

• 修改用户密码

1 2 3 4 5 6 7

alter user scott identified by 123456;--命令修改   conn scott/123456 password;--命令可视化修改1   connect scott/123456 password;--命令可视化修改2

• 设置用户是否锁定

1 2	alter user scott account lock; alter user scott account unlock;

　　

二.表空间的有关操作。

• 创建表空间

1 2 3 4

create tablespace mysapce datafile 'D:a.ora' size 10M--绝对路径和大小 extent management local uniform size 1M;--每个分区的大小

• 扩展表空间

1 2	alter tablespace mysapce add datafile 'D:b.ora' size 10M;

• 为ORACLE对象指定表空间

1 2	create user space_text identified by 123456 account unlock default tablespace mysapce; --创建表、索引也可以指定表空间；一旦指定，表空间无法修改。

• 删除表空间

1	drop tablespace mysapce;

　　

三.DDL的有关操作。

• 表table

1 2 3 4 5 6 7 8 9 10 11

--创建员工表   CREATE TABLE EMP（   EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,     ENAME VARCHAR2(10),     JOB VARCHAR2(9),     MGR VARCHAR(10),    --上司     HIREDATE DATE,    --入职日期     SAL NUMBER(7,2),  --薪水     COMM NUMBER(7,2), --津贴     DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT   );

　　

1 2 3 4 5 6

--创建部门表  CREATE TABLE DEPT(  DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,    DNAME VARCHAR2(14) ,    LOC VARCHAR2(13) --地址  );

　　

1 2 3 4 5 6

--创建工资等级表   CREATE TABLE SALGRADE(   GRADE NUMBER, --等级     LOSAL NUMBER, --等级中最低的薪水     HISAL NUMBER  --等级中最高的薪水   );

　　

• 视图view

1 2 3 4 5 6 7 8

--为emp表的empno,ename,sal和dept表的dname和salgrade表的grade创建一个视图   create view emp_dept_salgrade   as   select e.empno,e.ename,e.sal,d.dname,s.grade from   emp e inner join dept d using(deptno)   inner join salgrade s on e.sal between s.losal and s.hisal;       select * from emp_dept_salgrade;--通过视图查询

• 序列sequence

1 2 3 4 5 6 7 8 9 10

--为员工表的EMPNO创建一个序列   create sequence emp_empno_seq   start with 1001   increment by  1   nomaxvalue   nocycle   cache 10;     select emp_empno_seq.currval from dual;<span style="color: #008000;">查询序列的当前值</span>   select emp_empno_seq.nextval from dual;<span style="color: #008000;">查询序列的下一个值</span>

• 同义词synonym

1 2 3 4 5 6

--为视图emp_dept_salgrade创建同义词   create synonym eds for  emp_dept_salgrade;     select * from eds;<span style="color: #008000;">通过视图的同义词来查询视图中的数据    </span>

• 触发器trigger

1 2 3 4 5 6 7

--为员工表的empno创建一个自动插入的触发器   create or replace trigger emp_empno_tri   before insert on emp   for each row   begin     :new.empno:=emp_empno_seq.nextval;<span style="color: #008000;">--语句级（for each row）触发器里面可以:new.列名来给进行操作。</span>   end;

• 存储过程procedure

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

--创建一个可以控制行数的乘法表的过程。 create or replace procedure nine_nine(nine_line in number) as begin   for  i in 1..nine_line loop     for j in 1..i loop       dbms_output.put(i||'*'||j||'='||i*j||'  ');     end loop;       dbms_output.put_line('');   end loop; end;   --调用这个乘法过程 set serveroutput on; execute nine_nine(9);

• 存储函数function

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

--创建一个求1!+2!+..+20!的值的存储函数 create or replace function one_tw return number as value_sum number:=0; value_loop number:=1; begin   for i in 1..20 loop     value_loop:=value_loop*i;     value_sum:=value_sum+value_loop;   end loop;   return value_sum; end;   select one_tw() from dual;<span style="color: #008000;">--调用函数</span>   <span style="color: #008000;">备注：存储函数的调用可以放在表达式的位置，即表达式在哪里成立，它就可以在哪里调用。</span>

• 事务rollback、commit、savepoint

三.常用的结构查询。

• 查询用户和用户的信息

1 2	select username,user_id,password,default_tablespace from dba_users; select * from dba_users;

• 查询用户所拥有的角色

1 2	select * from user_role_privs;--系统用户 select * from session_roles;--普通用户

• 查询用户的权限

1	select * from user_sys_privs;普通用户和系统用户都可以

• 查看表中列的字符长度和字节长度

1	select length(ename),lengthb(ename) from emp;

• 查询表的相关信息

1 2	SELECT table_name, tablespace_name, temporary FROM user_tables;

• 查询表中列的相关信息

1 2	SELECT table_name,column_name, data_type, data_length, data_precision, data_scale FROM user_tab_columns;

• 对表进行重命名

1	rename student to mystudent;

• 给表添加备注

1	comment on table student is '我的练习';

• 给表中列添加备注

1	comment on column student.sno is '学生号';

• 查看表和视图的备注信息

1	select * from user_tab_comments where table_name='STUDENT';

• 查看表和视图中列的备注信息

1	select * from user_col_comments where table_name='STUDENT';

• 查看表的结构

1	describe student;

• 截断表

1	truncate table student;

• 使用连接运算符

1 2	select empno||ename as employees from emp; select concat(empno,ename) as employees from emp;

• 查看表的约束信息

1	select * from user_constraints where table_name='EMP';

• 查看列的约束信息

1	select * from user_cons_columns where column_name='SNO';

• 查看序列的信息

1	select * from user_sequences where sequence_name='EMP_EMPNO_SEQ';

• 查看索引的信息

1	select * from user_indexes;

　　

• 查看视图的信息

1	select * from user_views;

• 查看同义词

1	select * from user_synonyms;

• 查看触发器

1	select * from user_triggers;

• 查看存储过程

1	select * from user_procedures;

四.DML的有关操作。

• 插入数据insert

1 2 3 4 5 6 7 8 9 10 11

--dept-- INSERT INTO DEPT select 10,'ACCOUNTING','NEW YORK' from dual union select 20,'RESEARCH','DALLAS' from dual union select 30,'SALES','CHICAGO' from dual union select 40,'OPERATIONS','BOSTON' from dual; commit;                                    <span style="color: #008000;">--使用Oracle中的多行插入方法，关键字union，select自己想要的数据，与dual伪表组建一个完整的结构。 </span>

　　

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

--emp-- INSERT INTO EMP(ename,job,mgr,hiredate,sal,comm,deptno) select 'SMITH','CLERK',1009,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20 from dual union select 'ALLEN','SALESMAN',1006,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30 from dual union select 'WARD','SALESMAN',1006,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30 from dual union select 'JONES','MANAGER',1009,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20 from dual union select 'MARTIN','SALESMAN',1006,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30 from dual union select 'BLAKE','MANAGER',1009,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30 from dual union select 'CLARK','MANAGER',1009,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10 from dual union select 'SCOTT','ANALYST',1004,to_date('13-10-87','dd-mm-rr')-85,3000,NULL,20 from dual union select 'KING','PRESIDENT',1007,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10 from dual union select 'TURNER','SALESMAN',1006,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30 from dual union select 'ADAMS','CLERK',1009,to_date('13-10-87', 'dd-mm-rr')-51,1100,NULL,20 from dual union select 'JAMES','CLERK',1009,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30 from dual union select 'FORD','ANALYST',1004,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20 from dual union select 'MILLER','CLERK',1004,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10 from dual; commit;                                 <span style="color: #008000;">--这里使用了触发器emp_empno_tri来自动插入emp表的empno员工编号</span>

　　

1 2 3 4 5 6 7

--salgrade-- INSERT INTO SALGRADE VALUES (1,700,1200); INSERT INTO SALGRADE VALUES (2,1200,1400); INSERT INTO SALGRADE VALUES (3,1400,2000); INSERT INTO SALGRADE VALUES (4,2000,3000); INSERT INTO SALGRADE VALUES (5,3000,9999); commit;

　　

• 更新数据update

1	update emp set sal=3000 where empno=1004;

• 删除数据delete

1	delete from emp where empno=1004;<span style="color: #008000;">--from可以省略</span>

• 查询数据select

　　　　查询数据是DML语句中最关键的部分，也是最难的部分，在这里有许多围绕scott用户的实例，都是稍微复杂一点的查询，简单的就没必要写了。

　　　　1.最常用。

1 2 3

select * from emp; select * from dept; select * from salgrade;

　　　　2.内部连接。

　　　　　　2-1.查询每个员工所在的部门,使用where连接.

1	select e.empno,e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

　　　　　　2-2.inner join on连接.

1	select e.empno,e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;

　　　　　　2-3.inner join using连接.

1	select e.empno,e.ename,d.dname from emp e inner join dept d using(deptno);

　　　　3.外部连接。

　　　　　　3-1.左外连接：例如：查询出部门的员工的情况（显示所有部门）.

1	select e.ename,d.dname from emp e left join dept d using(deptno);

　　　　　　3-2.右外连接用(+).

1	select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno(+);

　　　　　　3-3.右外连接：例如：查询出所有的员工的部门情况（显示了所有员工）.

1	select e.ename,d.dname from emp e right join dept d using(deptno);

　　　　　　3-4.右外连接用(+).

1	select e.ename,d.dname from emp e,dept d where e.deptno(+)=d.deptno;

　　　　4.自连接。　　

　　　　　　4-1.查询出员工及他的上级。

1 2 3

select a.ename as 员工,b.ename as 上级 from emp a ,emp b where a.mgr=b.empno;    select a.ename as 上级,b.ename as 上级 from emp a inner join emp b on a.mgr=b.empno;

　　　　5.子查询。

　　　　　　5-1.查询工资高于平均工资的员工信息.

1	select * from emp where sal>(select avg(sal) from emp);

　　　　　　5-2.使用ANY查询任意满足工资低于最低档工资的员工信息.

1	select * from emp where sal<any(select losal from salgrade);

　　　　　　5-3.查询所有员工所属部门.

1	select dname from (select distinct dname from dept);

　　　　　　5-4.查询满足大于每个部门的最低工资的员工信息.

1	select * from emp where sal>all(select min(sal) from emp group by deptno);

　　　　　　5-5.查询出每个部门中，高出本部门平均工资的员工的雇员号和姓名.

1 2	select empno as 雇员号,ename as 姓名 from emp outer where sal>   (select avg(sal) from emp inner where inner.deptno=outer.deptno );

　　　　　　5-6.查询不在部门10的员工信息:注意子查询中的1，由于只关心子查询是否返回TRUE值，使用1可以提高查询的效率.

　　　　　　　　5-6.1.EXISTS子查询效率高于IN子查询.

1	select * from emp a where not exists (select 1 from emp b where a.deptno=10);

　　　　　　　　5-6.2.in的效率低，但比较好理解.

1	select * from emp where deptno not in 10;

　　　　　　5-7.查询emp表中可以管理别的员工的员工.

1	select ename  from emp a  where exists(select ename  from emp b where a.empno=b.mgr);

　　　　　　5-8.删除中部门重复行.

1	delete emp where rowid not in (select min(rowid) from emp group by deptno);

　　　　　　5-9.查找emp表第6-10条记录.

1 2	select * from(select rownum m,ename,sal,deptno from emp where rownum<=10) where m>5;

ATM取款机的数据库模拟开发和实战总结

一.ATM实战开发的简介。

　学习了几天的Oracle，开始着手用数据库PL/SQL语言做一个简单的ATM取款机业务，主要是为了巩固数据库的知识，并非真正的去实现高端的业务。有兴趣的可以看看，希望对同胞们都有用。

• 　ATM的表。它有四张表，用户信息表userinfo，卡号信息表cardinfo，交易信息表tradeinfo，存款类型表deposit。

用户信息表userInfo
customerID	int	客户编号	主键
customerName	varchar(10)	用户名	not null
personID	varcahr(20)	身份证号	not null unique 只能是15位 或者18位符合实际的身份证号
telephone	varcahr(20)	联系电话	not null，格式为xxxx-xxxxxxxx或者xxx-xxxxxxxx 或者11手机号
address	varchar(30)	居住地址	可选

 

 

 

 

 

 

 

 

 

1 2 3 4 5 6 7 8 9 10 11 12 13

--创建userinfo表-- create table UserInfo(   customerID int primary key,   customerName varchar(10) not null,   personID varchar(20) not null unique,   telephone varchar(20) not null,   address varchar(30) ); --为身份证号和电话增加正则表达式约束-- alter table userinfo add constraint CK_TELEPHONE check(regexp_like(telephone,'^1[3,4,5,7,8][0-9]-[0-9]{8}$|^[0-9]{3,4}-[0-9]{8}$')); alter table userinfo add constraint CK_PERSONID check(regexp_like(personid,'^[0-9]{15}$|^[0-9]{17}[0-9,x]$'));

 

卡号信息表cardInfo
cardID	varchar(30)	卡号	主键，如1010 3576 xxxx xxxx， 每4位后面有空格，卡号随机产生。
curID	varchar(5)	货币种类	必填，默认为RMB
savingID	varchar(5)	存款类型	外键，必填。
openDate	date	开户日期	必填。默认为当前时间
openMoney	decimal(10,2)	开户金额	必填，不低于1.
balance	decimal(10,2)	余额	必填，不低于1.
pwd	varchar(10)	密码	必填，6位数字。默认为888888
isReportLoss	char(2)	是否挂失	必填，只能是'是'或'否'。默认为'否'

 

 

 

 

 

 

 

 

 

 

 

 

　　customerid　　　　int　　　　开户编号　　　　　　　　外键，必填。

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

--创建cardinfo表-- create table CardInfo(   cardID varchar(30)  primary key,   curID varchar(5) default 'RMB' not null,   savingID varchar(5) not null,   openDate date default sysdate not null,   openMoney decimal(10,2) not null check(openMoney>=1),   balance decimal(10,2) not null check(balance>=1),   pwd varchar(10) default '888888' not null ,   IsReportLoss char(2) default '否' not null,   customerID int not null references UserInfo(customerID) ); --为卡号和密码增加正则表达式约束-- alter table cardinfo add constraint CK_PWD check (regexp_like(pwd,'^[0-9]{6}$'));   alter table cardinfo add constraint CK_CARDID check (regexp_like(cardid,'^1010[[:space:]]3576[[:space:]][0-9]{4}[[:space:]][0-9]{4}$'));

 

交易信息表tradeInfo
transdate	date	交易日期	必填。默认为系统时间
cardID	varchar(30)	卡号	外键，必填。
transType	varchar(10)	交易类型	必填，只能是存入或者支取
transmoney	decimal(10,2)	交易金额	必填，大于0
remark	varchar(50)	备注	可选

 

 

 

 

 

  

1 2 3 4 5 6 7 8

--创建tradeinfo表-- create table TradeInfo(   transDate date default sysdate not null,   cardID varchar(30) not null references CardInfo(cardID),   transType varchar(10) not null,   transMoney decimal(10,2) not null,   remark varchar(50) );

　　--为transtype增加约束--
　　alter table tradeinfo add constraint CK_TRANSTYPE check (transtype in('支取','存入'));

存款类型表deposit
savingID	int	类型编号	主键
savingName	varchar（20）	存款类型名称	not null unique

 

 

 

•  ATM模拟实现的业务。

　　　1.修改密码。

　　　2.挂失。

　　　3.查询本周开户的卡号。

　　　4.查询本月一次性交易金额最高的卡号。

　　　5.查询卡号挂失的用户的信息。

　　　6.开户。

　　　7.存款或者取款。

 

二.插入数据。

• 　　　为deposit表插入数据。

　　　插入数据前我们应该有这样一个认识，与客户无关的信息表先插入数据，与客户有关的次之。因为你开户的时候，客户存款类型必须与存款类型表deposit表的一条记录匹配才行。这就像一个银行一样，不管有多少客户，你银行本身的数据以及功能是必须有的。所以，先插入与银行的信息有关与客户无关的表的数据。

　　　为deposit插入数据的时候，存款类型编号我们一般也不会自己去输入，所以要创建一个插入类型名称时，自动插入类型编号的触发器。

　　　　首先创建一个savingid的序列。

1 2 3 4 5 6 7

--创建savingid序列-- create  sequence savingid_incr --创建序列不能加or replace start with 1 increment by 1 nomaxvalue nocycle cache 30;

　　　　然后创建savingid的触发器。

1 2 3 4 5 6 7 8

--创建savingid的触发器-- create or replace trigger savingid_insert before insert on deposit for each row declare begin   :new.savingid:=savingid_incr.nextval; end;

　　　　现在就可以插入数据了。

1 2 3

insert into deposit(savingname) values('定期'); insert into deposit(savingname) values('活期期'); insert into deposit(savingname) values('定活两便');

　　　　检测数据。select * from deposit;可以看到三条savingid自动产生并插入的记录。

• 　　　为userinfo表和cardinfo表插入数据：

　　　　　对于userinfo表，在插入数据的时候，我们不可能每次都去插入客户编号，所以要为客户编号创建一个插入其他数据时的客户编号自动插入的触发器；还要为卡号创建一个随机产生的过程，并且开户的时候通过过程去插入，因为卡号是随机产生并且不能重复，所以我把这个判断写入了开户的业务逻辑中。

　　　　　在这里的话，我直接去实现开户，然后通过开户为userinfo表和cardinfo表插入数据，东西很多，我们一步一步来。

　　　　　先为customerid创建序列。

1 2 3 4 5 6

create  sequence id_incr --创建序列不能加or replace start with 1001 increment by 1 nomaxvalue nocycle cache 30;

　　　　　再为customerid创键触发器。

1 2 3 4 5 6 7 8

create or replace trigger id_insert before insert on userinfo for each row declare next_customerid  userinfo.customerid%type; begin   :new.customerid:=id_incr.nextval; end;

　　　　为cardid创建随机产生的过程。

1 2 3 4 5 6 7 8 9 10 11 12 13

create or replace procedure r_cardid(out_id out varchar2) as    r_num number;    front_id varchar2(4);    back_id varchar2(4);    real_id varchar2(20); begin   select lpad(trunc(dbms_random.value*100000000),8,0)into r_num from dual;   front_id:=to_char(substr(r_num,1,4));   back_id:=to_char(substr(r_num,5,4));   real_id:='1010 3576 '||front_id||' '||back_id;   out_id:=real_id; end;<br>

　　　开户的时候，除了customerid和cardid以及表给的默认值，其他都是与用户开户有关的信息。所以准备工作做好之后，我们就可以实现开户的业务了。

*****************************************************开户******************************************************

******开户的数据*********

　　开户时需要用户输入的有：

　　　　身份证号——personid
　　　　存款类型——savingid
　　　　存款金额——openmoney
　　　　本卡密码——pwd
　　　　（在开户时如果用户是第一次开户的话，就又需要的输入的有：）
　　　　姓名——customername
　　　　联系方式——telephone
　　　　地址——address

　　系统自动赋予的值有：
　　　　用户号——customerid（触发器触发）
　　　　卡号——cardid（调用r_cardid(outid)过程）

　　　　其他都为系统给的默认值。

******开户的存储过程逻辑******

　　　　1.先判断用户是不是第一个开卡，如果是，那么先创建用户信息，再开户。

　　　　2.用户创建之后或者用户已存在时，调用卡号随机产生的存储过程，产生一个随机产生的不重复的卡号。

　　　　3.卡号产生之后，判断用户输入的存款类型是否正确，正确，就可以开户了。不正确则撤销之前所有的操作并且提示开户终止。

　　　　4.开户就是插入一条符合逻辑的cardinfo记录，并且提示开户的最终信息。

******开户的存储过程************

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

create or replace procedure openAccount( temp_personid in userinfo.personid%type, temp_savingname in deposit.savingname%type, temp_openmoney in cardinfo.openmoney%type, temp_pwd in cardinfo.pwd%type, temp_customername in userinfo.customername%type, temp_telephone in userinfo.telephone%type, temp_address in userinfo.address%type) as   isnullpersonid userinfo.personid%type; --select into判断身份证号是否存在。   temp_cardid cardinfo.cardid%type; --select into判断新产生的卡号是否存在,并且在后来是要用的。   temp_savingid cardinfo.savingid%type;--select into 判断用户输入的存款类型是否可用。   temp_customerid userinfo.customerid%type; begin   begin   --判断用户是否存在   select personid into isnullpersonid from userinfo where personid=temp_personid;   exception   when no_data_found then     -----创建用户----     insert into userinfo(customername,personid,telephone,address)     values(temp_customername,temp_personid,temp_telephone,temp_address);   end;   begin     while 1=1 loop --产生一个唯一不重复的卡号       r_cardid(temp_cardid);     select cardid into temp_cardid from cardinfo where cardid=temp_cardid;     --如果没有找到，则证明新产生的卡号是唯一的，进入exception继续完成操作。     end loop;   exception     when no_data_found then     --来到这里说明产生的卡号是可用的，接下来就应该判断存款类型temp_savingid.     begin       select  savingid into temp_savingid from deposit where savingname=temp_savingname;       --如果存在，那么就可以开户了，如果不存在，则撤销之前的所有操作，用事务。               --customerid是之前就有或者在开户中自动产生的，所以这里要通过SQL找到它。       select customerid into temp_customerid from userinfo where personid=temp_personid;       --开户---       insert into cardinfo(cardid,savingid,openmoney,balance,pwd,customerid)       values(temp_cardid,temp_savingid,temp_openmoney,temp_openmoney,temp_pwd,temp_customerid);       dbms_output.put_line('      开户成功！');       dbms_output.put_line('您的银行卡号为：'||temp_cardid);       dbms_output.put_line('开户日期：'||sysdate||'   开户金额 '||temp_openmoney);     exception       when no_data_found then       rollback;--撤销之前的所有操作       raise_application_error(-20000, '存款类型不正确，开户终止!');     end;   end; end;

***************利用开户存储过程来插入数据******************

插入一条用户第一次开户的数据：

set serveroutput on;
execute openAccount(410181199308084016,'定期',50000,762723,'徐万轩','151-03891462','河南省郑州市');

插入一条老用户开户的数据：

set serveroutput on;
execute openAccount(410181199308084016,'活期',50000,762723,'徐万轩','151-03891462','河南省郑州市');

此时查表就会发现有两条cardinfo记录，一条userinfo记录。

 

• 　　　为tradeinfo表插入数据。

　　　　tradeinfo表示记录交易信息的，所以我们可以调用存取款的过程来为tradeinfo表插入数据。

*****************************************************存取款存储过程*******************************************

******存取款需要用户的数据**********

　　需要用户输入的信息：

　　　　存款或者取款的金额：temp_money
　　　　存款或者取款的类型：temp_transtype
　　　　银行卡号：temp_cardid

******存取款的实现逻辑*************

　　1.首先判断用户输入的卡号是否存在，不存在则退出操作。

　　2.卡号存在，再判断卡是否挂失，如果挂失，提醒并退出操作。

　　3.没有挂失的话，判断存取款类型是否正确，如果正确，就可以存取款了。

　　4.存取款时，更新cardinfo表卡的balance余额，并且插入一条交易信息表。

　　5.如果取款之后，余额小于1，就会发生检查约束错误，捕获错误并且利用事务的原理rollback之前的操作。

*******存取款的存储过程**************

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

create or replace procedure inout_money( temp_money in number, temp_transtype in tradeinfo.transtype%type, temp_cardid in cardinfo.cardid%type ) as   isnulltranstype  tradeinfo.transtype%type;--判断存取款类型是否正确   isnullcardid  cardinfo.cardid%type;--判断银行卡是否存在   isnullloss  cardinfo.isreportloss%type;--判断银行卡是否冻结 begin   begin      --判断卡号是否存在     select cardid into isnullcardid from cardinfo where cardid=temp_cardid;   exception     when no_data_found then       begin         raise_application_error(-20000,'卡号不存在！');       end;   end;   begin     --先判断卡号是否冻结     select isreportloss into isnullloss from cardinfo where cardid=temp_cardid;     if isnullloss='是' then       raise_application_error(-20001,'该卡已冻结，不能执行该操作！');     end if;     --判断存取款类型是否存在     select distinct transtype into isnulltranstype from tradeinfo where transtype=temp_transtype;     if temp_transtype='支取'then       update cardinfo set balance=balance-temp_money where cardid=temp_cardid;       insert into tradeinfo(cardid,transtype,transmoney)       values(temp_cardid,temp_transtype,temp_money);       dbms_output.put_line('取出'||temp_money||'RMB！');     elsif temp_transtype='存入' then       update cardinfo set balance=balance+temp_money where cardid=temp_cardid;       insert into tradeinfo(cardid,transtype,transmoney)       values(temp_cardid,temp_transtype,temp_money);       dbms_output.put_line('存入'||temp_money||'RMB！');     end if;   exception     when no_data_found then       rollback;       raise_application_error(-20002,'存取款类型不正确！');     when others then       dbms_output.put_line('余额不能少于1');       rollback;           end; end;

*************利用存取款存储过程来插入tradeinfo数据*****************

　　set serveroutput on;
　　execute inout_money(500,'存入','1010 3576 1685 3672');

　　set serveroutput on;
　　execute inout_money(500,'支取','1010 3576 1685 3672');

　　这时，trande表会有两条记录，一个是1010 3576 1685 3672支取的记录，一个是1010 3576 1685 3672的存入记录。

　　其实，开户和存取款的过程与插入数据时两回事，但是我们却可以利用这两个过程来实现数据的插入，本人在这里也是懒省事。其实，这样插入数据的话，思路上也比较好理解。毕竟，对于练习来说，我们在实现业务之前的数据只是为了检查这些数据的插入是否满足表的约束和它的合理性，但是，在实际的开发过程中，一个业务的完成的前提条件是你必须有实现这个业务的功能，所以先实现业务，通过业务来插入数据时最为合理的。不过这样对于初学者或者基础不太扎实的同胞可能就有些难以理解了，没事，慢慢来吧。

三.实现业务逻辑。

• 挂失。

　********挂失需要用户输入的信息**********

　　1.卡号。

　　2.密码。

　　3.账户ID。

　　******挂失的存储过程*********

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

create or replace procedure lose(temp_cardid in cardinfo.cardid%type, temp_pwd in number,temp_customerid in varchar2) as   row_info cardinfo%rowtype;   islose varchar2(5); begin   select isreportloss into islose from cardinfo where cardid=temp_cardid;   if islose='是' then     dbms_output.put_line('此卡已经挂失！');     goto last_point;   end if;   select * into row_info from cardinfo    where cardid=temp_cardid;   if row_info.pwd=temp_pwd and row_info.customerid=temp_customerid then     update cardinfo set IsReportLoss='是' where cardid=temp_cardid;     dbms_output.put_line('挂失成功!');   else     dbms_output.put_line('对不起，您输入卡的信息不正确，不能挂失！');   end if;   <<last_point>>   null; exception   when NO_DATA_FOUND then     dbms_output.put_line('您输入的卡号不存在！'); end;

 

　　**********测试***********

　　set serveroutput on;
　　execute lose('1010 3576 4654 1134','888866','1001');

 

• 修改密码。

　　********修改密码所需的用户的信息********

　　1.卡号

　　2.密码

　　**********修改密码的存储过程**********

1 2 3 4 5 6 7 8 9 10 11 12 13 14

create or replace procedure up_pwd(temp_cardid in varchar2, temp_newpwd in number) as temp varchar2(30); BEGIN   select cardid into temp from cardinfo where cardid=temp_cardid;   update cardinfo set pwd=temp_newpwd where cardid=temp_cardid;   dbms_output.put_line('修改密码成功！'); EXCEPTION   when no_data_found then     dbms_output.put_line('输入的卡号不存在!');   when others then     dbms_output.put_line('违反检查约束'); END;

　　*********修改密码的测试*************

　　　　set serveroutput on;
　　　　execute up_pwd('1010 3576 0030 0000','666652');

• 查询本周开户的卡号。

1	select * from cardinfo where opendate>=trunc(sysdate,'day');

• 查询本月一次性交易金额最高的卡号。

1 2 3 4

select * from tradeinfo; --from后面跟本月的搜索结果，where处控制transmoney为最大值。 select distinct * from(select * from tradeinfo where transdate>trunc(sysdate,'month')) where transmoney in( select max(transmoney) from tradeinfo);

• 查询卡号挂失的用户的信息。

1 2 3 4

select u.customername,u.customerid,u.personid,u.telephone,u.address from userinfo u inner join cardinfo c on c.customerid=u.customerid where c.isreportloss='是';

　　

四.开发中遇到的一些问题。

　　在开发的过程中，遇到了许多问题，因为我学习Oracle数据库也就十天左右的时间，对于一些基本的还不是很熟悉。

　　遇到的问题：

　　　　1.创建过程的时候不能有declare关键字。

　　　　2.goto流程控制不能goto到exception执行体中。

　　　　3.select into语句千万别返回多行语句，在编译的时候是不会出错的，调用的时候出错也会提示较多的错误，修改很麻烦。比如，查看存款类型是否合法的时候，就需要在select语句前加上distinct来确保返回的是一条语句。

　　　　4.创建序列不可以使用 or replace。

　　　　5.添加行级触发器的时候，赋值用 ：new.属性值来赋值。如果select into语句中也赋值的话，就会用两次序列自动产生的值，所以编译器不会报错，但是你的序列的增长率却是你想要的二倍。

　　　　6.在像开户这样的逻辑实现过程中，需要多次去用select去判断。而且它还有判断之后需要共同实现的部分，所以对于我一些新手来说，还是比较难于理解begin end的嵌套结构。bengin  end里面可以嵌套begin end；exception之后的when then也可以接着begin end并且也可以嵌套。

　　　　7.日期函数不是很熟悉。

　　　　　　tranc(date,day)日期date所在周的周日的日期。

　　　　　　interval '2' month将2作为month来运算。日期的加减默认是天。

　　　　　　last_day(date)，date日期所在月的最后一天的日期。

　　　　　　add_months(date,2) date日期两个月之后的日期。

　　　　8.数值函数不是很熟悉。

　　　　　　dbms_random.value产生0-1之间的小数，精确到很多位。

　　　　　　lpad(对象,位数,0)向对象左侧填充0，知道对象的位数=输出的位数。

　　　　　　trunc(数值对象[,截取精度])，如果截取精度>0,则截取到小数点后第几位，如果截取精度<0,则截取到小数点前第几位，截取的部分用0填充。如果截取精度=0，则去掉小数部分。截取精度不写的话默认为0.