Java基礎——Oracle（七）

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

一、概述 pl/sql (procedural lanaguage/sql)是 oracle 在標準 sql 上的擴展 。不僅允許嵌入sql 語言,還可以定義變數和常量,允許使用條件語句和迴圈語句，允許使用例外處理錯誤。 -- 可以用來編寫過程,函數,和觸發器 -- 上述對象是放在資料庫中的 //數 ...


一、概述

pl/sql (procedural lanaguage/sql)是 oracle 在標準 sql 上的擴展 。不僅允許嵌入sql 語言,還可以定義變數和常量,允許使用條件語句和迴圈語句，允許使用例外處理錯誤。

-- 可以用來編寫過程,函數,和觸發器

-- 上述對象是放在資料庫中的  //資料庫端

-- 過程和函數可以在java程式中調用 ,觸發器只能被觸發,不能被調用

優點:

1.提高程式性能

2.模塊化的程式設計思想

3.減少網路傳輸流量

4.安全性高

5.能處理較複雜的sql操作

缺點:

1.移植性不好

2.可維護性差

3.不好調試

//例子  創建存儲過程
create  procedure sp_adduser is  //可以寫成  create  or replace (如果存在就替換 )
begin
insert into userinfo(userName) values ('zhangsan');
end;

 如何調用? 

exec 存儲過程名  -> exec sp_adduser

二、pl-sql 的簡單分類和編寫規範

簡單分類

1.過程(存諸過程)塊(編程的基本單位)

2.函數

3.觸發器

4.包

變數編寫規範

註釋 --    或   /*   */

變數 v_   // 比如  v_sal

常量 c_   // 比如  c_rate

游標 _cursor 為尾碼  //比如 emp_cursor

例外 e_   //比如 e_xxxerror

塊

塊(block) 是pl/sql 的基本程式單元,編寫pl/sql程式實際上就是編pl/sql塊. 簡單的功能可能只需一個塊,但是如果複雜,可能在一個塊中嵌套其他塊。

塊由三部分組成 定義部,執行部分,例外部分
如下

declear  //可選,比如定義常量,變數,游標,複雜數據類型等
begin
exception //可選
end;
//例一 只包擴執行部分的pl/sql塊    
set serveroutput on   //打開輸出選項,如果是off,則不會輸出,預設是off
begin
dbms_output.put_line('嘻嘻');
end;
//說明  dbms_output 是 oracle 提供的包(類似java中的類),該包中有一個過程叫 put_line
 //例二 包含定義部分和執行部分的pl/sql塊
declare
v_ename varchar2(50) ;  --定義字元串型變數,這個變數的長度要夠
begin
select ename into v_ename from emp where empno =&no;  -- // &no這種寫法是讓用戶可以手動輸入值
dbms_output.put_line('員工姓名'||v_ename); -   -// ||是連接字元
end;
//例三 將上例改為,連薪水也列印出來,包含例外處理
declare
v_ename varchar2(150) ;
v_sal number(7,2);
begin
select ename,sal into v_ename ,v_sal  from emp where empno =&no;
dbms_output.put_line('員工姓名'||v_ename || '工資:'||v_sal);
                 
exception  //進行例外處理
when no_data_found then
dbms_output.put_line('沒有查詢到對應的數據');
end;

說明 : 輸入了不存在的員工號,會出錯,要進行例外處理

oracle 預先定義了一些例外

NO_DATA_FOUND 就是找不到數據的時候出現的例外

預定義例外: INVALID_CURSOR,ZERO_DIVIDE,VALUE_ERROR,INVALID_NUMBER ....  //等共22種,都對應一個errorcod

過程

過程用於執行特定的操作,當建立過程的時候,即可指定輸入參數(in) ,也可以指定輸出參數 (out)

==通過輸入參數,可以將數據傳給過程的執行部分

==通過輸出參數,可以將執行部分的摟據傳給應用環境 (存儲過程的調用者)

可以用 create procedure 創建過程

例子

編寫過程,可以輸入員工名,新工資,可以修改員工的工資

調用過程  //exec 過程名

在java程式中調用一個過程

