目錄

Oracle之 ?第三篇 ?PL/SQL基礎(chǔ)

PL/SQL程序塊

?PL/SQL語(yǔ)言

PL/SQL的基本結(jié)構(gòu)?

?PL/SQL塊分類(lèi)

?? 一、PL/SQL語(yǔ)言

二、PL/SQL 常量 、變量???

合法字符

數(shù)據(jù)類(lèi)型

LOB? 數(shù)據(jù)類(lèi)型?

屬性類(lèi)型

? 運(yùn)算符

常量

? ?PL/SQL常量

1 、變量的聲明

? ? ? ?2、屬性類(lèi)型

% type

變量賦值

%type和%rowtype區(qū)別

RECORD 復(fù)合數(shù)據(jù)類(lèi)型

總結(jié)

PL/SQL語(yǔ)言篇--結(jié)構(gòu)化程序設(shè)計(jì)

PL/SQL結(jié)構(gòu)化語(yǔ)句

條件結(jié)構(gòu)

分支結(jié)構(gòu)

多分支結(jié)構(gòu)

帶臨時(shí)變量的多分支結(jié)構(gòu)

?多分支結(jié)構(gòu)CASE（四種—重點(diǎn)）

Select 語(yǔ)句中的case :

分支語(yǔ)句—case 用法3

?分支語(yǔ)句—case 用法4

? 循環(huán)結(jié)構(gòu)

1.? LOOP-EXIT-WHEN-END循環(huán)

2.? WHILE-LOOP-END循環(huán)

1、異常概述

異常的捕獲與處理:

異常概述:

1 、異常的類(lèi)型

Oracle之 ?第三篇 ?PL/SQL基礎(chǔ)

PL/SQL程序塊

• 理解PL/SQL程序塊的結(jié)構(gòu)
• 數(shù)據(jù)類(lèi)型 (掌握數(shù)據(jù)類(lèi)型%type和%rowtype的定義和應(yīng)用)
• PL/SQL語(yǔ)言基本的輸入和輸出和變量的賦值方式(select into 賦值方式).

?PL/SQL語(yǔ)言

???????? SQL 語(yǔ)言只是訪問(wèn)、操作數(shù)據(jù)庫(kù)的語(yǔ)言，并不是一種具有流程控制的程序設(shè)計(jì)語(yǔ)言，而只有程序設(shè)計(jì)語(yǔ)言才能用于應(yīng)用軟件的開(kāi)發(fā)。

????????? PL /SQL 是一種高級(jí)數(shù)據(jù)庫(kù)程序設(shè)計(jì)語(yǔ)言， 該語(yǔ)言專(zhuān)門(mén)用于在各種環(huán)境下對(duì) ORACLE 數(shù)據(jù)庫(kù)進(jìn)行訪問(wèn)。

??????????? 由于該語(yǔ)言集成于數(shù)據(jù)庫(kù)服務(wù)器中，所以PL/SQL 代碼可以對(duì)數(shù)據(jù)進(jìn)行快速高效的處理。除此之外，可以在 ORACLE 數(shù)據(jù)庫(kù)的某些客戶(hù)端工具中，使用 PL/SQL 語(yǔ)言也是該語(yǔ)言的一個(gè)特點(diǎn)。

PL/SQL的基本結(jié)構(gòu)?

---示例程序塊
---set serveroutput on
Declare 
    example_text   varchar2(100);
begin
    example_text:='歡迎來(lái)到PL/SQL世界,本例子為程序塊示例!';
    dbms_output.put_line(example_text);
exception
when others then
    dbms_output.put_line('出現(xiàn)異常了!');
 end;

• PL/SQL語(yǔ)言以塊為單位，塊中可以嵌套子塊。
• PL/SQL語(yǔ)言的組成、嵌套和執(zhí)行
• 1 一個(gè)基本的PL/SQL塊由3部分組成：
• 聲明（DECLARE）
• 可執(zhí)行部分（BEGIN）
• 異常處理部分(EXCEPTION）
• 聲明部分
• 聲明部分以關(guān)鍵字DECLARE開(kāi)始，BEGIN結(jié)束。主要用于聲明變量、常量、數(shù)據(jù)類(lèi)型、游標(biāo)、異常處理名稱(chēng)以及本地（局部）子程序定義等。
• 可執(zhí)行部分
• 以關(guān)鍵字BEGIN開(kāi)始，EXCEPTION或END結(jié)束（該部分通過(guò)變量賦值、流程控制、數(shù)據(jù)查詢(xún)、數(shù)據(jù)操縱、事務(wù)控制、游標(biāo)處理等實(shí)現(xiàn)塊的功能。
• 異常處理部分
• 異常處理部分以關(guān)鍵字EXCEPTION,該部分用于處理該塊執(zhí)行過(guò)程中產(chǎn)生的異常。
• 注意：

???? PL/SQL塊中的每一條語(yǔ)句都必須以分號(hào)結(jié)束，SQL語(yǔ)句可以多行，但分號(hào)表示該語(yǔ)句的結(jié)束。一行中可以有多條SQL語(yǔ)句，他們之間以分號(hào)分隔。

?PL/SQL塊分類(lèi)

• PL/SQL程序塊可以是一個(gè)命名的程序塊也可以是一個(gè)匿名程序塊，匿名程序塊可以用在服務(wù)器端也可以用在客戶(hù)端。
• 1、匿名塊
• 2、命名塊
• 函數(shù)(function)
• 存儲(chǔ)過(guò)程(procedure)
• 包(package)
• 觸發(fā)器(trigger)
• 3．塊的執(zhí)行
• SQL*PLUS中匿名的PL/SQL塊的執(zhí)行是在PL/SQL塊后輸入/來(lái)執(zhí)行。
• 命名的程序與匿名程序的執(zhí)行不同，執(zhí)行命名的程序塊必須使用execute關(guān)鍵字。

?? 一、PL/SQL語(yǔ)言

/*示例程序塊2   重要*/
  DECLARE
	    v_xm varchar2(8):='Jame';
		v_zym varchar2(10):='計(jì)算機(jī)';
		v_zxf number(2):=45;    /*定義變量類(lèi)型*/
	BEGIN
               UPDATE XS  SET zxf=v_zxf
		           WHERE xm=v_xm;
    		IF SQL%NOTFOUND THEN
                DBMS_OUTPUT.PUT_LINE('沒(méi)有該人，需要插入該人');
		      INSERT INTO XS(XH,XM,ZYM,ZXF)          VALUES('007',v_xm,v_zym,v_zxf);
	    END IF;
end;      /*注意區(qū)分字段變量和普通變量*/

/*示例程序塊3  重要  */

DECLARE
   row_id ROWID;
   info VARCHAR2(100);
BEGIN
   UPDATE scott.dept SET deptno=90 WHERE DNAME='RESEARCH'
         RETURNING rowid, dname||':'||to_char(deptno)||':'||loc
            INTO row_id, info;
   DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);
   DBMS_OUTPUT.PUT_LINE(info);
