Oracle基础——第三章 Oracle PL／SQL语言基础

偏执的太偏执、 2022-03-19 11:55 271阅读 0赞

### 本章目标 ###

PL/SQL语言基本结构

PL/SQL变量和常量的声明

PL/SQL程序的执行部分

了解PL/SQL常用函数

### PL/SQL简介 ###

1.       PL/SQL简介

a)       PL/SQL(Procedural Language/SQL，过程语言/SQL)

i.              它是结合Oracle过程语言和结构化查询语言的一种扩展语言

ii.              PL/SQL支持多种数据类型，可以使用条件语句和循环语句等控制结构

iii.              PL/SQL可用于创建存储过程、触发器和程序包，也可以用来处理业务规则、数据库事件或给SQL命令的执行添加程序逻辑

2.       PL/SQL的优点

a)        支持SQL

b)        支持面向对象编程(OOP)

c)        更好的性能

d)        可移植性

e)        与SQL集成

f)         安全性

### PL/SQL语言的基本结构 ###

1.       PL/SQL语言的基本结构

a)       PL/SQL语言是程序化程序设计语言。

块(Block)是PL/SQL程序中最基本的结构，所有PL/SQL程序都是由块组成。

b)       PL/SQL的块由变量声明、程序代码和异常处理代码3部分组成：

i.              DECLARE

1.        标记声明部分

2.        声明一些变量、常量、用户定义的数据类型及游标

ii.              BEGIN

1.        标记主程序体部分开始

2.        主程序体，在这里可以加入各种合法语句

iii.              EXCEPTION

1.        标记异常处理部分开始

2.        异常处理程序，当程序中出现错误时执行这一部分

iv.              END

标记主程序体结束部分

2.       PL/SQL字符集

i.              PL/SQL语言有效字符包括以下三类

ii.              所有大写和小写英文字母

iii.              0~9的阿拉伯数字

iv.              操作符，包括(、)、\+、\-、\*、/、<、>、!、=、@、%等

b)       PL/SQL标识符

标识符的最大长度为30个字符，不区分大小写，但建议在标识符中适当使用大小写，以增加程序的可读性。

3.       PL/SQL的运算符

a)       PL/SQL语言的运算符

i.              算术运算符

加(+)、减(-)、乘(\*)、除(/)、乘方(\*\*)和连接(||)

ii.              关系运算符

=、<>(或!=)、<、>、>=、<=、BETWEEN...AND...、IN、LIKE、IS NULL

iii.              逻辑运算符

逻辑与(AND)  、逻辑或(OR) 、逻辑非(NOT)

## PL/SQL常量和变量 ##

1.       PL/SQL常量和变量

a)        在PL/SQL程序运行时，需要定义一些变量来存放一些数据。常量和变量在使用前必须声明，可以使用DECLARE对变量进行声明，语法如下：

DECLARE

<变量名1> <数据类型1>;

<变量名2> <数据类型2>;

......

<变量名n> <数据类型n>;

b)        在DECLARE块中可以同时声明多个常量和变量。声明普通常量或变量是需要说明以下信息：

i.              常量或变量的名称

ii.              常量或变量的数据类型

2.        声明常量

a)        声明常量的基本格式如下：

b)       <常量名> constant <数据类型> := <值>;

i.              关键字constant表示声明的是常量。常量一旦定义，在以后的使用中其值不再改变。

ii.              一些固定的大小为了防止有人改变，最好定义成常量。

c)        例如

Pass\_Score constant INTEGER := 60 ;

3.        声明变量

a)        声明变量的基本格式如下：

i.              <变量名> <数据类型>\[(宽度) := <初始值>\];

变量声明是没有关键字，但要指定数据类型，宽度和初始值可以定义也可以不定义。

ii.              例如

Address VARCHAR2(30) :=‘地址未知’;

b)       PL/SQL对一个未初始化的变量，将被默认赋值为NULL

i.              例如

Address VARCHAR2(30);

4.        案例

a)        例如

SET SERVEROUTPUT ON;
    
     DECLARE
    
     Pass_Score constant INTEGER:=60;
    
     Address VARCHAR2(30):='北京海淀区';
    
     BEGIN
    
        DBMS_OUTPUT.PUT_LINE(Pass_Score);
    
        DBMS_OUTPUT.PUT_LINE(Address);
    
     END;

