（韓順平講解）pl/sql編程（二），順平pl

（韓順平講解）pl/sql編程（二），順平pl
一、pl/sql進階—控制結構
pl/sql中提供了三種條件分支語句 if -- then, if --then---else, if---then---elsif---else
(1)簡單的條件判斷 if – then
問題：編寫一個過程，可以輸入一個僱員名，如果該僱員的工資低於2000，就給該員工工資增加10%。
Sql代碼
1. create or replace procedure sp_pro6(spName varchar2) is
2. --定義
3. v_sal emp.sal%type;
4. begin
5. --執行
6. select sal into v_sal from emp where ename=spName;
7. --判斷
8. if v_sal<2000 then
9. update emp set sal=sal+sal*10% where ename=spName;
10. end if;
11. end;

(2)二重條件分支 if—then--else
問題：編寫一個過程，可以輸入一個僱員名，如果該僱員的補助不是0就在原來的基礎上增加100；如果補助為0就把補助設為200；
Sql代碼
1. create or replace procedure sp_pro6(spName varchar2) is
2. --定義
3. v_comm emp.comm%type;
4. begin
5. --執行
6. select comm into v_comm from emp where ename=spName;
7. --判斷
8. if v_comm<>0 then
9. update emp set comm=comm+100 where ename=spName;
10. else
11. update emp set comm=comm+200 where ename=spName;
12. end if;
13. end;

(3)多重條件分支 if--then--elsif--else
問題：編寫一個過程，可以輸入一個僱員編號，如果該僱員的職位是PRESIDENT就給他的工資增加1000，如果該僱員的職位是MANAGER就給他的工資增加500，其它職位的僱員工資增加200。
Sql代碼
1. create or replace procedure sp_pro6(spNo number) is
2. --定義
3. v_job emp.job%type;
4. begin
5. --執行
6. select job into v_job from emp where empno=spNo;
7. if v_job='PRESIDENT' then
8. update emp set sal=sal+1000 where empno=spNo;
9. elsif v_job='MANAGER' then
10. update emp set sal=sal+500 where empno=spNo;
11. else
12. update emp set sal=sal+200 where empno=spNo;
13. end if;
14. end;

二、迴圈語句 –while迴圈
基本迴圈至少要執行迴圈體一次，而對於while迴圈來說，只有條件為true時，才會執行迴圈體語句，while迴圈以while...loop開始，以end loop結束。
問題：請編寫一個過程，可以輸入使用者名稱，並迴圈添加10個使用者到users表中，使用者編號從11開始增加。
Sql代碼
1. create or replace procedure sp_pro6(spName varchar2) is
2. --定義 :=表示賦值
3. v_num number:=11;
4. begin
5. while v_num<=20 loop
6. --執行
7. insert into users values(v_num,spName);
8. v_num:=v_num+1;
9. end loop;
10. end;

三、順序控制語句-goto ,null
①goto語句
goto語句用於跳轉到特定標號去執行語句.注意由於使用goto語句會增加程式的複雜性，並使得應用程式可以讀性變差，所以在做一般應用開發時，建議大家不要使用goto語句.
基本文法如下 goto lable,其中lable是已經定義好的標號名.
declare
i int :=1;
begin
loop
dbms_output.put_line(‘輸出i=‘||i);
if i=10 then
goto end_loop;
end if;
i:=i+1;
end loop;
<<end_loop>>
dbms_output.put_line('迴圈結束');
end;
注意：--in:表示這是一個輸入參數，預設為in
--out:表示一個輸出參數

四、oracle視圖
建立視圖
create view 視圖名 as select語句 [with read only]
建立或修改視圖
create or replace view 視圖名 as select語句 [with read only]
刪除視圖
drop view 視圖名