END;
RETURNING 子句用于檢索被修改行的信息。 

• RETURNING 子句用于檢索 INSERT 語(yǔ)句中所影響的數(shù)據(jù)行數(shù)，當(dāng) INSERT 語(yǔ)句使用 VALUES 子句插入數(shù)據(jù)時(shí)，RETURNING 字句還可將列表達(dá)式、ROWID 和 REF 值返回到輸出變量中。幾點(diǎn)限制：

/*示例程序塊3  重要*/

DECLARE
    Row_id ROWID;
    info VARCHAR2(40);
BEGIN
     delete from scott.dept where deptno=70
       RETURNING rowid, dname||':'||to_char(deptno)||':'||loc
         INTO row_id, info;
     DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);
  DBMS_OUTPUT.PUT_LINE(info);
END;
RETURNING 子句用于檢索被修改行的信息。 

/*示例程序塊3  重要*/

DECLARE
  Row_id ROWID;
  info VARCHAR2(40);
BEGIN
  INSERT INTO scott.dept VALUES (12, '財(cái)務(wù)室', '海口')
    RETURNING rowid, dname||':'||to_char(deptno)||':'||loc
     INTO row_id, info;
  DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);
  DBMS_OUTPUT.PUT_LINE(info);
END;

二、PL/SQL 常量 、變量???

合法字符

???? 在使用PL/SQL進(jìn)行程序設(shè)計(jì)時(shí)，可以使用的有效字符包括以下3類(lèi)：

• 所有的大寫(xiě)和小寫(xiě)英文字母；
• 數(shù)字0～9；
• 符號(hào)() + － * / < > = ! ~ ；：. ｀ @? % ,? ＂ # ＾ ＆ ＿ { } ? [ ]。

???? PL/SQL標(biāo)識(shí)符必須以字母開(kāi)頭，后面可以跟多個(gè)字母、數(shù)字、$、下劃線和#。最大長(zhǎng)度為30個(gè)字符，不區(qū)分大小寫(xiě)。

數(shù)據(jù)類(lèi)型

LOB? 數(shù)據(jù)類(lèi)型?

• 用于存儲(chǔ)大文本、圖像、視頻剪輯和聲音剪輯等非結(jié)構(gòu)化數(shù)據(jù)。
• LOB 數(shù)據(jù)類(lèi)型可存儲(chǔ)最大 4GB的數(shù)據(jù)。
• LOB 類(lèi)型包括：
• BLOB?? 將大型二進(jìn)制對(duì)象存儲(chǔ)在數(shù)據(jù)庫(kù)中
• CLOB?? 將大型字符數(shù)據(jù)存儲(chǔ)在數(shù)據(jù)庫(kù)中
• BFILE???? 將大型二進(jìn)制對(duì)象存儲(chǔ)在操作系統(tǒng)文件中

屬性類(lèi)型

• 用于引用數(shù)據(jù)庫(kù)列的數(shù)據(jù)類(lèi)型，以及表示表中一行的記錄類(lèi)型
• 屬性類(lèi)型有兩種：
• %TYPE? -? 引用變量和數(shù)據(jù)庫(kù)列的數(shù)據(jù)類(lèi)型
• %ROWTYPE? -? 提供表示表中一行的記錄類(lèi)型
• 使用屬性類(lèi)型的優(yōu)點(diǎn)：
• 不需要知道被引用的表列的具體類(lèi)型。
• 如果被引用對(duì)象的數(shù)據(jù)類(lèi)型發(fā)生改變，PL/SQL 變量的數(shù)據(jù)類(lèi)型也隨之改變。

? 運(yùn)算符

Oracle提供了三類(lèi)運(yùn)算符：算術(shù)運(yùn)算符、關(guān)系運(yùn)算符和邏輯運(yùn)算符。

1.? 算術(shù)運(yùn)算符

算術(shù)運(yùn)算符執(zhí)行算術(shù)運(yùn)算。算術(shù)運(yùn)算符有：

+（加）、-（減）、*（乘）、/（除）

連接運(yùn)算符：‖（連接）

其中﹢(加)和﹣(減)運(yùn)算符也可用于對(duì)DATE(日期)數(shù)據(jù)類(lèi)型的值進(jìn)行運(yùn)算。

?

PL/SQL為支持編程，還使用其他一些符號(hào)。表5.4列出了部分符號(hào)，它們是最常用的，也是使用PL/SQL的所有用戶(hù)都必須了解的。

