ORACLE的PLSQL块

1 PL/SQL块

1.1 PL/SQL的概念

PL/SQL(Procedural Language/SQL)是一种过程化语言，属于第三代语言，它与C、C++、Java等语言一样关注于处理细节，可以用来实现比较复杂的业务逻辑。

它允许SQL的数据操纵语言和查询语句包含在块结构(block_structured)和代码过程语言中，使PL/SQL成为一个功能强大的事务处理语言。

1.2 PL/SQL块的分类

• 一

  无名块或匿名块（anonymous）：动态构造，只能执行一次，可调用其它程序，但不能被其它程序调用。

  命名块（named）：是带有名称的匿名块，这个名称就是标签。

  子程序（subprogram）：存储在数据库中的存储过程、函数等。当在数据库上建立好后可以在其它程序中调用它们。

• 二

  触发器 （Trigger）：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。

• 三

  程序包（package）：存储在数据库中的一组子程序、变量定义。在包中的子程序可以被其它程序包或子程序调用。但如果声明的是局部子程序，则只能在定义该局部子程序的块中调用该局部子程序。

1.3 标识符

PL/SQL程序设计中的标识符定义与SQL 的标识符定义的要求相同，要求和限制有：

标识符名不能超过30字符；
第一个字符必须为字母；
不区分大小写；
不能用’-‘(减号);
不能是SQL保留字。

1.4 变量命名

1.5 给变量赋值

使用 := 来为变量和常量赋初始值

• 示例

declare
	v_ename VARCHAR2(20) := 'Bill';
	v_sal NUMBER(7,2) :=1234.56;
	v_deptno NUMBER(2) := 10;
	v_empno NUMBER(4) := 8888;
begin
	insert into emp (empno,ename,JOB,sal,deptno,hiredate)
	VALUES(v_empno,v_ename,'Manager',v_sal,v_deptno,
	TO_DATE('1954.06.09','yyyy.mm.dd'));
commit;
end;
/

1.6 返回值

• 打开命令模式的返回值显示

set serveroutput on;

• 关闭命令模式的返回值显示

set serveroutput off;

• 查看命令模式的返回值显示

set serveroutput;

2 匿名块

只能执行一次的多条sql语句的集合,通常用于执行一个完整的事务

2.1 基本语法

set serveroutput on;

declare
 v_deptno number(2) := 99;
 v_dname  varchar2(20) := '南京销售部';
 v_loc    varchar2(20) := '南京';
begin   --执行sql语句
 insert into dept(deptno,dname,loc) values(v_deptno,v_dname,v_loc);
 --commit;
 returning to_char(deptno)||':'||dname||':'||loc into v_info;
 dbms_output.put_line('{'||v_info||'}插入成功');
end;
/

declare：定义变量
begin：执行sql语句
returning：返回值
into：将值注入某个变量
dbms_output.put_line：打印字符串
||：拼接字符串
end：结束PL/SQL块
/：执行PL/SQL块

2.2 创建匿名块

2.2.1 插入记录

set serveroutput on;

declare
row_id rowid;
info varchar2(40);
begin
	insert into dept values(90, '财务室', '海口')
	returning rowid,dname||':'||to_char(deptno)||':'||loc into row_id, info;
	dbms_output.put_line('ROWID:'||row_id);
	dbms_output.put_line(info);
END;
/

2.2.2 修改记录

set serveroutput on;

declare
row_id rowid;
info varchar2(40);
begin
	update dept set deptno=80 where dname='财务室'
	returning rowid,dname||':'||to_char(deptno)||':'||loc into row_id, info;
	dbms_output.put_line('ROWID:'||row_id);
	dbms_output.put_line(info);
END;
/

2.2.3 删除记录

set serveroutput on;

declare
row_id rowid;
info varchar2(40);
begin
	delete dept where dname='财务室'
	returning rowid,dname||':'||to_char(deptno)||':'||loc into row_id, info;
	dbms_output.put_line('ROWID:'||row_id);
	dbms_output.put_line(info);
END;
/

3 存储过程

3.1 概述

存储过程(Stored Procedure)是在大型数据库系统中， 一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。

存储过程是数据库中的一个重要对象。

• 语法：

create or replace procedure 过程名(过程参数1 类型(长度),...过程参数N 类型(长度))
IS
       变量定义
BEGIN 
       sql语句
END;
/

3.2 无参存储过程

set serveroutput on;

create or replace procedure update_dept_deptno_1
is
begin
  update emp set deptno = -1 where deptno = 20;
  update dept set deptno = 25 where deptno = 20;
  update emp set deptno = 25 where deptno = -1;
  commit;
  DBMS_OUTPUT.PUT_LINE('已成功将'||20||'号部门的部门号修改为'||25);
END;
/

exec update_dept_deptno_1;

3.3 有参存储过程

set serveroutput on;

create or replace procedure update_dept_deptno_2(deptno_old NUMBER,deptno_new NUMBER)
is
v_deptno_temp NUMBER(2) := -1;
begin
  update emp set deptno = v_deptno_temp where deptno = deptno_old;
  update dept set deptno = deptno_new where deptno = deptno_old;
  update emp set deptno = deptno_new where deptno = v_deptno_temp;
  commit;
  DBMS_OUTPUT.PUT_LINE('已成功将'||deptno_old||'号部门的部门号修改为'||deptno_new);
END;
/

exec update_dept_deptno_2(25,20);

3.4 调用存储过程

• 无参

exec 存储过程名;

• 有参

exec 存储过程名(参数1,参数2,...参数n);
--call不会打印显示
call 存储过程名(参数1,参数2,...参数n);

3.5 删除存储过程

drop procedure update_dept_deptno_1;
drop procedure update_dept_deptno_2;

4 函数

