Oracle笔记--PL/SQL(Procedure Language & Structured Query Language)

1、PL/SQL是一种高级数据库程序设计语言，专门用于在各种环境下对Oracle数据库进行访问。该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。

2、PL/SQL是对SQL语言存储过程语言的扩展，是Oracle系统的核心语言。

3、PL/SQL程序由三个块组成：声明部分、执行部分、异常处理部分。

DECLARE
/*声明部分*/
BEGIN
/*执行部分*/
EXCEPTION
/*异常处理部分*/
END;

4、基本类型

5、记录类型：把逻辑相关的数据作为一个单元存储起来，称作PL/SQL RECORD的域(Field),其作用是存放互不相同但逻辑相关的信息。

TYPE record_type IS RECORD(
    Field1 type1 [NOT NULL] [:=exp1],
    Field2 type2 [NOT NULL] [:=exp2],
    ......
    Fieldn typen [NOT NULL] [:=expn]);

--使用%TYPE：定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同；

--使用%ROWTYPE:返回一个记录类型，其数据类型和数据库表的数据结构一致。

6、流程控制：

--条件判断：方式一：if ... then ... elsif ... then ... else ... end if;

　　　　　　方式二：case ...when...then...end;

--循环结构：方式一：loop...exit when ... end loop;

　　　　　　方式二:while ... loop ... end loop;

　　　　　　方式三:for i in ... loop ... end loop;

--goto、exit;

7、游标使用（游标是一个指向上下文的句柄或指针，类似于Java中的Iterator）

--1)定义游标：CURSOR cursor_name [(param[, param, ...])] IS select_statement;

--2)打开游标：OPEN cursor_name [([param => ] value[, [param => ] value, ... ])] ;不能重复打开游标

--3)提取游标数据：FETCH cursor_name INTO {variable_list | record_variable};

--4)关闭游标：CLOSE cursor_name;

--使用FOR循环将自动完成2)到4)步。

8、异常处理：用来处理正常执行过程中未预料的事件

--1）预定义错误（ORACLE自动引发，大约有24种）

--2）非预定义错误（用户在程序中定义，然后由ORACLE自动引发）

declare
  v_salary employees.salary%type;
  e_delete_id exception;
  pragma exception_init(e_delete_id, -2292);
begin
     delete from employees
     where employee_id = 100;
exception
     when e_delete_id then dbms_output.put_line('违反外键约束');
end;

--3）用户定义错误（需要用户在程序中定义，然后显示地在程序中将其引发）

declare
  no_result exception;
begin
  update employees set salary = salary + 100 where employee_id='1000';
  if sql%notfound then raise no_result;
  end if;
exception
     when no_result then dbms_output.put_line('not found');
end;

9、存储函数（有返回值）、存储过程（无返回值）

--ORACLE提供可以把PL/SQL程序存储在数据库中，并可以在任何地方来运行它，这样就叫存储过程或函数；

--函数创建
CREATE OR REPLACE FUNCTION func_name(expr1 type1, expr2 type2, ...)
RETURN NUMBER
IS
       --函数使用过程中，需要声明的变量、记录类型、cursor
BEGIN
       --函数执行体
EXCEPTION
       --异常处理
END

--例(OUT类型表示输出参数)：

create or replace procedure add_sal(dept_id number, tmp_sal out number)
is
       v_pct number(4, 2) := 0;
       cursor cursor_sal is select employee_id, salary, hire_date from employees where department_id=dept_id;
begin
       tmp_sal := 0;
       for i in cursor_sal loop
         if to_char(i.hire_date, 'yyyy') < '1995' then v_pct :=0.05;
         elsif to_char(i.hire_date, 'yyyy') < '1998' then v_pct := 0.03;
         else v_pct := 0.01;
         end if;
         tmp_sal := tmp_sal + i.salary * v_pct;
         update employees set salary = salary * (1 + v_pct) where employee_id=i.employee_id;
       end loop;
end;

10、触发器(Trigger)：触发器由一个事件来启动运行，即触发器是当某个事件发生时自动地隐式运行，并且不能接收参数

--ORACLE事件是指对数据库的表进行的INSERT、UPDATE及DELETE操作或试图进行类似的操作。

--ORACLE可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。

--语句级触发器(表级触发器)：只执行一次；

--行级触发器：当触发器事件发生时，对受到该操作影响的的每一行数据，触发器都单独执行一次。