?表5.4 部分其他常用符號(hào)

常量

• 聲明常量時(shí)需要使用constant關(guān)鍵字,并且必須在聲明時(shí)就為該常量賦值,在程序其它部分不能改變?cè)摮Ａ康闹怠?/span>
• 常量名稱(chēng) constant? 常量類(lèi)型:=值;

? ?PL/SQL常量

DECLARE
    v1 constant  varchar2(4):='示例';
    v2 constant varchar2(10):='常量';
BEGIN
      DBMS_OUTPUT.PUT_LINE(v2||' '||v1);
END;

PL/SQL程序塊的賦值符號(hào)是   :=

? 變量

1.? 變量的聲明

???? 數(shù)據(jù)在數(shù)據(jù)庫(kù)與PL/SQL程序之間是通過(guò)變量進(jìn)行傳遞的。變量通常是在PL/SQL塊的聲明部分定義的。

???? 變量名必須是一個(gè)合法的標(biāo)識(shí)符，變量命名規(guī)則如下：

(1)變量必須以字母(A～Z)開(kāi)頭

(2)其后跟可選的一個(gè)或多個(gè)字母、數(shù)字(0～9)或特殊字符$、# 或_

(3)變量長(zhǎng)度不超過(guò)30個(gè)字符

(4)變量名中不能有空格

?表5.5是否合法的變量名

1 、變量的聲明

???? 在使用變量前，首先要聲明變量。變量定義的基本格式為：

??? <變量名><數(shù)據(jù)類(lèi)型>[(寬度):=<初始值>]；

例如：定義一個(gè)長(zhǎng)度為10byte的變量count，其初始值為1，是varchar2類(lèi)型。

? count varchar2(10) :=‘1’;

? ? ? ?2、屬性類(lèi)型

• 用于引用數(shù)據(jù)庫(kù)列的數(shù)據(jù)類(lèi)型，以及表示表中一行的記錄類(lèi)型
• 屬性類(lèi)型有兩種：
• %TYPE? -? 引用變量和數(shù)據(jù)庫(kù)列的數(shù)據(jù)類(lèi)型
• %ROWTYPE? -? 提供表示表中一行的記錄類(lèi)型
• 使用屬性類(lèi)型的優(yōu)點(diǎn)：
• 不需要知道被引用的表列的具體類(lèi)型。
• 如果被引用對(duì)象的數(shù)據(jù)類(lèi)型發(fā)生改變，PL/SQL 變量的數(shù)據(jù)類(lèi)型也隨之改變。

% type

• 聲明一個(gè)變量，使它的類(lèi)型與某個(gè)變量或數(shù)據(jù)庫(kù)基本表中某個(gè)列的數(shù)據(jù)類(lèi)型一致，可以使用%TYPE復(fù)合數(shù)據(jù)類(lèi)型。
• 語(yǔ)法：
• 變量名? 表名.列名%type;
• 示例

declare

v_empno? emp.empno%TYPE;

使用%TYPE聲明具有以下兩個(gè)優(yōu)點(diǎn)：

①? 不必知道XH列的確切的數(shù)據(jù)類(lèi)型；

②? 如果改變了XH列的數(shù)據(jù)庫(kù)定義，my_xh的數(shù)據(jù)類(lèi)型在運(yùn)行時(shí)會(huì)自動(dòng)進(jìn)行修改。?

?pl/sql程序，顯示輸出scott.emp表中的部分?jǐn)?shù)據(jù)

declare
  emp_number  constant number(4):=7876;
  emp_name  varchar2(10);
  emp_job  varchar2(9);
  emp_sal  number(7,2);
begin
  select ename,job,sal
      into emp_name,emp_job,emp_sal
              from scott.emp where empno=emp_number;
               dbms_output.put_line('查詢(xún)的員工號(hào)為'||emp_number);
               dbms_output.put_line('該員工的姓名為'||emp_name);
               dbms_output.put_line('該員工的職位為'||emp_job);
               dbms_output.put_line('該員工的工資為'||emp_sal);
end;

declare
  emp_number  constant number(4):=7900;
  emp_name  scott.emp.ename%type;
  emp_job  scott.emp.job%type;
  emp_sal  scott.emp.sal%type;
begin
  select ename,job,sal
      into emp_name,emp_job,emp_sal
              from scott.emp where empno=emp_number;
               dbms_output.put_line('查詢(xún)的員工號(hào)為'||emp_number);
               dbms_output.put_line('該員工的姓名為'||emp_name);
               dbms_output.put_line('該員工的職位為'||emp_job);
               dbms_output.put_line('該員工的工資為'||emp_sal);
end;

%TYPE使用舉例

declare

? emp_name? scott.emp.ename%type;

? emp_job? scott.emp.job%type;

? emp_sal? scott.emp.sal%type;

注意：

不同schema對(duì)象的描述

???? Scott.emp

???? System.xs

如果細(xì)化到列則為

??? Scott.emp.empno

??? System.xs.xh

變量賦值

?????? SELECT? INTO 賦數(shù)用法

?????? SELECT...INTO 語(yǔ)句可以給多個(gè)值同時(shí)賦值且兩邊的數(shù)量和類(lèi)型必須相等。

?????? 必須有where 子句，使得select 語(yǔ)句從數(shù)據(jù)庫(kù)表中選出的記錄有且僅有一條。

??? select * from 表名 可能有三種情況

• 無(wú)值? （產(chǎn)生異常）
• 多值? （游標(biāo)）
• 唯一值

?%TYPE使用舉例

declare
  emp_number  constant number(4):=7900;
  emp_name  scott.emp.ename%type;
  emp_job  scott.emp.job%type;
  emp_sal  scott.emp.sal%type;
