PL/SQL笔记

作者: 比轩 | 来源:发表于2016-07-28 16:47 被阅读120次

1、基础知识

PLSQL是一种类Pascal语言，每一段程序都是由Block（代码块）组成

declare
     变量定
begin
    sql语句
    pl语句
exception
    异常处理
end;

PLSQL块分为三种：

匿名块 (Anonymous)
存储过程 (Procedure)
函数 (function)

上面举得例子就是匿名块,因为没有名字，所以叫匿名块。存储过程和函数都是有名字的，函数有返回值。

2、PLSQL变量

PLSQL变量主要有以下四种：

系统内置的常规简单变量类型
用户自定义复杂类型变量
引用类型变量（保存了一个指针值）
大对象类型（LOB）

变量类型举例：

布尔类型
日期类型
BFILE的二进制文件类型
日期类型
BLOB类型
long类型，长字符串
字符串类型

变量声明举例：

DECLARE v_hiredateDATE;
v_deptno NUMBER(2) NOT NULL := 10;
v_location VARCHAR2(13) := 'Atlanta';
c_comm CONSTANT NUMBER := 1400;

变量声明后再没有赋值之前其值为NULL

同一个块中，应该避免多个变量使用相同的名字

常规变量的申明：

DECLARE
    v_job VARCHAR2(9);
    v_count BINARY_INTEGER := 0;
    v_total_sal NUMBER(9,2) := 0;
    v_orderdate DATE := SYSDATE + 7;
    c_tax_rate CONSTANT NUMBER(3,2) := 8.25;
    v_valid BOOLEAN NOT NULL := TRUE;
    ... 
-- 特殊申明变量方式：
    v_name employees.last_name%TYPE;
    v_min_balance v_balance%TYPE := 10;
begin
   null;
end;

PLSQL中的游标

游标概论：

游标是一个私有的SQL工作区域，Oracle数据库中有两种游标，分别是隐式游标和显式游标，隐式游标不易被用户和程序员察觉和意识到，实际上Oracle服务器使用隐式游标来解析和执行我们提交的SQL语句；而显式游标是程序员在程序中显式声明的；通常我们说的游标均指显式游标。

显示游标

对于返回多行结果的SQL语句的返回结果，可使用显式游标独立的处理器中每一行的数据。

此处图片缺失

显式游标控制的一般过程：

DECLARE
    v_empno employees.employee_id%TYPE;
    v_ename employees.last_name%TYPE;
    CURSOR emp_cursor IS --1.创建游标
      SELECT employee_id, last_name FROM employees;
BEGIN
    OPEN emp_cursor; --2.打开游标
    LOOP
      FETCH emp_cursor
        INTO v_empno, v_ename; --3.提取变量  fetch cursor_name into value1,value2;

      EXIT WHEN emp_cursor%ROWCOUNT > 10 OR emp_cursor%NOTFOUND; --emp_cursor%NOTFOUND 4.用来测试是否有数据
      DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_empno) || ' ' || v_ename);
    END LOOP;
    CLOSE emp_cursor; --5.关闭游标
END;

`for`循环控制游标

举例1：直接使用for+sql创建并适用游标

   --一般格式：
   --for record_name in cursor_name loop ... end loop;
   BEGIN
   FOR emp_record IN (SELECT last_name, department_id
   FROM employees) LOOP
   -- implicit open and implicit fetch occur
   IF emp_record.department_id = 80 THEN
   ...
   END LOOP; -- implicit close occurs
   END;

举例2：先创建游标emp_cursor，再用for调用

--这种情况适用于多次调用游标的情况
 DECLARE
   CURSOR emp_cursor IS --先创建游标
   SELECT last_name, department_id
   FROM employees;
BEGIN
   FOR emp_record IN emp_cursor LOOP --再调用游标访问
   -- implicit open and implicit fetch occur
   IF emp_record.department_id = 80 THEN
   ...
   END LOOP; -- implicit close occurs
END;

游标带参数

直接返回和参数相关的查询结果

 DECLARE
    CURSOR emp_cursor
       (p_deptno NUMBER, p_job VARCHAR2) IS
        SELECT employee_id, last_name
        FROM employees
        WHERE department_id = p_deptno
        AND job_id = p_job;
BEGIN
    OPEN emp_cursor (80, 'SA_REP');
    . . .
    CLOSE emp_cursor;
    OPEN emp_cursor (60, 'IT_PROG');
    . . .
END;

在游标中使用`FOR UPDATE NOWAIT`

有的时候我们打开一个游标是为了更新或者删除一些记录，这种情况下我们希望
在打开游标的时候即锁定相关记录，应该使用for update nowait语句，倘若锁定失败我们就停止不再继续，以免出现长时间等待资源的死锁情况。

举例：

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, last_name, department_name
        FROM employees,departments
        WHERE employees.department_id =
              departments.department_id
        AND employees.department_id = 80
        FOR UPDATE OF salary NOWAIT;

PLSQL中的异常

PLSQL中一般有两种异常，一种是由Oracle内部错误抛出的异常，分为预定义和非预定义两种；另外一种是由程序员显式的抛出。