五、pl/sql進階—例外處理
Exception
When ＜異常情況名＞ｔｈｅｎ
＜異常處理代碼＞
When ＜異常情況名＞ｔｈｅｎ
＜異常處理代碼＞
……
ｗｈｅｎ ｏｔｈｅｒｓ ｔｈｅｎ
＜異常處理代碼＞
例外情況名 錯誤碼 描述
NO_DATA_FOUND ORA-01403 對於SELECT 敘述沒有傳回任何值。
TOO_MANY_ROWS ORA-01427 只允許傳回一筆記錄的SELECT 敘述結果卻多於一筆。
INVALID_CURSOR ORA-01001 使用非法的的游標操作。
VALUE_ERROR ORA-06502 出現數值、資料形態轉換、擷取字串或強制性的錯誤。
INVALID_NUMBER ORA-01722 字串到數值的轉換失敗。
ZERO_DIVIDE ORA-01476 被零除。
DUP_VAL_ON_INDEX ORA-00001 試圖向具有唯一鍵值的索引中插入一個重複鍵值。
CASE_NOT_FOUND ORA-xxxxx 沒有case條件匹配

CURSOR_NOT_OPEN ORA-xxxxxx 遊標沒有開啟

各種案例：
1.請寫一個過程，可以向book表添加書，要求通過java程式調用該過程。
--編寫過程
Sql代碼
create or replace procedure sp_pro7(spBookId in number,spbookName in varchar2,sppublishHouse in varchar2) is
begin
insert into book values(spBookId,spbookName,sppublishHouse);
end;
--在java中調用

