三猪腕、ORACLE之PLSQL編程

ORACLE 數(shù)據(jù)庫中包含一種過程化語言姜胖，稱為 PL/SQL(Procedural Language/SQL)。PL/SQL 可以用來編寫包含 SQL 語句的程序纳猪。在 PL/SQL 中可以使用 IF 語句或者 LOOP 循環(huán)語句實現(xiàn)控制程序的執(zhí)行流程氧卧，還可以定義變量，實現(xiàn)語句之間傳遞數(shù)據(jù)信息氏堤，從而 PL/SQL 語言可以封裝程序?qū)崿F(xiàn)操控程序處理的細節(jié)沙绝。PL/SQL 是 ORACLE 的專用語言，它是對 SQL 語言的擴展鼠锈，它允許在其內(nèi)部嵌套普通的 SQL 語句闪檬。

1.PL/SQL 塊結構

PL/SQL 程序都是以塊為基本單位，整個 PL/SQL 塊分為三部分：聲明部分购笆、執(zhí)行部分和異常處理部分粗悯。

1.1 語句結構如下:

[DECLARE]
--聲明部分，可選
BEGIN
--執(zhí)行部分同欠，必選
[EXCEPTION]
--異常處理部分,可選
END

例子：

DECLARE
 v_num1 int:=400;
 v_num2 int:=2;
 v_result int;
BEGIN
  v_result := v_num1 / v_num2;
  DBMS_OUTPUT.put_line(v_result);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('出現(xiàn)異常了!');
END;

2.變量和類型

2.1 定義變量語法：

變量的名稱變量的類型; eg: v_num number;
變量的名稱變量的類型 := 值; eg:v_num number := 100;
定義一個常量 eg: PI constant number := 3.1415926;常量的定義一定要初始化样傍，不然就要報錯。
使用表名.列名%type; eg: v_sal emp.sal%type; 該方式引用emp表中的sal列作為變量的類型
使用表名%rowtype; eg:v_emprow emp%rowtype;該方式引用了emp表中的一行數(shù)據(jù)類型作為變量的類型铺遂。

[:=]是賦值運算符

2.2 PLSQL中的類型

1.數(shù)值型:number衫哥、int 、Integer 襟锐、 float等
2.字符型：varchar 撤逢、varchar2 、char等
3.日期型:Date
注意粮坞，只有字符型要指定長度蚊荣，其他可以不指定

例子：

DECLARE
  -- 定義都是變量 語法結構： 變量的名稱 變量的類型; 稱為聲明一個變量； 變量的名稱 變量的類型 :=[賦值運算符] 值; 稱為聲明變量并初始化捞蚂；
  v_num1 number :=200; 
  v_num2 number := 0;
  PI constant number := 3.1415926;
  v_result number;
BEGIN
  v_result := v_num1 / v_num2;
  -- 以下你就認為是java中的 system.out.println();
  DBMS_OUTPUT.put_line('結果為:' || v_result);
EXCEPTION
  WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('程序有異常妇押！');
END;

-- PLSQL 跟  ORACLE中表聯(lián)系起來 查詢SMITH員工的工資，部門編號姓迅，入職日期信息敲霍，員工姓名打印到控制臺
DECLARE
   v_sal number;
   v_deptno number;
   v_hiredate date;
   v_ename varchar2(50);
BEGIN
   -- INTO 用于 SELECT 查詢語句中俊马，表示把查詢的列數(shù)據(jù)賦值給相應的變量
   SELECT SAL,DEPTNO,HIREDATE,ENAME  INTO  v_sal, v_deptno, v_hiredate, v_ename FROM EMP WHERE ENAME = 'SMITH';
   
   DBMS_OUTPUT.put_line( v_sal || ',' || v_deptno||',' ||v_hiredate||','|| v_ename);

END;
-- 表.列%type;指定表中某個列的類型長度定義變量
DECLARE
   v_sal emp.sal%type;
   v_deptno emp.deptno%type;
   v_hiredate emp.hiredate%type;
   v_ename emp.ename%type;
BEGIN
   -- INTO 用于 SELECT 查詢語句中，表示把查詢的列數(shù)據(jù)賦值給相應的變量
   SELECT SAL,DEPTNO,HIREDATE,ENAME  INTO  v_sal, v_deptno, v_hiredate, v_ename FROM EMP WHERE ENAME = 'SMITH';
   
   DBMS_OUTPUT.put_line( v_sal || ',' || v_deptno||',' ||v_hiredate||','|| v_ename);

