ORACLE PL/SQL学习笔记

本文仅是自己学习oracle做的笔记和框架，就像Thomas Kyte所言，oracle最好能通过一条SQL语句完成，其次才是PL/SQL，再次才是Java等高级语言，之后是C语言。PL/SQL看下来跟其他编程语言的通用逻辑类似，有常量、变量、控制和循环、异常处理，甚至缓存（cursor）、方法（procedure和function）、对象（package封装变量和方法）、切面（触发器）。对比Java不具备的功能是：通信、接口、分层、api、设计模式这些高级功能。pl/sql是理解package代码、job的基础，此外也能拓展对oracle 的使用，加深对编程的理解。

ORACLE        PL/SQL学习笔记

 

PL/SQL基础及应用

一、定义

a)    PL/SQL是oracle在标准sql语言的基础上进行扩展，允许嵌入sql语句、定义变量、使用控制条件和循环、处理异常，从而提供了更强大的功能。

二、特点和结构

a)    程序块    DECLARE…BEGIN…END;/

b)    变量和类型、运算符和表达式、注释。

                i.         变量命名习惯：变量v_*   常量c_*     游标*_cursor  表*_table  一行数据*_ record   数据类型*_type

三、控制条件

这类使用与一般的编程一致，不举例了

a)    条件控制：

                i.         IF…THEN…ELSEIF…THEN…ELSE…END IF;    

              ii.         CASE…WHEN...THEN…WHEN…THEN…ELSE…END  CASE;

b)    循环：

                i.         LOOP…EXIT WHEN…END LOOP;

              ii.         WHILE…LOOP…END LOOP;   

             iii.         FOR i IN [REVERSE] 1..3 LOOP…END LOOP;

四、复合变量

a)    动态字段类型：%TYPE      emp.ename%type;

b)    动态行类型：%ROWTYPE emp%ROWTYPE

c)     自定义类型：TYPE emp_record_type is RECORD( eid int,ename varchar2)…

五、集合

a)    索引表:

                i.         类似于数组，index 可以取负数、varchar2

              ii.         TYPE **_table_type IS TABLE OF number index by binery_integer/varchar2; 先定义类型，然后照常使用

b)    嵌套表

                i.         下标从1开始；可以作为数据类型 TYPE emp_type IS TABLE OF ... ； Type eg_type IS TRABLE OF NUMBER;

              ii.         可以在PL/SQL语句块中使用，表列中嵌套表NESTED TABLE … STORE AS… ，赋值时使用构造方法 eg：eg_type(1,2,3);

c)     变长数组VARRAY

                i.         从1开始，有长度的数组TYPE … IS VARRAY(n) OF **

              ii.         赋值、检索、插入等与嵌套表类似

d)    PL/SQL记录表

                i.         Rowtype动态行组成的表类型

e)    集合方法、函数

                i.         EXITSTS()            COUNT()      LIMIT()  NEXT()/FIRST()   

六、游标

a)    作为保存在内存的临时表，可返回一条或一组数据

                i.         DECLARE CURSOR ** IS ***     OPEN **       FETCH **       CLOSE**

CURSOR emp_cursor IS SELECT * FROM emp BEGIN OPEN emp_cursor; ... END;/

              ii.         属性 %ISOPEN    %FOUND             %NOTFOUND            %ROWCOUNT

b)    参数化游标

                i.        带参数， 定义游标只能制定类型，where子句中需引用该参数。

CURSOR emp_cursor(vsal) IS SELECT * FROM EMP WHERE sal>vsal;

c)     更新或者删除数据

                i.      ... （condition）...   DELETE emp where CURRENT OF emp_cursor    注意：如果更新或者删除，定义游标时带有forupdate语句，提取游标之后，必须在update、delete语句中引用 WHERE CURRENT OF *_cursor 子句。

d)    游标for循环

                i.         相当于foreach语句 FOR .. IN  **_CURSOR      注意：oracle隐式OPEN、PETCH、CLOSE游标

e)    游标变量（动态游标）

                i.         定义：TYPE  emp_type is REF CURSOR [RETURN emp%ROWTYPE]

              ii.         使用：OPEN…FOR FETCHE…CLOSE…

