Oracle PL SQL 编程手册(SQL大全)

OraclePLSQL编程手册（SQL大全）

一、SQLPLUS

1引言

SQL命令

以下17个是作为语句开头的关键字：

alterdroprevoke

auditgrantrollback*

commit*insertselect

commentlockupdate

createnoauditvalidate

deleterename

这些命令必须以结尾

带*命令句尾不必加分号，同时不存入SQL缓存区。

SQL中没有的SQL*PLUS命令

这些命令不存入SQL缓存区

@definepause

#delquit

$describeremark

/disconnectrun

acceptdocumentsave

appendeditset

breakexitshow

btitlegetspool

changehelpsqlplus

clearhoststart

columninputtiming

computelistttitle

connectnewpageundefine

copy

2数据库查询

数据字典

TAB用户创建的所有基表、视图和同义词清单

DTAB构成数据字典的所有表

COL用户创建的基表的所有列定义的清单

CATALOG用户可存取的所有基表清单

select*fromlab;

describe命令描述基表的结构信息

describedept

select*

fromcmp;

selectcmpno,cnamc,job

fromemp;

select*fromdept

orderbydeptnodesc;

逻辑运算符

=!=或<>>>=<<=

in

betweenvalueIandvalue2

like

%

innull

not

noin,isnotnull

谓词in和notin

有哪些职员和分析员

selectename,job

fromemp

wherejobin('clerk','analyst');

selectename,job

fromemp