END;


DECLARE
   -- 表中的一行作為變量的類型
   v_emprow emp%rowtype;
BEGIN
   
   SELECT *  INTO v_emprow  FROM EMP WHERE ENAME = 'SMITH';
   
   DBMS_OUTPUT.put_line( v_emprow.sal || ',' || v_emprow.deptno||',' ||v_emprow.hiredate||','||v_emprow.ename);

END;

3.條件語句

條件語句用于控制程序的流程肩杈。在 PLSQL 中定義了有：

IF 條件 THEN 執(zhí)行的語句
ELSE 條件 THEN 執(zhí)行語句
ELSE 執(zhí)行語句
END IF標志條件語句結束

例子：-- 輸入年齡柴我，可以判斷該人是步入什么階段兒童、青年扩然、中年艘儒、老人、去死吧

-- 判斷語句  輸入年齡 夫偶，可以判斷該人是步入什么階段  兒童 界睁、 青年 、 中年 兵拢、老人 翻斟、 去死吧

DECLARE
   -- &age 模擬輸入的年齡
   v_age number:=&age;
BEGIN
   
   IF v_age < 12 THEN
     DBMS_OUTPUT.put_line('你是兒童');
     
     ELSIF v_age < 30 THEN
           DBMS_OUTPUT.put_line('你是青年');
      ELSIF v_age < 50 THEN
           DBMS_OUTPUT.put_line('你是中年');
       
      ELSIF v_age < 100 THEN
           DBMS_OUTPUT.put_line('你是老人');
   ELSE
           DBMS_OUTPUT.put_line('你去死吧！说铃！');
   END IF;  
END;

4.循環(huán)語句

循環(huán)語句有而是來控制程序執(zhí)行的访惜。PLSQL 中循環(huán)分三種。

LOOP 循環(huán)體 END LOOP
WHILE 條件 LOOP 循環(huán)體 END LOOP
FOR 變量名稱 IN [ REVERSE ] 范圍1..100 LOOP
循環(huán)體
END LOOP;

例子：

-- 數(shù)數(shù)腻扇，從1數(shù)到100债热，輸出到控制臺  FOR 變量名稱 IN 范圍 LOOP 循環(huán)體 END LOOP 
DECLARE
   v_num number :=&num;
BEGIN
   
   FOR i IN  REVERSE 1..v_num LOOP
     
   DBMS_OUTPUT.put_line(i);
   
   END LOOP;
END;

-- LOOP  循環(huán)體  END LOOP : EXIT WHEN條件
DECLARE
   v_num number :=&num;
   v_index number := 1;
BEGIN
   LOOP
    -- 循環(huán)退出的語句
    EXIT WHEN v_index > v_num;
    DBMS_OUTPUT.put_line(v_index);
    v_index := v_index + 1;
   END LOOP;
END;

-- WHILE 條件   LOOP  循環(huán)體  END LOOP;

DECLARE
   v_num number :=&num;
   v_index number := 1;
BEGIN
   WHILE v_index <=  v_num LOOP
    DBMS_OUTPUT.put_line(v_index);
    v_index := v_index + 1;
   END LOOP;
END;

5.游標

數(shù)據(jù)類型，用于接收一組數(shù)據(jù)幼苛，通過游標對象中的屬性進行數(shù)據(jù)抓取
游標分兩類：1.顯示游標 2.隱式游標
游標對象中的屬性：%found(判斷當前是否還有數(shù)據(jù)行窒篱，如果有返回true)、%notfount(判斷當前是否還有數(shù)據(jù)行蚓峦，如果沒有返回true)舌剂、 %isopen(游標是否是打開狀態(tài)) 、 %ROWCOUNT (返回受影響的行數(shù))暑椰、%ROWNUM(返回游標讀取行的位置)

顯示游標的使用過程
1.定義游標類型
2.定義游標變量
3.指定游標關聯(lián)的查詢SQL
4.先打開游標 open
5.抓取游標行數(shù)據(jù) fetch
6.關閉游標 close

例子：查詢emp表中所有數(shù)據(jù)霍转，打印到控制臺

-- 游標(動態(tài)游標) 使用方式1 該方式游標變量可以重復利用
DECLARE
   -- 1.定義游標類型
   type cur_emp is ref cursor;
   -- 2.定義游標變量
   my_cur cur_emp;
   -- 定義一個游標抓取行的臨時變量
   v_emprow emp%rowtype;
