亚洲.国产.欧美一区二区三区,亚洲日韩aⅴ在线视频,亚洲精品久久无码

SQL大全

一、SQLPLUS
1引言

SQL命令
以下17個是作為語句開頭的關鍵字：
alterdroprevoke
auditgrantrollback*
commit*insertselect
commentlockupdate
createnoauditvalidate
deleterename
這些命令必須以“;”結尾
帶*命令句尾不必加分號，并且不存入SQL緩存區。

SQL中沒有的SQL*PLUS命令
這些命令不存入SQL緩存區
@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數據庫查詢

數據字典
TAB用戶創建的所有基表、視圖和同義詞清單

DTAB構成數據字典的所有表

COL用戶創建的基表的所有列定義的清單

CATALOG用戶可存取的所有基表清單

select*fromtab;

describe命令描述基表的結構信息
describedept

select*
fromemp;

selectempno,ename,job
fromemp;

select*fromdept
orderbydeptnodesc;

邏輯運算符
=!=或<>>>=<<=
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','analyst');

selectename,job
fromemp
wherejobnotin('clerk','analyst');

謂詞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開頭)
selectename,deptnofromemp
whereenamelike'%K';
(以K結尾)
selectename,deptnofromemp
whereenamelike'W___';
(以W開頭，后面僅有三個字母)
selectename,jobfromemp
wherejobnotlike'sales%';
(哪些雇員的工種名不以sales開頭)

謂詞isnull,isnotnull
沒有獎金的雇員（即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';

表達式
+-*/

算術表達式
選擇獎金高于其工資的5%的雇員
selectename,sal,comm,comm/salfromemp
wherecomm>.05*sal
orderbycomm/saldesc;

日期型數據的運算
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顯示緩沖區的內容
list4顯示當前SQL命令的第4行，并把第4行作為當前行，在該行號后面有個*。
changeorc用新的內容替換原來在一行中第一次出現內容
SQL>c/(...)/('analyst')/
inputori增加一行或多行
appendora在一行后追加內容
del刪除當前行刪除SQL緩沖區中的當前行
run顯示并運行SQL緩沖區中的命令
/運行SQL緩沖區中的命令
edit把SQL緩沖區中的命令寫到操作系統下的文本文件，
并調用操作系統提供的編輯器執行修改。

-------------
3數據操縱
數據的插入
insertintodept
values(10,'accounting','newyork');

insertintodept(dname,deptno)
values('accounting',10);

從其它表中選擇插入數據
insertintoemp(empno,ename,deptno)
selectid,name,department
fromold_emp
wheredepartmentin(10,20,30,40);

使用參數
insertintodept
values(&deptno,&dname,&loc);
執行時，SQL/PLUS對每個參數將有提示用戶輸入

參數對應日期型或字符型數據時，可在參數上加引號，輸入時就可不用引號
insertintodept
values(&deptno,'&dname','&loc');

插入空值（NULL）
insertintodept
values(50,'education',null);

插入日期型數據
日期型數據缺省格式：DD-MON-YY
insertintoemp
(empno,ename,hiredate)
values(7963,'stone','07-APR-87');

系統時間：SYSDATE
insertintoemp
(empno,ename,hiredate)
values(7600,'kohn',SYSDATE);

數據更新
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';

數據刪除
deleteemp
whereempno=765;

更新的提交
commit

自動提交方式
setautocommiton
如果狀態設為開，則使用inesrt,update,delete會立即提交。

更新取消
rollback

兩次連續成功的commit之間的操作，稱為一個事務

---------------
4創建基表、視圖
創建基表
createtabledept
(deptnonumber(2),
dnamechar(14),
locchar(13));

數據字典會自動更新。
一個基表最多254列。

表名列名命名規則：
限制
第一個字符必須是字母，后面可任意（包括$#_但不能是逗號）。
名字不得超過30個字符。

唯一
某一用戶的基表名必須唯一，不能是ORACLE的保留字，同一基表的列名互不相同。

使用雙引號
如果表名用雙引號括起來，則可不滿足上述規則；
只有使用雙引號，才能區別大、小寫；
命名時使用了雙引號，在以后的操作也必須使用雙引號。

