Oracle03——游標、異常、存儲過程、存儲函數、觸發器和Java代碼訪問Oracle對象

来源:http://www.cnblogs.com/xieyupeng/archive/2017/09/05/7476717.html
-Advertisement-
Play Games

作者： kent鵬 轉載請註明出處： http://www.cnblogs.com/xieyupeng/p/7476717.html 1.游標(游標)Cursor 在寫java程式中有集合的概念，那麼在pl/sql中也會用到多條記錄，這時候我們就要用到游標，游標可以存儲查詢返回的多條數據。 語法： ...


作者： kent鵬  

轉載請註明出處： http://www.cnblogs.com/xieyupeng/p/7476717.html 

 

1.游標(游標)Cursor

在寫java程式中有集合的概念，那麼在pl/sql中也會用到多條記錄，這時候我們就要用到游標，游標可以存儲查詢返回的多條數據。

語法：

CURSOR  游標名  [ (參數名  數據類型,參數名 數據類型,...)]  IS  SELECT   語句;

例如：cursor c1 is select ename from emp;

游標的使用步驟：

  • 打開游標：      open c1;    (打開游標執行查詢)
  • 取一行游標的值：fetch c1 into pjob; (取一行到變數中)
  • 關閉游標：       close  c1;(關閉游標釋放資源)
  • 游標的結束方式   exit when c1%notfound
  • 註意： 上面的pjob必須與emp表中的job列類型一致： 

定義：pjob emp.empjob%type;

游標屬性:%isopen %rowcount(影響的行數)  %found  %notfound

範例1：使用游標方式輸出emp表中的員工編號和姓名

declare

  cursor pc is

    select * from emp;

  pemp emp%rowtype;

begin

  open pc;

  loop

    fetch pc

      into pemp;

    exit when pc%notfound;

    dbms_output.put_line(pemp.empno || ' ' || pemp.ename);

  end loop;

  close pc;

end;

範例2：寫一段PL/SQL程式，為部門號為10的員工漲工資。

declare

  cursor pc(dno myemp.deptno%type) is

    select empno from myemp where deptno = dno;

  pno myemp.empno%type;

begin

  open pc(20);

  loop

    fetch pc

      into pno;

    exit when pc%notfound;

    update myemp t set t.sal = t.sal + 1000 where t.empno = pno;

  end loop;

  close pc;

end;

 

2.異常

異常是程式設計語言提供的一種功能，用來增強程式的健壯性和容錯性。

系統定義異常 

no_data_found    (沒有找到數據)

too_many_rows   (select …into語句匹配多個行)

zero_divide   ( 被零除)

value_error   (算術或轉換錯誤)

timeout_on_resource  (在等待資源時發生超時)

範例1：寫出被0除的異常的plsql程式

declare

  pnum number;

begin

  pnum := 1 / 0;

exception

  when zero_divide then

    dbms_output.put_line('被0除');

  when value_error then

    dbms_output.put_line('數值轉換錯誤');

  when others then

    dbms_output.put_line('其他錯誤');

end;

 

用戶也可以自定義異常，在聲明中來定義異常

DECLARE

My_job   char(10);

v_sal   emp.sal%type;

No_data    exception;

cursor c1 is select distinct job from emp    order by job;

如果遇到異常我們要拋出raise no_data;

範例2：查詢部門編號是50的員工

declare

  no_emp_found exception;

  cursor pemp is

    select t.ename from emp t where t.deptno = 50;

  pename emp.ename%type;

begin

  open pemp;

  fetch pemp

    into pename;

  if pemp%notfound then

    raise no_emp_found;

  end if;

  close pemp;

exception

  when no_emp_found then

    dbms_output.put_line('沒有找到員工');

  when others then

    dbms_output.put_line('其他錯誤');

end;

 

3.存儲過程

存儲過程（Stored Procedure）是在大型資料庫系統中，一組為了完成特定功能的SQL 語句集，經編譯後存儲在資料庫中，用戶通過指定存儲過程的名字並給出參數（如果該存儲過程帶有參數）來執行它。存儲過程是資料庫中的一個重要對象，任何一個設計良好的資料庫應用程式都應該用到存儲過程。

 