BEGIN
  -- 3.游標變量關聯(lián)上查詢SQL
     open my_cur  for  SELECT * FROM EMP;
  -- 4.打開游標
    -- open my_cur;
  -- 5.抓取游標行數(shù)據(jù)
     LOOP
      fetch my_cur into  v_emprow;
      EXIT WHEN my_cur%NOTFOUND;
      DBMS_OUTPUT.put_line( v_emprow.sal || ',' || v_emprow.deptno||',' ||v_emprow.hiredate||','||v_emprow.ename);
     END LOOP;
  -- 6.關閉游標
  close my_cur;
  
END;


-- 游標(靜態(tài)游標) 使用方式2
DECLARE
   -- 1.定義游標變量,并關聯(lián)上查詢SQL
   CURSOR my_cur is select * from  emp;
   -- 定義一個游標抓取行的臨時變量
   v_emprow emp%rowtype;
BEGIN
 
  -- 3.打開游標
  open my_cur;
  -- 4.抓取游標行數(shù)據(jù)
     LOOP
      fetch my_cur into  v_emprow;
      EXIT WHEN my_cur%NOTFOUND;
      DBMS_OUTPUT.put_line( v_emprow.sal || ',' || v_emprow.deptno||',' ||v_emprow.hiredate||','||v_emprow.ename);
     END LOOP;
  -- 5.關閉游標
  close my_cur;
END;

靜態(tài)游標可以使用 for in loop 遍歷游標方式

-- 靜態(tài)游標遍歷數(shù)據(jù)，簡化一汽，使用 for in LOOP 語句
DECLARE
   -- 1.定義游標變量,并關聯(lián)上查詢SQL
   CURSOR my_cur is select * from  emp;

BEGIN
      FOR v_emprow IN my_cur LOOP
      DBMS_OUTPUT.put_line( v_emprow.sal || ',' || v_emprow.deptno||',' ||v_emprow.hiredate||','||v_emprow.ename); 
      END LOOP;
END;

帶參數(shù)的靜態(tài)游標

-- 帶參數(shù)的靜態(tài)游標  查詢10號部門的員工信息,并打印到控制臺避消。

DECLARE
   v_input number := &depno;
   CURSOR emp_cursor(v_deptno emp.deptno%type) is SELECT * FROM EMP WHERE DEPTNO = v_deptno;
BEGIN
  FOR v_emprow IN emp_cursor(v_input) LOOP
     DBMS_OUTPUT.put_line( v_emprow.sal || ',' || v_emprow.deptno||',' ||v_emprow.hiredate||','||v_emprow.ename); 
  END LOOP;
END;

隱式游標

沒有顯示的名字的游標，但是有一個默認名字SQL召夹，岩喷，當然也具有游標的屬性
%notfound、 %found监憎、 %isopen 纱意、%rownum、 %ROWCOUNT

-- 隱式游標
DECLARE
   v_input number := &depno;
BEGIN
   FOR v_emprow IN (SELECT * FROM EMP WHERE DEPTNO = v_input) LOOP
      DBMS_OUTPUT.put_line( v_emprow.sal || ',' || v_emprow.deptno||',' ||v_emprow.hiredate||','||v_emprow.ename); 
   END LOOP;
END;

-- 傳一個員工編號鲸阔，就把該條記錄刪除掉
DECLARE
   v_input number := &empno;
BEGIN
  DELETE FROM EMP WHERE EMPNO = v_input;
  DBMS_OUTPUT.put_line(SQL%rowcount);
END;

6.函數(shù)

函數(shù)用于返回特定的數(shù)據(jù)偷霉，當建立函數(shù)時迄委，在函數(shù)頭部必須包含return子句。而在函數(shù)體內(nèi)必須包含return語句返回的數(shù)據(jù)类少。

創(chuàng)建一個函數(shù)的結構:
CREATE [OR REPLACE] FUNCTION 函數(shù)的自定義名稱 (形參列表) RETUREN 返回數(shù)據(jù)的類型
IS
--聲明部分
BEGIN
-- 函數(shù)主題部分
END;

例子:

-- 函數(shù)
CREATE OR REPLACE FUNCTION FUN_EMP(v_input  number) RETURN NUMBER
IS
 -- 定義變量的部分
BEGIN
  DELETE FROM EMP WHERE EMPNO = v_input;
  RETURN SQL%rowcount;
END;

使用函數(shù)方式1：
DECLARE
  v number;
