Java基礎——Oracle(八)

来源:http://www.cnblogs.com/1693977889zz/archive/2017/10/21/7704864.html
-Advertisement-
Play Games

一、流程式控制制語句 1) 迴圈語句 == loop .. end loop 簡單的迴圈,至少被執行一次 == while ... loop end loop == for 2) 控制語句 == goto 用於跳轉到指定的標號去執行,不建議使用 語法: goto 標號名 == null 語句 null語 ...


一、流程式控制制語句

1) 迴圈語句

== loop ..  end loop

簡單的迴圈,至少被執行一次

create table userinfo (id number, name  varchar2(30)) ; //創建一個表
create or replace procedure sp_04 (p_name varchar2) is  //創建一個存儲過程,迴圈往表裡添10條數據
v_num number :=1;
begin
loop
insert into userinfo values (v_num,p_name);
exit when v_num=10;
v_num :=v_num+1;
end loop;
end;
                             
exec  sp_04('xxxx') ;

== while ... loop   end loop     

create or replace procedure sp_05 (p_name varchar2) is
v_num number :=11;
                  
begin
while v_num <=20 loop
insert into userinfo values (v_num,p_name);
v_num :=v_num+1;
end loop;
end;

== for

create or replace procedure sp_06 (p_name varchar2) is
v_num number :=21;
begin
for i in 21..30 loop   //可以 寫成 for i  reverse  in 21..30 loop ,  // reverse表示反轉,按相反的順序添加
insert into userinfo values (v_num,p_name);
v_num :=v_num+1;
end loop;    
end; 

2) 控制語句

== goto

用於跳轉到指定的標號去執行,不建議使用

語法: goto 標號名

set serveroutput on;
declare
i number:=1;  
begin
loop
dbms_output.put_line('i的值是'||i);
if i=10 then
goto end_loopAAA;
end if;
i:=i+1;
                          
end loop;
<<end_loopAAA>>   --//這是在聲明一個標號
dbms_output.put_line('程式執行結束');
end;

== null 語句 

null語句不執行任何操作,主要是為了讓程式好讀

declare
v_sal emp.sal%type;
v_ename emp.ename%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
if v_sal<8000 then
update emp set sal=99999 where ename=v_ename;
else
null;  
end if;
end;

二、存儲過程練習

//練習一 向 book 表添一本書,寫一個存儲過程,向表中添加一條數據,在java程式中調用

create table book (
bookid number,
bookname varchar2(50),
pubhouse varchar2(50)
)
                
create or replace procedure sp_addbook(sp_bookid in number,sp_bookname in varchar2,pubhouse in varchar2) is    --//in 表示是輸入參數
begin
insert into book (bookid,bookname,pubhouse)  values(sp_bookid,sp_bookname,pubhouse);
end;

在java中調用

public static void test3(){
Connection conn=null;
CallableStatement stm=null;
try{
     conn=DBUtil.getConn();
     stm=conn.prepareCall("{call sp_addbook(?,?,?)}");  
                    
     stm.setInt(1, 50);
     stm.setString(2, "紅岩");
     stm.setString(3, "清華大學出版社");
                    
      stm.execute();    
      }
      catch(Exception ex){
      ex.printStackTrace();
      }
      finally{
      DBUtil.close(null,stm,conn);
      }
}

//練習二 輸入一個書的編號,返回書名 (有輸入和輸出的存儲過程)

create or replace procedure sp_getbookname (sp_bookid in number ,sp_bookname out varchar2) is    --//out 表示這個參數是輸出參數
begin
select bookname into sp_bookname from book where bookid= sp_bookid;
end; 

在java中調用

