plsql操作手册
Oracle PL/SQL 编程手册(SQL大全)- -
一、SQLPLUS
1引言
一、SQLPLUS
1引言
SQL命令
以下17个是作为语句开头的关键字:
alterdroprevoke
auditgrantrollback*
commit*insertselect
commentlockupdate
createnoauditvalidate
deleterename
这些命令必须以“;”结尾
带*命令句尾不必加分号,并且不存入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
这些命令不存入SQL缓存区
@definepause
#delquit
$describeremark
/disconnectrun
acceptdocumentsave
appendeditset
breakexitshow
btitlegetspool
changehelpsqlplus
clearhoststart
columninputtiming
computelistttitle
connectnewpageundefine
copy
---------
2数据库查询
2数据库查询
数据字典
TAB用户创建的所有基表、视图和同义词清单
TAB用户创建的所有基表、视图和同义词清单
DTAB构成数据字典的所有表
COL用户创建的基表的所有列定义的清单
CATALOG用户可存取的所有基表清单
select*fromtab;
describe命令描述基表的结构信息
describedept
describedept
select*
fromemp;
fromemp;
selectempno,ename,job
fromemp;
fromemp;
select*fromdept
orderbydeptnodesc;
orderbydeptnodesc;
逻辑运算符
=!=或<>>>=<<=
in
betweenvalue1andvalue2
like
%
_
innull
not
noin,isnotnull
=!=或<>>>=<<=
in
betweenvalue1andvalue2
like
%
_
innull
not
noin,isnotnull
谓词in和notin
有哪些职员和分析员
selectename,job
fromemp
wherejobin('clerk','analyst');
有哪些职员和分析员
selectename,job
fromemp
wherejobin('clerk','analyst');
selectename,job
fromemp
wherejobnotin('clerk','analyst');
fromemp
wherejobnotin('clerk','analyst');
谓词between和notbetween
哪些雇员的工资在2000和3000之间
selectename,job,salfromemp
wheresalbetween2000and3000;
哪些雇员的工资在2000和3000之间
selectename,job,salfromemp
wheresalbetween2000and3000;
selectename,job,salfromemp
wheresalnotbetween2000and3000;
wheresalnotbetween2000and3000;
谓词like,notlike
selectename,deptnofromemp
whereenamelike'S%';
(以字母S开头)
selectename,deptnofromemp
whereenamelike'%K';
(以K结尾)
selectename,deptnofromemp
whereenamelike'W___';
(以W开头,后面仅有三个字母)
selectename,jobfromemp
wherejobnotlike'sales%';
(哪些雇员的工种名不以sales开头)
selectename,deptnofromemp
whereenamelike'S%';
(以字母S开头)
selectename,deptnofromemp
whereenamelike'%K';
(以K结尾)
selectename,deptnofromemp
whereenamelike'W___';
(以W开头,后面仅有三个字母)
selectename,jobfromemp
wherejobnotlike'sales%';
(哪些雇员的工种名不以sales开头)
谓词isnull,isnotnull
没有奖金的雇员(即commision为null)
selectename,jobfromemp
wherecommisnull;
没有奖金的雇员(即commision为null)
selectename,jobfromemp
wherecommisnull;
selectename,jobfromemp
wherecommisnotnull;
wherecommisnotnull;
多条件查询
selectename,job
fromemp
wheredeptno=20
andjob!='clerk';
selectename,job
fromemp
wheredeptno=20
andjob!='clerk';
表达式
+-*/
+-*/
算术表达式
选择奖金高于其工资的5%的雇员
selectename,sal,comm,comm/salfromemp
wherecomm>.05*sal
orderbycomm/saldesc;
选择奖金高于其工资的5%的雇员
selectename,sal,comm,comm/salfromemp
wherecomm>.05*sal
orderbycomm/saldesc;
日期型数据的运算
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
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,sal,comm,comm/sal"C/SRATIO"fromemp
wherecomm>.05*sal
orderbycomm/saldesc;
selectenameemployeefromemp
wheredeptno=10;
(别名:employee)
selectename,sal,comm,comm/sal"C/SRATIO"fromemp
wherecomm>.05*sal
orderbycomm/saldesc;
SQL命令的编辑
listorl显示缓冲区的内容
list4显示当前SQL命令的第4行,并把第4行作为当前行,在该行号后面有个*。
changeorc用新的内容替换原来在一行中第一次出现内容
SQL>c/(...)/('analyst')/
inputori增加一行或多行
appendora在一行后追加内容
del删除当前行删除SQL缓冲区中的当前行
run显示并运行SQL缓冲区中的命令
/运行SQL缓冲区中的命令
edit把SQL缓冲区中的命令写到操作系统下的文本文件,
并调用操作系统提供的编辑器执行修改。
listorl显示缓冲区的内容
list4显示当前SQL命令的第4行,并把第4行作为当前行,在该行号后面有个*。
changeorc用新的内容替换原来在一行中第一次出现内容
SQL>c/(...)/('analyst')/
inputori增加一行或多行
appendora在一行后追加内容
del删除当前行删除SQL缓冲区中的当前行
run显示并运行SQL缓冲区中的命令
/运行SQL缓冲区中的命令
edit把SQL缓冲区中的命令写到操作系统下的文本文件,
并调用操作系统提供的编辑器执行修改。
-------------
3数据操纵
数据的插入
insertintodept
values(10,'accounting','newyork');
3数据操纵
数据的插入
insertintodept
values(10,'accounting','newyork');
insertintodept(dname,deptno)
values('accounting',10);
values('accounting',10);
从其它表中选择插入数据
insertintoemp(empno,ename,deptno)
selectid,name,department
fromold_emp
wheredepartmentin(10,20,30,40);
insertintoemp(empno,ename,deptno)
selectid,name,department
fromold_emp
wheredepartmentin(10,20,30,40);
使用参数
insertintodept
values(&deptno,&dname,&loc);
执行时,SQL/PLUS对每个参数将有提示用户输入
insertintodept
values(&deptno,&dname,&loc);
执行时,SQL/PLUS对每个参数将有提示用户输入
参数对应日期型或字符型数据时,可在参数上加引号,输入时就可不用引号
insertintodept
values(&deptno,'&dname','&loc');
insertintodept
values(&deptno,'&dname','&loc');
插入空值(NULL)
insertintodept
values(50,'education',null);
insertintodept
values(50,'education',null);
插入日期型数据
日期型数据缺省格式:DD-MON-YY
insertintoemp
(empno,ename,hiredate)
values(7963,'stone','07-APR-87');
日期型数据缺省格式:DD-MON-YY
insertintoemp
(empno,ename,hiredate)
values(7963,'stone','07-APR-87');
系统时间:SYSDATE
insertintoemp
(empno,ename,hiredate)
values(7600,'kohn',SYSDATE);
insertintoemp
(empno,ename,hiredate)
values(7600,'kohn',SYSDATE);
数据更新
updateemp
setjob='manager'
whereename='martin';
updateemp
setjob='manager'
whereename='martin';
updateemp
setjob='marketrep'
whereename='salesman';
setjob='marketrep'
whereename='salesman';
updateemp
setdeptno=40,job='marketrep'
wherejob='salesman';
setdeptno=40,job='marketrep'
wherejob='salesman';
数据删除
deleteemp
whereempno=765;
deleteemp
whereempno=765;
更新的提交
commit
commit
自动提交方式
setautocommiton
如果状态设为开,则使用inesrt,update,delete会立即提交。
setautocommiton
如果状态设为开,则使用inesrt,update,delete会立即提交。
更新取消
rollback
rollback
两次连续成功的commit之间的操作,称为一个事务
---------------
4创建基表、视图
创建基表
createtabledept
(deptnonumber(2),
dnamechar(14),
locchar(13));
4创建基表、视图
创建基表
createtabledept
(deptnonumber(2),
dnamechar(14),
locchar(13));
数据字典会自动更新。
一个基表最多254列。
一个基表最多254列。
表名列名命名规则:
限制
第一个字符必须是字母,后面可任意(包括$#_但不能是逗号)。
名字不得超过30个字符。
限制
第一个字符必须是字母,后面可任意(包括$#_但不能是逗号)。
名字不得超过30个字符。
唯一
某一用户的基表名必须唯一,不能是ORACLE的保留字,同一基表的列名互不相同。
某一用户的基表名必须唯一,不能是ORACLE的保留字,同一基表的列名互不相同。
使用双引号
如果表名用双引号括起来,则可不满足上述规则;
只有使用双引号,才能区别大、小写;
命名时使用了双引号,在以后的操作也必须使用双引号。
如果表名用双引号括起来,则可不满足上述规则;
只有使用双引号,才能区别大、小写;
命名时使用了双引号,在以后的操作也必须使用双引号。
数据类型:
char(n)(不得超过240字符)
number(n,d)
date
long(最多65536字符)
raw(二进制原始数据)
char(n)(不得超过240字符)
number(n,d)
date
long(最多65536字符)
raw(二进制原始数据)
空值处理
有时要求列值不能为空
createtabledept
(deptnonumber(2)notnull,
dnamechar(14),
locchar(13));
有时要求列值不能为空
createtabledept
(deptnonumber(2)notnull,
dnamechar(14),
locchar(13));
在基表中增加一列
altertabledept
add(headcntnumber(3));
altertabledept
add(headcntnumber(3));
修改已有列属性
altertabledept
modifydnamechar(20);
注:只有当某列所有值都为空时,才能减小其列值宽度。
只有当某列所有值都为空时,才能改变其列值类型。
只有当某列所有值都为不空时,才能定义该列为notnull。
例:
altertabledeptmodify(locchar(12));
altertabledeptmodifylocchar(12);
altertabledeptmodify(dnamechar(13),locchar(12));
altertabledept
modifydnamechar(20);
注:只有当某列所有值都为空时,才能减小其列值宽度。
只有当某列所有值都为空时,才能改变其列值类型。
只有当某列所有值都为不空时,才能定义该列为notnull。
例:
altertabledeptmodify(locchar(12));
altertabledeptmodifylocchar(12);
altertabledeptmodify(dnamechar(13),locchar(12));
创建视图
createviewmanagersas
selectename,job,sal
fromemp
wherejob='manager';
createviewmanagersas
selectename,job,sal
fromemp
wherejob='manager';
为视图列名取别名
createviewmydept
(person,title,salary)
asselectename,job,sal
fromemp
wheredeptno=10;
createviewmydept
(person,title,salary)
asselectename,job,sal
fromemp
wheredeptno=10;
withcheckoption选项
使用withcheckoption,保证当对视图插入或更新数据时,
该数据必须满足视图定义中select命令所指定的条件。
createviewdept20as
selectename,job,sal,deptno
fromemp
wheredeptno=20
withcheckoption;
在做下述操作时,会发生错误
updatedept20
setdeptno=30
whereename='ward';
使用withcheckoption,保证当对视图插入或更新数据时,
该数据必须满足视图定义中select命令所指定的条件。
createviewdept20as
selectename,job,sal,deptno
fromemp
wheredeptno=20
withcheckoption;
在做下述操作时,会发生错误
updatedept20
setdeptno=30
whereename='ward';
基表、视图的拷贝
createtableemp2
asselect*fromemp;
createtableemp2
asselect*fromemp;
基表、视图的删除
droptable表名
dropview视图名
droptable表名
dropview视图名
------------
5SQL*PLUS报表功能
SQL*PLUS的一些基本格式命令
columndeptnoheadingdepartment
5SQL*PLUS报表功能
SQL*PLUS的一些基本格式命令
columndeptnoheadingdepartment
columnenameheadingname
columnsalheadingsalary
columnsalformat$99,999.00
ttitlesamplereportfor|hitechcorp
btitlestrictlyconfidential
breakondeptno
computesumofsalondeptno
run
表头和表尾
ttitlesamplereportfor|hitechcorp
btitlerightstrictlyconfidential
ttitlesamplereportfor|hitechcorp
btitlerightstrictlyconfidential
“|”表示换行,结尾不必加分号
选项有三种:leftrightcenter
选项有三种:leftrightcenter
使用TTITLE,系统将自动地在每页的顶部显示日期和页号。
TTITLET和BTITLE命令有效,直至重新设置表头或表尾,或退出SQL*PLUS。
TTITLET和BTITLE命令有效,直至重新设置表头或表尾,或退出SQL*PLUS。
下面命令使标题语句失效
TTITLEOFF
BTITLEOFF
TTITLEOFF
BTITLEOFF
列名
column命令定义用于显示列名
若名字为一个单词,不必加引号
columnenameheadingemployee
column命令定义用于显示列名
若名字为一个单词,不必加引号
columnenameheadingemployee
columnenameheading'employee|name'
(|为换行)
(|为换行)
取消栏定义
columnenameclear
columnenameclear
列的格式
columnenameformatA15
columnenameformatA15
columnsalformat$9,999.99
columncommlikesal
like子句,使得某一列的格式参照另一列格式,它拷贝列名及其格式
控制记录显示分组顺序
breakondeptno
(不显示重复值)
breakondeptno
(不显示重复值)
selectdeptno,ename
fromemp
orderbydeptno;
(ORDERBY子句用于控制BREAK)
fromemp
orderbydeptno;
(ORDERBY子句用于控制BREAK)
显示为
10clark
niller
20smith
scott
30allen
blake
10clark
niller
20smith
scott
30allen
blake
每次只有一个BREAK命令起作用,但一次可以在多个列上使用BREAK命令
breakon列名1on列名2
breakon列名1on列名2
记录分组
breakondeptnoskip2
selectdeptno,ename
fromemp
orderbydeptno;
breakondeptnoskip2
selectdeptno,ename
fromemp
orderbydeptno;
每个deptno之间空两行
clearbreak(取消BREAK命令)
breakonpage(每次从一新页开始)
breakonreport(每次从一新报表开始)
breakonpageonreport(联合使用)
clearbreak(取消BREAK命令)
breakonpage(每次从一新页开始)
breakonreport(每次从一新报表开始)
breakonpageonreport(联合使用)
分组计算
breakondeptnoskip2
computesumofsalondeptno
计算每个部门的工资总和
skip子句使部门之间的信息分隔开
breakondeptnoskip2
computesumofsalondeptno
计算每个部门的工资总和
skip子句使部门之间的信息分隔开
其他计算命令
computeavgofsalondeptno(平均值)
count非空值的总数
MAX最大值
MIN最小值
STD标准偏差
VAR协方差
NUMBER行数
computeavgofsalondeptno(平均值)
count非空值的总数
MAX最大值
MIN最小值
STD标准偏差
VAR协方差
NUMBER行数
使compute命令失效
一旦定义了COMPUTE,则一直有效,直到
关闭COMPUTE(clearcompute)
一旦定义了COMPUTE,则一直有效,直到
关闭COMPUTE(clearcompute)
SQL/PLUS环境命令
show选项
(显示当前参数设置情况)
show选项
(显示当前参数设置情况)
showall(显示全部参数)
设置参数
set选项值或开关
set选项值或开关
setautocommiton
SET命令包括
setautocommit{off|on|immediate}
(自动提交,OFF缺省)
setautocommit{off|on|immediate}
(自动提交,OFF缺省)
setecho{off|on}
(命令文件执行,是否在终端上显示命令本身,OFF缺省)
(命令文件执行,是否在终端上显示命令本身,OFF缺省)
setfeedback{off|on}
(ON:查询结束时,给出结果,记录数的信息,缺省;
OFF:无查询结果,记录数的信息)
(ON:查询结束时,给出结果,记录数的信息,缺省;
OFF:无查询结果,记录数的信息)
setheading{off|on}
(ON:列的头标在报表上显示,缺省;OFF:不在报表上显示)
(ON:列的头标在报表上显示,缺省;OFF:不在报表上显示)
setlinesize{n}
一行显示的最大字符数,缺省为80
一行显示的最大字符数,缺省为80
setpagesize{n}
每页的行数,缺省是14
每页的行数,缺省是14
setpause{off|on|text}
(ON:当报表超过一页时,每显示一屏就暂停显示,等待用户打回车键,再接着显示;
OFF:页与页不停顿,缺省;text:页与页停顿,并向用户提示信息)
(ON:当报表超过一页时,每显示一屏就暂停显示,等待用户打回车键,再接着显示;
OFF:页与页不停顿,缺省;text:页与页停顿,并向用户提示信息)
SETBUFFERbuffer
设置当头的命令缓冲区,通常情况下,SQL命令缓冲区已为当前缓冲区。
由于SQL命令缓冲区只能存放一条SQL命令,
所以可用其它缓冲区来存放SQL命令和SQL*PLUS命令。
设置当头的命令缓冲区,通常情况下,SQL命令缓冲区已为当前缓冲区。
由于SQL命令缓冲区只能存放一条SQL命令,
所以可用其它缓冲区来存放SQL命令和SQL*PLUS命令。
经常用到的设置可放在login.sql文件中。
SETNULL
setnull'nodata'
setnull'nodata'
selectename,comm
fromemp
wheredeptno=30;
把部门30中无佣金雇员的佣金显示为“NODATA”。
fromemp
wheredeptno=30;
把部门30中无佣金雇员的佣金显示为“NODATA”。
setnull是SQL*PLUS命令,用它来标识空值(NULL),可以设置为任意字符串。
存盘命令SAVE
save文件名
save文件名
input
1selectempno,ename,job
2fromemp
3wherejob='analyst'
1selectempno,ename,job
2fromemp
3wherejob='analyst'
saveresearch
目录中会增加一个research.sql文件。
编辑命令EDIT
edit
edit
EDIT编辑当前缓冲区中的内容。
编辑一个文件
editresearch
editresearch
调入命令GET
getresearch
把磁盘上的文件内容调入缓冲区,并显示在屏幕上,文件名尾不必加.sql。
getresearch
把磁盘上的文件内容调入缓冲区,并显示在屏幕上,文件名尾不必加.sql。
START命令
运行指定的文件
startresearch
运行指定的文件
startresearch
输出命令SPOOL
spooltryfile
不仅可以使查询结果在屏幕上显示,还可以使结果存入文件
spooltryfile
不仅可以使查询结果在屏幕上显示,还可以使结果存入文件
停止向文件输出
spooloff
spooloff
把查询结果在打印机上输出,先把它们存入一个文件中,
然后不必使用SPOOLOFF,而用:
spoolout
SPOOLOUT关闭该文件并在系统缺省的打印机上输出
然后不必使用SPOOLOFF,而用:
spoolout
SPOOLOUT关闭该文件并在系统缺省的打印机上输出
制作报表举例
edittryfile
edittryfile
setechooff
setautocommiton
setpagesize25
insertintoemp(empno,ename,hiredate)
values(9999,'geiger',sysdate);
insertintoemp(empno,ename,deptno)
values(3333,'samson',20);
spoolnew_emp
select*fromemp
wheredeptno=20
ordeptnoisnull
/
spooloff
setautocommitoff
setautocommiton
setpagesize25
insertintoemp(empno,ename,hiredate)
values(9999,'geiger',sysdate);
insertintoemp(empno,ename,deptno)
values(3333,'samson',20);
spoolnew_emp
select*fromemp
wheredeptno=20
ordeptnoisnull
/
spooloff
setautocommitoff
用start命令执行这个文件
--------
6函数
字符型函数
initcap(ename);将ename中每个词的第一个字母改为大写。
如:jacksmith--JackSmith
6函数
字符型函数
initcap(ename);将ename中每个词的第一个字母改为大写。
如:jacksmith--JackSmith
length(ename);计算字符串的长度。
substr(job,1,4);
其它
lower
upper
least取出字符串列表中按字母排序排在最前面的一个串
greatest取出字符串列表中按字母排序排在最后的一个串
lower
upper
least取出字符串列表中按字母排序排在最前面的一个串
greatest取出字符串列表中按字母排序排在最后的一个串
日期函数
add_month(hiredate,5)在雇佣时间上加5个月
month_between(sysdate,hiredate)计算雇佣时间与系统时间之间相差的月数
next_day(hiredate,'FRIDAY')计算受雇日期之后的第一个星期五的日期
add_month(hiredate,5)在雇佣时间上加5个月
month_between(sysdate,hiredate)计算雇佣时间与系统时间之间相差的月数
next_day(hiredate,'FRIDAY')计算受雇日期之后的第一个星期五的日期
例
selectename,sal,next_day(sysdate,'FRIDAY')as_of
fromemp
wheredeptno=20;
(as_of是别名)
selectename,sal,next_day(sysdate,'FRIDAY')as_of
fromemp
wheredeptno=20;
(as_of是别名)
如果不用to_char函数,日期在ORACLE中的缺省格式是'DD_MON_YY'
to_char(date,datepicture)
to_char(date,datepicture)
selectename,to_char(hiredate,'DyMondd,yyyy')hired
fromemp
wheredeptno=10;
fromemp
wheredeptno=10;
to_date(字符串,格式)
insertintoemp(empno,ename,hiredate)
values(7999,'asms',to_date('070387083000','MMDDYYHHMISS'));
values(7999,'asms',to_date('070387083000','MMDDYYHHMISS'));
日期型数据的格式
dd12
dyfri
dayfriday
ddspthtwelfth
dd12
dyfri
dayfriday
ddspthtwelfth
mm03
monmar
monthmarch
monmar
monthmarch
yy87
yyyy1987
yyyy1987
例
Mar12,1987'Mondd,yyyy'
MAR12,1987'MONdd,yyyy'
ThursdayMARCH12'DayMONTHdd'
Mar1211:00am'Monddhh:miam'
Thu,thetwelfth'Dy,"the"ddspth'
Mar12,1987'Mondd,yyyy'
MAR12,1987'MONdd,yyyy'
ThursdayMARCH12'DayMONTHdd'
Mar1211:00am'Monddhh:miam'
Thu,thetwelfth'Dy,"the"ddspth'
算术函数
least(v1,v2)
least(v1,v2)
selectename,empno,mgr,least(empno,mgr)lownum
fromemp
whereempno0
fromemp
whereempno0
trunc(sal,0)
取sal的近似值(截断)
取sal的近似值(截断)
空值函数
nvl(v1,v2)
v1为列名,如果v1不是空值,nvl返回其列值。
v1为空值,返回v2的值。
nvl(v1,v2)
v1为列名,如果v1不是空值,nvl返回其列值。
v1为空值,返回v2的值。
聚组函数
selectsum(comm)
fromemp;
(返回一个汇总信息)
不能把sum用在select语句里除非用groupby
selectsum(comm)
fromemp;
(返回一个汇总信息)
不能把sum用在select语句里除非用groupby
字符型、日期型、数字型的聚组函数
minmaxcount可用于任何数据类型
minmaxcount可用于任何数据类型
selectmin(ename)
fromemp;
fromemp;
selectmin(hiredate)
fromemp;
fromemp;
selectmin(sal)
fromemp;
fromemp;
有多少人有工作?
selectcount(job)
fromemp;
selectcount(job)
fromemp;
有多少种不同的工种?
selectcount(distinctjob)
fromemp;
selectcount(distinctjob)
fromemp;
countdistinct计算某一字段中不同的值的个数
其它聚组函数(只用于数字型数据)
avg计算平均工资
selectavg(sal)
fromemp;
avg计算平均工资
selectavg(sal)
fromemp;
stddev计算工资的平均差
selectstddev(sal)
fromemp;
selectstddev(sal)
fromemp;
sum计算总工资
selectsum(sal)
fromemp;
selectsum(sal)
fromemp;
groupby子句
selectdeptno,sum(sal),avg(sal)
fromemp
groupbydeptno;
selectdeptno,sum(sal),avg(sal)
fromemp
groupbydeptno;
按多个条件分组
每个部门的雇员数
selectdeptno,count(*)
fromemp
groupbydeptno;
每个部门的雇员数
selectdeptno,count(*)
fromemp
groupbydeptno;
每个部门的每个工种的雇员数
selectdeptno,job,count(*)
fromemp
groupbydeptno,job;
selectdeptno,job,count(*)
fromemp
groupbydeptno,job;
满足条件的分组
(where是针对select的,having是针对groupby的)
哪些部门的工资总和超过了9000
selectdeptno,sum(sal)
fromemp
groupbydeptno
havingsum(sal)>9000;
(where是针对select的,having是针对groupby的)
哪些部门的工资总和超过了9000
selectdeptno,sum(sal)
fromemp
groupbydeptno
havingsum(sal)>9000;
select小结
除去职员,哪些部门的工资总和超过了8000
selectdeptno,sum(sal)
fromemp
wherejob!='clerk'
groupbydeptno
havingsum(sal)>8000
orderbysum(sal);
除去职员,哪些部门的工资总和超过了8000
selectdeptno,sum(sal)
fromemp
wherejob!='clerk'
groupbydeptno
havingsum(sal)>8000
orderbysum(sal);
---------
7高级查询
等值联接
selectempno,ename,job,emp.deptno,dname
fromemp,dept
whereemp.deptno=dept.deptno;
7高级查询
等值联接
selectempno,ename,job,emp.deptno,dname
fromemp,dept
whereemp.deptno=dept.deptno;
外联接
selectename,dept.deptno,loc
fromemp,dept
whereemp.deptno(+)=dept.deptno;
如果在dept.deptno中有的数值,在emp.deptno中没有(如deptno=40),
则作外联接时,结果中会产生一个空值
selectename,dept.deptno,loc
fromemp,dept
whereemp.deptno(+)=dept.deptno;
如果在dept.deptno中有的数值,在emp.deptno中没有(如deptno=40),
则作外联接时,结果中会产生一个空值
自联接:同一基表的不同行要做联接,可使用自联接
指出每个雇员的经理名字
selectworker.ename,manager.enamemanager
fromempworker,empmanager
whereworker.mgr=manager.empno;
指出每个雇员的经理名字
selectworker.ename,manager.enamemanager
fromempworker,empmanager
whereworker.mgr=manager.empno;
非等值联接
哪些雇员的工资属于第三级别
selectename,sal
fromemp,salgrade
wheregrade=3
andsalbetweenlosalandhisal;
(基表salgrade:gradelosalhisal)
哪些雇员的工资属于第三级别
selectename,sal
fromemp,salgrade
wheregrade=3
andsalbetweenlosalandhisal;
(基表salgrade:gradelosalhisal)
集合运算
行的连接
集合运算把2个或多个查询结果合并为一个
union-setunion
Rowsoffirstqueryplusofsecondquery,lessduplicaterows
行的连接
集合运算把2个或多个查询结果合并为一个
union-setunion
Rowsoffirstqueryplusofsecondquery,lessduplicaterows
intersect-setintersection
Rowsbothquerieshaveincommon
Rowsbothquerieshaveincommon
minus-setdifference
rowsuniquetothefirstquery
rowsuniquetothefirstquery
介绍几个视图
accountview
enamesaljob
accountview
enamesaljob
salesview
enamesaljob
enamesaljob
researchview
enamesaljob
enamesaljob
union运算
返回一个查询结果中有但又不重要的行,它将基表或视图中的记录合并在一起
所有部门中有哪些雇员工资超过2000
对应列的数据类型必须相同
selectename,sal
fromaccount
wheresal>2000
union
selectename,sal
fromresearch
wheresal>2000
union
selectename,sal
fromsales
wheresal>2000;
返回一个查询结果中有但又不重要的行,它将基表或视图中的记录合并在一起
所有部门中有哪些雇员工资超过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;
返回查询结果中相同的部分
各个部门中有哪些相同的工种
selectjob
fromaccount
intersect
selectjob
fromresearch
intersect
selectjob
fromsales;
minus运算
返回在第一个查询结果中与第二个查询结果不相同的那部分行记录。
有哪些工种在财会部中有,而在销售部中没有?
selectjobfromaccount
minus
selectjobfromsales;
返回在第一个查询结果中与第二个查询结果不相同的那部分行记录。
有哪些工种在财会部中有,而在销售部中没有?
selectjobfromaccount
minus
selectjobfromsales;
子查询
slectename,deptno
fromemp
wheredeptno=
(selectdeptno
fromemp
whereename='smith');
slectename,deptno
fromemp
wheredeptno=
(selectdeptno
fromemp
whereename='smith');
多级子查询
selectename,job,sal
fromemp
wherejob=
(selectjob
fromemp
whereename='clark')
orsal>
(selectsal
fromemp
whereename='clark');
selectename,job,sal
fromemp
wherejob=
(selectjob
fromemp
whereename='clark')
orsal>
(selectsal
fromemp
whereename='clark');
多个基表与子查询
selectename,job,sal
fromemp,dept
whereloc='newyork'
andemp.deptno=dept.deptno
andsal>
(selectsal
fromemp
whereename='scott');
selectename,job,sal
fromemp,dept
whereloc='newyork'
andemp.deptno=dept.deptno
andsal>
(selectsal
fromemp
whereename='scott');
子查询中使用聚组函数
selectename,hiredate
fromemp
wherehiredate=
(selectmin(hiredate)
fromemp);
selectename,hiredate
fromemp
wherehiredate=
(selectmin(hiredate)
fromemp);
------------
8授权
系统权限
DBA所有权限
RESOURCE注册,创建新的基表
CONNECT,注册,查询
8授权
系统权限
DBA所有权限
RESOURCE注册,创建新的基表
CONNECT,注册,查询
只有DBA才有权创建新的用户
grantconnecttoscott
identifiedbytiger;
grantconnecttoscott
identifiedbytiger;
DBA或用户自己可以改变用户口令
grantconnecttoscott
identifiedbyleopard;
grantconnecttoscott
identifiedbyleopard;
基表权限1
有两种方法获得对基表操作的权限
有两种方法获得对基表操作的权限
创建自己的基表
获得基表创建用户的许可
grantselect,insert
onemp
toscott;
获得基表创建用户的许可
grantselect,insert
onemp
toscott;
这些权限有
selectinsertupdatedeletealterindex
selectinsertupdatedeletealterindex
把所有权限授于他人
grantallonemptoscott;
grantallonemptoscott;
同义词
select*
fromscott.emp
select*
fromscott.emp
创建同义词
为用户allen的EMP基表创建同义词employee
createsynonymemployee
forallen.emp
为用户allen的EMP基表创建同义词employee
createsynonymemployee
forallen.emp
基表权限2
你可以使其他用户有这样的权力,即其他用户可以把你的基表权限授予他人
grantall
onemp
toscott
withgrantoption;
你可以使其他用户有这样的权力,即其他用户可以把你的基表权限授予他人
grantall
onemp
toscott
withgrantoption;
收回权限
系统权限只有被DBA收回
系统权限只有被DBA收回
基表权限随时都可以收回
revokeinsert
onemp
fromscott;
onemp
fromscott;
---------
9索引
建立索引
createindexemp_ename
onemp(ename);
9索引
建立索引
createindexemp_ename
onemp(ename);
删除索引
dropindexemp_ename;
dropindexemp_ename;
关于索引
只对较大的基表建立索引(至少50条记录)
建立索引之前插入数据
对一个基表可建立任意多个索引
一般是在作为主键的列上建立索引
建立索引之后,不影响SQL命令的执行
建立索引之后,ORACLE自动维护和使用索引
只对较大的基表建立索引(至少50条记录)
建立索引之前插入数据
对一个基表可建立任意多个索引
一般是在作为主键的列上建立索引
建立索引之后,不影响SQL命令的执行
建立索引之后,ORACLE自动维护和使用索引
保证数据唯一性
提高执行速度的同时,索引还可以保证每个记录中的每个列值是不重复的。
createuniqueindexemp_empno
onemp(empno);
提高执行速度的同时,索引还可以保证每个记录中的每个列值是不重复的。
createuniqueindexemp_empno
onemp(empno);
--------
练习和答案
练习和答案
有没有工资比奖金多的雇员?如果有,按工资的降序排列。
如果有两个以上的雇员工资相同,按他们的名字排序。
selectenameemployee,salsalary,commcommision
fromemp
wheresal>comm
orderbysaldesc,ename;
如果有两个以上的雇员工资相同,按他们的名字排序。
selectenameemployee,salsalary,commcommision
fromemp
wheresal>comm
orderbysaldesc,ename;
列出有关雇员姓名、奖金占收百分比的信息。
要求显示时列名意义明确,按雇员姓名排序,不包括奖金未知的雇员。
selectenameemployee,(comm/(comm+sal))*100incentive
fromemp
wherecommisnotnull
orderbyename;
要求显示时列名意义明确,按雇员姓名排序,不包括奖金未知的雇员。
selectenameemployee,(comm/(comm+sal))*100incentive
fromemp
wherecommisnotnull
orderbyename;
在chicago(部门30)工作的所有雇员的工资上涨10%。
updateemp
setsal=1.1*sal
wheredeptno=30;
updateemp
setsal=1.1*sal
wheredeptno=30;
updateemp
setsal=1.1*sal
wheredeptno=(selectdeptno
fromdept
whereloc='chicago');
setsal=1.1*sal
wheredeptno=(selectdeptno
fromdept
whereloc='chicago');
为hitech公司新建一个部门,编号为50,其它信息均不可知。
insertintodept(dname,deptno)
values('faclities',50);
insertintodept(dname,deptno)
values('faclities',50);
创建视图,三个列名,其中不包括职员信息
createviewemployee("employeename",
"employeenumber",
"employeejob")
asselectename,empno,job
fromemp
wherejob!='clerk';
createviewemployee("employeename",
"employeenumber",
"employeejob")
asselectename,empno,job
fromemp
wherejob!='clerk';
制作工资报表,包括雇员姓名、受雇时间(按星期计算),工资和部门编号,
一页显示一个部门的信息,每页尾,显示该页的工资之和以及受雇时间之和,
报表结尾处,显示所有雇员的工资总和以及受雇时间总和,
工资按美元计算,受雇时间按星期计算,每页的上方应有标题。
ttitle'service'
breakondeptnoonpageonreport
computesumofsalondeptno
computesumofsalonreport
computesumofservice_lengthondeptno
computesumofservice_lengthonreport
columnsalformat$99,999.00
columnservice_lengthformat9999
selectdeptno,enameemployee,(sysdate-hiredate)/7service_length,sal
fromemp
orderbydeptno;
一页显示一个部门的信息,每页尾,显示该页的工资之和以及受雇时间之和,
报表结尾处,显示所有雇员的工资总和以及受雇时间总和,
工资按美元计算,受雇时间按星期计算,每页的上方应有标题。
ttitle'service'
breakondeptnoonpageonreport
computesumofsalondeptno
computesumofsalonreport
computesumofservice_lengthondeptno
computesumofservice_lengthonreport
columnsalformat$99,999.00
columnservice_lengthformat9999
selectdeptno,enameemployee,(sysdate-hiredate)/7service_length,sal
fromemp
orderbydeptno;
制作报表,包括雇员姓名、总收入和受佣日期,
且:姓名的第一个字母必须大写,雇佣日期格式为MM/DD/YYYY,
总收入包括没有奖金的雇员的总收入,姓名按字母顺序排列。
col"hiredate"formatA12
col"employee"formatA10
col"compensation"format$99,999.00
selectinitcap(ename)"employee",
(sal+nvl(comm,0))"compensation",
to_char(hiredate,'MM/DD/YYYY')"hiredate"
fromemp
orderbyename;
且:姓名的第一个字母必须大写,雇佣日期格式为MM/DD/YYYY,
总收入包括没有奖金的雇员的总收入,姓名按字母顺序排列。
col"hiredate"formatA12
col"employee"formatA10
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);
selectnation,area
fromnation
wherecodein
(selectnation_code
fromborder
groupbynation_code
havingcount(*)>7);
列出所有面积大于等于日本的岛国的国名和人口。
selectnation,population
fromnation,border
wherecode=nation_code(+)
andnation_codeisnull
andarea>=
(selectarea
fromnation
whereupper(nation)='JAPAN');
selectnation,population
fromnation,border
wherecode=nation_code(+)
andnation_codeisnull
andarea>=
(selectarea
fromnation
whereupper(nation)='JAPAN');
列出所有边界在其它国家中的国名,并且显示其边界国家名字。
breakonnation
selectnation1.nation,
nation2.nationborderin_country
fromnationnation1,border,nationnation2
wherenation1.code=border.nation_code
andborder.border_code=nation2.code
orderbynation1.nation;
breakonnation
selectnation1.nation,
nation2.nationborderin_country
fromnationnation1,border,nationnation2
wherenation1.code=border.nation_code
andborder.border_code=nation2.code
orderbynation1.nation;
-----------
-----------
PL/SQL
-----------
PL/SQL
2PL/SQL的块结构和数据类型
块结构的特点
嵌套
begin
......
begin
......
exception
......
end;
exception
......
end;
嵌套
begin
......
begin
......
exception
......
end;
exception
......
end;
标识符:
不能超过30个字符
第一个字符必须为字母
其余字符可以是字母,数字,$,_,或#
不区分大小写形式
如果用双引号括起来,则字符顺序可以任意排列,并区分大小写形式
无SQL保留字
不能超过30个字符
第一个字符必须为字母
其余字符可以是字母,数字,$,_,或#
不区分大小写形式
如果用双引号括起来,则字符顺序可以任意排列,并区分大小写形式
无SQL保留字
数据类型
数字型:
整数,实数,以及指数
数字型:
整数,实数,以及指数
字符串:
用单引号括起来
若在字符串表示单引号,则使用两个单引号
字符串长度为零(两个单引号之间没有字符),则表示NULL
用单引号括起来
若在字符串表示单引号,则使用两个单引号
字符串长度为零(两个单引号之间没有字符),则表示NULL
字符:
长度为1的字符串
长度为1的字符串
数据定义
语法
标识符[常数>数据类型[NOTNULL>[:=PL/SQL表达式>;
':='表示给变量赋值
语法
标识符[常数>数据类型[NOTNULL>[:=PL/SQL表达式>;
':='表示给变量赋值
数据类型包括
数字型number(7,2)
字符型char(120)
日期型date
布尔型boolean(取值为true,false或null,不存贮在数据库中)
数字型number(7,2)
字符型char(120)
日期型date
布尔型boolean(取值为true,false或null,不存贮在数据库中)
日期型
anniversarydate:='05-JUL-95';
project_completiondate;
anniversarydate:='05-JUL-95';
project_completiondate;
布尔型
over_budgetbooleannotnull:=false;
availableboolean;
(初始值为NULL)
over_budgetbooleannotnull:=false;
availableboolean;
(初始值为NULL)
%type类型匹配
books_printednumber(6);
books_soldbook_printed%type;
manager_nameemp.ename%type;
books_printednumber(6);
books_soldbook_printed%type;
manager_nameemp.ename%type;
变量赋值
变量名:=PL/SQL表达式
numvar:=5;
boolvar:=true;
datevar:='11-JUN-87';
变量名:=PL/SQL表达式
numvar:=5;
boolvar:=true;
datevar:='11-JUN-87';
字符型、数字型表达式中的空值
null+<数字>=null(空值加数字仍是空值)
null><数字>=null(空值与数字进行比较,结果仍是空值)
null||'字符串'='字符串'(null即'')
(空值与字符串进行连接运算,结果为原字符串)
null+<数字>=null(空值加数字仍是空值)
null><数字>=null(空值与数字进行比较,结果仍是空值)
null||'字符串'='字符串'(null即'')
(空值与字符串进行连接运算,结果为原字符串)
变量作用范围
标识符在宣言它的块中有效
标识符如果不在子块中重新定义,则在PL/SQL块的所有子块中同样有效
重新定义后的标识符,作用范围仅在本子块中有效
标识符在宣言它的块中有效
标识符如果不在子块中重新定义,则在PL/SQL块的所有子块中同样有效
重新定义后的标识符,作用范围仅在本子块中有效
例
declare
e_messchar(80);
begin
/*子块1*/
declare
v1number(4);
begin
selectempnointov1fromemp
wherejob='president';
exception
whentoo_many_rowsthen
insertintojob_errors
values('morethanonepresident');
end;
/*子块2*/
declare
v1number(4);
begin
selectempnointov1fromemp
wherejob='manager';
exception
whentoo_many_rowsthen
insertintojob_errors
values('morethanonemanager');
end;
exception
whenothersthen
e_mess:=substr(sqlerrm,1,80);
insertintogeneralerrorsvalues(e_mess);
end;
declare
e_messchar(80);
begin
/*子块1*/
declare
v1number(4);
begin
selectempnointov1fromemp
wherejob='president';
exception
whentoo_many_rowsthen
insertintojob_errors
values('morethanonepresident');
end;
/*子块2*/
declare
v1number(4);
begin
selectempnointov1fromemp
wherejob='manager';
exception
whentoo_many_rowsthen
insertintojob_errors
values('morethanonemanager');
end;
exception
whenothersthen
e_mess:=substr(sqlerrm,1,80);
insertintogeneralerrorsvalues(e_mess);
end;
---------
3SQL和PL/SQL
3SQL和PL/SQL
插入
declare
my_salnumber(7,2):=3040.55;
my_enamechar(25):='wanda';
my_hiredatedate:='08-SEP-88';
begin
insertintoemp
(empno,enmae,job,hiredate,sal,deptno)
values(2741,my_ename,'cabdriver',my_hiredate,my_sal,20);
end;
declare
my_salnumber(7,2):=3040.55;
my_enamechar(25):='wanda';
my_hiredatedate:='08-SEP-88';
begin
insertintoemp
(empno,enmae,job,hiredate,sal,deptno)
values(2741,my_ename,'cabdriver',my_hiredate,my_sal,20);
end;
删除
declare
bad_child_typechar(20):='naughty';
begin
deletefromsantas_gift_listwhere
kid_rating=bad_child_type;
end;
declare
bad_child_typechar(20):='naughty';
begin
deletefromsantas_gift_listwhere
kid_rating=bad_child_type;
end;
事务处理
commit[WORK>;
rollback[WORK>;
(关键字WORK可选,但对命令执行无任何影响)
savepoint标记名;(保存当前点)
在事务中标记当前点
rollback[WORK>to[SAVEPOINT>标记名;(回退到当前保存点)
取消savepoint命令之后的所有对数据库的修改
关键字WORK和SAVEPOINT为可选项,对命令执行无任何影响
commit[WORK>;
rollback[WORK>;
(关键字WORK可选,但对命令执行无任何影响)
savepoint标记名;(保存当前点)
在事务中标记当前点
rollback[WORK>to[SAVEPOINT>标记名;(回退到当前保存点)
取消savepoint命令之后的所有对数据库的修改
关键字WORK和SAVEPOINT为可选项,对命令执行无任何影响
函数
PL/SQL块中可以使用SQL命令的所有函数
insertintophonebook(lastname)value(upper(my_lastname));
selectavg(sal)intoavg_salfromemp;
PL/SQL块中可以使用SQL命令的所有函数
insertintophonebook(lastname)value(upper(my_lastname));
selectavg(sal)intoavg_salfromemp;
对于非SQL命令,可使用大多数个体函数
不能使用聚组函数和参数个数不定的函数,如
x:=sqrt(y);
lastname:=upper(lastname);
age_diff:=months_between(birthday1,birthday2)/12;
不能使用聚组函数和参数个数不定的函数,如
x:=sqrt(y);
lastname:=upper(lastname);
age_diff:=months_between(birthday1,birthday2)/12;
赋值时的数据类型转换
4种赋值形式:
变量名:=表达式
insertinto基表名values(表达式1,表达式2,...);
update基表名set列名=表达式;
select列名into变量名from...;
4种赋值形式:
变量名:=表达式
insertinto基表名values(表达式1,表达式2,...);
update基表名set列名=表达式;
select列名into变量名from...;
数据类型间能进行转换的有:
char转成number
number转成char
char转成date
date转成char
char转成number
number转成char
char转成date
date转成char
例
char_var:=nm_var;
数字型转换成字符型
date_var:='25-DEC-88';
字符型转换成日期型
insertinto表名(num_col)values('604badnumber');
错误,无法成功地转换数据类型
char_var:=nm_var;
数字型转换成字符型
date_var:='25-DEC-88';
字符型转换成日期型
insertinto表名(num_col)values('604badnumber');
错误,无法成功地转换数据类型
---------
4条件控制
例
declare
num_jobsnumber(4);
begin
selectcount(*)intonum_jobsfromauditions
whereactorid=&&actor_idandcalled_back='yes';
ifnum_jobs>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;
4条件控制
例
declare
num_jobsnumber(4);
begin
selectcount(*)intonum_jobsfromauditions
whereactorid=&&actor_idandcalled_back='yes';
ifnum_jobs>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+1;
exitwhenctr=100;
endloop;
end;
(注:如果ctr取为NULL,循环无法结束)
5循环
语法
loop
......
endloop;
exit;(退出循环)
exit[when>;(退出循环,当满足WHEN时)
例1
declare
ctrnumber(3):=0;
begin
loop
insertintotable1values('tastesgreat');
insertintotable2values('lessfilling');
ctr:=ctr+1;
exitwhenctr=100;
endloop;
end;
(注:如果ctr取为NULL,循环无法结束)
例2
FOR语法
for变量<范围>loop
......
endloop;
FOR语法
for变量<范围>loop
......
endloop;
declare
my_indexchar(20):='fettucinialfredo';
bowlchar(20);
begin
formy_indexinreverse21..30loop
insertintotemp(coll)values(my_index);
/*循环次数从30到21*/
endloop;
bowl:=my_index;
end;
跟在inreverse后面的数字必须是从小到大的顺序,必须是整数,不能是变量或表达式
my_indexchar(20):='fettucinialfredo';
bowlchar(20);
begin
formy_indexinreverse21..30loop
insertintotemp(coll)values(my_index);
/*循环次数从30到21*/
endloop;
bowl:=my_index;
end;
跟在inreverse后面的数字必须是从小到大的顺序,必须是整数,不能是变量或表达式
----------
6游标
显式游标
6游标
显式游标
打开游标
open<游标名>
例
opencolor_cur;
open<游标名>
例
opencolor_cur;
游标属性
%notfound
%found
%rowcount
%isopen
例
fetchmy_curintomy_var;
whilemy_cur%foundloop
(处理数据)
fetchmy_curintomy_var;
exitwhenmy_cur%rowcount=10;
endloop;
%notfound
%found
%rowcount
%isopen
例
fetchmy_curintomy_var;
whilemy_cur%foundloop
(处理数据)
fetchmy_curintomy_var;
exitwhenmy_cur%rowcount=10;
endloop;
%notfound属性
取值情况如下:
fetch操作没有返回记录,则取值为true
fetch操作返回一条记录,则取值为false
对游标无fetch操作时,取值为null
<游标名>%notfound
例
ifcolor_cur%notfoundthen...
注:如果没有fetch操作,则<游标名>%notfound将导致出错,
因为%notfound的初始值为NULL。
取值情况如下:
fetch操作没有返回记录,则取值为true
fetch操作返回一条记录,则取值为false
对游标无fetch操作时,取值为null
<游标名>%notfound
例
ifcolor_cur%notfoundthen...
注:如果没有fetch操作,则<游标名>%notfound将导致出错,
因为%notfound的初始值为NULL。
关闭游标
close<游标名>
例
closecolor_cur;
close<游标名>
例
closecolor_cur;
游标的FOR循环
语法
for<记录名>in<游标名>loop
<一组命令>
endloop;
其中:
索引是建立在每条记录的值之上的
记录名不必声明
每个值对应的是记录名,列名
初始化游标指打开游标
活动集合中的记录自动完成FETCH操作
退出循环,关闭游标
语法
for<记录名>in<游标名>loop
<一组命令>
endloop;
其中:
索引是建立在每条记录的值之上的
记录名不必声明
每个值对应的是记录名,列名
初始化游标指打开游标
活动集合中的记录自动完成FETCH操作
退出循环,关闭游标
隐式游标
隐式游标是指SQL命令中用到的,没有明确定义的游标
insert,update,delete,select语句中不必明确定义游标
调用格式为SQL%
存贮有关最新一条SQL命令的处理信息
隐式游标是指SQL命令中用到的,没有明确定义的游标
insert,update,delete,select语句中不必明确定义游标
调用格式为SQL%
存贮有关最新一条SQL命令的处理信息
隐式游标的属性
隐式游标有四个属性
SQL%NOTFOUND
SQL%FOUND
SQL%ROWCOUNT:隐式游标包括的记录数
例:
deletefrombaseball_teamwherebatting_avg<100;
ifsql%rowcount>5thn
insertintotemp
values('yourteamneedshelp');
endif;
隐式游标有四个属性
SQL%NOTFOUND
SQL%FOUND
SQL%ROWCOUNT:隐式游标包括的记录数
例:
deletefrombaseball_teamwherebatting_avg<100;
ifsql%rowcount>5thn
insertintotemp
values('yourteamneedshelp');
endif;
SQL%ISOPEN:取值总为FALSE。SQL命令执行完毕,PL/SQL立即关闭隐式游标。
---------
7标号
GOTO语句
用法:
gotoyou_are_here;
其中you_are_here是要跳转的语句标号
标号必须在同一组命令,或是同一块中使用
7标号
GOTO语句
用法:
gotoyou_are_here;
其中you_are_here是要跳转的语句标号
标号必须在同一组命令,或是同一块中使用
正确的使用
<>(标号)
x:=x+1
ifa>bthen
b:=b+c;
gotodinner;
endif;
<>(标号)
x:=x+1
ifa>bthen
b:=b+c;
gotodinner;
endif;
错误的使用
gotojail;
ifa>bthen
b:=b+c;
<>(标号)
x:=x+1;
endif;
gotojail;
ifa>bthen
b:=b+c;
<>(标号)
x:=x+1;
endif;
标号:解决意义模糊
标号可用于定义列值的变量
<>
declare
deptnonumber:=20;
begin
updateempsetsal=sal*1.1
wheredeptno=sample.deptno;
commit;
endsample;
如果不用标号和标号限制符,这条命令将修改每条记录。
标号可用于定义列值的变量
<>
declare
deptnonumber:=20;
begin
updateempsetsal=sal*1.1
wheredeptno=sample.deptno;
commit;
endsample;
如果不用标号和标号限制符,这条命令将修改每条记录。
----------
8异常处理
预定义的异常情况
任何ORACLE错误都将自动产生一个异常信息
一些异常情况已命名,如:
no_data_found当SELECT语句无返回记录时产生
too_many_rows没有定义游标,而SELECT语句返回多条记录时产生
whenevernotfound无对应的记录
8异常处理
预定义的异常情况
任何ORACLE错误都将自动产生一个异常信息
一些异常情况已命名,如:
no_data_found当SELECT语句无返回记录时产生
too_many_rows没有定义游标,而SELECT语句返回多条记录时产生
whenevernotfound无对应的记录
用户定义的异常情况
由用户自己获取
在DECLARE部分定义:
declare
xnumber;
something_isnt_rightexception;
用户定义的异常情况遵循一般的作用范围规则
条件满足时,获取异常情况:raisesomething_isnt_right
注意:同样可以获取预定义的异常情况
由用户自己获取
在DECLARE部分定义:
declare
xnumber;
something_isnt_rightexception;
用户定义的异常情况遵循一般的作用范围规则
条件满足时,获取异常情况:raisesomething_isnt_right
注意:同样可以获取预定义的异常情况
exception_init语句
允许为ORACLE错误命名
允许为ORACLE错误命名
调用格式:
pragmaexception_init(<表达式>,);
例
declare
deadlock_detectedexception;
pragmaexception_init(deadlock_detected,-60);
pragmaexception_init(<表达式>,);
例
declare
deadlock_detectedexception;
pragmaexception_init(deadlock_detected,-60);
raise语句
单独使用RAISE命令,可再一次获取当前的异常情况(就象异常情况被重复处理了一样)。
在异常处理中,此语句只能单独使用。
单独使用RAISE命令,可再一次获取当前的异常情况(就象异常情况被重复处理了一样)。
在异常处理中,此语句只能单独使用。
异常处理标识符
一组用于处理异常情况的语句:
exception
when<表达式>or[表达式...>then
<一组语句>
...
whenothersthen--最后一个处理
<一组语句>
end;既结束PL/SQL块部分,也结束异常处理部分
一组用于处理异常情况的语句:
exception
when<表达式>or[表达式...>then
<一组语句>
...
whenothersthen--最后一个处理
<一组语句>
end;既结束PL/SQL块部分,也结束异常处理部分
--------
练习与答案
1:
接收contract_no和item_no值,在inventory表中查找,如果产品:
已发货,在arrival_date中赋值为今天后的7天
已订货,在arrival_date中赋值为今天后的一个月
既无订货又无发货,则在arrival_date中赋值为今天后的两个月,
并在order表中增加一条新的订单记录。
练习与答案
1:
接收contract_no和item_no值,在inventory表中查找,如果产品:
已发货,在arrival_date中赋值为今天后的7天
已订货,在arrival_date中赋值为今天后的一个月
既无订货又无发货,则在arrival_date中赋值为今天后的两个月,
并在order表中增加一条新的订单记录。
product_status的列值为'shipped'和'ordered'
inventory:
product_idnumber(6)
product_descriptionchar(30)
product_statuschar(20)
std_shipping_qtynumber(3)
product_idnumber(6)
product_descriptionchar(30)
product_statuschar(20)
std_shipping_qtynumber(3)
contract_item:
contract_nonumber(12)
item_nonumber(6)
arrival_datedate
contract_nonumber(12)
item_nonumber(6)
arrival_datedate
order:
order_idnumber(6)
product_idnumber(6)
qtynumber(3)
order_idnumber(6)
product_idnumber(6)
qtynumber(3)
答案:
declare
i_product_idinventory.product_id%type;
i_product_descriptioninventory.product_description%type;
i_product_statusinventory.product_status%type;
i_std_shipping_qtyinventory.std_shipping_qty%type;
declare
i_product_idinventory.product_id%type;
i_product_descriptioninventory.product_description%type;
i_product_statusinventory.product_status%type;
i_std_shipping_qtyinventory.std_shipping_qty%type;
begin
selectproduct_id,product_description,product_status,std_shipping_qty
intoi_product_id,i_product_description,
i_product_status,i_std_shipping_qty
frominventory
whereproduct_id=(
selectproduct_id
fromcontract_item
wherecontract_no=&&contractnoanditem_no=&&itemno);
ifi_product_status='shipped'then
updatecontract_item
setarrival_date=sysdate+7
whereitem_no=&&itemnoandcontract_no=&&contractno;
elsifi_product_status='ordered'then
updatecontract_item
setarrival_date=add_months(sysdate,1)
whereitem_no=&&itemnoandcontract_no=&&contractno;
else
updatecontract_item
setarrival_date=add_months(sysdate,2)
whereitem_no=&&itemnoandcontract_no=&&contractno;
insertintoorders
values(100,i_product_id,i_std_shipping_qty);
endif;
endif;
commit;
end;
selectproduct_id,product_description,product_status,std_shipping_qty
intoi_product_id,i_product_description,
i_product_status,i_std_shipping_qty
frominventory
whereproduct_id=(
selectproduct_id
fromcontract_item
wherecontract_no=&&contractnoanditem_no=&&itemno);
ifi_product_status='shipped'then
updatecontract_item
setarrival_date=sysdate+7
whereitem_no=&&itemnoandcontract_no=&&contractno;
elsifi_product_status='ordered'then
updatecontract_item
setarrival_date=add_months(sysdate,1)
whereitem_no=&&itemnoandcontract_no=&&contractno;
else
updatecontract_item
setarrival_date=add_months(sysdate,2)
whereitem_no=&&itemnoandcontract_no=&&contractno;
insertintoorders
values(100,i_product_id,i_std_shipping_qty);
endif;
endif;
commit;
end;
2:
1.找出指定部门中的所有雇员
2.用带'&'的变量提示用户输入部门编号
3.把雇员姓名及工资存入prnttable表中,基结构为:
createtableprnttable
(seqnumber(7),linechar(80));
4.异常情况为,部门中奖金不为空值的雇员信息才能存入prnttable表中。
答案:
declare
cursoremp_curis
selectename,sal,comm
fromempwheredeptno=&dno;
emp_recemp_cur%rowtype;
null_commissionexception;
begin
openemp_cur;
fetchemp_curintoemp_rec;
while(emp_cur%found)loop
ifemp_rec.commisnullthen
begin
closeemp_cur;
raisenull_commission;
end;
endif;
fetchemp_curintoemp_rec;
endloop;
closeemp_sur;
exception
whennull_commissionthen
openemp_cur;
fetchemp_curintoemp_rec;
while(emp_cur%found)loop
ifemp_rec.commisnotnullthen
insertintotempvalues(emp_rec.sal,emp_rec.ename);
endif;
fetchemp_curintoemp_rec;
endloop;
closeemp_cur;
commit;
end;
declare
cursoremp_curis
selectename,sal,comm
fromempwheredeptno=&dno;
emp_recemp_cur%rowtype;
null_commissionexception;
begin
openemp_cur;
fetchemp_curintoemp_rec;
while(emp_cur%found)loop
ifemp_rec.commisnullthen
begin
closeemp_cur;
raisenull_commission;
end;
endif;
fetchemp_curintoemp_rec;
endloop;
closeemp_sur;
exception
whennull_commissionthen
openemp_cur;
fetchemp_curintoemp_rec;
while(emp_cur%found)loop
ifemp_rec.commisnotnullthen
insertintotempvalues(emp_rec.sal,emp_rec.ename);
endif;
fetchemp_curintoemp_rec;
endloop;
closeemp_cur;
commit;
end;
Java研究组织-版权所有2002-2002
Java研究组织-版权所有2002-2002
RE:ORACLE数据库对象与用户管理(转)
作者:UB时间:2003-08-14 21:06:59[修改][回复][删除]
作者:UB时间:2003-08-14 21:06:59[修改][回复][删除]
ORACLE数据库对象与用户管理
一、ORACLE数据库的模式对象的管理与维护
本节的主要内容是关于ORACLE数据库的模式对象的管理与维护,这些模式对象包括:表空间、表、视图、索引、序列、同义词、聚集和完整性约束。对于每一个模式对象,首先描述了它的定义,说明了它的功能,最后以基于SQL语言的实例说明如何对它们进行管理于维护。
1.1表空间
由于表空间是包含这些模式对象的逻辑空间,有必要先对它进行维护。
创建表空间
SQL>CREATETABLESPACEjxzy
SQL>CREATETABLESPACEjxzy
>DATAFILE‘/usr/oracle/dbs/jxzy.dbf’
>ONLINE;
修改表空间
SQL>ALTERTABLESPACEjxzyOFFLINENORMAL;
SQL>ALTERTABLESPACEjxzyOFFLINENORMAL;
SQL>ALTERTABLESPACEjxzy
>RENAMEDATAFILE‘/usr/oracle/dbs/jxzy.dbf’
>TO‘/usr/oracle/dbs/jxzynew.dbf’
>ONLINE
SQL>CREATETABLESPACEjxzyONLINE
删除表空间
SQL>DROPTABLESPACEjxzy
SQL>DROPTABLESPACEjxzy
>INCLUDINGCONTENTS
1.2表维护
表是数据库中数据存储的基本单位,一个表包含若干列,每列具有列名、类型、长度等。
表的建立
SQL>CREATETABLEjxzy.switch(
SQL>CREATETABLEjxzy.switch(
>OFFICE_NUMNUMBER(3,0)NOTNULL,
>SWITCH_CODENUMBER(8,0)NOTNULL,
>SWITCH_NAMEVARCHAR2(20)NOTNULL);
表的修改
SQL>ALTERTABLEjxzy.switch
SQL>ALTERTABLEjxzy.switch
>ADD(DESCVARCHAR2(30));
表的删除
SQL>DROPTABLEjxzy.switch
SQL>DROPTABLEjxzy.switch
>CASCADECONSTRAINTS
//删除引用该表的其它表的完整性约束
1.3视图维护
视图是由一个或若干基表产生的数据集合,但视图不占存储空间。建立视图可以保护数据安全(仅让用户查询修改可以看见的一些行列)、简化查询操作、保护数据的独立性。
视图的建立
SQL>CREATEVIEWjxzy.pole_well_viewAS
SQL>CREATEVIEWjxzy.pole_well_viewAS
>(SELECTpole_path_numASpath,
poleASdevice_numFROMpole
>UNION
>SELECTpipe_path_numASpath,
>wellASdevice_numFROMwell);
视图的替换
SQL>REPLACEVIEWjxzy.pole_well_viewAS
SQL>REPLACEVIEWjxzy.pole_well_viewAS
>(SELECTpole_path_numASpath,
poleASsupport_deviceFROMpole
>UNION
>UNION
>SELECTpipe_path_numASpath,
wellASsupport_deviceFROMwell);
视图的删除
SQL>DROPVIEWjxzy.pole_well_view;
视图的删除
SQL>DROPVIEWjxzy.pole_well_view;
1.4序列维护
序列是由序列发生器生成的唯一的整数。
序列的建立
SQL>CREATESEQUENCEjxzy.sequence_cable
SQL>CREATESEQUENCEjxzy.sequence_cable
>STARTWITH1
>INCREMENTBY1
>NO_MAXVALUE;
建立了一个序列,jxzy.sequence_cable.currval返回当前值,jxzy.sequence_cable.nextval返回当前值加1后的新值
序列的修改
SQL>ALTERSEQUENCEjxzy.sequence_cable
SQL>ALTERSEQUENCEjxzy.sequence_cable
>STARTWITH1//起点不能修改,若修改,应先删除,然后重新定义
>INCTEMENTBY2
>MAXVALUE1000;
序列的删除
SQL>DROPSEQUENCEjxzy.sequence_cable
SQL>DROPSEQUENCEjxzy.sequence_cable
1.5索引维护
索引是与表相关的一种结构,它是为了提高数据的检索速度而建立的。因此,为了提高表上的索引速度,可在表上建立一个或多个索引,一个索引可建立在一个或几个列上。
对查询型的表,建立多个索引会大大提高查询速度,对更新型的表,如果索引过多,会增大开销。
索引分唯一索引和非唯一索引
索引的建立
SQL>CREATEINDEXjxzy.idx_switch
SQL>CREATEINDEXjxzy.idx_switch
>ONswitch(switch_name)
>TABLESPACEjxzy;
索引的修改
SQL>ALTERINDEXjxzy.idx_switch
SQL>ALTERINDEXjxzy.idx_switch
>ONswitch(office_num,switch_name)
>TABLESPACEjxzy;
索引的删除
SQL>DROPINDEXjxzy.idx_switch;
SQL>DROPINDEXjxzy.idx_switch;
1.6完整性约束管理
数据库数据的完整性指数据的正确性和相容性。数据完整型检查防止数据库中存在不符合语义的数据。
完整性约束是对表的列定义一组规则说明方法。ORACLE提供如下的完整性约束.
a.NOTNULL非空
b.UNIQUE唯一关键字
c.PRIMATYKEY主键一个表只能有一个,非空
d.FOREIGAKEY外键
e.CHECK表的每一行对指定条件必须是true或未知(对于空值)
例如:
某列定义非空约束
SQL>ALTERTABLEoffice_organization
SQL>ALTERTABLEoffice_organization
>MODIFY(descVARCHAR2(20)
>CONSTRAINTnn_descNOTNULL)
某列定义唯一关键字
SQL>ALTERTABLEoffice_organization
SQL>ALTERTABLEoffice_organization
>MODIFY(office_nameVATCHAR2(20)
>CONSTRAINTuq_officenameUNIQUE)
定义主键约束,主键要求非空
SQL>CREATETABLEswitch(switch_codeNUMBER(8)
SQL>CREATETABLEswitch(switch_codeNUMBER(8)
>CONSTRAINTpk_switchcodePRIMARYKEY,)
使主键约束无效
SQL>ALTERTABLEswitchDISABLEPRIMARYKEY
SQL>ALTERTABLEswitchDISABLEPRIMARYKEY
定义外键
SQL>CREATETABLEPOLE(pole_codeNUMBER(8),
SQL>CREATETABLEPOLE(pole_codeNUMBER(8),
>office_numnumber(3)
>CONSTRAINTfk_officenum
>REFERENCESoffice_organization(office_num)
>ONDELETECASCADE);
定义检查
SQL>CREATETABLEoffice_organization(
SQL>CREATETABLEoffice_organization(
>office_numNUMBER(3),
>CONSTRAINTcheck_officenum
>CHECK(office_numBETWEEN10AND99);
二、ORACLE数据库用户与权限管理
ORACLE是多用户系统,它允许许多用户共享系统资源。为了保证数据库系统的安全,数据库管理系统配置了良好的安全机制。
2.1ORACLE数据库安全策略
建立系统级的安全保证
系统级特权是通过授予用户系统级的权利来实现,系统级的权利(系统特权)包括:建立表空间、建立用户、修改用户的权利、删除用户等。系统特权可授予用户,也可以随时回收。ORACLE系统特权有80多种。
系统级特权是通过授予用户系统级的权利来实现,系统级的权利(系统特权)包括:建立表空间、建立用户、修改用户的权利、删除用户等。系统特权可授予用户,也可以随时回收。ORACLE系统特权有80多种。
建立对象级的安全保证
对象级特权通过授予用户对数据库中特定的表、视图、序列等进行操作(查询、增、删改)的权利来实现。
对象级特权通过授予用户对数据库中特定的表、视图、序列等进行操作(查询、增、删改)的权利来实现。
建立用户级的安全保证
用户级安全保障通过用户口令和角色机制(一组权利)来实现。引入角色机制的目的是简化对用户的授权与管理。做法是把用户按照其功能分组,为每个用户建立角色,然后把角色分配给用户,具有同样角色的用户有相同的特权。
用户级安全保障通过用户口令和角色机制(一组权利)来实现。引入角色机制的目的是简化对用户的授权与管理。做法是把用户按照其功能分组,为每个用户建立角色,然后把角色分配给用户,具有同样角色的用户有相同的特权。
2.2用户管理
ORACLE用户管理的内容主要包括用户的建立、修改和删除
用户的建立
SQL>CREATEUSERjxzy
SQL>CREATEUSERjxzy
>IDENTIFIEDBYjxzy_password
>DEFAULTTABLESPACEsystem
>QUATA5MONsystem;//供用户使用的最大空间限额
用户的修改
SQL>CREATEUSERjxzy
SQL>CREATEUSERjxzy
>IDENTIFIEDBYjxzy_pw
>QUATA10MONsystem;
删除用户及其所建对象
SQL>DROPUSERjxzyCASCADE;//同时删除其建立的实体
SQL>DROPUSERjxzyCASCADE;//同时删除其建立的实体
2.3系统特权管理与控制
ORACLE提供了80多种系统特权,其中每一个系统特权允许用户执行一个或一类数据库操作。
授予系统特权
SQL>GRANTCREATEUSER,ALTERUSER,DROPUSER
SQL>GRANTCREATEUSER,ALTERUSER,DROPUSER
>TOjxzy_new
>WITHADMINOPTION;
回收系统特权
SQL>REVOKECREATEUSER,ALTERUSER,DROPUSER
SQL>REVOKECREATEUSER,ALTERUSER,DROPUSER
>FROMjxzy_new
//但没有级联回收功能
显示已被授予的系统特权(某用户的系统级特权)
SQL>SELECT*FROMsys.dba_sys_privs
SQL>SELECT*FROMsys.dba_sys_privs
2.4对象特权管理与控制
ORACLE对象特权指用户在指定的表上进行特殊操作的权利。这些特殊操作包括增、删、改、查看、执行(存储过程)、引用(其它表字段作为外键)、索引等。
授予对象特权
SQL>GRANTSELECT,INSERT(office_num,office_name),
SQL>GRANTSELECT,INSERT(office_num,office_name),
>UPDATE(desc)ONoffice_organization
>TOnew_adminidtrator
>WITHGRANTOPTION;
//级联授权
SQL>GRANTALLONoffice_organization
>TOnew_administrator
回收对象特权
SQL>REVOKEUPDATEONoffice_orgaization
SQL>REVOKEUPDATEONoffice_orgaization
>FROMnew_administrator
//有级联回收功能
SQL>REVOKEALLONoffice_organization
>FROMnew_administrator
显示已被授予的全部对象特权
SQL>SELECT*FROMsys.dba_tab_privs
SQL>SELECT*FROMsys.dba_tab_privs
2.5角色的管理
ORACLE的角色是命名的相关特权组(包括系统特权与对象特权),ORACLE用它来简化特权管理,可把它授予用户或其它角色。
ORACLE数据库系统预先定义了CONNECT、RESOURCE、DBA、EXP_FULL_DATABASE、IMP_FULL_DATABASE五个角色。CONNECT具有创建表、视图、序列等特权;RESOURCE具有创建过程、触发器、表、序列等特权、DBA具有全部系统特权; EXP_FULL_DATABASE、IMP_FULL_DATABASE具有卸出与装入数据库的特权。
通过查询sys.dba_sys_privs可以了解每种角色拥有的权利。
授予用户角色
SQL>GRANTDBATOnew_administractor
SQL>GRANTDBATOnew_administractor
>WITHGRANTOPTION;
作者:UB时间:2003-08-14 21:06:59[修改][回复][删除]
ORACLE数据库对象与用户管理
一、ORACLE数据库的模式对象的管理与维护
本节的主要内容是关于ORACLE数据库的模式对象的管理与维护,这些模式对象包括:表空间、表、视图、索引、序列、同义词、聚集和完整性约束。对于每一个模式对象,首先描述了它的定义,说明了它的功能,最后以基于SQL语言的实例说明如何对它们进行管理于维护。
1.1表空间
由于表空间是包含这些模式对象的逻辑空间,有必要先对它进行维护。
创建表空间
SQL>CREATETABLESPACEjxzy
SQL>CREATETABLESPACEjxzy
>DATAFILE‘/usr/oracle/dbs/jxzy.dbf’
>ONLINE;
修改表空间
SQL>ALTERTABLESPACEjxzyOFFLINENORMAL;
SQL>ALTERTABLESPACEjxzyOFFLINENORMAL;
SQL>ALTERTABLESPACEjxzy
>RENAMEDATAFILE‘/usr/oracle/dbs/jxzy.dbf’
>TO‘/usr/oracle/dbs/jxzynew.dbf’
>ONLINE
SQL>CREATETABLESPACEjxzyONLINE
删除表空间
SQL>DROPTABLESPACEjxzy
SQL>DROPTABLESPACEjxzy
>INCLUDINGCONTENTS
1.2表维护
表是数据库中数据存储的基本单位,一个表包含若干列,每列具有列名、类型、长度等。
表的建立
SQL>CREATETABLEjxzy.switch(
SQL>CREATETABLEjxzy.switch(
>OFFICE_NUMNUMBER(3,0)NOTNULL,
>SWITCH_CODENUMBER(8,0)NOTNULL,
>SWITCH_NAMEVARCHAR2(20)NOTNULL);
表的修改
SQL>ALTERTABLEjxzy.switch
SQL>ALTERTABLEjxzy.switch
>ADD(DESCVARCHAR2(30));
表的删除
SQL>DROPTABLEjxzy.switch
SQL>DROPTABLEjxzy.switch
>CASCADECONSTRAINTS
//删除引用该表的其它表的完整性约束
1.3视图维护
视图是由一个或若干基表产生的数据集合,但视图不占存储空间。建立视图可以保护数据安全(仅让用户查询修改可以看见的一些行列)、简化查询操作、保护数据的独立性。
视图的建立
SQL>CREATEVIEWjxzy.pole_well_viewAS
SQL>CREATEVIEWjxzy.pole_well_viewAS
>(SELECTpole_path_numASpath,
poleASdevice_numFROMpole
>UNION
>SELECTpipe_path_numASpath,
>wellASdevice_numFROMwell);
视图的替换
SQL>REPLACEVIEWjxzy.pole_well_viewAS
SQL>REPLACEVIEWjxzy.pole_well_viewAS
>(SELECTpole_path_numASpath,
poleASsupport_deviceFROMpole
>UNION
>UNION
>SELECTpipe_path_numASpath,
wellASsupport_deviceFROMwell);
视图的删除
SQL>DROPVIEWjxzy.pole_well_view;
视图的删除
SQL>DROPVIEWjxzy.pole_well_view;
1.4序列维护
序列是由序列发生器生成的唯一的整数。
序列的建立
SQL>CREATESEQUENCEjxzy.sequence_cable
SQL>CREATESEQUENCEjxzy.sequence_cable
>STARTWITH1
>INCREMENTBY1
>NO_MAXVALUE;
建立了一个序列,jxzy.sequence_cable.currval返回当前值,jxzy.sequence_cable.nextval返回当前值加1后的新值
序列的修改
SQL>ALTERSEQUENCEjxzy.sequence_cable
SQL>ALTERSEQUENCEjxzy.sequence_cable
>STARTWITH1//起点不能修改,若修改,应先删除,然后重新定义
>INCTEMENTBY2
>MAXVALUE1000;
序列的删除
SQL>DROPSEQUENCEjxzy.sequence_cable
SQL>DROPSEQUENCEjxzy.sequence_cable
1.5索引维护
索引是与表相关的一种结构,它是为了提高数据的检索速度而建立的。因此,为了提高表上的索引速度,可在表上建立一个或多个索引,一个索引可建立在一个或几个列上。
对查询型的表,建立多个索引会大大提高查询速度,对更新型的表,如果索引过多,会增大开销。
索引分唯一索引和非唯一索引
索引的建立
SQL>CREATEINDEXjxzy.idx_switch
SQL>CREATEINDEXjxzy.idx_switch
>ONswitch(switch_name)
>TABLESPACEjxzy;
索引的修改
SQL>ALTERINDEXjxzy.idx_switch
SQL>ALTERINDEXjxzy.idx_switch
>ONswitch(office_num,switch_name)
>TABLESPACEjxzy;
索引的删除
SQL>DROPINDEXjxzy.idx_switch;
SQL>DROPINDEXjxzy.idx_switch;
1.6完整性约束管理
数据库数据的完整性指数据的正确性和相容性。数据完整型检查防止数据库中存在不符合语义的数据。
完整性约束是对表的列定义一组规则说明方法。ORACLE提供如下的完整性约束.
a.NOTNULL非空
b.UNIQUE唯一关键字
c.PRIMATYKEY主键一个表只能有一个,非空
d.FOREIGAKEY外键
e.CHECK表的每一行对指定条件必须是true或未知(对于空值)
例如:
某列定义非空约束
SQL>ALTERTABLEoffice_organization
SQL>ALTERTABLEoffice_organization
>MODIFY(descVARCHAR2(20)
>CONSTRAINTnn_descNOTNULL)
某列定义唯一关键字
SQL>ALTERTABLEoffice_organization
SQL>ALTERTABLEoffice_organization
>MODIFY(office_nameVATCHAR2(20)
>CONSTRAINTuq_officenameUNIQUE)
定义主键约束,主键要求非空
SQL>CREATETABLEswitch(switch_codeNUMBER(8)
SQL>CREATETABLEswitch(switch_codeNUMBER(8)
>CONSTRAINTpk_switchcodePRIMARYKEY,)
使主键约束无效
SQL>ALTERTABLEswitchDISABLEPRIMARYKEY
SQL>ALTERTABLEswitchDISABLEPRIMARYKEY
定义外键
SQL>CREATETABLEPOLE(pole_codeNUMBER(8),
SQL>CREATETABLEPOLE(pole_codeNUMBER(8),
>office_numnumber(3)
>CONSTRAINTfk_officenum
>REFERENCESoffice_organization(office_num)
>ONDELETECASCADE);
定义检查
SQL>CREATETABLEoffice_organization(
SQL>CREATETABLEoffice_organization(
>office_numNUMBER(3),
>CONSTRAINTcheck_officenum
>CHECK(office_numBETWEEN10AND99);
二、ORACLE数据库用户与权限管理
ORACLE是多用户系统,它允许许多用户共享系统资源。为了保证数据库系统的安全,数据库管理系统配置了良好的安全机制。
2.1ORACLE数据库安全策略
建立系统级的安全保证
系统级特权是通过授予用户系统级的权利来实现,系统级的权利(系统特权)包括:建立表空间、建立用户、修改用户的权利、删除用户等。系统特权可授予用户,也可以随时回收。ORACLE系统特权有80多种。
系统级特权是通过授予用户系统级的权利来实现,系统级的权利(系统特权)包括:建立表空间、建立用户、修改用户的权利、删除用户等。系统特权可授予用户,也可以随时回收。ORACLE系统特权有80多种。
建立对象级的安全保证
对象级特权通过授予用户对数据库中特定的表、视图、序列等进行操作(查询、增、删改)的权利来实现。
对象级特权通过授予用户对数据库中特定的表、视图、序列等进行操作(查询、增、删改)的权利来实现。
建立用户级的安全保证
用户级安全保障通过用户口令和角色机制(一组权利)来实现。引入角色机制的目的是简化对用户的授权与管理。做法是把用户按照其功能分组,为每个用户建立角色,然后把角色分配给用户,具有同样角色的用户有相同的特权。
用户级安全保障通过用户口令和角色机制(一组权利)来实现。引入角色机制的目的是简化对用户的授权与管理。做法是把用户按照其功能分组,为每个用户建立角色,然后把角色分配给用户,具有同样角色的用户有相同的特权。
2.2用户管理
ORACLE用户管理的内容主要包括用户的建立、修改和删除
用户的建立
SQL>CREATEUSERjxzy
SQL>CREATEUSERjxzy
>IDENTIFIEDBYjxzy_password
>DEFAULTTABLESPACEsystem
>QUATA5MONsystem;//供用户使用的最大空间限额
用户的修改
SQL>CREATEUSERjxzy
SQL>CREATEUSERjxzy
>IDENTIFIEDBYjxzy_pw
>QUATA10MONsystem;
删除用户及其所建对象
SQL>DROPUSERjxzyCASCADE;//同时删除其建立的实体
SQL>DROPUSERjxzyCASCADE;//同时删除其建立的实体
2.3系统特权管理与控制
ORACLE提供了80多种系统特权,其中每一个系统特权允许用户执行一个或一类数据库操作。
授予系统特权
SQL>GRANTCREATEUSER,ALTERUSER,DROPUSER
SQL>GRANTCREATEUSER,ALTERUSER,DROPUSER
>TOjxzy_new
>WITHADMINOPTION;
回收系统特权
SQL>REVOKECREATEUSER,ALTERUSER,DROPUSER
SQL>REVOKECREATEUSER,ALTERUSER,DROPUSER
>FROMjxzy_new
//但没有级联回收功能
显示已被授予的系统特权(某用户的系统级特权)
SQL>SELECT*FROMsys.dba_sys_privs
SQL>SELECT*FROMsys.dba_sys_privs
2.4对象特权管理与控制
ORACLE对象特权指用户在指定的表上进行特殊操作的权利。这些特殊操作包括增、删、改、查看、执行(存储过程)、引用(其它表字段作为外键)、索引等。
授予对象特权
SQL>GRANTSELECT,INSERT(office_num,office_name),
SQL>GRANTSELECT,INSERT(office_num,office_name),
>UPDATE(desc)ONoffice_organization
>TOnew_adminidtrator
>WITHGRANTOPTION;
//级联授权
SQL>GRANTALLONoffice_organization
>TOnew_administrator
回收对象特权
SQL>REVOKEUPDATEONoffice_orgaization
SQL>REVOKEUPDATEONoffice_orgaization
>FROMnew_administrator
//有级联回收功能
SQL>REVOKEALLONoffice_organization
>FROMnew_administrator
显示已被授予的全部对象特权
SQL>SELECT*FROMsys.dba_tab_privs
SQL>SELECT*FROMsys.dba_tab_privs
2.5角色的管理
ORACLE的角色是命名的相关特权组(包括系统特权与对象特权),ORACLE用它来简化特权管理,可把它授予用户或其它角色。
ORACLE数据库系统预先定义了CONNECT、RESOURCE、DBA、EXP_FULL_DATABASE、IMP_FULL_DATABASE五个角色。CONNECT具有创建表、视图、序列等特权;RESOURCE具有创建过程、触发器、表、序列等特权、DBA具有全部系统特权; EXP_FULL_DATABASE、IMP_FULL_DATABASE具有卸出与装入数据库的特权。
通过查询sys.dba_sys_privs可以了解每种角色拥有的权利。
授予用户角色
SQL>GRANTDBATOnew_administractor
SQL>GRANTDBATOnew_administractor
>WITHGRANTOPTION;