|
一、SQL PLUS
1 引言
SQL命令
以下17個(gè)是作為語(yǔ)句開(kāi)頭的關(guān)鍵字：
alter drop revoke
audit grant rollback*
commit* insert select
comment lock update
create noaudit validate
delete rename
這些命令必須以“;”結(jié)尾
帶*命令句尾不必加分號(hào)，并且不存入SQL緩存區(qū)。
SQL中沒(méi)有的SQL*PLUS命令
這些命令不存入SQL緩存區(qū)
@ define pause
# del quit
$ describe remark
/ disconnect run
accept document save
append edit set
break exit show
btitle get spool
change help sqlplus
clear host start
column input timing
compute list ttitle
connect newpage undefine
copy
---------
2 數(shù)據(jù)庫(kù)查詢
數(shù)據(jù)字典
TAB 用戶創(chuàng)建的所有基表、視圖和同義詞清單
DTAB 構(gòu)成數(shù)據(jù)字典的所有表
COL 用戶創(chuàng)建的基表的所有列定義的清單
CATALOG 用戶可存取的所有基表清單
select * from tab;
describe命令 描述基表的結(jié)構(gòu)信息
describe dept
select *
from emp;
select empno,ename,job
from emp;
select * from dept
order by deptno desc;
邏輯運(yùn)算符
= !=或<> > >= < <=
in
between value1 and value2
like
%
_
in null
not
no in,is not null
謂詞in和not in
有哪些職員和分析員
select ename,job
from emp
where job in (‘clerk‘,‘a(chǎn)nalyst‘);
select ename,job
from emp
where job not in (‘clerk‘,‘a(chǎn)nalyst‘);
謂詞between和not between
哪些雇員的工資在2000和3000之間
select ename,job,sal from emp
where sal between 2000 and 3000;
select ename,job,sal from emp
where sal not between 2000 and 3000;
謂詞like,not like
select ename,deptno from emp
where ename like ‘S%‘;
(以字母S開(kāi)頭)
select ename,deptno from emp
where ename like ‘%K‘;
(以K結(jié)尾)
select ename,deptno from emp
where ename like ‘W___‘;
(以W開(kāi)頭，后面僅有三個(gè)字母)
select ename,job from emp
where job not like ‘sales%‘;
(哪些雇員的工種名不以sales開(kāi)頭)
謂詞is null,is not null
沒(méi)有獎(jiǎng)金的雇員（即commision為null）
select ename,job from emp
where comm is null;
select ename,job from emp
where comm is not null;
多條件查詢
select ename,job
from emp
where deptno=20
and job!=‘clerk‘;
表達(dá)式
+ - * /
算術(shù)表達(dá)式
選擇獎(jiǎng)金高于其工資的5%的雇員
select ename,sal,comm,comm/sal from emp
where comm>.05*sal
order by comm/sal desc;
日期型數(shù)據(jù)的運(yùn)算
add two days to 6-Mar-87
6-Mar-87 + 2 = 8-Mar-87
add two hours to 6-Mar-87
6-Mar-87 + 2/24 = 6-Mar-87 and 2hrs
add 15 seconds to 6-Mar-87
6-Mar-87 + 15/(24*60*60) = 6-Mar-87 and 15 secs
列名的別名
select ename employee from emp
where deptno=10;
（別名：employee）
select ename,sal,comm,comm/sal "C/S RATIO" from emp
where comm>.05*sal
order by comm/sal desc;
SQL命令的編輯
list or l 顯示緩沖區(qū)的內(nèi)容
list 4 顯示當(dāng)前SQL命令的第4行，并把第4行作為當(dāng)前行，在該行號(hào)后面有個(gè)*。
change or c 用新的內(nèi)容替換原來(lái)在一行中第一次出現(xiàn)內(nèi)容
SQL>c/(...)/(‘a(chǎn)nalyst‘)/
input or i 增加一行或多行
append or a 在一行后追加內(nèi)容
del 刪除當(dāng)前行 刪除SQL緩沖區(qū)中的當(dāng)前行
run 顯示并運(yùn)行SQL緩沖區(qū)中的命令
/ 運(yùn)行SQL緩沖區(qū)中的命令
edit 把SQL緩沖區(qū)中的命令寫到操作系統(tǒng)下的文本文件，
并調(diào)用操作系統(tǒng)提供的編輯器執(zhí)行修改。
-------------
3 數(shù)據(jù)操縱
數(shù)據(jù)的插入
insert into dept
values (10,‘a(chǎn)ccounting‘,‘new york‘);
insert into dept (dname,deptno)
values (‘a(chǎn)ccounting‘,10);
從其它表中選擇插入數(shù)據(jù)
insert into emp (empno,ename,deptno)
select id,name,department
from old_emp
where department in(10,20,30,40);
使用參數(shù)
insert into dept
values(&deptno,&dname,&loc);
執(zhí)行時(shí)，SQL/PLUS對(duì)每個(gè)參數(shù)將有提示用戶輸入
參數(shù)對(duì)應(yīng)日期型或字符型數(shù)據(jù)時(shí)，可在參數(shù)上加引號(hào)，輸入時(shí)就可不用引號(hào)
insert into dept
values(&deptno,‘&dname‘,‘&loc‘);
插入空值（NULL）
insert into dept
values(50,‘education‘,null);
插入日期型數(shù)據(jù)
日期型數(shù)據(jù)缺省格式：DD-MON-YY
insert into emp
(empno,ename,hiredate)
values(7963,‘stone‘,‘07-APR-87‘);
系統(tǒng)時(shí)間：SYSDATE
insert into emp
(empno,ename,hiredate)
values(7600,‘kohn‘,SYSDATE);
數(shù)據(jù)更新
update emp
set job=‘manager‘
where ename=‘martin‘;
update emp
set job=‘market rep‘
where ename=‘salesman‘;
update emp
set deptno=40,job=‘market rep‘
where job=‘salesman‘;
數(shù)據(jù)刪除
delete emp
where empno=765;
更新的提交
commit
自動(dòng)提交方式
set autocommit on
如果狀態(tài)設(shè)為開(kāi)，則使用inesrt,update,delete會(huì)立即提交。
更新取消
rollback
兩次連續(xù)成功的commit之間的操作，稱為一個(gè)事務(wù)
---------------
4 創(chuàng)建基表、視圖
創(chuàng)建基表
create table dept
(deptno number(2),
dname char(14),
loc char(13));
數(shù)據(jù)字典會(huì)自動(dòng)更新。
一個(gè)基表最多254列。
表名列名命名規(guī)則：
限制
第一個(gè)字符必須是字母，后面可任意（包括 $ # _ 但不能是逗號(hào)）。
名字不得超過(guò)30個(gè)字符。
唯一
某一用戶的基表名必須唯一，不能是ORACLE的保留字，同一基表的列名互不相同。
使用雙引號(hào)
如果表名用雙引號(hào)括起來(lái)，則可不滿足上述規(guī)則；
只有使用雙引號(hào)，才能區(qū)別大、小寫；
命名時(shí)使用了雙引號(hào)，在以后的操作也必須使用雙引號(hào)。
數(shù)據(jù)類型：
char(n) （不得超過(guò)240字符）
number(n,d)
date
long （最多65536字符）
raw （二進(jìn)制原始數(shù)據(jù)）
空值處理
有時(shí)要求列值不能為空
create table dept
(deptno number(2) not null,
dname char(14),
loc char(13));
在基表中增加一列
alter table dept
add (headcnt number(3));
修改已有列屬性
alter table dept
modify dname char(20);
注：只有當(dāng)某列所有值都為空時(shí)，才能減小其列值寬度。
只有當(dāng)某列所有值都為空時(shí)，才能改變其列值類型。
只有當(dāng)某列所有值都為不空時(shí)，才能定義該列為not null。
例：
alter table dept modify (loc char(12));
alter table dept modify loc char(12);
alter table dept modify (dname char(13),loc char(12));
創(chuàng)建視圖
create view managers as
select ename,job,sal
from emp
where job=‘manager‘;
為視圖列名取別名
create view mydept
(person,title,salary)
as select ename,job,sal
from emp
where deptno=10;
with check option選項(xiàng)
使用with check option，保證當(dāng)對(duì)視圖插入或更新數(shù)據(jù)時(shí)，
該數(shù)據(jù)必須滿足視圖定義中select命令所指定的條件。
create view dept20 as
select ename,job,sal,deptno
from emp
where deptno=20
with check option;
在做下述操作時(shí)，會(huì)發(fā)生錯(cuò)誤
update dept20
set deptno=30
where ename=‘ward‘;
基表、視圖的拷貝
create table emp2
as select * from emp;
基表、視圖的刪除
drop table 表名
drop view 視圖名
------------
5 SQL*PLUS報(bào)表功能
SQL*PLUS的一些基本格式命令
column deptno heading department
column ename heading name
column sal heading salary
column sal format $99,999.00
ttitle sample report for|hitech corp
btitle strictly confidential
break on deptno
compute sum of sal on deptno
run
表頭和表尾
ttitle sample report for|hitech corp
btitle right strictly confidential
“|”表示換行，結(jié)尾不必加分號(hào)
選項(xiàng)有三種：left right center
使用TTITLE，系統(tǒng)將自動(dòng)地在每頁(yè)的頂部顯示日期和頁(yè)號(hào)。
TTITLET和BTITLE命令有效，直至重新設(shè)置表頭或表尾，或退出SQL*PLUS。
下面命令使標(biāo)題語(yǔ)句失效
TTITLE OFF
BTITLE OFF
列名
column命令定義用于顯示列名
若名字為一個(gè)單詞，不必加引號(hào)
column ename heading employee
column ename heading ‘employee|name‘
（|為換行）
取消欄定義
column ename clear
列的格式
column ename format A15
column sal format $9,999.99
column comm like sal
like子句，使得某一列的格式參照另一列格式，它拷貝列名及其格式
控制記錄顯示分組順序
break on deptno
（不顯示重復(fù)值）
select deptno,ename
from emp
order by deptno;
（ORDER BY子句用于控制BREAK）
顯示為
10 clark
niller
20 smith
scott
30 allen
blake
每次只有一個(gè)BREAK命令起作用，但一次可以在多個(gè)列上使用BREAK命令
break on 列名1 on 列名2
記錄分組
break on deptno skip 2
select deptno,ename
from emp
order by deptno;
每個(gè)deptno之間空兩行
clear break（取消BREAK命令）
break on page（每次從一新頁(yè)開(kāi)始）
break on report（每次從一新報(bào)表開(kāi)始）
break on page on report（聯(lián)合使用）
分組計(jì)算
break on deptno skip 2
compute sum of sal on deptno
計(jì)算每個(gè)部門的工資總和
skip子句使部門之間的信息分隔開(kāi)
其他計(jì)算命令
compute avg of sal on deptno（平均值）
count 非空值的總數(shù)
MAX 最大值
MIN 最小值
STD 標(biāo)準(zhǔn)偏差
VAR 協(xié)方差
NUMBER 行數(shù)
使compute命令失效
一旦定義了COMPUTE，則一直有效，直到
關(guān)閉COMPUTE（clear compute）
SQL/PLUS環(huán)境命令
show 選項(xiàng)
（顯示當(dāng)前參數(shù)設(shè)置情況）
show all（顯示全部參數(shù)）
設(shè)置參數(shù)
set 選項(xiàng) 值或開(kāi)關(guān)
set autocommit on
SET命令包括
set autocommit {off|on|immediate}
（自動(dòng)提交，OFF缺?。?nbsp;
set echo {off|on}
（命令文件執(zhí)行，是否在終端上顯示命令本身，OFF缺?。?nbsp;
set feedback {off|on}
（ON：查詢結(jié)束時(shí)，給出結(jié)果，記錄數(shù)的信息，缺??；
OFF：無(wú)查詢結(jié)果，記錄數(shù)的信息）
set heading {off|on}
（ON：列的頭標(biāo)在報(bào)表上顯示，缺?。籓FF：不在報(bào)表上顯示）
set linesize {n}
一行顯示的最大字符數(shù)，缺省為80
set pagesize {n}
每頁(yè)的行數(shù)，缺省是14
set pause {off|on|text}
（ON：當(dāng)報(bào)表超過(guò)一頁(yè)時(shí)，每顯示一屏就暫停顯示，等待用戶打回車鍵，再接著顯示；
OFF：頁(yè)與頁(yè)不停頓，缺省；text：頁(yè)與頁(yè)停頓，并向用戶提示信息）
SET BUFFER buffer
設(shè)置當(dāng)頭的命令緩沖區(qū)，通常情況下，SQL命令緩沖區(qū)已為當(dāng)前緩沖區(qū)。
由于SQL命令緩沖區(qū)只能存放一條SQL命令，
所以可用其它緩沖區(qū)來(lái)存放SQL命令和SQL*PLUS命令。
經(jīng)常用到的設(shè)置可放在login.sql文件中。
SET NULL
set null ‘no data‘
select ename,comm
from emp
where deptno=30;
把部門30中無(wú)傭金雇員的傭金顯示為“NO DATA”。
set null是SQL*PLUS命令，用它來(lái)標(biāo)識(shí)空值（NULL），可以設(shè)置為任意字符串。
存盤命令SAVE
save 文件名
input
1 select empno,ename,job
2 from emp
3 where job=‘a(chǎn)nalyst‘
save research
目錄中會(huì)增加一個(gè)research.sql文件。
編輯命令EDIT
edit
EDIT編輯當(dāng)前緩沖區(qū)中的內(nèi)容。
編輯一個(gè)文件
edit research
調(diào)入命令GET
get research
把磁盤上的文件內(nèi)容調(diào)入緩沖區(qū)，并顯示在屏幕上，文件名尾不必加.sql。
START命令
運(yùn)行指定的文件
start research
輸出命令SPOOL
spool tryfile
不僅可以使查詢結(jié)果在屏幕上顯示，還可以使結(jié)果存入文件
停止向文件輸出
spool off
把查詢結(jié)果在打印機(jī)上輸出，先把它們存入一個(gè)文件中，
然后不必使用SPOOL OFF，而用：
spool out
SPOOL OUT關(guān)閉該文件并在系統(tǒng)缺省的打印機(jī)上輸出
制作報(bào)表舉例
edit tryfile
set echo off
set autocommit on
set pagesize 25
insert into emp (empno,ename,hiredate)
values(9999,‘geiger‘,sysdate);
insert into emp (empno,ename,deptno)
values(3333,‘samson‘,20);
spool new_emp
select * from emp
where deptno=20
or deptno is null
/
spool off
set autocommit off
用start命令執(zhí)行這個(gè)文件
--------
6 函數(shù)
字符型函數(shù)
initcap(ename);將ename中每個(gè)詞的第一個(gè)字母改為大寫。
如：jack smith--Jack Smith
length(ename);計(jì)算字符串的長(zhǎng)度。
substr(job,1,4);
其它
lower
upper
least 取出字符串列表中按字母排序排在最前面的一個(gè)串
greatest 取出字符串列表中按字母排序排在最后的一個(gè)串
日期函數(shù)
add_month(hiredate,5) 在雇傭時(shí)間上加5個(gè)月
month_between(sysdate,hiredate) 計(jì)算雇傭時(shí)間與系統(tǒng)時(shí)間之間相差的月數(shù)
next_day(hiredate,‘FRIDAY‘) 計(jì)算受雇日期之后的第一個(gè)星期五的日期
例
select ename,sal,next_day(sysdate,‘FRIDAY‘) as_of
from emp
where deptno=20;
（as_of是別名）
如果不用to_char函數(shù)，日期在ORACLE中的缺省格式是‘DD_MON_YY‘
to_char(date,date picture)
select ename,to_char(hiredate,‘Dy Mon dd,yyyy‘) hired
from emp
where deptno=10;
to_date(字符串,格式)
insert into emp(empno,ename,hiredate)
values(7999,‘a(chǎn)sms‘,to_date(‘070387083000‘,‘MMDDYYHHMISS‘));
日期型數(shù)據(jù)的格式
dd 12
dy fri
day friday
ddspth twelfth
mm 03
mon mar
month march
yy 87
yyyy 1987
例
Mar 12,1987 ‘Mon dd,yyyy‘
MAR 12,1987 ‘MON dd,yyyy‘
Thursday MARCH 12 ‘Day MONTH dd‘
Mar 12 11:00am ‘Mon dd hh:miam‘
Thu,the twelfth ‘Dy,"the"ddspth‘
算術(shù)函數(shù)
least(v1,v2)
select ename,empno,mgr,least(empno,mgr) lownum
from emp
where empno0
trunc(sal,0)
取sal的近似值（截?cái)啵?nbsp;
空值函數(shù)
nvl(v1,v2)
v1為列名，如果v1不是空值，nvl返回其列值。
v1為空值，返回v2的值。
聚組函數(shù)
select sum(comm)
from emp;
（返回一個(gè)匯總信息）
不能把sum用在select語(yǔ)句里除非用group by
字符型、日期型、數(shù)字型的聚組函數(shù)
min max count可用于任何數(shù)據(jù)類型
select min(ename)
from emp;
select min(hiredate)
from emp;
select min(sal)
from emp;
有多少人有工作？
select count(job)
from emp;
有多少種不同的工種？
select count(distinct job)
from emp;
count distinct 計(jì)算某一字段中不同的值的個(gè)數(shù)
其它聚組函數(shù)（只用于數(shù)字型數(shù)據(jù)）
avg 計(jì)算平均工資
select avg(sal)
from emp;
stddev 計(jì)算工資的平均差
select stddev(sal)
from emp;
sum 計(jì)算總工資
select sum(sal)
from emp;
group by子句
select deptno,sum(sal),avg(sal)
from emp
group by deptno;
按多個(gè)條件分組
每個(gè)部門的雇員數(shù)
select deptno,count(*)
from emp
group by deptno;
每個(gè)部門的每個(gè)工種的雇員數(shù)
select deptno,job,count(*)
from emp
group by deptno,job;
滿足條件的分組
（where是針對(duì)select的，having是針對(duì)group by的）
哪些部門的工資總和超過(guò)了9000
select deptno,sum(sal)
from emp
group by deptno
having sum(sal)>9000;
select小結(jié)
除去職員，哪些部門的工資總和超過(guò)了8000
select deptno,sum(sal)
from emp
where job!=‘clerk‘
group by deptno
having sum(sal)>8000
order by sum(sal);
---------
7 高級(jí)查詢
等值聯(lián)接
select empno,ename,job,emp.deptno,dname
from emp,dept
where emp.deptno=dept.deptno;
外聯(lián)接
select ename,dept.deptno,loc
from emp,dept
where emp.deptno(+)=dept.deptno;
如果在dept.deptno中有的數(shù)值，在emp.deptno中沒(méi)有（如deptno=40），
則作外聯(lián)接時(shí)，結(jié)果中會(huì)產(chǎn)生一個(gè)空值
自聯(lián)接：同一基表的不同行要做聯(lián)接，可使用自聯(lián)接
指出每個(gè)雇員的經(jīng)理名字
select worker.ename,manager.ename manager
from emp worker,emp manager
where worker.mgr=manager.empno;
非等值聯(lián)接
哪些雇員的工資屬于第三級(jí)別
select ename,sal
from emp,salgrade
where grade=3
and sal between losal and hisal;
（基表salgrade：grade losal hisal）
集合運(yùn)算
行的連接
集合運(yùn)算把2個(gè)或多個(gè)查詢結(jié)果合并為一個(gè)
union-set union
Rows of first query plus of second query, less duplicate rows
intersect-set intersection
Rows both queries have in common
minus-set difference
rows unique to the first query
介紹幾個(gè)視圖
account view
ename sal job
sales view
ename sal job
research view
ename sal job
union運(yùn)算
返回一個(gè)查詢結(jié)果中有但又不重要的行，它將基表或視圖中的記錄合并在一起
所有部門中有哪些雇員工資超過(guò)2000
對(duì)應(yīng)列的數(shù)據(jù)類型必須相同
select ename,sal
from account
where sal>2000
union
select ename,sal
from research
where sal>2000
union
select ename,sal
from sales
where sal>2000;
intersect運(yùn)算
返回查詢結(jié)果中相同的部分
各個(gè)部門中有哪些相同的工種
select job
from account
intersect
select job
from research
intersect
select job
from sales;
minus運(yùn)算
返回在第一個(gè)查詢結(jié)果中與第二個(gè)查詢結(jié)果不相同的那部分行記錄。
有哪些工種在財(cái)會(huì)部中有，而在銷售部中沒(méi)有？
select job from account
minus
select job from sales;
子查詢
slect ename,deptno
from emp
where deptno=
(select deptno
from emp
where ename=‘smith‘);
多級(jí)子查詢
select ename,job,sal
from emp
where job=
(select job
from emp
where ename=‘clark‘)
or sal>
(select sal
from emp
where ename=‘clark‘);
多個(gè)基表與子查詢
select ename,job,sal
from emp,dept
where loc=‘new york‘
and emp.deptno=dept.deptno
and sal>
(select sal
from emp
where ename=‘scott‘);
子查詢中使用聚組函數(shù)
select ename,hiredate
from emp
where hiredate=
(select min(hiredate)
from emp);
------------
8 授權(quán)
系統(tǒng)權(quán)限
DBA 所有權(quán)限
RESOURCE 注冊(cè)，創(chuàng)建新的基表
CONNECT，注冊(cè)，查詢
只有DBA才有權(quán)創(chuàng)建新的用戶
grant connect to scott
identified by tiger;
DBA或用戶自己可以改變用戶口令
grant connect to scott
identified by leopard;
基表權(quán)限1
有兩種方法獲得對(duì)基表操作的權(quán)限
創(chuàng)建自己的基表
獲得基表創(chuàng)建用戶的許可
grant select,insert
on emp
to scott;
這些權(quán)限有
select insert update delete alter index
把所有權(quán)限授于他人
grant all on emp to scott;
同義詞
select *
from scott.emp
創(chuàng)建同義詞
為用戶allen的EMP基表創(chuàng)建同義詞employee
create synonym employee
for allen.emp
基表權(quán)限2
你可以使其他用戶有這樣的權(quán)力，即其他用戶可以把你的基表權(quán)限授予他人
grant all
on emp
to scott
with grant option;
收回權(quán)限
系統(tǒng)權(quán)限 只有被DBA收回
基表權(quán)限 隨時(shí)都可以收回
revoke insert
on emp
from scott;
---------
9 索引
建立索引
create index emp_ename
on emp(ename);
刪除索引
drop index emp_ename;
關(guān)于索引
只對(duì)較大的基表建立索引（至少50條記錄）
建立索引之前插入數(shù)據(jù)
對(duì)一個(gè)基表可建立任意多個(gè)索引
一般是在作為主鍵的列上建立索引
建立索引之后，不影響SQL命令的執(zhí)行
建立索引之后，ORACLE自動(dòng)維護(hù)和使用索引
保證數(shù)據(jù)唯一性
提高執(zhí)行速度的同時(shí)，索引還可以保證每個(gè)記錄中的每個(gè)列值是不重復(fù)的。
create unique index emp_empno
on emp(empno);
--------
練習(xí)和答案
有沒(méi)有工資比獎(jiǎng)金多的雇員？如果有，按工資的降序排列。
如果有兩個(gè)以上的雇員工資相同，按他們的名字排序。
select ename employee,sal salary,comm commision
from emp
where sal>comm
order by sal desc,ename;
列出有關(guān)雇員姓名、獎(jiǎng)金占收百分比的信息。
要求顯示時(shí)列名意義明確，按雇員姓名排序，不包括獎(jiǎng)金未知的雇員。
select ename employee,(comm/(comm+sal))*100 incentive
from emp
where comm is not null
order by ename;
在chicago（部門30）工作的所有雇員的工資上漲10%。
update emp
set sal=1.1*sal
where deptno=30;
update emp
set sal=1.1*sal
where deptno=(select deptno
from dept
where loc=‘chicago‘);
為hitech公司新建一個(gè)部門，編號(hào)為50，其它信息均不可知。
insert into dept(dname,deptno)
values(‘faclities‘,50);
創(chuàng)建視圖，三個(gè)列名，其中不包括職員信息
create view employee("employee name",
"employee number",
"employee job")
as select ename,empno,job
from emp
where job!=‘clerk‘;
制作工資報(bào)表，包括雇員姓名、受雇時(shí)間（按星期計(jì)算），工資和部門編號(hào)，
一頁(yè)顯示一個(gè)部門的信息，每頁(yè)尾，顯示該頁(yè)的工資之和以及受雇時(shí)間之和，
報(bào)表結(jié)尾處，顯示所有雇員的工資總和以及受雇時(shí)間總和，
工資按美元計(jì)算，受雇時(shí)間按星期計(jì)算，每頁(yè)的上方應(yīng)有標(biāo)題。
ttitle ‘service‘
break on deptno on page on report
compute sum of sal on deptno
compute sum of sal on report
compute sum of service_length on deptno
compute sum of service_length on report
column sal format $99,999.00
column service_length format 9999
select deptno,ename employee,(sysdate-hiredate)/7 service_length,sal
from emp
order by deptno;
制作報(bào)表，包括雇員姓名、總收入和受傭日期，
且：姓名的第一個(gè)字母必須大寫，雇傭日期格式為MM/DD/YYYY，
總收入包括沒(méi)有獎(jiǎng)金的雇員的總收入，姓名按字母順序排列。
col "hire date"format A12
col "employee" format A10
col "compensation" format $99,999.00
select initcap(ename) "employee",
(sal+nvl(comm,0)) "compensation",
to_char(hiredate,‘MM/DD/YYYY‘) "hire date"
from emp
order by ename;
列出有超過(guò)7個(gè)周邊國(guó)家的國(guó)家名字和面積。
select nation,area
from nation
where code in
(select nation_code
from border
group by nation_code
having count(*)>7);
列出所有面積大于等于日本的島國(guó)的國(guó)名和人口。
select nation,population
from nation,border
where code=nation_code(+)
and nation_code is null
and area>=
(select area
from nation
where upper(nation)=‘JAPAN‘);
列出所有邊界在其它國(guó)家中的國(guó)名，并且顯示其邊界國(guó)家名字。
break on nation
select nation1.nation,
nation2.nation borderin_country
from nation nation1,border,nation nation2
where nation1.code=border.nation_code
and border.border_code=nation2.code
order by nation1.nation;
-----------
-----------
PL/SQL
2 PL/SQL的塊結(jié)構(gòu)和數(shù)據(jù)類型
塊結(jié)構(gòu)的特點(diǎn)
嵌套
begin
......
begin
......
exception
......
end;
exception
......
end;
標(biāo)識(shí)符：
不能超過(guò)30個(gè)字符
第一個(gè)字符必須為字母
其余字符可以是字母，數(shù)字，$，_，或#
不區(qū)分大小寫形式
如果用雙引號(hào)括起來(lái)，則字符順序可以任意排列，并區(qū)分大小寫形式
無(wú)SQL保留字
數(shù)據(jù)類型
數(shù)字型：
整數(shù)，實(shí)數(shù)，以及指數(shù)
字符串：
用單引號(hào)括起來(lái)
若在字符串表示單引號(hào)，則使用兩個(gè)單引號(hào)
字符串長(zhǎng)度為零（兩個(gè)單引號(hào)之間沒(méi)有字符），則表示NULL
字符：
長(zhǎng)度為1的字符串
數(shù)據(jù)定義
語(yǔ)法
標(biāo)識(shí)符[常數(shù)] 數(shù)據(jù)類型[NOT NULL][:=PL/SQL表達(dá)式];
‘:=‘表示給變量賦值
數(shù)據(jù)類型包括
數(shù)字型 number(7,2)
字符型 char(120)
日期型 date
布爾型 boolean（取值為true,false或null，不存貯在數(shù)據(jù)庫(kù)中）
日期型
anniversary date:=‘05-JUL-95‘;
project_completion date;
布爾型
over_budget boolean not null:=false;
available boolean;
（初始值為NULL）
%type類型匹配
books_printed number(6);
books_sold book_printed%type;
manager_name emp.ename%type;
變量賦值
變量名:=PL/SQL表達(dá)式
numvar:=5;
boolvar:=true;
datevar:=‘11-JUN-87‘;
字符型、數(shù)字型表達(dá)式中的空值
null+<數(shù)字>=null（空值加數(shù)字仍是空值）
null><數(shù)字>=null（空值與數(shù)字進(jìn)行比較，結(jié)果仍是空值）
null||‘字符串‘=‘字符串‘（null即‘‘）
（空值與字符串進(jìn)行連接運(yùn)算，結(jié)果為原字符串）
變量作用范圍
標(biāo)識(shí)符在宣言它的塊中有效
標(biāo)識(shí)符如果不在子塊中重新定義，則在PL/SQL塊的所有子塊中同樣有效
重新定義后的標(biāo)識(shí)符，作用范圍僅在本子塊中有效
例
declare
e_mess char(80);
begin
/*子塊1*/
declare
v1 number(4);
begin
select empno into v1 from emp
where job=‘president‘;
exception
when too_many_rows then
insert into job_errors
values(‘more than one president‘);
end;
/*子塊2*/
declare
v1 number(4);
begin
select empno into v1 from emp
where job=‘manager‘;
exception
when too_many_rows then
insert into job_errors
values(‘more than one manager‘);
end;
exception
when others then
e_mess:=substr(sqlerrm,1,80);
insert into general errors values(e_mess);
end;
---------
3 SQL和PL/SQL
插入
declare
my_sal number(7,2):=3040.55;
my_ename char(25):=‘wanda‘;
my_hiredate date:=‘08-SEP-88‘;
begin
insert into emp
(empno,enmae,job,hiredate,sal,deptno)
values(2741,my_ename,‘cab driver‘,my_hiredate,my_sal,20);
end;
刪除
declare
bad_child_type char(20):=‘naughty‘;
begin
delete from santas_gift_list where
kid_rating=bad_child_type;
end;
事務(wù)處理
commit[WORK];
rollback[WORK];
（關(guān)鍵字WORK可選，但對(duì)命令執(zhí)行無(wú)任何影響）
savepoint 標(biāo)記名;（保存當(dāng)前點(diǎn)）
在事務(wù)中標(biāo)記當(dāng)前點(diǎn)
rollback [WORK] to [SAVEPOINT] 標(biāo)記名;（回退到當(dāng)前保存點(diǎn)）
取消savepoint命令之后的所有對(duì)數(shù)據(jù)庫(kù)的修改
關(guān)鍵字WORK和SAVEPOINT為可選項(xiàng)，對(duì)命令執(zhí)行無(wú)任何影響
函數(shù)
PL/SQL塊中可以使用SQL命令的所有函數(shù)
insert into phonebook(lastname) value(upper(my_lastname));
select avg(sal) into avg_sal from emp;
對(duì)于非SQL命令，可使用大多數(shù)個(gè)體函數(shù)
不能使用聚組函數(shù)和參數(shù)個(gè)數(shù)不定的函數(shù)，如
x:=sqrt(y);
lastname:=upper(lastname);
age_diff:=months_between(birthday1,birthday2)/12;
賦值時(shí)的數(shù)據(jù)類型轉(zhuǎn)換
4種賦值形式:
變量名:=表達(dá)式
insert into 基表名 values(表達(dá)式1,表達(dá)式2,...);
update 基表名 set 列名=表達(dá)式;
select 列名 into 變量名 from ...;
數(shù)據(jù)類型間能進(jìn)行轉(zhuǎn)換的有:
char轉(zhuǎn)成number
number轉(zhuǎn)成char
char轉(zhuǎn)成date
date轉(zhuǎn)成char
例
char_var:=nm_var;
數(shù)字型轉(zhuǎn)換成字符型
date_var:=‘25-DEC-88‘;
字符型轉(zhuǎn)換成日期型
insert into 表名(num_col) values(‘604badnumber‘);
錯(cuò)誤，無(wú)法成功地轉(zhuǎn)換數(shù)據(jù)類型
---------
4 條件控制
例
declare
num_jobs number(4);
begin
select count(*) into num_jobs from auditions
where actorid=&&actor_id and called_back=‘yes‘;
if num_jobs>100 then
update actor set actor_rating=‘word class‘
where actorid=&&actor_id;
elsif num_job=75 then
update actor set actor_rating=‘daytime soaps‘
where actorid=&&actor_id;
else
update actor set actor_rating=‘waiter‘
where actorid=&&actor_id;
end if;
end if;
commit;
end;
--------
5 循環(huán)
語(yǔ)法
loop
......
end loop;
exit;（退出循環(huán)）
exit [when];（退出循環(huán)，當(dāng)滿足WHEN時(shí)）
例1
declare
ctr number(3):=0;
begin
loop
insert into table1 values(‘tastes great‘);
insert into table2 values(‘less filling‘);
ctr:=ctr+1;
exit when ctr=100;
end loop;
end;
（注：如果ctr取為NULL，循環(huán)無(wú)法結(jié)束）
例2
FOR語(yǔ)法
for 變量<范圍> loop
......
end loop;
declare
my_index char(20):=‘fettucini alfredo‘;
bowl char(20);
begin
for my_index in reverse 21..30 loop
insert into temp(coll) values(my_index);
/*循環(huán)次數(shù)從30到21*/
end loop;
bowl:=my_index;
end;
跟在in reverse后面的數(shù)字必須是從小到大的順序，必須是整數(shù)，不能是變量或表達(dá)式
----------
6 游標(biāo)
顯式游標(biāo)
打開(kāi)游標(biāo)
open <游標(biāo)名>
例
open color_cur;
游標(biāo)屬性
%notfound
%found
%rowcount
%isopen
例
fetch my_cur into my_var;
while my_cur %found loop
(處理數(shù)據(jù))
fetch my_cur into my_var;
exit when my_cur %rowcount=10;
end loop;
%notfound屬性
取值情況如下：
fetch操作沒(méi)有返回記錄，則取值為true
fetch操作返回一條記錄，則取值為false
對(duì)游標(biāo)無(wú)fetch操作時(shí)，取值為null
<游標(biāo)名> %notfound
例
if color_cur %notfound then...
注：如果沒(méi)有fetch操作，則<游標(biāo)名> %notfound將導(dǎo)致出錯(cuò)，
因?yàn)?notfound的初始值為NULL。
關(guān)閉游標(biāo)
close <游標(biāo)名>
例
close color_cur;
游標(biāo)的FOR循環(huán)
語(yǔ)法
for <記錄名> in <游標(biāo)名> loop
<一組命令>
end loop;
其中：
索引是建立在每條記錄的值之上的
記錄名不必聲明
每個(gè)值對(duì)應(yīng)的是記錄名，列名
初始化游標(biāo)指打開(kāi)游標(biāo)
活動(dòng)集合中的記錄自動(dòng)完成FETCH操作
退出循環(huán)，關(guān)閉游標(biāo)
隱式游標(biāo)
隱式游標(biāo)是指SQL命令中用到的，沒(méi)有明確定義的游標(biāo)
insert,update,delete,select語(yǔ)句中不必明確定義游標(biāo)
調(diào)用格式為SQL%
存貯有關(guān)最新一條SQL命令的處理信息
隱式游標(biāo)的屬性
隱式游標(biāo)有四個(gè)屬性
SQL%NOTFOUND
SQL%FOUND
SQL%ROWCOUNT：隱式游標(biāo)包括的記錄數(shù)
例：
delete from baseball_team where batting_avg<100;
if sql%rowcount>5 thn
insert into temp
values(‘your team needs help‘);
end if;
SQL%ISOPEN：取值總為FALSE。SQL命令執(zhí)行完畢，PL/SQL立即關(guān)閉隱式游標(biāo)。
---------
7 標(biāo)號(hào)
GOTO語(yǔ)句
用法：
goto you_are_here;
其中you_are_here是要跳轉(zhuǎn)的語(yǔ)句標(biāo)號(hào)
標(biāo)號(hào)必須在同一組命令，或是同一塊中使用
正確的使用
<>（標(biāo)號(hào)）
x:=x+1
if a>b then
b:=b+c;
goto dinner;
end if;
錯(cuò)誤的使用
goto jail;
if a>b then
b:=b+c;
<>（標(biāo)號(hào)）
x:=x+1;
end if;
標(biāo)號(hào)：解決意義模糊
標(biāo)號(hào)可用于定義列值的變量
<>
declare
deptno number:=20;
begin
update emp set sal=sal*1.1
where deptno=sample.deptno;
commit;
end sample;
如果不用標(biāo)號(hào)和標(biāo)號(hào)限制符，這條命令將修改每條記錄。
----------
8 異常處理
預(yù)定義的異常情況
任何ORACLE錯(cuò)誤都將自動(dòng)產(chǎn)生一個(gè)異常信息
一些異常情況已命名，如：
no_data_found 當(dāng)SELECT語(yǔ)句無(wú)返回記錄時(shí)產(chǎn)生
too_many_rows 沒(méi)有定義游標(biāo)，而SELECT語(yǔ)句返回多條記錄時(shí)產(chǎn)生
whenever notfound 無(wú)對(duì)應(yīng)的記錄
用戶定義的異常情況
由用戶自己獲取
在DECLARE部分定義：
declare
x number;
something_isnt_right exception;
用戶定義的異常情況遵循一般的作用范圍規(guī)則
條件滿足時(shí)，獲取異常情況：raise something_isnt_right
注意：同樣可以獲取預(yù)定義的異常情況
exception_init語(yǔ)句
允許為ORACLE錯(cuò)誤命名
調(diào)用格式：
pragma exception_init(<表達(dá)式>,);
例
declare
deadlock_detected exception;
pragma exception_init(deadlock_detected,-60);
raise語(yǔ)句
單獨(dú)使用RAISE命令，可再一次獲取當(dāng)前的異常情況（就象異常情況被重復(fù)處理了一樣）。
在異常處理中，此語(yǔ)句只能單獨(dú)使用。
異常處理標(biāo)識(shí)符
一組用于處理異常情況的語(yǔ)句：
exception
when <表達(dá)式> or [表達(dá)式...] then
<一組語(yǔ)句>
...
when others then--最后一個(gè)處理
<一組語(yǔ)句>
end;既結(jié)束PL/SQL塊部分，也結(jié)束異常處理部分
--------
練習(xí)與答案
1:
接收contract_no和item_no值，在inventory表中查找，如果產(chǎn)品：
已發(fā)貨，在arrival_date中賦值為今天后的7天
已訂貨，在arrival_date中賦值為今天后的一個(gè)月
既無(wú)訂貨又無(wú)發(fā)貨，則在arrival_date中賦值為今天后的兩個(gè)月，
并在order表中增加一條新的訂單記錄。
product_status的列值為‘shipped‘和‘ordered‘
inventory:
product_id number(6)
product_description char(30)
product_status char(20)
std_shipping_qty number(3)
contract_item:
contract_no number(12)
item_no number(6)
arrival_date date
order:
order_id number(6)
product_id number(6)
qty number(3)
答案：
declare
i_product_id inventory.product_id%type;
i_product_description inventory.product_description%type;
i_product_status inventory.product_status%type;
i_std_shipping_qty inventory.std_shipping_qty%type;
begin
select product_id,product_description,product_status,std_shipping_qty
into i_product_id,i_product_description,
i_product_status,i_std_shipping_qty
from inventory
where product_id=(
select product_id
from contract_item
where contract_no=&&contractno and item_no=&&itemno);
if i_product_status=‘shipped‘ then
update contract_item
set arrival_date=sysdate+7
where item_no=&&itemno and contract_no=&&contractno;
elsif i_product_status=‘ordered‘ then
update contract_item
set arrival_date=add_months(sysdate,1)
where item_no=&&itemno and contract_no=&&contractno;
else
update contract_item
set arrival_date=add_months(sysdate,2)
where item_no=&&itemno and contract_no=&&contractno;
insert into orders
values(100,i_product_id,i_std_shipping_qty);
end if;
end if;
commit;
end;
2:
1.找出指定部門中的所有雇員
2.用帶‘&‘的變量提示用戶輸入部門編號(hào)
3.把雇員姓名及工資存入prnttable表中，基結(jié)構(gòu)為：
create table prnttable
(seq number(7),line char(80));
4.異常情況為，部門中獎(jiǎng)金不為空值的雇員信息才能存入prnttable表中。
答案：
declare
cursor emp_cur is
select ename,sal,comm
from emp where deptno=&dno;
emp_rec emp_cur%rowtype;
null_commission exception;
begin
open emp_cur;
fetch emp_cur into emp_rec;
while (emp_cur%found) loop
if emp_rec.comm is null then
begin
close emp_cur;
raise null_commission;
end;
end if;
fetch emp_cur into emp_rec;
end loop;
close emp_sur;
exception
when null_commission then
open emp_cur;
fetch emp_cur into emp_rec;
while (emp_cur%found) loop
if emp_rec.comm is not null then
insert into temp values(emp_rec.sal,emp_rec.ename);
end if;
fetch emp_cur into emp_rec;
end loop;
close emp_cur;
commit;
end; |