b)        使用SET SERVEROUTPUT ON命令设置环境变量SERVEROUTPUT为打开状态，从而使PL/SQL程序能够在SQL\*Plus中输出结果（SET SERVEROUTPUT OFF;后不输出结果）

c)        使用函数DBMS\_OUTPUT.PUT\_LINE()可以输出参数的值

![1364821231_1301.png][]

5.        默认数据库管理员用户

a)        在安装Oracle数据库时，自动创建用户名SYS和SYSTEM，用户密码在安装时被设定，并自动授予数据库管理员(DBA)的角色。

b)       SYS用户

这个账号具有所有数据库管理的功能，所有数据库数据字典中的基本表和视图都存储在用户SYS的方案(Schema)中，这些基本表和视图对于Oracle数据库的操作都非常重要。

c)       SYSTEM用户

该账号除了不能用于数据库备份及恢复、数据库升级外，可以执行其他所有的日常管理管理工作。

## PL/SQL程序的执行部分 ##

1.       PL/SQL程序的执行部分

a)       PL/SQL程序的执行部分包括

i.              赋值语句

ii.              流程控制语句

iii.              SQL语句

iv.              游标语句

2.        使用赋值语句

a)        可以在声明变量时或处理变量时设置初始值。也可以在程序的执行部分对变量进行赋值。

SET ServerOutPut ON;
    
    DECLARE
    
    TrainName VARCHAR2(30);
    
    BEGIN
    
    TrainName := 'Oracle Administration';
    
    Dbms_output.put_line(TrainName);
    
    END;

b)        运行结果如下图所示（变量名不区分大小写）

![1364821423_9380.png][]

3.        流程控制语句

a)        流程控制语句是所有过程性程序语言的关键

b)       PL/SQL的主要控制语句如下：

i.              if...then...else(elsif)

判断if正确则执行then，否则执行else(elsif为嵌套判断)

ii.              case

有逻辑的从数值中做出选择

iii.              loop...exit...end

循环控制，用判断语句执行exit

iv.              loop...exit when...end

同上，当when为真时执行exit

v.              while...loop...end

当while为真时循环

vi.              for...in...loop...end

已知循环次数的循环

c)        条件语句IF

i.              IF语句是根据条件表达式的值决定执行相应的程序段。语法结构如下：

IF<条件表达式> THEN

<执行语句>... <执行语句n>

\[ELSIF <条件表达式> THEN

<执行语句>... <执行语句n>

...

ELSE

<执行语句>\]

END IF;

ii.              其中ELSIF子句是可选项。

iii.              程序中说明一个整型变量Number，使用IF语句判断Number变量是正数、负数或0。

SET ServerOutPut ON;
    
    DECLARE
    
    Number INTEGER := -10;
    
    Begin
    
           IF Number < 0 THEN
    
             dbms_output.put_line('负数');
    
           ELSIF Number > 0 THEN
    
             dbms_output.put_line('正数');
    
           ELSE
    
             dbms_output.put_line('0');
    
           END IF;
    
    End;

iv.              执行效果如右图所示：

![1364821444_8208.png][]

d)        分支语句CASE

i.              分支语句是对指定的变量进行判断，从指定的列表中选择满足条件的行，并把该行的值作为CASE语句的结果返回。

CASE语句的语法结构如下：

CASE <变量>

WHEN <表达式1> THEN值1

WHEN <表达式2> THEN值2

......

WHEN <表达式n> THEN值n

ELSE 值n+1

ii.              声明一个整型变量varDAY和一个字符型变量Result。使用CASE语句判断varDAY是星期几。如果变量varDAY在1~7之间，则能够显示相应的星期信息，否则返回提示信息“数据越界”；

SET ServerOutPut ON;
    
    DECLARE
    
        varDAY INTEGER := 3;
    
        Result VARCHAR2(20);
    
    BEGIN
    
      Result :=Case varDAY
    
        WHEN 1 THEN '星期一'
    
        WHEN 2 THEN '星期二'
    
        WHEN 3 THEN '星期三'
    
        WHEN 4 THEN '星期四'
    
        WHEN 5 THEN '星期五'
    
        WHEN 6 THEN '星期六'
    
        WHEN 7 THEN '星期日'
    
        ELSE '数据越界'
    
        END;
    
        dbms_output.put_line(Result);
    
    END;