函数通常用于返回特定的数据。其实质是一个有名字的 PL/SQL块，作为一个schema对象存储于数据库，可以被反复执行。

函数通常被作为一个表达式来调用或存储过程的一个参数，具有返回值。

4.1 创建函数

create or replace function hello(str in varchar2)
return varchar2	--返回值类型
is
ret varchar2(20);
begin
ret := 'Hello,'||str||'!';
dbms_output.put_line(ret);
return ret;	--返回的具体的值
end;
/

4.2 调用函数

select hello('zzz') from dual;
select hello(ename) from emp;

4.3 删除函数

drop function hello;

4.4 示例

将emp表中名字的中间部分隐藏

--将首位字符中间的部分隐藏
select rpad(substr(ename,1,1),length(ename)-1,'*')||substr(ename,-1,1) from emp;
--将首位字符中间填充固定长度的字符隐藏
select rpad(substr(ename,1,1),3,'*')||substr(ename,-1,1) from emp;

create or replace function hide_name(str in varchar2)
return varchar2
is
ret varchar2(40);
begin 
  ret :=rpad(substr(str,1,1),length(str)-1,'*')||substr(str,-1,1);
  --dbms_output.put_line(ret);
return ret;
end;
/

select hide_name(ename) from emp;

5 触发器

触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。

触发器是当某个事件发生时自动地隐式运行。
触发器不能接收参数。
触发器中不能运行DDL语句

所以运行触发器就叫触发或点火（firing）。

5.1 触发器分类

• DML触发器

ORACLE可以以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。
通常用于物理删除的数据备份
:old 修改之前的记录 delete|update
:new 修改之后的记录 update|insert

• 视图操作代替触发器

由于在ORACLE里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。它就是ORACLE 专门为进行视图操作的一种处理方法。

• 系统触发器

ORACLE 提供了第三种类型的触发器叫系统触发器。它可以在ORACLE数据库系统的事件中进行触发，如ORACLE系统的启动与关闭等。

5.2 触发器的组成

• 触发事件：引起触发器被触发的事件

DML语句（INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作）
DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）
数据库系统事件（如系统启动或退出、异常错误）、
用户事件（如登录或退出数据库）。

• 触发时间

该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。

• 触发操作

即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。 例如：PL/SQL 块。

• 触发对象

包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。

• 触发条件

由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。

• 触发频率

说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。

5.3 创建触发器

5.3.1 DML触发器

• 删除备份触发器

create table emp_backup as select * from emp where 1=2;
alter table emp_backup add(update_time varchar2(30));

create or replace trigger emp_delete
       before delete
       on xykj.emp
       for each row
       --只对部门号为30的youxiao，不写改行则全表有效
       when (old.deptno=30)
begin
       insert into emp_backup
values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end;
/

delete emp where empno=7900;
delete emp where empno=8888;

select * from emp_backup;

• 修改备份触发器

--修改前
create or replace trigger emp_update_1
       before update
       on xykj.emp
       for each row
begin
       insert into emp_backup
values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end;
/

--修改后
create or replace trigger emp_update_2
       after update
       on xykj.emp
       for each row
begin
       insert into emp_backup
values(:new.empno,:new.ename,:new.job,:new.mgr,:new.hiredate,:new.sal,:new.comm,:new.deptno,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end;
/

update emp set sal=1111 where empno=7788;

select * from emp_backup;

5.3.2 系统触发器

• 创建备份表

create table log_event (
       user_name varchar2(10),
       address varchar2(20),
       logon_date timestamp,
       logoff_date timestamp
);

• 创建登录触发器

create or replace trigger tr_logon after logon on database
begin
  insert into log_event(user_name,address,logon_date)
  values(ora_login_user,ora_client_ip_address,systimestamp);
end;
/

• 创建登出触发器

create or replace trigger tr_logoff before logoff on database
begin
  insert into log_event(user_name,address,logoff_date)
  values(ora_login_user,ora_client_ip_address,systimestamp);
end;
/

select * from log_event;

5.3.3 INSTEAD OF 触发器

主要用于视图，运行开发者对视图执行INSERT、UPDATE、或DELETE操作，并由触发器定义实际的执行逻辑。

• 创建视图

create view emp_dept_view  
as
select  e.empno,e.ename,d.deptno,d.dname 
from  emp e,dept d where e.deptno=d.deptno;
--插入数据失败
insert into emp_dept_view (empno,ename,deptno) values(7777,'MARY',10);

• 创建触发器

create or replace trigger trg_instead_of_insert
	instead of insert on emp_dept_view
	for each row
begin
	insert into emp(empno,ename,deptno) 	values(:new.empno,:new.ename,:new.deptno);
end;
/ 
--插入成功
insert into emp_dept_view (empno,ename,deptno) values(7777,'MARY',10);

select * from emp;

5.4 删除触发器

drop trigger emp_delete;

6 程序包

方便对具有相同作用或针对同类对象的存储过程或函数的统一的管理

用来存放并管理procedure，function，变量的定义等，相当于java的包

6.1 创建包的引用

create or replace package dept_package
as
       --定义此包中过程名(没有过程体)
       procedure add_my_dept2(V_deptno number,V_dname varchar2,V_loc varchar2);
       procedure del_my_dept3(V_deptno number);
end;

6.2 创建包体

create or replace package body dept_package 
as    
	procedure add_my_dept2(V_deptno number,V_dname varchar2,V_loc varchar2)
	is
	begin
		insert into my_dept values(V_deptno,V_dname,V_loc);   
		commit;
	end add_my_dept2;---结束add_my_dept2
          
	procedure del_my_dept3(V_deptno number)
	is
	begin
		delete from my_dept where deptno= V_deptno;
		commit;
	end del_my_dept3;---结束del_my_dept3
end;