PL/SQL循序漸進全面學習教程–Oracle

課程 一 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 網管網bitsCN.com
例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
54ne.com
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