begin
  v := FUN_EMP(7369);
end;
=========
方式2叙身，只支持函數(shù)的操作不能是DML操作：
 
SQL> SELECT FUN_EMP2(7369) FROM DUAL;
 
FUN_EMP2(7369)
--------------
           800

7.過程

過程用于執(zhí)行特定的操作，當建立過程時硫狞，既可以指定輸入?yún)?shù)(in)信轿，也可以指定輸出參數(shù)(out)，通過在過程中使用輸入?yún)?shù)残吩，可以將數(shù)據(jù)傳遞到執(zhí)行部分财忽；通過使用輸出參數(shù)，可以將執(zhí)行部分的數(shù)據(jù)傳遞到應用環(huán)境

過程創(chuàng)建的結構：
CREATE [OR REPLACE] PROCEDURE 過程自定義名稱 (參數(shù)列表)
IS
-- 申明部分
BEGIN
-- 過程體
END;

例子：

-- 指定員工編號世剖，輸出該員工的姓名和薪水以及入職日期
CREATE OR REPLACE PROCEDURE PRO_EMP(v_empno IN emp.empno%type,v_ename OUT EMP.ENAME%TYPE,v_sal OUT emp.sal%type,v_hiredate out emp.hiredate%type)

is
      -- 申明部分
begin
      -- 過程體
      SELECT SAL,ENAME,HIREDATE INTO v_sal,v_ename,v_hiredate FROM EMP WHERE EMPNO = v_empno;

end;

使用過程：

方式1定罢，在sql window窗口中調(diào)用
declare
      v_ename  EMP.ENAME%TYPE;
      v_sal  emp.sal%type;
      v_hiredate  emp.hiredate%type;
begin
  pro_emp(7369,v_ename,v_sal,v_hiredate);
  DBMS_OUTPUT.put_line(v_ename);
   DBMS_OUTPUT.put_line(v_hiredate);
    DBMS_OUTPUT.put_line(v_sal);
end;

  方式2，在sql命令窗口中調(diào)用
SQL> var c1 varchar2(10);
SQL> var c2 number;
SQL> var c3 date;
SQL> CALL PRO_EMP(7369,:c1,:c2,:c3);
 
Method called
c1
---------
SMITH
c2
---------
800
c3
---------
1980/12/17
==========================

SQL> exec PRO_EMP(7499,:c1,:c2,:c3);
 
PL/SQL procedure successfully completed
c1
---------
ALLEN
c2
---------
1600
c3
---------
1981/2/20

8.JDBC調(diào)用過程旁瘫、函數(shù)

8.1JDBC 調(diào)用函數(shù)

  package com.xingxue.oracle.function;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;

public class JdbcFunction {

    public static void main(String[] args) {

        Connection conn = null;
        // 該對象是用于發(fā)出 執(zhí)行函數(shù)(過程)的對象
        CallableStatement prepareCall = null;
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.189:1521:orcl", "scott", "tiger");
            prepareCall = conn.prepareCall("{?=call fun_emp2(?)}");
            // 設置暫位符
            prepareCall.setObject(2, 7902);
            // 注冊一個返回的值參數(shù)
            prepareCall.registerOutParameter(1, Types.DOUBLE);
            // 發(fā)出執(zhí)行
            prepareCall.execute();
            double sal = prepareCall.getDouble(1);
            System.out.println(sal);

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

}

8.2 JDBC 調(diào)用過程

package com.xingxue.oracle.function;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;

public class JDBCPro {