數據類型：
char(n)（不得超過240字符）
number(n,d)
date
long（最多65536字符）
raw（二進制原始數據）

空值處理
有時要求列值不能為空
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);
注：只有當某列所有值都為空時，才能減小其列值寬度。
只有當某列所有值都為空時，才能改變其列值類型。
只有當某列所有值都為不空時，才能定義該列為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));

創建視圖
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選項
使用withcheckoption，保證當對視圖插入或更新數據時，
該數據必須滿足視圖定義中select命令所指定的條件。
createviewdept20as
selectename,job,sal,deptno
fromemp
wheredeptno=20
withcheckoption;
在做下述操作時，會發生錯誤
updatedept20
setdeptno=30
whereename='ward';

基表、視圖的拷貝
createtableemp2
asselect*fromemp;

基表、視圖的刪除
droptable表名
dropview視圖名

------------
5SQL*PLUS報表功能
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

“｜”表示換行，結尾不必加分號
選項有三種：leftrightcenter

使用TTITLE，系統將自動地在每頁的頂部顯示日期和頁號。
TTITLET和BTITLE命令有效，直至重新設置表頭或表尾，或退出SQL*PLUS。

下面命令使標題語句失效
TTITLEOFF
BTITLEOFF

列名
column命令定義用于顯示列名
若名字為一個單詞，不必加引號
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
（不顯示重復值）

selectdeptno,ename
fromemp
orderbydeptno;
（ORDERBY子句用于控制BREAK）

顯示為
10clark
niller
20smith
scott
30allen
blake

每次只有一個BREAK命令起作用，但一次可以在多個列上使用BREAK命令
breakon列名1on列名2

記錄分組
breakondeptnoskip2
selectdeptno,ename
fromemp
orderbydeptno;

每個deptno之間空兩行
clearbreak（取消BREAK命令）
breakonpage（每次從一新頁開始）
breakonreport（每次從一新報表開始）
breakonpageonreport（聯合使用）

分組計算
breakondeptnoskip2
computesumofsalondeptno
計算每個部門的工資總和
skip子句使部門之間的信息分隔開

其他計算命令
computeavgofsalondeptno（平均值）
count非空值的總數
MAX最大值
MIN最小值
STD標準偏差
VAR協方差
NUMBER行數

使compute命令失效
一旦定義了COMPUTE，則一直有效，直到
關閉COMPUTE（clearcompute）

SQL/PLUS環境命令
show選項
（顯示當前參數設置情況）

showall（顯示全部參數）

設置參數
set選項值或開關

setautocommiton

SET命令包括
setautocommit{off｜on｜immediate}
（自動提交，OFF缺省）

setecho{off｜on}
（命令文件執行，是否在終端上顯示命令本身，OFF缺省）

setfeedback{off｜on}
（ON：查詢結束時，給出結果，記錄數的信息，缺省；
OFF：無查詢結果，記錄數的信息）

setheading{off｜on}
（ON：列的頭標在報表上顯示，缺省；OFF：不在報表上顯示）

setlinesize{n}
一行顯示的最大字符數，缺省為80

setpagesize{n}
每頁的行數，缺省是14

setpause{off｜on｜text}
（ON：當報表超過一頁時，每顯示一屏就暫停顯示，等待用戶打回車鍵，再接著顯示；
OFF：頁與頁不停頓，缺省；text：頁與頁停頓，并向用戶提示信息）

SETBUFFERbuffer
設置當頭的命令緩沖區，通常情況下，SQL命令緩沖區已為當前緩沖區。
由于SQL命令緩沖區只能存放一條SQL命令，
所以可用其它緩沖區來存放SQL命令和SQL*PLUS命令。

經常用到的設置可放在login.sql文件中。

SETNULL
setnull'nodata'

selectename,comm
fromemp
wheredeptno=30;
把部門30中無傭金雇員的傭金顯示為“NODATA”。

setnull是SQL*PLUS命令，用它來標識空值（NULL），可以設置為任意字符串。

存盤命令SAVE
save文件名

input
1selectempno,ename,job
2fromemp
3wherejob='analyst'

saveresearch

目錄中會增加一個research.sql文件。

編輯命令EDIT
edit

EDIT編輯當前緩沖區中的內容。

編輯一個文件
editresearch