iii.              运行效果

![1364821484_7965.png][]

e)        循环语句LOOP...EXIT...END

i.              此语句的功能是重复执行循环体中的程序块，直到执行EXIT语句，则退出循环。

LOOP...EXIT...END语句的语法结构如下

LOOP

<程序块 1>

IF <条件表达式> THEN

EXIT

END IF

<程序块 2>

END LOOP;

ii.              计算1~4累加

SET ServerOutPut ON;
    
    DECLARE
    
      varNum INTEGER := 1;
    
      varSum INTEGER := 0;
    
    BEGIN
    
      LOOP
    
        varSum := varSum + varNum;
    
        dbms_output.put_line(varNum);
    
        IF varNum = 4 THEN
    
          EXIT;
    
        END IF;
    
        dbms_output.put_line('+');
    
        varNum := varNum + 1;
    
      END LOOP;
    
      dbms_output.put_line('=');
    
      dbms_output.put_line(varSum);
    
    END;

![1364821510_2654.png][]

f)         循环语句LOOP...EXIT WHEN...END

i.              此循环语句的功能是重复执行循环体中的程序块，直到满足EXIT WHEN后面的判断语句，则退出循环。

ii.              LOOP...EXIT WHEN...END语句的语法结构如下：

LOOP

<程序块 1>

EXIT WHEN <条件表达式>

<程序块 2>

iii.              重新实现1~4累加

SET ServerOutPut ON;
    
     DECLARE
    
       varNum INTEGER := 1;
    
       varSum INTEGER := 0;
    
     BEGIN
    
       LOOP
    
         varSum := varSum + varNum;
    
         dbms_output.put_line(varNum);
    
         EXIT WHEN varNum = 4;
    
         dbms_output.put_line('+');
    
         varNum := varNum + 1;
    
       END LOOP;
    
       dbms_output.put_line('=');
    
       dbms_output.put_line(varSum);
    
     END;

iv.              运行效果

![1364821520_3638.png][]

g)        循环语句WHILE...LOOP...END LOOP

i.              此语句的功能是当WHILE后面的语句条件成立时，重复执行循环体中的程序块。

WHILE...LOOP...END LOOP语句语法结构如下：

WHILE <条件表达式>

LOOP

<程序块>

END LOOP;

ii.              再次实现1~4累加

SET ServerOutPut ON;
    
    DECLARE
    
      varNum INTEGER := 1;
    
      varSum INTEGER := 0;
    
    BEGIN
    
      WHILE varNum <= 4
    
      LOOP
    
        varSum := varSum + varNum;
    
        dbms_output.put_line(varNum);
    
        IF varNum < 4 THEN
    
          dbms_output.put_line('+');
    
        END IF;
    
        varNum := varNum + 1;
    
      END LOOP;
    
      dbms_output.put_line('=');
    
      dbms_output.put_line(varSum);
    
    END;

![1364821541_8309.png][]

h)        循环语句FOR...IN...LOOP...END LOOP

i.              此语句定义一个循环变量，并指定循环变量的初始值和终止值。每循环一次循环变量自动加1.

ii.              FOR...IN...LOOP...END LOOP语句的语法如下

FOR <循环变量> IN <初始值>..<终止值>

LOOP

<程序块>

END LOOP;

iii.              再次实现1~4累加

SET ServerOutPut ON;
    
    DECLARE
    
      varNum INTEGER := 1;
    
      varSum INTEGER := 0;
    
    BEGIN
    
      FOR varNum IN 1..4
    
      LOOP
    
        varSum := varSum + varNum;
    
        dbms_output.put_line(varNum);
    
        IF varNum < 4 THEN
    
          dbms_output.put_line('+');
    
        END IF;
    
      END LOOP;
    
      dbms_output.put_line('=');
    
      dbms_output.put_line(varSum);
    
    END;

![1364821568_2259.png][]

4.        异常处理

a)       PL/SQL程序在运行过程中，可能会出现错误或异常现象

b)        例如：无法建立到Oracle的连接或用0做除数。好的程序应该对可能发生的异常情况进行处理，异常处理代码在EXCEPTION块中实现

