玩轉oracle學習第七天，oracle學習第七天

玩轉oracle學習第七天，oracle學習第七天


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:hin@127.0.0.1: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;
 
 注意：視圖和視圖之間可以做複雜聯集查詢
 
 修改視圖：
 
 刪除視圖：