create procedure sp_01(p_name varchar2, p_sal number) is    --//這裡不用指定長度
begin
update emp set sal=p_sal where ename=p_name;
end;

執行 exec sp_01('SCOTT',9090);

//在java中調用

public static void test()  {
Connection conn = null;
CallableStatement stm = null;
try {
     conn = DBUtil.getConn();
     stm = conn.prepareCall("{call sp_01(?,?)}");  如果是sa 登錄 要寫成 {scott.call sp_01(?,?)}
     stm.setString(1, "SCOTT");
     stm.setInt(2, 10900);
     stm.execute();
     System.out.println("---操作成功----");
     } catch (Exception ex) {
     ex.printStackTrace();
     } finally {
     DBUtil.close(null, stm, conn);
     }
}

 附: 驅動類名: oracle.jdbc.driver.OracleDriver ,連接字串 jdbc:oracle:thin:@localhost:1521:orcl

函數

函數用於返回特定的數據,當建立函數時,在函數頭部必須包括 return 子句,而在函數體內必須包含return語句返回的數據。我們可以使用 create function 來建立函數。

函數一般只返回一個值

//輸入員工的姓名,返回他的年薪
create function fun01(p_name varchar2)
return number is  -- 在函數頭部必須包括 return 子句 ,number 表示返回的是數值類型
totalsal number(8,2);
begin
select sal*12+nvl(comm,0)*12 into totalsal from emp where ename=p_name;
return totalsal;
end;

執行函數

SQL> var result number;
SQL> call fun01('SCOTT') into :result;
//在java程式中調用

public static void test2(){
Connection conn = null;
CallableStatement stm = null;
try {
     conn = DBUtil.getConn();
     stm=conn.prepareCall("{?=call fun01('SCOTT')}"); //fun01 是函數名,SCOTT是函數要求的參數,? 是返回值的占位符
     stm.registerOutParameter(1, java.sql.Types.VARCHAR);  //聲要接收返回值,1 代表第一個問號 java.sql.Types.VARCHAR 代表返回值的類型
     stm.execute();
                        
     String result= stm.getString(1); //取出返回值
     System.out.println("年薪是:"+result);    
      } catch (Exception ex) {
     ex.printStackTrace();
     } finally {
       DBUtil.close(null, stm, conn);
    }
}

包

包用於在邏輯上組合過程和函數,由包規範和包體組成

1) 可以使用 create package 命令來創建包

create package sp_package_01 is
procedure sq_update_sal(name varchar2,newsal number); --//只有聲明,沒有實現
function fun02(name varchar2) return number;  --//這個函數有一個 number類型的返回值
end;

包的規範只包含了過程和函數的說明,但沒有實現

2) 建立包體  create package body 命令

create package body sp_package_01 is
procedure sq_update_sal(name varchar2,newsal number) is
begin
update emp set sal=newsal where ename=name;  --//更新工資
end;
function fun02(name varchar2)   return number is
totalsal number(8,2);
begin
select sal*12+nvl(comm,0)*12 into totalsal from emp where ename=name;
return totalsal;
end;

 //顯示錯誤信息 show errors

如何調用包里的內容,要用包名,如果是其他方案的,要有方案名.

//執行
exec sp_package_01.sq_update_sal('SCOTT',5000);

變數-之標量

在編寫pl/sql 的時候,可以定義變數和常量,包括

== 標量類型(scalar)

== 複合類型(composite)

== 參照類型(reference)

== lob (large object)  //大對象

標量(scalar)

1) 常用類型的標量的定義

標量主要用來放單個數據

如果要使用變數,要在定義部分定義

語法

identifier  [constant] datatype [not null] [:=| default expr]

identifier:名稱

constant: 指定它是常量, 需要指定初值,其值不能變

datatype: 數據類型

not null: 表示不能為空

default: 初值

expr: 指定初始值的pl/sql 表達式,可以是文本,其他變數,函數等

例子

-- 定義一個變長字元串

v_empnam varchar2(10)

-- 定義一個小數  值在 -9999.99 - 9999.99 之間 

v_sal number(6,2)

