[大数据]PL/SQL简介

一、PL/SQL中的数据类型

1、字符串类型

????????在 PL/SQL 中的字符串类型所你所想的一样，是一种常见的数据类型定义，它允许在其中有字母和数字。像名字、代码、描述、序列号等都可以包括在字符串中。

? ? ? ? char中存储着固定长度的字符串代码，char最大长度为32767个字节。

????????语法：char(max_length)

? ? ? ? 子类型：character

? ? ? ? varchar2则存储着长度可变的字符串，varchar2 所允许的最大长度为 32767 个字节。

? ? ? ? 语法：varchar2(max_length)

? ? ? ? 子类型：varchar,string

? ? ? ? long 也可以存储变长的字符串，最大长度为 32760 个字节，long 是典型的用以存储长文本。

2、数值数据类型

????????number用以存储在 ORACLE 数据库中的任何类型的数值?。

? ? ? ? 语法：number(max_length)

????????使用下边的语法可以指定数据类型的精度：number(precision,scale)

? ? ? ? 子类型：dec、decimal、double precision、integer、int、numeric、real、smallint、float

? ? ? ? pls_integer定义的整数是可以带有符号的，例如：负数。

3、二进制数据类型

????????二进制数据类型可以以二进制形式来存储数据。这种数据类型包括raw 和 longraw。

4、日期数据类型

????????date是在 oracle 中可以有效存储的数据类型，如果你将某一列定义为date类型，你就不能指定它的长度，date 数据类型的长度是默认的，oracle日期数据类型：01- OCT-97。

5、逻辑数据类型

????????逻辑数据类型：true、false?和 NULL。与 date相类似，boolen 在作为列或变量的类型在定义时也不需要参数。

6、rowid

????????rowid是存在于oracle数据库的每一个表中的预定义列。rowid以二进制格式存储和确定表中的每一列，索引就是使用rowid指向数据的。

二、PL/SQL块结构

????????PL/SQL 是一种块结构语言，也就是说 PL/SQL 的程序可以分成逻辑块来写。在一个块的内部可以有像数据操作或查询之类的过程。

? ? ? ? declare部分包括了定义的变量和其它的对象，如常量和指针。//可选的

? ? ? ? procdure部分包括条件语句和 SQL 语句，块可以对它进行控制。 //强制的

????????exception告诉了 PL/SQL 如何处理指定的错误并按用户的定义进行处理。//可选的

????????块是 PL/SQL 代码的逻辑单元，包括至少一个 procdure 部分和可以选择的 declare?以及 exception 部分。

PL/SQL块的基本结构：

begin            --可选的，意味着块的开始
    declare      --可选的，变量定义
    begin        --强制性，是指程序段的开始
    exception    --可选的，是指除部分的开始
    end          --强制，是指程序段的结束
end              --可选的，是指块的结束

三、注释

? ? ? ? PL/SQL单行注释：-- This is a one-line comment.? ?使用--进行注释。
? ? ? ? ? ? ? ? ? ? ? 多行注释：/* This is a multiple-line comment.*/? 使用/**/方式进行注释。

????????PL/SQL不支持数据字典语言(DDL)命令，用户可以使用 PL/SQL 数据，不能维护数据结构。

四、declare部分

1、变量声明

--声明变量
declare
    owner char(10);
    tablename char(30);
    bytes number(10);
    tody date

--初始化变量
declare
    customer char(30);
    fiscal_year number(2):='97';

2、常量的定义

????????定义常量的方法与定义变量相同，但是常量的数值是静态的，他们不能改变。在上个例子中 fiscal_year 可能是常量。

3、指针的定义

????????指针是 PL/SQL 中的另一种类型的变量。指针型变量则指向了查询结果中的某一行数据。在查询的结果有多个时，需要在每个记录之间进行翻阅，利用指针进行返回每行的数据。

declare 
cursor employee_cursor is
    select * from employees;

4、%TYPE属性

????????%TYPE 可以返回表中给定列的变量属性。

declare
    cursor employee_cursor is
        select emp_id,emp_name from employees;
    id_num employee.emp_id%TYPE;--id_num和emp_id的数据类型相同
    name employee.emp_name%TYPE;--name和emp_name的数据类型相同

5、%ROWTYPE

? ? ? ? 当定义的变量和指针关联，可以使用%ROWTYPE声明变量和指针所指向的行类型相同。

declare
    cursor employee_cursor is
        select emp_id,emp_name from employees;
    employee_record employee_cursor%ROWTYPE;
--定义变量employee_record和employee_cursor所在行的数据类型相同

6、ROWCOUNT

????????PL/SQL 中%ROWCOUNT 属性可以保证在特定的 SQL 语句块中的游标行数。

declare
    cursor employee_cursor is
        select emp_id,emp_name from employees;
    records_processed:= employee_cursor%ROWCOUNT;
--records_processed 将会返回 PL/SQL 语句所访问的 employee_cursor 的行数

五、Procdure

1、begin.....end

????????begin表示过程开始，end表示语句块的结束。

begin
    open a cursor;
    condition1；
        statement1;
    condition2;
        statement2;
    close the cursor;
end

2、指针控制命令?

????????如何对定义过的指针进行访问？

2.1? declare

2.2? open?打开指针

begin
    open employee_cursor;
    statement1;
    statement2;
end

2.3? FETCH 从指针中获取变量的值

declare
    cursor employee_cursor is
        select emp_id, emp_name from employees;
    employee_record employee_cursor%ROWTYPE;
    begin
        open employee_cursor;
            loop
                fetch employee_cursor into employee_record;
            end loop;
        close employee_cursor;
    end