begin
  select ename,job,sal
      into emp_name,emp_job,emp_sal
              from scott.emp where empno=emp_number;
               dbms_output.put_line('查詢(xún)的員工號(hào)為'||emp_number);
               dbms_output.put_line('該員工的姓名為'||emp_name);
               dbms_output.put_line('該員工的職位為'||emp_job);
               dbms_output.put_line('該員工的工資為'||emp_sal);
end;

變量的常用賦值方式

:=

• SELECT INTO 變量集?? (注意必須有where 子句，使得選出的記錄有且僅有一條)
• ?FETCH INTO??? 變量集

賦值語(yǔ)句練習(xí)一

要求：使用PL/SQL語(yǔ)言，統(tǒng)計(jì)xs表中同學(xué)的個(gè)數(shù)并顯示出來(lái)。

declare
       v_1  number;
begin  
   select count(*) into v_1 from xs;
   dbms_output.put_line(v_1);
exception
   when others then 
   dbms_output.put_line('出現(xiàn)異常了');
end;

?PL/SQL中常用的基本數(shù)據(jù)類(lèi)型

常用數(shù)據(jù)類(lèi)型

• %用于表示屬性提示符
• 復(fù)合數(shù)據(jù)類(lèi)型
• --1 .使用 %type 定義變量
• --2. 將%rowtype獲得整個(gè)記錄的數(shù)據(jù)類(lèi)型。

%ROWTYPE復(fù)合數(shù)據(jù)類(lèi)型:

declare
one_emp  scott.emp%rowtype;
  begin
     select  *
         into one_emp
        from scott.emp where empno=7900;
         dbms_output.put_line('該員工的職位為'||one_emp.job);
          dbms_output.put_line('該員工的工資為'||one_emp.sal);
end;

注意：如何通過(guò)點(diǎn)記法取%rowtype類(lèi)型的值
?

declare
  emp_number  constant scott.emp.empno%type:=7900;
  one_emp  scott.emp%rowtype;
  begin
    select *
        into one_emp
            from scott.emp where empno=emp_number;
    dbms_output.put_line('查詢(xún)的員工號(hào)為'||emp_number);
    dbms_output.put_line('該員工的姓名為'||one_emp.ename);
    dbms_output.put_line('該員工的職位為'||one_emp.job);
     dbms_output.put_line('該員工的工資為'||one_emp.sal);
end;

%type和%rowtype區(qū)別

• 使用%type可以使變量獲得字段的數(shù)據(jù)類(lèi)型，使用%rowtype可以使變量獲得整個(gè)記錄的數(shù)據(jù)類(lèi)型。

--比較兩者定義的不同：

• 變量名 數(shù)據(jù)表.列名%type
• 變量名 數(shù)據(jù)表%rowtype。

%rowtype和%type類(lèi)型使用舉例:

DECLARE
  v_emp emp%ROWTYPE;
  v_ename emp.ename%type;
  v_sal   emp.sal%type;
BEGIN
SELECT * INTO v_emp FROM emp WHERE ename='SMITH';
  DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.sal);

  select ename,sal INTO v_ename,v_sal FROM emp WHERE empno=7900;
  DBMS_OUTPUT.PUT_LINE(v_ename||' '||v_sal);
END; 

%rowtype和%type類(lèi)型使用舉例

要求：使用PL/SQL語(yǔ)言，中的%type和%rowtype 定義xs表中的變量，顯示007同學(xué)的信息。

DECLARE
  v_student_name xs.name%TYPE;
  v_student_age xs.age%TYPE;
  v_student_gender xs.gender%TYPE;
  
  v_student_row xs%ROWTYPE;
BEGIN
  -- 使用%type定義變量
  SELECT name, age, gender INTO v_student_name, v_student_age, v_student_gender
  FROM xs
  WHERE student_id = '007';
  
  -- 使用%rowtype定義變量
  SELECT *
  INTO v_student_row
  FROM xs
  WHERE student_id = '007';
  
  -- 顯示變量值
  DBMS_OUTPUT.PUT_LINE('Using %type:');
  DBMS_OUTPUT.PUT_LINE('Name: ' || v_student_name);
  DBMS_OUTPUT.PUT_LINE('Age: ' || v_student_age);
  DBMS_OUTPUT.PUT_LINE('Gender: ' || v_student_gender);
  
  DBMS_OUTPUT.PUT_LINE('Using %rowtype:');
  DBMS_OUTPUT.PUT_LINE('Name: ' || v_student_row.name);
  DBMS_OUTPUT.PUT_LINE('Age: ' || v_student_row.age);
  DBMS_OUTPUT.PUT_LINE('Gender: ' || v_student_row.gender);
END;
/

????????在上述示例中，定義了三個(gè)變量v_student_name、v_student_age和v_student_gender，它們分別與表"xs"中的"name"、"age"和"gender"列具有相同的數(shù)據(jù)類(lèi)型。使用%type關(guān)鍵字，可以避免硬編碼數(shù)據(jù)類(lèi)型，使代碼更加靈活和可維護(hù)。

????????另外，使用%rowtype定義了一個(gè)變量v_student_row，它與表"xs"的行結(jié)構(gòu)具有相同的字段和數(shù)據(jù)類(lèi)型。通過(guò)將整行數(shù)據(jù)直接賦值給該變量，可以方便地訪問(wèn)表中所有列的值。

????????最后，使用DBMS_OUTPUT.PUT_LINE函數(shù)將變量的值輸出到控制臺(tái)顯示。運(yùn)行該示例，即可顯示"xs"表中"007"同學(xué)的信息。

RECORD 復(fù)合數(shù)據(jù)類(lèi)型

