胡波 2013年10月
PS:由于字數限制，超出了2萬多字，故删除部分描述性文字，僅保留關鍵資訊。
使用者的管理
建立
概述：在 oracle 中要建立一個新的使用者使用 create user 語句，一般是具有 dba(資料庫管理者)的權限才能使用。
create user 使用者名 identified by 密碼; (oracle 有個毛病，密碼必須以字母開頭，如果以字母開頭，它不會建立使用者)
給使用者修改密碼
password 使用者名
如果給别人修改密碼則需要具有 dba 的權限，或是擁有 alter user 的系統權限
SQL> alter user 使用者名 identified by 新密碼
删除使用者
概述：一般以 dba 的身份去删除某個使用者，如果用其它使用者去删除使用者則需要具有 drop user 的權限。
比如 drop user 使用者名 【cascade】
在删除使用者時，注意：
如果要删除的使用者，已經建立了表，那麼就需要在删除的時候帶一個參數 cascade;
賬戶鎖定
1. 賬戶鎖定
例子：指定 scott 這個使用者最多隻能嘗試 3 次登陸，鎖定時間為 2 天，讓我們看看怎麼實作。
建立 profile 檔案
SQL> create profile lock_account limit failed_login_attempts 3 password_lock_time 2;
SQL> alter user scott profile lock_account;
2. 給賬戶( 使用者) 解鎖
SQL> alter user tea account unlock;
oracle 表的管理( ( 資料類型，表建立删 除，資料 D CRUD 操作) )
表名和列的命名規則
必須以字母開頭
長度不能超過 30 個字元
不能使用 oracle 的保留字
隻能使用如下字元 A-Z，a-z，0-9，$,#等
oracle 支援的資料類型
字元類
char 定長 最大 2000 個字元
clob(character large object) 字元型大對象 最大 4G
char 查詢的速度極快浪費空間，查詢比較多的資料用。
varchar 節省空間
數字型
number 範圍 -10 的 38 次方 到 10 的 38 次方
number(5,2)
number(5)
日期類型
date 包含年月日和時分秒 oracle 預設格式 1-1 月-1999
timestamp 這是 oracle9i 對 date 資料類型的擴充。可以精确到毫秒。
圖檔
blob 二進制資料 可以存放圖檔/聲音 4G 一般來講，在真實項目中是不會把圖檔和聲音真的往資料庫裡存放，一般存放
圖檔、視訊的路徑，如果安全需要比較高的話，則放入資料庫。
建表
create table student ( ---表名
xh number(4), --學号
xm varchar2(20), --姓名
sex char(2), --性别
birthday date, --出生日期
sal number(7,2) --獎學金
);
CREATE TABLE class(
classId NUMBER(2),
cName VARCHAR2(40)
);
修改表
添加一個字段
SQL>ALTER TABLE student add (classId NUMBER(2));
修改一個字段的長度
SQL>ALTER TABLE student MODIFY (xm VARCHAR2(30));
修改字段的類型/或是名字(不能有資料) 不建議做
SQL>ALTER TABLE student modify (xm CHAR(30));
删除一個字段 不建議做(删了之後，順序就變了。加就沒問題，應為是加在後面)
SQL>ALTER TABLE student DROP COLUMN sal;
修改表的名字 很少有這種需求
SQL>RENAME student TO stu;
删除表
SQL>DROP TABLE student;
添加資料
所有字段都插入資料
INSERT INTO student VALUES ('A001', '張三', '男', '01-5 月-05', 10);
oracle 中預設的日期格式‘dd-mon-yy’ dd 日子(天) mon 月份 yy 2 位的年 ‘09-6 月-99’ 1999 年 6 月 9 日
修改日期的預設格式(臨時修改，資料庫重新開機後仍為預設；如要修改需要修改系統資料庫)
ALTER SESSION SET NLS_DATE_FORMAT ='yyyy-mm-dd';
修改後，可以用我們熟悉的格式添加日期類型：
INSERT INTO student VALUES ('A002', 'MIKE', '男', '1905-05-06', 10);
插入部分字段
INSERT INTO student(xh, xm, sex) VALUES ('A003', 'JOHN', '女');
插入空值
INSERT INTO student(xh, xm, sex, birthday) VALUES ('A004', 'MARTIN', '男', null);
問題來了，如果你要查詢 student 表裡 birthday 為 null 的記錄，怎麼寫 sql 呢？
錯誤寫法：select * from student where birthday = null;
正确寫法：select * from student where birthday is null;
如果要查詢 birthday 不為 null,則應該這樣寫：
select * from student where birthday is not null;
修改資料
修改一個字段
UPDATE student SET sex = '女' WHERE xh = 'A001';
修改多個字段
UPDATE student SET sex = '男', birthday = '1984-04-01' WHERE xh = 'A001';
修改含有 null 值的資料
不要用 = null 而是用 is null；
SELECT * FROM student WHERE birthday IS null;
删除資料
DELETE FROM student;
删除所有記錄，表結構還在，寫日志，可以恢複的，速度慢。
Delete 的資料可以恢複。
savepoint a; --建立儲存點
DELETE FROM student;
rollback to a; --恢複到儲存點
一個有經驗的 DBA，在確定完成無誤的情況下要定期建立還原點。
DROP TABLE student; --删除表的結構和資料；
delete from student WHERE xh = 'A001'; --删除一條記錄；
truncate TABLE student; --删除表中的所有記錄，表結構還在，不寫日志，無法找回删除的記錄，速度快。
oracle 表查詢 (1)
檢視表結構
DESC emp;
查詢所有列
SELECT * FROM dept;
切忌動不動就用 select *
查詢指定列
SELECT ename, sal, job, deptno FROM emp;
如何取消重複行 DISTINCT
SELECT DISTINCT deptno, job FROM emp;
查詢 SMITH 所在部門，工作，薪水
SELECT deptno,job,sal FROM emp WHERE ename = 'SMITH';
注意：oracle 對内容的大小寫是區分的，是以 ename='SMITH'和 ename='smith'是不同的
使用 nvl null
問題：如何顯示每個雇員的年工資？
SELECT sal*13+nvl(comm, 0)*13 "年薪" , ename, comm FROM emp;
使用列的别名
SELECT ename "姓名", sal*12 AS "年收入" FROM emp;
如何處理 null 值
使用 nvl 函數來處理
如何連接配接字元串(||)
SELECT ename || ' is a ' || job FROM emp;
如何使用 like 操作符
%：表示 0 到多個字元 _：表示任意單個字元
問題：如何顯示首字元為 S 的員工姓名和工資？
SELECT ename,sal FROM emp WHERE ename like 'S%';
多列子查詢
單行子查詢是指子查詢隻傳回單列、單行資料，多行子查詢是指傳回單列多行資料，都是針對單列而言的，而多列子查詢是指
查詢傳回多個列資料的子查詢語句。
請思考如何查詢與 SMITH 的部門和崗位完全相同的所有雇員。
SELECT deptno, job FROM emp WHERE ename = 'SMITH';
SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH');
在 from 子句中使用子查詢
SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno;
SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) ds
WHERE e.deptno = ds.deptno AND e.sal > ds.mysal;
如何衡量一個程式員的水準？
網絡處理能力， 資料庫， 程式代碼的優化程式的效率要很高
分頁查詢
oracle 的分頁一共有三種方式
1.根據 rowid 來分
select * from t_xiaoxi where rowid in (select rid from (select rownum rn, rid from(select rowid rid, cid from
t_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc;
執行時間 0.03 秒
2.按分析函數來分
select * from (select t.*, row_number() over(order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980;
執行時間 1.01 秒
3.按 rownum 來分
select * from (select t.*,rownum rn from(select * from t_xiaoxi order by cid desc)t where rownum<10000) where
rn>9980;
執行時間 0.1 秒
其中 t_xiaoxi 為表名稱，cid 為表的關鍵字段，取按 cid 降序排序後的第 9981-9999 條記錄，t_xiaoxi 表有 70000 多條記錄。
個人感覺 1 的效率最好，3 次之，2 最差。
用查詢結果建立新表
CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT empno, ename, sal, job, deptno FROM emp;
合并查詢
有時在實際應用中，為了合并多個 select 語句的結果，可以使用集合操作符号 union，union all，intersect，minus
多用于資料量比較大的資料局庫，運作速度快。
1). union
該操作符用于取得兩個結果集的并集。當使用該操作符時，會自動去掉結果集中重複行。
SELECT ename, sal, job FROM emp WHERE sal >2500
UNION
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
2).union all
該操作符與 union 相似，但是它不會取消重複行，而且不會排序。
SELECT ename, sal, job FROM emp WHERE sal >2500
UNION ALL
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
什麼是事務
事務用于保證資料的一緻性，它由一組相關的 dml 語句組成，該組的 dml(資料操作語言，增删改，沒有查詢)語句要麼全部成功，要麼全部失敗。
送出事務
當執行用 commit 語句可以送出事務。當執行了 commit 語句之後，會确認事務的變化、結束事務。删除儲存點、釋放鎖，當使用 commit 語句結束事務之後，其它會話将可以檢視到事務變化後的新資料。儲存點就是為回退做的。儲存點的個數沒有限制
回退事務
在介紹回退事務前，我們先介紹一下儲存點(savepoint)的概念和作用。儲存點是事務中的一點。用于取消部分事務，當結束事務時，會自動的删除該事務所定義的所有儲存點。當執行 rollback 時，通過指定儲存點可以回退到指定的點，這裡我們作圖
說明。
事務的幾個重要操作
1.設定儲存點 savepoint a
2.取消部分事務 rollback to a
3.取消全部事務 rollback
注意：這個回退事務，必須是沒有 commit 前使用的；如果事務送出了，那麼無論你剛才做了多少個儲存點，都統統沒有。
字元函數
lower(char)：将字元串轉化為小寫的格式。
upper(char)：将字元串轉化為大寫的格式。
length(char)：傳回字元串的長度。
substr(char,m,n)：取字元串的子串 n 代表取 n 個的意思，不是代表取到第 n 個
replace(char1,search_string,replace_string)
instr(char1,char2,[,n[,m]])取子串在字元串的位置
數學函數
數學函數包括 cos，cosh，exp，ln, log，sin，sinh，sqrt，tan，
tanh，acos，asin，atan，round，我們講最常用的：
round(n,[m]) 該函數用于執行四舍五入，如果省掉 m，則四舍五入到整數，如果 m 是正數，則四舍五入到小數點的 m 位後。
如果 m 是負數，則四舍五入到小數點的 m 位前。
trunc(n,[m]) 該函數用于截取數字。如果省掉 m，就截去小數部分，如果 m 是正數就截取到小數點的 m 位後，如果 m 是負數，
則截取到小數點的前 m 位。
mod(m,n)
floor(n) 傳回小于或是等于 n 的最大整數
ceil(n) 傳回大于或是等于 n 的最小整數
對數字的處理，在财務系統或銀行系統中用的最多，不同的處理方法，對财務報表有不同的結果。其它的數學函數：
abs(n)： 傳回數字 n 的絕對值
select abs(-13) from dual;
acos(n)： 傳回數字的反餘弦值
asin(n)： 傳回數字的反正弦值
atan(n)： 傳回數字的反正切值
cos(n)：
exp(n)： 傳回 e 的 n 次幂
log(m,n)： 傳回對數值
power(m,n)： 傳回 m 的 n 次幂
日期函數
(1)sysdate: 該函數傳回系統時間
(2)add_months(d,n)
(3)last_day(d)：傳回指定日期所在月份的最後一天
to_char
SQL> select * from emp where to_char(hiredate, 'yyyy')=1980;
to_date
函數 to_date 用于将字元串轉換成 date 類型的資料。
系統函數
sys_context
1)terminal：目前會話客戶所對應的終端的标示符
2)lanuage: 語言
3)db_name： 目前資料庫名稱
4)nls_date_format： 目前會話客戶所對應的日期格式
5)session_user： 目前會話客戶所對應的資料庫使用者名
6)current_schema： 目前會話客戶所對應的預設方案名
7)host： 傳回資料庫所在主機的名稱
導出表
1.導出自己的表
exp userid=scott/[email protected] tables=(emp,dept) file=d:\e1.dmp
2.導出其它方案的表
如果使用者要導出其它方案的表，則需要 dba 的權限或是 exp_full_database 的權限，比如 system 就可以導出 scott 的表
E:\oracle\ora92\bin>exp userid=system/[email protected] tables=(scott.emp) file=d:\e2.emp
特别說明：在導入和導出的時候，要到 oracle 目錄的 bin 目錄下。
3. 導出表的結構
exp userid=scott/[email protected] tables=(emp) file=d:\e3.dmp rows=n
4. 使用直接導出方式
exp userid=scott/[email protected] tables=(emp) file=d:\e4.dmp direct=y
這種方式比預設的正常方式速度要快，當資料量大時，可以考慮使用這樣的方法。
這時需要資料庫的字元集要與用戶端字元集完全一緻，否則會報錯...
導出方案
導出方案是指使用 export 工具導出一個方案或是多個方案中的所有對象(表，索引，限制...)和資料。并存放到檔案中。
1. 導出自己的方案
exp userid=scott/[email protected] owner=scott file=d:\scott.dmp
2. 導出其它方案
如果使用者要導出其它方案，則需要 dba 的權限或是 exp_full_database 的權限，比如 system 使用者就可以導出任何方案
exp userid=system/[email protected] owner=(system,scott) file=d:\system.dmp
導出資料庫
導出資料庫是指利用 export 導出所有資料庫中的對象及資料，要求該使用者具有 dba 的權限或者是 exp_full_database 權限
增量備份(好處是第一次備份後，第二次備份就快很多了)
exp userid=system/[email protected] full=y inctype=complete file=d:\all.dmp
導入
導入表
1. 導入自己的表
imp userid=scott/[email protected] tables=(emp) file=d:\xx.dmp
2. 導入表到其它使用者
要求該使用者具有 dba 的權限，或是 imp_full_database
imp userid=system/[email protected] tables=(emp) file=d:\xx.dmp touser=scott
3. 導入表的結構
隻導入表的結構而不導入資料
imp userid=scott/[email protected] tables=(emp) file=d:\xx.dmp rows=n
4. 導入資料
如果對象(如比表)已經存在可以隻導入表的資料
imp userid=scott/[email protected] tables=(emp) file=d:\xx.dmp ignore=y
導入方案
導入方案是指使用 import 工具将檔案中的對象和資料導入到一個或是多個方案中。如果要導入其它方案，要求該使用者具有 dba
的權限，或者 imp_full_database
1． 導入自身的方案
imp userid=scott/tiger file=d:\xxx.dmp
2． 導入其它方案
要求該使用者具有 dba 的權限
imp userid=system/manager file=d:\xxx.dmp fromuser=system touser=scott
導入資料庫
在預設情況下，當導入資料庫時，會導入所有對象結構和資料，案例如下：
imp userid=system/manager full=y file=d:\xxx.dmp
資料庫的邏輯結構
oracle 中邏輯結構包括表空間、段、區和塊。
說明一下資料庫由表空間構成，而表空間又是由段構成，而段又是由區構成，而區又是由 oracle 塊構成的這樣的一種結構，可以提高資料庫的效率。
表空間
表空間用于從邏輯上組織資料庫的資料。資料庫邏輯上是由一個或是多個表空間組成的。通過表空間可以達到以下作用：
1. 控制資料庫占用的磁盤空間
2. dba 可以将不同資料類型部署到不同的位置，這樣有利于提高 i/o 性能，同時利于備份和恢複等管理操作。
建立表空間
建立表空間是使用 crate tablespace 指令完成的，需要注意的是，一般情況下，建立表空間是特權使用者或是 dba 來執行的，如果用其它使用者來建立表空間，則使用者必須要具有 create tablespace 的系統權限。
建立資料表空間
在建立資料庫後，為便于管理表，最好建立自己的表空間
create tablespace data01 datafile 'd:\test\dada01.dbf' size 20m uniform size 128k;
說明： 執行完上述指令後， 會建立名稱為 data01 的表空間， 并為該表空間建立名稱為 data01.dbf 的資料檔案， 區的大小為 128k
使用資料表空間
create table mypart(deptno number(4), dname varchar2(14), loc varchar2(13)) tablespace data01;
檢視表屬于那個表空間
select tablespace_name, table_name from user_tables where table_name=’emp’;
删除表空間
drop tablespace ‘表空間’ including contents and datafiles;
說明：including contents 表示删除表空間時，删除該空間的所有資料庫對象，而 datafiles 表示将資料庫檔案也删除。
表空間小結
1. 了解表空間和資料檔案的作用
2. 掌握常用表空間，undo 表空間和臨時表空間的建立方法
3. 了解表空間的各個狀态(online, offline, read write, read only)的作用，及如何改變表空間的狀态的方法。
4. 了解移動資料檔案的原因，及使用 alter tablespace 和 alter datatable 指令移動資料檔案的方法。
限制
維護資料的完整性， oracle 中， 資料完整性可以使用限制、 觸發器、 應用程式 (過
程、函數)三種方法來實作，在這三種方法中，因為限制易于維護，并且具有最好的性能，是以作為維護資料完整性的首選。限制包括：not null、 unique， primary key， foreign key，和 check 五種。
not null(非空)
unique(唯一)
primary key(主鍵)
foreign key(外鍵)
check
用于強制行資料必須滿足的條件，假定在 sal 列上定義了 check 限制，并要求 sal 列值在 1000-2000 之間如果不在 1000-2000
之間就會提示出錯。
删除限制
alter table 表名 drop constraint 限制名稱；
在删除主鍵限制的時候，可能有錯誤，比如：
alter table 表名 drop primary key；
這是因為如果在兩張表存在主從關系，那麼在删除主表的主鍵限制時，必須帶上 cascade 選項 如像：
alter table 表名 drop primary key cascade;
索引是用于加速資料存取的資料對象。合理的使用索引可以大大降低 i/o 次數，進而提高資料通路性能。索引有很多種我們主要介紹常用的幾種：
單列索引
create index 索引名 on 表名(列名);
複合索引
複合索引是基于兩列或是多列的索引。在同一張表上可以有多個索引，但是要求列的組合必須不同，比如：
create index emp_idx1 on emp (ename, job);
create index emp_idx1 on emp (job, ename);
使用原則
1. 在大表上建立索引才有意義
2. 在 where 子句或是連接配接條件上經常引用的列上建立索引
3. 索引的層次不要超過 4 層
索引缺點分析
1. 建立索引，系統要占用大約為表 1.2 倍的硬碟和記憶體空間來儲存索引。
2. 更新資料的時候，系統必須要有額外的時間來同時對索引進行更新，以維持資料和索引的一緻性。
實踐表明，不恰當的索引不但于事無補，反而會降低系統性能。因為大量的索引在進行插入、修改和删除操作時比沒有索引花
費更多的系統時間。
比如在如下字段建立索引應該是不恰當的：
1. 很少或從不引用的字段；
2. 邏輯型的字段，如男或女(是或否)等。
pl/sql(procedural language/sql)是 oracle 在标準的 sql 語言上的擴充。pl/sql 不僅允許嵌入 sql 語言，還可以定義變量和常量，允許使用條件語句和循環語句，允許使用例外處理各種錯誤，這樣使得它的功能變得更加強大。
學習必要性
1.提高應用程式的運作性能
2.子產品化的設計思想【分頁的過程，訂單的過程，轉賬的過程。。】
3.減少網絡傳輸量
4.提高安全性(sql 會包括表名，有時還可能有密碼，傳輸的時候會洩露。PL/SQL 就不會)
pl/sql 可以做什麼
簡單分類
|————過程(存儲過程)
|
|————函數
塊(程式設計)—————|
|————觸發器
|
|————包
編寫規範
編寫規範
1.注釋
單行注釋 --
select * from emp where empno=7788; --取得員工資訊
多行注釋 來劃分
2.标志符号的命名規範
1).當定義變量時，建議用 v_作為字首 v_sal
2).當定義常量時，建議用 c_作為字首 c_rate
3).當定義遊标時，建議用_cursor 作為字尾 emp_cursor
4).當定義例外時，建議用 e_作為字首 e_error
塊(block)是 pl/sql 的基本程式單元，編寫 pl/sql 程式實際上就是編寫 pl/sql 塊，要完成相對簡單的應用功能，可能隻需
要編寫一個 pl/sql 塊，但是如果想要實作複雜的功能，可能需要在一個 pl/sql 塊中嵌套其它的 pl/sql 塊。
塊結構示意圖
pl/sql 塊由三個部分構成：定義部分，執行部分，例外處理部分。
如下所示：
declare
begin
exception
end;
定義部分是從 declare 開始的，該部分是可選的；
執行部分是從 begin 開始的，該部分是必須的；
例外處理部分是從 exception 開始的，該部分是可選的。
可以和 java 程式設計結構做一個簡單的比較。
pl/sql 塊的執行個體(1)
執行個體 1-隻包括執行部分的 pl/sql 塊
set serveroutput on --打開輸出選項
begin
dbms_output.put_line('hello');
end;
相關說明：
dbms_output 是 oracle 所提供的包(類似 java 的開發包)，該包包含一些過程，put_line 就是 dbms_output 包的一個過程。
pl/sql 塊的執行個體(2)
執行個體 2-包含定義部分和執行部分的 pl/sql 塊
declare
v_ename varchar2(5); --定義字元串變量
begin
select ename into v_ename from emp where empno=&aa;
dbms_output.put_line('雇員名：'||v_ename);
end;
/
如果要把薪水也顯示出來，那麼執行部分就應該這麼寫：
select ename,sal into v_ename,v_sal from emp where empno=&aa;
相關說明：
& 表示要接收從控制台輸入的變量。
pl/sql 塊的執行個體(3)
執行個體 3-包含定義部分，執行部分和例外處理部分
為了避免 pl/sql 程式的運作錯誤，提高 pl/sql 的健壯性，應該對可能的錯誤進行處理，這個很有必要。
1.比如在執行個體 2 中，如果輸入了不存在的雇員号，應當做例外處理。
2.有時出現異常，希望用另外的邏輯處理，[網示]
我們看看如何完成 1 的要求。
相關說明：
oracle 事先預定義了一些例外，no_data_found 就是找不到資料的例外。
declare
--定義變量
v_ename varchar2(5);
v_sal number(7,2);
begin
--執行部分
select ename,sal into v_ename,v_sal from emp where empno=&aa;
--在控制台顯示使用者名
dbms_output.put_line('使用者名是：'||v_ename||' 工資：'||v_sal);
--異常處理
exception
when no_data_found then
dbms_output.put_line('朋友，你的編号輸入有誤！')；
end;
/
過程，函數，包，觸發器
過程
過程用于執行特定的操作，當建立過程時，既可以指定輸入參數(in)，也可以指定輸出參數(out)， 通過在過程中使用輸入參
數，可以将資料傳遞到執行部分；通過使用輸出參數，可以将執行部分的資料傳遞到應用環境。在 sqlplus 中可以使用 create
procedure 指令來建立過程。
執行個體如下：
1.請考慮編寫一個過程，可以輸入雇員名，新工資，可修改雇員的工資
2.如何調用過程有兩種方法； exec call
3.如何在 java 程式中調用一個存儲過程
問題：如何使用過程傳回值？
特别說明：
對于過程我們會在以後給大家詳細具體的介紹，現在請大家先有一個概念。
create procedure sp_pro3(spName varchar2, newSal number) is
--不要寫成 number(3,2),表明類型就可以了，不需要大小。就好像 Java 寫方法時的參數一樣
begin
--執行部分，根據使用者名去修改工資
update emp set sal=newSal where ename=spName;
end;
/
java 程式中調用一個存儲過程
//示範 java 程式去調用 oracle 的存儲過程案例
import java.sql.*;
public class TestOraclePro{
public static void main(String[] args){
try{
//1.加載驅動
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到連接配接
Connection ct =
DriverManager.getConnection("jdbc:oracle:[email protected]:1521:MYORA1","scott","m123");
//3.建立 CallableStatement
CallableStatement cs = ct.prepareCall("{call sp_pro3(?,?)}");
//4.給?指派
cs.setString(1,"SMITH");
cs.setInt(2,10);
//5.執行
cs.execute();
//關閉
cs.close();
ct.close();
} catch(Exception e){
e.printStackTrace();
}
}
}
函數
函數用于傳回特定的資料，當建立函數時，在函數頭部必須包含 return 子句。而在函數體内必須包含 return 語句傳回的數
據。我們可以使用 create function 來建立函數，實際案例：
--輸入雇員的姓名，傳回該雇員的年薪
create function annual_incomec(name varchar2)
return number is
annual_salazy number(7,2);
begin
--執行部分
select sal*12+nvl(comm, 0) into annual_salazy from emp where ename=name;
return annual_salazy;
end;
/
如果函數建立過程有編譯錯誤，可以使用 show error;指令顯示錯誤
在 sqlplus 中調用函數
SQL> var income number
SQL> call annual_incomec('scott') into: income;
SQL> print income
同樣我們可以在 java 程式中調用該函數
select annual_income('SCOTT') from dual;
這樣可以通過 rs.getInt(l)得到傳回的結果。
包
包用于在邏輯上組合過程和函數，它由包規範和包體兩部分組成。
1.我們可以使用 create package 指令來建立包。
執行個體：
--建立一個包 sp_package
--聲明該包有一個過程 update_sal
--聲明該包有一個函數 annual_income
create package sp_package is
procedure update_sal(name varchar2, newsal number);
function annual_income(name varchar2) return number;
end;
包的規範隻包含了過程和函數的說明，但是沒有過程和函數的實作代碼。包體用于實作包規範中的過程和函數。
2.建立包體可以使用 create package body 指令
--給包 sp_package 實作包體
create or replace package body sp_package is
procedure update_sal(name varchar2, newsal number)
is
begin
update emp set sal = newsal where ename = name;
end;
function annual_income(name varchar2) return number is
annual_salary number;
begin
select sal * 12 + nvl(comm, 0) into annual_salary from emp
where ename = name;
return annual_salary;
end;
end;
/
3.如何調用包的過程或是函數
當調用包的過程或是函數時，在過程和函數前需要帶有包名，如果要通路其它方案的包，還需要在包名前加方案名。
如：
SQL> call sp_package.update_sal('SCOTT', 1500);
特别說明：
包是 pl/sql 中非常重要的部分，我們在使用過程分頁時，将會再次體驗它的威力呵呵。
觸發器
觸發器是指隐含的執行的存儲過程。當定義觸發器時，必須要指定觸發的事件和觸發的操作，常用的觸發事件包括
insert,update,delete語句，而觸發操作實際就是一個 pl/sql 塊。可以使用 create trigger 來建立觸發器。
循環語句 –loop
是 pl/sql 中最簡單的循環語句，這種循環語句以 loop 開頭，以 end loop 結尾，這種循環至少會被執行一次。
案例：現有一張表 users，表結構如下：
使用者 id | 使用者名
|
請編寫一個過程，可以輸入使用者名，并循環添加 10 個使用者到 users 表中，使用者編号從 1 開始增加。
create or replace procedure sp_pro6(spName varchar2) is
--定義 :=表示指派
v_num number:=1;
begin
loop
insert into users values(v_num,spName);
--判斷是否要退出循環
exit when v_num=10;
--自增
v_num:=v_num+1;
end loop;
end;
/
環語句 –while 循環
基本循環至少要執行循環體一次，而對于 while 循環來說，隻有條件為 true 時，才會執行循環體語句，while 循環以
while...loop 開始，以 end loop 結束。
案例：現有一張表 users，表結構如下：
使用者 id 使用者名
問題：請編寫一個過程，可以輸入使用者名，并循環添加 10 個使用者到 users 表中，使用者編号從 11 開始增加。
create or replace procedure sp_pro6(spName varchar2) is
--定義 :=表示指派
v_num number:=11;
begin
while v_num<=20 loop
--執行
insert into users values(v_num,spName);
v_num:=v_num+1;
end loop;
end;
/
循環語句 –for 循環
begin
for i in reverse 1..10 loop
insert into users values (i, 'shunping');
end loop;
end;
我們可以看到控制變量 i，在隐含中就在不停地增加。
順序控制語句 –goto，null
預定義例外 case_not_found
在開發 pl/sql 塊中編寫 case 語句時，如果在 when 子句中沒有包含必須的條件分支，就會觸發 case_not_found 的例外：
create or replace procedure sp_pro6(spno number) is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno = spno;
case
when v_sal < 1000 then
update emp set sal = sal + 100 where empno = spno;
when v_sal < 2000 then
update emp set sal = sal + 200 where empno = spno;
end case;
exception
when case_not_found then
dbms_output.put_line('case 語句沒有與' || v_sal || '相比對的條件');
end;
預定義例外 cursor_already_open
當重新打開已經打開的遊标時，會隐含的觸發例外 cursor_already_open
declare
cursor emp_cursor is select ename, sal from emp;
begin
open emp_cursor;
for emp_record1 in emp_cursor loop
dbms_output.put_line(emp_record1.ename);
end loop;
exception
when cursor_already_open then
dbms_output.put_line('遊标已經打開');
end;
/
預定義例外 dup_val_on_index
在唯一索引所對應的列上插入重複的值時，會隐含的觸發例外 dup_val_on_index 例外
begin
insert into dept values (10, '公關部', '北京');
exception
when dup_val_on_index then
dbms_output.put_line('在 deptno 列上不能出現重複值');
end;
預定義例外 invalid_cursor
當試圖在不合法的遊标上執行操作時，會觸發該例外
例如：試圖從沒有打開的遊标提取資料，或是關閉沒有打開的遊标。則會觸發該例外
declare
cursor emp_cursor is select ename, sal from emp;
emp_record emp_cursor%rowtype;
begin
--open emp_cursor; --打開遊标
fetch emp_cursor into emp_record;
dbms_output.put_line(emp_record.ename);
close emp_cursor;
exception
when invalid_cursor then
dbms_output.put_line('請檢測遊标是否打開');
end;
預定義例外 invalid_number
當輸入的資料有誤時，會觸發該例外
比如：數字 100 寫成了 loo 就會觸發該例外
begin
update emp set sal= sal + 'loo';
exception
when invalid_number then
dbms_output.put_line('輸入的數字不正确');
end;
預定義例外 no_data_found
下面是一個 pl/sql 塊，當執行 select into 沒有傳回行，就會觸發該例外
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp
when ename='&name';
exception
when no_data_found then
dbms_output.put_line('不存在該員工')；
end;
預定義例外 too_many_rows
當執行 select into 語句時，如果傳回超過了一行，則會觸發該例外。
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp;
exception
when too_many_rows then
dbms_output.put_line('傳回了多行');
end;
預義例外 zero_divide
當執行 2/0 語句時，則會觸發該例外。
預定義例外 value_error
當在執行指派操作時，如果變量的長度不足以容納實際資料，則會觸發該例外 value_error，比如：
declare
v_ename varchar2(5);
begin
select ename into v_ename from emp where empno = &no1;
dbms_output.put_line(v_ename);
exception
when value_error then
dbms_output.put_line('變量尺寸不足');
end;
視圖是一個虛拟表，其内容由查詢定義，同真實的表一樣，視圖包含一系列帶有名稱的列和行資料。但是，視圖并不在資料庫
中以存儲的資料值集形式存在。行和列資料來自由定義視圖的查詢所引用的表，并且在引用視圖時動态生成。(視圖不是真實
存在磁盤上的)
看圖：
視與表的差別
視圖與表的差別
1.表需要占用磁盤空間，視圖不需要
2.視圖不能添加索引(是以查詢速度略微慢點)
3.使用視圖可以簡化，複雜查詢
比如：學生選課系統
4.視圖的使用利于提高安全性
比如：不同使用者檢視不同視圖
建立/修改視圖
建立視圖
create view 視圖名 as select 語句 [with read only]
建立或修改視圖
create or replace view 視圖名 as select 語句 [with read only]
删除視圖
drop view 視圖名
當表結構國語複雜，請使用視圖吧！
--建立視圖，把 emp 表的 sal<1000 的雇員映射到該視圖(view)
create view myview as select * from emp where sal<1000;
--為簡化操作，用一個視圖解決 顯示雇員編号，姓名和部門名稱
create view myview2 as select emp.empno,emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno;
視圖之間也可以做聯合查詢
未完。。。。