Oracle专题11之PLSQL入门

发布时间：2020-07-16 09:55:04 作者：糖醋白糖
来源：网络阅读：2748

1、PLSQL工具

PL/SQL Developer的简介：PL/SQL Developer是一种专门用于开发、测试、调试和优化Oracle PL/SQL的一种工具，更是一个非常好用的Oracle的管理工具。
官网下载地址：https://www.allroundautomations.com/bodyplsqldevreg.html （像普通软件安装即可）
2、PLSQL的简介

a、什么是PL/SQL？
PL/SQL是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。
PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点。
PL/SQL是面向过程的语言，PL/SQL是SQL语言的扩展。

- 不同数据库对SQL语言的扩展：
Oracle：PL/SQL; SQL Server：Transac-SQL(T-SQL)。
b、PLSQL的特点？
PL/SQL是Oracle系统的核心语言，现在Oracle的许多部件都是由PL/SQL写成的。PLSQL具有简单、高效、灵活、实用的特点。
3、PLSQL的语法结构

a、PL/SQL块
块（block）：是PL/SQL的基本程序单元。
PL/SQL块由三部分构成：声明部分、执行部分、异常处理部分。
声明部分（DECLARE）：声明变量、常量、复杂数据类型、游标等；执行部分（BEGIN）：PL/SQL语句和SQL语句；异常处理部分（EXCEPTION）：处理执行错误。
END; -- 块结束标记。（执行部分是必须的，声明部分和异常处理部分是可选的。）
b、第一个PL/SQL程序
示例：打印hello everyone!
1、使用Command Window窗口：在PLSQL Developer工具中，使用菜单栏中的New -> Command Window打开命令行：
在打开的命令行中输入如下内容：
注意：当使用DBMS_OUPUT包输出信息时，需要设置SQL*Plus环境serveroutput的值为ON。（使用 /后，键入回车键执行PL/SQL程序）
```
SQL> begin dbms_output.put_line('hello everyone!');
 end;
    /
PL/SQL procedure successfully completed

SQL> set serveroutput on
SQL> begin dbms_output.put_line('hello everyone!');
end;
 /
hello everyone!
PL/SQL procedure successfully completed
```
2、使用SQL Window窗口：在PLSQL Developer工具中，使用菜单栏中的New ->SQL Window打开命令行：

在打开的命令行中输入如下内容：

begin dbms_output.put_line('hello everyone!');
end;

键入F8执行后，output窗口中显示打印行：（不需要像Command Window窗口中设置serveroutput的值）
需要注意，SQL Window窗口只能执行PL/SQL或者是SQL代码。（不能执行SQLPlus命令）
c、PL/SQL块的分类
匿名块：动态构造、只能执行一次。（如上的第一个PL/SQL程序）
子程序：存储在数据库中的存储过程、函数以及包等。当在数据库上建立好后可以在其他程序中调用它们。
触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。
4、PLSQL的变量类型

a、标识符
当编写PL/SQL块时，为了临时存储数据，需要定义变量和常量。那么变量和常量的定义是需要满足标识符的限制要求的：
1、标识符名不能超过30个字符；2、第一个字符必须为字母；3、不分大小写；4、不能用‘-’
（减号）。
注意：尽量不把变量名声明和表中字段名一样。
b、变量的命名方法
为了提高代码的可读性，建议遵从以下编码规则：

标识符	命令规则	例子
程序变量	v_name	v_sal
程序常量	c_name	c_pi
游标变量	name_curror	emp_curror
异常标识	e_name	e_integrity_error
记录类型	name_record	emp_record

c、变量的类型

数值类型：NUMBER(p, s)（用于定义固定长度的整数和浮点数）以及子类型INT、FLOAT等。
字符类型：CHAR(n)（定长字符串）、VARCHAR2(n)。（变长的字符串）
日期类型：DATE。
布尔类型：BOOLEAN。（布尔类型值有false，true和nul，为PLSQL数据类型，表中的列不能够采用此类型）

d、变量的大小写规则

当编写sql语句和PL/SQL语句时，即可以采用大写格式，也可以采用小写格式。但是为了程序的可读性，应该尽量按照以下规则：
SQL关键字采用大写格式，如SELECT，UPDATE等。
PL/SQL关键字采用大写格式，如DECLARE，BEGIN，END等。
数据类型采用大写格式，如INT、DATE等。
标识符和参数采用小写格式，如：v_sal等。
数据库对象和列采用小写格式，如：emp，sal等。
e、PLSQL中的注释
单行注释： -- 注释内容
多行注释：/ 注释内容 /
f、PL/SQL程序综合示例

使用SQL Window窗口，在窗口中键入如下代码：