-- 定義一個小數,並給初值為 3.14

v_pai number(3,2) :=3.14     // := 賦值符號

-- 定義一個日期類型的變數

v_hiredate data

-- 定義一個布爾型變數,不能為空,初值為false

v_result boolean not null default false

2)標量的使用

例子 輸入員工號,顯示員工工資,姓名,個人所得稅(稅率 0.02)

set serveroutput on ;
declare

c_tax_rage number(3,2):=0.02 ;    --//稅率,是常量,必須給初值

v_ename varchar2(50);

v_sal number(8,2);

v_tax_sal number(8,2);  --//應交的所得稅

begin

select ename ,sal into v_ename,v_sal from emp where empno=&no;

v_tax_sal:=v_sal*c_tax_rage;  --//所得稅=工資* 稅率

dbms_output.put_line('姓名'||v_ename||'稅'||v_tax_sal);

end ;

3) 使用 %type 類型定義標量

上例存在一個問題

如果員工的姓名超過了50 ,會報錯   字元串緩衝區太小

可以使用  %type  屬性定義標量,它會根據你的資料庫中列的長度和類型來定義標量

標識符名稱 表名 列名   %type

比如上例中 可以    v_ename emp.ename%type

變數-之複合變數

複合類型變數(composite) 好比數組

用於存放多個值

-- pl/sql 記錄

-- pl/sql 表名

-- 嵌套表

-- varry //動態數組

1) pl/sql 記錄

類似高級語言中的結構體 ,當引用 pl/sql 記錄成員時,必須要加記錄變數做為首碼(記錄變數.記錄名)

declare
type emp_recorder_type is record  --//聲明一個複合類型變數
(
    name emp.ename%type,
    salary emp.sal%type,
    title emp.job%type
);
sp_record  emp_recorder_type;  --//定義了一個複合類型的變數,名稱是sp_record,類型是 emp_recorder_type
begin
select ename,sal,job into sp_record from emp where empno=7788;
dbms_output.put_lne('員工名'||sp_record.name||'工資'||sp_record.salary||'工作'||sp_record.title);
end;

2) pl/sql 表名

相當於數組 ,但它的下標可以負值,並且元素的下標沒有限制

declare
type sp_table_type is table of emp.ename%type   --//定義一個 sp_table_type 自定義類型,用於存放 emp.ename%type  類型的數據
index by binary_integer ; --//讓這個表的下標是按整數來排序的
               
sp_table  sp_table_type; --//定義了一個 sp_table_type 類型的變數,名字是 sp_table
               
begin
select ename into sp_table(0) from emp where empno='7788';  --//目前只放了一條數據,如果是多條會報錯
dbms_output.put_line('員工名'||sp_table(0));   --//這個下標是負數也可以
end;

變數-之參照類型變數 (reference)   

用於存放數值指針的變數,通過它可以使得應用程式共用相同的對象,降低占用空間。
有以下兩種

游標類型 (ref cursor)

對象類型 (ref obj_type) //不講

1) 參照類型  -- ref cursor 游標變數

定義游標

用的時候(open ),要指定select 語句,這樣一個游標就和select語句關聯了，需求 寫一個pl/sql語句塊,可以輸入部門號,並顯示該部門的所有員工姓名和工資。如果某個員工的工資低於5000,就增加10000 元。

declare
type sp_cursor is ref cursor ;  --//定義一個游標類型叫 sp_cursor
v_ename emp.ename%type;
v_sal emp.sal%type;
test_cursor sp_cursor;   --//定義一個游標類型的變數,名叫 test_cursor
                 
begin
open test_cursor for select ename,sal from emp where deptno=&no;   --//打開游標 ,要指定一個select語句
loop

fetch test_cursor into v_ename,v_sal;
exit when test_cursor % notfound;
dbms_output.put_line(v_ename||v_sal);
if v_sal<3000 then
update emp set sal=v_sal+10000 where ename=v_ename;
end if;
                         
end loop;
close test_cursor;
end if;
end;

控制結構語句

條件分支語句

if then

if then else

if then elsif else   //註意,不是elseif

迴圈語句

loop ... end loop