package com.oracle.demo;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;public class Procedure_07_Test {public static void main(String[] args) {try {Class.forName("oracle.jdbc.driver.OracleDriver");Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "0108");CallableStatement cs = conn.prepareCall("{call sp_pro7(?,?,?)}");//給？賦值cs.setInt(1, 10);cs.setString(2, "天龍八部");cs.setString(3, "清華大學出版社");//執行cs.execute();cs.close();conn.close();} catch (Exception e) {e.printStackTrace();}}}

2.案例：編寫一個過程，可以輸入僱員的編號，返回該僱員的姓名。
案例擴張：編寫一個過程，可以輸入僱員的編號，返回該僱員的姓名、工資和崗位。
Sql代碼
--有輸入和輸出的預存程序
create or replace procedure sp_pro8
(spno in number, spName out varchar2) is
begin
select ename into spName from emp where empno=spno;
end
--在java中調用

package com.oracle.demo;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;public class Procedure_08_Test {public static void main(String[] args) {try {Class.forName("oracle.jdbc.driver.OracleDriver");Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "0108");CallableStatement cs = conn.prepareCall("{call sp_pro8(?,?)}");//給第一個？賦值cs.setInt(1, 7788);//給第二個？賦值   cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);//執行   cs.execute();//取出傳回值,要注意？的順序String name = cs.getString(2);System.out.println(name);cs.close();conn.close();} catch (Exception e) {e.printStackTrace();}}}

3.案例：有返回值的預存程序（列表[結果集]）。編寫一個過程，輸入部門號，返回該部門所有僱員資訊。
由於oracle預存程序沒有返回值，它的所有返回值都是通過out參數來替代的，列表同樣也不例外，
但由於是集合，所以不能用一般的參數，必須要用pagkage了,步驟如下：
①建一個包。
②建立預存程序。
③下面看看如何在java程式中調用
1.建立一個包，在該包中，我定義類型test_cursor,是個遊標。 如下：
Sql代碼
create or replace package testpackage as
TYPE test_cursor is ref cursor;
end testpackage;
2.建立預存程序。如下：
Sql代碼
create or replace procedure sp_pro9(spNo in number,p_cursor out testpackage.test_cursor) is
begin
open p_cursor for
select * from emp where deptno = spNo;
end sp_pro9;
3.如何在java程式中調用該過程

package com.oracle.demo;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;public class Procedure_09_Test {public static void main(String[] args) {try {Class.forName("oracle.jdbc.driver.OracleDriver");Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott", "0108");CallableStatement cs = ct.prepareCall("{call sp_pro9(?,?)}");cs.setInt(1, 10);cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);cs.execute();ResultSet set = (ResultSet) cs.getObject(2);while (set.next()) {System.out.println(set.getInt(1)+ " " + set.getString(2));}cs.close();ct.close();} catch (Exception e) {e.printStackTrace();}}}

4.編寫分頁過程
Sql代碼
select t1.*, rownum rn from (select * from emp) t1 where rownum<=10;
select * from (select t1.*, rownum rn from (select * from emp) t1 where rownum<=10) where rn>=6;
--開發一個包
--建立一個包，在該包中，我定義類型test_cursor,是個遊標。 如下：
Sql代碼
create or replace package testpackage as
TYPE test_cursor is ref cursor;
end testpackage;
--開始編寫分頁的過程
create or replace procedure fenye
(tableName in varchar2,
pagesize in number,--一頁顯示記錄數
pageNow in number,
myrows out number,--總記錄數
myPageCount out number,--總頁數
p_cursor out testpackage.test_cursor--返回的記錄集
) is
--定義部分
--定義sql語句 字串
v_sql varchar2(1000);
--定義兩個整數
v_begin number:=(pageNow-1)*pagesize+1;
v_end number:=pageNow*pagesize;
begin
--執行部分
v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin;
--把遊標和sql關聯
open p_cursor for v_sql;
--計算myrows和myPageCount
--組織一個sql語句
v_sql:='select count(*) from '||tableName;
--執行sql,並把返回的值，賦給myrows；
execute immediate v_sql into myrows;
--計算myPageCount
if mod(myrows,pagesize)=0 then
myPageCount:=myrows/pagesize;
else
myPageCount:=myrows/pagesize+1;
end if;
--關閉遊標
close p_cursor;
end;
--使用java測試

package com.oracle.demo;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;public class Procedure_fenye_Test {public static void main(String[] args) {try {Class.forName("oracle.jdbc.driver.OracleDriver");Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott", "0108");CallableStatement cs = ct.prepareCall("{call fenye(?,?,?,?,?,?)}");cs.setString(1, "emp");cs.setInt(2, 5);cs.setInt(3, 2);//註冊總記錄數 cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);//註冊總頁數 cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);//註冊返回的結果集cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);cs.execute(); //取出總記錄數   /這裡要注意，getInt(4)中4，是由該參數的位置決定的 int rowNum = cs.getInt(4);int pageCount = cs.getInt(5);ResultSet rs = (ResultSet) cs.getObject(6);System.out.println("rowNum:" + rowNum);System.out.println("pageCount:" + pageCount);while(rs.next()){System.out.println("編號："+rs.getInt(1)+" 名字："+rs.getString(2)+" 工資："+rs.getFloat(6));   }cs.cancel();ct.close();} catch (Exception e) {e.printStackTrace();}}}

控制台輸出：

rowNum:14
pageCount:3
編號：7698 名字：BLAKE 工資：2850.0
編號：7782 名字：CLARK 工資：2450.0
編號：7788 名字：SCOTT 工資：4678.0
編號：7839 名字：KING 工資：6000.0
編號：7844 名字：TURNER 工資：1500.0

這裡注意下，在建立過程時，遊標不能關閉，否則java測試是報異常，提示找不到遊標。所以，close p_cursor;這句在執行sql語句時應該注釋掉。

--新的需要，要求按照薪水從低到高排序，然後取出6-10
過程的執行部分做下改動，如下：
Sql代碼
begin
--執行部分
v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||' order by sal) t1 where rownum<='||v_end||') where rn>='||v_begin;
重新執行一次procedure,java不用改變，運行，控制台輸出：
rowNum:14
pageCount:3
編號：7934 名字：MILLER 工資：1300.0
編號：7844 名字：TURNER 工資：1500.0
編號：7499 名字：ALLEN 工資：1600.0
編號：7782 名字：CLARK 工資：2450.0
編號：7698 名字：BLAKE 工資：2850.0

oracle中的PL/SQL編程兩道簡單的題目?;

第一題
SET SERVEROUT ON
DECLARE
V_FLAG BOOLEAN;
BEGIN
FOR I IN 2 .. 100 LOOP
V_FLAG := TRUE;
FOR J IN 2 .. I - 1 LOOP
IF MOD(I,J) = 0 THEN
V_FLAG := FALSE;
END IF;
END LOOP;
IF V_FLAG = TRUE THEN
DBMS_OUTPUT.PUT_LINE(I);
END IF;
END LOOP;
END;
/

第二個，你要輸入08年2月29日怎麼辦？5年前還沒有呢
 
韓順平oracle 中pl sql 是哪個版本

檢查一下oracle是執行個體狀態是否是open ，如果不是open狀態，比如nomount及mount狀態，普通使用者串連時，會提示 “Oracle初始化或關閉過程中”