創建存儲過程語法：

create [or replace] PROCEDURE 過程名[(參數名 in/out 數據類型)]  

AS

begin

        PLSQL子程式體；

End;

或者

create [or replace] PROCEDURE 過程名[(參數名 in/out 數據類型)]  

is

begin

        PLSQL子程式體；

End  過程名;

 

範例1：給指定的員工漲100工資，並列印出漲前和漲後的工資

分析：我們需要使用帶有參數的存儲過程

create or replace procedure addSal1(eno in number) is

  pemp myemp%rowtype;

begin

  select * into pemp from myemp where empno = eno;

  update myemp set sal = sal + 100 where empno = eno;

  dbms_output.put_line('漲工資前' || pemp.sal || '漲工資後' || (pemp.sal + 100));

end addSal1;

 

調用

begin

  -- Call the procedure

  addsal1(eno => 7902);     

  commit;

end;

 

4.存儲函數

create or replace function 函數名(Name in type, Name out type, ...) return 數據類型 is

  結果變數 數據類型;

begin

  

  return(結果變數);

end[函數名];

 

存儲過程和存儲函數的區別

一般來講，過程和函數的區別在於函數可以有一個返回值；而過程沒有返回值。 

但過程和函數都可以通過out指定一個或多個輸出參數。我們可以利用out參數，在過程和函數中實現返回多個值。

 

範例：使用存儲函數來查詢指定員工的年薪

create or replace function empincome(eno in emp.empno%type) return number is

  psal  emp.sal%type;

  pcomm emp.comm%type;

begin

  select t.sal into psal from emp t where t.empno = eno;

  return psal * 12 + nvl(pcomm, 0);

end;

使用存儲過程來替換上面的例子

create or replace procedure empincomep(eno in emp.empno%type, income out number) is

  psal emp.sal%type;

  pcomm emp.comm%type;

begin

  select t.sal, t.comm into psal, pcomm from emp t where t.empno = eno;

  income := psal*12+nvl(pcomm,0);

end empincomep;

 

調用：

declare

  income number;

begin

  empincomep(7369, income);

  dbms_output.put_line(income);

end;

 

5.觸發器

資料庫觸發器是一個與表相關聯的、存儲的PL/SQL程式。每當一個特定的數據操作語句(Insert,update,delete)在指定的表上發出時，Oracle自動地執行觸發器中定義的語句序列。 

 

1.觸發器作用

l 數據確認 

l 實施複雜的安全性檢查

l 做審計，跟蹤表上所做的數據操作等 

l 數據的備份和同步 

 

2.觸發器的類型 

語句級觸發器 ：在指定的操作語句操作之前或之後執行一次，不管這條語句影響      了多少行 。 

行級觸發器（FOR EACH ROW） ：觸發語句作用的每一條記錄都被觸發。在行級觸   發器中使用old和new偽記錄變數, 識別值的狀態。 

語法：

CREATE  [or REPLACE] TRIGGER  觸發器名

   {BEFORE | AFTER}

   {DELETE | INSERT | UPDATE [OF 列名]}

   ON  表名

   [FOR EACH ROW [WHEN(條件) ] ]

declare

    ……

begin

   PLSQL 塊 

End 觸發器名

 

範例：插入員工後列印一句話“一個新員工插入成功”

create or replace trigger testTrigger

  after insert on person  

declare

  -- local variables here

begin

  dbms_output.put_line('一個員工被插入');

end testTrigger;

 

範例：不能在休息時間插入員工

create or replace trigger validInsertPerson

  before insert on person

 

declare

  weekend varchar2(10);

begin

  select to_char(sysdate, 'day') into weekend from dual;

  if weekend in ('星期一') then

    raise_application_error(-20001, '不能在非法時間插入員工');

  end if;

end validInsertPerson;

當執行插入時會報錯

 

在觸發器中觸發語句與偽記錄變數的值

觸發語句