七、异常

a)    预定义异常

                i.         举例：ACCESS_INTO_NULL     COLLECTION_IS_NULL      DUP_VAL_ON_INDEX       NO_DATA_FOUND       TOO_MANY_ROWS

b)    用户自定义异常

                i.         e_no_employeeEXCEPTION;  PARAM EXCEPTION_INTO(e_no_employee,-2291)

c)     异常函数

                i.         RAISE_APPLICATION_ERROR(e_number,msg)

 

PL/SQL高级应用

       可重用的语句块

一、存储过程       PROCEDUER

a)    创建：CREATE  OR  REPLACE PROCEDURE  out_time  IS BEGIN  …  END;/

使用：EXEC out_time              CALLout_time()  注意：call过程名加()

b)    过程的参数传递

                i.         创建procedure为有参，begin调用时赋值

c)     过程的参数模式

                i.         形参行为：IN              OUT              IN OUT

              ii.         建立时省略则默认为IN，可显式定义；OUT 不可省略。

1.     使用procedure传入形参使用  :para_name

d)    参数的默认值

                i.         声明默认值：para_name   para_type     default [:=] value

二、函数

类似procedure，不同：p用于完成任务，返回值不定；function包含RETURN语句，操作数据，返回单独的函数值，仅能用于一个表达式中   

a)    创建

                i.         CREATEOR REPLACE FUNCTION get_name(eno NUMBER) RETURN VARCHAR2  AS v_name VARCHAR2;

BEGIN … RETURN … END;/

                     程序块任意处可调用

b)    带参函数

                i.         IN：传参，可省略； OUT：调用函数会赋值给该形参，用于返回多个值（函数仅能返回单个）。

三、程序包

a)    基本操作：

                i.         包头       CREATE OR REPLACE PACKAGE pack_test AS (过程、类型、函数、对象)

              ii.         包体       CREATE OR REPLACEPACKAGE BODY pack_test AS … END;/

b)    系统预定义包

                i.         DBMS_*        UTL_*            dbms_output.put_line()

c)     私有过程函数

                i.         包规范：定义包的公用组件——常量、变量、游标、过程、函数

              ii.         包体：实现过程和函数、定义私有组件（仅包内调用）

             iii.         调用包组件：仅包的公用组件可在其他程序块中调用

d)    包重载

                i.         包规范定义、包体实现重载方法。

e)    使用包构造过程

                i.         类似于Java类的属性、方法概念，属性在最后的end…begin…块中赋值

四、触发器（TRIGGER）

类似于函数、过程。区别：隐式调用、无参、全局（不可指定范围）、不能递归、无事务、不能声明LONG

a)    分类：DML   替代       系统       DDL

b)    建立：CREATE OR REPLACE TRIGGER emp_count AFTER DELETE ON temp BEGIN…END…;/

                i.         修改：replace            开闭：alter tr* enabled/disabled alter table enable all triggers      删除：drop

c)     语句触发器

                i.         Beforeinsert or update or delete

              ii.         条件谓词：    INSERTING   UPDATING   DELETING

d)    触发器的新值和旧值

                i.         仅能作用于行级触发器（FOR EACH ROW）

e)    行触发器

                i.         对比语句触发器，每作用一行则触发一次。

              ii.         Before/after行触发器、限制行触发器（when）

f)     Insteadof 触发器

                i.         实现对复杂视图的DML操作

              ii.         方法：

1.     建立视图：一条复杂查询的的返回结果的逻辑集合

2.     建立instead of 触发器，执行insert、update、delete语句。

g)    系统事件触发器

                i.         历程启动和关闭触发器

1.     建表记录事件和时间。注意：启动必须after，关闭必须before

2.     AFTERSTARTUP / BEFORE SHUTDOWN ON DATABASE

              ii.         登陆和退出触发器

1.     建信息表。注意：触发器以特权用户建立

2.     AFTERLOGON / BEFORE LOGOFF

             iii.         DDL触发器

1.     建表

2.     AFTERDDL ON scott.schema