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.
808
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
