课件及补充达内代码

TTS

4.0Oracle

SQL

(day

5)2010年12月日程（上午）09:30~10:40••CORE-F-018CORE-F-01910:50~12:00CORE-F-019日程（下午）13:30~14:30CORE-F-02014:40~15:40CORE-F-02115:50~16:50CORE-F-022总结、讲评和答疑CORE-F-018索引（INDEX）创建索引:语法在一列或多列上创建索引CREATE

INDEX

indexON

table

(column[,

column]...);例子:在表emp上的ename列上创建索引SQL>

CREATE

INDEX

emp_ename_idx2

ON

emp(ename);Indexcreated.表扫描的方式全表扫描FTS（FullTableScan）高水位线：曾经包含数据的最右边的块将扫描高水位线以下的所有数据块通过rowid来扫描数据Rowid：标识一条记录的物理位置包含如下信息：该记录属于哪张表的（哪个数据库对象）：object_id该记录在哪个数据文件里：file_id该记录在数据文件的第几个数据块里：block_id该记录在数据块里是第几条记录：row_id哪些列适合建索引经常出现在WHERE子句的列经常用于表连接的列该列包含许多NULL值表很大，查询的结果集小主键（PK）列、唯一键（UK）列外键（FK）列经常需要排序（ORDER

BY）和分组（GROUP

BY）的列索引不是万能的哪些列不适合建索引小表列很少出现在WHERE子句查询的结果集也大该列被反复更新哪些写法会导致索引用不了函数导致索引用不了Where

upper(first_name)

=

‘CARMEN’表达式导致索引用不了Where

salary*12

=

12000部分隐式数据类型导致索引用不了Where

c1=2

(c1为varchar2类型)LikeWhere

first_name

like

‘CA%’哪些写法会导致索引用不了否定形式导致索引用不了Where

first_name

<>

‘CARMEN’Where

salary

not

between

1000

and

2000Where

dept_id

not

in

(32,34,42)Is

null导致索引用不了Where

comm

is

nullCORE-F-019视图（VIEW）表EMPLOYEES

:视图控制数据访问简化查询数据独立性避免重复访问相同的数据为什么使用视图创建视图创建视图的语法SQL>

CREATE

[OR

REPLACE]

[FORCE|NOFORCE]

VIEWview_name[(alias[,

alias]...)]ASsubquery创建视图的例子CREATE

VIEW

emp20ASSELECT empno,

ename,

salFROM

empWHERE deptno

=

20;查看视图的结构SQL>

DESCemp20查询视图查询视图的内容SQL>

SELECT

*

FROM

emp20;删除视图（删除视图只是删除视图的定义，并不会删除基表的数据）创建复杂视图SQL>

CREATE

OR

REPLACE

VIEW

dept_sum(name,avgsal)ASSELECT d.dname,

AVG(e.sal)FROMWHEREempe,dept

de.deptno

=

d.deptnoGROUP

BY

d.dname;创建复杂视图SQL>

DROP

VIEWdept_sum;CORE-F-020FROM后面跟子查询（INlLINE

VIEWS）例子哪些员工的工资比本部门的平均工资高？SQL>

SELECTe.ename,

e.deptno,

e.sal2

FROM empe

JOIN345ONAND(SELECT

deptno

,

avg(sal)

avgsalFROM

empGROUP

BY

deptno)

ae.deptno

=

a.deptnoe.sal

>a.sal;ROWNUM的含义ROWNUM是一个伪列，对查询返回的行编号即行号，由1开始依次递增WHERE

ROWNUM<=5的执行过程Oracle获取第一个符合条件的行，将它叫做第1行有5行了吗？如果没有，oracle就再返回行，因为它要满足行号小于等于5的条件，如果到了5行，那么，oracle就不再返回行Oracle获取下一行，并递增行号（从2，到3，再到4，等等）返回到第2步关键点：Oracle的rownum数值是在获取每行之后才赋予的排名问题排名问题返回的是某列最大或最小的n个值结构如下：SELECT [column_list],

ROWNUMFROM (SELECT

[column_list]FROM

tableORDER

BYTop-N_column)WHERE

ROWNUM

<=

NSELECT

ename,

sal,

ROWNUMFROM(SELECT

ename,salFROM

empORDER

BY

sal

desc)WHERE

ROWNUM

<=

5;分页问题分页问题返回的是第m条到第n条记录结构如下：（按某个字段排序后第m到n条的记录）SELECT [column_list],

rnFROM(SELECTROWNUM

rn,

[column_list]FROM

(SELECT

*

FROM

emp

ORDER

BY

column

)

a)WHERErn

BETWEENM

AND

NSELECT

ename,

salFROM(SELECT

a.*,

rownum

rn

FROM(SELECT*

FROM

emp

ORDER

BY

sal)

a)WHERErn

BETWEEN4

AND

8;CORE-F-021序列号（SEQUENCE）什么是序列号（SEQUENCE）能自动产生唯一值是个可共享的数据库对象主要用于产生主键值可以减少应用程序代码量序列号：语法创建sequence的语法：CREATE