DECLARE
    v_name VARCHAR2(10);
    v_sal NUMBER(7,2);
    v_hiredate DATE;
    c_tax_rate CONSTANT NUMBER(3,2) := 0.02;
    v_tax_sal NUMBER(7,2);
    v_valid BOOLEAN DEFAULT TRUE;
BEGIN
    SELECT ename, sal, hiredate INTO v_name, v_sal, v_hiredate FROM emp WHERE empno = 7369;
    --计算所得税
    v_tax_sal := v_sal * c_tax_rate;
    --打印输出
    DBMS_OUTPUT.put_line(v_name ||'的工资是：' || v_sal ||'， 雇员日期是：' || v_hiredate || '，所得税是：' || v_tax_sal);
F v_valid THEN 
        DBMS_OUTPUT.put_line('已核实');
        END IF;
END;

需要注意，在PL/SQL程序中变量和常量的赋值需要使用“:=”的方式，而数据库中的数据的赋值需要使用INTO关键字。
布尔类型为PLSQL特有的数据类型，不能把数据库中检索出来的数据赋值给布尔类型，主要用于逻辑判断。
5、PLSQL的引用型变量和记录型变量

a、为什么要使用引用型变量和记录型变量
在许多情况下，PL/SQL变量可以用来存储在数据库表中的数据。在这种情况下，变量应该拥有与表列相同的类型。

举例：使用SQL Window窗口。

DECLARE 
    v_name varchar2(10);
    v_sal number(7,2);
BEGIN
    SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = 7788;
    --打印姓名和薪水
    DBMS_OUTPUT.PUT_LINE(v_name || '的工资是：' || v_sal);
END;

这种情况建议使用引用型变量（使用%TYPE）或者记录型变量（使用%ROWTYPE）而不是将变量类型硬性编码。
b、引用型变量
引用型变量：是指其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同。

举例：

DECLARE 
    --定义引用型变量
    v_name emp.ename%TYPE;
    v_sal emp.sal%TYPE;
BEGIN
    --将7788的姓名和工资赋值给定义的引用型变量
    SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = 7788;
    --打印姓名和薪水
    DBMS_OUTPUT.put_line(v_name || '的工资是：' || v_sal);
END;

c、记录型变量

PL/SQL提供%ROWTYPE操作符，返回一个记录类型，其数据类型和数据库表的数据结构相一致。
举例：记录型变量，emp_record emp%ROWTYPE;、记录型变量分量的引用：emp_record.ename := 'ROSE';

示例：

DECLARE          
    --定义记录型变量
    emp_record emp%ROWTYPE;
BEGIN 
    --将7788的员工信息赋值给了记录型变量emp_record
    SELECT * INTO emp_record FROM emp WHERE empno = 7788;
    --打印姓名和薪水
    DBMS_OUTPUT.PUT_LINE(emp_record.ename || '的工资是：' || emp_record.sal);
END;

6、PLSQL的运算符

a、算术运算符

运算符	意义
+	加号
-	减号
*	乘号
/	除号
**	乘方

示例：

BEGIN 
    dbms_output.put_line(10 + 2);
    dbms_output.put_line(10 - 2);
    dbms_output.put_line(10 * 2);
    dbms_output.put_line(10 / 2);
    dbms_output.put_line(10 ** 2);
END;

b、关系运算符

运算符	意义
=	等于
<>, !=, ~=, ^=	等于
<	小于
>	大于
<=	小于等于
>=	大于等于

示例：

DECLARE
    v_num1 NUMBER(2) := &n1;
    v_num2 NUMBER(2) := &n2;
BEGIN
    IF(v_num1 = v_num2) THEN
                        DBMS_OUTPUT.put_line('num1 等于 num2');
    ELSIF(v_num1 < v_num2) THEN
                        DBMS_OUTPUT.put_line('num1 小于 num2');
    ELSIF(v_num1 > v_num2) THEN
                        DBMS_OUTPUT.put_line('num1 大于 num2');
    END IF;      
    IF(v_num1 <> v_num2) THEN 
                        DBMS_OUTPUT.put_line('num1 不等于 num2');
    END IF;
END;

注意：&n1、&n2为键盘输入值。
c、比较运算符

运算符	意义
IS NULL	是空值
BETWEEN...AND	介于两者之间
IN	等于列表中的某个值

示例：

DECLARE
    v_num1 NUMBER(2) := &n1;
BEGIN
    IF(v_num1 BETWEEN 5 AND 10) THEN
                        DBMS_OUTPUT.put_line('num1 介于5 到10之间');
    ELSE 
                        DBMS_OUTPUT.put_line('num1 不在5到10之间');
    END IF;

    IF(v_num1 IN(3, 8, 10)) THEN
                        DBMS_OUTPUT.put_line('num1 等于3, 8 , 10中的一个值');
    ELSE
                        DBMS_OUTPUT.put_line('num1 不等于3, 8 , 10中的一个值');
    END IF;

    IF(v_num1 IS NULL) THEN 
                        DBMS_OUTPUT.put_line('num1 为空');
    ELSE
                        DBMS_OUTPUT.put_line('num1 不为空');
    END IF;