--将一个指针所指的行的数据赋给了一个名字叫employee_record的变量,使用 LOOP 循环来移动指针，块实际没有做任何工作

declare
    cursor employee_cursor is
        select emp_id, emp_name from employees;
    employee_record employee_cursor%ROWTYPE;
    id_num employees.emp_id%TYPE;
    name employees.emp_name%TYPE;
    begin
        open employee_cursor;
            loop
                fetch employee_cursor into employee_record;
            end loop;
        close employee_cursor;
    end
--将当前的指针所指的行的对应数据填入变量id_num和name

2.4? close

????????在结束指针使用时，应当使用close将指针关闭。在关闭指针以后，查询的结果集就不存在了。

3、条件语句

3.1? if....then

--对两种情况分别处理
IF condition1 THEN
 statement1;
ELSE
 statement2;
END IF;
--判断的条件多余两个
IF condition1 THEN
 statement1;
ELSIF condition2 THEN
 statement2;
ELSE
 statement3;
END IF;

3.2? loops循环

?3.3.1 loop

?????????loop本身是一个无限的循环，经常在指针中使用，若终止这种循环，必须指定在什么时候退出。

--当指针处于最后一行时退出
BEGIN
open employee_cursor;
LOOP
    FETCH employee_cursor into employee_record;
    EXIT WHEN employee_cursor%NOTFOUND;
    statement1;
END LOOP;
close employee_cursor;
END;

3.3.2? while-loop

? ? ? ? while-loop循环只有满足while条件才能执行特定的语句，不满足的时候，会退出循环。

DECLARE
    cursor payment_cursor is
    select cust_id, payment, total_due from payment_table;
    cust_id payment_table.cust_id%TYPE;
    payment payment_table.payment%TYPE;
    total_due payment_table.total_due%TYPE;
BEGIN
    open payment_cursor;
    WHILE payment < total_due LOOP
        FETCH payment_cursor into cust_id, payment, total_due;
        EXIT WHEN payment_cursor%NOTFOUND;
        insert into underpay_table
        values (cust_id, 'STILL OWES');
    END LOOP;
    close payment_cursor;
END;

3.3.3? for-loop

? ? ? ? 当指针所指定的行的数值不满足for循环的内容，将会退出循环。

DECLARE
    cursor payment_cursor is
    select cust_id, payment, total_due from payment_table;
    cust_id payment_table.cust_id%TYPE;
    payment payment_table.payment%TYPE;
    total_due payment_table.total_due%TYPE;
BEGIN
    open payment_cursor;
    for pay_rec IN payment_cursor LOOP
        IF pay_rec.payment < pay_rec.total_due THEN
            insert into underpay_table
            values (pay_rec.cust_id, 'STILL OWES');
        END IF;
    END LOOP;
    close payment_cursor;
END;

4、EXCEPTION

4.1? 激活exception异常

? ? ? ? 可以使用raise语句进行激活异常。在日常生活中，当数据库出现错误时，是被自动激活的。

BEGIN
    DECLARE
        exception_name EXCEPTION;
    BEGIN
        IF condition THEN
            RAISE exception_name;
        END IF;
    EXCEPTION
        WHEN exception_name THEN
            Statement;    
    END;
END;

4.2? 异常处理

? ? ? ? 在捕获异常以后，对异常进行处理，需要在错误的状态继续运行或是终止运行。

EXCEPTION
    WHEN exception1 THEN
    statement1;
    WHEN exception2 THEN
    statement2;
    WHEN OTHERS THEN
    statement3;

5、将输入返回给用户

????????PL/SQL 在它的语法部分中并没有提供直接的方式来显示输出，但是它可以让你来用一个对该语句块服务的包，这个包是由 DBMS_OUTPUT 来调用的。

EXCEPTION
    WHEN zero_divide THEN
        DBMS_OUTPUT.put_line('ERROR: DIVISOR IS ZERO. SEE YOUR DBA.');

五、事务处理

???????事务控制命令：COMMIT、ROLLBACK、AVEPOINT。这些命令可以让程序员在在事务向数据库中进行写操作时加以控制。PL/SQL 可以自动地执行事务控制命令来代替对大型事务的不断监控。

六、存储过程、包和触发机制

????????使用 PL/SQL，可以创建存储对象来代替日复一日的输入单调和枯燥的代码。存储过程是一些可以执行一些特定类型的存储工作的代码块，相关的过程可以组合和存储在一起。这称为包。触发机制是一种在其它的事务中使用的数据库对象。

存储过程：

PROCEDURE procedure_name IS
 variable1 datatype;
 ...
BEGIN
 statement1;
 ...
EXCEPTION
 when ...
END procedure_name;

包：

CREATE PACKAGE package_name AS
    PROCEDURE procedure1 (global_variable1 datatype, ...);
    PROCEDURE procedure2 (global_variable1 datatype, ...);
END package_name;
CREATE PACKAGE BODY package_name AS
    PROCEDURE procedure1 (global_variable1 datatype, ...) IS
        BEGIN
            statement1;
            ...
        END procedure1;
    PROCEDURE procedure2 (global_variable1 datatype, ...) IS
        BEGIN
            statement1;
            ...
        END procedure2;
END package_name;

触发机制：

CREATE TRIGGER trigger_name
    AFTER UPDATE OF column ON table_name
    FOR EACH ROW
BEGIN
    statement1;
    ...
END;