調入命令GET
getresearch
把磁盤上的文件內容調入緩沖區，并顯示在屏幕上，文件名尾不必加.sql。

START命令
運行指定的文件
startresearch

輸出命令SPOOL
spooltryfile
不僅可以使查詢結果在屏幕上顯示，還可以使結果存入文件

停止向文件輸出
spooloff

把查詢結果在打印機上輸出，先把它們存入一個文件中，
然后不必使用SPOOLOFF，而用：
spoolout
SPOOLOUT關閉該文件并在系統缺省的打印機上輸出

制作報表舉例
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命令執行這個文件

--------
6函數
字符型函數
initcap(ename);將ename中每個詞的第一個字母改為大寫。
如：jacksmith--JackSmith

length(ename);計算字符串的長度。

substr(job,1,4);

其它
lower
upper
least取出字符串列表中按字母排序排在最前面的一個串
greatest取出字符串列表中按字母排序排在最后的一個串

日期函數
add_month(hiredate,5)在雇傭時間上加5個月
month_between(sysdate,hiredate)計算雇傭時間與系統時間之間相差的月數
next_day(hiredate,'FRIDAY')計算受雇日期之后的第一個星期五的日期

例
selectename,sal,next_day(sysdate,'FRIDAY')as_of
fromemp
wheredeptno=20;
（as_of是別名）

如果不用to_char函數，日期在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,'asms',to_date('070387083000','MMDDYYHHMISS'));

日期型數據的格式
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'

算術函數
least(v1,v2)

selectename,empno,mgr,least(empno,mgr)lownum
fromemp
whereempno0

trunc(sal,0)
取sal的近似值（截斷）

空值函數
nvl(v1,v2)
v1為列名，如果v1不是空值，nvl返回其列值。
v1為空值，返回v2的值。

聚組函數
selectsum(comm)
fromemp;
（返回一個匯總信息）
不能把sum用在select語句里除非用groupby

字符型、日期型、數字型的聚組函數
minmaxcount可用于任何數據類型

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計算某一字段中不同的值的個數

其它聚組函數（只用于數字型數據）
avg計算平均工資
selectavg(sal)
fromemp;

stddev計算工資的平均差
selectstddev(sal)
fromemp;

sum計算總工資
selectsum(sal)
fromemp;

groupby子句
selectdeptno,sum(sal),avg(sal)
fromemp
groupbydeptno;

按多個條件分組
每個部門的雇員數
selectdeptno,count(*)
fromemp
groupbydeptno;

每個部門的每個工種的雇員數
selectdeptno,job,count(*)
fromemp
groupbydeptno,job;

滿足條件的分組
（where是針對select的，having是針對groupby的）
哪些部門的工資總和超過了9000
selectdeptno,sum(sal)
fromemp
groupbydeptno
havingsum(sal)>9000;

select小結
除去職員，哪些部門的工資總和超過了8000
selectdeptno,sum(sal)
fromemp
wherejob!='clerk'
groupbydeptno
havingsum(sal)>8000
orderbysum(sal);

---------
7高級查詢
等值聯接
selectempno,ename,job,emp.deptno,dname
fromemp,dept
whereemp.deptno=dept.deptno;

外聯接
selectename,dept.deptno,loc
fromemp,dept
whereemp.deptno(+)=dept.deptno;
如果在dept.deptno中有的數值，在emp.deptno中沒有（如deptno=40），
則作外聯接時，結果中會產生一個空值

自聯接：同一基表的不同行要做聯接，可使用自聯接
指出每個雇員的經理名字
selectworker.ename,manager.enamemanager
fromempworker,empmanager
whereworker.mgr=manager.empno;

非等值聯接
哪些雇員的工資屬于第三級別
selectename,sal
fromemp,salgrade
wheregrade=3
andsalbetweenlosalandhisal;
（基表salgrade：gradelosalhisal）

集合運算
行的連接
集合運算把2個或多個查詢結果合并為一個
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

介紹幾個視圖
accountview
enamesaljob

salesview
enamesaljob

researchview
enamesaljob

union運算
返回一個查詢結果中有但又不重要的行，它將基表或視圖中的記錄合并在一起
所有部門中有哪些雇員工資超過2000
對應列的數據類型必須相同
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運算
返回查詢結果中相同的部分
各個部門中有哪些相同的工種
selectjob
fromaccount
intersect
selectjob
fromresearch
intersect
selectjob
fromsales;