END;

d、逻辑运算符

逻辑符	意义
AND	逻辑与
NOT	逻辑或
OR	取反，如IS NOT NULL, NOT IN

示例：

DECLARE
    v_b1 BOOLEAN := &n1;
    v_b2 BOOLEAN := &n2;
BEGIN
    IF(v_b1 AND v_b2) THEN
                    DBMS_OUTPUT.put_line('AND --true');
    END IF;

    IF(v_b1 OR v_b2) THEN
                    DBMS_OUTPUT.put_line('OR --true');
    END IF;

    IF(NOT v_b1) THEN
                    DBMS_OUTPUT.put_line('b_v1取反为TRUE');      
    END IF;
END;

e、字符以及数字运算的特点

空值加上数字仍然是空值：NULL + <数字> = NULL。
空值加上（连接）字符，结果是字符：NULL || <字符串> = <字符串>
7、PLSQL的条件控制语句

- 附加说明：PL/SQL流程控制语句分类：
条件控制语句（条件分支语句）：IF语句和CASE语句。
循环语句：LOOP语句。
顺序语句：GOTO语句、NULL语句。

a、条件控制语句：if语句
分为三种条件分支语句：简单条件判断：IF-THEN；二重条件分支：IF-THEN-ELSE；多重条件分支：IF-THEN-ELSIF。
简单条件判断的语法格式：IF condition THEN statements; END IF;
二重条件分支的语法格式：IF condition THEN statements; ELSE statements; END IF;
多重条件分支的语法格式：IF condition THEN statements; ELSIF condition THEN statements; ELSE statements; END IF;
b、条件控制语句：CASE语句
使用CASE语句执行多重条件分支操作，语句更加简捷，执行效率更好。
使用CASE语句处理多重条件分支有两种方法：第一种方法是使用单一选择符进行等值比较；第二种方法是使用多种条件进行非等值比较。

- CASE语句中使用单一选择符进行等值比较的语法格式：
```
CASE selector
        WHEN expression1 THEN sequence_of_statements1;
        WHEN expression2 THEN sequence_of_statements2;
        ......
        WHEN expressionN THEN sequence_of_statementsN;
        [ELSE sequence_of_statements;]
END CASE;
```

示例：输入成绩等级，判断属于哪个层次，并打印输出。

DECLARE
    v_grade CHAR(1) := '&n';
BEGIN
    CASE v_grade
        WHEN 'A' THEN
            DBMS_OUTPUT.put_line('优秀');
        WHEN 'B' THEN
            DBMS_OUTPUT.put_line('中等');
        WHEN 'C' THEN
            Dbms_Output.put_line('一般');
        ELSE
            DBMS_OUTPUT.put_line('输入有误');
 END CASE;
END;

- CASE语句中使用多种条件比较的语法格式：

CASE 
        WHEN condition1 THEN sequence_of_statements1;
        WHEN condition2 THEN sequence_of_statements2;
        ......
        WHEN conditionN  THEN sequence_of_statementsN;
        [ELSE sequence_of_statements;]
END CASE;

示例：输入员工号，获取员工工资，判断工资，如果工资小于1500，补助加100，如果工资小于2500，补助加80，如果工资小于5000，补助加50。

DECLARE
v_sal emp01.sal%TYPE;
v_empno emp01.empno%TYPE := &no;
BEGIN
SELECT sal INTO v_sal FROM emp01 WHERE empno = v_empno;
CASE
    WHEN v_sal < 1500 THEN 
        UPDATE emp01 SET comm = nvl(comm, 0) + 100 where empno = v_empno;
    WHEN v_sal < 2500 THEN
        UPDATE emp01 SET comm = nvl(comm, 0) + 80 where empno = v_empno;
    WHEN v_sal < 5000 THEN
        UPDATE emp01 SET comm = nvl(comm, 0) + 50 where empno = v_empno;
    COMMIT;
END CASE;
END;

c、循环语句：基本循环

语法格式：LOOP statement1; ...... END LOOP;

示例：打印数字1到10:。

DECLARE 
    v_cnt INT :=1;
BEGIN
    LOOP
        DBMS_OUTPUT.put_line(v_cnt);
        EXIT WHEN v_cnt = 10;
        v_cnt := v_cnt + 1;
    END LOOP;
END LOOP;

d、循环语句：WHILE循环

语法格式：WHILE condition LOOP statement1; statement2; ...... END LOOP;

DECLARE 
    v_cnt INT :=1;