常见的异常：

异常代码	异常类型
NO_DATA_FOUND	没有发现数据
INVALID_CURSOR	游标在被打开以前，引用%NOTFOUND属性会产生此异常
TOO_MANY_ROWS	返回数据过多
ZERO_DIVIDE	除数为零，出现异常
DUP_VAL_ON_INDEX	唯一索引上有重复值

对othres的处理

others表明我们未能预计的错误，所以全部归到othres中去，单发生这种情况时，我们希望了解错误号和相关信息，可以使用Oracle内置的函数SQLCODE和SQLERRM来返回错误号和错误描述，举例见下：

DECLARE
    v_error_code NUMBER;
    v_error_message VARCHAR2(255);
BEGIN
    ...
EXCEPTION
    ...
WHEN OTHERS THEN
    ROLLBACK;
    v_error_code := SQLCODE ;
    v_error_message := SQLERRM ;
    INSERT INTO errors
    VALUES(v_error_code, v_error_message);
END;

处理用户自定义异常

此处图片缺失

举例：

DECLARE
    e_invalid_department EXCEPTION;
BEGIN
    UPDATE departments
    SET department_name = &p_department_desc
    WHERE department_id = &p_department_number;
    IF SQL%NOTFOUND THEN
        RAISE e_invalid_department;
    END IF;
    COMMIT;
EXCEPTION
    WHEN e_invalid_department THEN
        DBMS_OUTPUT.PUT_LINE('No such department id.');
END;

传递异常到外层代码块

DECLARE
    . . .
    e_no_rows exception;
    e_integrity exception;
    PRAGMA EXCEPTION_INIT (e_integrity, -2292);
BEGIN
    FOR c_record IN emp_cursor LOOP
      --内层代码块
      BEGIN
        SELECT ...
        UPDATE ...
        IF SQL%NOTFOUND THEN
            RAISE e_no_rows; --将异常扔到外层代码块中，使用关键字：RAISE
        END IF;
      END;

    END LOOP;
EXCEPTION
    WHEN e_integrity THEN ...
    WHEN e_no_rows THEN ...
END;

存储过程

存储过程基本语法

语法：

CREATE [OR REPLACE] PROCEDURE procedure_name
    [(parameter1 [mode1] datatype1,
    parameter2 [mode2] datatype2,
    . . .)]
IS|AS
PL/SQL Block;

举例：

CREATE OR REPLACE PROCEDURE raise_salary
    (p_id IN employees.employee_id%TYPE)
IS
BEGIN
    UPDATE employees
    SET salary = salary * 1.10
    WHERE employee_id = p_id;
END raise_salary;

存储过程参数模式

IN	OUT	IN OUT
默认模式	必须显示指定	必须显示指定
用以把值传给过程	用以把值从过程返回给调用环境	用以把变量传递给过程，并返回给调用环境
参数可以是常数、变量、表	必须是个变量	必须是个变量
必须是个变量	不能赋予默认值	不能赋予默认值

举例：IN OUT型变量的应用

--程序功能为：将传入的电话号码字符串按xxx-xxx-xxxxx的形式返回
CREATE OR REPLACE PROCEDURE format_phone
    (p_phone_no IN OUT VARCHAR2)
IS
BEGIN
    p_phone_no := '(' || SUBSTR(p_phone_no,1,3) ||
    ')' || SUBSTR(p_phone_no,4,3) ||
    '-' || SUBSTR(p_phone_no,7);
END format_phone;
/

存储过程的参数传递

使用默认值
按顺序传递
使用=>传递

举例：

首先创建存储过程add_dept，设定了两个参数，p_name和p_loc

--设定默认参数使用关键字：DEFAULT
CREATE OR REPLACE PROCEDURE add_dept
    (p_name IN departments.department_name%TYPE DEFAULT 'unknown',
    p_loc IN departments.location_id%TYPE DEFAULT 1700)
IS
BEGIN
    INSERT INTO departments(department_id,
                department_name, location_id)
    VALUES (departments_seq.NEXTVAL, p_name, p_loc);
END add_dept;
/

然后，使用调用add_dept，分别使用三种方式传递参数

BEGIN
    add_dept; --1.默认参数
    add_dept ('TRAINING', 2500); --2.顺序传递
    add_dept ( p_loc => 2400, p_name =>'EDUCATION');--3.使用=>传递
    add_dept ( p_loc => 1200) ;
END;
/
SELECT department_id, department_name, location_id
FROM departments; --sql查询验证

存储过程处理异常

此处图片缺失

函数

自治事务

关键字：

一个事务A在另一个事务B内被调用，那个事务A是自治事务，自治事务A执行过程中会脱离其session内未执行完毕的事务的影响。如果session从B事务开始——A事务开始和结束——B事务结束，上述的A事务不受没有完成的B事务的影响，然后A事务执行完毕后再次回到B事务执行没有完成的B事务。

创建自治事务语法：

Create or replace procedure pro_name as pragma AUTONOMOUS_TRANSACTION;
Begin
 null;
End;

下面通过一个例子来对自治事务进行说明和解释，首先创建一个procedure—noautonomous_transaction01：