minus運算
返回在第一個查詢結果中與第二個查詢結果不相同的那部分行記錄。
有哪些工種在財會部中有，而在銷售部中沒有？
selectjobfromaccount
minus
selectjobfromsales;

子查詢
slectename,deptno
fromemp
wheredeptno=
(selectdeptno
fromemp
whereename='smith');

多級子查詢
selectename,job,sal
fromemp
wherejob=
(selectjob
fromemp
whereename='clark')
orsal>
(selectsal
fromemp
whereename='clark');

多個基表與子查詢
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');

子查詢中使用聚組函數
selectename,hiredate
fromemp
wherehiredate=
(selectmin(hiredate)
fromemp);

------------
8授權
系統權限
DBA所有權限
RESOURCE注冊，創建新的基表
CONNECT，注冊，查詢

只有DBA才有權創建新的用戶
grantconnecttoscott
identifiedbytiger;

DBA或用戶自己可以改變用戶口令
grantconnecttoscott
identifiedbyleopard;

基表權限1
有兩種方法獲得對基表操作的權限

創建自己的基表
獲得基表創建用戶的許可
grantselect,insert
onemp
toscott;

這些權限有
selectinsertupdatedeletealterindex

把所有權限授于他人
grantallonemptoscott;

同義詞
select*
fromscott.emp

創建同義詞
為用戶allen的EMP基表創建同義詞employee
createsynonymemployee
forallen.emp

基表權限2
你可以使其他用戶有這樣的權力，即其他用戶可以把你的基表權限授予他人
grantall
onemp
toscott
withgrantoption;

收回權限
系統權限只有被DBA收回

基表權限隨時都可以收回

revokeinsert
onemp
fromscott;

---------
9索引
建立索引
createindexemp_ename
onemp(ename);

刪除索引
dropindexemp_ename;

關于索引
只對較大的基表建立索引（至少50條記錄）
建立索引之前插入數據
對一個基表可建立任意多個索引
一般是在作為主鍵的列上建立索引
建立索引之后，不影響SQL命令的執行
建立索引之后，ORACLE自動維護和使用索引

保證數據唯一性
提高執行速度的同時，索引還可以保證每個記錄中的每個列值是不重復的。
createuniqueindexemp_empno
onemp(empno);

--------
練習和答案

有沒有工資比獎金多的雇員？如果有，按工資的降序排列。
如果有兩個以上的雇員工資相同，按他們的名字排序。
selectenameemployee,salsalary,commcommision
fromemp
wheresal>comm
orderbysaldesc,ename;

列出有關雇員姓名、獎金占收百分比的信息。
要求顯示時列名意義明確，按雇員姓名排序，不包括獎金未知的雇員。
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公司新建一個部門，編號為50，其它信息均不可知。
insertintodept(dname,deptno)
values('faclities',50);

創建視圖，三個列名，其中不包括職員信息
createviewemployee("employeename",
"employeenumber",
"employeejob")
asselectename,empno,job
fromemp
wherejob!='clerk';

制作工資報表，包括雇員姓名、受雇時間（按星期計算），工資和部門編號，
一頁顯示一個部門的信息，每頁尾，顯示該頁的工資之和以及受雇時間之和，
報表結尾處，顯示所有雇員的工資總和以及受雇時間總和，
工資按美元計算，受雇時間按星期計算，每頁的上方應有標題。
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;

制作報表，包括雇員姓名、總收入和受傭日期，
且：姓名的第一個字母必須大寫，雇傭日期格式為MM/DD/YYYY，
總收入包括沒有獎金的雇員的總收入，姓名按字母順序排列。
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;

列出有超過7個周邊國家的國家名字和面積。
selectnation,area
fromnation
wherecodein
(selectnation_code
fromborder
groupbynation_code
havingcount(*)>7);

列出所有面積大于等于日本的島國的國名和人口。
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');

列出所有邊界在其它國家中的國名，并且顯示其邊界國家名字。
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的塊結構和數據類型

塊結構的特點
嵌套
begin
......
begin
......
exception
......
end;
exception
......
end;