BEGIN
    while v_cnt <= 10 LOOP
                DBMS_OUTPUT.put_line(v_cnt);
                v_cnt := v_cnt + 1;
    END LOOP;
END ;

e、循环语句：FOR循环

语法格式：

FOR counter in [REVERSE] lower_bound ..upper_bound LOOP 
statement1; statement2; 
...... 
END LOOP;

示例：打印从1到10。

BEGIN 
    FOR i IN 1..10 LOOP
        Dbms_Output.put_line(i);
    END LOOP;
END;

需要注意，其中i为Oracle的隐含控制变量。
f、循环语句：嵌套循环和控制语句

嵌套循环和标号：嵌套循环是指在一个循环语句中嵌入另一个循环语句；标号用于标记嵌套块或者嵌套循环；使用<<lable_name>>定义标号。

DECLARE 
    v_result INT;
BEGIN
    <<outter>>
    FOR i IN 1..5 LOOP
        <<inter>>
        FOR j IN 1 .. 5 LOOP
            v_result := i;
            EXIT outter WHEN i = 4;
        END LOOP inner; 
        DBMS_OUTPUT.put_line('内：' || v_result);
    END LOOP outter;
    DBMS_OUTPUT.put_line('外：' || v_result);
END;

EXIT和EXIT WHEN语句：EXIT语句用于直接退出当前循环。（EXIT WHEN语句用于在满足条件时退出当前循环）

DECLARE
    v_cnt INT :=1;
BEGIN
    LOOP
        DBMS_OUTPUT.put_line(v_cnt);
        EXIT WHEN v_cnt = 10;
        v_cnt := v_cnt + 1;
    END LOOP;
END;

CONTINUE和CONTINUE WHEN语句：
CONTINUE语句时oracle 11g的新特性，用于直接结束当前循环并继续下一组循环。

CONTINUE WHEN语句用于在满足特定条件时结束当前循环语句并继续下一组循环语句。

DECLARE
    v_cnt INT := 0;
BEGIN
    LOOP 
        v_cnt := v_cnt + 1;
        CONTINUE WHEN v_cnt = 5;
        DBMS_OUTPUT.put_line(v_cnt);
        EXIT WHEN v_cnt = 10;
    END LOOP;
END;

g、顺序语句

GOTO语句：GOTO语句用于跳转到特定标号处执行语句。
语法格式：GOTO label_name;
注意：当使用GOTO跳转到特定标号时，标号后至少要包含一条执行语句。

示例：打印输出1-10，使用GOTO语句处理。

DECLARE
    v_cnt INT := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.put_line(v_cnt);
    IF v_cnt = 10 THEN
        GOTO end_loop;
    END IF;
    v_cnt := v_cnt + 1;
    END LOOP;
    <<end_loop>>
    DBMS_OUTPUT.put_line('循环结束');
END;

NULL语句：NULL语句不会执行任何操作，并且会直接将控制传递到下一个语句，使用该语句的主要目的是提高PL/SQL块的可读性。

BEGIN
    SELECT ename, sal INTO v_name, v_sal FROM emp01 WHERE empno = &no;
    IF v_sal < 3000 THEN
        UPDATE emp01 SET comm = nvl(comm, 0) + sal * 0.2 WHERE ename = v_name;
        COMMIT;
        DBMS_OUTPUT.put_line(v_name || '的奖金更新了');
    ELSE
        NULL;
    END IF;
END;

温馨提示：之后的Oracle专题代码示例大部分在SQL WINDOW窗口下执行。

Oracle专题11之PLSQL入门

1、PLSQL工具

2、PLSQL的简介

a、什么是PL/SQL？

PL/SQL是面向过程的语言，PL/SQL是SQL语言的扩展。

b、PLSQL的特点？

3、PLSQL的语法结构

a、PL/SQL块

b、第一个PL/SQL程序

c、PL/SQL块的分类

4、PLSQL的变量类型

a、标识符

b、变量的命名方法

c、变量的类型

d、变量的大小写规则

e、PLSQL中的注释

f、PL/SQL程序综合示例

5、PLSQL的引用型变量和记录型变量

a、为什么要使用引用型变量和记录型变量

b、引用型变量

c、记录型变量

6、PLSQL的运算符

a、算术运算符

b、关系运算符

c、比较运算符

d、逻辑运算符

e、字符以及数字运算的特点

7、PLSQL的条件控制语句

顺序语句：GOTO语句、NULL语句。

a、条件控制语句：if语句

b、条件控制语句：CASE语句

使用CASE语句处理多重条件分支有两种方法：第一种方法是使用单一选择符进行等值比较；第二种方法是使用多种条件进行非等值比较。

c、循环语句：基本循环

d、循环语句：WHILE循环

e、循环语句：FOR循环

f、循环语句：嵌套循环和控制语句

g、顺序语句

相关阅读