课程 一 PL/SQL 基本查询与排序
本课重点：
1、写SELECT语句进行数据库查询
2、进行数学运算
3、处理空值
4、使用别名ALIASES
5、连接列
6、在SQL PLUS中编辑缓冲，修改SQL SCRIPTS
7、ORDER BY进行排序输出。
8、使用WHERE 字段。
一、写SQL 命令：
不区分大小写。
SQL 语句用数字分行，在SQL PLUS中被称为缓冲区。
最后以；或 / 结束语句。
也可以用RUN来执行语句
二、例1：SQL> SELECT dept_id, last_name, manager_id
2 FROM s_emp;
2：SQL> SELECT last_name, salary * 12, commission_pct
2 FROM s_emp;
对于数值或日期型的字段，可以进行相应的四则运算，优先级与标准的高级语言相同。
SQL> SELECT last_name, salary, 12 * (salary + 100)
2 FROM s_emp;
三、列的别名ALIASES：
计算的时候特别有用；
紧跟着列名，或在列名与别名之间加“AS”；
如果别名中含有SPACE，特殊字符，或大小写，要用双引号引起。
例(因字体原因，读者请记住：引号为英文双引号Double Quotation)：
SQL> SELECT last_name, salary,
2 12 * (salary + 100) ”Annual Salary”
3 FROM s_emp;
四、连接符号：||
连接不同的列或连接字符串
使结果成为一个有意义的短语：
SQL> SELECT first_name || ’ ’ || last_name
2 || ’, ’|| title ”Employees”
3 FROM s_emp;
五、管理NULL值：
SQL> SELECT last_name, title,
2 salary * NVL(commission_pct,0)/100 COMM
3 FROM s_emp;
此函数使NULL转化为有意义的一个值，相当于替换NULL。
六、SQL PLUS的基本内容，请参考
1<sql plus="" 简单实用精髓篇="">
2
3七、ORDER BY 操作：
4
5与其他SQL92标准数据库相似，排序如：
6
7SELECT expr
8
9FROM table
10
11[ORDER BY {column,expr} [ASC|DESC]];
12
13从Oracle7 release 7.0.16开始，ORDER BY 可以用别名。
14
15另：通过位置判断排序：
16
17SQL> SELECT last_name, salary*12
18
192 FROM s_emp
20
213 ORDER BY 2;
22
23这样就避免了再写一次很长的表达式。
24
25另：多列排序：
26
27SQL> SELECT last name, dept_id, salary
28
292 FROM s_emp
30
313 ORDER BY dept_id, salary DESC;
32
33八、限制选取行：
34
35SELECT expr
36
37FROM table
38
39[WHERE condition(s)]
40
41[ORDER BY expr];
42
43例1：
44
45SQL> SELECT first_name, last_name, start_date
46
472 FROM s_emp
48
493 WHERE start_date BETWEEN ’09-may-91’
50
514 AND ’17-jun-91’;
52
53例2：
54
55SQL> SELECT last_name
56
572 FROM s_emp
58
593 WHERE last_name LIKE ’_a%’; //显示所有第二个字母为 a的last_name
60
61例3：
62
63如果有列为NULL
64
65SQL> SELECT id, name, credit_rating
66
672 FROM s_customer
68
693 WHERE sales_rep_id IS NULL;
70
71优先级：
72
73Order Evaluated Operator
74
751 All comparison operators (=, <>, >, >=, <, <=, IN, LIKE, IS NULL, BETWEEN)
76
772 AND
78
793 OR
80
81总结：我们今天主要学习了如何进行查询SELECT操作，具体的组合查询与子查询将在以后的课堂中学习，同时希望大家可以工作、学习中多多摸索，实践!
82
83课程 二 PL/SQL PL/SQL 查询行函数
84
85
86本课重点：
871、掌握各种在PL/SQL中可用的ROW函数
88
892、使用这些函数的基本概念
90
913、SELECT语句中使用函数
92
934、使用转换函数
94
95注意：以下实例中标点均为英文半角
96
97一、FUNCTION的作用：
98
99进行数据计算，修改独立的数据，处理一组记录的输出，不同日期显示格式，进行数据类型转换
100
101函数分为：单独函数(ROW)和分组函数
102
103注意：可以嵌套、可以在SELECT, WHERE, 和 ORDER BY中出现。
104
105语法：function_name (column|expression, [arg1, arg2,...])
106
107二、字符型函数
108
1091、LOWER 转小写
110
1112、UPPER
112
1133、INITCAP 首字母大写
114
1154、CONCAT 连接字符，相当于 ||
116
1175、SUBSTR SUBSTR(column|expression,m[,n])
118
1196、LENGTH 返回字符串的长度
120
1217、NVL 转换空值
122
123其中，1、2经常用来排杂，也就是排除插入值的大小写混用的干扰，如：
124
125SQL> SELECT first_name, last_name
126
1272 FROM s_emp
128
1293 WHERE UPPER(last_name) = ’PATEL’;
130
131FIRST_NAME LAST_NAME
132
133\-------------------- --------------------
134
135Vikram Patel
136
137Radha Patel
138
139三、数学运算函数
140
1411、ROUND
142
143四舍五入：ROUND(45.923，2) = 45.92
144
145ROUND(45.923，0) = 46
146
147ROUND(45.923，-1) = 50
148
1492、TRUNC
150
151截取函数
152
153TRUNC(45.923，2)= 45.92
154
155TRUNC(45.923)= 45
156
157TRUNC(45.923，-1)= 40
158
1593、MOD 余除
160
161MOD(1600，300)
162
163实例：
164
165SQL> SELECT ROUND(45.923,2), ROUND(45.923,0),
166
1672 ROUND(45.923,-1)
168
1693 FROM SYS.DUAL;
170
171四、ORACLE 日期格式和日期型函数：
172
1731、默认格式为DD-MON-YY.
174
1752、SYSDATE是一个求系统时间的函数
176
1773、DUAL［'dju:el] 是一个伪表，有人称之为空表，但不确切。
178
179SQL> SELECT SYSDATE
180
1812 FROM SYS.DUAL;
182
1834、日期中应用的算术运算符
184
185例：SQL> SELECT last_name, (SYSDATE-start_date)/7 WEEKS
186
1872 FROM s_emp
188
1893 WHERE dept_id = 43;
190
191DATE+ NUMBER = DATE
192
193DATE-DATE= NUMBER OF DAYS
194
195DATE + (NUMBER/24) = 加1小时
196
1975、函数：
198
199MONTHS_BETWEEN(date1, date2) 月份间隔，可正，可负，也可是小数
200
201ADD_MONTHS(date,n) 加上N个月，这是一个整数，但可以为负
202
203NEXT_DAY(date,‘char’) 如：NEXT_DAY (restock_date,’FRIDAY’),从此日起下个周五。
204
205ROUND(date[,‘fmt’])
206
207TRUNC(date[,‘fmt’])
208
209解释下面的例子：
210
211SQL> SELECT id, start_date,
212
2132 MONTHS_BETWEEN (SYSDATE,start_date) TENURE,
214
2153 ADD_MONTHS(start_date,6) REVIEW
216
2174 FROM s_emp
218
2195 WHERE MONTHS_BETWEEN (SYSDATE,start_date)<48;
220
221我们看到： MONTHS_BETWEEN (SYSDATE,start_date)<48，说明至今工作未满一年的员工。
222
223LAST_DAY (restock_date) 返回本月的最后一天
224
225SQL> select round(sysdate,'MONTH') from dual
226
227ROUND(SYSD
228
229\----------
230
23101-11月-01
232
233round(sysdate,'YEAR') = 01-1月 -02
234
235ROUND 之后的值比基值大的最小符合值，大家可以用更改系统时间的方法测试，以15天为分界线，也是非常形象的四舍五入，而TRUNC恰好相反，是对现有的日期的截取。
236
237五、转换函数：
238
2391、TO_CHAR
240
241使一个数字或日期转换为CHAR
242
2432、TO_NUMBER
244
245把字符转换为NUMBER
246
2473、TO_DATE
248
249字符转换为日期
250
251这几个函数较为简单，但要多多实践，多看复杂的实例。
252
253SQL> SELECT ID,TO_CHAR(date_ordered,’MM/YY’) ORDERED
254
2552 FROM s_ord
256
2573 WHERE sales_rep_id = 11;
258
259转换时，要注意正确的缺省格式：
260
261SELECT TO_DATE('03-MAR-92') CORRECT FROM DUAL；//正确
262
263SELECT TO_DATE('031092') CORRECT FROM DUAL；//不正确
264
265SELECT TO_DATE('031095','MMDDYY') ERRORR FROM DUAL
266
267输出 3月10日
268
269SELECT TO_DATE('031095','DDMMYY') ERRORR FROM DUAL
270
271输出 10月3日
272
2734、实例：
274
275select to_char(sysdate,'fmDDSPTH "of" MONTH YYYY AM') TODAYS FROM DUAL;
276
277TODAYS
278
279\--------------------------------
280
281SIXTEENTH of 11月 2001 下午
282
283大小写没有什么影响，引号中间的是不参与运算。
284
285实例 ：
286
287SELECT ROUND(SALARY*1.25) FROM ONE_TABLE；
288
289意义：涨25%工资后，去除小数位。在现实操作中，很有意义。
290
2915、混合实例：
292
293SQL> SELECT last_name, TO_CHAR(start_date,
294
2952 ’fmDD ”of” Month YYYY’) HIREDATE
296
2973 FROM s_emp
298
2994 WHERE start_date LIKE ’%91’;
300
301LAST_NAME HIREDATE
302
303\------------ --------------------
304
305Nagayama 17 of June 1991
306
307Urguhart 18 of January 1991
308
309Havel 27 of February 1991
310
311这里要注意：fmDD 和 fmDDSPTH之间的区别。
312
313SQL> SELECT id, total, date_ordered
314
3152 FROM s_ord
316
3173 WHERE date_ordered =
318
3194 TO_DATE(’September 7, 1992’,’Month dd, YYYY’);
320
321六、独立的函数嵌套
322
323SQL> SELECT CONCAT(UPPER(last_name),
324
3252 SUBSTR(title,3)) ”Vice Presidents”
326
3273 FROM s_emp
328
3294 WHERE title LIKE ’VP%’;
330
331* 嵌套可以进行到任意深度，从内向外计算。
332
333例：
334
335SQL> SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS
336
3372 (date_ordered,6),’FRIDAY’),
338
3393 ’fmDay, Month ddth, YYYY’)
340
3414 ”New 6 Month Review”
342
3435 FROM s_ord
344
3456 ORDER BY date_ordered;
346
347SQL> SELECT last_name,
348
3492 NVL(TO_CHAR(manager_id),’No Manager’)
350
3513 FROM s_emp
352
3534 WHERE manager_id IS NULL;
354
355对于例子，大家重要的理解，并多做测试，并注意英文版和中文版在日期上的区别。
356
357有些教材上的例子，不要盲目的相信其结果，实践后才有发言权，希望大家能够在学习的过程中不要忽略了用，
358
359多想一想为什么实例要如此设计，在何种情况下应用此实例来解决问题。这样，我们才真正掌握了知识。
360
361课程 三 从多个表中提取数据
362
363
364本课重点：
3651、SELECT FROM 多个表，使用等连接或非等连接
3662、使用外连接OUTER JOIN
3673、使用自连接
368注意：以下实例中标点均为英文半角
369一、连接的概念：
370
371是指一个从多个表中的数据进行的查询。连接一般使用表的主键和外键。
372连接类型：
373等连接、不等连接、外连接、自连接
374二、Cartesian product ：
375指的是当JOIN条件被省略或无效时，所有表的行(交叉)都被SELECT出来的现象。
376Cartesian product可以产生大量的记录，除非是你有意如此，否则应该加上某种条件限制。
377SQL> SELECT name, last_name
3782 FROM s_dept, s_emp;
379300 rows selected. 其中一个表12行，一个表25行。
380
381三、简单连接查询：
382SELECT table.column, table.column...
383FROM table1, table2
384WHERE table1.column1 = table2.column2;
385
386如：SQL> SELECT s_emp.last_name, s_emp.dept_id,
3872 s_dept.name
3883 FROM s_emp, s_dept
3894 WHERE s_emp.dept_id = s_dept.id;
390注意：表前缀的重要性：
391SQL> SELECT s_dept.id ”Department ID”,
3922 s_region.id ”Region ID”,
3933 s_region.name ”Region Name”
3944 FROM s_dept, s_region
3955 WHERE s_dept.region_id = s_region.id;
396在WHERE 段中，如果没有前缀，两个表中都有ID字段，就显得的模棱两可，AMBIGUOUS。
397这在实际中应该尽量避免。
398WHERE 字段中，还可以有其他的连接条件，如在上例中，加上：
399INITCAP(s_dept.last_name) = ’Menchu’;
400再如：WHERE s_emp.dept_id = s_dept.id AND s_dept.region_id = s_region.id AND s_emp.commission_pct > 0;
401
402四、表别名ALIAS：
4031、使用别名进行多表查询 。
4042、仅在这个查询中生效，一旦用了表别名，就不能再用表的原有的名字进行连接。
405实例：
406SQL> SELECT c.name ”Customer Name”,
4072 c.region_id ”Region ID”,
4083 r.name ”Region Name”
4094 FROM s_customer c, s_region r
4105 WHERE c.region_id = r.id;
411别名最多可以30个字符，但当然越少越好。最好也能容易识别。
412五、非等连接
413非等连接一般用在没有明确的等量关系的两个表；
414最简单的说：非等连接就是在连接中没有“=”出现的连接。
415SQL> SELECT e.ename, e.job, e.sal, s.grade
4162 FROM emp e, salgrade s
4173 WHERE e.sal BETWEEN s.losal AND s.hisal;
418说明：Create a non-equijoin to evaluate an employee’s salary grade. The salary 必须在另一个表中最高和最低之间。
419其他操作符<= >= 也可以实现，但是BETWEEN是非常简单实用的。
420BETWEEN ....AND是指闭区间的，这点要注意 ，请大家测试。
421六、外连接
422语法结构：SELECT table.column, table.column
423FROM table1, table2
424WHERE table1.column = table2.column(+);
425实例：
426SQL> SELECT e.last_name, e.id, c.name
4272 FROM s_emp e, s_customer c
4283 WHERE e.id (+) = c.sales_rep_id
4294 ORDER BY e.id;
430显示.....，即使有的客户没有销售代表。
431* 可以理解为有+号的一边出现了NULL，也可以做为合法的条件。
432外连接的限制：
4331、外连接符只能出现在信息缺少的那边。
4342、在条件中，不能用 IN 或者 OR做连接符。
435七、自连接
436同一个表中使用连接符进行查询；
437FROM 的后面用同一个表的两个别名。
438实例：
439SQL> SELECT worker.last_name||’ works for ’||
4402 manager.last_name
4413 FROM s_emp worker, s_emp manager
4424 WHERE worker.manager_id = manager.id;
443意味着：一个员工的经理ID匹配了经理的员工号，但这个像绕口令的连接方式并不常用。
444以后我们会见到一种 子查询：
445select last_name from s_emp where salary=(select max(salary) from s_emp)
446也可以看作是一种变向的自连接，但通常我们将其
447
448课程 四 组函数
449
450
451本课重点：
4521、了解可用的组函数
4532、说明每个组函数的使用方法
4543、使用GROUP BY
4554、通过HAVING来限制返回组
456注意：以下实例中标点均为英文半角
457一、概念：
458组函数是指按每组返回结果的函数。
459组函数可以出现在SELECT和HAVING 字段中。
460GROUP BY把SELECT 的结果集分成几个小组。
461HAVING 来限制返回组，对RESULT SET而言。
462二、组函数：(#号的函数不做重点)
4631、AVG
4642、COUNT
4653、MAX
4664、MIN
4675、STDDEV #
4686、SUM
4697、VARIANCE #
470语法：
471SELECT column, group_function
472FROM table
473[WHERE condition]
474[GROUP BY group_by_expression]
475[HAVING group_condition]
476[ORDER BY column];
477实例1：一个混合实例，说明所有问题：
478SQL> SELECT AVG(salary), MAX(salary), MIN(salary),
4792 SUM(salary)
4803 FROM s_emp
4814 WHERE UPPER(title) LIKE ’SALES%’;
482AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)
483\----------- ----------- ----------- -----------
4841476 1525 1400 7380
485说明：很多函数，我们在讲函数的已经向大家介绍过，但在此为何叫分组函数呢，主要是因为它们可以与GROUP BY来形成对不同组的计算，相当于在很多值中进行挑选。
486* MIN MAX函数可以接任何数据类型。
487如果是MIN(last_name), MAX(last_name)，返回的是什么呢？
488千万记住，不是指LAST_NAME的长度，而是指在FIRST字母的前后顺序，第一个相同，然后比较第二个，如：xdopt > cssingkdkdk > adopt > acccc
489实例2：
490SQL> SELECT COUNT(commission_pct)
4912 FROM s_emp
4923 WHERE dept_id = 31;
493返回所有非空行个数
494三、GROUP BY的应用：
495先看一个简单实例：
496SQL> SELECT credit_rating, COUNT(*) ”# Cust”
4972 FROM s_customer
4983 GROUP BY credit_rating;
499注意这里别名的应用，复习一下从前的课程，加了引号后，就可以用特殊字符，但也仅有三个：#$_，什么对象的名字都如此。当然空格也是可以的。
500复杂实例：
501SQL> SELECT title, SUM(salary) PAYROLL
5022 FROM s_emp
5033 WHERE title NOT LIKE ’VP%’
5044 GROUP BY title
5055 ORDER BY SUM(salary);
506这里要注意一下几个CLAUSE的先后次序。
507WHERE在这里主要是做参与分组的记录的限制。
508**另外，如果要选取出来一个不加组函数的列，如上面的TITLE，就要把这个列GROUP BY !否则要出错的!信息为：ERROR at line 1:
509ORA-00937: not a single-group group function
510理论很简单，如果不GROUP BY TITLE，显示哪一个呢？这个在试题中经常出现。
511结论：不加分组函数修饰的列必定要出现在GROUP BY 里。
512错误实例：
513SQL> SELECT dept_id, AVG(salary)
5142 FROM s_emp
5153 WHERE AVG(salary) > 2000
5164 GROUP BY dept_id;
517
518WHERE AVG(salary) > 2000
519*
520ERROR at line 3:
521ORA-00934: group function is not allowed here
522应在GROUP BY 后面加上HAVING AVG(salary) > 2000;
523因为是用来限制组的返回。
524多级分组实例：
525SQL> SELECT dept_id, title, COUNT(*)
5262 FROM s_emp
5273 GROUP BY dept_id, title;
528就是先按照DEPT_ID分组，当DEPT_ID相同的时候，再按TITLE分组，而COUNT(*)以合成的组计数。
529顺序对结果有决定性的影响。
530
531总结：本课我们主要学习了分组函数的使用及如何进行分组查询，我们可以想像一下，SQL SERVER中有COMPUTE BY，来进行分组总数的计算，但在ORACLE中是没有的。大家可以建立一个有多个列，多个重复值的表，然后进行各种分组的演示，用得多了，自然明了。
532
533
534
535课程 五 子查询
536
537
538本课重点：
5391、在条件未知的情况下采用嵌套子查询
5402、用子查询做数据处理
5413、子查询排序
542
543注意：以下实例中标点均为英文半角
544
545一、概述：
546子查询是一种SELECT句式中的高级特性，就是一个SELECT语句作为另一个语句的一个段。我们可以利用子查询来在WHERE字段中引用另一个查询来攻取值以补充其无法事先预知的子结果。
547子查询可以用在WHERE子句，HAING子句，SELECT或DELETE语句中的FROM 子句。
548注意：1、子查询必须在一对圆括号里。
5492、比较符号：>, =, 或者 IN.
5503、子查询必须出现在操作符的右边
5514、子查询不能出现在ORDER BY里 (试题中有时出现找哪行出错)
552二、子查询的执行过程：
553NESTED QUERY MAIN QUERY
554SQL> SELECT dept_id SQL> SELECT last_name, title
5552 FROM s_emp 2 FROM s_emp
5563 WHERE UPPER(last_name)=’BIRI’; 3 WHERE dept_id =
557这里 ，每个查询只运行一次。当然，子查询要首先被执行，大家设想一下，如果子查询中有一个以上的人的LASTNAME为BIRI，会如何？-----会出错，因为不能用=来连接。
558ORA-1427: single-row subquery returns more than
559one row
560以上的查询也被称之为 单行子查询。
561DELECT子查询实例：
562delete from new_table where cata_time > to_date('19990901','yyyymmdd') and pro_name=(
563select pro_name from new_product where pro_addr in ('bj','sh'))
564三、子查询中的GROUP 函数的应用
565实例 1：
566SQL> SELECT last_name, title, salary
5672 FROM s_emp
5683 WHERE salary <
5694 (SELECT AVG(salary)
5705 FROM s_emp);
571实例2：
572选择出工资最高的员工的家庭住址：
573select emp_addr from employees where salary =
574(select max(salary) from employees);
575这是一个简单实用的例子，可以衍生出很多情况，在实际应用经常出现，请大家多多思考。
576实例3：
577SQL> SELECT dept_id, AVG(salary)
5782 FROM s_emp
5793 GROUP BY dept_id
5804 HAVING AVG(salary) >
5815 (SELECT AVG(salary)
5826 FROM s_emp
5837 WHERE dept_id = 32);
584子查询被多次执行，因为它出现在HAVING 子句中。
585SQL> SELECT title, AVG(salary)
5862 FROM s_emp
5873 GROUP BY title
5884 HAVING AVG(salary) =
5895 (SELECT MIN(AVG(salary))
5906 FROM s_emp
5917 GROUP BY title);
592对子查询，我们了解这么多在理论上已经覆盖了所有的知识点，对于UPDATE 和DELETE的子查询，不作为重点，但也要练习掌握。今天到这，谢谢大家。
593
594
595
596课程 六 运行时应用变量
597
598
599本课重点：
600
6011、创建一个SELECT语句，提示USER在运行时先对变量赋值。
602
6032、自动定义一系列变量，在SELECT运行时进行提取。
604
6053、在SQL PLUS中用ACCEPT定义变量
606
607注意：以下实例中标点均为英文半角
608
609一、概述：
610
611变量可以在运行时应用，变量可以出现在WHERE 字段，文本串，列名，表名等。
612
6131、我们这里的运行时，指的是在SQL PLUS中运行。
614
6152、ACCEPT ：读取用户输入的值并赋值给变量
616
6173、DEFINE：创建并赋值给一个变量
618
6194、在做REPORT时经常使用，比如对某个部门的销售信息进行统计，部门名称可以以变量代替。
620
621SQL PLUS不支持对输入数据的有效性检查，因此提示要简单且不模棱两可。
622
623二、应用实例：
624
6251、SQL> SELECT id, last_name, salary
626
6272 FROM s_emp
628
6293 WHERE dept_id = &department_number;
630
6312、可以在赋值前后进行比较：
632
633SET VERIFY ON
634
635.....
636
6371* select * from emp where lastname='&last_name'
638
639输入 last_name 的值: adopt
640
641原值 1: select * from emp where lastname='&last_name'
642
643新值 1: select * from emp where lastname='adopt'
644
645\----如果在原语句中没有单引号，那么在输入值的时候要手工加上单引号。一般字符和日期型要在语句中加上单引号。
646
647SET VERIFY OFF 之后，原值和新值这两句消失。这在ORACLE8I中是默认为ON。
648
6493、子句为变量：WHERE &condition 要注意引号
650
651三、DEFINE和ACCEPT的应用：
652
6531、SET ECHO OFF //使内容不 显示在用户界面
654
655ACCEPT p_dname PROMPT ’Provide the department name: ’
656
657SELECT d.name, r.id, r.name ”REGION NAME”
658
659FROM s_dept d, s_region r
660
661WHERE d.region_id = r.id
662
663AND UPPER(d.name) LIKE UPPER(’%&p_dname%’)
664
665/
666
667SET ECHO ON
668
669存为文件：l7prompt.SQL
670
671SQL> START l7prompt
672
673Provide the department name: sales
674
6752、SQL> DEFINE dname = sales
676
677SQL> DEFINE dname
678
679DEFINE dname = ”sales” (CHAR)
680
681SQL> SELECT name
682
6832 FROM s_dept
684
6853 WHERE lower(name) = ’&dname’;
686
687可以正常执行了。
688
689SQL> DEFINE dname 主要是显示当前的变量是否赋值，值是什么。当然，我们可以用UNDEFINEGO 来使变量恢复初始，不然它会一直保持下去。
690
6913、如果变量在SQL SCRIPT文件中确定 ：可以SQL> START l7param President 来赋值。
692
693总结：本课主要针对较古老的SQLPLUS方法，在REPORT和结果集生成方面使用变量，达到方便操作，动态修改的目的。
694
695
696课程 七 其他数据库对象
697
698
699SEQUENCE
700
701创建实例：
702
703SQL> CREATE SEQUENCE s_dept_id
704
7052 INCREMENT BY 1
706
7073 START WITH 51
708
7094 MAXVALUE 9999999
710
7115 NOCACHE
712
7136 NOCYCLE;
714
715Sequence created.
716
7171、NEXTVAL和CURRVAL的用法
718
719只有在INSERT 中，才可以作为子查询出现。
720
721以下几个方面不可用子查询：
722
723SELECT 子句OF A VIEW
724
725有DISTINCT的出现的SELECT。
726
727有GROUP BY，HAVING，ORDER BY的SELECT 子句。
728
729SELECT 或DELETE，UPDATE 中的子查询。
730
731DEFAULT选项中不能用。
732
7332、编辑SEQUENCE
734
735只有OWNER或有ALTER权限的用户才能修改SEQUENCE
736
737未来的NUMBER受修改的影响。
738
739不能修改START WITH，如果变，则要RE－CREATE。
740
741修改会受到某些有效性检验的限制，如MAXVALUE
742
7433、删除：
744
745DROP SEQUENCE sequence;
746
747ORACLE对象之INDEX
748
749一、INDEX概述：
750
751是ORACLE的一种数据对象，用POINTER来加速查询行。通过快速路径存取方法定位数据并减少I/O。 INDEX独立于表。INDEX由ORACLE SERVER来使用和保持。
752
753二、索引如何建立？
754
7551、自动：通过PRIMARY KEY和UNIQUE KEY约束来建立。
756
7572、用户手工建立非唯一性索引。
758
759三、创建方法：
760
761语法：CREATE INDEX index
762
763ON table (column[, column]...);
764
765何时建立INDEX：
766
767此列经常被放到WHERE字段或JOIN来作条件查询。
768
769此列含有大量的数据。
770
771此列含有大量的空值。
772
773两个或几个列经常同时放到WHERE字段进行组合查询
774
775表很大而且只有少于2-4% 的ROW可能被查询的时候。
776
777以下情况不要建立索引：
778
779表很小；
780
781表被更新频繁。
782
783四、查看已经存在的索引：
784
7851、USER_INDEXES可以查询索引名和类型。
786
7872、USER_IND_COLUMNS包含索引名、表名、列名。
788
789实例：
790
791SQL> SELECT ic.index_name, ic.column_name,
792
7932 ic.column_position col_pos, ix.uniqueness
794
7953 FROM user_indexes ix, user_ind_columns ic
796
7974 WHERE ic.index_name = ix.index_name
798
7995 AND ic.table_name = ’S_EMP’;
800
801五、删除索引：
802
803DROP INDEX index;
804
805SYNONYMS 同义词
806
807语法：
808
809CREATE [PUBLIC] SYNONYM synonym for object;
810
811注意：此对象不能包含在一个包里；
812
813一个私有的同义词不能与同一USER的其他对象重名。
814
815DROP SYNONYM D_SUM；
816
817
818课程 八 用户访问控制
819
820本课重点：
8211、创建用户
8222、创建角色来进行安全设置
8233、使用GRANT或REVOKE 来控制权限
824
825注意：以下实例中标点均为英文半角
826
827一、概述：
828ORACLE通过用户名和密码进行权限控制。
829数据库安全：系统安全和数据安全
830系统权限：使用户可以访问数据库
831对象权限：操纵数据库中的对象
832SCHEMA：各种对象的集合
833二、系统权限：
8341、超过80个权限可用。
8352、DBA有最高的系统权限：
836CREATE NEW USER
837REMOVE USERS
838REMOVE ANY TABLE
839BACKUP ANY TABLE
840三、创建用户
8411、CREATE USER user IDENTIFIED BY password;
8422、系统权限：CREATE SESSION Connect to the database.
843CREATE TABLE Create tables in the user’s schema.
844CREATE SEQUENCE Create a sequence in the user’s schema.
845CREATE VIEW Create a view in the user’s schema.
846CREATE PROCEDURE Create a stored procedure, function, or package in
847the user’s schema.
8483、授权用户系统权限：
849GRANT privilege [, privilege...] TO user [, user...];
850GRANT CREATE TABLE TO SCOTT；
851四、角色的使用
8521、概念：角色是一组权限的命名，可以授予给用户。这样就如同给了某个用户一个权限包。
8532、创建、授予给角色：
854CREATE ROLE MANAGER；
855GRANT CREATE TABLE，CREATE VIEW TO MANAGER；
856GRANT MANAGER TO CLARK
857五、修改密码：
858ALTER USER user IDENTIFIED BY password;
859六、对象权限：
8601、语句：
861GRANT {object_priv(, object_priv...)|ALL}[(columns)]
862ON object
863TO {user[, user...]|role|PUBLIC}
864[WITH GRANT OPTION];
8652、实例：
866最简单：
867SQL> GRANT select
8682 ON s_emp
8693 TO sue, rich;
870稍复杂：
871SQL> GRANT update (name, region_id)
8722 ON s_dept
8733 TO scott, manager;
874SQL> GRANT select, insert
8752 ON s_dept
8763 TO scott
8774 WITH GRANT OPTION;
878
879课程 九 声明变量
880
881本课重点：
8821、了解基本的PLSQL块和区域
8832、描述变量在PLSQL中的重要性
8843、区别PLSQL与非PLSQL变量
8854、声明变量
8865、执行PLSQL块
887
888注意：以下实例中标点均为英文半角
889
890一、概述：
8911、PLSQL 块结构：
892DECLARE --- 可选
893变量声明定义
894BEGIN ---- 必选
895SQL 和PLSQL 语句
896EXCEPTION ---- 可选
897错误处理
898END；---- 必选
899二、实例：
900declare
901vjob varchar(9);
902v_count number:=0;
903vtotal date:=sysdate +7;
904c_tax constant number(3,2):=8.25;
905v_valid boolean not null:=true;
906begin
907select sysdate into vtotal from dual;
908end;
909/
910上例中，如果没有这个SELECT语句，会如何？
911出错，说明必须有STATEMENTS
912如果： select sysdate from dual into vtotal ;
913同样，也不行。而且变量与赋值的类型要匹配。
914三、%TYPE的属性
915声明一个变量使之与数据库某个列的定义相同或与另一个已经定义过的变量相同
916所以%TYPE要作为列名的后缀：如：
917v_last_name s_emp.last_name%TYPE;
918v_first_name s_emp.first_name%TYPE; --这样做的好处是我们不必去知晓此列的类型与定义
919或：v_balance NUMBER(7,2);
920v_minimum_balance v_balance%TYPE := 10;
921四、声明一个布尔类型的变量
9221 只有TRUE、FALSE、NULL可以赋值给BOOLEAN变量
9232 此变量可以接逻辑运算符NOT、AND、OR。
9243、变量只能产生TRUE、FALSE、NULL。
925实例：
926VSAL1：=50000；
927VSQL2：=60000；
928VCOMMSAL BOOLEAN：=(VSAL1<vsql2)； \--其实是把true赋值给此变量。="" sql="" variables="" 五、lob="" 六：使用host="" 共有clob、blob、bfile、nclob几种，这里不做为重点。="" 类型的变量=""> variable n number
929SQL> print n
930：n=v_sal /12;
931:n这个加了：前缀的变量不是PLSQL变量，而是HOST。
932七、以下几个PLSQL声明变量，哪个不合法？
933A 、DECLARE
934V_ID NUMBER(4)；
935B、DECLARE
936V_X，V_Y，V_Z VARCHAR2(9)；
937C、DECLARE
938V_BIRTH DATE NOT NULL；
939D、DECLARE
940V_IN_STOCK BOOLEAN：=1；
941E、DECLARE
942TYPE NAME_TAB IS TABLE OF VARCHAR2(20)
943INDEX BY BINARY_INTEGER；
944DEPT_NAME NAME_TAB；
945上面的习题我会在下章给出答案，这也正是声明变量的规则和难点。
946
947
948
949课程 十 写执行语句
950
951本课重点：
9521、了解PLSQL执行区间的重要性
9532、写执行语句
9543、描述嵌套块的规则
9554、执行且测试PLSQL块
9565、使用代码惯例
957
958注意：以下实例中标点均为英文半角
959
960一、PLSQL 块的语法规则：
9611、语句可以跨跃几行。
9622、词汇单元可以包括：分隔符、标识符、文字、和注释内容。
9633、分隔符：
964+-*/=<>||....
9654、标识符：
966最多30个字符，不能有保留字除非用双引号引起。
967字母开头，不与列同名。
9685、文字串：如 V_ENAME:='FANCY';要用单引号括起来。
969数值型可以用简单记数和科学记数法。
9706、注释内容：单行时用-- 多行用/* */
971与C很相似
972二、SQL函数在PL/SQL的使用：
9731、可用的：
974单行数值型、字符型和转换型，日期型。
9752、不可用的：
976最大、最小、DECODE、分组函数。
977实例：
978BEGIN
979SELECT TO_CHAR(HIREDATE,'MON,DD,YYYY') FROM EMP;
980END;
981V_comment:=user||':'||sysdate; -- 会编译出错
982V_comment:=user||':'||to_char(sysdate); --正确
983如果有可能，PLSQL都会进行数据一致性的转换，但ORACLE推荐你应该进行显示的转换，因为这样会提高性能。
984三、嵌套块和变量作用区域
9851、执行语句允许嵌套时嵌套。
9862、嵌套块可以看作正常的语句块。
9873、错误处理模块可以包括一个嵌套块
9884、exponential指数 逻辑、算数、连接、小括号
9895、看正面实例：
990declare
991vjob varchar(9);
992v_count number:=0;
993vtotal date:=sysdate +7;
994c_tax constant number(3,2):=8.25;
995v_valid boolean not null:=true;
996ttt vtotal%type;
997begin
998\--select sysdate into vtotal from dual;--体会有无此句与结果的影响
999dbms_output.put_line (vtotal);
1000end;
1001/
1002注意：在执行块之前，要在SQL PLUS中执行：SET SERVEROUTPUT ON
1003三、以实例来说明函数的参数声明作用域
1004declare
1005v_weight number(3):=600;
1006v_message varchar2(255):='product10000';
1007begin
1008declare
1009\--sub-block
1010v_weight number(3):=1;
1011v_message varchar2(255):='pro300';
1012begin
1013v_weight:=v_weight +1;
1014end;
1015v_weight:=v_weight +1;
1016v_message:=v_message || 'my name';
1017end;
1018/
1019子块中的V_WEIGHT值为 2
1020我们可以在子块中加入：dbms_output.put_line('subblock value is '||v_weight);
1021在主体中加入：dbms_output.put_line('main value is '||v_weight);
1022我们发现MAINBLOCK中V_WEIGHT为 601
1023改动：
10241、在主块的声明中加 v_date date default sysdate;
1025在子块中加入：dbms_output.put_line('subblock date value is '||v_date);
1026执行结果：subblock date value is 22-11月-01
1027****说明：主块中的变量，如果子块中没有同名变量声明，则继承主块中的声明和初始化值；
10282、在子块中加入：v_sub char(9);
1029dbms_output.put_line('subblock char value is '||v_sub);
1030此时正常输出。
1031在主块中加入：dbms_output.put_line('main char value is '||v_sub);
1032输出：ORA-06550: 第 21 行, 第 45 列:
1033PLS-00201: 必须说明标识符 'V_SUB'
1034说明：
1035子块中声明的变量主块中并不知晓，因此出错。
1036了解了此实例，一切情况的变量的值的走向就都明了了。
1037
1038
1039课程 十一 与ORACLE SERVER交互
1040
1041本课重点：
10421、在PLSQL中成功的写SELECT语句
10432、动态声明PLSQL变量类型与SIZE
10443、在PLSQL中写DML语句
10454、在PLSQL中控制事务
10465、确定DML操作的结果
1047
1048注意：以下实例中标点均为英文半角
1049
1050一、PLSQL中的SQL语句：
1051SELECT、DML、COMMIT、ROLLBACK、SAVEPOINT、CURSOR
1052特殊强调：PLSQL不支持DCL，不要问为什么。(DBMS_SQL package allows you to issue DDL and DCL statements.)
1053二、SELECT
1054SELECT select_list
1055INTO variable_name | record_name
1056FROM table
1057WHERE condition;
1058例：
1059SQL> r
10601 declare
10612 v_deptno number(2);
10623 v_loc varchar2(15);
10634 begin
10645 select deptno,loc
10656 into v_deptno,v_loc
10667 from dept
10678 where dname='SALES';
10689 DBMS_OUTPUT.PUT_LINE (V_deptno ||' and '||v_loc);
106910* end;
107030 and CHICAGO
1071选取字段与变量个数和类型要一致。声明的变量一定要在SIZE上大于返回的赋值，否则提示缓冲区溢出。
1072如果SELECT语句没有返回值：ORA-01403: 未找到数据
1073ORA-06512: 在line 5
1074如果有多个值返回：ORA-01422: 实际返回的行数超出请求的行数
1075这些我们到了错误处理时会逐一讲解。
1076例：
1077上面的例子可以改为：
1078declare
1079v_deptno dept.deptno%type;
1080v_loc dept.loc%type;
1081begin
1082select deptno,loc
1083into v_deptno,v_loc
1084from dept
1085where dname='SALES';
1086DBMS_OUTPUT.PUT_LINE (V_deptno ||' and '||v_loc);
1087end;
1088/
1089这样，可以在未知其他字段大小和类型的时候定义变量，提高兼容性。
1090三、DML 操作：
10911、实例：
1092declare
1093&</vsql2)；></sql>