1 /*
2 一、表达式
3 1.定义:表达式是操作数与操作符的组合
4
5 操作数:可以是常量、变量、函数的返回值、另一个查询语句返回的值
6
7 操作符:就是运算符，包括算术运算符、赋值运算符、比较运算符、逻辑运算符、字符匹配运算等
8
9 算术运算符:+,-,*,/,mod(m,n)[计算m和n的余数]
10 连接运算符:||
11 赋值运算符:= (注:比较运算符也是=)
12 比较运算符:=,>,<,>=,<=,<>,!=,between...and,in,like,is null
13 逻辑运算符:not,and,or
14 集合运算符:union,union all,minus,INTERSECT
15 union:取出2个表数据合并，去除重复记录
16 union all:取出2个表数据合并，保留重复记录
17 minus:取出2个表中不同数据
18 Intersect:取出2个表中共有数据【交集】
19 字符匹配运算:
20 BETWEEN...AND:如果操作数在某个范围之内，那么就为 TRUE
21 IN:如果操作数等于表达式列表中的一个，那么就为 TRUE
22 like:如果操作数与一种模式相匹配，那么就为 TRUE
23 %:零个或任意个字符
24 _:任意一个字符
25
26
27
28
29 二、添加数据
30
31 1.语法一:(常用)
32 insert into 表名(字段1,...,字段n)
33 values(值1,...,值n)
34
35
36 2.语法二:(常用)
37 insert into 表名
38 values(值1,...,值n)
39
40 注：必须要添加全部字段的数据(标识[identity]列除外)
41
42
43 3.语法三:添加多条记录，数据的具体的数据
44 insert into 表名(目标表)
45 select 数据1,...,数据n from dual
46 union
47 select 数据1,...,数据n from dual
48 union
49 select 数据1,...,数据n from dual
50 ...
51
52
53
54 create table student
55 (
56 id int identity(1,1) primary key ,
57 name varchar(20) not null ,
58 age int ,
59 wieght float
60 )
61
62 insert into student
63 select '赵六',20,180 from dual
64 union
65 select '田七',5,180 from dual
66 union
67 select '王八',2,250 from dual
68
69
70 4.语法四:添加多条记录，数据来源于另一张数据表
71 把某张表(源表)的数据，添加到另一张表(目标表,此表必须存在)中
72
73 insert into 表名(目标表)
74 select 字段1,...,字段n
75 from 表名(源表)
76 where 条件
77
78 注：目标表必须存在
79
80 insert into student_two
81 select name,age,wieght from student
82 where age>=18
83
84
85 create table student_two
86 (
87 id int identity(1,1) primary key ,
88 name varchar(20) not null ,
89 age int ,
90 wieght float
91 )
92
93 select * from student
94 select * from student_two
95
96
97
98 5.语法五:在创建数据表的同时，把另一张表的数据录入到表中
99
100 select 源表字段名1, 源表字段名2,……, 源表字段名n
101 into 新表名
102 from 源表名
103 where 源表字段条件
104
105 create table 表名（目标表）
106 as
107 select 字段1,字段2,...|* from 表名（源表）
108
109 注：目标表可以不存在
110
111 三、删除数据
112
113 1.语法一:delete from 表名
114
115 select * from student_two
116 delete from student_two
117
118 2.语法二:delete from 表名 where 条件
119
120 注：删除一般要写条件，否则会把整张表的数据都删除了;
121 一般选择唯一键、主键做为条件
122
123 delete from student where id=6
124
125 3.语法三:truncate table 表名
126 语法三的功能等同于语法一：都可以清空表中的数据
127
128 truncate table student
129
130 语法一和语法三的区别:
131 1)TRUNCATE删除数据的速度快，DELETE相对更慢。
132 2)TRUNCATE只能一次性删除表中全部数据，DELETE可以删除指定条件的数据行。
133 3)TRUNCATE删除数据后不能回滚(不写日志)，而DELETE可以回滚。
134 4)使用TRUNCATE删除表数据时，不会触发删除触发器，而DELETE则会触发相应的删除触发器。
135 5)对于有FOREIGN KEY约束引用的表不能使用TRUNCATE，而DELETE则可以(除已经被引用的数据行以外)。
136
137
138 四、修改数据
139 语法:
140 update 表名 set
141 字段1='新值',
142 字段2='新值',
143 ...
144 字段n='新值'
145 where 条件
146
147 select * from s69
148 update s69 set
149 name='张三三' ,
150 age=81
151 where id=3
152
153
154
155 注：修改一般要写条件，否则会把整张表都修改了
156
157
158
159
160 五、查询数据
161 1.语法:
162 select [distinct | 聚合函数] 字段集合 [as 别名]|*
163 from 表名
164 [where 查询条件语句集合]
165 [group by 分组字段列表]
166 [having 过滤条件语句集合] 分组查询条件
167 [order by 排序字段集合 [asc | desc]]
168
169
170 --查询所有字段的信息
171 select * from 表名
172 select * from dept;
173 select deptno,dname,loc from dept; --建议
174
175
176 --查询数据表中局部字段的信息
177 select 字段名1,...,字段名n
178 from 表名
179
180 select dname,loc from dept ;
181
182
183 --按条件查询
184 select * from 学生信息
185 where 条件
186
187 select * from dept
188 where deptno>20
189
190
191 --模糊查询like
192 通配符：
193 _:任意一个字符
194 %：0个或多个任意字符
195
196 --查看部门名称含有字母"S"的数据
197 select * from dept
198 where dname like '%S%'
199
200 --查看部门名称以字母"S"结尾的数据
201 select * from dept
202 where dname like '%S'
203
204 --查看部门名称以"LES"结尾并前面含有两个任意字符的数据
205 select * from dept
206 where dname like '__LES' ;
207
208
209 --未知值(is null , is not null)：查询某值是否为null
210 create table t1
211 as
212 select * from dept ;
213
214 insert into t1(deptno,dname) values (50,'Java开发部')
215
216 --查询部门地址为null的部门信息
217 select * from t1
218 where loc is null
219
220 --查询部门地址不为null的部门信息
221 select * from t1
222 where loc is not null
223
224
225
226 --列表运算符(in , not in)：查询匹配列表中的某一个值
227 select * from 表名
228 where 字段 [not] in ('值1',...,'值n')
229
230 --查询部门地址在'NEW YORK','CHICAGO','BOSTON'的部门信息
231 select * from t1
232 where loc in ('NEW YORK','CHICAGO','BOSTON')
233
234 select * from t1
235 where loc = 'NEW YORK' or loc = 'CHICAGO' or loc = 'BOSTON'
236
237
238 select * from t1
239 where loc not in ('NEW YORK','CHICAGO','BOSTON') or loc is null
240
241
242
243
244
245 --查询前面的n条记录
246 注意：Oracle不支持select top 语句，所以在Oracle中经常是用order by 跟rownum
247 的组合来实现select top n的查询。语法如下：
248
249 select 列名1 ...列名n from
250 (
251 select 列名1 ...列名n
252 from 表名 order by 列名1
253 )
254 where rownum <=N(抽出记录数)
255 order by rownum asc
256
257
258
259 eg:
260 select id,name from
261 (
262 select id,name
263 from student order by name
264 )
265 where rownum<=10 order by rownum asc
266
267 按姓名排序取出前十条数据
268 其中，rownum是产生有序编号的伪列
269
270
271 扩展：某个范围中的数据->分页查询
272 方法一：利用分析函数（建议），语法为：
273 row_number() over(order by 字段 desc|asc)
274
275 eg:
276 select deptno,dname,loc from
277 (
278 select
279 deptno,
280 dname,
281 loc,row_number() over ( order by deptno asc) rn
282 from t1
283 ) where rn between 2 and 4;
284
285
286
287 方法二：伪列（rownum）
288 select deptno,dname,loc from
289 (
290 select deptno,dname,loc,rownum as rn
291 from dept
292 where rownum <= 4
293 ) where rn >= 2;
294
295
296
297 --字符串连接:||
298 select 'hello' || ' world' from dual
299
300 trim(字段|数据):去除空格
301
302
303
304
305 --改列名（别名）用法
306
307 select 'hello' || ' world' as 你好 from dual
308
309
310
311 --可以省略as
312 select 'hello' || ' world' 你好 from dual
313
314 --排序(默认的是升序)
315 order by 字段名 asc | desc
316 1)asc:升序(默认)
317 2)desc:降序
318
319 select * from t1 order by deptno desc;
320 select * from t1 order by dname asc ;
321
322 --聚合函数
323 1)max:求最大值
324 2)min:求最小值
325 3)sum:求和
326 4)avg:求平均值
327 5)count:求记录数
328
329 select
330 max(sal) as 最高薪水,
331 min(sal) as 最低薪水,
332 sum(sal) as 薪水总和,
333 avg(sal) as 平均薪水,
334 count(*) as 总人数
335 from emp ;
336
337 select count(*),count(loc) from t1 ;
338
339 注：
340 count中如果传递具体字段时，不会统计null的字段
341 聚合函数一般结合分组函数使用
342 --统计各部门的平均薪水
343 select
344 deptno as 部门编号,
345 avg(sal) as 平均薪水
346 from emp group by deptno;
347
348 --统计各经理有多少个下属员工
349 select
350 mgr as 领导,
351 count(*) as 下属人数
352 from emp
353 group by mgr
354 having mgr is not null
355
356
357
358 --集合操作符
359 union:取出2个表数据合并，去除重复记录
360 union all:取出2个表数据合并，保留重复记录
361 minus:取出2个表中不同数据
362 Intersect:取出2个表中共有数据【交集】
363
364 SELECT 字段集合|* FROM 表1
365
366 UNION|union all|minus|intersect
367
368 SELECT 字段集合|* FROM 表2
369
370 注意:
371 两个查询的字段个数必须相同;
372 T_2 的查询字段类型要和 T_1的相同.
373
374 create table t2
375 as
376 select * from dept ;
377
378
379 delete from t2 where deptno>=30
380
381 select * from t2 ;
382 select * from dept ;
383
384
385 select * from t2
386 union
387 select * from dept;
388
389 select 'aa','bb' from dual
390 union
391 select 'cc','dd' from dual
392 union
393 select 'cc','dd' from dual
394
395
396 select * from t2
397 union all
398 select * from dept;
399
400 select 'aa','bb' from dual
401 union all
402 select 'cc','dd' from dual
403 union all
404 select 'cc','dd' from dual
405
406
407
408
409 select * from dept
410 minus
411 select * from t2;
412
413
414 select * from dept
415 intersect
416 select * from t2;
417
418
419 六、连接查询--连接(合并)两张或多张表，进行查询
420 （多表查询一般是通过主外键关联(公共关键字)）
421
422 连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接等。
423 通过连接运算符可以实现多个表查询。
424
425
426 关系型数据库
427 表1
428 表2
429 表3
430
431
432 1、内连接 inner join ... on ...
433 select * from 表1 inner join 表2 on 表1.字段=表2.字段
434
435 注：一般是根据主键和外键进行连接
436
437 select * from emp inner join dept
438 on emp.deptno = dept.deptno;
439
440
441 select * from emp e inner join dept d
442 on e.deptno = d.deptno;
443
444 select
445 ename,
446 job,
447 e.deptno,
448 dname
449 from emp e inner join dept d
450 on e.deptno = d.deptno;
451
452
453
454
455 2、等值连接 、不等值连接
456 select * from 表1,表2
457 where 表1.字段(主键)=表2.字段(外键)
458
459 select * from 表1,表2
460 where 表1.字段!=表2.字段
461
462
463 select * from emp,dept
464 where emp.deptno = dept.deptno
465
466 select * from emp e,dept d
467 where e.deptno = d.deptno
468
469 select ename,job,dname from emp e,dept d
470 where e.deptno = d.deptno
471
472
473
474 3、外连接
475 3.1）左外连接
476 select * from 表1 left [outer] join 表2
477 on 表1.字段=表2.字段
478
479
480 select * from 表1,表2 where 表1.字段(+)=表2.字段
481
482 注：（+）的用法：
483 1>（+）操作符只能出现在WHERE子句中，并且不能与OUTER JOIN语法同时使用。
484 2>当使用（+）操作符执行外连接时，如果在WHERE子句中包含有多个条件，则必须在所有条件中都包含（+）操作符。
485 3>（+）操作符只适用于列，而不能用在表达式上。
486 4>（+）操作符不能与OR和IN操作符一起使用。
487 5>（+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。
488
489 3.2）右外连接
490 select * from 表1 right [outer] join 表2 on 表1.字段=表2.字段
491 select * from 表1,表2 on 表1.字段=表2.字段(+)
492
493
494 3.3）完全外连接
495 select * from 表1 full [outer] join 表2 on 表1.字段=表2.字段
496
497 --主键表(主表)
498 create table cls
499 (
500 name varchar2(30) primary key ,
501 teacher varchar2(30) ,
502 loc varchar2(30)
503 )
504
505 insert into cls values('s3sj132','张老师','213') ;
506 insert into cls values('s3sj133','李老师','214') ;
507 insert into cls values('s3sj134','王老师','215') ;
508 insert into cls values('s3sj137','赵老师','216') ;
509
510 select * from cls;
511 drop table stu ;
512 --外键表(从表)
513 create table stu
514 (
515 name varchar2(30) ,
516 age number(3,0) ,
517 sex char(2) ,
518 cls_name varchar2(30)
519 )
520
521 insert into stu values ('张三',18,'男','s3sj132') ;
522 insert into stu values ('李四',19,'男','s3sj133') ;
523
524 insert into stu values ('王五',17,'女','s3sj134') ;
525 insert into stu values ('赵六',16,'男','s3sj135') ;
526
527 delete from stu where name='张三'
528
529
530
531 select * from cls;
532 select * from stu;
533
534 select * from cls left join stu
535 on cls.name=cls_name;
536
537 select * from cls inner join stu
538 on cls.name=cls_name;
539
540
541
542
543 select * from cls right outer join stu
544 on cls.name = cls_name;
545
546
547 select * from cls full outer join stu
548 on cls.name = cls_name;
549
550
551
552
553 select * from cls,stu
554 where cls.name(+)=stu.cls_name;
555
556 select * from cls,stu
557 where cls.name=stu.cls_name(+);
558
559
560
561 4、交叉连接
562 select * from 表1 cross join 表2
563 select * from 表1 , 表2
564
565 select * from cls cross join stu;
566 select * from cls,stu;
567
568
569 七、子查询
570 1.概念：当一个查询是另一个查询的条件时，称为子查询。
571
572
573 --在SELECT语句中使用子查询
574 select * from 学生信息
575 where 学号 in
576 (
577 select 学生编号 from 成绩信息 where 分数>96
578 )
579
580 --查询薪水最高的员工所在的部门信息
581 select * from dept where deptno in
582 (
583 select deptno from emp where sal=
584 (
585 select max(sal) from emp
586 )
587 )
588
589
590 select emp.deptno,dname,loc,ename,sal from dept,emp
591 where dept.deptno=emp.deptno
592 and emp.sal =
593 (
594 select max(sal) from emp
595 );
596
597
598
599
600
601 --子查询可以使用在SELECT、INSERT、UPDATE或DELETE语句中
602 insert into 学生信息
603 values ('2014010102','李四四',
604 (select 性别 from 学生信息
605 where 姓名='张苗苗'),
606 '1999-09-09','汉族','20050101','广东珠海')
607
608
609 update 学生信息 set
610 性别=(select 性别 from 学生信息 where 姓名='赵希坤')
611 where 姓名='张苗苗'
612
613
614 delete from 学生信息
615 where convert(varchar,家庭住址)=(select convert(varchar,家庭住址) from 学生信息 where 姓名='张苗苗')
616
617
618
619
620 八、事务处理
621 1、commit:提交事务
622 show autocommit : 显示是否自动事务提交
623 set autocommit=on|off : 设置是否自动事务提交
624
625 2、rollback:事务回滚
626 rollback
627 rollback to 保存点
628
629 3、设置保存点
630 savepoint 保存点名称
631
632 4、设置只读事务
633 set transaction read only
634
635 九、函数
636
637 */
638
639 /*
640 一、函数的定义
641 具有某种功能的代码段
642
643 实现代码重用，模块化编程
644
645 二、分类
646 1.系统函数,用户自定义函数
647
648 2.参数,返回值
649 1)无参无返
650 2)无参有返
651 3)有参无返
652 4)有参有返
653
654 函数中有两个角色:主调函数(张老师)，被调函数(袁家辉)
655 参数:主调函数给被调函数传递的信息（数据）
656 参数的数量：0个或多个
657
658 形式参数(形参):在定义函数时的参数
659 实际参数(实参):在调用函数时的参数
660
661 返回值：被调函数给主调函数传递的信息（数据）
662 返回值的数量：0个或１个
663
664
665 int sum(int a,int b) {
666 int s ;
667 s = a + b ;
668 return s ;
669 }
670
671 sum(1,2) ;
672
673 三、Oracle提供的系统函数
674 1.数学函数
675
676 --求绝对值
677 select abs(-4) from dual
678
679 --power(n,m):n的m次方
680 select power(2,3) from dual
681
682
683 --返回大于或等于n最小整数值（3，4，5...）
684 select ceil(2.48) from dual ; --3
685 select ceil(2.68) from dual ; --3
686
687 --返回小于或等于n最大整数值（2，1，0，-1...）
688 select floor(2.48) from dual ; --2
689 select floor(2.68) from dual ; --2
690
691 --四舍五入
692 select round(2.48) from dual ; --2
693 select round(2.68) from dual ; --3
694
695 --四舍五入，设置保留位数
696 select round(2.48,1) from dual ; --2.5
697 select round(2.163,2) from dual ; --2.16
698
699 --随机数
700 --1）小数(0 ~ 1)
701 select dbms_random.value from dual;
702
703 --2）指定范围内的小数 ( 0 ~ 100 )
704 select dbms_random.value(0,100) from dual;
705
706 --3）指定范围内的整数 ( 0 ~ 100 )
707 select round(dbms_random.value(0,100),0) from dual;
708 select round(dbms_random.value(0,100)) from dual;
709
710 --4）长度为20的随机数字串
711 select substr(cast(dbms_random.value as varchar2(38)),3,20) from dual;
712
713 --5）随机字符串
714 select dbms_random.string(opt, length) from dual;
715 其中：opt为选项，规则如下所示：
716 'u','U' : 大写字母
717 'l','L' : 小写字母
718 'a','A' : 大、小写字母
719 'x','X' : 数字、大写字母
720 'p','P' : 可打印字符
721 length为随机字符串的长度
722
723 select dbms_random.string('a',10) from dual;
724
725 --6）生成GUID：32位十六进制字符串
726 select sys_guid() from dual;
727 select length(sys_guid()) from dual;
728
729
730
731 2.字符串函数
732 --length:求字符串的长度
733 print len('hello,world')
734 select length(ename),ename from emp ;
735
736 --lower/upper:大小写
737 select ename,lower(ename),upper(ename) from emp ;
738
739 --concat/||：字符串连接
740 select concat('hello ','world') from dual ;
741 select 'hello ' || 'world' from dual ;
742
743
744 --substr("字符串",start,n):截取字符串，从start开始截取n个字符
745 select substr('hello,world',1,3) from dual;
746 select ename,substr(ename,1,3) from emp ;
747
748
749 --replace:替换字符串
750 replace('字符串','被替换子字符串','替换字符串')
751 select replace('hello world','world','china') from dual ;
752
753 --instr:查找字符串
754 instr('字符串','查找字符/字符串','起始位置'),返回下标位置（从1开始）
755 select instr('abcabc','c',-2) from dual;
756 select instr('abcabc','c',0) from dual;
757 select instr('abcabc','c') from dual;
758
759 注：起始位置中，正数从左向右、负数从右向左查找
760
761
762 --trim:去掉字符串左边、右边两边的空格
763 --ltrim:去掉字符串左边空格
764 --rtrim:去掉字符串右边空格
765 select 'AAA' || ' BBB ' || 'CCC' from dual ;
766 select 'AAA' || trim( ' BBB ') || 'CCC' from dual ;
767
768 --
769
770 3.日期函数 getdate()
771 --sysdate：返回当前session所在时区的默认时间
772 --获取当前系统时间
773 select sysdate from dual;
774
775 --add_months：返回指定日期月份+n之后的值，n可以为任何整数
776 --查询当前系统月份+2 的时间
777 select add_months(sysdate,2) from dual;
778
779 --查询当前系统月份-2 的时间
780 select add_months(sysdate,-2) from dual;
781
782 --last_day：返回指定时间所在月的最后一天
783 --获取当前系统月份的最后一天
784 select last_day(sysdate) from dual;
785
786 --months_between：返回月份差，结果可正可负，当然也有可能为 0
787 --获取入职日期距离当前时间多少月
788 select months_between(sysdate, hiredate) from emp;
789 select months_between(hiredate, sysdate) from emp;
790
791
792
793 --trunc（number,num_digits）
794 --用法一：截取日期值
795 select trunc(sysdate) from dual --2013-01-06 今天的日期为2013-01-06
796
797 select trunc(sysdate, 'mm') from dual
798 --2013-01-01 返回当月第一天.
799
800 select trunc(sysdate,'yy') from dual --2013-01-01 返回当年第一天
801
802 select trunc(sysdate,'dd') from dual --2013-01-06 返回当前年月日
803
804 select trunc(sysdate,'yyyy') from dual --2013-01-01 返回当年第一天
805
806 select trunc(sysdate,'d') from dual --2013-01-06 (星期天)返回当前星期的第一天
807
808 select trunc(sysdate, 'hh') from dual --2013-01-06 17:00:00 当前时间为17:35
809 select trunc(sysdate, 'mi') from dual --2013-01-06 17:35:00 TRUNC()函数没有秒的精确
810
811
812 --用法二：截取数值
813 number:需要截尾取整的数字。
814 num_digits:用于指定取整精度的数字。Num_digits 的默认值为 0。
815 trunc()函数截取时不进行四舍五入
816
817 select trunc(123.458) from dual --123
818 select trunc(123.458,0) from dual --123
819 select trunc(123.458,1) from dual --123.4
820 select trunc(123.458,-1) from dual --120
821 select trunc(123.458,-4) from dual --0
822 select trunc(123.458,4) from dual --123.458
823 select trunc(123) from dual --123
824 select trunc(123,1) from dual --123
825 select trunc(123,-1) from dual --120
826
827
828 4.转换函数
829 --to_char：日期转换
830 select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual
831 select to_char(sysdate, 'yyyy-mm-dd hh12:mi:ss') from dual
832
833 --to_char：数字转换
834 select to_char(-100.789999999999,'L99G999D999') from dual
835 select to_char(-100000.789999999999,'L99G999D99') from dual
836 select to_char(-100000.789999999999,'L999G999D99') from dual
837
838 9 代表一位数字，如果当前位有数字，显示数字，否则不显示(小数部分仍然会强制显示)
839 0 强制显示该位，如果当前位有数字，显示数字，否则显示 0
840 . （句点） 小数点
841 , （逗号） 分组（千）分隔符
842 PR 尖括号内负值
843 S 带负号的负值（使用本地化）
844 $ 美元符号显示
845 L 货币符号（使用本地化）
846 D 小数点（使用本地化）
847 G 分组分隔符（使用本地化） 10,000
848 MI 在指明的位置的负号（如果数字 < 0）
849 PL 在指明的位置的正号（如果数字 > 0）
850 SG 在指明的位置的正/负号
851 RN 罗马数字（输入在 1 和 3999 之间）
852 TH or th 转换成序数
853 V 移动 n 位（小数）（参阅注解）
854 EEEE 科学记数。现在不支持。
855
856
857
858 --to_date：将字符串转换成日期对象
859 select to_date('2011-11-11 11:11:11', 'yyyy-mm-ddhh24:mi:ss') from dual
860
861 --to_number：将字符转换成数字对象
862 --字符转换成数字对象
863 select to_number('209.976')*5 from dual
864 select to_number('209.976', '9G999D999')*5 from dua
865
866
867 5.空值判断函数
868 --nvl(内容,data):空值函数，类似 SQLServer中的 null()函数,如果内容为空，则值设置为data
869 select ename,comm,nvl(comm,0) from emp;
870
871 --nvl2(内容,data1,data2):如果内容不为空，则值设置为data1,否则设置为data2
872 select ename,comm,nvl2(comm,comm+200,200) from emp;
873
874 --nullif(a,b):如果 a,b 的值相等，返回 null，如果不相等，返回a
875 select nullif(10,10) from dual; --空，神马都没有
876 select nullif(10,11) from dual; --返回 10
877
878
879 6.分析函数
880 --row_number() over (order by 字段 asc|desc):为有序组中的每一行（划分组的行或查询行）返回一个唯一的排序值
881 select ename,sal,row_number() over(order by sal desc) 名次 from emp;
882
883 --rank() over(order by 字段 asc|desc):排名中如果出现相同的，名次相同，后面的名次跳过相应次数
884 select ename,sal,row_number() over(order by sal desc) 名次,rank() over(order by sal desc) 名次 from emp;
885
886 --dense_rank() over(order by 字段 asc|desc):排名中如果出现相同的，名次相同，后面的名次不跳过相应次数
887 select
888 ename,
889 sal,row_number() over(order by sal desc) 名次1,
890 rank() over(order by sal desc) 名次2,
891 dense_rank() over(order by sal desc) 名次3
892 from emp;
893
894
895
896
897
898
899
900 */
901 /*
902 数据库对象
903
904 一、概述
905 ORACLE数据库是关系型数据库，同时也是面向对象关系型数据库，又称ORDBMS，因此，在 ORACLE 数据库中也有专属的 ORACLE 对象， 主要有如下数据库对象：
906 1、同义词
907 2、序列
908 3、表
909 4、表分区
910 5、视图
911 6、过程
912 7、索引
913
914
915 二、同义词（别名）
916 1、概念：
917 同义词是数据库方案对象的一个“别名”，经常用于简化对象访问和提高对象访问的安全性。
918 同义词并不占用实际存储空间，只在数据字典中保存了同义词的定义。
919 Oracle同义词有两种类型，分别是公用 Oracle 同义词与私有 Oracle 同义词。
920
921 2、问题
922 select * from scott.emp;
923
924
925
926 --当前登录的session是非scott用户，访问emp表时，必须指定schema.表名
927
928 3、解决：同义词
929 --创建公用同义词
930 create public synonym syn_emp for scott.emp;
931 --通过访问同义词来简化对象的访问
932 select * from syn_emp;
933
934 4、创建同义词的语法：
935 create [or replace] [public] synonym sys_name
936 for [schema.]object_name
937
938 说明：
939 create:创建同义词
940 create or replace:没有则创建，有则替换
941 public:声明公用同义词，不写则为私有同义词
942 synonym:关键字
943 sys_name:用户创建同义词的名称，建议以sys_为前缀
944 for：关键字
945 schema:对象的集合，如包含tables, views, sequences, synonyms, indexes等；
946 一个用户一般对应一个schema,该用户的schema名等于用户名，并作为该用户缺省schema。
947
948 object_name:对象名
949
950 --eg1:公用同义词
951 create public synonym syn_emp for scott.emp;
952 select * from syn_emp;
953
954 --eg2:私用同义词
955 create or replace synonym syn_pri_emp for scott.emp
956 select * from syn_pri_emp;
957
958
959 5、查看同义词
960 --查看当前用户创建的私有同义词
961 select * from user_synonyms;
962
963 --查看当前用户创建的所有同义词
964 select * from all_synonyms where table_owner='SCOTT';
965 select * from dba_synonyms where table_owner='SCOTT';
966
967 6、删除同义词
968 drop public synonym 公有同义词名称
969
970 drop synonym 私有同义词名称
971
972
973 7、注意事项
974 1）用户必须拥有 Create public synonym 的系统权限才能创建公共同义词;
975 只有Create any synonym 权限才能创建私有同义词
976
977
978 2）用户必须拥有同义词所定义对象的权限才能进行访问，同义词不代表权限
979
980 3）同义词不仅可以查询，还可以添加，删除，修改，但都作用于物理表
981
982
983
984 二、序列（自动增长）
985 1、概念
986 在oracle中sequence就是所谓的序列号，
987 每次取的时候它会自动增加，
988 一般用在需要按序列号排序的地方；
989 Oracle的序列(SEQUENCE)类似SQLServer中的自动增长列，
990 用来生成唯一，连续的整数的数据库对象，
991 序列通常用来生成主键或唯一值，并且可以排序。
992
993
994 2、语法：
995 CREATE SEQUENCE sequence_name
996 INCREMENT BY 1 --每次加几个 默认 1
997 START WITH 1 --从 1 开始计数 默认 1
998 [MAXVALUE 值|NOMAXVALUE] --设置最大值 默认最大 10E27
999 [MINVALUE 值|NOMINVALUE] --设置最小值 默认最小-10E26
1000 [CYCLE|NOCYCLE] --一直累加，不循环
1001 [CACHE 10|NoCYCLE] --使序列号预分配
1002 [Order|NoOrder 默认]
1003
1004
1005 eg:
1006 CREATE SEQUENCE seq_test
1007 INCREMENT BY 1 --每次加几个
1008 START WITH 1 --从1开始计数
1009 NOMAXVALUE --不设置最大值
1010 NOCYCLE --一直累加，不循环
1011 CACHE 10 --使序列号预分配10个数,默认NOCACHE
1012
1013 3、访问序列的值
1014 NEXTVAL:返回序列的下一个值
1015 CURRVAL:返回序列的当前值
1016
1017 select 序列.nextval from dual
1018 select 序列.currval from dual
1019
1020 select seq_test.nextval from dual
1021 select seq_test.currval from dual
1022
1023 4、使用
1024 insert into 表名（自动增长的字段） values (序列名称.nextval)
1025
1026 5、修改
1027 alter sequence 序列名称 increment by 2;
1028
1029
1030 alter sequence seq_test increment by 2;
1031 --每次加2
1032
1033 1）不能修改序列的初始值
1034 2）序列的最小值不能大于当前值
1035 3）序列的最大值不能小于当前值
1036
1037 5、删除序列
1038 drop sequence 序列名称
1039
1040
1041 三、表分区
1042 1、概述
1043 在ORACLE中，当表的数据不断增加后，查询数据的速度就会降低，应用程序的效率也将大大下降，每次检索数据时都得扫描整张表，浪费了极大的资源，如何处理超大表数据存储和查询带来的问题， ORACLE 提供了特有的表分区技术。
1044
1045 2、什么是表分区
1046 ORACLE的表分区是一种处理超大型表，索引等对象的技术，简单可以理解为分而治之，即将一张大表分成可以管理的小块。表分区后逻辑上依然是同一张表，只是将表中的数据在物理上存储到多个(表空间)物理文件上。
1047
1048 3、表分区的优点
1049 1）增强可用性【一个分区出问题，不影响其他分区】
1050 2）维护方便【同上，只维护部分分区】
1051 3）均衡 IO【不同分区映射到磁盘平衡 IO】
1052 4）改善查询性能【检索自己需要的分区，提高检索速度】
1053
1054 4、分类
1055
1056 4.1）范围分区--最早,最经典,数据管理能力强，但分配不均匀
1057
1058 范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。
1059 这种分区方式是最为常用的，并且分区键经常采用日期，数值。
1060
1061 1）语法：
1062 CREATE TABLE 表名
1063 (
1064 字段名1 类型 [约束],
1065 ...
1066 字段名n 类型 [约束]
1067 )
1068 --表分区的定义
1069 PARTITION BY RANGE (COLUMN_NAME)
1070 (
1071 PARTITION PART1 VALUES LESS THAN (RANG1) [TABLESPACE TBS1],
1072
1073 PARTITION PART2 VALUES LESS THAN (RANG2) [TABLESPACE TBS2],
1074
1075 ...
1076
1077 PARTITION PARTN VALUES LESS THAN (MAXVALUE) [TABLESPACE TBSN],
1078 );
1079
1080 其中，
1081 COLUMN_NAME：指定分区字段
1082 PART1...PARTN:是表分区的名称
1083 RANG1...MAXVALUE:表分区的边界值，其中MAXVALUE表示边界最大值，每个分区的边界值必须比下一个分区的边界值小。
1084 TABLESPACE：表空间（可选），指定表分区所在的表空间
1085 TBS1...TBSN：表分区所在的表空间
1086
1087 注意：
1088 只能在创建表时创建表分区（指定相关的表分区类型），而不能对现有的表(未创建表分区)创建表分区。
1089
1090 2）例子：
1091
1092 eg1:根据某个值的范围来分区
1093
1094 CREATE TABLE part_andy1
1095 (
1096 andy_ID NUMBER NOT NULL PRIMARY KEY,
1097 FIRST_NAME VARCHAR2(30) NOT NULL,
1098 LAST_NAME VARCHAR2(30) NOT NULL,
1099 PHONE VARCHAR2(15) NOT NULL,
1100 EMAIL VARCHAR2(80),
1101 TATUS CHAR(1)
1102 )PARTITION BY RANGE (andy_ID)(
1103 PARTITION PART1 VALUES LESS THAN (10000) ,
1104 PARTITION PART2 VALUES LESS THAN (20000)
1105 );
1106
1107 说明：
1108 andy_ID字段的数值小于10000分配在PART1分区，
1109 andy_ID字段的数值大于等于10000且小于20000分配在PART2分区，
1110 andy_ID字段的数值大于等于20000将会出错，数据无法添加
1111
1112
1113 eg2:根据日期分区
1114 注意：如果是Date类型的字段，则必须使用年份为4个字符的格式，需要使用to_date()函数指定分区边界。
1115 CREATE TABLE part_andy2
1116 (
1117 ORDER_ID NUMBER(7) NOT NULL,
1118 ORDER_DATE DATE,
1119 OTAL_AMOUNT NUMBER,
1120 CUSTOTMER_ID NUMBER(7),
1121 PAID CHAR(1)
1122 )PARTITION BY RANGE (ORDER_DATE)(
1123 PARTITION p1 VALUES LESS THAN (TO_DATE('2014-10-1', 'yyyy-mm-dd')) ,
1124 PARTITION p2 VALUES LESS THAN (TO_DATE('2015-10-1', 'yyyy-mm-dd')) ,
1125 partition p3 values less than (maxvalue)
1126 );
1127
1128 说明：
1129 ORDER_DATE在2014-10-1之前的，分配在p1分区，
1130 ORDER_DATE大于或等于2014-10-1且小于2015-10-1的，分配在p2分区，
1131 ORDER_DATE大于或等于2015-10-1，分配在p3分区
1132
1133
1134 --课堂作业
1135 创建一张表（姓名、班级、课程名称、成绩），并使用范围分区对成绩字段划分为以下四个分区：
1136 不合格：小于60分
1137 合格：大于等60小于80
1138 良好：>=80且<90
1139 优秀:>=90且<=100
1140
1141
1142 3）修改分区--分区界限必须调整为高于最后一个分区界限
1143 ALTER TABLE 表名
1144 ADD PARTITION 表分区名称 VALUES LESS THAN (值);
1145
1146 alter table part_andy1
1147 add partition PART3 values less than (30000)
1148
1149
1150 4）截断分区--分区中数据将全部删除，但分区依然存在
1151 alter table 表名
1152 truncate partition 分区名;
1153
1154 alter table part_andy1
1155 truncate partition PART2;
1156
1157 5）合并分区--将两个相邻分区合并成一个新分区，继承原分区中最高上限（可重用上界限名称，下界限不可以，也可以使用新的）
1158 alter table 表名
1159 merge partitions 分区名1,分区名2 into partition 新分区名或原上界限名称;
1160
1161 alter table part_andy1
1162 merge partitions PART2,PART3 into partition PART3;
1163
1164 6）拆分分区--将一个分区在指定的 value 值处一分为二，变成 2 个分区，原分区将不存在，数据将分到相应新的分区
1165 alter table 表名
1166 split partition 原表分区 at (value) into (partition 拆分表分区1,partition 拆分表分区1);
1167
1168
1169 --查询part_andy1中，表分区PART3的数据
1170 select * from part_andy1 partition(PART3);
1171
1172 --拆分分区
1173 alter table part_andy1
1174 split partition PART3 at (20000) into (partition PART31,partition PART32);
1175
1176 --查看
1177 select * from part_andy1 partition(PART31);
1178 select * from part_andy1 partition(PART32);
1179
1180 7）变更分区名--将分区名称改变
1181 alter table 表名
1182 rename partition 原分区名 to 新分区
1183
1184 alter table part_andy1
1185 rename partition PART31 to PART31_NEW
1186
1187 8）删除分区
1188 Alter table 表名
1189 drop partition 分区名
1190
1191 alter table part_andy1
1192 drop partition PART31_NEW
1193
1194 4.2）散列分区--适合静态数据，总体性能最佳，易于实施，均匀
1195 散列分区是在列值上使用散列算法， 通过在分区键上执行 HASH 函数决定存储的分区，将数据平均地分布到不同的分区，当列的值没有合适的条件时，建议使用散列分区。
1196 CREATE TABLE EMPLOYEE
1197 (
1198 EMP_ID NUMBER(4),
1199 EMP_NAME VARCHAR2(14),
1200 EMP_ADDRESS VARCHAR2(15),
1201 DEPARTMENT VARCHAR2(10)
1202 )PARTITION BY HASH (DEPARTMENT)
1203 (
1204 partition p1,
1205 partition p2,
1206 partition p3
1207 )
1208 --PARTITIONS 4;
1209 select * from EMPLOYEE partition(p1);
1210 select * from EMPLOYEE partition(p2);
1211 select * from EMPLOYEE partition(p3);
1212
1213 4.3）列表（List）分区
1214 列表分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区，允许用户将不相关的数据组织在一起。
1215 CREATE TABLE employee
1216 (
1217 Emp_ID number(4),
1218 Emp_Name varchar2(14),
1219 Emp_Address varchar2 (15)
1220 )PARTITION BY LIST (Emp_Address)(
1221 Partition north values ('北京') ,
1222 Partition west values ('成都','重庆') ,
1223 Partition south values ('广州', '深圳'),
1224 Partition east values ('杭州', '苏州','温州')
1225 );
1226
1227 --添加数据
1228 insert into employee values(1,'zhangsan','北京');
1229 insert into employee values(2,'lucy','广州');
1230 insert into employee values(3,'petter','深圳');
1231
1232
1233 --查询数据
1234 select * from employee partition (north);? --zhangsan
1235 select * from employee partition (south); --lucy、petter
1236
1237 4.4）复合分区：
1238 形式一：范围-散列分区
1239 表首先按某列进行范围分区，然后再按散列算法进行散列分区，分区之中的分区被称为子分区
1240 create table slog
1241 (
1242 sno number,
1243 sinfo varchar(300)
1244 )partition by range(sno) --范围分区
1245 subpartition by hash(sinfo) --散列分区
1246 subpartitions 6(
1247 partition p1 values less than (2000),
1248 partition p2 values less than (4000),
1249 partition p3 values less than (6000),
1250 partition p4 values less than (8000)
1251 );
1252
1253 形式二：范围-列表分区
1254 表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区
1255 create table slog
1256 (
1257 sno number,
1258 sinfo varchar(300)
1259 )
1260 partition by range(sno) --范围分区
1261 subpartition by LIST(sinfo) --列表分区
1262 subpartition template(
1263 subpartition t1 values('404','NotFind'),
1264 subpartition t2 values('500','Error'),
1265 subpartition t3 values('200','Success')
1266 )
1267 (
1268 partition p1 values less than (2000),
1269 partition p2 values less than (4000),
1270 partition p3 values less than (6000),
1271 partition p4 values less than (8000)
1272 );
1273
1274
1275 4.5）Interval分区
1276 11G 版本引入的 interval 分区范围分区的一种增强功能，可实现 equi-sized 范围分区的自动化。创建的分区作为元数据，只有最开始的分区是永久分区。随着数据的增加会分配更多的部分，并自动创建新的分区和本地索引
1277 CREATE TABLE test
1278 (
1279 ID NUMBER,
1280 ORDER_DATE DATE
1281 ) PARTITION BY RANGE (ORDER_DATE)
1282 INTERVAL (NUMTOYMINTERVAL(1,'month'))
1283 (
1284 PARTITION p_first VALUES LESS THAN (to_date('2013-06-23','yyyy-mm-dd'))
1285 );
1286
1287 insert into test values(1,to_date('2013-06-22','yyyy-mm-dd'));
1288 insert into test values(1,to_date('2013-06-23','yyyy-mm-dd'));
1289 insert into test values(1,to_date('2013-07-20','yyyy-mm-dd'));
1290 insert into test values(1,to_date('2013-07-24','yyyy-mm-dd'));
1291
1292 select * from test partition (p_first);
1293
1294 ......
1295
1296 5、分区原则
1297 1）表的大小：当表的大小超过 1.5GB－2GB，或对于 OLTP 系统，表的记录超
1298 过 1000 万，都应考虑对表进行分区。
1299
1300 2）数据访问特性：基于表的大部分查询应用，只访问表中少量的数据。对于
1301 这样表进行分区，可充分利用分区排除无关数据查询的特性。
1302
1303 3）数据维护：按时间段删除成批的数据，例如按月删除历史数据。对于这样
1304 的表需要考虑进行分区，以满足维护的需要。
1305
1306 4）数据备份和恢复： 按时间周期进行表空间的备份时，将分区与表空间建
1307 立对应关系。
1308
1309 5）只读数据：如果一个表中大部分数据都是只读数据，通过对表进行分区，
1310 可将只读数据存储在只读表空间中，对于数据库的备份是非常有益的。
1311
1312 6）并行数据操作：对于经常执行并行操作（如 Parallel Insert,Parallel Update
1313 等）的表应考虑进行分区。
1314
1315 7）表的可用性：当对表的部分数据可用性要求很高时，应考虑进行表分区。
1316
1317
1318
1319
1320
1321 */
1322
1323 视图
1324 /*
1325 一、概念
1326 1、视图是一张虚拟的表，此表的结构从一个或多个表（或其它视图）查询的得到的结果一致。
1327
1328 2、视图一经定义，则以对象的方式存储在Oracle数据库中，视图中的数据是来源于查询的基表；对视图的CRUD操作，相应的基表也会发生变化。
1329
1330 3、对视图的更新或者插入限制很多,事实上,除非视图包含的是简单的select语句,
1331 否则不能通过它来做更新.推荐的做法还是在基表上做更新或者插入操作,
1332 一般情况下,视图只是用来方便查询的
1333
1334 二、优点：
1335 1、集中用户使用的数据
1336 2、掩盖数据库的复杂性
1337 3、简化用户权限的管理
1338 4、重新组织数据
1339 5、不占物理存储空间，它只是一个逻辑对象（虚拟的表）
1340
1341 三、分类
1342 1、关系视图：
1343 关系视图(relational view)基本上就是经过存储的查询，可以将它的输出看作是一个表。它就是基于关系数据的存储对象。
1344
1345 2、内嵌视图：
1346 又称为嵌套查询，是嵌入到父查询中的查询，能够在任何可以使用表名称的地方使用。
1347
1348 3、对象视图：
1349 为了迎合数据库中对象类型而将关系表投射到特定数据类型的虚拟对象表中，视图的每行都是带有属性、方法和唯一标识(OID)的对象实例。
1350
1351 4、物化视图：
1352 就是在数据库中查询结果存储在视图中，并支持查询重写、刷新、提交等特性的视图
1353
1354
1355 四、创建视图的步骤：
1356 1、编写select语句
1357 2、测试select语句
1358 3、查询结果的正确性
1359 4、创建视图
1360
1361 注意：
1362 1）普通用户并没有创建视图的权限，如果要创建视图，需要使用 dba 角色的用户赋予 create view 的权限，如：
1363
1364 grant create view to scott;
1365
1366 2）查看视图：User_views,All_views,Dba_views
1367
1368 五、语法:
1369 CREATE [OR REPLACE] [FORCE] VIEW view_name [(alias[, alias]...)]
1370 AS
1371 select_statement
1372 [WITH CHECK OPTION [CONSTRAINT constraint]]
1373 [WITH READ ONLY]
1374
1375 其中：
1376 OR REPLACE：若所创建的试图已经存在，ORACLE自动重建该视图；
1377 FORCE：不管基表是否存在ORACLE都会自动创建该视图；
1378 NOFORCE：只有基表都存在ORACLE才会创建该视图（默认）：
1379 alias：为视图产生的列定义的别名；
1380 select_statement：一条完整的SELECT语句，可以在该语句中定义别名；
1381 WITH CHECK OPTION ： 插入或修改的数据行必须满足视图定义的约束；
1382 WITH READ ONLY ： 该视图上不能进行任何DML操作。
1383
1384 六、关系视图
1385 1、创建普通关系视图，并对其进行DML操作
1386 create table emp1
1387 as
1388 select * from emp;
1389
1390 create view view_emp1
1391 as
1392 select * from emp1
1393
1394 update view_emp1 set sal=1000 where empno=7369
1395
1396 2、创建只读视图
1397 特点：只读，不能执行其他 DML 操作
1398 create or replace view view_emp11
1399 as
1400 select * from emp1 where sal>=3000 with read only;
1401
1402 --执行删除操作
1403 delete from view_emp11; --错误
1404
1405
1406 3、创建检查视图
1407 特点：执行 DML 操作时，自动检测是否满足创建视图时所建立的 where 条件，如果不满足，直接出错
1408 create or replace view view_emp11
1409 as
1410 select * from emp1
1411 where sal>=3000 with check option;
1412
1413 update view_emp11 set sal=4000 where empno=7902 --正确
1414 update view_emp11 set sal=1000 where empno=7902 --错误
1415
1416
1417 4、创建连接视图
1418 特点：连接视图是指基于多个表所创建的视图，即定义视图的查询是一个连接查询。使用连接视图的主要目的是为了简化连接查询。【 只能更新键保留表】
1419 create or replace view view_emp_dept
1420 as
1421 select e.*,d.dname,d.loc
1422 from emp e,dept d
1423 where e.deptno=d.deptno
1424
1425 update view_emp1_dept1 set sal=2450 where empno=7782 --正确
1426 update view_emp1_dept1 set dname='aaa' where empno=7782 --错误
1427
1428 注意：emp的主键在视图中作为主键，则emp是键保留表，而dept是非键保留表。
1429 在连接视图中，oracle规定可以更新键保留表。 因此，可以更新emp表中的数据，不能更新dept表中的数据。
1430
1431 5、创建复杂视图
1432 特点: 复杂视图是指包含函数、表达式或分组数据的视图，主要目的是为了简化
1433 查询
1434 create or replace view view_emp
1435 as
1436 select count(*) 人数,avg(sal+nvl(comm,0)) 平均工资,deptno 部门编号
1437 from emp
1438 group by deptno;
1439
1440 6、创建强制视图
1441 特点:正常情况下，如果基表不存在，创建视图就会失败。但是可以使用 FORCE选项强制创建视图（前提是创建视图的语句没有语法错误），但此时该视图处于失效状态,调用会出错，直到这个基表已经存在
1442
1443 create or replace force view view_test
1444 as select * from myemp;
1445
1446 select * from myemp; --错误
1447
1448 create table myemp --创建myemp表
1449 as
1450 select * from emp ;
1451
1452 select * from myemp; --正确
1453
1454 课堂练习
1455 1、创建一个视图，以便于查询薪水大于2000的员工信息
1456
1457 2、创建一个视图，以便于查询部门为SALES的员工信息，及部门所在地
1458
1459 3、修改某数据测试是否成功
1460
1461 七、内嵌视图
1462 内嵌视图又称为嵌套查询嵌视图。
1463 可以出现在 SELECT 语句的 FROM 子句中，以及INSERT INTO、 UPDATE、甚至是 DELETE FROM 语句中。
1464 内嵌视图是临时的，它只存在于父查询的运行期间。
1465 eg:
1466 select * from (select e.*,rownum rn from emp e) tab
1467 where rn>=5 and rn<=10;
1468 其中: select e.*,rownum rn from emp e 就是一个内嵌视图，临时有效
1469
1470
1471 八、物化视图
1472 1、概述
1473 物化视图简单理解就是一张特殊的物理表，预先计算并保存表连接或统计中需要耗时较多的操作的结果。物化视图也称为”快照”。
1474
1475 物化视图可以定时更新视图中的数据，对于大量数据统计查询后得出的
1476 小量结果集这种情况比较适合。
1477
1478 物化视图可以查询表，视图和其它的物化视图。
1479
1480 我们可以通过 user_segments 查看用户创建对象所在资源情况。
1481
1482 2、物化视图的作用
1483 1）实现两个数据库之间的数据同步，可以存在时间差。
1484
1485 2）如果是远程链接数据库的场景时，提高查询速度。（由于查询逻辑复杂，数据量比较大，导致每次查询视图的时候，查询速度慢，效率低下）
1486
1487 3、分类
1488 1）包含聚集的物化视图
1489 2）只包含连接的物化视图
1490 3）嵌套物化视图
1491
1492 注意：
1493 无论哪种视图，都需要设置物化视图的创建方式、 查询重写、 刷新方式等
1494 几个方面的功能选项。
1495
1496 1)创建方式(Build Methods)
1497 build immediate:是在创建物化视图的时候就生成数据。 默认为build immediate。
1498
1499 build deferred:是在创建时不生成数据，以后根据需要在生成数据
1500
1501 2)查询重写( Query Rewrite)
1502 查询重写(ENABLE QUERY REWRITE):指当对物化视图的基表进行查询时，Oracle 会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。
1503
1504 不查询重写(DISABLE QUERY REWRITE):指当对物化视图的基表进行查询时， Oracle不会判断能否通过查询物化视图来得到结果，直接对基表进行查询数据而不从物化视图中读取数据。 默认为DISABLE QUERY REWRITE。
1505
1506 3）刷新(Refresh)方式【自动 on commit，手动】
1507 刷新的方法有四种: FAST、 COMPLETE、 FORCE 和 NEVER。 默认值是 FORCE。
1508
1509 刷新的模式有两种: ON DEMAND 和 ON COMMIT。 默认值是 ON DEMAND
1510
1511
1512 4、创建视图日志文件
1513 CREATE MATERIALIZED VIEW LOG ON
1514 <table_name>
1515 [TABLESPACE <tablespace_name>] --视图日志保存位置
1516 [WITH [PRIMARY KEY｜ROWID｜SEQUENCE ]; --标示基表每一行
1517 [including new values]
1518
1519 注意：如果设置刷新方法为 fast，必须先构建一个基于基表的视图日志。
1520
1521 eg:
1522 create materialized view log on emp with rowid
1523
1524 5、创建物化视图语法
1525 CREATE MATERIALIZED VIEW [mv_name]
1526 [
1527 TABLESPACE [ts_name] -- 指定表空间
1528 BUILD [IMMEDIATE|DEFERRED] -- 创建时是否产生数据
1529 REFRESH [FAST|COMPLETE|FORCE] -- 快速、完全刷新
1530 [ON COMMIT|ON DEMAND START WITH (start_time) NEXT (next_time)] -- 刷新方式
1531 [WITH {PRIMARY KEY |ROWID}] --快速刷新时候唯一标示一条记录
1532 {ENABLE|DISABLED} QUERY REWRITE -–是否查询重写
1533 ]
1534 AS {select_statement};
1535
1536 eg1:
1537 create materialized view my_view
1538 build immediate
1539 refresh fast on commit
1540 with rowid
1541 as
1542 select empno,ename,sal from emp where sal>=3000;
1543
1544 eg2:
1545 create materialized view my_view
1546 build immediate
1547 refresh
1548 on commit
1549 enable query rewrite
1550 as
1551 select deptno,count(*) amount from myemp group by deptno;
1552
1553 eg3:
1554 create materialized view my_view
1555 refresh
1556 start with sysdate next sysdate+1/48
1557 with rowid
1558 as
1559 select count(*),avg(sal+nvl(comm,0)) sals from myemp;
1560
1561 6、删除物化日志文件
1562 drop materialized view log on empd
1563
1564 7、删除物化视图
1565 drop materialized view my_view;
1566
1567
1568
1569 九、常用系统视图
1570 1、USER_*:有关用户所拥有的对象信息，即用户自己创建的对象信息。
1571 1）Select * from user_users; --查看当前用户信息
1572 2）Select * from user_tables; --查看当前用户创建表信息
1573 3）Select * from user_views; --查看当前用户创建视图信息
1574 4）select * from user_tab_privs; -—查看当前用户表权限
1575 5）select * from user_sys_privs; --查看当前用户系统
1576 6）select * from user_role_privs; --查看当前用户角色
1577
1578 2、ALL_*： 有关用户可以访问的对象的信息，即用户自己创建的对象的信息
1579 加上其他用户创建的对象但该用户有权访问的信息。
1580
1581 3、DBA_*： 有关整个数据库中对象的信息。这里的 *可以为 TABLES，
1582 INDEXES， OBJECTS， USERS 等。
1583
1584 4、V$*:一般是动态视图，随着客户端或参数值设定的不同而不
1585
1586
1587
1588 */
1589 PL/SQL
1590 一、概述
1591 1、概念
1592 是oracle在标准的 sql 语言上的扩展 。
1593 实现具体的业务功能。
1594
1595 2、组成
1596 1)procedural language：过程语言
1597 2)SQL(struts Query language)：结构化查询语言
1598
1599 3、PL/SQL块 -> PL/SQL代码的集合
1600 逻辑上相关的声明和语句组合在一起
1601 块(block)是 pl/sql 的基本程序单元。
1602
1603 4、PL/SQL块分类
1604 匿名块
1605 非匿名块
1606
1607 注意：
1608 在块中不能直接使用 DDL 语句，
1609 但可以通过动态 SQL 来解决。
1610
1611 二、PL/SQL块的三大结构：声明部分、可执行部分、异常部分
1612 语法：
1613
1614 declare --声明部分（可省略）
1615 变量/常量的定义
1616
1617 begin --可执行部分,由begin开始，end结束，end后必须加分号，实现具体的功能业务
1618 ...
1619
1620
1621 exception --异常处理（可省略）
1622 ...
1623 end;
1624
1625
1626
1627 void main() {
1628
1629 }
1630
1631 public static void main(String[] args) {
1632
1633 }
1634
1635 public xxx extends HttpServlet {
1636 public void service() {
1637
1638 }
1639 }
1640
1641
1642
1643
1644 说明：
1645 1）各语句必须以分号结束
1646
1647 2）变量的定义 - declare关键字下定义
1648 变量名称 数据类型
1649 变量名称 数据类型 not null default 值 --not null 必须指定默认值
1650 变量名称 数据类型 := 值 --定义变量时指定默认值（方式一）
1651 变量名称 数据类型 default 值 --定义变量时指定默认值（方式二）
1652
1653 注：赋值运算符为 :=
1654
1655 eg1:
1656 declare
1657 name varchar2(30);
1658 name := '张三11' ; --错误
1659 begin
1660 name := '张三11' ;
1661 dbms_output.put_line('你好,' || name) ;
1662 end;
1663
1664 eg2:
1665 declare
1666 name varchar2(30) not null default '匿名' ;
1667 begin
1668 name := '张三' ;
1669 dbms_output.put_line('你好,' || name) ;
1670 end;
1671
1672 eg3:
1673 declare //定义
1674 name varchar2(30) not null default '匿名' ;
1675 sex char(2) := '男' ;
1676 age number(3) default 18 ;
1677 begin
1678 name := '张三' ;
1679 dbms_output.put_line('姓名：' || name) ;
1680 dbms_output.put_line('性别：' || sex) ;
1681 dbms_output.put_line('年龄：' || age) ;
1682 end;
1683
1684
1685 3）常量的定义：常量在定义时必须赋初始值
1686 常量名 constant 数据类型 ; --错误
1687
1688 常量名 constant 数据类型 := 常量值
1689
1690 常量名 constant 数据类型 default 常量值
1691
1692
1693 4）给变量赋值
1694 方式一：使用赋值运算符,把具体的数值赋值给变量/常量
1695 变量名称 := 值/表达式 ;
1696
1697 方式二:select into，把查询的数据动态的给变量赋值
1698 select 字段 into 变量名 from 表名 [where 条件];
1699
1700 select 字段1,...,字段n into 变量名1,...,变量n from 表名 [where 条件];
1701
1702
1703 5）&符号：代表由用户根据提示手动输入数据 -> 输入
1704 &提示内容
1705 declare
1706 变量名 varchar2 = '&提示内容' ;
1707
1708 declare
1709 name varchar2(4); --name超过4个字符会出错，解决使用%type或定义足够长的字符
1710 begin
1711 select ename into name from emp where empno=&员工编号;
1712 dbms_output.put_line('姓名为:'||name);
1713 end;
1714
1715 6）%type：定义变量的数据类型和长度与数据表某列的一致
1716 语法：变量 表名.字段%type
1717
1718 declare
1719 name emp.ename%type;
1720 begin
1721 select ename into name from emp where empno=&员工编号;
1722 dbms_output.put_line('姓名为:'||name);
1723 end;
1724
1725 7）%rowtype：返回一个记录类型，其数据类型和数据表的数据结构一致
1726 语法：变量 表名%rowtype
1727
1728 访问数据：
1729 变量.字段
1730
1731 declare
1732 obj emp%rowtype;
1733 begin
1734 select * into obj from emp where empno = 7369;
1735 dbms_output.put_line('姓名='|| obj.ename);
1736 dbms_output.put_line('岗位='|| obj.job);
1737 dbms_output.put_line('薪水='|| obj.sal);
1738 end;
1739
1740
1741 declare
1742 v_no emp.empno%type := &empno; --用户输入员工编号
1743 rec emp%rowtype; --rec为记录
1744 begin
1745 select * into rec from emp where empno=v_no;
1746 dbms_output.put_line('姓名:'||rec.ename||' 工资:'||rec.sal);
1747 end;
1748
1749 8）异常
1750 zero_divide
1751 case_not_found
1752 ...
1753
1754 when 异常种类 then
1755 ...
1756
1757
1758 三、数据类型
1759 1、标量数据类型
1760 标量类型是非常常用的一种类型，没有内部组件，仅包含单个值，主要包括 number，character,date/time,boolean 类型
1761
1762 declare
1763 变量 标量数据类型
1764 ...
1765
1766
1767 2、LOB 数据类型
1768
1769 3、组合【复合】数据类型
1770 1）record：用来存储多个值的变量称之为组合或者复合变量，其中存储的多个值可以是 PL/SQL 记录,也可以是 PL/SQL 中的表
1771
1772 declare
1773 type 组合类型名称 is record --创建一个组合类型
1774 (
1775 变量1 数据类型 ,
1776 ...
1777 变量n 数据类型
1778 );
1779
1780 age number(3,2) ;
1781
1782 组合类型变量 组合类型名称 ; --定义一个组合类型的变量
1783
1784
1785 begin
1786 select 字段1,...,字段n into 组合类型的变量 from 表名 where ... ;
1787 ...
1788 end;
1789
1790
1791
1792 declare
1793 type emp_mytype is record
1794 (
1795 name emp.ename%type,
1796 job emp.job%type,
1797 sal number(10,2)
1798 );
1799
1800 einfo emp_mytype;
1801
1802 begin
1803 select ename,job,sal into einfo from emp where empno=7788;
1804 dbms_output.put_line('姓名:'||einfo.name||' 岗位:'||einfo.job||' 待遇:'||einfo.sal);
1805 end;
1806
1807
1808 不足之处：一次只能存储一条记录的值
1809
1810
1811 2）table
1812
1813 declare
1814 type 组合类型名称 is table of 数据类型 [index by binary_integer];
1815
1816 组合类型变量 组合类型名称;
1817
1818 begin
1819 select 字段 into 组合类型变量(下标1) from 数据表 where ...;
1820 ...
1821 select 字段 into 组合类型变量(下标2) from 数据表 where ...;
1822
1823 end;
1824
1825 注：
1826 下标可以任意的整数（负数，无上下限）
1827
1828 index by binary_integer : 下标自动增长，并不需要每次使用extend增加一个空间
1829
1830 eg1:使用by binary_integer
1831 declare
1832 type my_table is table of emp.ename%type index by binary_integer;
1833
1834 einfo my_table; --不需要初始化
1835 begin
1836 --不必须使用extend增加一个空间且下标可以任意整数
1837 select ename into einfo(-1) from emp where empno=7788;
1838 select ename into einfo(-2) from emp where empno=7900;
1839 select ename into einfo(-3) from emp where empno=7902;
1840
1841 dbms_output.put_line('姓名 1:'||einfo(-1)||'姓名 2:'||einfo(-2)||'姓名 3:'||einfo(-3));
1842 end;
1843
1844
1845
1846
1847
1848 eg2:不使用by binary_integer
1849 declare
1850 type my_table is table of emp.ename%type ;
1851 einfo my_table := my_table() ; --必须初始化
1852 begin
1853 einfo.extend; --必须使用extend增加一个空间且下标从1开始
1854 select ename into einfo(1) from emp where empno=7788;
1855
1856 einfo.extend;
1857 select ename into einfo(2) from emp where empno=7900;
1858
1859 einfo.extend;
1860 select ename into einfo(3) from emp where empno=7902;
1861
1862 dbms_output.put_line('姓名 1:'||einfo(1)||'姓名 2:'||einfo(2)||'姓名 3:'||einfo(3));
1863 end;
1864
1865
1866
1867
1868 eg3:可以使用bulk collect一次将符合条件的数据全部写入表中
1869 declare
1870 type my_table is table of emp.ename%type index by binary_integer;
1871 einfo my_table;
1872 begin
1873 select ename bulk collect into einfo from emp ;
1874 for i in 1 .. einfo.count --count返回表的记录数
1875 loop
1876 dbms_output.put_line(einfo(i));
1877 end loop;
1878 end;
1879
1880
1881
1882 eg4:record与table组合类型的混合应用
1883
1884 declare
1885 --第一：自定义组合类型 - recod
1886 type myrecord is record (
1887 mname emp.ename%type ,
1888 mjob emp.job%type
1889 ) ;
1890
1891 --第一：自定义组合类型 - table
1892 type myType is table of myrecord index by binary_integer ;
1893
1894 --第二：创建组合类型的变量
1895 einfo myType ;
1896
1897 begin
1898 --第二：给组合类型变量赋值
1899 select ename,job into einfo(1) from emp where empno=7369 ;
1900 select ename,job into einfo(2) from emp where empno=7499 ;
1901 select ename,job into einfo(3) from emp where empno=7521 ;
1902
1903 dbms_output.put_line('第一个姓名：' || einfo(1).mname || ' 职位' || einfo(1).mjob) ;
1904 dbms_output.put_line('第二个姓名：' || einfo(2).mname || ' 职位' || einfo(2).mjob) ;
1905 dbms_output.put_line('第三个姓名：' || einfo(3).mname || ' 职位' || einfo(3).mjob) ;
1906 end;
1907
1908
1909
1910 4、引用【参照】数据类型
1911
1912
1913 四、程序控制语句
1914 1、条件
1915 1）if
1916
1917 if 条件 then
1918 代码块;
1919 end if;
1920
1921 if(条件) {
1922 代码块 ;
1923 }
1924
1925 注：条件一般是逻辑运算符或关系运算符或混合
1926
1927 --输入年龄，判断是否大于18岁，如果大于18岁，则输出可以去网吧。
1928
1929 2)if...else
1930
1931 if 条件 then
1932 代码块 ;
1933 else
1934 代码块 ;
1935 end if;
1936
1937
1938 --输入年龄，判断是否大于18岁，如果大于18岁，则输出可以去网吧，否则输出不可以去网吧。
1939
1940 3）if..else if..
1941 if 条件1 then
1942 代码块1 ;
1943 elsif 条件2 then
1944 代码块2 ;
1945 elsif 条件3 then
1946 代码块3 ;
1947 else
1948 代码块n
1949 end if;
1950
1951 --输入成绩，判断成绩的等级
1952 0-60:不及格
1953 60-70:及格
1954 70-80:中等
1955 80-90:良好
1956 90-100:优秀
1957 其它:输入的成绩有误
1958
1959
1960 4）case
1961 case
1962 when 条件1 then
1963 语句块1 ;
1964 ...
1965 when 条件n then
1966 语句块n ;
1967 else
1968 语句块n+1 ;
1969 end case ;
1970
1971 --使用case改写以上的练习
1972 --判断今天是星期几
1973 如果是周1-5，打印输出"好好学习，天天向上"
1974 如果是周六，打印输出"睡个懒觉,醒了去外面玩玩"
1975 如果是周日,打印输出"复习、做作业"
1976
1977
1978 2、循环
1979 1）loop
1980 loop
1981 要执行的语句（循环体）;
1982 exit when <条件语句> --条件满足，退出循环语句
1983 end loop;
1984
1985 其中：
1986 exit when 子句是必须的，否则循环将无法停止。
1987
1988
1989 --循环打印输出1-10的数据
1990
1991
1992 2)while
1993 WHILE 条件
1994 LOOP
1995 要执行的语句;
1996 END LOOP;
1997
1998 其中：
1999 在 WHILE 循环语句中仍然可以使用 EXIT 或 EXIT WHEN 子句
2000
2001 --使用while语句实现变量v_num从1到10的循环，打印输出v_num的值，如果v_num=8退出循环。
2002
2003 3)for
2004 for 循环变量 in [ REVERSE ] 下限 .. 上限
2005 LOOP
2006 要执行的语句;
2007 END LOOP;
2008
2009 其中：
2010 每循环一次，循环变量自动加 1；使用关键字 REVERSE，循环变量自动减 1
2011 跟在 IN REVERSE 后面的数字必须是从小到大的顺序，但不一定是整数，可以是能够转换成整数的变量或表达式
2012 可以使用 EXIT WHEN 子句退出循环
2013
2014
2015 4)特殊语句
2016 exit -- 相当于高级语言中的break
2017 continue
2018
2019 exit when 条件
2020 continue when 条件
2021
2022
2023 五、动态SQL语句
2024
2025
2026
2027 六、异常处理
2028
2029
2030
2031 五、动态SQL语句
2032 1、概述
2033 在 PL/SQL 块中，可以执行 DML 和 TCL,但是不可以直接执行 DDL 以及 DCL，如果想在块中使用，必须使用动态 SQL。
2034
2035 --在PL/SQL块执行DDL操作
2036 begin
2037 drop table stu ;
2038 end;
2039
2040 2、动态SQL的两种实现（分类）：
2041 本地动态 SQL
2042 DBMS_SQL包
2043
2044 3、本地动态SQL
2045 1）语法：
2046
2047 Execute immediate dynamic_sql_string
2048 [into define_variable_list]
2049 [using bind_argument_list]
2050
2051 说明:
2052 dynamic_sql_string:动态执行的SQL语句
2053 define_variable_list:用于接受 select 查询记录值的变量列表
2054 bind_argument_list:绑定输入参数的列表
2055
2056 2）例子
2057 --eg1:动态的执行DDL语句
2058 declare
2059 str_sql varchar(300) ;
2060 begin
2061 str_sql := 'drop table stu' ;
2062 execute immediate str_sql;
2063 end;
2064
2065 --eg2:接受 select 查询记录值的变量列表
2066 declare
2067 str_sql varchar(300) ;
2068 row_line emp%rowtype ;
2069 begin
2070 str_sql := 'select * from emp where empno=' || &工号 ;
2071 execute immediate str_sql into row_line;
2072 dbms_output.put_line('姓名：' || row_line.ename);
2073 end;
2074
2075
2076
2077 --eg3:绑定输入参数的列表
2078 1>设置占位符-> :1、:2、...、:n
2079 2>动态传入参数 -> using 参数值1,...,参数值n
2080
2081 create table stu (
2082 name varchar2(30) ,
2083 age number(3) ,
2084 sex char(2)
2085 )
2086
2087 declare
2088 str_sql varchar2(100) ;
2089 begin
2090 str_sql := 'insert into stu values (:1,:2,:3)' ;
2091 execute immediate str_sql using '张三',18,'男';
2092 commit;
2093 end;
2094
2095 select * from stu ;
2096
2097
2098 4、DBMS_SQL 包执行动态 SQL
2099 1）步骤
2100 第一:构建动态 SQL 语句
2101
2102 第二:打开游标
2103
2104 第三:使用 DBMS_SQL 包的 parse 过程来分析字符串
2105
2106 第四:使用 DBMS_SQL 包的 bind_variable 过程来绑定变量
2107
2108 第五:使用 DBMS_SQL 包的 execute 函数来执行语句并返回受影响的行
2109
2110
2111 第六:关闭游标
2112
2113 2）例子
2114 declare
2115 table_name varchar2(50):='&table_name';
2116 str_sql varchar2(500);
2117 v_cursor number;
2118 v_row int;
2119 begin
2120 --第一：构建动态DDL语句
2121 str_sql:='delete from ' || table_name || ' where name=:1';
2122 --第二：为处理打开游标
2123 v_cursor:=dbms_sql.open_cursor;
2124 --第三：分析语句 -- dbms_sql.native指定语句的行为（根据版本）
2125 dbms_sql.parse(v_cursor,str_sql,dbms_sql.native);
2126 --第四：绑定变量
2127 dbms_sql.bind_variable(v_cursor,':1','张三');
2128 --第五：执行语句[DDL语句，该操作可以省略]
2129 v_row:=dbms_sql.execute(v_cursor);
2130 --第六：关闭游标
2131 dbms_sql.close_cursor(v_cursor);
2132 dbms_output.put_line('删除表中的数据，受影响行为:' || v_row);
2133 end;
2134
2135
2136
2137 六、异常处理
2138 1、预定义异常
2139 declare
2140 v_name varchar2(30);
2141 begin
2142 select ename into v_name from emp; --where empno=73691;
2143 dbms_output.put_line('姓名：' || v_name) ;
2144
2145 exception
2146 when TOO_MANY_ROWS then
2147 dbms_output.put_line('返回多行');
2148 when no_data_found then --可以有多个 when
2149 dbms_output.put_line('没有查询到数据');
2150 when others then --可选
2151 dbms_output.put_line('未知异常,错误号'||sqlcode||',错误信息'||sqlerrm);
2152
2153 end;
2154
2155 2、非预定异常
2156 declare
2157 v_i int;
2158 my_math exception; --定义一个异常名
2159 pragma exception_init(my_math,-1476); --将异常名与 Oracle 错误码绑定
2160 begin
2161 v_i := 10/0; --将出现异常
2162 exception
2163 --when my_math then --除数为 0 异常将被处理
2164 --dbms_output.put_line('除数不能为 0！ ');
2165 when others then
2166 dbms_output.put_line('未知异常,错误号'||sqlcode||',错误信息'||sqlerrm);
2167 end;
2168
2169
2170
2171 3、用户定义异常
2172 eg1:
2173 declare
2174 myexception exception; --定义一个异常名
2175 age int;
2176 begin
2177 age:=&age;
2178
2179 if age<18 or age>36 then
2180 raise myexception; --手动抛出异常
2181 else
2182 dbms_output.put_line('您输入的年龄是' || age);
2183 end if;
2184
2185 exception
2186 when myexception then --处理异常
2187 dbms_output.put_line('年龄不符合标准');
2188 end;
2189
2190
2191
2192 eg2:抛出应用程序异常：raise_application_error
2193 declare
2194 age int;
2195 begin
2196 age:=&age;
2197 if age<18 or age>36 then
2198 raise_application_error(-20001,'你输入的数据'||age||'超出了合适的范围!'); --手动抛出系统异常
2199 else
2200 dbms_output.put_line('您输入的年龄是'|| age);
2201 end if;
2202 end;
2203
2204
2205 eg3:
2206 declare
2207 age int;
2208 myexeption exception;--定义异常名
2209 pragma exception_init(myexeption,-20001);--将异常名与系统错误号绑定
2210 begin
2211 age:=&age;
2212
2213 if age<18 or age>36 then
2214 raise_application_error(-20001,'你输入的数据'||age||'超出了合适的范围!'); --手动抛出系统异常
2215 else
2216 dbms_output.put_line('您输入的年龄是'|| age);
2217 end if;
2218
2219 exception
2220 when myexeption then
2221 --在自定义异常时，函数 sqlcode 与 sqlerrm 可以用来显示错误号与错误信息
2222 dbms_output.put_line('出错了,错误号:'||sqlcode||',错误信息:'||sqlerrm);
2223 end;
2224
2225 存储过程
2226 一、子程序
2227 一个命名的 PL/SQL 块，编译并存储在数据库中。
2228
2229 PL/SQL块可以实现复杂的业务逻辑，但只是临时的，只能用一次;
2230 而子程序把实现业务逻辑的PL/SQL块进行命名，存储在数据库中，便于重复使用。
2231
2232 二、子程序的结构
2233 子程序的结构和普通的PL/SQL块是一致的，也包括如下部分:
2234
2235 声明部分
2236 可执行部分
2237 异常处理部分
2238
2239
2240 三、子程序的分类
2241 存储过程
2242 函数
2243
2244 四、优点
2245 模块化【将程序分解为逻辑模块】
2246 可重用性【可以被任意数目的程序调用】
2247 可维护性【简化维护操作】
2248 安全性【通过设置权限，使数据更安全】
2249
2250 五、存储过程
2251 1、概念
2252 过程是用于完成特定任务的子程序，通过使用过程不仅可以简化客户端应用程序的开发和维护，而且还可以提高应用程序的运行性能。
2253
2254 2、语法
2255 --创建过程，可指定运行过程需传递的参数
2256 create [or replace] procedure 存储过程名 [(参数列表)]
2257 is|as
2258 [变量/常量的定义]
2259 begin
2260 可执行的语句部分 --具体的业务逻辑功能的实现
2261 [exception
2262 异常的具体处理
2263 ]
2264 end;
2265
2266 注意事项:
2267 1）过程体内不能使用查询语句，只能用于赋值（SQL语句块都如此）
2268 2）如果过程体语句有错误也能创建成功
2269 3）没有参数就不写，不用()
2270 4）参数列表，语法如下
2271 参数名 in/out 数据类型,...
2272 5）在调用存储过程时，也可以通过"=>"给参数赋值。
2273 输入/输出参数名=>值
2274
2275 3、hello,world
2276 --创建
2277 create or replace procedure sayhello
2278 as
2279 begin
2280 dbms_output.put_line('hello,world') ;
2281 end;
2282
2283 --调用
2284 begin
2285 sayhello();
2286 end;
2287
2288 4、调用
2289 exec 存储过程名[(实参列表)] ;
2290
2291 或
2292
2293 begin
2294 存储过程名称[(实参列表)] ;
2295 end;
2296
2297
2298 5、输入/输出参数
2299 in : 输入参数（默认）
2300 out: 输出参数
2301
2302
2303 6、例子
2304 1）无输入参数、无输出参数
2305 --创建
2306 create or replace procedure sum1
2307 as
2308 a number := 1 ;
2309 b constant number := 1 ;
2310 begin
2311 dbms_output.put_line(a+b) ;
2312 end;
2313
2314 --调用
2315 begin
2316 sum1;
2317 end;
2318
2319
2320 2）有输入参数、无输出参数
2321 --创建(参数默认是输入参数)
2322 create or replace procedure sum2(a in number,b number)
2323 as
2324 c number := 3 ;
2325 begin
2326 dbms_output.put_line(a+b+c) ;
2327 end;
2328
2329 --调用
2330 begin
2331 sum2(1,2);
2332 end;
2333
2334 3、无参有返
2335 --创建
2336 create or replace procedure sum3(s out number)
2337 as
2338 a number := 1 ;
2339 b number := 2 ;
2340 c number := 3 ;
2341 begin
2342 s := a + b + c ;
2343 end;
2344
2345 --调用
2346 declare
2347 s number ;
2348 begin
2349 sum3(s);
2350 dbms_output.put_line(s) ;
2351 end;
2352
2353
2354
2355 4、有参有返
2356 --创建
2357 create or replace procedure sum4(a number,b in number,s out number)
2358 as
2359 begin
2360 s := a + b ;
2361 end;
2362
2363 --调用
2364 declare
2365 aa number := 11 ;
2366 bb number := 22 ;
2367 ss number ;
2368 begin
2369 sum4(aa,bb,ss);
2370 dbms_output.put_line(ss) ;
2371 end;
2372
2373 5、多输出参数
2374 --创建
2375 create or replace procedure sum5(a number,b in number,s1 out number,s2 out number)
2376 as
2377 begin
2378 s1 := a + b ;
2379 s2 := a * b ;
2380 end;
2381
2382 --调用
2383 declare
2384 aa number := 2 ;
2385 bb number := 3 ;
2386 ss1 number ;
2387 ss2 number ;
2388 begin
2389 sum5(aa,bb,ss1,ss2);
2390 dbms_output.put_line(ss1) ;
2391 dbms_output.put_line(ss2) ;
2392 end;
2393
2394
2395 课堂作业
2396 1、输入员工编号，打印输出员工的姓名、职位、薪水，如果没有此员工，则进行异常处理，输出“对不起，不存在此用户”
2397 2、输入部门编号，返回该部门的员工人数
2398 3、输入部门编号，返回该部门最高薪水和最低薪水的员工姓名
2399
2400
2401 六、为用户授予执行存储过程的权限
2402 grant execute on 存储过程名 to 用户名;
2403
2404
2405 七、查看存储过程
2406 select * from user_objects;
2407 select name,line,text from user_source where name='HELLO';
2408
2409 八、删除存储过程
2410 drop procedure 存储过程名 ;
2411
2412
2413 ---------------------------------------------------------------
2414 函数
2415 一、概念
2416
2417
2418 二、语法
2419 create [or replace] function 函数名[(参数列表)]
2420 return 数据类型
2421 is|as
2422 变量/常量的定义
2423 begin
2424 函数具体实现的功能;
2425 return 结果;
2426 [exception
2427 异常处理;]
2428 end;
2429
2430 三、例子
2431 1、无参
2432 create or replace function fun_one return int
2433 as
2434 v_num number;
2435 begin
2436 select max(sal)-min(sal) into v_num from emp;
2437 return v_num;
2438 end;
2439
2440 --调用
2441 --SQL语句调用函数
2442 select fun_one from dual;
2443
2444 --使用PL/SQL块调用函数
2445 declare
2446 n number:=0;
2447 begin
2448 n:=fun_one();
2449 dbms_output.put_line('n:' || n);
2450 end;
2451
2452 2、有参
2453 create or replace function fun_two(eno number) return varchar2
2454 as
2455 v_ename emp.ename%type;
2456 begin
2457 select ename into v_ename from emp where empno=eno;
2458 return v_ename;
2459 exception
2460 when no_data_found then
2461 dbms_output.put_line('没有找到数据');
2462 return '';
2463 end;
2464
2465
2466 四、函数授权
2467 grant execute on 函数名 to 用户名
2468
2469
2470 五、查看
2471 user_objects,user_source
2472
2473 六、删除函数
2474 drop function 函数名
2475
2476 七、存储过程与函数的区别
2477 1、存储过程：
2478 1）作为 PL/SQL 语句执行
2479 2）在规格说明中不包含 RETURN 子句
2480 3）不返回任何值
2481 4）可以包含 RETURN 语句，但是与函数不同，它不能用于返回值
2482
2483 2、函数
2484 1）作为表达式的一部分供SQL调用
2485 2）必须在规格说明中包含 RETURN 子句
2486 3）必须返回单个值
2487 4）必须包含至少一条 RETURN 语句
2488
2489
2490
2491
2492
2493 */
2494
2495 create or replace procedure sayhello(b in number)
2496 as
2497 a number := 10 ;
2498 c number ;
2499 begin
2500 c := a-b ;
2501 dbms_output.put_line(c) ;
2502 end ;
2503
2504 begin
2505 sayhello(20);
2506 end ;
2507
2508
2509
2510
2511 --1、输入员工编号，打印输出员工的姓名、职位、薪水，如果没有此员工，则进行异常处理，输出“对不起，不存在此用户”
2512
2513 --第一
2514 create or replace procedure hao(b in number)
2515 as
2516
2517 v_name varchar2(30) ;
2518 v_job varchar2(30) ;
2519 v_sal number ;
2520 begin
2521 select ename,job,sal into v_name,v_job,v_sal from emp where empno=b ;
2522 dbms_output.put_line('姓名：'|| v_name || ' 工作：' || v_job || ' 薪水：' || v_sal) ;
2523
2524 exception
2525 when no_data_found then
2526 dbms_output.put_line('对不起，不存在此用户');
2527 end ;
2528
2529
2530 declare
2531 bianhao number ;
2532 begin
2533 bianhao :=&编号 ;
2534 hao(bianhao) ;
2535 end ;
2536
2537
2538 --第二
2539
2540 create or replace procedure haohao(b in number)
2541 as
2542
2543 obj emp%rowtype;
2544 begin
2545 select * into obj from emp where empno=b ;
2546 dbms_output.put_line('姓名：'|| obj.ename || ' 工作：' || obj.job || ' 薪水：' || obj.sal) ;
2547
2548 exception
2549 when no_data_found then
2550 dbms_output.put_line('对不起，不存在此用户');
2551 end ;
2552
2553
2554 declare
2555 bianhao number ;
2556 begin
2557 bianhao :=&编号 ;
2558 haohao(bianhao) ;
2559 end ;
2560
2561
2562
2563
2564
2565 --2、输入部门编号，返回该部门的员工人数
2566
2567 create or replace procedure bumen(b in number,s out number)
2568 as
2569
2570 v_people number ;
2571 begin
2572 select count(ename) into v_people from emp e,dept d where e.deptno=d.deptno and d.deptno=b ;
2573
2574 s:=v_people ;
2575
2576 end ;
2577
2578 declare
2579 bianhao number ;
2580 s number ;
2581 begin
2582 bianhao :=&编号 ;
2583 bumen(bianhao,s) ;
2584
2585 dbms_output.put_line('部门编号为'||bianhao||'的部门人数为:'||s) ;
2586 end ;
2587
2588
2589
2590
2591
2592 --3、输入部门编号，返回该部门最高薪水和最低薪水的员工姓名
2593
2594
2595 create or replace procedure bumen2(b in number)
2596 as
2597 max_name varchar2(30) ;
2598 min_name varchar2(30) ;
2599 max_sal number ;
2600 min_sal number ;
2601 begin
2602 select max(sal) into max_sal from emp e,dept d where e.deptno=d.deptno and d.deptno=b ;
2603 select ename into max_name from emp where sal= max_sal and deptno=b ;
2604
2605 select min(sal) into min_sal from emp e,dept d where e.deptno=d.deptno and d.deptno=b ;
2606 select ename into min_name from emp where sal=min_sal and deptno=b ;
2607
2608
2609 dbms_output.put_line('部门编号为'||b||'的最高薪水的姓名为:'||max_name||'薪水为：'||max_sal) ;
2610 dbms_output.put_line('部门编号为'||b||'的最低薪水的姓名为:'||min_name||'薪水为：'||min_sal) ;
2611 end ;
2612
2613 declare
2614 bianhao number ;
2615 begin
2616 bianhao :=&编号 ;
2617 bumen2(bianhao) ;
2618 end ;
2619
2620 select min(sal) from emp e,dept d where e.deptno=d.deptno and deptno=20 ;
2621
2622 select min(sal),ename into min_sal,min_name from emp where deptno=20 ;
2623
2624 select * from emp