:old

:new

Insert

所有欄位都是空(null)

將要插入的數據

Update

更新以前該行的值

更新後的值

delete

刪除以前該行的值

所有欄位都是空(null)

範例：判斷員工漲工資之後的工資的值一定要大於漲工資之前的工資

create or replace trigger addsal4p

  before update of sal on myemp

  for each row

begin

  if :old.sal >= :new.sal then

    raise_application_error(-20002, '漲前的工資不能大於漲後的工資');

  end if;

end;

 

調用

update myemp t set t.sal = t.sal - 1;

 

3.觸發器實際應用

需求：使用序列，觸發器來模擬mysql中自增效果

1. 創建序列

1、建立表

複製代碼 代碼如下:

create table user  (   

    id   number(6) not null,   

    name   varchar2(30)   not null primary key  

)

 

2、建立序列SEQUENCE

代碼如下:

create sequence user_seq increment by 1 start with 1 minvalue 1 maxvalue 9999999999999 nocache order;

 

2.創建自增的觸發器

分析：創建一個基於該表的before insert 觸發器，在觸發器中使用剛創建的SEQUENCE。

代碼如下:

create or replace trigger user_trigger   

before insert on user  

for each row   

begin  

      select   user_seq.nextval  into:new.id from sys.dual ;   

end;

 

3.測試效果

insert into itcastuser(name) values('aa');

commit;

insert into itcastuser(name) values('bb');

commit;

 

5.Java代碼訪問Oracle對象

1.java連接oracle的jar包

可以在虛擬機中xp的oracle安裝目錄下找到jar包 :ojdbc14.jar

 

2.資料庫連接字元串

String driver="oracle.jdbc.OracleDriver";

String url="jdbc:oracle:thin:@192.168.56.10:1521:orcl";

String username="scott";

String password="tiger";


測試代碼： 

 

3.實現過程與函數的調用

1.調用過程

1.過程定義
--統計年薪的過程

create or replace procedure proc_countyearsal(eno in number,esal out number)

as

begin

   select sal*12+nvl(comm,0) into esal from emp where empno=eno;

end;

 

--調用

declare

   esal number;

begin

   proc_countyearsal(7839,esal);

   dbms_output.put_line(esal);

end;

  