Create or replace procedure noautonomous_transaction01 as
Begin
    Insert into test01 select * from test01 where rownum=1;
    Commit;
End;
--进行查询测试
Select count（*） from test01
--结果：Count(*)|25

此时，发现test01有25条数据

declare
begin
    Insert into test01 select * from test01 where rownum=1;
    Noautonomous_transaction01;--调用事务
    Rollback;
End;

上述pl/sql程序块内嵌套了一个dml的procedure过程。执行pl/sql程序块结束后发现test01有27条数据，也就说明了procedure中的commit完成了上述procedure和pl/sql程序块的提交。最后的rollback也就没有任何效果。

结论：非自治事务内的procedure中得commit或者rollback会影响前面所有dml的影响。

现在创建一个自治事务：

--Create Procedure的autonomous_transaction01
Create or replace procedure autonomous_transaction01 as pragma autonomous_transaction01;
Begin
    Insert into test01select * from test01 where rownum=1;
    Commit;
End;
--查询进行测试
Select count(*) from test01
--Count(*)|25

此时test01有25条数据。然后，在pl/sql程序块中调用自治事务autonomous_transaction01：

Begin
Insert into test01 select * from test01 where rownum=1;
    Autonomous_transaction01;
    Rollback;--rollback并没有影响到自治事务内的操作
End;

自治事务内的commit只会影响自治事务内的没有提交的dml，调用自治事务完毕后又会回到调用自治事务的事务内，此时最后的rollback也只能回滚自治事务外的dml，所以此时的test01中还是只有25条数据。

自治事务与被调用事务完全独立，不能共享调用者事务使用的锁和其他资源，而且自治事务内还可以调用其他自治事务。自治事务与其调用者可能会发生死锁，oracle会检测此类死锁并返回错误信息。

文件操作

系统函数：

utl_file.fopen(三个参数：文件对象，文件名，打开方式)       打开文件
utl_file.put_line(文件对象，字符串)    输出文件
utl_file.get_line()    读取文件
utl_file.fclose()      关闭文件
utl_file.fflush()      强制输出缓冲

文件打开方式

也就是fopen()的第三个参数：

r -- read text
w -- write text
a -- append text
rb -- read byte mode
wb -- write byte mode
ab -- append byte mode
--如果指定'a'或者'ab'但是文件不存在会先创建。

举例：

CREATE OR REPLACE PROCEDURE FILE_TEMP IS
   FILEHANDLE UTL_FILE.FILE_TYPE;--定义文件对象
BEGIN
  --打开文件
  FILEHANDLE := UTL_FILE.fopen('FILENAME' ,'FILE_TEMP.TXT' ,'W' );
  --写入文件
  UTL_FILE.put_line(FILEHANDLE,'这是一个练习和测试');
  FOR TEMP_1 IN (SELECT * FROM EMPLOYEES) LOOP
   --写入文件
   UTL_FILE.put_line(FILEHANDLE,TEMP_1.LAST_NAME || '|' || TEMP_1.SALARY);
  END LOOP;
  UTL_FILE.FCLOSE(FILEHANDLE);--关闭文件
END;

引用游标

create or replace procedure test_ref1
  is
   type s_table is ref cursor;
   f  s_table;
   f_rec t1% ;
   stmt varchar2(100) := 'select empno,ename from emp';
begin
   insert into t1 select empno,ename,sal from emp;
   open f for select * from t1;
   loop
       fetch f into f_rec;
       dbms_output.put_line(f_rec.empno ||'  '||f_rec.ename);
       exit when f%rowcount=3 ;
   end loop;
   close f;
end;
/

上课记录

begin ...end; 为一个程序块

匿名块不能被调用，因为没有名字，一般用于测试，可以直接执行
存储过程和函数相比，没有返回值，两者都需要编译后进行调用，才可以执行。
返回值和参数定义不需要指定长度，定义变量需要指定长度

PL/SQL笔记

1、基础知识

2、PLSQL变量

PLSQL中的游标

显示游标

`for`循环控制游标

游标带参数

在游标中使用`FOR UPDATE NOWAIT`

PLSQL中的异常

常见的异常：

对othres的处理

处理用户自定义异常

传递异常到外层代码块

存储过程

存储过程基本语法

存储过程参数模式

存储过程的参数传递

存储过程处理异常

函数

自治事务

文件操作

系统函数：

文件打开方式

引用游标

上课记录

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

数据库

编程学习

PL/SQL

PL/SQL笔记

1、基础知识

2、PLSQL变量

PLSQL中的游标

显示游标

for循环控制游标

游标带参数

在游标中使用FOR UPDATE NOWAIT

PLSQL中的异常

常见的异常：

对othres的处理

处理用户自定义异常

传递异常到外层代码块

存储过程

存储过程基本语法

存储过程参数模式

存储过程的参数传递

存储过程处理异常

函数

自治事务

文件操作

系统函数：

文件打开方式

引用游标

上课记录

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

数据库

编程学习

PL/SQL

`for`循环控制游标

在游标中使用`FOR UPDATE NOWAIT`