c)        可以使用WHEN语句来定义异常。WHEN语句的使用方法如下：

EXCEPTION

WHEN <异常情况名> THEN

<异常处理代码>

WHEN <异常情况名> THEN

<异常处理代码>

...

WHEN OTHERS THEN

<异常处理代码>

d)        预定义异常种类

![1364821581_9358.jpg][]

e)        案例

i.              向一个NUMBER类型的变量赋值字符串时，导致异常的发生

SET ServerOutPut ON;
    
    DECLARE
    
      varNum NUMBER;
    
    BEGIN
    
      varNum := 'abc';
    
    EXCEPTION
    
      WHEN VALUE_ERROR THEN
    
        dbms_output.put_line('VALUE_ERROR');
    
    END;

![1364821600_2173.png][]

## PL/SQL常用函数 ##

1.       PL/SQL常用函数

a)        在PL/SQL编程中，会经常使用Oracle数据库提供的函数来完成用户需要的功能。

b)        除了统计函数之外，还有字符型函数、数值型函数、日期型函数等等。

2.        字符型函数

![1364821636_5538.jpg][]

3.        数值型函数

![1364821655_2653.jpg][]

4.        日期型函数

![1364821669_6106.jpg][]

## 本章总结 ##

PL/SQL语言基本结构

变量和常量的声明

流程控制语句

常用函数

<table style="border-bottom:#48c5db 5px double; border-left:#48c5db 5px double; padding-bottom:5px; background-color:black; padding-left:5px; border-spacing:0; width:100%; padding-right:30px; height:90px; border-top:#48c5db 5px double; border-right:#48c5db 5px double; padding-top:5px"> 
 <tbody> 
  <tr> 
   <td><img alt="katoon" src="https://img-my.csdn.net/uploads/201301/06/1357456262_5409.jpg" style="height:80px"></td> 
   <td style="text-align:right"><a title="新浪微博求粉 ^_^" href="http://weibo.com/wentasy" rel="nofollow"><img title="新浪微博求粉 ^_^" alt="Sina" src="https://img-my.csdn.net/uploads/201210/26/1351182273_5476.png.thumb.jpg" style="height:40px"></a> &nbsp;<a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow"><img title="我的CSDN博客——HelloWorld" alt="CSDN" src="https://img-my.csdn.net/uploads/201210/26/1351182268_8254.png.thumb.jpg" style="height:40px"></a></td> 
  </tr> 
  <tr> 
   <td style="text-align:right; font-family:microsoft yahei; color:white; font-size:12px"> <a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow">@Wentasy</a> 博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。原创博文如需转载请注明出处，谢谢 :) [CSDN博客]</td> 
  </tr> 
 </tbody> 
</table>

[1364821231_1301.png]: https://img-my.csdn.net/uploads/201304/01/1364821231_1301.png
[1364821423_9380.png]: https://img-my.csdn.net/uploads/201304/01/1364821423_9380.png
[1364821444_8208.png]: https://img-my.csdn.net/uploads/201304/01/1364821444_8208.png
[1364821484_7965.png]: https://img-my.csdn.net/uploads/201304/01/1364821484_7965.png
[1364821510_2654.png]: https://img-my.csdn.net/uploads/201304/01/1364821510_2654.png
[1364821520_3638.png]: https://img-my.csdn.net/uploads/201304/01/1364821520_3638.png
[1364821541_8309.png]: https://img-my.csdn.net/uploads/201304/01/1364821541_8309.png
[1364821568_2259.png]: https://img-my.csdn.net/uploads/201304/01/1364821568_2259.png
[1364821581_9358.jpg]: https://img-my.csdn.net/uploads/201304/01/1364821581_9358.jpg
[1364821600_2173.png]: https://img-my.csdn.net/uploads/201304/01/1364821600_2173.png
[1364821636_5538.jpg]: https://img-my.csdn.net/uploads/201304/01/1364821636_5538.jpg
[1364821655_2653.jpg]: https://img-my.csdn.net/uploads/201304/01/1364821655_2653.jpg
[1364821669_6106.jpg]: https://img-my.csdn.net/uploads/201304/01/1364821669_6106.jpg