2.過程調用
    @Test
    public void testProcedure01() {

        String driver = "oracle.jdbc.OracleDriver";

        String url = "jdbc:oracle:thin:@192.168.56.10:1521:orcl";

        String username = "scott";

        String password = "tiger";

        try {

            Class.forName(driver);

            Connection con = DriverManager.getConnection(url, username, password);

            CallableStatement callSt = con.prepareCall("{call proc_countyearsal(?,?)}");

            callSt.setInt(1, 7839);

            callSt.registerOutParameter(2, OracleTypes.NUMBER);

            callSt.execute();

            System.out.println(callSt.getObject(2));

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

 


2.調用函數 

1.函數定義
--統計年薪的函數

create or replace function fun_countyearsal(eno in number)

return number

as

   esal number:=0;

begin

  select sal*12+nvl(comm,0) into esal from emp where empno=eno;

  return esal;

end;

 

--調用

declare

   esal number;

begin

   esal:=fun_countyearsal(7839);

   dbms_output.put_line(esal);

end;

 

 

2.函數調用
    @Test
    public void testFunction01() {

        String driver = "oracle.jdbc.OracleDriver";

        String url = "jdbc:oracle:thin:@192.168.56.10:1521:orcl";

        String username = "scott";

        String password = "tiger";

        try {

            Class.forName(driver);

            Connection con = DriverManager.getConnection(url, username, password);

            CallableStatement callSt = con.prepareCall("{?= call fun_countyearsal(?)}");

            callSt.registerOutParameter(1, OracleTypes.NUMBER);

            callSt.setInt(2, 7839);

            callSt.execute();

            System.out.println(callSt.getObject(1));

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

 


4.游標引用的java測試 

1.定義過程，並返回引用型游標

--定義過程

create or replace procedure proc_cursor_ref(dno in number,empList out sys_refcursor)

as

begin

  open empList for select * from emp where deptno = dno;

end;

 

--pl/sql中調用

declare

  mycursor_c sys_refcursor;

  myempc emp%rowtype;

begin

  proc_cursor_ref(20,mycursor_c);

  

  loop

    fetch mycursor_c into myempc;

    exit when mycursor_c%notfound;

    dbms_output.put_line(myempc.empno||','||myempc.ename);

  end loop;

  close mycursor_c;

end;

 

2.java代碼調用游標類型的out參數

    @Test
    public void testFunction() {

        String driver = "oracle.jdbc.OracleDriver";

        String url = "jdbc:oracle:thin:@192.168.56.10:1521:orcl";

        String username = "scott";

        String password = "tiger";

        try {

            Class.forName(driver);

            Connection con = DriverManager.getConnection(url, username, password);

            CallableStatement callSt = con.prepareCall("{call proc_cursor_ref (?,?)}");

            callSt.setInt(1, 20);

            callSt.registerOutParameter(2, OracleTypes.CURSOR);

            callSt.execute();

            ResultSet rs = ((OracleCallableStatement) callSt).getCursor(2);

            while (rs.next()) {

                System.out.println(rs.getObject(1) + "," + rs.getObject(2));

            }

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

 

作者： kent鵬

出處： http://www.cnblogs.com/xieyupeng/

關於作者：專註JAVAEE領域，請多多賜教！

本文版權歸作者和博客園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文鏈接。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Genymotion-Android模擬器提示"Unable to connect to the Genymotion server. Please check your Internet connection."解決方法
    昨天剛裝的Genymotion，昨晚還用得好好的。 今晚開機，重新打開Genymotion，卻提示："Unable to connect to the Genymotion server. Please check your Internet connection."。 看提示，我以為是網路問題，分 ...
  • react native進階
    一、前沿||潛心修心，學無止盡。生活如此，coding亦然。本人鳥窩，一隻正在求職的鳥。聯繫我可以直接微信：jkxx123321 二、項目總結 **||**文章參考資料：1. http://blog.csdn.net/u011272795/article/details/73824558 2.htt ...
  • UIWebView的高度不對問題
    一般情況，在- (void)webViewDidFinishLoad:(UIWebView *) webView方法里添加如下代碼： 這方法有時候會失效！ 這方法在沒有改變webview的字體大小的時候還是使用正常的，但是你在改了字體大小或者圖片大小等之後是沒有效果的， webview的高度還是原來 ...
  • [sql] 常用函數整理（T-SQL 版）
    USE master; --5.2 字元串函數 --1.計算字元串長度 SELECT LEN('abc'); --2.字元串轉換為小寫 SELECT 'A', LOWER('a'); --3.字元串轉換為大寫 SELECT 'a', UPPER('a'); --4.截去字元串左側空格 SELECT ... ...
  • mssql instead of 觸發器應用一-創建只讀視圖(view)的方法
    轉自: http://www.maomao365.com/?p=4906 <span style="color:white;background-color:blue;font-weight:bold;">一、instead of 觸發器應用一創建只讀視圖的方法</span><span style= ...
  • 如何編寫更好的SQL查詢：終極指南-第二部分
    結構化查詢語言（SQL）是數據挖掘分析行業不可或缺的一項技能。對於SQL來說，編寫查詢語句只是第一步，確保查詢語句高效並且適合於你的資料庫操作工作，才是最重要的。 ...
  • Mysql之觸發器的操作:
    觸發器的操作: 1.觸發器的創建: (1).創建包含一條語句的觸發器 create trigger trigger_name before|after trigger_event on table_name for each row trigger_stmt; trigger_event表示操作事件 ...
  • Amazon Aurora解讀(SIGMOD 2017)
    Amazon在SIGMOD 2017發表了論文《Amazon Aurora: DesignConsiderations for High Throughput Cloud-Native Relational Databases》，第一次公開介紹了Aurora的設計理念和內部實現，下文是我對論文的解讀 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類