DECLARE
   --定義與 hr.employees 表中的這幾個(gè)列相同的記錄數(shù)據(jù)類(lèi)型
   TYPE RECORD_TYPE_EMPLOYEES IS RECORD(
   f_name hr.employees.first_name%TYPE,
   h_date hr.employees.hire_date%TYPE,
   j_id hr.employees.job_id%TYPE);
  --聲明一個(gè)該記錄數(shù)據(jù)類(lèi)型的記錄變量
   v_emp_record RECORD_TYPE_EMPLOYEES;
BEGIN
    SELECT first_name, hire_date, job_id INTO v_emp_record
     FROM hr.employees WHERE employee_id = &emp_id;
    DBMS_OUTPUT.PUT_LINE('雇員名稱(chēng)：'||v_emp_record.f_name
    ||' 雇傭日期：'||v_emp_record.h_date||' 崗位：'||v_emp_record.j_id);
END;

本節(jié)要點(diǎn):

• 理解PL/SQL程序塊的結(jié)構(gòu)
• 數(shù)據(jù)類(lèi)型 (掌握數(shù)據(jù)類(lèi)型%type和%rowtype的定義和應(yīng)用)
• PL/SQL語(yǔ)言基本的輸入和輸出和變量的賦值方式。

總結(jié)

• 5.1 PL/SQL語(yǔ)言基本結(jié)構(gòu)（PL/SQL塊的組成）
• 5.2 PL/SQL字符集
• 5.3 變量、常量和數(shù)據(jù)類(lèi)型

PL/SQL語(yǔ)言篇--結(jié)構(gòu)化程序設(shè)計(jì)

PL/SQL結(jié)構(gòu)化語(yǔ)句

• IF 語(yǔ)句根據(jù)條件執(zhí)行一系列語(yǔ)句，有三種形式：IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIF

條件結(jié)構(gòu)

1.? IF邏輯結(jié)構(gòu)

???? (1)? IF-THEN-ENDIF結(jié)構(gòu)

???? (2)? IF-THEN-ELSE-ENDIF結(jié)構(gòu)

???? (3)? IF-THEN-ELSIF結(jié)構(gòu)

???? (4)? CASE結(jié)構(gòu)

1.? IF邏輯結(jié)構(gòu)

(3)? IF-THEN-ELSIF-THEN-ELSE

語(yǔ)法格式：

??? ?IF 條件 1 THEN? ?????

? Run_expression1?????????????

? ELSIF條件 ?2 THEN ?

? Run_expression2???????????? ?

? ELSE

? Run_expression3??? ? ????????

? END IF

分支結(jié)構(gòu)

1、要求：向?qū)W生表中添加 記錄，值為’007’ ‘Jame’ ‘計(jì)算機(jī)’ 45，并說(shuō)明是否成功

DECLARE
      v_xm varchar2(8):='Jame';
     v_zym varchar2(10):='計(jì)算機(jī)';
     v_zxf number(2):=45;    /*定義變量類(lèi)型*/
  BEGIN
 INSERT INTO XS(XH,XM,ZYM,ZXF)        VALUES('007',v_xm,v_zym,v_zxf);
 IF SQL%FOUND THEN
       DBMS_OUTPUT.PUT_LINE('操作成功');
ELSE
       DBMS_OUTPUT.PUT_LINE('沒(méi)有插入該人');
 END IF;
END; 

??? 2? 、要求：針對(duì)scott.emp表，計(jì)算7788號(hào)雇員的應(yīng)交稅金情況，薪金>=3000,應(yīng)繳稅金為薪金的0.08,薪金在1500和3000之間，應(yīng)繳薪金的0.06，其它應(yīng)繳0.04.

declare 
   v_sal  scott.emp.sal%type;
   v_tax   scott.emp.sal%type;
begin
   select sal into v_sal  from  scott.emp  where empno=7788;
   if  v_sal>=3000  then
      v_tax:=v_sal*0.08;
    elsif  v_sal>=1500  then  
       v_tax:=v_sal*0.06;
    else
        v_tax:=v_sal*0.04;
    end if;  
    DBMS_OUTPUT.PUT_LINE('應(yīng)繳稅金:'||v_tax);
  end; 

多分支結(jié)構(gòu)

3 、要求：涉及表為scott.emp，輸入一個(gè)員工號(hào)，修改該員工的工資，如果該員工為10號(hào)部門(mén)(deptno)，則要求工資增加100；若為20號(hào)部門(mén)，要求工資增加150；若為30號(hào)部門(mén)，工資增加200；否則增加300。

DECLARE
     v_deptno scott.emp.deptno%type;
     v_zj NUMBER(4); 
     v_empno  scott.emp.empno%type;
BEGIN
      v_empno:='7788';
     SELECT deptno INTO v_deptno FROM scott.emp WHERE empno=v_empno;
     IF        v_deptno=10          THEN v_zj:=100;
     ELSIF v_deptno=20          THEN v_zj:=150;
     ELSIF v_deptno=30          THEN v_zj:=200;
     ELSE    v_zj:=300;
  END IF; 
  UPDATE scott.emp SET sal=sal+v_zj  WHERE empno='7788';
END;

declare 
   v_deptno  scott.emp.deptno%type;
   v_zl  scott.emp.sal%type;
begin
   select deptno into v_deptno  from  scott.emp  where empno=&&a;
   if v_deptno=10 then
       v_zl:=100;
     elsif   v_deptno=20 then
        v_zl:=150;
          elsif   v_deptno=30 then
          v_zl:=200;
     else
     v_zl:=300;
     end if; 
     
     UPDATE scott.emp SET sal=sal+v_zl  WHERE empno=&a;
     end;

多分支結(jié)構(gòu)

• 臨時(shí)變量&&a? 和&a

?多分支結(jié)構(gòu)CASE（四種—重點(diǎn)）