//根據書的id查詢書名( 即有輸入,也有輸出的存儲過程)
public static void test4(){
Connection conn=null;
CallableStatement stm=null;
     try{
         conn=DBUtil.getConn();
         stm=conn.prepareCall("{call sp_getbookname(?,?)}");  
                        
          stm.setInt(1, 50); //傳一個輸入參數 (書的id)
          stm.registerOutParameter(2, OracleTypes.VARCHAR); //指定輸入參數所對應的類型
                        
         stm.execute();    
                        
         String bookName=stm.getString(2);  //取存儲過程輸出參數返回的值 2,代表是第2個問號
         System.out.println(bookName);    
         }
         ...
}

//練習三 一個存儲過程同時有多個輸出參數  

create or replace procedure sp_getempinfo (sp_empno number  , sp_totalsal out number, sp_job out varchar2, sp_name out varchar2) is
begin
select sal*12+nvl(comm,0)*12, job,  ename into sp_totalsal,sp_job,sp_name  from  emp where empno =sp_empno;
end;
            
public static void test5(){
          Connection conn=null;
          CallableStatement stm=null;
          try{
          conn=DBUtil.getConn();
          stm=conn.prepareCall("{call sp_getempinfo(?,?,?,?)}");  
                    
           stm.setInt(1, 7788); //傳一個輸入參數 ,員工id
           stm.registerOutParameter(2, OracleTypes.NUMBER); //sp_totalsal
           stm.registerOutParameter(3, OracleTypes.VARCHAR); //sp_job
           stm.registerOutParameter(4, OracleTypes.VARCHAR); //sp_name
                    
           stm.execute();    
                    
           System.out.println("姓名是"+stm.getString(4));
           System.out.println("崗位是"+stm.getString(3));
           System.out.println("年薪是"+stm.getString(2));
                 
                    
           }catch(Exception ex){
           ex.printStackTrace();
           }finally{
           DBUtil.close(null,stm,conn);
                }
            }

 //例四 返回列表(結果集)

編寫一個過程,輸入部門號,查詢出該部門所有員的工信息

Oracle 中的存儲過程沒有返回值 ,所以它返回內容的時候,都是用 out 參數,對於返回列表也不例外,但由於返回的是列表,不能用一般的參數,所以要用 package

1) 建一個包

create or replace package testpackage as
TYPE  test_cursor is ref cursor ;  --//聲明瞭一個游標類型
end testpackage;

2) 建一個存儲過程

create or replace procedure sp_testquery (sp_deptno in  number, p_cursor out testpackage.test_cursor ) is
begin
open p_cursor for  select * from emp where deptno=sp_deptno;   --// 把游標和查詢關聯起來
end;

3) java程式

public static void getEmpList(){
     Connection conn=null;
     CallableStatement stm=null;
        try{
            conn=DBUtil.getConn();
            stm=conn.prepareCall("{call sp_testquery(?,?)}"); //deptno, p_cursor
            stm.setInt(1, 20);  //給部門編號傳值
            stm.registerOutParameter(2, OracleTypes.CURSOR);  //給第二個參數(存儲過程的輸出參數) 指定類型
                        
            stm.execute();
                        
            ResultSet rs= (ResultSet)stm.getObject(2);
            while(rs.next()){
                    System.out.print(rs.getString("ename")+"\t");
                    System.out.print(rs.getString("job")+"\t");
                    System.out.println(rs.getString("sal"));
                    }
             }catch(Exception ex){
               ex.printStackTrace();
             }finally{
              DBUtil.close(null,stm,conn);
             }
     }
}

三、分頁存儲過程

1) 分頁語句 : 

select * from (  select t1.*,rownum rn from  ( select * from emp) t1 where rownum <=10 ) where rn >5;

2) 建一個包

create or replace package pagePackage as
type p_cursor is ref cursor ;    --//聲明一個游標類型
end pagePackage;

3) 存儲過程

