課程 一 PL/SQL 基本查詢與排序
本課重點：
1、寫SELECT語句進行資料庫查詢
2、進行數學運算
3、處理空值
4、使用別名ALIASES
5、連接列
6、在SQL PLUS中編輯緩衝，修改SQL SCRIPTS
7、ORDER BY進行排序輸出。
8、使用WHERE 欄位。
一、寫SQL 命令：
不區分大小寫。
SQL 語句用數字分行，在SQL PLUS中被稱為緩衝區。
最後以；或 / 結束語句。
也可以用RUN來執行語句
二、例1：SQL> SELECT dept_id, last_name, manager_id
2 FROM s_emp;
2：SQL> SELECT last_name, salary * 12, commission_pct
2 FROM s_emp;
對於數值或日期型的欄位，可以進行相應的四則運算，優先順序與標準的高階語言相同。
SQL> SELECT last_name, salary, 12 * (salary + 100)
2 FROM s_emp;
三、列的別名ALIASES：
計算的時候特別有用；
緊跟著列名，或在列名與別名之間加“AS”；
如果別名中含有SPACE，特殊字元，或大小寫，要用雙引號引起。
例(因字體原因，讀者請記住：引號為英文雙引號Double Quotation)：
SQL> SELECT last_name, salary,
2 12 * (salary + 100) ”Annual Salary”
3 FROM s_emp;
四、連接符號：||
連接不同的列或連接字串
使結果成為一個有意義的短語：
SQL> SELECT first_name || ’ ’ || last_name
2 || ’, ’|| title ”Employees”
3 FROM s_emp;
五、管理NULL值：
SQL> SELECT last_name, title,
2 salary * NVL(commission_pct,0)/100 COMM
3 FROM s_emp;
此函數使NULL轉化為有意義的一個值，相當於替換NULL。
六、SQL PLUS的基本內容，請參考<SQL PLUS 簡單實用精髓篇 >
七、ORDER BY 操作：
與其他SQL92標準資料庫相似，排序如：
SELECT expr
FROM table
[ORDER BY {column,expr} [ASC|DESC]];
從Oracle7 release 7.0.16開始，ORDER BY 可以用別名。
另：通過位置判斷排序：
SQL> SELECT last_name, salary*12
2 FROM s_emp
3 ORDER BY 2;
這樣就避免了再寫一次很長的運算式。
另：多列排序：
SQL> SELECT last name, dept_id, salary
2 FROM s_emp
3 ORDER BY dept_id, salary DESC;
八、限制選取行：
SELECT expr
FROM table
[WHERE condition(s)]
[ORDER BY expr];
例1：
SQL> SELECT first_name, last_name, start_date
2 FROM s_emp
3 WHERE start_date BETWEEN ’09-may-91’
4 AND ’17-jun-91’;
例2：
SQL> SELECT last_name
2 FROM s_emp
3 WHERE last_name LIKE ’_a%’; //顯示所有第二個字母為 a的last_name
例3：
如果有列為NULL
SQL> SELECT id, name, credit_rating
2 FROM s_customer
3 WHERE sales_rep_id IS NULL;
優先順序：
Order Evaluated Operator
1 All comparison operators (=, <>, >, >=, <, <=, IN, LIKE, IS NULL, BETWEEN)
2 AND
3 OR
總結：我們今天主要學習了如何進行查詢SELECT操作，具體的組合查詢與子查詢將在以後的課堂中
學習，同時希望大家可以工作、學習中多多摸索，實踐!
課程 二 PL/SQL PL/SQL 查詢行函數
本課重點：
1、掌握各種在PL/SQL中可用的ROW函數
2、使用這些函數的基本概念
3、SELECT語句中使用函數
4、使用轉換函數
注意：以下實例中標點均為英文半形
一、FUNCTION的作用：
進行資料計算，修改獨立的資料，處理一組記錄的輸出，不同日期顯示格式，進行資料類型轉換
函數分為：單獨函數(ROW)和分組函數
注意：可以嵌套、可以在SELECT, WHERE, 和 ORDER BY中出現。
語法：function_name (column|expression, [arg1, arg2,...])
二、字元型函數
1、LOWER 轉小寫
2、UPPER
3、INITCAP 首字母大寫
4、CONCAT 連接字元，相當於 ||
5、SUBSTR SUBSTR(column|expression,m[,n])
6、LENGTH 返回字串的長度
7、NVL 轉換空值
其中，1、2經常用來排雜，也就是排除插入值的大小寫混用的幹擾，如：
SQL> SELECT first_name, last_name
2 FROM s_emp
3 WHERE UPPER(last_name) = ’PATEL’;
FIRST_NAME LAST_NAME
-------------------- --------------------
Vikram Patel
Radha Patel
三、數學運算函數
1、ROUND
四捨五入：ROUND(45.923，2) = 45.92
ROUND(45.923，0) = 46
ROUND(45.923，-1) = 50
2、TRUNC
截取函數
TRUNC(45.923，2)= 45.92
TRUNC(45.923)= 45
TRUNC(45.923，-1)= 40
3、MOD 餘除
MOD(1600，300)
實例：
SQL> SELECT ROUND(45.923,2), ROUND(45.923,0),
2 ROUND(45.923,-1)
3 FROM SYS.DUAL;
四、ORACLE 日期格式和日期型函數：
1、默認格式為DD-MON-YY.
2、SYSDATE是一個求系統時間的函數
3、DUAL〔'dju:el] 是一個偽表，有人稱之為空表，但不確切。
SQL> SELECT SYSDATE
2 FROM SYS.DUAL;
4、日期中應用的算術運算符
例：SQL> SELECT last_name, (SYSDATE-start_date)/7 WEEKS
2 FROM s_emp
3 WHERE dept_id = 43;
DATE+ NUMBER = DATE
DATE-DATE= NUMBER OF DAYS
DATE + (NUMBER/24) = 加1小時
5、函數：
MONTHS_BETWEEN(date1, date2) 月份間隔，可正，可負，也可是小數
ADD_MONTHS(date,n) 加上N個月，這是一個整數，但可以為負
NEXT_DAY(date,‘char’) 如：NEXT_DAY (restock_date,’FRIDAY’),從此日起下個週五。
ROUND(date[,‘fmt’])
TRUNC(date[,‘fmt’])
解釋下面的例子：
SQL> SELECT id, start_date,
2 MONTHS_BETWEEN (SYSDATE,start_date) TENURE,
3 ADD_MONTHS(start_date,6) REVIEW
4 FROM s_emp
5 WHERE MONTHS_BETWEEN (SYSDATE,start_date)<48;
我們看到： MONTHS_BETWEEN (SYSDATE,start_date)<48，說明至今工作未滿一年的員工。
LAST_DAY (restock_date) 返回本月的最後一天
SQL> select round(sysdate,'MONTH') from dual
ROUND(SYSD
----------
01-11月-01
round(sysdate,'YEAR') = 01-1月 -02
ROUND 之後的值比基值大的最小符合值，大家可以用更改系統時間的方法測試，以15天為分界線，
也是非常形象的四捨五入，而TRUNC恰好相反，是對現有的日期的截取。
五、轉換函數：
1、TO_CHAR
使一個數位或日期轉換為CHAR
2、TO_NUMBER
把字元轉換為NUMBER
3、TO_DATE
字元轉換為日期
這幾個函數較為簡單，但要多多實踐，多看複雜的實例。
SQL> SELECT ID,TO_CHAR(date_ordered,’MM/YY’) ORDERED
2 FROM s_ord
3 WHERE sales_rep_id = 11;
轉換時，要注意正確的缺省格式：
SELECT TO_DATE('03-MAR-92') CORRECT FROM DUAL；//正確
SELECT TO_DATE('031092') CORRECT FROM DUAL；//不正確
SELECT TO_DATE('031095','MMDDYY') ERRORR FROM DUAL
輸出 3月10日
SELECT TO_DATE('031095','DDMMYY') ERRORR FROM DUAL
輸出 10月3日
4、實例：
select to_char(sysdate,'fmDDSPTH "of" MONTH YYYY AM') TODAYS FROM DUAL;
TODAYS
--------------------------------
SIXTEENTH of 11月 2001 下午
大小寫沒有什麼影響，引號中間的是不參與運算。
實例 ：
SELECT ROUND(SALARY*1.25) FROM ONE_TABLE；
意義：漲25%工資後，去除小數位。在現實操作中，很有意義。
5、混合實例：
SQL> SELECT last_name, TO_CHAR(start_date,
2 ’fmDD ”of” Month YYYY’) HIREDATE
3 FROM s_emp
4 WHERE start_date LIKE ’%91’;
LAST_NAME HIREDATE
------------ --------------------
Nagayama 17 of June 1991
Urguhart 18 of January 1991
Havel 27 of February 1991
這裏要注意：fmDD 和 fmDDSPTH之間的區別。
SQL> SELECT id, total, date_ordered
2 FROM s_ord
3 WHERE date_ordered =
4 TO_DATE(’September 7, 1992’,’Month dd, YYYY’);
六、獨立的函數嵌套
SQL> SELECT CONCAT(UPPER(last_name),
2 SUBSTR(title,3)) ”Vice Presidents”
3 FROM s_emp
4 WHERE title LIKE ’VP%’;
* 嵌套可以進行到任意深度，從內向外計算。
例：
SQL> SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS
2 (date_ordered,6),’FRIDAY’),
3 ’fmDay, Month ddth, YYYY’)
4 ”New 6 Month Review”
5 FROM s_ord
6 ORDER BY date_ordered;
SQL> SELECT last_name,
2 NVL(TO_CHAR(manager_id),’No Manager’)
3 FROM s_emp
4 WHERE manager_id IS NULL;
對於例子，大家重要的理解，並多做測試，並注意英文版和中文版在日期上的區別。
有些教材上的例子，不要盲目的相信其結果，實踐後才有發言權，希望大家能夠在學習的過程中
不要忽略了用，
多想一想為什麼實例要如此設計，在何種情況下應用此實例來解決問題。這樣，我們才真正掌握了知識。
14:41 | 添加評論 | 閱讀評論 (6) | 固定鏈結 | 引用通告 (0) | 寫入日誌 | 電腦與 Internet
PL/Sql循序漸進全面學習教程--Oracle(2)
課程 三 從多個表中提取資料
本課重點：
1、SELECT FROM 多個表，使用等連接或非等連接
2、使用外連接OUTER JOIN
3、使用自連接
注意：以下實例中標點均為英文半形
一、連接的概念：
是指一個從多個表中的資料進行的查詢。連接一般使用表的主鍵和外鍵。
連接類型：
等連接、不等連接、外連接、自連接
二、Cartesian product ：
指的是當JOIN條件被省略或無效時，所有表的行(交叉)都被SELECT出來的現象。
Cartesian product可以產生大量的記錄，除非是你有意如此，否則應該加上某種條件限制。
SQL> SELECT name, last_name
2 FROM s_dept, s_emp;
300 rows selected. 其中一個表12行，一個表25行。
三、簡單連接查詢：
SELECT table.column, table.column...
FROM table1, table2
WHERE table1.column1 = table2.column2;
如：SQL> SELECT s_emp.last_name, s_emp.dept_id,
2 s_dept.name
3 FROM s_emp, s_dept
4 WHERE s_emp.dept_id = s_dept.id;
注意：表首碼的重要性：
SQL> SELECT s_dept.id ”Department ID”,
2 s_region.id ”Region ID”,
3 s_region.name ”Region Name”
4 FROM s_dept, s_region
5 WHERE s_dept.region_id = s_region.id;
在WHERE 段中，如果沒有首碼，兩個表中都有ID欄位，就顯得的模棱兩可，AMBIGUOUS。
這在實際中應該儘量避免。
WHERE 欄位中，還可以有其他的連接條件，如在上例中，加上：
INITCAP(s_dept.last_name) = ’Menchu’;
再如：WHERE s_emp.dept_id = s_dept.id AND s_dept.region_id = s_region.id AND
s_emp.commission_pct > 0;
四、表別名ALIAS：
1、使用別名進行多表查詢 。
2、僅在這個查詢中生效，一旦用了表別名，就不能再用表的原有的名字進行連接。
實例：
SQL> SELECT c.name ”Customer Name”,
2 c.region_id ”Region ID”,
3 r.name ”Region Name”
4 FROM s_customer c, s_region r
5 WHERE c.region_id = r.id;
別名最多可以30個字元，但當然越少越好。最好也能容易識別。
五、非等連接
非等連接一般用在沒有明確的等量關係的兩個表；
最簡單的說：非等連接就是在連接中沒有“=”出現的連接。
SQL> SELECT e.ename, e.job, e.sal, s.grade
2 FROM emp e, salgrade s
3 WHERE e.sal BETWEEN s.losal AND s.hisal;
說明：Create a non-equijoin to evaluate an employee’s salary grade. The salary 必須在另一個
表中最高和最低之間。
其他操作符<= >= 也可以實現，但是BETWEEN是非常簡單實用的。
BETWEEN ....AND是指閉區間的，這點要注意 ，請大家測試。
六、外連接
語法結構：SELECT table.column, table.column
FROM table1, table2
WHERE table1.column = table2.column(+);
實例：
SQL> SELECT e.last_name, e.id, c.name
2 FROM s_emp e, s_customer c
3 WHERE e.id (+) = c.sales_rep_id
4 ORDER BY e.id;
顯示.....，即使有的客戶沒有銷售代表。
* 可以理解為有+號的一邊出現了NULL，也可以做為合法的條件。
外連接的限制：
1、外連接符只能出現在資訊缺少的那邊。
2、在條件中，不能用 IN 或者 OR做連接符。
七、自連接
同一個表中使用連接符進行查詢；
FROM 的後面用同一個表的兩個別名。
實例：
SQL> SELECT worker.last_name||’ works for ’||
2 manager.last_name
3 FROM s_emp worker, s_emp manager
4 WHERE worker.manager_id = manager.id;
意味著：一個員工的經理ID匹配了經理的員工號，但這個像繞口令的連接方式並不常用。
以後我們會見到一種 子查詢：
select last_name from s_emp where salary=(select max(salary) from s_emp)
也可以看作是一種變向的自連接，但通常我們將其
課程 四 組函數
本課重點：
1、瞭解可用的組函數
2、說明每個組函數的使用方法
3、使用GROUP BY
4、通過HAVING來限制返回組
注意：以下實例中標點均為英文半形
一、概念：
組函數是指按每組返回結果的函數。
組函數可以出現在SELECT和HAVING 欄位中。
GROUP BY把SELECT 的結果集分成幾個小組。
HAVING 來限制返回組，對RESULT SET而言。
二、組函數：(#號的函數不做重點)
1、AVG
2、COUNT
3、MAX
4、MIN
5、STDDEV #
6、SUM
7、VARIANCE #
語法：
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
實例1：一個混合實例，說明所有問題：
SQL> SELECT AVG(salary), MAX(salary), MIN(salary),
2 SUM(salary)
3 FROM s_emp
4 WHERE UPPER(title) LIKE ’SALES%’;
AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)
----------- ----------- ----------- -----------
1476 1525 1400 7380
說明：很多函數，我們在講函數的已經向大家介紹過，但在此為何叫分組函數呢，主要是因為它們
可以與GROUP BY來形成對不同組的計算，相當於在很多值中進行挑選。
* MIN MAX函數可以接任何資料類型。
如果是MIN(last_name), MAX(last_name)，返回的是什麼呢？
千萬記住，不是指LAST_NAME的長度，而是指在FIRST字母的前後順序，第一個相同，然後比較第二
個，如：xdopt > cssingkdkdk > adopt > acccc
實例2：
SQL> SELECT COUNT(commission_pct)
2 FROM s_emp
3 WHERE dept_id = 31;
返回所有非空行個數
三、GROUP BY的應用：
先看一個簡單實例：
SQL> SELECT credit_rating, COUNT(*) ”# Cust”
2 FROM s_customer
3 GROUP BY credit_rating;
注意這裏別名的應用，復習一下從前的課程，加了引號後，就可以用特殊字元，但也僅有三個：
#$_，什麼物件的名字都如此。當然空格也是可以的。
複雜實例：
SQL> SELECT title, SUM(salary) PAYROLL
2 FROM s_emp
3 WHERE title NOT LIKE ’VP%’
4 GROUP BY title
5 ORDER BY SUM(salary);
這裏要注意一下幾個CLAUSE的先後次序。
WHERE在這裏主要是做參與分組的記錄的限制。
**另外，如果要選取出來一個不加組函數的列，如上面的TITLE，就要把這個列GROUP BY !否則要
出錯的!信息為：ERROR at line 1:
ORA-00937: not a single-group group function
理論很簡單，如果不GROUP BY TITLE，顯示哪一個呢？這個在試題中經常出現。
結論：不加分組函數修飾的列必定要出現在GROUP BY 裏。
錯誤實例：
SQL> SELECT dept_id, AVG(salary)
2 FROM s_emp
3 WHERE AVG(salary) > 2000
4 GROUP BY dept_id;
WHERE AVG(salary) > 2000
*
ERROR at line 3:
ORA-00934: group function is not allowed here
應在GROUP BY 後面加上HAVING AVG(salary) > 2000;
因為是用來限制組的返回。
多級分組實例：
SQL> SELECT dept_id, title, COUNT(*)
2 FROM s_emp
3 GROUP BY dept_id, title;
就是先按照DEPT_ID分組，當DEPT_ID相同的時候，再按TITLE分組，而COUNT(*)以合成的組計數。
順序對結果有決定性的影響。
總結：本課我們主要學習了分組函數的使用及如何進行分組查詢，我們可以想像一下，SQL SERVER
中有COMPUTE BY，來進行分組總數的計算，但在ORACLE中是沒有的。大家可以建立一個有多個列，多個重
複值的表，然後進行各種分組的演示，用得多了，自然明瞭。
課程 五 子查詢
本課重點：
1、在條件未知的情況下採用嵌套子查詢
2、用子查詢做資料處理
3、子查詢排序
注意：以下實例中標點均為英文半形
一、概述：
子查詢是一種SELECT句式中的高級特性，就是一個SELECT語句作為另一個語句的一個段。我們可以
利用子查詢來在WHERE欄位中引用另一個查詢來攻取值以補充其無法事先預知的子結果。
子查詢可以用在WHERE子句，HAING子句，SELECT或DELETE語句中的FROM 子句。
注意：1、子查詢必須在一對圓括號裏。
2、比較符號：>, =, 或者 IN.
3、子查詢必須出現在操作符的右邊
4、子查詢不能出現在ORDER BY裏 (試題中有時出現找哪行出錯)
二、子查詢的執行過程：
NESTED QUERY MAIN QUERY
SQL> SELECT dept_id SQL> SELECT last_name, title
2 FROM s_emp 2 FROM s_emp
3 WHERE UPPER(last_name)=’BIRI’; 3 WHERE dept_id =
這裏 ，每個查詢只運行一次。當然，子查詢要首先被執行，大家設想一下，如果子查詢中有一個
以上的人的LASTNAME為BIRI，會如何？-----會出錯，因為不能用=來連接。
ORA-1427: single-row subquery returns more than
one row
以上的查詢也被稱之為 單行子查詢。
DELECT子查詢實例：
delete from new_table where cata_time > to_date('19990901','yyyymmdd') and pro_name=(
select pro_name from new_product where pro_addr in ('bj','sh'))
三、子查詢中的GROUP 函數的應用
實例 1：
SQL> SELECT last_name, title, salary
2 FROM s_emp
3 WHERE salary <
4 (SELECT AVG(salary)
5 FROM s_emp);
實例2：
選擇出工資最高的員工的家庭住址：
select emp_addr from employees where salary =
(select max(salary) from employees);
這是一個簡單實用的例子，可以衍生出很多情況，在實際應用經常出現，請大家多多思考。
實例3：
SQL> SELECT dept_id, AVG(salary)
2 FROM s_emp
3 GROUP BY dept_id
4 HAVING AVG(salary) >
5 (SELECT AVG(salary)
6 FROM s_emp
7 WHERE dept_id = 32);
子查詢被多次執行，因為它出現在HAVING 子句中。
SQL> SELECT title, AVG(salary)
2 FROM s_emp
3 GROUP BY title
4 HAVING AVG(salary) =
5 (SELECT MIN(AVG(salary))
6 FROM s_emp
7 GROUP BY title);
對子查詢，我們瞭解這麼多在理論上已經覆蓋了所有的知識點，對於UPDATE 和DELETE的子查詢，
不作為重點，但也要練習掌握。今天到這，謝謝大家。
課程 六 運行時應用變數
本課重點：
1、創建一個SELECT語句，提示USER在運行時先對變數賦值。
2、自動定義一系列變數，在SELECT運行時進行提取。
3、在SQL PLUS中用ACCEPT定義變數
注意：以下實例中標點均為英文半形
一、概述：
變數可以在運行時應用，變數可以出現在WHERE 欄位元，文本串，列名，表名等。
1、我們這裏的運行時，指的是在SQL PLUS中運行。
2、ACCEPT ：讀取用戶輸入的值並賦值給變數
3、DEFINE：創建並賦值給一個變數
4、在做REPORT時經常使用，比如對某個部門的銷售資訊進行統計，部門名稱可以以變數代替。
SQL PLUS不支援對輸入資料的有效性檢查，因此提示要簡單且不模棱兩可。
二、應用實例：
1、SQL> SELECT id, last_name, salary
2 FROM s_emp
3 WHERE dept_id = &department_number;
2、可以在賦值前後進行比較：
SET VERIFY ON
.....
1* select * from emp where lastname='&last_name'
輸入 last_name 的值: adopt
原值 1: select * from emp where lastname='&last_name'
新值 1: select * from emp where lastname='adopt'
----如果在原語句中沒有單引號，那麼在輸入值的時候要手工加上單引號。一般字元和日期型要
在語句中加上單引號。
SET VERIFY OFF 之後，原值和新值這兩句消失。這在ORACLE8I中是默認為ON。
3、子句為變數：WHERE &condition; 要注意引號
三、DEFINE和ACCEPT的應用：
1、SET ECHO OFF //使內容不 顯示在用戶介面
ACCEPT p_dname PROMPT ’Provide the department name: ’
SELECT d.name, r.id, r.name ”REGION NAME”
FROM s_dept d, s_region r
WHERE d.region_id = r.id
AND UPPER(d.name) LIKE UPPER(’%&p_dname%’)
/
SET ECHO ON
存為文件：l7prompt.SQL
SQL> START l7prompt
Provide the department name: sales
2、SQL> DEFINE dname = sales
SQL> DEFINE dname
DEFINE dname = ”sales” (CHAR)
SQL> SELECT name
2 FROM s_dept
3 WHERE lower(name) = ’&dname’;
可以正常執行了。
SQL> DEFINE dname 主要是顯示當前的變數是否賦值，值是什麼。當然，我們可以用
UNDEFINEGO 來使變數恢復初始，不然它會一直保持下去。
3、如果變數在SQL SCRIPT檔中確定 ：可以SQL> START l7param President 來賦值。
總結：本課主要針對較古老的SQLPLUS方法，在REPORT和結果集生成方面使用變數，達到方便操作，
動態修改的目的。
14:39 | 添加評論 | 固定鏈結 | 引用通告 (0) | 寫入日誌 | 電腦與 Internet
PL/Sql循序漸進全面學習教程--Oracle(3)
課程 七 其他資料庫物件
SEQUENCE
創建實例：
SQL> CREATE SEQUENCE s_dept_id
2 INCREMENT BY 1
3 START WITH 51
4 MAXVALUE 9999999
5 NOCACHE
6 NOCYCLE;
Sequence created.
1、NEXTVAL和CURRVAL的用法
只有在INSERT 中，才可以作為子查詢出現。
以下幾個方面不可用子查詢：
SELECT 子句OF A VIEW
有DISTINCT的出現的SELECT。
有GROUP BY，HAVING，ORDER BY的SELECT 子句。
SELECT 或DELETE，UPDATE 中的子查詢。
DEFAULT選項中不能用。
2、編輯SEQUENCE
只有OWNER或有ALTER許可權的用戶才能修改SEQUENCE
未來的NUMBER受修改的影響。
不能修改START WITH，如果變，則要RE－CREATE。
修改會受到某些有效性檢驗的限制，如MAXVALUE
3、刪除：
DROP SEQUENCE sequence;
ORACLE對象之INDEX
一、INDEX概述：
是ORACLE的一種資料物件，用POINTER來加速查詢行。通過快速路徑存取方法定位資料並減少I/O
。 INDEX獨立於表。INDEX由ORACLE SERVER來使用和保持。
二、索引如何建立？
1、自動：通過PRIMARY KEY和UNIQUE KEY約束來建立。
2、用戶手工建立非唯一性索引。
三、創建方法：
語法：CREATE INDEX index
ON table (column[, column]...);
何時建立INDEX：
此列經常被放到WHERE欄位或JOIN來作條件查詢。
此列含有大量的資料。
此列含有大量的空值。
兩個或幾個列經常同時放到WHERE欄位進行組合查詢
表很大而且只有少於2-4% 的ROW可能被查詢的時候。
以下情況不要建立索引：
表很小；
表被更新頻繁。
四、查看已經存在的索引：
1、USER_INDEXES可以查詢索引名和類型。
2、USER_IND_COLUMNS包含索引名、表名、列名。
實例：
SQL> SELECT ic.index_name, ic.column_name,
2 ic.column_position col_pos, ix.uniqueness
3 FROM user_indexes ix, user_ind_columns ic
4 WHERE ic.index_name = ix.index_name
5 AND ic.table_name = ’S_EMP’;
五、刪除索引：
DROP INDEX index;
SYNONYMS 同義詞
語法：
CREATE [PUBLIC] SYNONYM synonym for object;
注意：此物件不能包含在一個包裏；
一個私有的同義詞不能與同一USER的其他物件重名。
DROP SYNONYM D_SUM；
課程 八 用戶訪問控制
本課重點：
1、創建用戶
2、創建角色來進行安全設置
3、使用GRANT或REVOKE 來控制許可權
注意：以下實例中標點均為英文半形
一、概述：
ORACLE通過用戶名和密碼進行許可權控制。
資料庫安全：系統安全和資料安全
系統許可權：使用戶可以訪問資料庫
對象許可權：操縱資料庫中的物件
SCHEMA：各種物件的集合
二、系統許可權：
1、超過80個許可權可用。
2、DBA有最高的系統許可權：
CREATE NEW USER
REMOVE USERS
REMOVE ANY TABLE
BACKUP ANY TABLE
三、創建用戶
1、CREATE USER user IDENTIFIED BY password;
2、系統許可權：CREATE SESSION Connect to the database.
CREATE TABLE Create tables in the user’s schema.
CREATE SEQUENCE Create a sequence in the user’s schema.
CREATE VIEW Create a view in the user’s schema.
CREATE PROCEDURE Create a stored procedure, function, or package in
the user’s schema.
3、授權用戶系統許可權：
GRANT privilege [, privilege...] TO user [, user...];
GRANT CREATE TABLE TO SCOTT；
四、角色的使用
1、概念：角色是一組許可權的命名，可以授予給用戶。這樣就如同給了某個用戶一個許可權包。
2、創建、授予給角色：
CREATE ROLE MANAGER；
GRANT CREATE TABLE，CREATE VIEW TO MANAGER；
GRANT MANAGER TO CLARK
五、修改密碼：
ALTER USER user IDENTIFIED BY password;
六、對象許可權：
1、語句：
GRANT {object_priv(, object_priv...)|ALL}[(columns)]
ON object
TO {user[, user...]|role|PUBLIC}
[WITH GRANT OPTION];
2、實例：
最簡單：
SQL> GRANT select
2 ON s_emp
3 TO sue, rich;
稍複雜：
SQL> GRANT update (name, region_id)
2 ON s_dept
3 TO scott, manager;
SQL> GRANT select, insert
2 ON s_dept
3 TO scott
4 WITH GRANT OPTION;
課程 九 聲明變數
本課重點：
1、瞭解基本的PLSQL塊和區域
2、描述變數在PLSQL中的重要性
3、區別PLSQL與非PLSQL變數
4、聲明變數
5、執行PLSQL塊
注意：以下實例中標點均為英文半形
一、概述：
1、PLSQL 塊結構：
DECLARE --- 可選
變數聲明定義
BEGIN ---- 必選
SQL 和PLSQL 語句
EXCEPTION ---- 可選
錯誤處理
END；---- 必選
二、實例：
declare
vjob varchar(9);
v_count number:=0;
vtotal date:=sysdate +7;
c_tax constant number(3,2):=8.25;
v_valid boolean not null:=true;
begin
select sysdate into vtotal from dual;
end;
/
上例中，如果沒有這個SELECT語句，會如何？
出錯，說明必須有STATEMENTS
如果： select sysdate from dual into vtotal ;
同樣，也不行。而且變數與賦值的類型要匹配。
三、%TYPE的屬性
聲明一個變數使之與資料庫某個列的定義相同或與另一個已經定義過的變數相同
所以%TYPE要作為列名的尾碼：如：
v_last_name s_emp.last_name%TYPE;
v_first_name s_emp.first_name%TYPE; --這樣做的好處是我們不必去知曉此列的類型與定義
或：v_balance NUMBER(7,2);
v_minimum_balance v_balance%TYPE := 10;
四、聲明一個布林類型的變數
1 只有TRUE、FALSE、NULL可以賦值給BOOLEAN變數
2 此變數可以接邏輯運算符NOT、AND、OR。
3、變數只能產生TRUE、FALSE、NULL。
實例：
VSAL1：=50000；
VSQL2：=60000；
VCOMMSAL BOOLEAN：=(VSAL1<VSQL2)；
--其實是把TRUE賦值給此變數。
五、LOB 類型的變數
共有CLOB、BLOB、BFILE、NCLOB幾種，這裏不做為重點。
六：使用HOST VARIABLES
SQL> variable n number
SQL> print n
：n=v_sal /12;
:n這個加了：首碼的變數不是PLSQL變數，而是HOST。
七、以下幾個PLSQL聲明變數，哪個不合法？
A 、DECLARE
V_ID NUMBER(4)；
B、DECLARE
V_X，V_Y，V_Z VARCHAR2(9)；
C、DECLARE
V_BIRTH DATE NOT NULL；
D、DECLARE
V_IN_STOCK BOOLEAN：=1；
E、DECLARE
TYPE NAME_TAB IS TABLE OF VARCHAR2(20)
INDEX BY BINARY_INTEGER；
DEPT_NAME NAME_TAB；
上面的習題我會在下章給出答案，這也正是聲明變數的規則和難點。
課程 十 寫執行語句
本課重點：
1、瞭解PLSQL執行區間的重要性
2、寫執行語句
3、描述嵌套塊的規則
4、執行且測試PLSQL塊
5、使用代碼慣例
注意：以下實例中標點均為英文半形
一、PLSQL 塊的語法規則：
1、語句可以跨躍幾行。
2、辭彙單元可以包括：分隔符號號、識別字、文字、和注釋內容。
3、分隔符號號：
+-*/=<>||....
4、識別字：
最多30個字元，不能有保留字除非用雙引號引起。
字母開頭，不與列同名。
5、文字串：如 V_ENAME:='FANCY';要用單引號括起來。
數值型可以用簡單記數和科學記數法。
6、注釋內容：單行時用-- 多行用/* */
與C很相似
二、SQL函數在PL/SQL的使用：
1、可用的：
單行數值型、字元型和轉換型，日期型。
2、不可用的：
最大、最小、DECODE、分組函數。
實例：
BEGIN
SELECT TO_CHAR(HIREDATE,'MON,DD,YYYY') FROM EMP;
END;
V_comment:=user||':'||sysdate; -- 會編譯出錯
V_comment:=user||':'||to_char(sysdate); --正確
如果有可能，PLSQL都會進行資料一致性的轉換，但ORACLE推薦你應該進行顯示的轉換，因為這樣
會提高性能。
三、嵌套塊和變數作用區域
1、執行語句允許嵌套時嵌套。
2、嵌套塊可以看作正常的語句塊。
3、錯誤處理模組可以包括一個嵌套塊
4、exponential指數 邏輯、算數、連接、小括弧
5、看正面實例：
declare
vjob varchar(9);
v_count number:=0;
vtotal date:=sysdate +7;
c_tax constant number(3,2):=8.25;
v_valid boolean not null:=true;
ttt vtotal%type;
begin
--select sysdate into vtotal from dual;--體會有無此句與結果的影響
dbms_output.put_line (vtotal);
end;
/
注意：在執行塊之前，要在SQL PLUS中執行：SET SERVEROUTPUT ON
三、以實例來說明函數的參數聲明作用域
declare
v_weight number(3):=600;
v_message varchar2(255):='product10000';
begin
declare
--sub-block
v_weight number(3):=1;
v_message varchar2(255):='pro300';
begin
v_weight:=v_weight +1;
end;
v_weight:=v_weight +1;
v_message:=v_message || 'my name';
end;
/
子塊中的V_WEIGHT值為 2
我們可以在子塊中加入：dbms_output.put_line('subblock value is '||v_weight);
在主體中加入：dbms_output.put_line('main value is '||v_weight);
我們發現MAINBLOCK中V_WEIGHT為 601
改動：
1、在主塊的聲明中加 v_date date default sysdate;
在子塊中加入：dbms_output.put_line('subblock date value is '||v_date);
執行結果：subblock date value is 22-11月-01
****說明：主塊中的變數，如果子塊中沒有同名變數聲明，則繼承主塊中的聲明和初始化值；
2、在子塊中加入：v_sub char(9);
dbms_output.put_line('subblock char value is '||v_sub);
此時正常輸出。
在主塊中加入：dbms_output.put_line('main char value is '||v_sub);
輸出：ORA-06550: 第 21 行, 第 45 列:
PLS-00201: 必須說明識別字 'V_SUB'
說明：
子塊中聲明的變數主塊中並不知曉，因此出錯。
瞭解了此實例，一切情況的變數的值的走向就都明瞭了。
課程 十一 與ORACLE SERVER交互
本課重點：
1、在PLSQL中成功的寫SELECT語句
2、動態聲明PLSQL變數類型與SIZE
3、在PLSQL中寫DML語句
4、在PLSQL中控制事務
5、確定DML操作的結果
注意：以下實例中標點均為英文半形
一、PLSQL中的SQL語句：
SELECT、DML、COMMIT、ROLLBACK、SAVEPOINT、CURSOR
特殊強調：PLSQL不支持DCL，不要問為什麼。(DBMS_SQL package allows you to issue DDL and
DCL statements.)
二、SELECT
SELECT select_list
INTO variable_name | record_name
FROM table
WHERE condition;
例：
SQL> r
1 declare
2 v_deptno number(2);
3 v_loc varchar2(15);
4 begin
5 select deptno,loc
6 into v_deptno,v_loc
7 from dept
8 where dname='SALES';
9 DBMS_OUTPUT.PUT_LINE (V_deptno ||' and '||v_loc);
10* end;
30 and CHICAGO
選取欄位與變數個數和類型要一致。聲明的變數一定要在SIZE上大於返回的賦值，否則提示緩衝區
溢出。
如果SELECT語句沒有返回值：ORA-01403: 未找到數據
ORA-06512: 在line 5
如果有多個值返回：ORA-01422: 實際返回的行數超出請求的行數
這些我們到了錯誤處理時會逐一講解。
例：
上面的例子可以改為：
declare
v_deptno dept.deptno%type;
v_loc dept.loc%type;
begin
select deptno,loc
into v_deptno,v_loc
from dept
where dname='SALES';
DBMS_OUTPUT.PUT_LINE (V_deptno ||' and '||v_loc);
end;
/
這樣，可以在未知其他欄位大小和類型的時候定義變數，提高相容性。
三、DML 操作：
1、實例：
declare
v_empno emp.empno%type;
begin
select max(empno)
into v_empno
from emp;
v_empno:=v_empno+1;
insert into emp(empno,ename,job,deptno)
values(v_empno,'asdfasdf','ddddd',10);
end;
/
這樣也可以實現如SEQUENCE一樣的編號唯一遞增。
2、更新和刪除：
這個較為簡單：
DECLARE
V_DEPTNO EMP.DEPtno%type :=10;
begin
delete from emp
where deptno=v_deptno;
end;
/
大家多多實踐即可掌握。
PLSQL首先檢查一個識別字是否是一個資料庫的列名，如果不是，再假定它是一個PLSQL的識別字。所
以如果一個PLSQL的變數名為ID，列中也有個ID，如：
SELECT date_ordered, date_shipped
INTO date_ordered, date_shipped
FROM s_ord
WHERE id = id;
就會返回TOO MANY ROWS，這是要儘量避免的。
四、SQL CURSOR
遊標是一個獨立SQL工作區，有兩種性質的遊標：
隱式遊標： 當PARSE 和EXECUTE 時使用隱式遊標。
顯式遊標： 是由程式師顯式聲明的。
遊標的屬性：
SQL%ROWCOUNT：一個整數值，最近SQL語句影響的行數。
SQL%FOUND BOOLEAN屬性，如果為TRUE，說明最近的SQL STATEMENT有返回值。
SQL%NOTFOUND 與SQL%FOUND相反
SQL%ISOPEN 在隱式遊標中經常是FALSE，因為執行後立即自動關閉了。
SQL> variable row_de number
SQL> r
1 declare
2 v_deptno number:=10;
3 begin
4 delete from emp where
5 deptno=v_deptno;
6 :row_de:=sql%rowcount;
7* end;
PL/SQL 過程已成功完成。
SQL> print row_de --這是一個SQL PLUS變數
ROW_DE
----------
4
這時其實並沒有真正的刪除，而是需要 COMMIT或ROLLBACK，來完成事務。
14:37 | 添加評論 | 固定鏈結 | 引用通告 (0) | 寫入日誌 | 電腦與 Internet
PL/Sql循序漸進全面學習教程--Oracle(4)
課程 十二 編寫控制結構語句
本課重點：
1、結構控制的的用途和類型
2、IF 結構
3、構造和標識不同的迴圈
4、使用邏輯表
5、控制流和嵌套
注意：以下實例中標點均為英文半形
一、控制執行流
可以是分支和迴圈：IF THEN END IF
IF condition THEN
statements;
[ELSIF condition THEN
statements;]
[ELSE
statements;]
END IF;
例子：IF V_ENAME='OSBORNE' THEN
V_MGR:=22;
END IF;
這裏我們可以注意，PLSQL和C語言或JAVA在條件上的不同，=代表關係運算，而：=代表賦值。
看一個函數：
create FUNCTION calc_val
(v_start IN NUMBER)
RETURN NUMBER
IS
BEGIN
IF v_start > 100 THEN
RETURN (2 * v_start);
ELSIF v_start >= 50 THEN
RETURN (.5 * v_start);
ELSE
RETURN (.1 * v_start);
END IF;
END calc_val;
現在，雖然我們尚未講解CREATE 函數或過程，但可以看到IF 條件在其中的作用。
二、注意LOGIC TABLE中的邏輯對應關係
1、NOT、AND、OR
2、任何運算式中含有空值結果都為 NULL
3、連接字串中含有空值會把NULL作為 EMPTY STRING
declare
v_deptno dept.deptno%type;
v_loc dept.loc%type;
V_FLAG BOOLEAN ;
V_REC BOOLEAN :=FALSE; --此值改為TRUE、NULL、FALSE進行不同的比較
V_AVA BOOLEAN:=NULL;
begin
V_FLAG:=V_REC AND V_AVA;
IF V_FLAG=TRUE THEN
DBMS_OUTPUT.PUT_LINE ('TRUE');
ELSIF V_FLAG=FALSE THEN
DBMS_OUTPUT.PUT_LINE ('FALSE');
ELSE
DBMS_OUTPUT.PUT_LINE ('NULL');
END IF;
end;
/
值得注意的是：NULL AND FALSE ---> FALSE
這是在實踐中總結出來的。
三、基本迴圈基礎：
1、LOOP
statement1;
statement2;
. . .
EXIT [WHEN condition];
END LOOP;
v_ord_id s_item.ord_id%TYPE := 101;
v_counter NUMBER (2) := 1;
BEGIN
. . .
LOOP
INSERT INTO s_item (ord_id, item_id)
VALUES (v_ord_id, v_counter);
v_counter := v_counter + 1;
EXIT WHEN v_counter > 10;
END LOOP;
2、FOR迴圈：
FOR index IN [REVERSE] lower_bound..upper_bound LOOP
statement1;
statement2;
. . .
END LOOP;
實例：DECLARE
V_LOWER NUMBER:=1;
V_UPPER NUMBER:=23;
BEGIN
DBMS_OUTPUT.PUT_LINE('');
FOR I IN V_LOWER..V_UPPER LOOP
DBMS_OUTPUT.PUT_LINE(I);
END LOOP;
END;
/
3、WHILE 迴圈：
WHILE condition LOOP
statement1;
statement2;
. . .
END LOOP;
4、迴圈是可以多層嵌套的。可以用<<LABEL>>做迴圈的標籤。
...
BEGIN
<<Outer–loop>>LOOP
v_counter :=v_counter+1;
EXIT WHEN v_counter>10;
<<Inner–loop>>LOOP
...
EXIT Outer_loop WHEN total_done = ’YES’;
–– Leave both loops
EXIT WHEN inner_done = ’YES’;
–– Leave inner loop only
...
END LOOP Inner_Loop;
...
END LOOP Outer_loop;
END;
總結：本章內容較為繁雜，雖然不是很難，而且多數與其他高階語言有某種共性，但大家要多多練習
，用實踐來檢驗對某些含糊的猜測。
課程 十三 使用組合資料類型* 遊標操縱資料
本課重點：
1、創建用戶自定義的PLSQL記錄
2、利用%ROWTYPE屬性來創建記錄
3、創建PLSQL表
4、描述記錄、表、記錄的表之間的區別
注意：以下實例中標點均為英文半形
一、合成資料類型
1、類型分為PLSQL記錄和PLSQL表
2、包含內部元件
3、可重用
二、PLSQL記錄
與3GL中的記錄結構相似
與資料庫表是兩回事
是一個方便的途徑FETCH一些行FROM一個表來進行相關處理。
標準語法格式我們暫不介紹，因為每本書上均有。
看例子：
declare
vjob varchar(9);
v_count number:=0;
vtotal date:=sysdate +7;
c_tax constant number(3,2):=8.25;
v_valid boolean not null:=true;
ttt vtotal%type;
type emp_record_type is record
(empno number not null:=100,
ename emp.ename%type,
job emp.job%type);
emp_record emp_record_type;
begin
--select sysdate into vtotal from dual;--體會有無此句與結果的影響
dbms_output.put_line (vtotal);
end;
/
主要看TYPE RECORD出現的位置。每一個例子都是可以成功執行的。
我們也可以利用原有的表結構：
DECLARE
EMP_RECORD EMP%ROWTYPE；
遊標操縱資料
PLSQL遊標提供了一種從資料庫提取多行資料，然後對每行資料進行單獨處理的方法。
一、兩種遊標：
顯式遊標
隱式遊標
二、顯式遊標：操縱步驟如下：聲明遊標、打開遊標、從遊標中取回資料、關閉遊標
三、聲明遊標：
DECLARE CURSOR_NAME
IS
SELECT STATMENT
能夠控制遊標的，唯一參數是INIT.ORA中的OPEN_CURSORS，我原來以為是用戶端最多可以打開多
少個遊標，但有本書上講這是用於管理遊標的記憶體的數量。
DECLARE
CURSOR C_NAME
IS
SELECT ENAME FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT
WHERE CITY_ID=‘BJ’)
--- 說明遊標可以用子查詢
四、打開遊標
OPEN CURSOR_NAME；
這時遊標將它的指標指向活動集的開始，指標指向第一條記錄的前面是因為它還沒有執行FETCH命令
。如果試圖打開一個已經打開的遊標，將出錯：
ORA-06511：PL/SQL：CURSOR ALREADY OPEN
我們可以這樣：
IF NOT C_NAME%ISOPEN
THEN
OPEN C_NAME；
END IF；
五、從遊標中取回資料
FETCH CURSOR_NAME INTO RECOR-LIST；
關閉遊標：CLOSE CURSOR_NAME
六、實例：
DECLARE
myname varchar2(22);
CURSOR C_NAME
IS
SELECT ENAME FROM EMP;
begin
IF NOT C_NAME%ISOPEN
THEN
OPEN C_NAME;
end if;
LOOP
FETCH c_name into myname;
dbms_output.put_line (myname);
exit when c_name%notfound;
end loop;
close c_name;
end;
/--我們將對以上程式進行變形，形成複雜的游標利用。
DECLARE
myname varchar2(22);
thisdeptno scott.emp.deptno%type;
CURSOR C_NAME
IS
SELECT ENAME,deptno FROM EMP order by deptno desc;
begin
IF NOT C_NAME%ISOPEN
THEN
OPEN C_NAME;
end if;
LOOP
FETCH c_name into myname,thisdeptno;
dbms_output.put_line (myname||','||thisdeptno || ',' || to_char(c_name%rowcount));
exit when c_name%notfound;
end loop;
dbms_output.put_line ('the Total record is fetched is ' || to_char(c_name%rowcount));
close c_name;
end;
/
我們增加變數，進行用了排序，使用了遊標屬性，大家看結果發生的變化，想想為什麼。
實例精華!!!： DECLARE
myname varchar2(22);
ii number;
thisdeptno scott.emp.deptno%type;
CURSOR C_NAME
IS
SELECT * FROM EMP order by deptno desc;
emp_record c_name%rowtype;
begin
ii:=1;
for emp_record in c_name loop
dbms_output.put_line(ii);
ii:=ii+1;
end loop;
end;
/
--這裏使用了遊標FOR迴圈，在FOR迴圈的開始，進行、和END LOOP，分別隱式進行了遊標的打開、FETCH
和CLOSE。
我們甚至可以不聲明遊標：FOR emp_record in (SELECT * FROM DEPT) loop
這種技術被稱為顯式遊標的自動化。
在上面，我們可以將一個表的所有欄位輸出,如我們將PUT_LINE的II改為emp_record.ename，就可以
輸出一個欄位內容。
這種方式非常簡單而且效率較高。
----------------
為了測試遊標屬性的重要性，我們做一個以下的過程：
create or replace PROCEDURE change_salary
(v_emp_id IN NUMBER, -- formal parameters
v_new_salary IN NUMBER)
IS
BEGIN -- begin PL/SQL block
UPDATE emp
SET sal = v_new_salary
WHERE empno = v_emp_id;
COMMIT;
END change_salary;
/
這樣，我們在匿名塊中，
UPDATE DEPT
SET DNAME='MY DEPT' WHERE ....;
IF SQL%FOUND THEN
COMMIT;
ELSE
change_salary(7369,9000);
END IF;
我們看到我們通過流程控制了不同的執行結果，對於過程，我們可以用以下幾種方法調用：
在SQLPLUS中：CALL change_salary(7369,9000);
EXECUTE change_salary(7369,9000);
在一個塊中，如：
begin
change_salary(7369,9000);
end;
14:35 | 添加評論 | 固定鏈結 | 引用通告 (0) | 寫入日誌 | 電腦與 Internet
PL/Sql循序漸進全面學習教程--Oracle(5)
最後一課 異常處理本章重點：
1、定義PLSQL異常
2、列舉不同的異常處理方法
3、捕獲非預期的錯誤
4、描述異常的影響
5、定制異常的返回資訊 一、PLSQL異常處理
異常是由ORACLE錯誤或顯式的拋出一個錯誤產生的。
如何處理：
用一個處理程式來捕獲它；
將它傳遞給CALLING ENVIRONMENT
二、異常的類型：
ORACLE SERVER 預定義錯誤
非ORACLE SERVER 預定義錯誤，但也是ORACLE SERVER 的標準錯誤
用戶自定義異常
三、捕捉異常的要點：
Place the WHEN OTHERS clause after all other exception handling clauses.
You can have at most one WHEN OTHERS clause.
Begin exception-handling section of the block with the keyword EXCEPTION.
Define several exception handlers, each with their own set of actions, for the
block.
When an exception occurs, PL/SQL will process only one handler before leaving
the block.
EXCEPTION
WHEN exception1 [OR exception2 . . .] THEN
statement1;
四、常用錯誤：
NO_DATA_FOUND ORA-01403
TOO_MANY_ROWS ORA-01422
INVALID_CURSOR ORA-01001
ZERO_DIVIDE ORA-01476
DUP_VAL_ON_INDEX ORA-00001
五、實例
PROCEDURE elim_inventory
(v_product_id IN s_product.id%TYPE) IS
v_id s_product.id%TYPE;
BEGIN
SELECT id
INTO v_id
FROM s_product
WHERE id = v_product_id;
DELETE FROM s_inventory
WHERE product_id = v_product_id;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
TEXT_IO.PUT_LINE(TO_CHAR(v_product_id)||
’ is invalid.’);
WHEN TOO_MANY_ROWS THEN
ROLLBACK;
TEXT_IO.PUT_LINE(’Data corruption in S_PRODUCT.’);
WHEN OTHERS THEN
ROLLBACK;
TEXT_IO.PUT_LINE(’Other error occurred.’);
END elim_inventory;
在SCOTT環境中使用要稍加改動
六、使用non-predefined Oracle7 Server error
DECLARE
E_PRO EXCEPTION;
PRAGMA EXCEPTION_INIT(E_PRO,ERROR_NUMBER);
BEGIN
......
EXCEPTION
WHEN E_PRO THEN
DBMS_OUTPUT.PUT_LINE('ASDLKFJKASDJFASJDFLKASDF');
......
END;
七、用戶自定義
exception EXCEPTION;
RAISE exception; EXCEPTION
WHEN E_PRO THEN
DBMS_OUTPUT.PUT_LINE('ASDLKFJKASDJFASJDFLKASDF');
......
END; 這裏，只有用戶自定義異常是要顯式聲明的，其他兩個不用。
在SUN OS5.8中，進行SVRMGRL> OERR ORA 01840 可返回資訊
或查錯誤代碼：
HTTP://TECHNET.ORACLE.COM/DOC/SERVER.815/A67785/E1500.HTM
題外話，create public database link otlink connect to system identified
by manager using 'oratest';
說回來，以下兩個函數：
SQLCODE ----Returns the numeric value for the error code. You can assign it
to a NUMBER variable.
SQLERRM ----Returns character data containing the message associated with
the error number.
一般這樣，
EXCEPTION
... WHEN OTHERS THEN
ROLLBACK;
v_error_code:=SQLCODE;
V_ERROR_MESSAGE:=SQLERRM;
INSERT INTO ........
END;
八、調用週邊環境
SQLPLUS
PROCEDURE BUILDER
DEVELOPER 2000
OTHER .........
---- 也就是把ERROR NUMBER和MESSAGE輸出到SCREEN。
九、使用RAISE_APPLICATION_ERROR
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20201,'NO MATCH RECORD YOU WANNA');
END;
當然，RAISE_APPLICATION_ERRO也是可以放在EXECUTE區的，IF 。。。 THEN
RAISE_APPLICATION_ERRO()；
END IF；
這也是很好理解
有效創建oracle dblink 的兩種方式
1、已經配置本地服務
以下是引用片段：
create public database
link fwq12 connect to fzept
identified by neu using 'fjept'
CREATE DATABASE LINK資料庫鏈結名CONNECT TO 用戶名 IDENTIFIED BY 密碼 USING ‘本地配置的
資料的實例名’;
2、未配置本地服務
以下是引用片段：
create database link linkfwq
connect to fzept identified by neu
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.142.202.12)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = fjept)
)
)';
host=資料庫的ip位址，service_name=資料庫的ssid。
其實兩種方法配置dblink是差不多的，我個人感覺還是第二種方法比較好，這樣不受本地服務的影響
。
資料庫連接字串可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA裏定義.
資料庫參數global_name=true時要求資料庫鏈結名稱跟遠端資料庫名稱一樣
資料庫全局名稱可以用以下命令查出
SELECT * FROM GLOBAL_NAME;
查詢遠端資料庫裏的表
SELECT …… FROM 表名@資料庫鏈結名;
查詢、刪除和插入資料和操作本地的資料庫是一樣的，只不過表名需要寫成“表名@dblink伺服器”
而已。
附帶說下同義詞創建:
CREATE SYNONYM同義詞名FOR 表名;
CREATE SYNONYM同義詞名FOR 表名@資料庫鏈結名;
刪除dblink：DROP PUBLIC DATABASE LINK linkfwq。
如果創建全局dblink，必須使用systm或sys用戶，在database前加public
2159
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