SEQUENCE

name[INCREMENT

BY

n][START

WITH

n][{MAXVALUEn

|

NOMAXVALUE}][{MINVALUE

n

|

NOMINVALUE}][{CYCLE

|

NOCYCLE}]

[{CACHEn

|

NOCACHE}]序列号：例子为dept表的deptno列创建一个sequence：SQL>

CREATE

SEQUENCE

s_dept_noINCREMENT

BY

1START

WITH

51MAXVALUE

9999999NOCACHENOCYCLE;Sequence

created.NEXTVAL和CURRVAL伪列nextval返回的下一个可得到的序列号（sequence）的值每次返回的都是一个唯一的值currval返回的是当前session已经取到的值在执行currval前，必须先执行nextval序列号:用法部门号deptno的值用序列号（sequence）s_dept_no产生SQL>INSERT

INTO dept(deptno,

dname,loc)2

VALUES (s_dept_no.NEXTVAL,

'Finance',

‘beijing’);1

row

created.查看在当前session下，s_dept_no的当前取值是多少SQL>SELECT2

FROMs_dept_no.CURRVALSYS.dual;序列号的使用如果使用了cache子句，系统将序列号（sequence）的多个值缓存到内存（共享池shared

pool）中，将提高执行

nextval的效率如下情况会使序列号的值不连续回滚发生系统失败同一个序列号用于多张表修改序列号：语法修改sequence的语法（序列修改以前产生的值不受影响）ALTER

SEQUENCE

sequence[INCREMENT

BY

n][{MAXVALUEn

|

NOMAXVALUE}][{MINVALUE

n

|

NOMINVALUE}][{CYCLE

|

NOCYCLE}][{CACHEn

|

NOCACHE}]删除序列号：语法删除sequence的语法：SQL>

DROP

SEQUENCEs_dept_id;Sequence

dropped.CORE-F-022PL/SQL编程PL/SQL是Procedure

Language

&

StructuredQuery

Language

的缩写PL/SQL是对SQL语言存储过程语言的扩展什么是PL/SQLDatabaseDatabaseSQLSQLSQLSQLSQLIF...THENSQLELSESQLEND

IF;SQL应用程序应用程序PL/SQL的目的：提高性能PL/SQL程序由三个块组成即声明部分执行部分异常处理部分PL/SQL块结构和组成元素ORACLE提供可以把PL/SQL程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。过程和函数统称为PL/SQL子程序，他们是被命名的

PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则可以返回或不返回数据。函数和过程CREATE

[OR

REPLACE]

FUNCTION

function_name[

(argment[

{

IN

|

OUT

|

IN

OUT

}

]

Type

，argment

[

{

IN

|

OUT

|

IN

OUT

}

]

Type

]RETURN

return_type{

IS|

AS}<类型.变量的说明>BEGINFUNCTION_bodyEXCEPTION其它语句END;注意:这里的type只能是类型,不能有长度或大小的定义,比如varchar2而非varchar2(20)创建函数CREATE

OR

REPLACE

FUNCTION

get_salary(Dept_no

NUMBER,

Emp_count

OUT

NUMBER)RETURN

NUMBERISV_sum

NUMBER;BEGINSELECT

SUM(sal),

count(*)

INTO

V_sum,

emp_countFROM

emp

WHERE

deptno=dept_no;RETURN

v_sum;EXCEPTIONWHEN

NO_DATA_FOUND

THENDBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');WHEN

OTHERS

THENDBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);END

get_salary;创建函数：例子借助匿名块来调用函数：DECLAREV_num

NUMBER;V_sum

NUMBER;BEGINV_sum

:=get_salary(30,

v_num);DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||'，人数：'||v_num);END;调用函数的方法：例子CREATE

[OR

REPLACE]

PROCEDURE

Procedure_name[

(argment

[

{

IN

|

OUT

|

IN

OUT

}

]

Type,argment

[

{

IN

|

OUT

|

IN

OUT

}

]

Type

]{

IS

|

AS

}<类型.变量的说明>BEGIN<执行部分>EXCEPTION<可选的异常错误处理程序>END;存储过程CREATE

OR

REPLACE

PROCEDURE

DelEmp(v_empno

IN

emp.empno%TYPE)ASNo_result

EXCEPTION;BEGINDELETE

FROM

emp

WHERE

empno=v_empno;IF

SQL%NOTFOUND

THENRAISE

no_result;END

IF;DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已被除名!');EXCEPTIONWHEN

no_result

THENDBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');WHEN

OTHERS

THENDBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);END

DelEmp;存储过程：例子CREATE

OR

REPLACE

PROCEDURE

proc_demo(Dept_no

NUMBER

DEFAULT

10

,

Sal_sum

OUT

NUMBER,Emp_count

OUT

NUMBER)ISBEGINSELECT

SUM(sal),

COUNT(*)

INTO

sal_sum,