create or replace procedure sp_fenye
(
   tableName in varchar2 , --表名
   pageSize in number,    --每頁多少條記錄
   pageIndex in number,    --當前是第幾頁
               
   sp_cursor out pagePackage.p_cursor,  --用於返回記錄集的游標
   myrowCount out number, -- 返回記錄總數
   mypageCount out number -- 返回總頁數
               
   )  is
        
         v_sql varchar2 (2000);
         v_begin number :=(pageIndex-1)* pageSize +1;
         v_end number :=pageIndex*pageSize;
        
         begin
           v_sql:=' select * from (  select t1.*,rownum rn from 
( select * from '||tableName||') t1 where rownum <='||v_end||' ) where rn >= '||v_begin ; open sp_cursor for v_sql; v_sql:='select count(*) from '||tableName; execute immediate v_sql into myrowCount; --//查詢記錄總數,立即查詢 /*計算mypageCount if mod(myrowCount,pageSize) ==0 then mypageCount:=myrowCount/pageSize; else mypageCount:=myrowCount/pageSize+1; end if; */ mypageCount :=ceil (myrowCount/pageSize); --//這句可以替代上面的邏輯 end;

4) java程式

public static void getListByPage(int pageSize, int pageIndex,String tableName){
       Connection conn=null;
       CallableStatement stm=null;
       ResultSet rs=null;
          try{
          conn=DBUtil.getConn();
          stm=conn.prepareCall("{call sp_fenye(?,?,?,?,?,?)}");
                    
          stm.setString(1,tableName);  //tableName
          stm.setInt(2, pageSize); //pageSize 設為每頁5條
          stm.setInt(3, pageIndex); //pageIndex ,設當前頁是第二頁
                    
          stm.registerOutParameter(4, OracleTypes.CURSOR);  //指定第四個參數的返回類型為游標類型
          stm.registerOutParameter(5, OracleTypes.NUMBER);  //接收總記錄數
          stm.registerOutParameter(6, OracleTypes.NUMBER);  //接收總頁數
                    
          stm.execute();
                    
          rs=(ResultSet)stm.getObject(4) ; //打開返回的游標
          while(rs.next()){
               System.out.print(rs.getString("ename")+"\t");
               System.out.print(rs.getDouble("sal")+"\t");
               System.out.println(rs.getString("job")+"\t");
                    }
                    
               System.out.println("總行數:"+stm.getInt(5));
               System.out.println("總頁數:"+stm.getInt(6));
                        
               }catch(Exception ex){
                    ex.printStackTrace();
                }finally{
                    DBUtil.close(rs,stm,conn);
                }
}

四、異常處理

//例外

預定義例外,非預定義例外 和 自定義例外

預定義例外用於處理常見的Oracle 錯誤

非預定義例外用於處理預定義例外不能處理的例外

自定義例外,用於處理與Oracle錯誤無關的其他情況

//例子        
set serveroutput on;
declare
v_ename emp.ename%type ;
begin
select ename into v_ename from emp where empno=&no;
dbms_output.put_line(v_ename);
Exception
when  no_data_found then
dbms_output.put_line('數據沒找到');
end;

預定義例外是由 pl/sql提供的系統例外

1) case_not_found

在編寫 case 語句的時候同, 如果when 子句沒有包含必須的 case 分支

create or replace procedure sp_g(spno number) is
          v_sal emp.sal%type;
                        
          begin
          select sal into v_sal from emp where empno=spno;
          case
          when v_sal<1000 then
          dbms_output.put_line('少於1000');
          when v_sal<2000 then
          dbms_output.put_line('少於2000');
          end case;
          exception
          when case_not_found then
          dbms_output.put_line('case 語句沒有得到匹配的條件');
         end;

2)  cursor_already_open

當重新打開已打開的游標時,會觸發
             

declare
cursor emp_cursor is select ename,sal from emp;
begin
open emp_cursor;
for emp_record1 in emp_cursor loop  -- 這裡又打開了一次游標
dbms_output.put_line(emp_record1.ename);
end loop;
                            
exception
when cursor_already_open then
dbms_output.put_line('游標已經打開');
end;

