Oracle数据库第二课——使用PL/SQL进行初步编程，了解PL/SQL的相关知识

墨蓝 2024-04-17 05:42 16阅读 0赞

**知识点：**了解PL/SQL的编程的基础；了解PL/SQL的注释；掌握PL/SQL的块结构；掌握PL/SQL的数据类型、运算符、字符集、标识符；掌握PL/SQL语句块的书写、变量的声明和赋值

# 1、PL/SQL简介 #

## 1.1 什么是PL/SQL？ ##

PL/SQL 也是一种程序语言，叫做**过程化 SQL 语言**（Procedural Language/SQL）。 PL/SQL 是 Oracle 数据库对 SQL 语句的扩展。在普通 SQL 语句的使用上增加了编程语言的特点，所以 PL/SQL 就是把数据操作和查询语句组织在 PL/SQL 代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。

它是一种高级数据库程序设计语言，该语言专门用于在各种环境下对 Oracle 数据库进行访问。由于该语言集成于数据库服务器中，所以 PL/SQL 代码可以对数据进行快速高效的处理。除此之外，可以在 Oracle数据库的某些客户端工具中，使用 PL/SQL 语言也是该语言的一个特点。

## 1.2 PL/SQL的优点 ##

使用 PL/SQL 可以编写具有很多高级功能的程序，虽然通过多个 SQL 语句也能实现同样的功能，但是相比而言，PL/SQL 具有更为明显的一些优点。

*  **支持SQL，**SQL是访问数据库的标准语言，通过SQL命令，用户可以操纵数据库的数据。PL/SQL支持所有的SQL数据操纵命令、游标控制命令、事务控制命令、SQL函数、运算符和伪列。同时PL/SQL和SQL语言紧密集成，PL/SQL支持所有的SQL数据类型和NULL值。
 *  PL/SQL 具有编程语言的特点，它能把一组 SQL 语句放到一个模块中，使其更具**模块化程序**的特点。 
 *  PL/SQL 可以采用**过程性语言控制程序**的结构，也就是说，我们可以在 PL/SQL中增加逻辑结构判断、循环等程序结构。 
 *  同其他编程语言一样，PL/SQL 可以对程序中的错误进行自动处理，使程序能够在遇到错误时不会中断，即它的**异常处理机制**。 
 *  PL/SQL 程序块具有更好的**可移植性**，可以移植到另一个 Oracle 数据库中。
 *  **更好的性能**，SQL是非过程语言，只能一条一条的执行，而PL/SQL把一个PL/SQL统一进行编译后执行，同时还可以把编译好的PL/SQL块存储起来，以备重用，减少了应用程序和服务器之间的通信时间，所以PL/SQL是高效而快速的。
 *  **安全性**，可以通过存储过程对客户机和服务器之间的应用程序逻辑进行分割，这样可以限制对ORACLE数据库的访问，数据库还可以授权和撤销其他用户的访问权利。

# 2、PL/SQL 块结构（PL/SQL 的基本语法） #

PL/SQL 程序都是以块(BLOCK)为基本单位，整个 PL/SQL 块分为 3 部分：**声明部分（用DECLARE 开头）、执行部分（以 BEGIN 开头）和异常处理部分（以 EXCEPTION 开头）**。 其中执行部分是必需的，其他两个部分可选。无论 PL/SQL 程序段的代码量有多大，其基本结构都是由这 3 部分组成的。

标准 PL/SQL 块语法：

[declare]
        --声明部分：（可选）
        --在这里声明PL/SQL用到的变量、类型及游标，以及局部的存储过程和函数
    
    begin
        --执行部分：（必须有）
        --在这里写过程及SQL语句，也就是程序的主要部分
    [exception]
        --执行异常部分：（可选）
        --错误处理
    end;

**注意：**

*  PL/SQL是一种编程语言，有自己独有的数据类型，变量声明和赋值以及流程控制语句。
 *  对大小写不敏感，但是为了规范：关键字全部大写，其余部分小写。
 *  每一条语句以分号结束。

### **实例练习：**使用PL/SQL输出欢迎语句“hello Oracle!”： ###

**实现思路：**①设置环境变量serveroutput为打开状态；②使用标准PL/SQL语句块；③使用函数dbms\_output.put\_line()可以输出参数的值。