wherejobnotin('clerk',analyst');

谓词between和notbetween

哪些雇员的工资在2000和3000之间

selectenamejob.salfromemp

wheresalbetween2000and3000;

selectename,job,salfromemp

wheresalnotbetween2000and3000;

谓词like.notlike

selectename,deptnofromemp

whereenamelike'S%';

（以字母S开头）

selectename,deptnofromemp

whereenameHkc%K';

（以K结尾）

selectcnanic,dcptnofromemp

whereenamelike'W__

（以W开头，后面仅有二个字母）

selectenamejobfromemp

wherejobnotlike'sales%';

（哪些雇员的工种名不以sales开头）

谓词isnull,isnotnull

没有奖金的雇员（即commision为null）

selectenamejobfromemp

wherecommisnull;

selectenamejobfromemp

wherecommisnotnull;

多条件查询

selectenamejob

fromemp

wheredeptno=20

andjob!-clerk1;

表达式

+-*/

算术表达式

选择奖金高于其工资的5%的雇员

selectename,sal,comm,comm/salfromemp

wherecomm>.05*sal

orderbyconim/saldcsc;

日期型数据的运算

addtwodaysto6-Mar-87

6-Mar-87+2=8-Mar-87

addtwohoursto6-Mar-87

6-Mar-87+2/24=6-Mar-87and2hrs

add15secondsto6-Mar-87

6-Mar-87+15/(24*60*60)=6-Mar-87and15secs

列名的别名

selectenameemployeefromemp

wheredeptno=10;

(别名：employee)

selectename,saUcomm,comin/sal"C/SRATIO"fromcmp

wherecomm>.05*sal

orderbycomm/saldesc;

SQL命令的编辑

listorI显示缓冲区的内容

list4显示当前SQL命令的第4行，并把笫4行作为当前行，在该行号后面有个九

changeorc用新的内容替换原先在一行中第一次显现内容

SQL>c/(...)/('analyst,)/

inputori增加一行或多行

叩pendora在一行后追加内容

del删除当前行删除SQL缓冲区中的当前行

run显示并运行SQL缓冲区中的命令

/运行SQL缓冲区中的命令

edit把SQL缓冲区中的命令写到操作系统下的文本文件，

并调用操作系统提供的编饵器执行修改。

3数据操纵

数据的插入

insertintodept

values(10,'accounting','newyork');

insertintodept(dname,deptno)

values('accounting',10);

从其它表中选择插入数据

insertintoemp(empno,ename,deptno)

selectid,namc,depanmcnt

fromold_emp

wheredepartmentin(10,20,30,40);

使用参数

insertintodept

values(&deptno.&dname,&loc);

执行时，SQL/PLUS对每个参数将有提示用户输入

参数对应日期型或字符型数据时，可在参数上加引号，输入时就可不用引号

insertintodept

values(&deptno,'&dnanie','&loc');

插入空值(NULL)

insertintodept

values(50.'education'.null);

插入日期型数据

日期型数据缺省格式：DD-MON-YY

insertintoemp

(empno,ename.hiredate)

values(7963；stonc','07-APR-87');

系统时刻：SYSDATE

insertintoemp

(cmpno,ename,hiredate)

values(7600；kohn',SYSDATE);

数据更新

updateemp

setjob='manager'

whereename='marlin';

updateemp

setjob='marketrep'

whereename=,salesman';

updateemp

setdeptno=40,job='marketrep'

wherejob:'salesman';

数据删除

deleteemp

whereempno=765;

更新的提交

commit

自动提交方式

setautocommiton

假如状态设为开，那么使用inesrl,update,delete会赶忙提交。

更新取消

rollback

两次连续成功的commit之间的操作，称为一个事务

4创建基表、视图

创建基表

createtabledept

(deptnonumber(2),

dnamechar(14),

locchar(13));

数据字典会自动更新。

一个基表最多254列。

表名列名命名规那么：

限制

第一个字符必须是字母，后面可任意［包括$#_但不能是逗号)。

名字不得超过30个字符。

唯独

某一用户的基表名必须唯独，不能是ORACLE的保留字，同一基表的列名互不相同。

使用双引号

假如表名用双引号括起来，那么可不满足上述规那么；

只有使用双引号，才能区别大、小写；

命名时使用了双引号，在以后的操作也必须使用双引号。

数据类型：

char(n)(不得超过240字符)

number(n.d)

date

long(最多65536字符)

raw(二进制原始数据)

空值处理

有时要求列值不能为空

createtabledept

(deptnonumber(2)notnull,

dnamechar(14),

locchar(13));

在基表中增加一列

alter(abledept

add(headentnumber。))；

修改已有列属性

altertabledept

modifydnamechar(20);

注：只有当某列所有值都为空时，才能减小其列值宽度。

只有当某列所有值都为空时，才能改变其列值类型。

只有当某列所有值都为不空时，才能定义该列为notnullo

例：

alter(abledeptmedify(locchar(12));

altertabledeptmedifylocchar(12);

altertabledeptmedify(dnamechar(13),locchar(12));

创建视图

createviewmanagersas

selectenamejob,sal

fromemp

wherejob='managcr,;

为视图列名取别名

createviewmydept

(person.title,salary)

asselectenamejob,sal

fromemp

wheredcptno=10;

withcheckoption选项

使用wilhcheckoplion,保证当对视图插入或更新数据时,

该数据必须满足视图定义中select命令所指定的条件。

createviewdept20as

selectename,job,sal,deptno

fromemp

wheredeptno=20

withcheckoption;

在做下述操作时，会发生错误

updatedcpt20

setdeplno=30

whereename='ward';

基表、视图的拷贝

createtableemp2

asselect*fromemp;

基表、视图的删除

droptable表名

dropview视图名

5SQL*PLUS报表功能

SQL*PLUS的一些差不多格式命令

columndeptnoheadingdepartment

columncnamcheadingname

columnsalheadingsalary

columnsalformat$99,999.00

ttitlesamplereportforIhitechcorp

blidestrictlyconlldential

breakondeptno

computesumofsalondeptno

run

表头和表尾

ttidesamplereportforIhitechcorp

btitlerightstrictlyconfidential

"I"表示换行，结尾不必加分号

选项有三种：leftrightcenter

使用TTITLE,系统将自动地在每页的顶部显示口期和页号。

TTITLET和BTITLE命令有效，直至重新设置表头或表尾，或退出SQL*PLUS,

下面命令使标题语句失效

TTITLEOFF

BTITLEOFF

列名

column命令定义用于显示列名

假设名字为一个单词，不必加引号

columnenameheadingemployee

columnenameheading'employee1name'

（1为换行）

取消栏定义

columncnamcclear

列的格式

columncnamcformatA15

columnsalformat$9,999.99

columncommlikesal

like子句，使得某一列的格式参照另一列格式，它拷贝列名及其格式

操纵记录显示分组顺序

breakondeptno

（不显示重复值）

selectdeplno.ename

fromemp

orderbydeptno;

（ORDERBY子句用于操纵BREAK）

显示为

10dark

niller

20smith

scott

30alien

blakc

每次只有一个BREAK命令起作用，但一次能够在多个列上使用BREAK命令

breakon列名1on列名2

记录分组

breakondeptnoskip2

selectdeptno,ename

fromemp

orderbydeptno;

每个deptno之间空两行

clearbreak（取消BREAK命令）

breakonpage〔每次从一新页开始）

breakonreport（每次从一新报表开始）

breakonpageonreport（联合使用）

分组运算

breakondeptnoskip2

computesumofsalondeptno

运党每个部门的工资总和

skip子句使部门之间的信息分隔开

其他运算命令

computeavgofsalondeptno（平均值〕

count非空值的总数

MAX最大值

MIN最小值

STD标准偏差

VAR协方差

NUMBER行数

使compute命令失效

一旦定义了COMPUTE,那么一直有效，直到

关闭COMPUTE（clearcompute）

SQL/PLUS环境命令

show选项

（显示当前参数设置情形）

showall（显示全部参数）

设置参数

set选项值或开关

setautocommilon

SET命令包括

setautocommit{offIonIimmediate）

（自动提交，OFF缺省）

setecho{offIon）

（命令文件执行，是否在终端上显示命令本身，OFF缺省）

setfeedback{offIon}

（ON：查询终止时，给四结果，记录数的信息，缺省；

OFF：无查询结果，记录数的信息）

setheading{offIon}

（ON：列的头标在报表上显示，缺省；OFF：不在报表上显示）

setlinesize{n}

一行显示的最大字符数，块省为80

setpagesize{n}

每页的行数，缺省是14

setpause{offIonItext}

（ON：当报表超过一页时，每显示一屏就暂停显示，等待用户打回车键，再接着显示;

OFF：页与页不停顿，缺省；text：页与页停顿，并向用户提示信息）

SETBUFFERbuffer

设置当头的命令缓冲区，通常情形下，SQL命令缓冲区已为当前缓冲区。

由于SQL命令缓冲区只能存放一条SQL命令，

因此可用其它缓冲区来存放SQL命令和SQL*PLUS命令。

经常用到的设置可放在login.sql文件中。

SETNULL

setnull'nodata'

selectename,comm

fromemp

wheredeptno=30;

把部门30中无佣金雇员的佣金显示为''NODATA".

setnull是SQL*PLUS命令，用它来标识空值（NULL）,能够设置为任意字符串。

存盘命令SAVE

save文件名

input

1selectenipno,enamejob

2fromenip

3wherejob='analyst'

saveresearch

名目中会增加一个research.sql文件。

编辑命令EDIT

edit

EDIT编辑当前缓冲区中的内容。

编辑一个文件

editresearch

调入命令GET

getresearch

把磁盘上的文件内容调入缓冲区，并显示在屏幕上，文件名尾不必加.sql。

START命令

运行指定的文件

startresearch

输出命令SPOOL

spooltryfile

不仅能够使查询结果在屏幕上显示，还能够使结果存入文件

停止向文件输出

spooloff

把查询结果在打印机上输出，先把它们存入一个文件中，

然后不必使用SPOOLOFF,而用：

spoolout

SPOOLOUT关闭该文件并在系统缺省的打印机上输出

制作报表举例

edittryfile

setechooff

setautocommiton

setpagesize25

insertintoemp(enipno,ename,hiredate)

values(9999,'geiger',sysdate);

insertintoemp(empno,ename,deptno)

values(3333/sanison',20);

spoolnew_emp

select*fromemp

wheredeptno=20

ordeptnoisnull

/

spooloff

setautocommitoff

用start命令执行那个文件

6函数

字符型函数

initcap(ename);将ename中每个词的第一个字母改为大写。

如：jacksmith-JackSmith

length(ename);运算字符串的长度。

subslr(job,1.4);

其它

lower

upper

least取出字符串列表中按字母排序排在最前面的一个串

greatest取出字符串列表中按字母排序排在最后的一个串

日期函数

add_monih(hiredate,5)在雇佣时刻上加5个月

month_between(sysdate,hiredate)运算雇佣时刻与系统时刻之间相差的月数

next_day(hiredate：FRIDAY')运算受雇日期之后的第一个星期五的日期

例

selectename,sal,next_day(sysdate,'FRIDAY')as_of

fromemp

wheredeptno=20;

(as_of是别名)

假如不用to_char函数,日期在ORACLE中的缺省格式是'DD_MON_YY，

to_char(datc,datcpicture)

selectename,to_char(hiredate,'DyMondd,yyyy')hired

fromemp

wheredeptno=10;

to_date(字符串,格式)

insertintoemp(empno,ename,hiredate)

valuesC7999，asms\to_date('(n03870g3000','MMDDYYHHMISS));

日期型数据的格式

dd12

dyfri

dayfriday

ddspthtwelfth

mm03

monmar

monthmarch

yy87

yyyy1987

例

Mar12,1987'Mondd,yyyy'

MAR12,1987'MONdd.yyyy'

ThursdayMARCH12DayMONTHdd,

Mar1211:00am'Monddhh:miam'

Thu,thetwelfth'Dy,"the"ddspth'

算术函数

least(vl,v2)

selectename,empno,mgr,least(empno,mgr)lownum

fromemp

whereempnoO

trunc(saLO)

取sal的近似值(截断)

空值函数

nvl(vl,v2)

V1为列名，假如V1不是空值，nvl返回其列值。

vl为空值，返回v2的值。

聚组函数

selectsum(comm)

fromemp;

(返回一个汇总信息)

不能把sum用在select语句里除非用groupby

字符型、日期型、数字型的聚组函数

ininmaxcount可用于任何数据类型

selectmin(ename)

fromemp;

selectmin(hircdatc)

fromemp;

selectmin(sal)

fromcmp;

有多少人有工作？

selectcount(job)

fromemp;

有多少种不同的工种？

selectcount(distinctjob)

fromemp;

countdistinct运算某一字段中不同的值的个数

其它聚组函数(只用于数字型数据)

avg运算平均工资

selectavg(sal)

fromemp;

stddev运算工资的平均差

selectstddev(sal)

fromemp;

sum运算总工资

selectsum(sal)

fromemp;

groupby子句

selectdcptno,sum(sal),avg(sal)

fromemp

groupbydcptno;

按多个条件分组

每个部门的雇员数

selectdeptno,count(*)

fromcmp

groupbydeptno;

每个部门的每个工种的雇员数

selectdeptnojobxount(*)

fromemp

groupbydeptnojob;

满足条件的分组

(where是针对select的，having是针对groupby的)

哪些部门的工资总和超过了9000

selectdcptno.sum(scil)

fromemp

groupbydeptno

havingsum(sal)>9000;

select小结

除去职员，哪些部门的工资总和超过了8000

selectdeptno,sum(sal)

fromemp

wherejob!="clerk,

groupbydeptno

havingsum(sal)>8000

orderbysum(sal);

7高级查询

等值联接

selectempno,ename,job,emp.deptno,dname

fromemp,dept

wherecmp.dcptno=dcpt.dcptno;

外联接

selectename,dept.deptno,loc

fromemp,dept

whereemp.deptno(+)=dept.deptno;

假如在dept.deptno中有的数值，在emp.deptno中没有(如deptno=40),

那么作外联接时，结果中会产生一个空值

自联接：同•基表的不同行要做联接，可使用自联接

指出每个雇员的经理名字

selectworker.ename,manager.enamemanager

fromempworker,empmanager

wherewoiker.mgr=manager.empno;

非等值联接

哪些雇员的工资属于第三级别

selectename,sal

fromemp,salgrade

wheregrade=3

andsalbetweenlosalandhisal;

(基表salgradc：gradelosalhisal)

集合运算

行的连接

集合运算把2个或多个查询结果合并为一•个

union-setunion

Rowsoffirstqueryplusofsecondquery,lessduplicaterows

intersect-setintersection

Rowsbothquerieshaveincommon

minus-setdifference

rowsuniquetothefirstquery

介绍几个视图

accountview

enamcsaljob

salesview

enamesaljob

researchview

enamesaljob

union运算

返回一个查询结果中有但又不重要的行，它将基表或视图中的记录合并在一起

所有部门中有哪些雇职员资超过2000

对应列的数据类型必须相同

selectename,sal

fromaccount

wheresal>2000

union

selectename,sal

fromresearch

wheresal>2000

union

selectename,sal

fromsales

wheresal>2000;

intersect运算

返回查询结果中相同的部分

各个部门中有哪些相同的工种

selectjob

fromaccount

intersect

selectjob

fromresearch

intersect

selectjob

fromsales;

minus运算

返回在第一个查询结果中与第二个杳询结果不相同的那部分行记录。

有哪些工种在财会部中有，而在销售部中没有？

selectjobfromaccount

minus

selectjobfromsales;

子查询

sleetcnarnc,dcptno

fromemp

wheredeptno=

(selectdeptno

fromemp

whereename=,srnith');

多级子查询

selectename,job.sal

fromemp

wherejob=

(selectjob

fromemp

whereename='clark')

orsal>

(selectsal

fromemp

whereename='clark');

多个基表与子查询

selectename,job,sal

fromcmp,dcpt

whereloc='newyork'

andemp.deptno=dept.deptno

andsal>

(selectsal

fromemp

whereename='scott');

子查询中使用聚组函数

selectcnanie,hiredate

fromemp

wherehircdatc=

(selectmin(hiredate)

fromemp);

8授权

系统权限

DBA所有权限

RESOURCE注册，创建新的基表

CONNECT,注册，查询

只有DBA才有权创建新的用户

grantconnecttoscott

identifiedbytiger;

DBA或用户自己能够改变用户口令

grantconnecttoscott

identifiedbyleopard;

基表权限1

有两种方法获得对基表操作的权限

创建自己的基表

获得基表创建用户的许可

grantselect,insert

onemp

toscott;

这些权限有

selectinsertupdatedeletealterindex

把所有权限授于他人

grantallonemptoscott;

同义词

select*

fromscotl.emp

创建同义词

为用户alien的EMP基表创建同义词employee

createsynonymemployee

forallen.emp

基表权限2

你能够使其他用户有如此的权力，即其他用户能够把你的基表权限授予他人

grantall

onemp

toscott

withgrantoption;

收回权限

系统权限只有被DBA收回

基表权限随时都能够收回

revokeinsert

onemp

fromscott;

9索引

建立索引

createindexemp_ename

oncmp(cname);

删除索引

dropindexemp_ename;

关于索引

只对较大的基表建立索引(至少50条记录)

建立索引之前插入数据

对一个基表可建立任意多个索引

一样是在作为主键的列上建立索引

建立索引之后，不阻碍SQL命令的执行

建立索引之后，ORACLE自动爱护和使用索引

保证数据唯独性

提高执行速度的同时，索引还能够保证每个记录中的每个列值是不重生的。

createuniqueindexemp_empno

onemp(empno);

练习和答案

有没有工资比奖金多的雇员？假如有，按工资的降序排列。

假如有两个以上的雇职员资相同，按他们的名字排序。

selectcnanicemployee,salsalary,commcommision

fromemp

whcrcsal>comm

orderbysaldesc,ename;

列出有关雇员姓名、奖金占收百分比的信息。

要求显示时列名意义明确，按雇员姓名排序，不包括奖金未知的雇员。

selectcnamccniploycc,(coinm/(comm+sal))*100incentive

fromemp

wherecommisnotnull

orderbyename;

在Chicago(部门30)工作的所有雇员的工资上涨1()%。

updateemp

setsal=l.l*sal

wheredeptno=30;

updateemp

setsal=l.l*sal

wheredeptno=(selectdeptno

fromdept

whcrcloc='chicago');

为hitech公司新建一个部口，编号为50,其它信息均不可知。

insertintodept(dname,deptno)

values('faclities',50);

创建视图，三个列名，其中不包括职员信息

createviewcmployec("cmployecname",

"employeenumber",

"employeejob")

asselectename,empno,job

fromemp

wherejob!='clerk';

制作工资报表，包括雇员姓名、受雇时刻(按星期运算1,工资和部门编号，

一页显示一个部门的信息，每页尾，显示该页的工资之和以及受雇时刻之和,

报表结尾处，.显示所有雇员的工资总和以及受雇时刻总和，

工资按美元运算，受雇时刻按星期运算，每页的上方应有标题。

ttitle'service'

breakondeptnoonpageonreport

computesumofsalondeptno

computesumofsalonreport

computesumofsen;ice_leng(hondeptno

computesumofser\ncc_lcngthonreport

columnsalformat$99,999.00

columnsci-vicc_lcngthformat9999

selectdeptno,enameemployee,(sysdate-hiredate)/7service_length,sal

fromemp

orderbydeptno;

制作报表，包括雇员姓名、总收入和受佣日期，

且：姓名的第一个字母必须大写，雇佣日期格式为MM/DD/YYYY,

总收入包括没有奖金的雇员的总收入，姓名按字母顺序排列。

col"hiredate"formatAI2

col"employee"formatAIO

col"compensation"format$99,999.00

selectinitcap(ename)"employee",

(sal+nvl(comm,0))"compensation",

to_char(hiredate：MM/DD/YYYY,)"hiredate"

fromemp

orderbyename;

列出有超过7个周边国家的国家名字和面积。

selectnation,area

fromnation

wherecodein

(selectnation_code

fromborder

groupbynation_code

havingcount(*)>7);

列出所有面积大于等于口本的岛国的国名和人口。

selectna(ion,population

fromnation,border

wherecode=nation_code(+)

andnation_codeisnull

andarea>=

(selectarea

fromnation

whereupper(nalion户'JAPAN');

列出所有边界在其它国家中的国名，同时显示其边界国家名字。

breakonnation

selectnation1.nation,

nation2.nationborderin_country

fromnationnation1,border,nationnation2

wherenation1.code=border.nation_code

andbordcr.bordcr_codc=nation2.codc

orderbynation1.nation;

PL/SQL

2PL/SQL的块结构和数据类型

块结构的特点

嵌套

begin

begin

exception

end;

exception

end;

标识符：

不能超过30个字符

第一个字符必须为字母

其余字符能够是字母，数字，$，一，或#

不区分大小写形式

假如用双引号括起来，那么字符顺序能够任意排列，并区分大小写形式

无SQL保留字

数据类型

数字型：

整数，实数，以及指数

字符串：

用单引号括起来

假设在字符串表示单引号，那么使用两个单引号

字符串长度为零(两个单引号之间没有字符)，那么表示NULL

字符：

长度为1的字符串

数据定义

语法

标识符［常数〉数据类型［NOTNULL＞［:=PL/SQL表达式〉；

'尸'表示给变量赋值

数据类型包括

数字型number。,2)

字符型char(120)

日期型date

布尔型boolean(取值为true,false或null,不存贮在数据库中)

日期型

anniversarydate:='O5-JUL-95';

project_completiondate:

布尔型

ovcr_budgctbooleannotnull:=false;

availableboolean;

(初始值为NULL)

%type类型匹配

books_printednumber(6);

books_sokibook_prin(ed%type;

managcr_namcainc%type;

变量赋值

变量名:=PL/SQL表达式

numvar:=5;

boolvar:=true;

datevar:='lI-JUN-87';

字符型、数字型表达式中的空值

IU111+＜数字＞=null1空值加数字仍是空值）

nullx数字＞=null1空值与数字进行比较，结果仍是空值）

nullII字符串I字符串,（null即”）

（空值与字符串进行连接运算，结果为原字符串）

变量作用范畴

标识符在宣言它的块中有效

标识符假如不在子块中重新定义，那么在PL/SQL块的所有子块中同样有效

重新定义后的标识符，作用范畴仅在本子块中有效

例

declare

c_messchar(80);

begin

declare

vlnumbcr(4);

begin

selectempnointovlfromemp

wherejob='prcsidcnt';

exception

whentoo_many_rowsthen

insertintojob_errors

valuesCmorethanonepresident');

end;

/右块2*/

declare

vlnumber(4);

begin

selectempnointov1fromemp

wherejob='manager';

exception

whentoo_many_rowsthen

insertintojob_errors

values('morethanonemanager');

end;

exception

whenothersthen

e_mess:=substr(sqlerrm,1,83);

insertintogeneralerrorsvalues(e_mess);

end;

3SQL和PL/SQL

插入

declare

my_salnumber(7,2):=3040.55;

my_enamechar(25):='wanda';

my_hiredatedate:=,08-SEP-88';

begin

insertintoemp

(empno,enmae,job,hiredale,sal,deptno)

values(2741,my_ename,'cabdriver\my_hiredate,my_sal,20);

end;

删除

declare

bad_child_typcchar(20):-naughty';

begin

deletefromsantas_gift_listwhcrc

kid_rating=bad_child_type;

end;

事务处理

commit[WORK>;

rollbackfWORK>;

(关键字WORK可选，但对命令执行无任何阻碍)

savepoint标记名；1储存当前点)

在事务中标记当前点

rollback[WORK〉to[SAVEPOINT〉标记名；(回退到当前储存点)

取消savepoint命令之后的所有对数据库的修改

关键字WORK和SAVEPOINT为可选项，对命令执行无任何阻碍

函数

PL/SQL块中能够使用SQL命令的所有函数

insertintophonebook(lastname)value(upper(my_lastname));

selectavg(sal)intoavg_salfromemp;

关于非SQL命令，可使用大多数个体函数

不能使用聚组函数和参数个数不定的函数，如

x:=sqrt(y);

lastname:=upper(lastname);

age_diff:=months_between(birtliday1.birthday2)/l2;

赋值时的数据类型转换

4种赋值形式：

变量名尸表达式

insertinto基表名values(表达式1,表达式2,...);

update基表名set列名=表达式；

select列名into变量名from

数据类型间能进行转换的有：

char转成number

number转成char

char转成date

date转成char

例

char_var:=nm_var;

数字型转换成字符型

date_var:='25-DEC-88,;

字符型转换成日期型

insertinto表名(num_col)values('604badnumber');

错误，无法成功地转换数据类型

4条件操纵

例

declare

numjobsnumber(4);

begin

selectcount(*)intonumjobsfromauditions

whereactorid=&&actor_idandcalled_back='yes';

ifnumjobs>100then

updateactorsetactor_rating='wordclass'

whereactorid=&&actor_id;

elsifnum_job=75then

updateactorsetactor_rating='daytimesoaps'

whereactorid=&&actor_id;

else

updateactorsetactor_rating='waiter'

whereactorid=&&actor_id;

endif;

endif;

commit;

end;

5循环

语法

loop

endloop;

exit;(退出循环)

exit[when>;(退出循环，当满足WHEN时)

例1

declare

ctrnumber(3):=0;

begin

loop

insertintotable1values('tastesgreat');

insertintotable2values('lessfilling');

ctr:=ctr+l;

exitwhenctr=100;

endloop;

end;

(注：假如cir取为NULL,循环无法终止)

例2

FOR语法

for变显〈范畴〉loop

endloop;

declare

myjndexchar(2O):='fettucinialfredo';

bowlchar(2O);

begin

formy_indexinreverse21..30loop

insertintotcmp(coll)valucs(my_indcx);

/*循环次数从30到21*/

endloop:

bowl:=my_index;

end;

跟在inreverse后面的数字必须是从小到大的顺序，必须是整数，不能是变量或表达式

6游标

显式游标

打开游标

open＜游标名〉

例

opencolor_cur;

游标属性

%notfound

%found

%rowcount

%isopen

例

fetchmy_curintomy_var;

whilemy_cur%foundloop

（处理数据）

fetchmy_curintomy_var;

exitwhenmy_cur%rowcount=10;

endloop;

%notfound属性

取值情形如下：

felch操作没有返归I记录，那么取值为inie

fetch操作返回条记录，那么取值为false

对■游标无fetch操作时，取值为null

〈游标名〉%notfound

例

ifcolor_cur%notfbundthen...

注：假如没有fetch操作，那么〈游标名＞％notfound将导致出错,

因为％noi「ound的初始值为NULLo

关闭游标

close〈游标名〉

例

closecolor_cur;

游标的FOR循环

语法

for〈记录名〉in＜游标名＞loop

v一组命令,

endloop;

其中：

索引是建立在每条记录的值之上的

记录名不必声明

每个值对应的是记录名，列名

初始化游标指打开游标

活动集合中的记录自动完成FETCH操作

退出循环，关闭游标

隐式游标

隐式游标是指SQL命令中用到的，没有明确定义的游标

insert,update,delete,select语句中不必明确定义游标

调用格式为SQL%

存贮有关最新一条SQL命令的处理信息

隐式游标的属性

隐式游标有四个属性

SQL%NOTFOUND

SQL%FOUND

SQL%ROWCOUNT：隐式游标包括的记录数

例：

deletefrombaseballjeamwherebatting_avg<l(X);

ifsql%rowcount>5thn

insertintotemp

values('yourteamneedshelp');

endif;

SQL%ISOPEN：取值总为FALSE。SQL命令执行完毕，PL/SQL赶忙关闭隐式游标。

7标号

GOTO语句

用法：

gotoyou_are_here;

其中you_are_here是要跳转的语句标号

标号必须在同一组命令，或是同一块中使用

正确的使用

<>(标号)

x:=x+l

ifa>bthen

b:=b+c;

gotodinner;

endif;

错误的使用

gotojail;

ifa>bthen

b:=b+c;

<>（标号）

x:=x+l;

endif;

标号：解决意义模糊

标号可用于定义列值的变量

<>

declare

deptnonumber:=20;

begin

updateempsetsal=sal*l.l

wheredcptno=sample.dcptno;

commit;

endsample;

假如不用标号和标号限制符，这条命令将修改每条记录。

8专门处理

预定义的专门情形

任何ORACLE错误都将自动产生一个专门信息

一些专门情形已命名，如：

no_da（a_found当SELECT语句无返I可记录时产生

too_many_iows没有定义游标，而SELECT语句返回多条记录时产生

whenevernotfound无对应的记录

用户定义的专门情形

由用户自己猎取

在DECLARE部分定义：

declare

xnumber;

something_isnt_rightexception;

用户定义的专门情形遵循一样的作用范畴规那么

条件满足时，猎取专门情形：raisesomething_isnt_right

注意：同样能够猎取预定义的专门情形

exception_init语句

承诺为ORACLE错误命名

调用格式：

pragmaexception_init（<S达式>,）;

例

declare

deadlock_detectedexception;

pragmaexception_init（diacllock_detected,-60）;

raise语句

单独使用RAISE命令，可再一次猎取当前的专门情形（就象专门情形被重复处理了一样）。

在专门处理中，此语句只能单独使用。

专门处理标识符

一组用「处理专门情形的语句：

exception

when〈表达式〉or|表达式...>then

v一组语句〉

whenothersihen--最后一个处理

V一组语句）

end;既终止PL/SQL块部分，也终止专门处理部分

练习与答案

1:

接收contract_no和item_no值，在inventory表中查找,假如产品：

己发货，在arrivaLdatc中赋值为今天后的7天

已订货，在arrival_date中赋值为今天后的一个月

既无订货又无发货，那么仁arrivaLdatc中赋值为今天后的两个月，

并在order表中增加一条新的订单记录。

product_status的歹U值为,shipped和'ordered,

inventory:

product_idnumber(6)

product_descriptionchar(30)

product_statuschar(20)

std_shipping_qtynumber(3)

contract_item:

contract_nonumbcr(12)

item_nonumber(6)

arrival_datedate

order:

order_idnumber(6)

product_idnumber(6)

qtynumbcr(3)

答案：

declare

i_product_idinventory.product_id%type;

i_product_descriptionduct_description%type;

i_product_statusinventor)\product_stalus%type;

i_std_shipping_qtyinvcntory.std_shipping_qty%typc;

begin

selectproduct_id,product_description,product_status,s(d_shipping_qty

intoi_product_id,i_product_description,

i_product_status,i_std_shipping_qty

frominventory

whereproduct_id=(

selectproduct_id

fromcontract_item

whereconlract_no=&&contractnoanditem_no=&&i(emno);

ifi_product_status='shipped'then

updatecontract_iteni

setarrival_date=sysdate+7

whereitcm_no=&&itcmnoandcontract_no=&&contractno;

elsifi_produc(_status='ordeied'then

updatecontract_itcm

setarrival_date=add_months(sysdale,1)

whcrcitcm_no=&&itcmnoandcontract_no=&&ccntractno;

else

updatecontract_item

setarrival_datc=add_nionths(sysdatc,2)

whereitem_no=&&itemnoandcontract_no=&&ccntractno;

insertintoorders

values(lO0,i_producl_id,i_sld_shipping_q(y);

endif;

endif;

commit;

end;

2:

1.找出指定部门中的所有雇员

2.用带回的变量提示用户输入部门编号

3.把雇员姓名及工资存入printable表中，基结构为：

createtableprnttable

(seqnumber(7),linechar(80));

4.专门情形为，部门中奖金不为空值的雇员信息才能存入prnttable表中。

答案：

declare

cursoremp_curis

selectename,sal,comm

fromempwheredeptno=&dno;

cmp_rcccmp_cur%rowtypc;

null_commissionexception;

begin

openemp_cur;

fetchemp_curintoemp_rec;

while(emp_cur%found)loop

ifemp_recmisnullthen

begin

closeemp_cur;

raisenuli_commission;

end;

endif；

fetchemp_curintoemp_rec;

endloop;

closecmp_sur;

exception

whennull_commissionthen

openemp_cur;

fetchcmp_curintocmp_rcc;

while(emp_cur%found)loop

ifemp_recmisnotnullthen

insertintotempvalues(emp_rcc.sal,cmp_rcc.enainc);

endif；

fetchcmp_curintocmp_rcc;

endloop;

closeemp_cur;

commit;

end;

RE:ORACLE数据库对象与用户治理（转）

UB时刻：2003-08-1421:06:59［修改］［回复］［删除］

ORACLE数据库对象与用户治理

一、ORACLE数据库的模式对象的治理与爱护

本节的要紧内容是关于ORACLE数据库的模式对象的治理与爱护，这些模式对象包括：表

空间、表、视图、索引、序列、同义词、集合和完整性约束。关于每一个模式对象，第一描

述了它的定义，说明了它的功能，最后以基于SQL语言的实例说明如何对它们进行治理于

爱护。

1.1表空间

由于表空间是包含这些模式对象的逻辑空间，有必要先对它进行爱护。

创建表空间

SQL>CREATETABLESPACEjxzy

>DATAFILE'/usr/oracle/dbs/jxzy.dbf,

>ONLINE;

修改表空间

SQL>ALTERTABLESPACEjxzyOFFLINENORMAL;

SQL>ALTERTABLESPACEjxzy

>RENAMEDATAFILE</usr/oracle/dbs/jxzy.dbf,

>TO'/usr/oracle/dbs/jxzynew.dbf*

>ONLINE

SQL>CREATETABLESPACEjxzyONLINE

删除表空间

SQL>DROPTABLESPACEjxzy

>INCLUDINGCONTENTS

1.2表爱护

表是数据库中数据储备的差不多单位，一个表包含假设干列，每列具有列名、类型、长度等。

表的建立

SQL>CREATETABLEjxzy.switch(

>OFFICE_NUMNUMBER(3,0)NOTNULL,

>SWITCH_CODENUMBER(8,0)NOTNULL,

>SWITCH_NAMEVARCHAR2(20)NOTNULL);

表的修改

SQL>ALTERTABLEjxzy.switch

>ADD(DESCVARCHAR2(30));

表的删除

SQL>DROPTABLEjxzy.switch

>CASCADECONSTRAINTS

〃删除引用该表的其它表的完整性约束

1.3视图爱护

视图是由一个或假设干基表产生的数据集合，但视图不占储备空间。建立视图能够爱犷数据

安全（仅让用户查询修改能够看见的一些行列）、简化查询操作、爱护数据的独立性。

视图的建立

SQL>CREATEVIEWjxzy.pole_well_viewAS

>(SELECTpole_path_namASpath,

poleASdcvice_numFROMpole

>UNION

>SELECTpipe_path_numASpath,

>wellASdevice_numFROMwell);

视图的替换

SQL>REPLACEVIEWjxzy.pole_well_viewAS

>(SELECTpole_path_n.imASpath.

poleASsupporl_deviceFROMpole

>UNION

>SELECTpipe_path_numASpath,

wellASsupport_de