while ... loop     end loop

for

控制語句

goto 語句

null 語句

1)條件分支語句

== if then

編寫一個過程,可以輸入一個員工名,如果工資小於5000,則再扣3000

和上例相似

if v_sal<5000 then

update emp set sal=v_sal-3000 where ename=v_ename;

end if;

== if then else  //二重條件分支

輸入一個員工名,如果補助不是0 就在原來的基礎上增加100,如果為0 就把補助改成200

create or replace procedure sp_02 (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=200 where ename=spName;
end if;     
end;

執行: exec sp_02('SCOTT');

== 多重條件分支

if then elsif else

編寫一個過程,可以輸入一個員工編號,如果職位是 CLERK  給工資加 100 ,SALESMAN 給加工資 500,其他加 200

create or replace procedure sp_03(sp_no number) is
 v_job emp.job%type;
                  
begin
select job into v_job from emp where empno=sp_no;
if v_job='CLERK' then
update emp set sal=sal+100 where  empno=sp_no;
                      
elsif  v_job='SALESMAN' then
update emp set sal=sal+500 where  empno=sp_no;
                    
else
update emp set sal=sal+200 where  empno=sp_no;
end if;
                  
end;

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

-Advertisement-
Play Games
更多相關文章
  • Anroid四大組件service之本地服務
    服務是Android四大組件之一，與Activity一樣，代表可執行程式。但Service不像Activity有可操作的用戶界面，它是一直在後臺運行。用通俗易懂點的話來說： 如果某個應用要在運行時向用戶呈現可操作的信息就應該選擇Activity,如果不是就選擇Service。 Service的生命周 ...
  • Found an unexpected Mach-O header code: 0x72613c21
    在按照第三方sdk文檔中的Emedded Binaries 中加入了他們的framework，在刪除這下麵的對應的framework後，問題就得到瞭解決 發下有個英文的頁面也是涉及這個問題的, 描述的很詳細. 鏈接： https://stackoverflow.com/questions/41786 ...
  • sqlser 2005 使用執行計劃來優化你的sql
    一:sqlserver 執行計劃介紹 sqlserver 執行計是在sqlser manager studio 工具中打開，是檢查一條sql執行效率的工具。建議配合SET STATISTICS IO ON等語句來一起使用，執行計劃是從右向左看，耗時高的一般顯示在右邊,我們知道，sqlserver 查 ...
  • Windows10安裝MySQL5.7..及忘記root密碼修改
    例：MySQL5.7.19 下載 當然首先是要下載咯https://dev.mysql.com/downloads/mysql/ 官網下載地址。 選擇適合自己電腦的版本，點擊Download，跳轉，直接No thanks下載好了。 靜靜的等待下載，解壓。這是我的解壓路徑D:\MySQL 正式開始安裝 ...
  • Redis4.0 Cluster — Centos7
    Redis4.0 Cluster — Centos7， Ruby redis-trib.rb , gem redis ...
  • Ubuntu 離線安裝Mysql
    一、安裝包 先從網路上，下載Mysql安裝包，複製到U盤 下載地址：https://dev.mysql.com/downloads/mysql/ 二、掛載U盤 2.1查看分區 先輸入命令 cat /proc/partitions 插入U盤，重新輸入命令 cat /proc/partitions 結果 ...
  • oracle 資料庫基本操作——數據定義語言
    熟悉Oracle上機環境及Oracle客戶端的配置；熟練掌握和使用DDL語言，建立、修改和刪除資料庫表、主鍵、外鍵約束關係和索引。 (修改資料庫表名) 將資料庫表S1改名為Student_Temp。 在定義外鍵約束條件時，不能把其他表中沒有的屬性定義在本表的外鍵中，否則會生辰一個錯誤； 在建表時，因 ...
  • Java基礎——Oracle（八）
    一、流程式控制制語句 1) 迴圈語句 == loop .. end loop 簡單的迴圈,至少被執行一次 == while ... loop end loop == for 2) 控制語句 == goto 用於跳轉到指定的標號去執行,不建議使用 語法: goto 標號名 == null 語句 null語 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類