SQL> set serveroutput on;
    SQL> DECLARE
      2     v_say varchar2(30) := 'hello Oracle !';
      3  BEGIN
      4     dbms_output.put_line(v_say);
      5     dbms_output.put_line('你好，Oracle!');
      6  END;
      7  /

**注意：**一般在**end;**（注意有分号）后加反斜杠，代表是要执行某个存储过程，如果没有反斜杠，则代表是普通换行。这种情况一般用于oracle中的命令窗口。

**界面演示效果：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70][]

# 3、代码注释 #

注释用于对程序代码的解释说明，它能够增强程序的可读性，使程序更易于理解。注释编译时被 PL/SQL 编译器忽略掉。注释有单行注释和多行注释两种情况。

*  **单行注释：**由两个链接字符“--”开始，后面跟着注释内容。
 *  **多行注释：**由“/\*”开头，由“\*/”结尾，这种用法大多数编程语言是相同的。

# 4、运算符、字符集和标识符 #

## 4.1 运算符 ##

### （1）关系运算符 ###

关系运算符比较两个表达式或值，并**返回一个布尔结果**。

<table> 
 <tbody> 
 <tr> 
 <td> 运算符 </td> 
 <td> 意义 </td> 
 </tr> 
 <tr> 
 <td> = </td> 
 <td> 等于 </td> 
 </tr> 
 <tr> 
 <td> &lt;&gt;&nbsp;,&nbsp;!=&nbsp;,&nbsp;~=&nbsp;,&nbsp;^= </td> 
 <td> 不等于 </td> 
 </tr> 
 <tr> 
 <td> &lt;&nbsp; </td> 
 <td> 小于 </td> 
 </tr> 
 <tr> 
 <td> &gt;&nbsp; </td> 
 <td> 大于 </td> 
 </tr> 
 <tr> 
 <td> &lt;= </td> 
 <td> 小于或等于 </td> 
 </tr> 
 <tr> 
 <td> &gt;= </td> 
 <td> 大于或等于 </td> 
 </tr> 
 </tbody> 
</table>

### （2） 一般运算符 ###

<table> 
 <tbody> 
 <tr> 
 <td> 运算符 </td> 
 <td> 意义 </td> 
 </tr> 
 <tr> 
 <td> + </td> 
 <td> 加号 </td> 
 </tr> 
 <tr> 
 <td> - </td> 
 <td> 减号 </td> 
 </tr> 
 <tr> 
 <td> * </td> 
 <td> 乘号 </td> 
 </tr> 
 <tr> 
 <td> / </td> 
 <td> 除号 </td> 
 </tr> 
 <tr> 
 <td> := </td> 
 <td> 赋值号 </td> 
 </tr> 
 <tr> 
 <td> =&gt; </td> 
 <td> 关系号 </td> 
 </tr> 
 <tr> 
 <td> .. </td> 
 <td> 范围运算符 </td> 
 </tr> 
 <tr> 
 <td> || </td> 
 <td> 字符连接符 </td> 
 </tr> 
 </tbody> 
</table>

### （3） 逻辑运算符 ###

<table> 
 <tbody> 
 <tr> 
 <td> 运算符 </td> 
 <td> 意义 </td> 
 </tr> 
 <tr> 
 <td> IS&nbsp;NULL </td> 
 <td> 是空值&nbsp; </td> 
 </tr> 
 <tr> 
 <td> BETWEEN </td> 
 <td> 介于两者之间 </td> 
 </tr> 
 <tr> 
 <td> IN </td> 
 <td> 在一列值中间&nbsp; </td> 
 </tr> 
 <tr> 
 <td> AND </td> 
 <td> 逻辑与 </td> 
 </tr> 
 <tr> 
 <td> OR </td> 
 <td> 逻辑或 </td> 
 </tr> 
 <tr> 
 <td> NOT </td> 
 <td> 取反,如IS&nbsp;NOT&nbsp;NULL,&nbsp;NOT&nbsp;IN </td> 
 </tr> 
 </tbody> 
</table>

# #

## 4.2 PL/SQL字符集 ##

所有的PL/SQL程序元素（比如关键字、变量名、常量等）都是由一些字符序列组合而成。而这些字符序列中的字符必须取自PL/SQL语言所允许使用的字符集，这些合法的字符集主要包括以下内容：

