1.上節回顧
2.PL/SQL的進階
3.oracle的視圖
4.oracle的觸發器
目標：
1.掌握PL/SQL的高級用法（能編寫分頁過程模塊，下訂單過程模塊。。。）
2.會處理oracle常見的例外
3.會編寫oracle各種觸發器
4.理解視圖的概念並能靈活使用視圖
任何計算機語言都有各種控制語句，在PL/SQL中也存在這樣的控制結構
在本部分學習完畢後，希望大家達到：
1）使用各種if
2）使用循環結構
3）使用控制語句 goto 和 null;
條件分支語句：
if then end if
if then else end if
if then elsif else end if
簡單的條件判斷：
案例：編寫一個過程，可以輸入一個雇員名，如果該雇員的工資低於
2000，就給該雇員工資增加10%
create or replace procedure sp_pro6(spName varchar2) is
--定義
v_sal emp.sal%type;
begin
--執行
select sal into v_sal from emp where ename=spName;
--判斷
if v_sal < 2000 then
update emp set sal=sal+sal*10 where ename = spName;
end if;
end;
/
調用：
exec sp_pro6('scott');
二重條件分支：if - then - else
編寫一個過程，可以輸入一個雇員名，如果該雇員的補助不是0，則工資添加100
create or replace procedure sp_pro6(spName varchar2) is
--定義
v_comm emp.comm%type;
begin
--執行
select comm into v_comm from emp where ename=spName;
--判斷
if v_comm <> 0 then
update emp set comm=comm+100 where ename = spName;
else
update emp set comm=comm+200 where ename = spName;
end if;
end;
/
--編寫過程，給不同職位的員工添加不同的工資
create or replace procedure sp_pro6(spNo number) is
v_job emp.job%type;
begin
select job into v_job from emp where empno=spNo;
if v_job = 'PRESIDENT' then
update ...
elsif
update ...
else
update ...
end if;
end;
循環語句 -loop
PL/SQL中的循環語句，最簡單的循環語句是loop語句
--編寫過程，可以輸入用戶名，並循環添加10個用戶到user表中，
用戶編號從1開始增加
create or replace procedure sp_pro6(spName varchar2) is
v_num number := 1;
begin
loop
insert into users values(v_num,spName);
--判斷是否要退出循環
exit then v_num=10; --等於10就要退出循環
--自增
v_num := v_num+1;
end loop;
end;
exec sp_pro6('你好')；
循環語句：while循環
--編寫過程，可輸入用戶名，並循環添加10個用戶到users表中，
用戶編號從11開始增加
while v_num <= 20 loop
insert into user values(v_num,spName);
v_num:=v_num+1;
end loop;
end;
循環語句：for循環
基本for循環的基本結構如下：
begin
for i in reverse 1 .. 10 loop
insert into user values(i,'世陽')
end loop;
end;
goto語句和null語句
goto end_loop;
<<end_loop>>
goto案例：
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;
/
if
...
else
null;--表示什麼都不做
分頁過程:
無返回值的存儲過程
create table book(bookId number,bookName varchar2(50),publishHouse varchar2(50));
--編寫過程：
--in代表這是一個輸入參數，默認為in
--out:表示一個輸出參數
create or replace sp_pro7(spBookId in number，spbookName in varchar2,sppublishHouse in varchar2) is
begin
insert into book values(spBookId,spbookName,sppublishHouse);
end;
編寫java程序調用無返回值的過程
//1.加載驅動
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection ct=DriverManager.getConnection("jdbc:oracle:[email protected]:1521","scott","tiger");
//2.創建CallableStatement
CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}");
//給？賦值
cs.setInt(1,10);
cs.setString(2,"笑傲江湖");
cs.setString(3,"人民出版社");
//執行
cs.execute();
編寫有返回值的存儲過程（非列表）
create or replace procedure sp_pro8
(spno in number,spName out varchar2，spSal out number,spJob out varchar2)is
begin
select ename, sal, job into spName, spSal, spJob from emp where empno=spno;
end;
java如何獲取有返回的存儲過程的數據
//
callableStatement cs = ct.prepareCall("{call sp_pro8(?,?,?,?)}");
//給第一個？賦值
cs.setInt(1,7788);
//給第二個？賦值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);
//執行
cs.execute();
//區返回值要注意？的順序
string name=cs.getString(2);
double sal =cs.getDouble(3);
string job=cs.getString(4);
編寫一個過程，輸入部門號，返回該部門所有雇員信息。此時用一般的參數是不可以的，需要使用package了，所以要分為兩部分
(1)創建一個包，如下：
--返回結果集的過程
--創建一個包，包中定義了一個游標，類型test_cursor
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
（2）創建存儲過程
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;
java程序調用：
//創建CallableStatement
CallableStatement cs=ct.prepareCall("{call sp_pro9(?,?)}");
//給？賦值
cs.SetInt(1,10);
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
//執行
cs.execute();
ResultSet rs = (ResultSet)cs.getObject(2);
while(rs.next())
{
System.out.println(rs.getInt(1)+" "+ra.getString(2));
}
編寫分頁過程
輸入表名，每頁顯示記錄數，當前頁，返回總記錄數，總頁數
--oracle的分頁：
select t1.*,rownum rn from(select * from emp) t1 where rownum<=10;
--在分頁時，大家可以把下面的sql語句當做一個模板使用
select * from (select t1.*,rownum rn from(select * from emp order by sal) t1 where rownum<=10) where rn>=6;
--開發一個包
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(1024);
--定義兩個整數
v_begin number := (pageNow-1)*pagesize+1;
v_end number := pageNow*pagesize;
begin
--執行部分
v_sql := 'select * from (select t1.*,rownum rn from(select * from '||tbaleName ||') 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程序來驗證分頁過程顯示的正確性
//測試分頁
//加載驅動
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection ct=DriverManager.getConnection("...");
CallableStatement cs=ct.prepareCall("{call fenye(?,?,?,?,?,?)}");
cs.setString(1,"emp");
cs.setInt(2,5);
cs.setInt(3,1);
cs.registerOutParameter(4,orace.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(5,oracle.jdbc.OrcleTYpes.INTEGER);
cs.registerOutParameter(5,oracle.jdbc.OrcleTYpes.CURSOR);
cs.execute();
//獲取總記錄數/這裡要注意，getInt(4),其中4，是由該參數的位置決定的
int rowNum = cs.getInt(4);
int pageCount = cs.getINt(5);
ResultSet rs = (ResultSet)cs.getObject(6);
while(rs.next())
{
...
}
--新的需求，按照薪水由低到高進行排序
PL/SQL的進階 --例外處理
例外的分類
例外傳遞
--例外案例
寫一個塊：
declare
--定義
v_ename emp.ename%type;
begin
--
select ename into v_name from emp where empno=&gno;
dbms.output.put_line(v_ename);
exception
when no_data_found then
dbms.output.put_line('編號沒有');
end;
處理預定義例外：
PL/SQL提供了20過個預定義的例外：
case_no_found
case when ... when ... end case
dup_val_on_index
在試圖在不合法的游標上執行操作時，會觸發該例外
例如：試圖從沒有打開的游標提取數據，或是關閉沒有打開的游標，則會
觸發該例外
invalid_number
當輸入的數據有誤時，會觸發該例外
比如：
too_many_rows
當執行select into語句的時候，如果返回超過了一行，則會觸發該異常
zero_divide
value_error
當執行賦值操作時，如果變量的長度不足以容納實際數據
處理自定義例外
預定義例外和自定義例外都是與oracle錯誤相關的，並且
--自定義例外
create or replace procedure ex_test(spNo number)
is
--定義一個例外
myex exception;
begin
--更新用戶sal
update emp set sal=sal+1000 where empno=spNo;
--sql%notfound這裡表示沒有update
--raise myex;觸發myex
if sql%notfound then
raise myex;
end if;
exception
when myex then
dbms_output.put_line('沒有更新任何用戶');
end;
/
exec ex_test(56);
oracle視圖
介紹：
視圖是一個虛擬表，其內容由查詢定義，同真實的表一樣，視圖包含一系列帶有名稱的列
和行的數據。但是，視圖並不在數據庫中以存儲數據值集形式存在
例如兩張表 emp表和dept表
1.如果要顯示各個雇員的名字和他所在部門的名稱，必須用兩張表？
2.假定管理員創建一個用戶，現在只希望該用戶查詢sal<1000的那些雇員？
視圖和表的區別：
1.表需要占用磁盤空間，視圖不需要
2.視圖沒有索引，表有索引，所以視圖查詢較表速度慢
3.使用視圖可以簡化復雜查詢
4.使用視圖有利於提高安全性
創建視圖：
--把emp表的 sal<1000的雇員 映射到該視圖（view）
create view myView as select * from emp where sal<1000;
--視圖一旦創建成功，就可以當成一個普通表來使用
--為簡化操作，用一個視圖解決 顯示雇員編號，姓名和部門名稱，並且為可讀視圖
create view myView1 as select emp.empno,emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno with read only;
注意：視圖和視圖之間可以做復雜聯合查詢
修改視圖：
刪除視圖：