3)  dup_val_on_index

在唯一索引所對應的例上插入重覆的值時觸發

begin
insert into dept values(10,'公案安部','北京');
   exception
   when dup_val_on_index then
   dbms_output.put_line('插入重覆列了');
end;

創建索引 create index 索引名 on 表名(列名)

4) invaild_cursor

試圖在不合法的游標上操作時

例如: 試圖從沒有打開的游標提取數據,或是關閉沒有打開的游標        

declare
cursor emp_cursor is select ename,sal from emp;
emp_record emp_cursor%rowtype;      -- //emp_record是這個游標變數的名字,
begin 
open emp_cursor;//打開游標

fetch emp_cursor into emp_record;
dbms_output.put_line(emp_record.ename);
close emp_cursor;
exception
when invalid_cursor then
dbms_output.put_line('請檢測游標是否打開');
end;

五、批量生成測試數據

create  table TestKKK (idAAA number,dateAAA varchar2(50),randomAAA number , nameAAA varchar2(50));
          insert  into TestKKK (idAAA ,dateAAA ,randomAAA,nameAAA)
          select rownum ,
           to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss'),
           trunc(dbms_random.value(0, 100)),
           dbms_random.string('x', 20)
           from dual  connect by level <= 100000   //一般用於遞歸查詢

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

-Advertisement-
Play Games
更多相關文章
  • 首先#pragma在本質上是聲明,常用的功能就是註釋,尤其是給Code分段註釋;而且它還有另一個強大的功能是處理編譯器警告,但卻沒有上一個功能用的那麼多。 clang diagnostic 是#pragma 第一個常用命令: 常見的用法 1. 方法棄用告警 2. 不相容指針類型 3. 迴圈引用 4. ...
  • 服務是Android四大組件之一,與Activity一樣,代表可執行程式。但Service不像Activity有可操作的用戶界面,它是一直在後臺運行。用通俗易懂點的話來說: 如果某個應用要在運行時向用戶呈現可操作的信息就應該選擇Activity,如果不是就選擇Service。 Service的生命周 ...
  • 在按照第三方sdk文檔中的Emedded Binaries 中加入了他們的framework,在刪除這下麵的對應的framework後,問題就得到瞭解決 發下有個英文的頁面也是涉及這個問題的, 描述的很詳細. 鏈接: https://stackoverflow.com/questions/41786 ...
  • 一:sqlserver 執行計劃介紹 sqlserver 執行計是在sqlser manager studio 工具中打開,是檢查一條sql執行效率的工具。建議配合SET STATISTICS IO ON等語句來一起使用,執行計劃是從右向左看,耗時高的一般顯示在右邊,我們知道,sqlserver 查 ...
  • 例:MySQL5.7.19 下載 當然首先是要下載咯https://dev.mysql.com/downloads/mysql/ 官網下載地址。 選擇適合自己電腦的版本,點擊Download,跳轉,直接No thanks下載好了。 靜靜的等待下載,解壓。這是我的解壓路徑D:\MySQL 正式開始安裝 ...
  • Redis4.0 Cluster — Centos7, Ruby redis-trib.rb , gem redis ...
  • 一、安裝包 先從網路上,下載Mysql安裝包,複製到U盤 下載地址:https://dev.mysql.com/downloads/mysql/ 二、掛載U盤 2.1查看分區 先輸入命令 cat /proc/partitions 插入U盤,重新輸入命令 cat /proc/partitions 結果 ...
  • 熟悉Oracle上機環境及Oracle客戶端的配置;熟練掌握和使用DDL語言,建立、修改和刪除資料庫表、主鍵、外鍵約束關係和索引。 (修改資料庫表名) 將資料庫表S1改名為Student_Temp。 在定義外鍵約束條件時,不能把其他表中沒有的屬性定義在本表的外鍵中,否則會生辰一個錯誤; 在建表時,因 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 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 ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...