標識符：
不能超過30個字符
第一個字符必須為字母
其余字符可以是字母，數字，$，_，或#
不區分大小寫形式
如果用雙引號括起來，則字符順序可以任意排列，并區分大小寫形式
無SQL保留字

數據類型
數字型：
整數，實數，以及指數

字符串：
用單引號括起來
若在字符串表示單引號，則使用兩個單引號
字符串長度為零（兩個單引號之間沒有字符），則表示NULL

字符：
長度為1的字符串

數據定義
語法
標識符[常數>數據類型[NOTNULL>[:=PL/SQL表達式>;
':='表示給變量賦值

數據類型包括
數字型number(7,2)
字符型char(120)
日期型date
布爾型boolean（取值為true,false或null，不存貯在數據庫中）

日期型
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表達式
numvar:=5;
boolvar:=true;
datevar:='11-JUN-87';

字符型、數字型表達式中的空值
null+<數字>=null（空值加數字仍是空值）
null><數字>=null（空值與數字進行比較，結果仍是空值）
null｜｜'字符串'='字符串'（null即''）
（空值與字符串進行連接運算，結果為原字符串）

變量作用范圍
標識符在宣言它的塊中有效
標識符如果不在子塊中重新定義，則在PL/SQL塊的所有子塊中同樣有效
重新定義后的標識符，作用范圍僅在本子塊中有效

例
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;

事務處理
commit[WORK>;
rollback[WORK>;
（關鍵字WORK可選，但對命令執行無任何影響）
savepoint標記名;（保存當前點）
在事務中標記當前點
rollback[WORK>to[SAVEPOINT>標記名;（回退到當前保存點）
取消savepoint命令之后的所有對數據庫的修改
關鍵字WORK和SAVEPOINT為可選項，對命令執行無任何影響

函數
PL/SQL塊中可以使用SQL命令的所有函數
insertintophonebook(lastname)value(upper(my_lastname));
selectavg(sal)intoavg_salfromemp;

對于非SQL命令，可使用大多數個體函數
不能使用聚組函數和參數個數不定的函數，如
x:=sqrt(y);
lastname:=upper(lastname);
age_diff:=months_between(birthday1,birthday2)/12;

賦值時的數據類型轉換
4種賦值形式:
變量名:=表達式
insertinto基表名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';
字符型轉換成日期型
insertinto表名(num_col)values('604badnumber');
錯誤，無法成功地轉換數據類型

---------
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循環
語法
loop
......
endloop;
exit;（退出循環）
exit[when>;（退出循環，當滿足WHEN時）
例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，循環無法結束）

例2
FOR語法
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);
/*循環次數從30到21*/
endloop;
bowl:=my_index;
end;
跟在inreverse后面的數字必須是從小到大的順序，必須是整數，不能是變量或表達式

----------
6游標
顯式游標

打開游標
open<游標名>
例
opencolor_cur;

游標屬性
%notfound
%found
%rowcount
%isopen
例
fetchmy_curintomy_var;
whilemy_cur%foundloop
(處理數據)
fetchmy_curintomy_var;
exitwhenmy_cur%rowcount=10;
endloop;

%notfound屬性
取值情況如下：
fetch操作沒有返回記錄，則取值為true
fetch操作返回一條記錄，則取值為false
對游標無fetch操作時，取值為null
<游標名>%notfound
例
ifcolor_cur%notfoundthen...
注：如果沒有fetch操作，則<游標名>%notfound將導致出錯，
因為%notfound的初始值為NULL。

關閉游標
close<游標名>
例
closecolor_cur;

游標的FOR循環
語法
for<記錄名>in<游標名>loop
<一組命令>
endloop;
其中：
索引是建立在每條記錄的值之上的
記錄名不必聲明
每個值對應的是記錄名，列名
初始化游標指打開游標
活動集合中的記錄自動完成FETCH操作
退出循環，關閉游標

隱式游標
隱式游標是指SQL命令中用到的，沒有明確定義的游標
insert,update,delete,select語句中不必明確定義游標
調用格式為SQL%
存貯有關最新一條SQL命令的處理信息

隱式游標的屬性
隱式游標有四個屬性
SQL%NOTFOUND
SQL%FOUND
SQL%ROWCOUNT：隱式游標包括的記錄數
例：
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命令執行完畢，PL/SQL立即關閉隱式游標。

---------
7標號
GOTO語句
用法：
gotoyou_are_here;
其中you_are_here是要跳轉的語句標號
標號必須在同一組命令，或是同一塊中使用

正確的使用
<>（標號）
x:=x+1
ifa>bthen
b:=b+c;
gotodinner;
endif;

錯誤的使用
gotojail;
ifa>bthen
b:=b+c;
<>（標號）
x:=x+1;
endif;

標號：解決意義模糊
標號可用于定義列值的變量
<>
declare
deptnonumber:=20;
begin
updateempsetsal=sal*1.1
wheredeptno=sample.deptno;
commit;
endsample;
如果不用標號和標號限制符，這條命令將修改每條記錄。

----------
8異常處理
預定義的異常情況
任何ORACLE錯誤都將自動產生一個異常信息
一些異常情況已命名，如：
no_data_found當SELECT語句無返回記錄時產生
too_many_rows沒有定義游標，而SELECT語句返回多條記錄時產生
whenevernotfound無對應的記錄

用戶定義的異常情況
由用戶自己獲取
在DECLARE部分定義：
declare
xnumber;
something_isnt_rightexception;
用戶定義的異常情況遵循一般的作用范圍規則
條件滿足時，獲取異常情況：raisesomething_isnt_right
注意：同樣可以獲取預定義的異常情況

exception_init語句
允許為ORACLE錯誤命名

調用格式：
pragmaexception_init(<表達式>,);
例
declare
deadlock_detectedexception;
pragmaexception_init(deadlock_detected,-60);

raise語句
單獨使用RAISE命令，可再一次獲取當前的異常情況（就象異常情況被重復處理了一樣）。
在異常處理中，此語句只能單獨使用。

異常處理標識符
一組用于處理異常情況的語句：
exception
when<表達式>or[表達式...>then
<一組語句>
...
whenothersthen--最后一個處理
<一組語句>
end;既結束PL/SQL塊部分，也結束異常處理部分

--------
練習與答案
1:
接收contract_no和item_no值，在inventory表中查找，如果產品：
已發貨，在arrival_date中賦值為今天后的7天
已訂貨，在arrival_date中賦值為今天后的一個月
既無訂貨又無發貨，則在arrival_date中賦值為今天后的兩個月，
并在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.用帶'&'的變量提示用戶輸入部門編號
3.把雇員姓名及工資存入prnttable表中，基結構為：
createtableprnttable
(seqnumber(7),linechar(80));
4.異常情況為，部門中獎金不為空值的雇員信息才能存入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;



Java研究組織-版權所有2002-2002

作者：UB時間：2003-08-14 21:06:59[修改][回復][刪除]

ORACLE數據庫對象與用戶管理

一、ORACLE數據庫的模式對象的管理與維護

本節的主要內容是關于ORACLE數據庫的模式對象的管理與維護，這些模式對象包括：表空間、表、視圖、索引、序列、同義詞、聚集和完整性約束。對于每一個模式對象，首先描述了它的定義，說明了它的功能，最后以基于SQL語言的實例說明如何對它們進行管理于維護。

1.1表空間

由于表空間是包含這些模式對象的邏輯空間，有必要先對它進行維護。

創建表空間
SQL>CREATETABLESPACEjxzy

>DATAFILE‘/usr/oracle/dbs/jxzy.dbf’

>ONLINE;

修改表空間
SQL>ALTERTABLESPACEjxzyOFFLINENORMAL;

SQL>ALTERTABLESPACEjxzy

>RENAMEDATAFILE‘/usr/oracle/dbs/jxzy.dbf’

>TO‘/usr/oracle/dbs/jxzynew.dbf’

>ONLINE

SQL>CREATETABLESPACEjxzyONLINE

刪除表空間
SQL>DROPTABLESPACEjxzy

>INCLUDINGCONTENTS

1.2表維護

表是數據庫中數據存儲的基本單位，一個表包含若干列，每列具有列名、類型、長度等。

表的建立
SQL>CREATETABLEjxzy.switch(

>OFFICE_NUMNUMBER(3,0)NOTNULL,

>SWITCH_CODENUMBER(8,0)NOTNULL,

>SWITCH_NAMEVARCHAR2(20)NOTNULL);

表的修改
SQL>ALTERTABLEjxzy.switch

>ADD(DESCVARCHAR2(30));

表的刪除
SQL>DROPTABLEjxzy.switch

>CASCADECONSTRAINTS

//刪除引用該表的其它表的完整性約束

1.3視圖維護

視圖是由一個或若干基表產生的數據集合，但視圖不占存儲空間。建立視圖可以保護數據安全（僅讓用戶查詢修改可以看見的一些行列）、簡化查詢操作、保護數據的獨立性。

視圖的建立
SQL>CREATEVIEWjxzy.pole_well_viewAS

>(SELECTpole_path_numASpath,

poleASdevice_numFROMpole

>UNION

>SELECTpipe_path_numASpath,

>wellASdevice_numFROMwell);

視圖的替換
SQL>REPLACEVIEWjxzy.pole_well_viewAS

>(SELECTpole_path_numASpath,

poleASsupport_deviceFROMpole
>UNION

>SELECTpipe_path_numASpath,

wellASsupport_deviceFROMwell);
視圖的刪除
SQL>DROPVIEWjxzy.pole_well_view;

1.4序列維護

序列是由序列發生器生成的唯一的整數。

序列的建立
SQL>CREATESEQUENCEjxzy.sequence_cable

>STARTWITH1

>INCREMENTBY1

>NO_MAXVALUE;

建立了一個序列，jxzy.sequence_cable.currval返回當前值，jxzy.sequence_cable.nextval返回當前值加1后的新值

序列的修改
SQL>ALTERSEQUENCEjxzy.sequence_cable

>STARTWITH1//起點不能修改，若修改，應先刪除，然后重新定義

>INCTEMENTBY2

>MAXVALUE1000;

序列的刪除
SQL>DROPSEQUENCEjxzy.sequence_cable

1.5索引維護

索引是與表相關的一種結構，它是為了提高數據的檢索速度而建立的。因此，為了提高表上的索引速度，可在表上建立一個或多個索引，一個索引可建立在一個或幾個列上。

對查詢型的表，建立多個索引會大大提高查詢速度，對更新型的表，如果索引過多，會增大開銷。

索引分唯一索引和非唯一索引

索引的建立
SQL>CREATEINDEXjxzy.idx_switch

>ONswitch(switch_name)

>TABLESPACEjxzy;

索引的修改
SQL>ALTERINDEXjxzy.idx_switch

>ONswitch(office_num,switch_name)

>TABLESPACEjxzy;

索引的刪除
SQL>DROPINDEXjxzy.idx_switch;

1.6完整性約束管理

數據庫數據的完整性指數據的正確性和相容性。數據完整型檢查防止數據庫中存在不符合語義的數據。

完整性約束是對表的列定義一組規則說明方法。ORACLE提供如下的完整性約束.

a.NOTNULL非空

b.UNIQUE唯一關鍵字

c.PRIMATYKEY主鍵一個表只能有一個，非空

d.FOREIGAKEY外鍵

e.CHECK表的每一行對指定條件必須是true或未知（對于空值）

例如：

某列定義非空約束
SQL>ALTERTABLEoffice_organization

>MODIFY(descVARCHAR2(20)

>CONSTRAINTnn_descNOTNULL)

某列定義唯一關鍵字
SQL>ALTERTABLEoffice_organization

>MODIFY(office_nameVATCHAR2(20)

>CONSTRAINTuq_officenameUNIQUE)

定義主鍵約束，主鍵要求非空
SQL>CREATETABLEswitch(switch_codeNUMBER(8)

>CONSTRAINTpk_switchcodePRIMARYKEY,)

使主鍵約束無效
SQL>ALTERTABLEswitchDISABLEPRIMARYKEY

定義外鍵
SQL>CREATETABLEPOLE(pole_codeNUMBER(8),

>office_numnumber(3)

>CONSTRAINTfk_officenum

>REFERENCESoffice_organization(office_num)

>ONDELETECASCADE);

定義檢查
SQL>CREATETABLEoffice_organization(

>office_numNUMBER(3),

>CONSTRAINTcheck_officenum

>CHECK(office_numBETWEEN10AND99);

二、ORACLE數據庫用戶與權限管理

ORACLE是多用戶系統，它允許許多用戶共享系統資源。為了保證數據庫系統的安全，數據庫管理系統配置了良好的安全機制。

2.1ORACLE數據庫安全策略

建立系統級的安全保證
系統級特權是通過授予用戶系統級的權利來實現，系統級的權利（系統特權）包括：建立表空間、建立用戶、修改用戶的權利、刪除用戶等。系統特權可授予用戶，也可以隨時回收。ORACLE系統特權有80多種。

建立對象級的安全保證
對象級特權通過授予用戶對數據庫中特定的表、視圖、序列等進行操作（查詢、增、刪改）的權利來實現。

建立用戶級的安全保證
用戶級安全保障通過用戶口令和角色機制（一組權利）來實現。引入角色機制的目的是簡化對用戶的授權與管理。做法是把用戶按照其功能分組，為每個用戶建立角色，然后把角色分配給用戶，具有同樣角色的用戶有相同的特權。

2.2用戶管理

ORACLE用戶管理的內容主要包括用戶的建立、修改和刪除

用戶的建立
SQL>CREATEUSERjxzy

>IDENTIFIEDBYjxzy_password

>DEFAULTTABLESPACEsystem

>QUATA5MONsystem;//供用戶使用的最大空間限額

用戶的修改
SQL>CREATEUSERjxzy

>IDENTIFIEDBYjxzy_pw

>QUATA10MONsystem;

刪除用戶及其所建對象
SQL>DROPUSERjxzyCASCADE;//同時刪除其建立的實體

2.3系統特權管理與控制

ORACLE提供了80多種系統特權，其中每一個系統特權允許用戶執行一個或一類數據庫操作。

授予系統特權
SQL>GRANTCREATEUSER,ALTERUSER,DROPUSER

>TOjxzy_new

>WITHADMINOPTION;

回收系統特權
SQL>REVOKECREATEUSER,ALTERUSER,DROPUSER

>FROMjxzy_new

//但沒有級聯回收功能

顯示已被授予的系統特權（某用戶的系統級特權）
SQL>SELECT*FROMsys.dba_sys_privs

2.4對象特權管理與控制

ORACLE對象特權指用戶在指定的表上進行特殊操作的權利。這些特殊操作包括增、刪、改、查看、執行（存儲過程）、引用（其它表字段作為外鍵）、索引等。

授予對象特權
SQL>GRANTSELECT,INSERT(office_num,office_name),

>UPDATE(desc)ONoffice_organization

>TOnew_adminidtrator

>WITHGRANTOPTION;

//級聯授權

SQL>GRANTALLONoffice_organization

>TOnew_administrator

回收對象特權
SQL>REVOKEUPDATEONoffice_orgaization

>FROMnew_administrator

//有級聯回收功能

SQL>REVOKEALLONoffice_organization

>FROMnew_administrator

顯示已被授予的全部對象特權
SQL>SELECT*FROMsys.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>GRANTDBATOnew_administractor

>WITHGRANTOPTION;

最大值
select greatest(jjkxd_sysj,slkxd_sysj,sp_lgfstl_sysj) from wx_ysjcsj where jh_id=6

最小值
select least(jjkxd_sysj,slkxd_sysj,sp_lgfstl_sysj) from wx_ysjcsj where jh_id=6

posted on 2006-06-27 09:57 ASONG 閱讀(311) 評論(0) 編輯收藏所屬分類: database

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發表評論。




網站導航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關文章: oracle數據庫的還原和備份 Oracle性能調整的誤區 ORACLE SQL語句優化技術分析常用SQL語句書寫技巧(轉) SQL語句性能優化--LECCO SQL Expert Oracle10g EM亂碼之快速解決在Linux x86上安裝Oracle數據庫 10g (4) 在Linux x86上安裝Oracle數據庫 10g (3) 在Linux x86上安裝Oracle數據庫 10g (2) 在Linux x86上安裝Oracle數據庫 10g (1)

ASONG

SQL大全

導航

常用鏈接

留言簿(2)

我參與的團隊

隨筆檔案

文章分類

文章檔案

webgis

搜索

最新評論