Oracle 笔记04

PL/SQL

PL/SQL是什么?

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

总之，在Oracle中，如果你想要按流程化执行一个任务，只用普通的SQL语句是不行的，PL/SQL能很好的弥补这个缺点。

PL/SQL的特点

• 与SQL语言紧密结合
• 减少网络流量，提高程序执行性能
• 模块化地设计程序功能
• 可移植性好

PL/SQL的执行过程

PL/SQL块的组成

DECLARE
 --声明部分，定义变量、数据类型、异常、局部子程序等（可选）
BEGIN
 --执行部分，实现语句块的功能（必须）
EXCEPTION
 --异常处理部分（可选）
END；
 --结束标志（必须）

示例

DECLARE
 v_ename VARCHAR2(10);  --定义一个varchar2类型的变量v_ename
BEGIN
 SELECT ename INTO v_ename FROM emp
 WHERE empno = 7844;
 DBMS_OUTPUT.PUT_LINE('*********');  --在SQL PLUS环境下使用DBMS_OUTPUT.PUT_LINE方法必须先↓
 DBMS_OUTPUT.PUT_LINE(v_ename);      --将SERVEROUTPUT设置为ON↓
 DBMS_OUTPUT.PUT_LINE('*********');  --SET SERVEROUTPUT ON
EXCEPTION
 WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('There is not such a employee');
END;  --所有PL/SQL块都以 END; 结束

PL/SQL块的嵌套

嵌套可发生在一个块的执行部分中，也可发生在异常处理部分中

示例

DECLARE
    v_sal NUMBER(6,2);
    v_deptno NUMBER(2);
BEGIN
    BEGIN
        SELECT deptno INTO v_deptno FROM emp
        WHERE empno = 7844;
    END;
    SELECT avg(sal) INTO v_sal FROM emp
    WHERE deptno = v_deptno;
    DBMS_OUTPUT.PUT_LINE(v_sal);
END;

PL/SQL块分类

• 匿名块：动态生成，只能执行一次，不能被其他程序调用
• 命名块：可多次执行， 包括函数、存储过程、包、触发器，编译后保存在服务器中在特定条件下调用执行

命名块

CREATE OR REPLACE PROCEDURE showavgsal(p_deptno NUMBER)
AS
    v_sal NUMBER(6,2);
BEGIN
    SELECT avg(sal) INTO v_sal FROM EMP
    WHERE deptno = p_deptno;
    DBMS_OUTPUT.PUT_LINE(v_sal);
END;
--调用命名块
BEGIN
    showavgsal(30);
END;

匿名块

DECLARE
    v_sal NUMBER(6,2);
BEGIN
    SELECT avg(sal) INTO v_sal FROM emp
    WHERE deptno = 30;
    DBMS_OUTPUT.PUT_LINE(v_sal);
end;

词法单元

• 字符集
• 标识符
• 分隔符
• 常量值
• 注释

字符集

标识符

用来定义PL/SQL的 变量、常量、异常、游标、参数等
以字母开头，后边可以跟 字母、数字、美元符号、井号、下划线
最大长度为30个字符
如果需要 区分大小写、使用预留关键字、标识包含空格等特殊符号
需要将标识符用 双引号 括起来

分隔符

有特定意义的单个符号或组合符号

常量

字符型文字 -------- 用单引号引起来的字符串，区分大小写
数字型文字 -------- 整数和实数两类，可用科学计数法表示
布尔型文字 -------- TRUE FALSE NULL
日期型文字 -------- 日期值

注释

单行注释： --
多行注释：/* ........... */

数据类型

其他类型
%TYPE 和 %ROWTYPE

%TYPE：定义一个类型与某个变量的数据类型或数据库表中某个列的数据类型一致
%ROWTYPE：定义一个与数据库中某个表结构一致的记录类型的变量
PS：
变量的类型随参照的变量类型变化而变化
如果数据库表列中有NOT NULL约束，则%TYPE与%ROWTYPE返回的数据类型无此限制
示例：

DECLARE
    v_sal emp.sal%TYPE; --v_sal类型与emp.sal相同
    v_emp emp%ROWTYPE;  --v_em内部结构与emp任一元组相同
BEGIN
    SELECT sal INTO v_sal FROM emp WHERE empno=7844;
    SELECT * INTO v_emp FROM emp WHERE empno=7900;
    DBMS_OUTPUT.PUT_LINE(v_sal);
    DBMS_OUTPUT.PUT_LINE(v_emp.ename||v_emp.sal);
END;

变量与常量

--定义
variable_name [CONSTANT] datatype [NOT NULL] [DEFAULT|:=expression];

变量和常量名称应符合标识符命名规范
每行只能定义一个变量
如加上关键字CONSTANT，代表所定义的为常量，必须赋初始值
如使用NOT NULL关键字，必须为变量赋默认值
如果变量没有赋初值，默认为NULL
使用DEFAULT或 “:=” 运算符进行赋初值

DECLARE
    v1 NUMBER(4);      --定义一个变量v1，没有赋初始值，默认为NULL
    v2 NUMBER(4) NOT NULL :=10;      --定义一个NOT NULL的变量v2，默认值为10
    v3 CONSTANT NUMBER(4) DEFAULT 100;  --定义一个常量v3，值为100

声明作用域：
局部声明：变量与常量的作用域仅限于声明所在的块中
全局声明：既可以在内部块中引用，也可以在外部块中引用

接收用户的输入：

-- 使用 & 符号来接收用户的输入
DECLARE
    v_num NUMBER(4,2);
BEGIN
    v_num := &x;        --等待用户输入并赋值给v_num
    DBMS_OUTPUT.PUT_LINE('你输入的数字是：');
    DBMS_OUTPUT.PUT_LINE(v_num);
END;

PL/SQL记录类型

记录：数据库表中的一个元组
记录类型：由不同的标量数据类型组合到一起形成的数据类型，例如使用%ROWTYPE声明的标量