PL/SQL特点及高级应用

为什么使用PL/SQL

　　执行sql语句时，一次仅向Oracle服务器发送一条语句可能会导致大量的网络流量。PL/SQL可以一次性发送多条sql语句，减少了Oracle服务器的开销。

PL/SQL特点

　　数据抽象。在PL/SQL中，面向对象的编程是基于对象类型的。对象类型封装了数据结构、函数和过程。组成数据结构的为属性，函数和过程为方法。

CREATE TYPE BankAccount AS OBJECT (
    accountNo VARCHAR2(30),    
    MEMBER PROCEDURE desp(amount IN REAL),
    MEMBER FUNCTION computeBalance(num IN INTEGER) RETURN INTEGER
);

基本语言块　　

DECLARE -- 声明变量、游标、用户自定义类型和异常
BEGIN -- 程序开始标志
EXCEPTION -- 异常处理部分
END; -- 程序结束标志

过程　　

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE print ( hyname VARCHAR2 )
    AS
BEGIN
    dbms_output.put_line(hyname);
END;

EXEC print('hello world');

函数

CREATE OR REPLACE FUNCTION compare (
    firstnum   IN NUMBER,
    endnum     IN NUMBER
) RETURN NUMBER AS
    resultnum   NUMBER(38);
BEGIN
    IF
        firstnum > endnum
    THEN
        resultnum := firstnum;
    ELSE
        resultnum := endnum;
    END IF;

    RETURN resultnum;
END;

BEGIN
    dbms_output.put_line(compare(100,200) );
END;

包

　　 包是一组相关过程、函数、变量、类型和游标等PL/SQL程序设计元素的组合。包具有面向对象设计的特点，是对这些PL/SQL程序设计元素的封装。一个包由两个分开的部分组成即包头和包体。包为PL/SQL提供了全局变量的作用。　　

-- 创建包头
CREATE OR REPLACE PACKAGE packagedemo AS
    age INTEGER;
    FUNCTION compare ( firstnum NUMBER,endnum NUMBER ) RETURN NUMBER;

END;

-- 创建包体
CREATE OR REPLACE PACKAGE BODY packagedemo AS

    FUNCTION compare ( firstnum NUMBER,endnum NUMBER ) RETURN NUMBER AS
        resultnum   NUMBER(38);
    BEGIN
        IF
            firstnum > endnum
        THEN
            resultnum := firstnum;
        ELSE
            resultnum := endnum;
        END IF;

        RETURN resultnum;
    END;

END;

-- DEMO
SELECT PACKAGEDEMO.COMPARE(100, 200) AS DEMO FROM F_ORDER;

BEGIN
    packagedemo.age := 100;
    dbms_output.put_line(packagedemo.age);
END;

集合

　　index-by表

DECLARE
    TYPE hyname IS
        TABLE OF f_order.hy_name%TYPE INDEX BY BINARY_INTEGER;
    membername   hyname;
BEGIN
    membername(1) := 'hello world!';
    dbms_output.put_line(membername(1) );
END;

　　嵌套表

DECLARE
    TYPE hynameArr IS
        TABLE OF VARCHAR2(30);
    memberArr   hynameArr;
BEGIN
    memberArr := hynameArr('hello', 'world');
    dbms_output.put_line(memberArr(1) );
END;

　　可变数组

DECLARE
    TYPE hynameArr IS
        VARRAY(10) OF VARCHAR2(30);
    memberArr   hynameArr;
BEGIN
    memberArr := hynameArr('hello', 'world');
    dbms_output.put_line(memberArr(1) );
END;

游标

　　游标创建

　　1、显示游标

SET SERVEROUTPUT ON;
DECLARE
    CURSOR forder_cursor IS
        SELECT
            f_order_code,
            hy_code,
            hy_name
        FROM
            f_order;

BEGIN
    FOR result_row IN forder_cursor LOOP
        dbms_output.put_line(result_row.f_order_code
         || ','
         || result_row.hy_code
         || ','
         || result_row.hy_name);
    END LOOP;
END;

　　2、隐式游标

　　用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update等语句的执行情况。

 

SET SERVEROUTPUT ON;

BEGIN
    UPDATE f_order
        SET
            hy_name = 'hello world'
    WHERE
        f_order_code = 'hello world';

    IF
        SQL%found
    THEN
        dbms_output.put_line('update success');
    ELSE
        dbms_output.put_line('update failed');
    END IF;

END;

　　游标变量及控制游标变量

DECLARE
    TYPE fordercursortype IS REF CURSOR RETURN f_order%rowtype;
    fordercursor   fordercursortype;

    PROCEDURE f_order_procedure ( forder fordercursortype ) AS
        fordertemp   f_order%rowtype;
    BEGIN
        LOOP
            FETCH forder INTO fordertemp;
            EXIT WHEN forder%notfound;
            dbms_output.put_line(fordertemp.f_order_code
             || ','
             || fordertemp.hy_code
             || ','
             || fordertemp.hy_name);

        END LOOP;
    END;

BEGIN
    OPEN fordercursor FOR
        SELECT
            *
        FROM
            f_order
        WHERE
            hy_name = 'hello';

    f_order_procedure(fordercursor);
    CLOSE fordercursor;
END;