* 大写和小写字母：A~Z或a~z。
 * 数字：0~9。
 * 非显示的字符：制表符、空格和回车。
 * 数字符号:+，-，\*，/，>，<，=等。
 * 间隔符：包括()，\{\}，\[\]，?，!，;，:，@，\#，%，$，&等。

只有上面列出的这些符合要求的字符才可以在 PL/SQL 程序中使用，其他的字符都是非法的，不可以使用。PL/SQL 程序不区分字母的大小写，但由单引号“  **''**  ”引起来的字符串和格式化后的日期除外。

## 4.3 标识符 ##

PL/SQL 程序设计中的标识符定义与 SQL 的标识符定义的要求相同。要求和限制有：

*   标识符名不能超过 30 字符； 
 *   第一个字符必须为字母； 
 *   不分大小写； 
 *   不能用’-‘(减号); 
 *   不能是 SQL 保留字。

**提示：**一般不要把变量名与表中字段名完全一样，如果这样可能得到不正确的结果。

# 5、数据类型 #

## 5.1 基本数据类型 ##

<table> 
 <tbody> 
 <tr> 
 <td> 数据类型 </td> 
 <td style="width:137px;"> 参数 </td> 
 <td style="width:600px;"> 描述 </td> 
 </tr> 
 <tr> 
 <td> char(n)&nbsp;&nbsp; </td> 
 <td style="width:137px;"> n=1 to 2000字节 </td> 
 <td style="width:600px;"> 定长字符串，n字节长，如果不指定长度，缺省为1个字节长（一个汉字为2字节） </td> 
 </tr> 
 <tr> 
 <td>nchar(n)</td> 
 <td style="width:137px;">n=1 to 2000字节</td> 
 <td style="width:600px;">默认1字符，最大存储内容2000字节.这是一个包含Unicode格式数据的定长字符串。它的最大长度取决于国家字符集。</td> 
 </tr> 
 <tr> 
 <td> varchar2(n) </td> 
 <td style="width:137px;"> n=1 to 4000字节 </td> 
 <td style="width:600px;"> 可变长的字符串，具体定义时指明最大长度n，&nbsp;这种数据类型可以放数字、字母以及ASCII码字符集(或者EBCDIC等数据库系统接受的字符集标准)中的所有符号。&nbsp;如果数据长度没有达到最大值n，Oracle 8i会根据数据大小自动调节字段长度，&nbsp;如果你的数据前后有空格，Oracle 8i会自动将其删去。VARCHAR2是最常用的数据类型。&nbsp;可做索引的最大长度3209。 </td> 
 </tr> 
 <tr> 
 <td>nvarchar2(n)</td> 
 <td style="width:137px;">n=1 to 4000字节</td> 
 <td style="width:600px;">最大长度必须指定，最大存储内容4000字节.包含n个字符的可变长度Unicode字符数据.</td> 
 </tr> 
 <tr> 
 <td> number(m,n) </td> 
 <td style="width:137px;"> m=1 to 38 n=-84 to 127 </td> 
 <td style="width:600px;"> 可变长的数值列，允许0、正值及负值，m是所有有效数字的位数，n是小数点以后的位数。 如：number(5,2)，则这个字段的最大值是999.99， 如果数值超出了位数限制就会被截取多余的位数。 如：number(5,2)，但在一行数据中的这个字段输入575.316，则真正保存到字段中的数值是575.32。 如：number(3,0)，输入575.316，真正保存的数据是575。 </td> 
 </tr> 
 <tr> 
 <td> date </td> 
 <td style="width:137px;">无</td> 
 <td style="width:600px;"> 从公元前4712年1月1日到公元4712年12月31日的所有合法日期， Oracle 8i其实在内部是按7个字节来保存日期数据，在定义中还包括小时、分、秒。 缺省格式为DD-MON-YY，如07-11月-00 表示2000年11月7日。 </td> 
 </tr> 
 <tr> 
 <td> long </td> 
 <td style="width:137px;">无</td> 
 <td style="width:600px;"> 可变长字符列，最大长度限制是2GB，用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型。 long是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代 </td> 
 </tr> 
 <tr> 
 <td> raw(n) </td> 
 <td style="width:137px;">n=1 to 2000</td> 
 <td style="width:600px;"> 可变长二进制数据，在具体定义字段的时候必须指明最大长度n，Oracle 8i用这种格式来保存较小的图形文件或带格式的文本文件，如Miceosoft Word文档。 raw是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代 </td> 
 </tr> 
 <tr> 
 <td> long raw </td> 
 <td style="width:137px;">无</td> 
 <td style="width:600px;"> 可变长二进制数据，最大长度是2GB。long raw也是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。 </td> 
 </tr> 
 <tr> 
 <td> blob clob nclob </td> 
 <td style="width:137px;">无</td> 
 <td style="width:600px;"> 三种大型对象(LOB)，用来保存较大的图形文件或带格式的文本文件，如Miceosoft Word文档，以及音频、视频等非文本文件，最大长度是4GB。 blob 存储二进制数据，clob存储字符数据，nclob存储Unicode字符数据 </td> 
 </tr> 
 <tr> 
 <td> bfile </td> 
 <td style="width:137px;">无</td> 
 <td style="width:600px;">把非结构化的二进制数据存储在数据库以外的操作系统文件中</td> 
 </tr> 
 <tr> 
 <td>urowid&nbsp;</td> 
 <td style="width:137px;">无</td> 
 <td style="width:600px;">存储表示任何类型列地址的二进制数据</td> 
 </tr> 
 <tr> 
 <td>float</td> 
 <td style="width:137px;">无</td> 
 <td style="width:600px;">存储浮点数</td> 
 </tr> 
 </tbody> 
