sql語句的程式設計手冊(轉)

zhouwf0726發表於2019-01-20
SQL程式設計

sql語句的程式設計手冊 SQL PLUS基礎命令


一、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：當報表超過一頁時，每顯示一屏就暫停顯示，等待使用者打Enter鍵，再接著顯示；
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;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242362/，如需轉載，請註明出處，否則將追究法律責任。

相關文章