四種CASE語(yǔ)句

1、搜索型CASE語(yǔ)句

語(yǔ)法格式：

? CASE ??變量名

?????? WHEN? 變量值?? THEN???? 處理語(yǔ)句 1 ；

?????? WHEN 變量值?? THEN???? 處理語(yǔ)句2；

????? ELSE ? ???處理語(yǔ)句n+1? ；

? END CASE;

5.4 PL/SQL基本程序結(jié)構(gòu)

例: 關(guān)于成績(jī)等級(jí)制和百分制的相互轉(zhuǎn)換。

---例: 關(guān)于成績(jī)等級(jí)制和百分制的相互轉(zhuǎn)換。
---簡(jiǎn)單case表達(dá)式
declare 
  grade  varchar2(4):='良好';
begin
  case grade
    when '優(yōu)秀' then dbms_output.put_line('大于等于90');
    when '良好' then dbms_output.put_line('大于等于80，小于90');
    when '及格' then dbms_output.put_line('大于等于60，小于80');
    else dbms_output.put_line('不及格');
 end case;
end;    

等值比較的CASE語(yǔ)句:

DECLARE
   v_deptno emp.deptno%type;
   v_increment NUMBER(4);
   v_empno  emp.empno%type;
BEGIN
  v_empno:=&x;
 SELECT deptno INTO v_deptno FROM emp WHERE empno=v_empno;
  CASE v_deptno  
    WHEN 10 THEN v_increment:=100;
    WHEN 20 THEN v_increment:=150;
    WHEN 30 THEN v_increment:=200;
    ELSE  v_increment:=300;
END CASE;
UPDATE emp SET sal=sal+v_increment WHERE empno=v_empno; 
END; 

• 完善程序
• Update語(yǔ)句之后追加如下語(yǔ)句：
• if SQL%FOUND then
• ?dbms_output.put_line('更改成功');
• select sal into v_sal from scott.emp where empno='7788';
• ?dbms_output.put_line(v_sal);
• end? if;

1.? 搜索型CASE語(yǔ)句

語(yǔ)法格式：

? CASE ?

?????? WHEN ??關(guān)系表達(dá)式 1? THEN???? 處理語(yǔ)句 1 ；

?????? WHEN?? 關(guān)系表達(dá)式 2? THEN???? 處理語(yǔ)句2；

????? ELSE ? ???處理語(yǔ)句n+1? ；

? END CASE;

比較與簡(jiǎn)單型case的區(qū)別

5.4 PL/SQL基本程序結(jié)構(gòu)和語(yǔ)句

例: 關(guān)于成績(jī)等級(jí)制和百分制的相互轉(zhuǎn)換。

---例: 關(guān)于成績(jī)等級(jí)制和百分制的相互轉(zhuǎn)換。
---搜索case表達(dá)式
declare 
  score  int:=91;
begin
  case 
    when score>=90 then dbms_output.put_line('優(yōu)秀');
    when score>=80 then dbms_output.put_line('良好');
    when score>=60 then dbms_output.put_line('及格');
    else dbms_output.put_line('不及格');
 end case;
end; 	

3、嵌入到SELECT語(yǔ)句執(zhí)行復(fù)雜任務(wù)的CASE

??? if 語(yǔ)句不同,case 語(yǔ)句可以用在select語(yǔ)句中，用于在檢索數(shù)據(jù)的同時(shí)對(duì)數(shù)據(jù)進(jìn)行判斷并返回判斷結(jié)果。

??? 如下圖:? 通過(guò)case語(yǔ)句顯示每一位同學(xué)的獲得學(xué)分情況。

嵌入到SELECT語(yǔ)句執(zhí)行復(fù)雜任務(wù)的CASE

? 預(yù)備知識(shí)（注意列表中別名的用法）

----構(gòu)建需求列

----- ,‘獲得學(xué)分情況’ 列需要分情況處理（case）

• select? xh,xm,zxf, (case 分支語(yǔ)句)? as '獲得學(xué)分情況’? from xs;

Select 語(yǔ)句中的case :

實(shí)現(xiàn):

• select? xh,xm,zxf,

注意：

??? 1.? 整個(gè)case 語(yǔ)句沒(méi)有標(biāo)點(diǎn)符號(hào)

???? 2.? case 語(yǔ)句的結(jié)束用end 而非end case

???? 3.? then 之后沒(méi)有dbms_output.put_line().

???? 4.? as 之后無(wú)引號(hào).

思考：如何把上例的結(jié)果保存起來(lái)（以表的形式）????????

• create table t01 as select xh,xm,zym,
  (case
      when zxf>50 then 'gao'
        when zxf>=40 then 'zhong'
         else '學(xué)分不夠，需繼續(xù)'
  end) as
  獲得學(xué)分情況  from xs;
  

Select 語(yǔ)句中的case :