</table>

## 5.2 特殊数据类型 ##

### %TYPE ###

为了使一个新定义的变量与另一个已经定义了的变量(通常是表的某一列)的数据类型保持一致，Oracle提供了%Type的定义方式，当被参照的那个变量的数据类型发生改变时，那么这个新定义的变量的数据类型也会随之发生改变，这种定义方式类似与其他编程语言中的"var"，其类型不由自己决定。而是由与之关联的对象决定。这样就不用修改Sql语句了，当不能确切的知道那个变量的类型是，就采用这种方法来定义变量的数据类型。

### %ROWTYPE ###

如果一个表有较多的列，使用%ROWTYPE来定义一个表示表中一行记录的变量，比分别使用%TYPE来定义表示表中各个列的变量要简洁得多，并且不容易遗漏、出错。这样会增加程序的可维护性。

为了使一个变量的数据类型与一个表中记录的各个列的数据类型相对应、一致，Oracle提供%ROWTYPE定义方式。当表的某些列的数据类型改变了之后，这个新定义的变量的数据类型会自动跟随其改变，容易保持一致，也不用修改PL/SQL程序了。当不能确切地知道被参照的那个表的结构及其数据类型时，就只能采用这种方法定义变量的数据类型。

一行记录可以保存从一个表或游标中查询到的整个数据行的各列数据。一行记录的各个列与表中一行的各个列有相同的名称和数据类型。

### 实例练习：使用特殊数据类型，输出emp表中员工编号为7788的，部分信息 ###

**实现思路：**①确定库中是否有表emp，可以先select \* from emp；查看结果。②先声明一个员工编号的变量，v\_empno，该变量与emp表中empno这一列的数据类型一致，因此使用%TYPE数据类型，并给变量赋值。③我们希望查询编号7788员工的姓名、工资、工作时间信息，这三项信息，因此使用%ROWTYPE特殊数据类型。

SQL> DECLARE
      2     v_empno emp.empno%TYPE := 7788;
      3     v_rec emp%ROWTYPE;
      4  BEGIN
      5     select * into v_rec from emp where empno = v_empno;
      6     dbms_output.put_line('姓名：' || v_rec.ename || '工资：' || v_rec.sal || '工作时间：' || v_rec.hiredate );
      7  END;
      8  /

**注意：**

语句：**SELECT value1, value2  into 变量1，变量2  from Table1；**要求目标变量存在，在插入时会将value值存入对应的变量中，并将Table1中指定字段数据复制到Table2中 。

语句：**SELECT value1, value2  into Table2  from Table1；**要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中 。

**界面演示效果：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 1][]

