--查询scott用户的emp表
select * from scott.emp;
/**
--创建表空间
create tablespace 表空间名称
datafile '文件存储位置'
size xxM;
*/
create tablespace testorcl1
datafile 'D:\oracle_db\testorcl1.ora'
size 100M;
--创建用户
/**
语法
create user 用户名
identified by 密码
default tablespace 表空间名；
*/
create user testdb
identified by java
default tablespace testorcl1;
/**
给用户授权
语法
grant 角色 to 用户；
*/
--授予testdb数据库管理员权限
grant dba to testdb;
--授予testdb存储过程和函数的执行权限
grant resource to testdb;
--使用testdb身份登陆
--创建班级表
create table cloazz(
cid number(9) primary key,
cname varchar2(50) not null
);
--创建学生表,并建立与班级的主外键关联
--删除表
--drop table student;
create table student(
sid number(9) primary key,
sname varchar2(50) not null,
sex varchar2(4),
address varchar2(50),
birthday date,
classid number(9) not null references cloazz(cid)
);
--序列
/**
序列可以生产连续的整数，主要用于为表的主键值自增设置数据
--创建序列的语法
create sequences 序列名；
--创建班级表的序列
create sequence seqclazz;
--创建学生表的序列
create sequence seqstu;
--获取序列的下一个值
序列名.nextval
--获取序列的当前值
序列名.currval
*/
select * from cloazz;
select * from student;
--使用序列为班级表的逐渐字段设置属性
insert into cloazz values(seqclazz.nextval,'java'||seqclazz.currval||'班');
commit;--提交数据到数据库表
--给学生表插入数据
insert into student values(seqstu.nextval,'张飞','男','户县','09-2月-1992',1);
insert into student values(seqstu.nextval,'马超','男','西凉',sysdate,1);
commit;
--获取oe用户的订单表
select * from oe.orders order by order_id;
--获取oe用户订单表orders的前5条记录
select od.*,rownum r from oe.orders od
where rownum > 0 and rownum <= 5
order by order_id;
--分页的语法
/**
select * from
(select a.*,rownum r from 表1 a where rownum <=当前页数*每页记录数)
where r > (当前记录数 - 1)* 每页记录数；
*/
--备份表的数据
/**
create table 备份表
as
select * from 表名 [where 条件]
*/
--备份oe用户的orders表的数据,只进行数据备份，不备份约束
create table bakorders
as
select * from oe.orders;
select * from bakorders;
--只备份字段
create table bakorders1
as
select * from oe.orders where 1=2;
--获取客户的姓名,连接首姓名和尾姓名 || 代表连接
select cs.cust_first_name || '.' || cs.cust_last_name 姓名 from oe.customers cs;
--获取系统时间
select sysdate from dual;
--oracle函数
--添加月份
select add_months(sysdate,4) from dual;
--获取指定日期所在月份最后一天的
日期
select last_day('12-2月-2017')from dual;
--计算两个日期之间相差的月份
select months_between(sysdate,'12-2月-2012') from dual;
--按照指定的日期格式进行四舍五入
select round(sysdate,'month') from dual;
select round(sysdate - 220,'year') from dual;
select round(sysdate,'day') from dual;
--获取星期数所在日期的下一个星期所在日期
select next_day(sysdate,'星期二') from dual;
--按照指定的日期格式截断当前指定日期
select trunc(sysdate, 'year') from dual;
select trunc(sysdate - 20, 'month') from dual;
select trunc(sysdate, 'day') from dual;
--字符函数
--截断当前字符
select substr('helloworld',0,5) from dual;
select substr('helloworld',5) from dual;
--获取指定字符在当前字符中的位置
select instr('hellworld','o') from dual;
--左右填充
select lpad('hello',10,'*') from dual;
select rpad('hello',10,'*') from dual;
--去空格或者替换当前字符串左右指定的字符
select trim(' hel lo ' ) from dual;
select trim('' from ' hello ') from dual;--无法显示
--数学函数
--四舍五入
select round(3.1415926, 3) from dual;
select round(3.1415926) from dual;
--转换函数
--1数字转换字符串
select trim(to_char(123.45,'$9999999999.99999')) from dual;
--2将日期转换为字符串
select to_char(sysdate,'yyyy-MM-dd') from dual;
--12小时制
select to_char(sysdate,'yyyy-MM-dd hh:MI:ss') from dual;
--24小时制
select to_char(sysdate,'yyyy-MM-dd hh24:MI:ss') from dual;
select '1' + '4' from dual;
--字符串转日期
select to_date('2014-12-21','yyyy-MM-dd') from dual;
--12小时制
select to_date('2014-12-21 1:03:11','yyyy-MM-dd hh:mi:ss') from dual;
--24小时制
select to_date('2014-12-21 21:03:11','yyyy-MM-dd hh24:mi:ss') from dual;
--获取用户id
select uid from dual;
--获取用户名称
select user from dual;
--其他函数
select nvl('test','hello') from dual;
select nvl('','hello') from dual;
--分组函数
/**
根据客户名称获取客户的订单数量
及其订单的总金额
*/
select * from oe.customers;
select * from oe.orders;
select
c.cust_first_name || '.' c.cust_last_name,
count(o.order_id) 订单数量,
sum(o.order_total) 订单总金额
from oe.orders o, oe.customers c
where o.customer_id=c.customer_id;
--testdb用户更新student表的第二条记录
select * from student;
update student s set s.sname='貂蝉' where s.sid=3;
commit;
--使用select获取行级锁
select * from student where sid=3 for update;
--给student表设置表级锁
lock table student in share mode;
--给student表设置排它锁
lock table student in exclusive mode;
update testdb.student s set s.sname='许褚' where s.sid=3;
select * from testdb.student;
select * from testdb.student where sid=3 for update wait 5;
commit;
lock table testdb.student in share mode;
lock table testdb.student in exclusive mode nowait;
--创建表空间
create tablespace test1
datafile 'D:\Oracle_sql\test1.ora'
--使用范围分区
create table testa1(
tid number(9) not null,
tname varchar2(50) not null
)partition by range(tid)
(
partition p1 values less than(1000) tablespace test1,
partition p2 values less than(2000) tablespace test2,
)
--按照分区表查询数据
select * from testa1 partition(p1);
select * from testa1 partition(p2);
--散列分区
create table testa2(
tid number(9) not null,
tname varchar2(50) not null
) partition by hash(tid)
(
partition ph1 tablespace test1,
partition ph2 tablespace tett2
)
--创建同义词
create synonym myorders for oe.orders;
--使用同义词访问替他用户的表
select * from myorders;
--删除同义词
drop synonym myorders;
--创建共有的同义词
create public synonym myorders for oe.orders;
--创建序列
create sequence seq_stu;
select * from student;
--给student表插入数据，主键值由序列提供
insert into student values(seq_stu.nextval,'aa'||seq_stu.currval,'男','山西',to_date('1994-8-28','yyyy-MM-dd'),1);
--创建视图
--replace 为修改视图的关键字
create or replace view vworders
as
select
od.*,c.cust_first_name||'.'||c.cust_last_name cname
from oe.orders od,oe.customers c
where od.customer_id=c.customer_id
--访问视图
select * from vworders;
--rowid
select o.*,rowid from oe.orders o;
--创建抽象类型
create or replace type myaddress as object(
addressid number(9),
city varchar2(50),
state varchar2(50),
street varchar2(50),
zip varchar2(50)
)
--创建表，指定字段的类型为自定义类型
create table student1(
stuid number(9) primary key,
sname varchar2(50),
address myaddress
)
--插入数据
insert into student1 values(
1,
'张飞',
myaddress(1001,'西安','陕西','科技四路','710060')
);
insert into student1 values(
2,
'关羽',
myaddress(1001,'太原','山西','科技四路','710060')
);
insert into student1 values(
3,
'赵云',
myaddress(1001,'安康','陕西','科技四路','710060')
);
insert into student1 values(
4,
'马超',
myaddress(1001,'西安','陕西','科技四路','710060')
);
--修改自定义类型字段的值
update student1 stu set stu.address.city='米脂',stu.sname='貂蝉'
where stuid=1;
commit;
--删除自定义类型
drop type 类型名称;
select * from student1;
--创建可变数组
/**
语法
create or replace type 类型名称 as array(长度) of 数组元素的类型
*/
--创建 商品名称数组类型
create or replace type items as array(5) of varchar2(50);
--创建购物车表
create table cart(
cid number(9) primary key,
product items
);
--插入数据
insert into cart values(
1,
items('面包','饼干','榨菜','锅巴',
'干果')
);
insert into cart values(
2,
items('苹果','饼干','梨子','锅巴','干果')
);
insert into cart values(
3,
items('栗子','饼干','香蕉','锅巴','方便面')
);
insert into cart values(
4,
items('枣子','饼干','火腿肠','锅巴','萝卜干')
);
commit;
select * from cart;
--查看可变数组的数据
select * from the(select product from cart c where c.cid=1);
select c.cid, p.* from cart c,table(select product from cart c where c.cid=1)p
where c.cid=1
;
--修改可变数组的数据
update cart c
set c.product=items('面包','饼干','榨菜','锅巴','芝麻酱')
where c.cid=1;
commit;
--嵌套表
--1.创建抽象数据类型
create or replace type emp_ty as object(
eid number(9),
ename varchar2(50),
sex varchar2(4),
address varchar2(50)
);
--2.创建表类型
create or replace type emp_table_type as table of emp_ty;
--3.基于表类型创建表
create table dep(
depid number(9) primary key,
depname varchar2(50),
emp emp_table_type
)nested table emp store as emp_table;
--向嵌套表中插入数据
insert into dep values(
1,
'技术部',
emp_table_type(emp_ty(1,'张飞','男','山西'),
emp_ty(2,'马超','男','山西'),
emp_ty(3,'赵云','男','山西'),
emp_ty(4,'黄忠','男','弧线')
)
);
insert into dep values(
2,
'财务部',
emp_table_type(emp_ty(1,'高峰','男','山西'),
emp_ty(2,'曹操','男','山西'),
emp_ty(3,'刘备','男','山西'),
emp_ty(4,'许褚','男','弧线')
)
);
insert into dep values(
3,
'项目部',
emp_table_type(emp_ty(1,'貂蝉','女','山西'),
emp_ty(2,'小巧','女','米脂'),
emp_ty(3,'小乔','女','锦州'),
emp_ty(4,'孙尚香','女','弧线')
)
);
commit;
select * from dep;
select * from the(select emp from dep where depid=1);
--向嵌套表中插入数据
insert into the(select emp from dep where depid=1)
values(5,'威严','女','上海');
commit;
--删除嵌套表的数据
delete from table(select emp from dep where depid=1)where eid=2;
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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
1
22
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
/**
语法
declare
变量 类型[(长度)][:=值];
......
begin
sql语句块;
.......
[
exception
when
异常对象(变量) then 异常处理语句;
.......
]
end;
*/
--实例
declare--声明变量
a number(9);
b number(9);
begin
a:=10;
b:=20;
dbms_output.put_line('a='||a);
dbms_output.put_line('b='||b);
dbms_output.put_line('a+b='||(a+b));--注意（a+b）要括号
end;
--使用into关键字获取表的字段值为变量赋值
/**
编写sqlpl/sql语句
给定指定的订单编号，获取订单的日期，金额和客户的名称
*/
/****************************************/
declare
odate date;
money number;
cname varchar2(50);
cid number;
begin
--获取指定编号的订单信息
select od.order_date,order_total,customer_id
into ---注意
odate,money,cid
from oe.orders od where od.order_id=2458;
--在customers表中查出name值赋给cname变量
select cs.cust_first_name||'.'||cs.cust_last_name
into
cname
from oe.customer
s cs where cs.customer_id=cid;
--输出获取的消息
dbms_output.put_line('客户名称:'||cname);
dbms_output.put_line('订单日期:'||to_char(odate,'yyyy-MM-dd'));
dbms_output.put_line('金额:'||money);
end;
/****************************************/
--使用表的列类型作为变量的数据类型
/**
表名.列名%type表示引用表的列类型
表名%rowtype--表示应用表的行类型
*/
--实例
/****************************************/
declare
odate oe.orders.order_date%type;
money oe.orders.order_total%type;
cname varchar2(50);
cid oe.customers.customer_id%type;
begin
--获取指定编号的订单信息
select od.order_date,order_total,customer_id
into ---注意
odate,money,cid
from oe.orders od where od.order_id=2459;--有异常
--在customers表中查出name值赋给cname变量
select cs.cust_first_name||'.'||cs.cust_last_name
into
cname
from oe.customers cs where cs.customer_id=cid;
--输出获取的消息
dbms_output.put_line('客户名称:'||cname);
dbms_output.put_line('订单日期:'||to_char(odate,'yyyy-MM-dd'));
dbms_output.put_line('金额:'||money);
--ocrale异常
exception
when no_data_found then dbms_output.put_line('没有数据！');
end;
/****************************************/
----------------------------------------------
--行类型的使用
/**
获取指定编号的订单的所有数据和客户名称
*/
declare
--定义行变量
odrows oe.orders%rowtype;--行类型
cid oe.customers.customer_id%type;--列类型
cname varchar2(50);
begin
select
od.*
into
odrows
from oe.orders od where od.order_id=2458;
--在customers表中查出name值赋给cname变量
select cs.cust_first_name||'.'||cs.cust_last_name
into
cname
from oe.customers cs where cs.customer_id=odrows.customer_id;
--输出获取的消息
dbms_output.put_line('客户名称:'||cname);
dbms_output.put_line('订单日期:'||to_char(odrows.order_date,'yyyy-MM-dd'));
dbms_output.put_line('金额:'||odrows.order_total);
end;
----------------------------------------------
--if语句
/**
语法
if 条件表达式 then
sql语句
else
处理语句
......
end if;
*/
/**
编写pl/sql
根据指定的订单编号获取信息，当订单编号不存在时，提示查无数据
否则显示信息
*/
----------------------------------------------
declare
--定义行变量
odrows oe.orders%rowtype;--行类型
cid oe.customers.customer_id%type;--列类型
cname varchar2(50);
num number;
begin
--获取指定编号的订单的数量
select
count(od.order_id) into num
from oe.orders od where od.order_id=2459;
if num > 0 then
select
od.*
into
odrows
from oe.orders od wh
ere od.order_id=2458;
--在customers表中查出name值赋给cname变量
select cs.cust_first_name||'.'||cs.cust_last_name
into
cname
from oe.customers cs where cs.customer_id=odrows.customer_id;
--输出获取的消息
dbms_output.put_line('客户名称:'||cname);
dbms_output.put_line('订单日期:'||to_char(odrows.order_date,'yyyy-MM-dd'));
dbms_output.put_line('金额:'||odrows.order_total);
else
dbms_output.put_line('订单信息不存在');
end if;
end;
----------------------------------------------
--循环语句
/**
1---loop循环语法
loop
语句块
....
exit when 退出条件;
......
end loop;
*/
--使用loop循环完成1-100累加
declare
i number:=0;
j number:=0;
begin
loop
i:=i + 1;
j:=j + i;
dbms_output.put_line('i:'||i);
dbms_output.put_line('j:'||j);
exit when i >= 100;
end loop;
end;
--2--while循环
/**
语法
while 条件 loop
循环语句
.....
end loop;
*/
declare
i number:=0;
j number:=0;
begin
while i <100 loop
i:=i + 1;
j:=j + i;
dbms_output.put_line('i:'||i);
dbms_output.put_line('j:'||j);
end loop;
end;
---------------------------------------
--for循环
/**
语法
for 变量 in 范围 loop
循环语句;
......
end loop;
*/
declare
j number:=0;
begin
for i in 1.. 100 loop
j:=j + i;
dbms_output.put_line('i:'||i);
dbms_output.put_line('j:'||j);
end loop;
end;
---------------------------------------
--游标
/**
1.隐式游标，变量名称为sql，由系统确定
游标属性
%notfound --true/false 没有数据被找到
%found --true/false 有数据返回真
%rowCount --true/false 返回记录数
%isopen --true/false 游标是否打开，隐式一直是false
*/
----------------------------------------------
declare
--定义行变量
odrows oe.orders%rowtype;--行类型
cid oe.customers.customer_id%type;--列类型
cname varchar2(50);
num number;
begin
--获取指定编号的订单的数量
select
count(od.order_id) into num
from oe.orders od where od.order_id=2459;
dbms_output.put_line('返回的记录数:'||sql%rowCount);
if num > 0 then
select
od.*
into
odrows
from oe.orders od where od.order_id=2458;
--在customers表中查出name值赋给cname变量
select cs.cust_first_name||'.'||cs.cust_last_name
into
cname
from oe.customers cs where cs.customer_id=odrows.customer_id;
--输出获取的消息
dbms_output.put_line('客户名称:'||cname);
dbms_output.put_line('订单日期:'||to_char(odrows.order_date,'yyyy-MM-dd'));
dbms_output.put
_line('金额:'||odrows.order_total);
else
dbms_output.put_line('订单信息不存在');
end if;
end;
----------------------------------------------
--显示游标
/**
语法
declare
cursor 游标名称 is select 语句; --声名游标
......
begin
open 游标名; -- 打开游标
....
fetch 游标 into ....--操作游标
....
close 游标; -- 关闭游标
end;
*/
/**
编写pl/sql语句
使用游标完成
给定客户编号，获取客户的订单信息
*/
-----------------------------------
declare
cursor csod is select * from oe.orders
where oe.orders.customer_id=101;
odrows oe.orders%rowtype;--行变量
begin
open csod; --打开游标
loop
fetch csod into odrows;
exit when csod%notfound;
dbms_output.put_line('订单日期:'||to_char(odrows.order_date,'yyyy-MM-dd'));
dbms_output.put_line('金额:'||odrows.order_total);
dbms_output.put_line('--------');
end loop;
close csod;
end;
-----------------------------------
--使用for循环操作游标
declare
cursor csod is select * from oe.orders
where oe.orders.customer_id=101;
begin
for odrows in csod loop
--输出获取信息
dbms_output.put_line('订单日期:'||to_char(odrows.order_date,'yyyy-MM-dd'));
dbms_output.put_line('金额:'||odrows.order_total);
dbms_output.put_line('--------');
end loop;
end;
--创建存储过程
/**
语法
create or replace procedure 过程名(参数 [in],参数 out 类型...)
is
局部变量 类型;
.....
begin
sql语句块;
.....
end;
*/
--调用存储过程
/**
declare
...
begin
过程名(参数...);
end;
*/
---------------------------------
--创建存储过程
/**
建立存储过程
传递订单编号，返回订单的日期，金额和客户名称
*/
------------------------------------------------
create or replace procedure proc_orders(oid in number,
odate out date,
money out number,
cname out varchar2
)
is
cid number;
begin
--获取指定编号的订单信息
select
od.order_date,order_total,customer_id
into--使用into将表的字段值传递给指定变量
odate,money,cid
from oe.orders od where od.order_id=oid;
--获取客户名称
select
cs.cust_first_name||'.'||cs.cust_last_name
into
cname
from oe.customers cs where cs.customer_id=cid;
exception
when no_data_found then dbms_output.put_line('未找到数据!');
end;
----------------------------------
create or replace procedure pro_orders(oid in number,odate out date, money out number,cname varchar2)
is
cid number;
begin
select oe.orders.order_date,oe.orders.orde
r_total,oe.orders.customer_id
into
odate,
money,
cid
from oe.orders where oe.orders.order_id=oid;
--获取客户名称
select oe.customers.cust_first_name||'.'||oe.customers.cust_last_name
into
cname
from oe.customers where oe.customers.customer_id=cid;
exceptions
when no_data_found then dbms_output.put_line('无数据！');
end;
--select * from oe.orders where oe.orders.order_id=2458;
--执行过程
declare
adate date;
amoney number;
aname varchar2(50);
begin
--执行过程
proc_orders(2458,adate,amoney,aname);
dbms_output.put_line('姓名:'||aname);
dbms_output.put_line('金额:'||amoney);
dbms_output.put_line('日期：'||adate);
end;
--函数
/**
语法
create or replace function 函数名(参数,类型.....) return 类型
is
变量 类型;
begin
sql语句快;
.....
return 返回值;
end;
--执行函数
select 函数(参数) from dual;
--使用pl/sql执行
declare
变量 类型;
begin
变量:=函数(参数....);
.....
end;
*/
---编写函数
create or replace function addtest(a number, b number) return number
is
c number;
begin
c:=a+b;
return c;
end;
--执行函数
select addtest(12,33) from dual;
--根据客户的编号返回客户的名称
create or replace function addtest1(cid number) return varchar2
is
cname varchar2(50);
begin
select cs.cust_first_name||'.'||cs.cust_last_name
into
cname
from oe.customers cs
where cs.customer_id=cid;
return cname;
end;
--执行函数
select addtest1(193) from dual;
--根据客户的编号返回客户的名称
create or replace function getCname(cid number) return varchar2
is
cname varchar2(50);
begin
select
cs.cust_first_name||'.'||cs.cust_last_name into cname
from oe.customers cs where cs.customer_id=cid;
return cname;
end;
--执行
select getCname(101) from dual;