• 對(duì)于學(xué)生借閱圖書(shū)項(xiàng)目，檢驗(yàn)圖書(shū)是否過(guò)期：
• 要求具體說(shuō)明：
• 過(guò)期
• 沒(méi)過(guò)期
• 或者有借閱圖書(shū) ????三種具體情況
• 可以用帶日期型的表替代借閱日期列（scott.emp）

  SQL> select Months_Between(sysdate,to_date('20151001','yyyymmdd')) from dual;
   
  MONTHS_BETWEEN(SYSDATE,TO_DATE
  ------------------------------
                1.04413045101553
   
  SQL> select trunc(sysdate-to_date('20181001','yyyymmdd')) 天數(shù) from dual;
   

  

分支語(yǔ)句—case 用法3

檢驗(yàn)圖書(shū)是否過(guò)期：

select empno,ename,job,hiredate,

(case

??? when trunc(sysdate-HIREDATE)>360? then '過(guò)期'

??? when hiredate is null then '沒(méi)借書(shū)'

???? else? '沒(méi)過(guò)期'

??? end)

?? as 是否過(guò)期 from scott.emp;

思考：如何在顯示是否過(guò)期的同時(shí)，顯示根據(jù)過(guò)期天數(shù)確立的罰款數(shù)。

?分支語(yǔ)句—case 用法4

• 嵌入到PL/SQL程序語(yǔ)句（如賦值語(yǔ)句）的CASE

FOR循環(huán)

LOOP-EXIT-END LOOP循環(huán)

WHILE循環(huán)

? 循環(huán)結(jié)構(gòu)

1.? LOOP-EXIT-WHEN-END循環(huán)

? LOOP

? Run_expression?????????????????

? EXIT WHEN Boolean_expression?????

END LOOP;

【例5.10】求10的階乘。

---【例5.10】求10的階乘。
	DECLARE 
           s NUMBER:=1;
	    n  NUMBER:=2;
	BEGIN
		LOOP
		        s:=s*n;
		        n:=n+1;
		        exit when n>10;
		END LOOP;
		dbms_output.put_line(to_char(s));
	END;

2.? WHILE-LOOP-END循環(huán)

語(yǔ)法格式：

???? WHILE Boolean_expression

? LOOP

? Run_expression???????????????

? END LOOP;

【例5.11】用WHILE-LOOP-END循環(huán)結(jié)構(gòu)求10的階乘。

---【例5.11】用WHILE-LOOP-END循環(huán)結(jié)構(gòu)求10的階乘。
DECLARE 
           s NUMBER:=1;
		n NUMBER:=2;
	BEGIN
	    while n<=10
                 LOOP
		        s:=s*n;
		        n:=n+1;
		 end LOOP;
		dbms_output.put_line(to_char(s));
	END;

結(jié)構(gòu)化設(shè)計(jì)之循環(huán)結(jié)構(gòu)

4.? FOR-IN-LOOP-END循環(huán)

語(yǔ)法格式：

? FOR count IN count_1..count_n?? ? /*定義跟蹤循環(huán)的變量*/

? LOOP

? ?? /*執(zhí)行循環(huán)體*/

? END LOOP;

【例5.12】用FOR-IN-LOOP-END循環(huán)結(jié)構(gòu)求10的階乘。

---【例5.12】用FOR-IN-LOOP-END循環(huán)結(jié)構(gòu)求10的階乘。
DECLARE 
           s NUMBER:=1;
	   n NUMBER:=2;
	BEGIN
		for n in 2..10
                 Loop
		      s:=s*n;
		end loop;
		dbms_output.put_line(to_char(s));
	END;

For 循環(huán)中的逆序:

DECLARE 
     s NUMBER:=1;
	   n NUMBER:=2;
 begin
  for n in reverse 1..10
		loop
      s:=s*n;
      end loop;
		dbms_output.put_line(to_char(s));
	END;

水仙花數(shù)

--- 水仙花數(shù)
declare
       i int;a int;b int;c int;
begin  
   for i in 100..999
    loop
       a:= trunc(i/100);
       b:=trunc(i/10) mod 10;
       c:=i mod 10;
      -- dbms_output.put_line(a||' '||b||' '||c);
       if (i=a*a*a+b*b*b+c*c*c) then
         dbms_output.put_line(i);
       end if;
    end loop;   
end;

TRUNC(x,y)
功能: 計(jì)算截尾到y位小數(shù)的x值. y缺省為0,結(jié)果變?yōu)橐粋€(gè)整數(shù)值.

習(xí)題:

• 習(xí)題：針對(duì)xs_kc,表如何通過(guò)select語(yǔ)句顯示學(xué)生信息時(shí)，顯示每個(gè)學(xué)生的成績(jī)所屬的級(jí)別？示例查詢(xún)結(jié)果如下：

1、異常概述

• Oracle錯(cuò)誤處理機(jī)制
• 一個(gè)優(yōu)秀的程序都應(yīng)該能夠正確處理各種出錯(cuò)情況，并盡可能從錯(cuò)誤中恢復(fù)。任何 ORACLE 錯(cuò)誤（ORA-xxxxx 形式的 Oracle 錯(cuò)誤號(hào)）

異常的捕獲與處理:

• 異常處理器的基本形式為
• EXCEPTION
• ?WHEN 異常1? OR? 異常2????? THEN ??語(yǔ)句1;
• WHEN 異常3??????????????????????? THEN?? 語(yǔ)句2;

????? ……

• 注意：
• 一個(gè)異常處理器可以捕獲多個(gè)異常，只需要在WHEN子句中用OR連接即可；
• 當(dāng)數(shù)據(jù)庫(kù)或PL/SQL在運(yùn)行時(shí)發(fā)生錯(cuò)誤時(shí)，一個(gè)異常被PL/SQL自動(dòng)拋出。
• 一個(gè)異常只能被一個(gè)異常處理器捕獲，并進(jìn)行處理。

異常概述:

1 、異常的類(lèi)型

• 預(yù)定義的Oracle異常
• 用戶(hù)定義的異常

異常處理部分 ：?

【例】下面是一個(gè)異常處理的例子：

SET SERVEROUTPUT ON;
DECLARE
    x NUMBER;
BEGIN
    x:= 'aa123';
EXCEPTION
   WHEN VALUE_ERROR THEN
     DBMS_OUTPUT.PUT_LINE('數(shù)據(jù)類(lèi)型錯(cuò)誤');
END;

預(yù)定義的異常 p213

?與數(shù)據(jù)庫(kù)有關(guān)的一段異常:查找“李明”同學(xué)的學(xué)號(hào) :