    public static void main(String[] args) {
        Connection conn = null;
        CallableStatement prepareCall = null;
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.189:1521:orcl", "scott", "tiger");
            prepareCall = conn.prepareCall("{call pro_emp(?,?,?,?)}");
            // 設置 in 參數(shù)
            prepareCall.setObject(1, 7369);
            // 注冊 out 參數(shù)
            prepareCall.registerOutParameter(2, Types.VARCHAR);
            prepareCall.registerOutParameter(3, Types.DOUBLE);
            prepareCall.registerOutParameter(4, Types.DATE);
            // 執(zhí)行
            prepareCall.execute();

            Object o1 = prepareCall.getObject(2);
            Object o2 = prepareCall.getObject(3);
            Object o3 = prepareCall.getObject(4);
            System.out.println(o1);
            System.out.println(o2);
            System.out.println(o3);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

package com.xingxue.oracle.pro;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

import oracle.jdbc.OracleTypes;

public class JDBPro2 {
    public static void main(String[] args) {
        Connection conn = null;
        CallableStatement prepareCall = null;
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.189:1521:orcl", "scott", "tiger");
            prepareCall = conn.prepareCall("{call pro_emp_deptno(?,?)}");
            // 設置 in 參數(shù)
            prepareCall.setObject(1, 20);
            // 注冊 out 游標參數(shù)
            prepareCall.registerOutParameter(2, OracleTypes.CURSOR);
            // 執(zhí)行
            prepareCall.execute();

            ResultSet rs = (ResultSet) prepareCall.getObject(2);

            while (rs.next()) {
                Object object = rs.getObject("sal");
                Object object1 = rs.getObject("hiredate");
                System.out.println(object + ":" + object1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

最后編輯于：2017.12.12 16:23:15

?著作權歸作者所有,轉載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個濱河市琼蚯，隨后出現(xiàn)的幾起案子酬凳，更是在濱河造成了極大的恐慌，老刑警劉巖遭庶，帶你破解...
沈念sama閱讀 211,817評論 6贊 492
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件宁仔，死亡現(xiàn)場離奇詭異，居然都是意外死亡峦睡，警方通過查閱死者的電腦和手機翎苫，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 90,329評論 3贊 385
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來榨了，“玉大人煎谍，你說我怎么就攤上這事×耄” “怎么了呐粘？”我有些...
開封第一講書人閱讀 157,354評論 0贊 348
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵，是天一觀的道長转捕。經(jīng)常有香客問我作岖，道長，這世上最難降的妖魔是什么五芝？我笑而不...
開封第一講書人閱讀 56,498評論 1贊 284
?港島之戀（遺憾婚禮）
正文為了忘掉前任痘儡，我火速辦了婚禮，結果婚禮上枢步，老公的妹妹穿的比我還像新娘沉删。我一直安慰自己渐尿，他們只是感情好，可當我...
茶點故事閱讀 65,600評論 6贊 386
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布丑念。她就那樣靜靜地躺著涡戳，像睡著了一般。火紅的嫁衣襯著肌膚如雪脯倚。梳的紋絲不亂的頭發(fā)上渔彰，一...
開封第一講書人閱讀 49,829評論 1贊 290
城市分裂傳說
那天，我揣著相機與錄音推正，去河邊找鬼恍涂。笑死，一個胖子當著我的面吹牛植榕，可吹牛的內(nèi)容都是我干的再沧。我是一名探鬼主播，決...
沈念sama閱讀 38,979評論 3贊 408
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼尊残，長吁一口氣：“原來是場噩夢啊……” “哼炒瘸！你這毒婦竟也來了？” 一聲冷哼從身側響起寝衫，我...
開封第一講書人閱讀 37,722評論 0贊 266
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤顷扩，失蹤者是張志新（化名）和其女友劉穎，沒想到半個月后慰毅，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體隘截，經(jīng)...
沈念sama閱讀 44,189評論 1贊 303
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 36,519評論 2贊 327
?白月光啟示錄
正文我和宋清朗相戀三年汹胃，在試婚紗的時候發(fā)現(xiàn)自己被綠了婶芭。大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
茶點故事閱讀 38,654評論 1贊 340
活死人
序言：一個原本活蹦亂跳的男人離奇死亡着饥，死狀恐怖犀农，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情贱勃，我是刑警寧澤井赌，帶...
沈念sama閱讀 34,329評論 4贊 330
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布，位于F島的核電站贵扰，受9級特大地震影響仇穗，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜戚绕，卻給世界環(huán)境...
茶點故事閱讀 39,940評論 3贊 313
男人毒藥：我在死后第九天來索命
文/蒙蒙一纹坐、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧舞丛，春花似錦耘子、人聲如沸果漾。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,762評論 0贊 21
一樁弒父案谷誓，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽绒障。三九已至，卻和暖如春捍歪，著一層夾襖步出監(jiān)牢的瞬間户辱，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 31,993評論 1贊 266
情欲美人皮
我被黑心中介騙來泰國打工糙臼，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留庐镐，地道東北人。一個月前我還...
沈念sama閱讀 46,382評論 2贊 360
代替公主和親
正文我出身青樓变逃，卻偏偏與公主長得像必逆，于是被迫代替她去往敵國和親。傳聞我的和親對象是個殘疾皇子揽乱，可洞房花燭夜當晚...
茶點故事閱讀 43,543評論 2贊 349

三、ORACLE之PLSQL編程