**注意：**scott是一个系统已经新建好的普通用户，用户名是scott，scott常作为一个示例，在刚开始学习oracle ，常用到scott用户。scott用户下的默认表有四个：dept部门表、emp雇员表、bonus奖金表、salgrade工资登记表。

使用语句：select table\_name from all\_tables where owner='SCOTT';可以查看SCOTT用户下的默认表。

使用语句：desc 表名; 可以查看标的结构

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 2][]

# 6、变量和变量的声明、赋值 #

## 6.1 变量命名 ##

变量命名在 PL/SQL 中有特别的讲究，建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求，使得整个系统的文档在规范上达到要求。下面是建议的命名方法：

<table> 
 <tbody> 
 <tr> 
 <td> 标识符 </td> 
 <td> 命名规则 </td> 
 <td> 例子 </td> 
 </tr> 
 <tr> 
 <td> 程序变量 </td> 
 <td> V_name </td> 
 <td> V_name </td> 
 </tr> 
 <tr> 
 <td> 程序常量 </td> 
 <td> C_Name </td> 
 <td> C_company_name </td> 
 </tr> 
 <tr> 
 <td> 游标变量 </td> 
 <td> Name_cursor，cur_name </td> 
 <td> Emp_cursor，cur_emp </td> 
 </tr> 
 <tr> 
 <td> 异常标识 </td> 
 <td> E_name </td> 
 <td> E_too_many </td> 
 </tr> 
 <tr> 
 <td> 表类型 </td> 
 <td> Name_table_type </td> 
 <td> Emp_record_type </td> 
 </tr> 
 <tr> 
 <td> 表 </td> 
 <td> Name_table </td> 
 <td> Emp </td> 
 </tr> 
 <tr> 
 <td> 记录类型 </td> 
 <td> Name_record </td> 
 <td> Emp_record </td> 
 </tr> 
 <tr> 
 <td> SQL*Plus&nbsp;替代变量 </td> 
 <td> P_name </td> 
 <td> P_sal </td> 
 </tr> 
 <tr> 
 <td> 绑定变量 </td> 
 <td> G_name </td> 
 <td> G_year_sal </td> 
 </tr> 
 </tbody> 
</table>

## 6.2 变量的声明和赋值 ##

在 PL/SQL 块的可执行部分引用变量和常量前，必须先对其进行声明。变量和常量在PL/SQL 块的声明部分声明，在 PL/SQL 块的可执行部分被使用。

### 声明变量语法： ###

### variable\_name    data\_type\[(size)\]   \[ := init\_value \]; ###

*  variable\_name：表示变量名称。 
 *  data\_type：表示变量的 SQL 或 PL/SQL 数据类型。 
 *  size：指定变量的范围（可选）。 
 *  init\_value：指变量的初始值（可选）。

在PL/SQL编程中，如果在变量的定义上没有做到统一的话，可能会隐藏一些危险的错误，这样的原因主要是变量的作用范围所致。与其它高级语言类似，**PL/SQL的变量作用范围**特点是：

*  变量的作用范围是在你所引用的程序单元（块、子程序、包）内。即从声明变量开始到该块的结束。
 *  一个变量（标识）只能在你所引用的块内是可见的。 
 *  当一个变量超出了作用范围，PL/SQL引擎就释放用来存放该变量的空间（因为它可能不用了）。
 *  在子块中重新定义该变量后，它的作用仅在该块内。

### 声明常量语法： ###

### variable\_name   CONSTANT    data\_type \[(size)\]   \[ := init\_value \]; ###

CONSTANT：常量

### 实例练习：声明变量和常量，输出自我介绍 ###

SQL> DECLARE
      2     v_name varchar2(20) := 'lily' ;
      3     v_home varchar2(20);
      4     c_age number(2) := 18;
      5  BEGIN
      6     v_home := '湖北武汉';
      7     dbms_output.put_line('姓名：'||v_name ||' 家乡:'||v_home ||' 年龄：'||c_age);
      8  END;
      9  /

**界面演示效果：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 3][]

## ##

==========这里是结束分割线============

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/158f25c15ec041fc86a9eb973e5b03fe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/ffd364c762df49539e685248d8ff3f25.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/4df6d0f5d9d840468f446c67dd21c543.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/0ffa25b0a0e74cfd9f308cfcbe936e6a.png