---與數(shù)據(jù)庫(kù)有關(guān)的一段異常:查找“李明”同學(xué)的學(xué)號(hào) ★
DECLARE
    v_result xs.xm%TYPE;
    BEGIN
    SELECT xh INTO v_result
             FROM xs 
             WHERE xm='李明';
    DBMS_OUTPUT.PUT_LINE('The student number is '||v_result);
    EXCEPTION
           WHEN TOO_MANY_ROWS THEN
              DBMS_OUTPUT.PUT_LINE('There has TOO_MANY_ROWS error');
           WHEN NO_DATA_FOUND THEN
             DBMS_OUTPUT.PUT_LINE('There has NO_DATA_FOUND error');
          WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('錯(cuò)誤情況不明');
    END;

查詢(xún)名為SMITH的員工工資，如果該員工不存在，則輸出“There is not such an employee!”；如果存在多個(gè)同名的員工，則輸出'There has too_many_rows error!”

declare 
 v_sal scott.emp.sal%type;
begin
    select sal into v_sal from scott.emp where ename='SMITH';
   DBMS_OUTPUT.PUT_LINE(v_sal);
  EXCEPTION
       WHEN NO_DATA_FOUND THEN
             DBMS_OUTPUT.PUT_LINE(‘沒(méi)有返回?cái)?shù)據(jù)');
       WHEN TOO_MANY_ROWS THEN
             DBMS_OUTPUT.PUT_LINE(‘返回多行匹配數(shù)據(jù)');
 end;

• 注意：使用others異常可以借助兩個(gè)函數(shù)來(lái)說(shuō)明捕捉到的異常的類(lèi)型-----SQLCODE和SQLERRM

DECLARE
      v_result number;
    BEGIN
      SELECT xm INTO v_result
        FROM xs 
        WHERE xh='010010';
      DBMS_OUTPUT.PUT_LINE('The student name is'||v_result);
      EXCEPTION
           WHEN OTHERS THEN
               DBMS_OUTPUT.PUT_LINE('the sqlcode is'||SQLCODE);
               DBMS_OUTPUT.PUT_LINE('the sqlERRM is'||SQLERRM);
    END;

用戶(hù)自定義的異常

• 異常中涉及的步驟
• 　　 -　 聲明異常
• 　　 -　 引發(fā)異常
• 　　 -　 處理異常
• 用戶(hù)自定義異常必須在聲明部分進(jìn)行聲明。
• 當(dāng)異常發(fā)生時(shí)，系統(tǒng)不能自動(dòng)觸發(fā)，需要用戶(hù)使用RAISE語(yǔ)句。
• 在異常處理部分捕捉并處理異常。

2.? 用戶(hù)定義異常

?? 語(yǔ)法格式：

?? delcare

???????? 異常處理名稱(chēng)?? exception;

?begin

?? …..

? EXCEPTION

? ??? WHEN 異常處理名稱(chēng) THEN

? ????????? 語(yǔ)句1;

??????? WHEN THEN

? ?????? ? 語(yǔ)句2;

? ??? WHEN OTHERS THEN

? ?????????? 語(yǔ)句3;

? END;

【例】修改7844員工的工資(增加1000)，保證修改后工資不超過(guò)6000。

---修改7844員工的工資(增加1000)，保證修改后工資不超過(guò)6000。
DECLARE
  e_1 EXCEPTION;
  v_sal scott.emp.sal%TYPE;
BEGIN
     UPDATE scott.emp SET sal=sal+1000 WHERE empno=7844 ;
      select sal into v_sal from scott.emp  where empno=7844;
     --- 取出更新后的工資
     IF v_sal>4000 THEN 
           RAISE e_1;
     END IF;
    EXCEPTION
    WHEN e_1 THEN
            DBMS_OUTPUT.PUT_LINE('The salary is too large!');
    ROLLBACK;
END; 

異常處理練習(xí)：

練習(xí)：更新scott.emp中7788員工的工資，若沒(méi)有成功，請(qǐng)拋出異常。

declare
    v_empno  scott.emp.empno%type;
    no_result  EXCEPTION;
Begin
   v_empno:=&a;
    update scott.emp  set sal=sal+100 where empno=v_empno;
    if  SQL%NOTFOUND   then
         raise no_result;
    end if;
exception
    when  no_result then
       dbms_output.put_line('數(shù)據(jù)沒(méi)有更新');
    when others then
       dbms_output.put_line(sqlcode||'   '||sqlerrm);
end;

習(xí)題 :

顯示學(xué)生借書(shū)系統(tǒng)，是否借書(shū)過(guò)期，如過(guò)期，請(qǐng)給出需要繳納的罰款數(shù)。

select deptno,empno,sal,hiredate ,(case
  when trunc(sysdate-hiredate)>5000 then ‘過(guò)期'
  when hiredate is null then '沒(méi)借書(shū)'
    else ''
   end)  as 是否借書(shū),
  (case
  when trunc(sysdate-hiredate)>5000 then    trunc(sysdate-hiredate)*0.1
      else 0
      end) as  罰款
 from scott.emp；

異常處理練習(xí)文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-781873.html

--- 練習(xí)：更新scott.emp中7788員工的工資，若沒(méi)有成功，請(qǐng)拋出異常。

declare
    v_empno  scott.emp.empno%type;
    no_result  EXCEPTION;
Begin
   v_empno:=&a;
    update scott.emp  set sal=sal+100 where empno=v_empno;
    if _______then
___________________end if;
exception
_________________
dbms_output.put_line('數(shù)據(jù)沒(méi)有更新');
    when others then
       dbms_output.put_line(sqlcode||'   '||sqlerrm);
end;

到了這里，關(guān)于Oracle之 第三篇 PL/SQL基礎(chǔ)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！