关于PL/SQL我写了一份从0到1的入门教程

PL/SQL

什么是PL/SQL

​ 许多时候我们会利用结构化查询语言（SQL）来访问和操作关系型数据库。这种语言的特点就是非过程化。也就是说使用的时候不用指明执行的具体方法和途径，即不用关注任何的实现细节。但这种语言也有一个问题，就是在某些情况下满足不了复杂业务流程的需求。
​ 所以就出现了PL/SQL，Oracle的PL/SQL语言正是完美的解决了这个问题，也就是过程化语言。和JAVA、C#等语言一样可以关注细节，用它可以实现复杂的业务逻辑。

PL/SQL的优点

​ 使用PL/SQL可以编写具有很多高级功能的程序，虽然这些功能可以通过多个SQL语句来完成同样的功能，但是PL/SQL具有以下优点：

• 使一组语句功能形成模块化程序开发
• 使用过程性语言控制程序结构
• 可以对程序中的错误进行处理
• 具有较好的可移植性
• 集成在数据中，调用更快
• 减少了网络的交互，有助于提高程序的性能

PL/SQL提供的新特性

PL/SQL提供了一些新的特性，可以进行复杂的信息处理

1. 软件包
2. 触发器
3. 存储过程
4. 函数
5. 异常处理

PL/SQL可以使用所有的SQL数据操作，游标控制和事务控制命令，以及所有的SQL函数和运算符。PL/SQL完全支持SQL数据类型

PL/SQL块的基本结构

PL/SQL中起作用的部分都是由基本块组成的：基本块有四个
组成部分：
--声明部分：
	DECLARE -- 可选部分
	-- 变量、常量、游标、用户定义异常声明
-- 执行体开始部分
	BEGIN 	-- 必要部分
	-- SQL语句
	-- PL/SQL语句
-- 异常处理部分
	EXCEPTION -- 可选部分
	-- 程序出现异常时，捕捉异常并处理异常
-- 执行体结束
	END; -- 必要部分（结尾）

在PL/SQL中处理变量

• 在声明部分声明和初始化变量
• 在执行部分为变量赋新值，或在表达中使用变量
• 在异常处理部分也可以使用变量
• 通过参数把值传递到PL/SQL块中
• 通过输出变量或者参数将值传出PL/SQL块

DECLARE
	-- 在declare部分声明变量，常量等
	-- 声明 变量的规范：  变量名称 变量类型 [:=缺省值];
	v_deptno number;
BEGIN
	-- 在 begin部分可以写 SQL语句，PL/SQL语句
	-- 在 begin部分中可以使用declare部分声明的变量，常量
	dbms_output.put_line('欢迎使用PL/SQL，执行查询语句之前，v_deptno=' || v_deptno);
	-- dbms_output.put_line();是输出语句，单引号包起来的将原封不动的给我们，|| 是拼接符
	select deptno into v_deptno from emp where empno = 7369;-- PL/SQL语句
	-- 把查询语句查询的结果赋值给v_deptno这个变量
	dbms_output.put_line('执行查询语句之后，v_deptno=' || v_deptno);
	delete from emp where deptno = v_daptno;
	delete from emp where deptno = v_daptno;
END;

在PL/SQL中处理变量

record类型

declare
    -- 声明记录类型
    type aaa is record(
        empno number(4),
        ename varchar2(50),
        sal number(4)
    ); -- 这TM不就跟我们创建表一样吗？只是不写约束
    --使用记录类型来声明变量
    aaa_info aaa; -- aaa_info 是我取得变量名，aaa是我上面声明的记录类型。
begin
	-- 给记录类型的变量赋值，只有类型中声明好的字段，才可以访问该字段
	
	select empno,ename,sal into aaa_info  from emp where empno=7
	aaa_info.empno:=10;
	aaa_info.ename:='张三';
	aaa_info.sal:=8000;
end;
/*
recode类型是由多个组件组成的一种类型，包含一个或多个组件，
每一个组件称为一个域（fiele）,域的数据类型可以是简单变量类型或是table类型。
在使用record变量时把多个域的集合作为一个逻辑单元使用，对记录类型变量（%type）赋值或
引用，都需要使用‘记录变量.域名’的方式来实现。主要用于从表中取出查询到的行数据。

记录类型可以包含一个或多个域，每个域相当于记录类型变量的一个属性。
在使用记录变量类型时，实际上是对记录类型变量的属性进行操作。每个域
都可以是不同的数据类型，存放不同类型的数据。
*/

%type和%rowtype

/*
除了可以使用已经确定的类型来声明变量，还可以使用%type和%rowtype来作为变量的类型。
当使用%type来声明变量的时候，%type的前缀可以是一个前面已经声明的简单类型的量，
也可以是一个表的字段名称。
*/ 
declare
 	a number(4); -- 这tm就是简单变量
 	b a%type; -- %type 也tm可以这么用
begin

end;

-- 还能特么这么用
declare
 	a emp.empno%type; -- a为我的变量名，emp是我的表名，emp.empno是我表中的字段。后面加个%type，意思就是用emp表中empno这个字段的数据类型作为变量名a的数据类型。
begin

end;

/*
%rowtype 前缀可以是一个表名，也可以是前面声明的一个记录类型的变量（该变量必须参照一个表，而不是自定义的记录类型）
*/
declare
    --e是我取的变量名，emp是我的表 row是行，type是类型。那rowtype就是所有行的类型
    -- 那这里的emp%rowtype 就是表示 e可以用emp表里行数据中的所有字段
    e emp%rowtype;
    -- 在使用%rowtype的时候，Oracle做了两件事：
    -- 1.用emp表中的字段及其类型来声明了一种记录类型
    -- 2.用这种记录来声明变量
begin
    select * into e  from emp where empno=7499;
    dbms_output.put_line(e.ename||e.sal);
end;

逻辑关系

IF语句

/*
IF语句的逻辑条件可以是一个逻辑表达式，也可以由多个逻辑表达式连接在一起，
连接多个表达式的符号就是连接操作符，包括and(与)or(或)not(非)
*/

declare
	v_total_sal number(9,2):=0; -- PL/SQL中用 := 赋值
	c_tax_rate constant number(3,2) :=8.25 -- PL/SQL中的常量只能赋值一次 
    -- 常量格式  常量名 constant 数据类型 := 默认值
    v_gender char(1);
    v_valid boolean not null := true;-- 布尔类型
    v_b boolean;
    v_num1 number(2) := 10;
    v_num2 number(2) := 10;
begin
	dbms_output.put_line('v_total_sal=' || v_total_sal);
	-- 可以把变量名通过输出语句来查看值
	v_b := (v_num1=v_num2);
	-- 这里的 = 相当于Java中的 == 的功能，执行 逻辑比较  操作
	if(v_b = true) then -- if 后面的表达式为true就执行 then后面的语句
		dbms_output.put_line('OK');
	else -- 反之 执行else中的语句
		dbms_output.put_line('NOT OK');
	end if;
end;

循环语句

/*
循环用于多次执行某些语句。
主要由三种循环类型：
	简单循环
	for循环
	while循环
	
循环的四个条件：
1、初始条件
2、循环条件
3、循环体
4、迭代条件
*/

declare
	i number(3):=1; -- 出生条件
begin
	loop
		dbms_output.put_line(i); --循环体
		i := i+1; -- 迭代条件
		exit when i>10; -- 循环条件
	end loop;
end;

/*
利用loop循环，向users表中插入100条数据，如果编号是奇数，性别为男，
如果编号是偶数，性别为女。
*/
declare
	v_sex varchar(5);
	v_count number(3):=1;
	v_name varchar(50);
begin
	loop
		if(mod(v_count,2)=0) then
			v_sex:='女';
		else
			v_sex:='男';
		end if;
		v_name :='王'||v_count;
		insert into users values(v_count,v_name,v_sex);
		v_count := v_count +1;
		exit when v_count>100;
	end loop;
end;
truncate table users;-- 清空表
/*
	while循环
*/
declare
	v_count number(3):=1; -- 初始条件
begin
	while (v_count <= 10) loop -- 循环条件
		dbms_output.put_line(v_count); -- 循坏体
		v_count := v_cout+1; -- 迭代条件
	end loop;
end;
/*
利用while循环，向users表中插入100条数据，如果编号是奇数，性别为男，
如果编号是偶数，性别为女。
*/
declare
	v_count number(3):=1; -- 初始条件
	v_sex varchar2(2);
	v_name varchar2(50);
begin
	while (v_count <= 100) loop -- 循环条件
		if(mod(v_count,2) = 0) then
			v_sex:='女';
		else
			v_sex:='男';
		end if;
		v_name :='王'||v_count;
		insert into users values(v_count,v_name,v_sex);
		v_count := v_cout+1; -- 迭代条件
	end loop;
	commit;
end;

/*
for循环
在使用for循环的时候，由Oracle来维护计数器
for 变量 in[reverse] 1..100 loop
	上面的reverse是倒序的意思
end loop;
*/
declare
	v_sex varchar2(3);
	v_name varchar2(50);
begin
	-- Oracle会自动的声明计数器，会自动的进行计数器的迭代
	for v_count in 1..100 loop
		if(mod(v_count,2) = 0) then
			v_sex:='女';
		else
			v_sex:='男';
		end if;
		v_name :='王'||v_count;
		insert into users values(v_count,v_name,v_sex);
	end loop;
	commit;
end;

SQL游标

/*
在执行执行增删改查语句的时候，Oracle都会开辟一块内存空间，
用来暂时存放收到SQL语句影响的数据。
这块内存空间就被称为游标区域，我们可以借助于游标来分析这些受到影响的数据
*/
/*
游标的分类：
1、隐式游标：
增删改查语句都会由隐式游标，也就是说，我们可以通过隐式游标来分析受到增删改查语句影响的数据。

2、显式游标：
在PL/SQL种执行select语句的特殊要求：
(1):select语句只能返回一条记录
(2):必须搭配使用into
也就是说：显示游标专门用来从数据库种查询多条数据的；
*/

游标的属性

/*
游标属性包括四种：
%rowcount	受SQL影响的行数
%found		boolean值，是否还有数据
%notfound	boolean值，是否已无数据
%isopen		游标是否打开
显示游标和隐式游标都有这四个属性。但是使用方法和含义却不相同。
*/

隐式游标

/*
在使用游标的时候，需要使用游标名称作为前缀。但是隐式游标没有名称，
所以在使用隐式游标的时候采取统一的一个名称SQL。
也就是说：隐式游标同意使用SQL前缀，例如：
SQL%rowcount	受SQL影响的行数
SQL%found		boolean值，是否还有数据
SQL%notfound	boolean值，是否已无数据
SQL%isopen		总是false
*/
declare
	v_count number(3);
begin
	delete from emp where deptno = 10;
	v_count :=SQL%rowcount;
	dbms_output.put_line('被删除的数据的条数是：'|| v_count);
end;

显示游标

/*
显式游标的使用：
1、可以用于暂存查询取出的多行结果，然后一行一行的处理。
2、显示游标就是专门用来查询多条数据的
3、按行处理查询返回的多行结果
4、显示游标首先将查询出的多行数据暂存在游标区域中，然后在PL/SQL中
借助循环语句手动的控制游标的多行操作，每次取出一条进行处理，直到取出
游标中所有的数据为止。

显式游标和隐式游标不同，在调用隐式游标的时候，通过SQL前缀来调用（SQL%rowcount），
而显式游标都有自己的名称，在调用时使用显示游标的名称，作为属性的前缀（游标名%rowcount）。
*/

declare
	-- 1、声明游标，一个显示游标，就是和一个有效的select语句绑死的
	 cursor cur_emp is select * from emp;
	 v_emp emp%rowtype;
begin
	-- 2、打开游标，就是执行了游标绑定的SQL语句，并且把受到影响的数据放入到了游标区域中
	open cur_emp;
	-- 3、取出游标中的一条数据装入记录类型的变量中
	fetch cur_emp into v_emp;
	-- 从记录类型的变量中取出查询数据
	dbms_output.put_line(v_emp.empno||','||v_emp.ename||','||
                         v_emp.job||','||v_emp.mgr||','||v_emp.hiredate||','||
                         v_emp.sal||','||nvl(v_emp.comm,0)||','||v_emp.deptno);
     -- 关闭游标，清空游标区域
     close cur_emp;
     
     -- 那么问题来了，我们不是说可以从显式游标中取出多条数据吗？但我们只取了一条，那是因为我们没有用循环。
end;



-- 使用loop循环和%notfound来遍历游标
declare
-- 1、声明游标，一个显示游标，就是和一个有效的select语句绑死的
	 cursor cur_emp is select * from emp;
	 v_emp emp%rowtype;
begin
	--dbms_output.put_line('查询游标的总条数'||cur_emp%rowtype);
	-- 在游标没有打开之前或者关闭之后，是无法使用的，就会导致无效的游标错误
	-- 在关闭之后，如果需要重新使用游标，需要重新打开游标
	
	-- 2、打开游标，就是执行了游标绑定的SQL语句，并且把受到影响的数据放入到了游标区域中
	open cur_emp;
	loop 
		-- 3、取出游标中的一条数据装入记录类型的变量中
        fetch cur_emp into v_emp;
        exit when cur_emp%notfound; -- 当游标中没有数据的时候，退出循环。
        dbms_output.put_line(v_emp.empno||','||v_emp.ename||','||
                             v_emp.job||','||v_emp.mgr||','||v_emp.hiredate||','||
                             v_emp.sal||','||nvl(v_emp.comm,0)||','||v_emp.deptno);

	end loop;
	dbms_output.put_line('查询游标的总条数'||cur_emp%rowcount);
	close cur_emp;
	--dbms_output.put_line('查询游标的总条数'||cur_emp%rowtype);
end;



-- 使用while循环和%found搭配使用分析游标数据
declare
 	cursor cur_emp is select * from emp;
	v_emp emp%rowtype;
begin
	open cur_emp;
	fetch cur_emp into v_emp;--取出游标中的一条数据装入记录类型的变量中
	-- 如果把这句放在while里面，那么游标里面的值就是0
	while(cur_emp%found) loop
		-- fetch cur_emp into v_emp;
		dbms_output.put_line(v_emp.empno||','||v_emp.ename||','||
                         v_emp.job||','||v_emp.mgr||','||v_emp.hiredate||','||
                         v_emp.sal||','||nvl(v_emp.comm,0)||','||v_emp.deptno);
         fetch cur_emp into v_emp;--迭代，如果没有的话，就会一直存入第一条数据，造成内容溢出。    
	end loop;
	dbms_output.put_line('查询游标的总条数'||cur_emp%rowcount);
	close cur_emp;
end;


-- 使用for循环，可以简化游标的开发，Oracle会自动的声明记录类型的变量，Oracle会自动的open，fetch、close游标。
declare
 	cursor cur_emp is select * from emp;
begin
	-- Oracle会自动的声明记录类型的变量v_emp,类型是emp%rowtype；
	-- for循环会自动 open 
	for v_emp in cur_emp loop
		dbms_output.put_line(v_emp.empno||','||v_emp.ename||','||
                         v_emp.job||','||v_emp.mgr||','||v_emp.hiredate||','||
                         v_emp.sal||','||nvl(v_emp.comm,0)||','||v_emp.deptno);
	end loop;
end;



-- 带参数的游标
/*
也就是带条件的游标，因为有时候我们不需要查询所有。
*/
declare
	cursor cur_emp(v_deptno number) is select * from emp where deptno=v_deptno;
	v_emp emp%rowtype;
begin
	open cur_emp(20);--查询20部门
	loop 
	fetch cur_emp into v_emp;
	dbms_output.put_line(v_emp.empno||','||v_emp.ename||','||
                         v_emp.job||','||v_emp.mgr||','||v_emp.hiredate||','||
                         v_emp.sal||','||nvl(v_emp.comm,0)||','||v_emp.deptno);
     	exit when cur_emp%notfound; -- 但游标中没有数据的时候，退出循环。
	end loop;
end;

使用游标更新或删除行数据

允许使用游标删除或更新活动集中的行
声明游标时必须使用 select....for update 语句

declare
	v_emp emp%rowtype;
	cursor mycur is select * from emp where deptno=20 for update;
begin
	open mycur;
	fetch mycur into v_emp;
	while mycur%found loop
		-- where current of 游标名; 对游标读取的所在行进行更新和删除
		update emp set sal = 100 where current of mycur;
		fetch mycur into v_emp;
	end loop;
	close mycur;
end;

REF游标

/*
REF 游标
*/
-- 查询所有的员工信息

declare
	-- 声明 ref 游标类型
	type myret is ref cursor;
	-- 声明ref游标类型的变量
	cur_emp myret;
	-- 声明变量
	v_emp emp%rowtype;
begin
	-- 开启 ref 游标
	open cur_emp for select * from emp;
	fetch cur_emp into v_emp;
	
	while cur_emp%found loop
		dbms_output.put_line(v_emp.empno||','||v_emp.ename||','||
                         v_emp.job||','||v_emp.mgr||','||v_emp.hiredate||','||
                         v_emp.sal||','||nvl(v_emp.comm,0)||','||v_emp.deptno);
         fetch cur_emp into v_emp;
	end loop;
	close cur_emp;
end;

集合类型

/*
集合的定义：
  集合是相同类型元素的组合。在集合中，使用唯一的下标来标识其中的每个元素。
  
而集合又分为以下几个类型：
1、索引表也叫关联数组
2、嵌套表
3、可变长度数组

*/

索引表

/*
索引表：
	可以使用整数（pls_integer,binary_integer）或字符串当下标，下标可以是不连续的。
	索引表的元素的个数是无限制的
	索引表只能用在plsql中，不能保存再数据库
	
定义索引表类型的语法:
	type 类型名称 is table of 数据类型（是集合中值的数据类型） index by 下标的数据类型
声明一个集合变量：
	集合变量的名字  集合的类型；
集合中数据的存取:
	集合变量(下标)
*/
declare
	-- 定义索引表类型
	type indextable is table of varchar2(20) index by pls_integer;
	-- 声明集合变量
	v_indextable indextable;
begin
	-- 使用：集合变量（下标） 存储值或者赋值
	v_indextable(1):='a';
	v_indextable(3):='b';
	v_indextable(5):='c';
	v_indextable(-8):='d';
	-- 取值:集合变量（下标）
	dbms_output.put_line(v_indextable(1));
	dbms_output.put_line(v_indextable(3));
	dbms_output.put_line(v_indextable(5));
	dbms_output.put_line(v_indextable(-8));
end;

集合的属性和方法

/*
集合名字.属性名   集合名字.方法名
1.first取集合的第一元素的下标                     
2.last取集合的最后一个元素的下标
3.count 取集合的总长度 
4.limit 取集合元素索引的最大值  (索引表和嵌套表是不限个数的，所以返回null，变长数组返回定义时的最大索引 )
5.delete([n]) 删除集合中的元素，加n表示下标，删除对应下标的值
6.extend(n[,ind]):扩展集合元素 n是一个数字，表示扩展的元素个数，ind是集合中的一个元素的下标，加上它表示扩展集合时，给扩展的元素加上值，值是ind这个下标对应的元素
7.next(下标)取当前元素下一个元素的下标
8.prior(下标)取当前元素上一个元素的下标 
*/
declare
	-- 定义索引表类型
	type indextable is table of varchar2(20) index by pls_integer;
	-- 声明集合变量
	v_indextable indextable;
begin
	-- 使用：集合变量（下标） 存储值或者赋值
	v_indextable(1):='a';
	v_indextable(3):='b';
	v_indextable(5):='c';
	v_indextable(-8):='d';
	
	-- 取值:集合变量（下标）
	dbms_output.put_line(v_indextable.first);
	dbms_output.put_line(v_indextable.last);
	dbms_output.put_line(v_indextable.count);
	--dbms_output.put_line(v_indextable.limit);
	if v_indextable.limit is null then
		dbms_output.put_line('v_indextable.limit返回的是个null');
	end if;
	v_indextable.dalete(); -- delete()不写下标则删除全部，写下表则表示删除那一个，被删除的下标则不能再次访问
	dbms_output.put_line(v_indextable.count);
	v_indextable.extend(2);-- 表示扩展俩个集合元素，但是索引表是无限值的，所以不能扩展
	dbms_output.put_line(v_indextable.next(-8));-- 返回下一个元素的下标
	dbms_output.put_line(v_indextable.prior(-8)); -- 返回上一个小标
end;

遍历索引表集合

/*
loop
*/
declare
  -- 定义索引表类型
  type indextable is table of varchar2(20) index by pls_integer;
  -- 声明集合变量
  v_indextable indextable;
  -- 声明变量存储下标
  v_index pls_integer;
begin
  -- 使用：集合变量（下标） 存储值或者赋值
  -- 下标不存在就是添加，如果存在就是修改
  v_indextable(1):='a';
  v_indextable(3):='b';
  v_indextable(5):='c';
  v_indextable(-8):='d';
  -- 从前往后
  v_index:=v_indextable.first;-- 获取第一个下标
  loop
    dbms_output.put_line(v_index);
    dbms_output.put_line(v_indextable(v_index));-- 遍历值
    exit when v_index=v_indextable.last;
    v_index:=v_indextable.next(v_index);
  end loop;
  /*
  从后往前
  v_index:=v_indextable.last;-- 获取最后一个下标
  loop
    dbms_output.put_line(v_index);
    dbms_output.put_line(v_indextable(v_index));-- 遍历值
    exit when v_index=v_indextable.first;
    v_index:=v_indextable.prior(v_index);
  end loop;
  */
end;
/*
for  使用for循环遍历数组元素下标一定要是连续
*/
declare
  type index_table is table of number(21) index by pls_integer;
  t index_table;
begin
  t(1):=11;
  t(2):=23;
  t(3):=43;
  t(4):=22;
  t(5):=21;
  t(6):=56;
  t(7):=55;
  t(8):=32;
  dbms_output.put_line('for循环开始了');
  for j in t.first..t.last loop
    dbms_output.put_line(t(j));
  end loop;
end;

/*
while
*/
declare
  type index_table is table of number(21) index by pls_integer;
  t index_table;
  n number;
begin
  t(-1):=11;
  t(-2):=23;
  t(3):=43;
  t(1):=22;
  t(9):=21;
  t(20):=56;
  t(8):=55;
  t(10):=32;
  n:=t.first;
  while n<=t.last loop
    dbms_output.put_line(t(n));
    n:=t.next(n);
  end loop;
end;

/*
集合里面的存储元素也可以是记录类型
*/
declare
	-- 声明记录类型
	type type_emp is record(
    	ename emp.ename%type,
    v_emp type_emp;
    v_emp2 type_emp;
    -- 声明集合类型
    type mytable is table of type_emp index by pls_integer;
    -- 声明集合变量
	v mytable;    
begin
	v_emp.ename :='张三';
	v_emp.sal :='4500';
	-- 存放记录类型数据
	v(1):=v_emp;
	-- 取记录类型数据
	v_emp2 :=v(1);
	dbms_output.put_line(v_emp2.ename||','||v_emp2.sal);
end;

嵌套表

/*
嵌套表
使用整数（只能为正数）作为下标，下标是连续的；
元素的个数是无限制的，可以在plsql中使用，也可以存储到数据库中。

定义嵌套标类型：
	type 类型名称 is table of 数据类型（存储数据的数据类型）；
声明嵌套标类型变量：
	变量名 类型名称
	
数据的存取和索引表相同

嵌套表在赋值之前需要初始化
嵌套表的初始化：
	变量名:=类型名（）； -- 初始化
	变量名:=类型名(值，值，值);
*/
declare
	-- 定义嵌套表类型
 	type mytable is table of varchar2(20);
 	-- 声明变量
 	v mytable;
begin
	--为嵌套表初始化 
	v:=mytable('a');
	v(1):='aa';
	dbms_output.put_line(v(1));
end;

declare
	-- 定义嵌套表类型
 	type mytable is table of varchar2(20);
 	-- 声明变量
 	v1 mytable;
 	v2 mytable;
begin
	--为嵌套表初始化 
	v:=mytable('a');
	v(1):='aa';
	dbms_output.put_line(v(1));
	-- 扩展集合元素，默认值是null
	v.extend(2);  -- 扩展两个元素
	v.extend(2,1) -- 扩展两个元素，值是下标1的值
end;

遍历嵌套表

/*
loop
*/
declare
	-- 定义嵌套表类型
	type mytable is table of varchar2(20);
	-- 声明变量
	v1 mytable;
	-- 声明变量 下标
	n number;
begin
	v1:=mytable('a','b','c');
	n:=v1.first;
	loop
	dbms_output.put_line(n);
	exit when n=v1.last;
	n:=v1.next(n);
	end loop;
end;
/*
while
*/
declare
  -- 定义嵌套表类型
  type mytable is table of varchar2(20);
  -- 声明变量
  v mytable;
  n number;
begin
  v:=mytable('a','b','c');
  n:=v.first;
  while n<=v.last loop 
    dbms_output.put_line(v(n));	
	   n:=v.next(n);
  end loop;
end;

/*
for  因为他的下边是连续的
*/
declare
	-- 定义嵌套表类型
	type mytable is table of varchar2(20);
	-- 声明变量
	v1 mytable;
begin
	v1:=mytable('a','b','c');
	for n in v1.first..v1.last loop
		dbms_output.put_line(n);
	end loop;
end;

嵌套表在数据库中的使用

-- 格式
create type 类型名称 is table of 数据类型;

create type mytable is table of varchar2(20);
	-- 声明变量 mytable是一个嵌套表类型（已经在我们数据库中）
	v mytable;
begin
	v:=mytable('a','abc','b');
	for n in v.first..v.last loop
		dbms_output.put_line(n);
	end loop;
end;

/*
创建表的时候也可以使用嵌套表类型
create table 表名(
	列  数据类型，
	嵌套表列  嵌套表类型
)nested table 嵌套表类型列名 store as 表名（是数据库中没有的表）；
*/
-- 创建
create table a(
	id number,
    namelist mytable
)nested table namelist store as myname;
-- 存值
insert into a(id,namelist) values(1,mytable('tom','jack','jerrt'));
-- 查询
select * from a;
-- 查询嵌套表中的数据
select * from table(select namelist from a where id =1);
-- 删除时要注意，先删除表在删除类型
drop table a;
drop type mytable;

变长数组

/*
使用整数(只能为正)(pls_integer,binary_integer)作为下标，下标也是连续的；
元素个数有限制的，可以使用在plsql中也可以存储在数据库中。
和嵌套表一样，使用前呢需要初始化
*/

-- 格式
type 类型名称 is varray(长度)|varying array(长度) of 数据类型(保存的数据的数据类型);

declare
	-- 声明变长数组类型
	type myvar is varray(5) of varchar2(20);
	-- 声明变量
	v myvar;
begin
	v:=myvar('a','b','c');
	dbms_output.put_line(v(1));
	dbms_output.put_line(v(2));
	dbms_output.put_line(v(3));
	-- v.delete()  变长数组不能指定删除，
	-- v.extend() 扩展时不能超过声明时的长度
end;

遍历变长数组

/*
loop
*/
declare
	-- 声明变长数组类型
	type myvar is varray(5) of varchar2(20);
	-- 声明变量
	v myvar;
	-- 声明变量
	n pls_integer;
begin
	v:=myvar('a','b','c','d');
	n:=v.first;
	loop
		dbms_output.put_line(n);
		exit when n=v.last;
		n:=v.next(n);
	end loop;
end;
/*
for
*/
declare
	-- 声明变长数组类型
	type myvar is varray(5) of varchar2(20);
	-- 声明变量
	v myvar;
begin
	v:=myvar('a','b','c','d');
	for n in v.first..v.last loop
		dbms_output.put_line(n);
	end loop;
end;
/*
while
*/
declare
	-- 声明变长数组类型
	type myvar is varray(5) of varchar2(20);
	-- 声明变量
	v myvar;
	-- 声明变量
	n pls_integer;
begin
	v:=myvar('a','b','c','d');
	n:=v.first;
	while n<=v.last loop
		dbms_output.put_line(n);
		n:=v.next(n);
	end loop;
end;

变长数组在数据库中的使用

/*
变长数组在数据库中的使用：
create type 类型名称 is varray(长度)|varying array(长度) of 数据类型;
*/
create type myvarray is varray(5) of varchar2(20);
declare
	-- 声明变量
	v myvarray;
begin
	v:=myvarray('a','b','c','d');
	for n in v.first..v.last loop
		dbms_output.put_line(n);
	end loop;
end;

/*
创建表
create table 表名(
	列名  数据类型，
	数组列 数组类型
);
*/
create table b(
	id number,
    abc myvarray
);
-- 查询
select * from b;
-- 增加
insert into b(id,abc) values(1,myvarray('a','b','c','d'));
select * from table(select abc from b where id=1);

bulk collect

/*
可以把一组数据取出来存入一个集合类型中
我们之前那个select...into 变量：只能查出一条数据保存到变量中
*/
declare
	v_ename emp.ename%type;
begin
	select ename into v_ename  from emp where empno=7369;
	dbms_output.put_line(v_ename);
end;
/*
而使用 select。。bulk collect into 集合类型的变量：
可以取多条数据存储在集合中
*/
declare
	v_ename emp.ename%type;
	-- 定义集合类型嵌套表
	type mytable is table of emp.ename%type;
	-- 定义集合变量
	v mytable;
begin
	select ename into v_ename from emp where empno=7369;
	dbms_output.put_line(v_ename);
	dbms_output.put_line('--------------');
	select ename bulk collect into v from emp;
	-- 遍历集合中所以的元素
	for n in v.first..v.last loop
		dbms_output.put_line(v(n));
	end loop;
end;

/*
bulk collect 还可以玩游标
fetch 游标 bulk collect into 集合类型变量
*/
declare
  -- 声明游标变量
  cursor cur_emp is select * from emp;
  -- 声明变量
  v_emp emp%rowtype;
begin
  open cur_emp;
  loop
    fetch cur_emp into  v_emp;
    exit when cur_emp%notfound;
    dbms_output.put_line(v_emp.empno);
  end loop;
  close cur_emp;
end;

declare
	-- 声明游标变量
	cursor cur_emp is select * from emp;
	-- 声明集合类型嵌套表
	type mytable is table of emp%rowtype;
	-- 声明集合变量
	v mytable;
begin
	open cur_emp;
		fetch cur_emp bulk collect into v;
		for n in v.first..v.last loop
			dbms_output.put_line(v(n).empno)
		end loop;
	close cur_emp;
end;

批量绑定

/*
格式：
forall 变量 in 集合
	sql语句：insert、delete、update
*/
-- 删除所有有部门编号的员工信息
declare
	-- 定义集合
	type mytable is table of dept.deptno%type;
	-- 定义集合变量
	v mytable;
begin
	select deptno bulk collect into v from dept;
	/*
	这是一种
	for n in v.first..v.last loop
		delete from emp where deptno = v(n);
	end loop;
	*/
	-- 这是forall
	forall n in v.first..v.last
		delete from emp where deptno = v(n);
		--update emp set sal=8000 where deptno=v(n);
		--insert into emp(empno) values(v(n));
end;

动态sql

/*
它是指在PL/SQL程序执行时生成的SQL语句，在编译过程中对它不做处理。
而是在程序运行时动态构造语句、对语句进行语法分析并执行。

我们之前在PL/SQL中只能使用DML、DQL。不能直接使用DDL;
但是我们可以通过动态SQL来执行DDL语句命令。

格式：
execute immediate 字符串参数 [into] 变量 using 参数
*/

-- 按照员工编号查询员工的信息
declare
	v_emp emp%rowtype;
begin
/*
	select * into v_emp from emp where empno=7369;
	dbms_output.put_line(v_emp.empno||','||v_emp.ename);
*/ 
    execute immediate 'select * from emp where empno=7369' into v_emp;
    dbms_output.put_line(v_emp.empno||','||v_emp.ename);		
end;



declare
	v_emp emp%rowtype;
	v_sql varchar2(100);
begin
/*
	select * into v_emp from emp where empno=7369;
	dbms_output.put_line(v_emp.empno||','||v_emp.ename);
*/ 
	v_sql:='select * into v_emp from emp where empno=7369';
    execute immediate v_sql into v_emp;
    dbms_output.put_line(v_emp.empno||','||v_emp.ename);		
end;


declare
	v_emp emp%rowtype;
	v_sql varchar2(100);
begin
/*
	select * into v_emp from emp where empno=7369;
	dbms_output.put_line(v_emp.empno||','||v_emp.ename);
*/ 
	v_sql:='select * into v_emp from emp where empno=:a and deptno=:b';
    execute immediate v_sql into v_emp using 7369,20;
    dbms_output.put_line(v_emp.empno||','||v_emp.ename);		
end;



--执行DDL语句

-- 创建test001表
begin
  execute immediate 'create table test001(id number(2),name varchar2(10))';
end;


declare
	stmt varchar2(200):='create table test002(id number(2),name varchar2(10))';
begin
  execute immediate stmt;
end;
总结：PLSQL块中不能直接执行DDL语句，所以可以用动态sql去执行
-- excute immediate sql语句

--案例1: 编写一个plsql块，往test001中插入一条数据，要求从键盘输入
declare
    v_id test001.id%type:=&id;
    v_name test001.name%type:=&name;
begin
  execute immediate 'insert into test001 values(:1,:2)' using v_id,v_name;
end;
--案例2：编写一个pl/sql块，往test001中插入一条记录，要求值是从键盘输入
declare
    v_id test001.id%type:=&id;
    v_name test001.name%type:=&name;
begin
  execute immediate 'insert into test001 values(:1,:2)' using v_id,v_name;
end;
总结：execute immediate sql语句 using 值1，值2，…;
:1,:2：需要用到变量的地方用:1,:2来代替

-- 案例3：查询test001，从键盘接收id，输出其姓名
declare
    v_id test001.id%type:=&id;
    v_name test001.name%type;
begin
  execute immediate 'select name from test001 where id=:1' into v_name using v_id;
  dbms_output.put_line('姓名是：'||v_name);
end;
 总结：execute immediate sql语句 into 变量1 using 变量2;
(1)sql语句中没有select...into...
(2)where id=:1:1是占位符，表示这个地方在运行的时候需要有一个值替代

--案例4：从键盘输入一个员工编号，查询该员工的姓名和工资，如果工资小于2000元，那么给他增加500，返回增加后的工资。

declare
    v_empno emp.empno%type:=&no;
    v_ename emp.ename%type;
    v_sal   emp.sal%type;
begin
  select ename,sal into v_ename,v_sal from emp where empno=v_empno;
  if v_sal<2000 then
    execute immediate 'update emp set sal=sal+500 where empno=:1 returning sal into :2' using v_empno returning into v_sal;
  end if;
  dbms_output.put_line('新工资是：'||v_Sal);
end;
总结：execute immediate sql语句 using 变量1 returning into 变量2;
update emp set sal=sal+500 where empno=:1 returning sal into :2 意思是更新sal,返回更新后的工资。

--案例5：从键盘接受一个员工编号，删除该员工信息
declare
	v_empno emp.empno%type:=&no;
begin
  execute immediate 'delete from emp where empno=:num' using v_empno;
end;

异常处理

概述

/*
在PL/SQL中出现的异常，如果我们没有处理，异常会被传递给调用环境，中断我们的程序。
*/
declare
  v_job emp.job%type;
begin
  select job into v_job from emp where empno=8001;
  dbms_output.put_line(v_job);-- 异常  没有找到数据
  dbms_output.put_line('程序结束');
end;
/*
PL/SQL程序会从发生异常的代码处中断，以后的代码是无法执行的；
而Oracle异常分为俩种类型：系统异常、自定义异常。
而系统异常又分为预定义异常和非预定义异常。
*/

预定义异常

--Oracle以及为这种异常定义好了名称，我们在异常处理部分直接通过异常名称进行了捕获

ORACLE定义了它们的错误编号和异常名字，常见的预定义异常处理如下:
ACCESS_INTO_NULL:对应ORA-06530为了引用对象属性，必须首先初始化对象。直接引用未初始化的对象属性时，会发生异常
CASE_NOT_FOUND:对应ORA-06592，当CASE语句的WHEN子句没有包含必须条件分支或者ELSE子句时，会触发
COLLECTION_IS_NULL:对应ORA-06531,在给嵌套表变量或者varrary变量赋值之前，必须首先初始化集合
CURSOR_ALREADY_OPEN:ORA-06511，当已打开游标上执行OPEN操作时会触发
INVALID_CURSOR:ORA-01001,当视图从未打开游标，提取数据，或者关闭未打开游标时会触发
INVALID_NUMBER:ORA-01722,当内嵌SQL语句不能将字符转变成数字时会触发
LOGIN_DENIED:ORA-01017,连接Oracle数据库时，如果提供了不正解的用户名和口令时会触发
NO_DATA_FOUND:ORA-01403执行SELECT INTO 未返回行或者引用了未初始化的PL/SQL表元素时会触发
NOT_LOGGED_ON:ORA-01012没有连接数据库执行SQL时会触发
PROGRAM_ERROR:ORA-06501存在PL/SQL内部问题，在这种情况下需要重新安装数据字典视图和PL/SQL包
ROWTYPE_MISMATCH:ORA-016504当执行赋值操作时，如果宿主变量和游标变量不兼容的返回类型时，会触发
SELF_IS_NULL: ORA-30625,当使用对象类型时，如果在null实例上调用成员方法时，会触发
STORAGE_ERROR:ORA-06500当执行PL/SQL块时，如果超出内存空间或者内存被破坏，会触发
SUBSCRIPT_BEYOND_COUNT:ORA-06533当使用嵌套或者varray元素的范围进会触发
SUBSCRIPT_OUTSIDE_LIMIT:ORA-06532，使用嵌套表或者varray元素时，如果元素下标为负值时，会触发
SYS-INVALID_ROWID:ORA-01410当字符串转变为ROWID时如果使用了无效字符串，会触发
TIMEOUT_ON_RESOURCE:ORA-00051当等待资源时如果出现超时会触发
TOO_MANY_ROWS:ORA-01422当执行SELECT INTO时，如果返回超过一行、会触发
VALUE_ERROR:ORA-06502,执行赋值时，如果变量长度不足，会触发
ZERO_DIVIDE：ORA-01476如果用数字值除0，会触发

/*
当plsql程序中，在begin部分的语句出现了异常，就会进入到exception部分执行异常处理功能
只要我们采取了处理措施后，就不会产生报错信息
*/
declare
  v_job emp.job%type;
  cursor mycur is select * from emp;
  v_num number(8);
begin
  select job into v_job from emp where empno=8001;
  dbms_output.put_line(v_job);-- 异常  没有找到数据
  dbms_output.put_line(mycur%rowcount);
  v_num := 10/0;
  insert into dept values(10,'气氛组','青岛');
exception
	when NO_DATA_FOUND then
		dbms_output.put_line('执行的select语句没有查询到结果');
	when TOO_MANY_ROWS then
		dbms_output.put_line('执行的select语句不能查询多条结果');
    when INVALID_CURSOR then
		dbms_output.put_line('无效的游标');
	when ZERO_DIVIDE then
		dbms_output.put_line('0不能作为除数');
	when DUP_VAL_ON_INDEX then --唯一索引中插入重复值
		dbms_output.put_line('违反了主键约束');
	when VALUE_ERROR then 
		dbms_output.put_line('赋值错误');	
	when others then -- 最牛逼的异常处理  不会有一条异常信息；
		dbms_output.put_line('PL/SQL中发生了异常');
end;


注意：
SQLCODE与SQLERRM为异常处理函数。
函数SQLCODE用于取得Oracle错误号，
函数SQLERRM用于取得与错误号对应的相关错误消息
用法：
exception
	when NO_DATA_FOUND then
		dbms_output.put_line('执行的select语句没有查询到结果'||','||sqlcode||','||sqlerrm);

非预定义异常

	非预定异常，也是因为违反了Oracle的规则，Oracle会产生报错信息（有错误编号和错误信息），
但是Oracle并没有为这类错误定义好异常名称（但有错误编号），那么我们可以自已定义一种异常名称，并将这样异常名称和错误编号进行绑定；
										帕克码
	我们使用的时候，先声名一个异常名，通过伪过程 PRAGMA EXCEPTION_INIT，将异常名与错误号关联起来。

declare
  v  exception; -- 自己定义的一种异常名称
  -- 通过 PRAGMA EXCEPTION_INIT(异常名称，错误编码)
  pragma exception_init(v,-04098); 
begin
  delete from dept where deptno = 10; 
exception
  when v then
    dbms_output.put_line('违反了外键约束');
end;


-- 在不知道错误编码的时候怎么用
begin
  delete from dept where deptno = 10; 
exception
  when others then
    dbms_output.put_line('违反了外键约束'||sqlcode);
end;

自定义异常

程序员从业务角度出发，制定的一些规则和限制。

抛出异常：

润丝
raise 异常名称;

declare
	myex exception;
begin
	raise myex;
	-- null;
end;

-- 完成2个数相除，要求除数不能为5
declare
	v_1 number:=&请输入第一个数:
	v_2 number:=&请输入第二个数;
begin
	if v_2 = 5 then
		dbms_output.put_line('除数不能为5');
	end if;
	dbms_output.put_line(v_1/v_2);
end;
这么写的话，我的程序依然会往下执行。



declare
	v_1 number:=&请输入第一个数;
	v_2 number:=&请输入第二个数;
	myex exception;
begin
	if v_2 = 5 then
		dbms_output.put_line('除数不能为5');
		raise myex; -- 抛个异常 让程序停止
	end if;
	dbms_output.put_line(v_1/v_2);
end;

declare
	v_1 number:=&请输入第一个数;
	v_2 number:=&请输入第二个数;
	myex exception;
begin
	if v_2 = 5 then
		dbms_output.put_line('除数不能为5');
		raise myex;
	end if;
	dbms_output.put_line(v_1/v_2);
exception
  when myex then -- 自己定义的异常可以自己处理
    dbms_output.put_line('除数不能为5');
end;

引发应用程序错误

可能不是很多人知道 RAISE_APPLICATION（哎破tei谁）_ERROR（哎我） 的用途是什么，虽然从字面上已经猜到这个函数是干什么用的。平时用来测试的异常处理
我们都是通过dbms_output.put_line来输出异常信息，但是在实际的应用中，需要把异常信息返回给调用的客户端。

declare
  rate_exception exception;
  b number(20);
begin
  b := &b;
  if b not in (2, 1, 3) then
    raise rate_exception;
  else
    dbms_output.put_line('该水果名称为香蕉' || b);
  end if;
exception
  when rate_exception then
    raise_application_error(-20003, '这是我自己定义的');
end;

存储过程和函数

概述：
ORACLE 提供 可以把PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。存储过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。

存储过程

存储过程，就是一段存储在数据库中执行某块业务功能的程序模块。它是由一段或者多段的PL/SQL代码块或者SQL语句组成的一系列代码块。

--创建存储过程的语法
create or replace procedure 存储过程名字 [(参数 in| out|in out 参数的数据类型)]
as | is
声明部分：
begin
plsql代码块
exception
异常处理
end;

-- 无参数存储过程
create or replace procedure p1
is
	cursor cur_emp is select * from emp;
begin
	for v_emp in cur_emp loop
	dbms_output.put_line(v_emp.ename);
	end loop;
end;

/*
如果使用/调用存储过程
*/
-- 1、在plsql块中调用
begin
 p1;-- p1();
end;
-- 2、call 命令调用
call p1();
-- 3、 execute 命令调用  （属于sqlplus命令） 需要通过cmd小黑窗执行 记得 set serverout on
execute p1();

/*
查询存储过程 user
select * from user_objects  --数据字典
*/

/*
删除存储过程
drop procedure 存储过程的名字；
*/
drop procedure  p1;
/*
参数的三种模式
IN  用于接受调用程序的值。默认的参数模式
OUT  用于向调用程序返回值 
IN OUT  用于接受调用程序的值，并向调用程序返回更新的值
*/
-- IN  用于接受调用程序的值。默认的参数模式
--写一个存储过程，根据传入部门编号，查询该部门下的所有员工，并打印员工信息
create or replace procedure p2(v_deptno in number)-- 这里的数据类型不需要加长度，这里in可以省略，默认为in
is
begin
 for v_emp in (select * from emp where deptno = v_deptno) loop
 	dbms_output.put_line(v_emp.empno||','||v_emp.ename);
 end loop;
end;

-- 使用
begin
	p2(10);
end;

begin
	p2(v_deptno => 10);
end;

declare
	v number :=20;
begin
	p2(v); 
end;

create or replace procedure p2_2(v_deptno1 in number,v_deptno2 in number)
is
begin
 	dbms_output.put_line(v_deptno1||','||v_deptno1);
end;

begin
	p2_2(10,20);
end;

begin
	p2(v_deptno2 => 10,v_deptno1 => 20);-- 顺序可以打乱
end;

declare
	v1 number :=20;
	v2 number :=20;
begin
	p2_2(v1,v2); 
end;

-- OUT  用于向调用程序返回值 
--根据传入的员工编号，查询出员工信息并以输出参数的方式返回
create or replace procedure p3(v_empno in emp.empno%type,v_emp out emp%rowtype)
is
begin
	select * into v_emp from emp where empno = v_empno;
end;

-- 使用
declare
	v_e emp%rowtype;
begin
	p3(7369,v_e);
	dbms_output.put_line(v_e.empno||','||v_e.ename);
end;

--IN OUT  用于接受调用程序的值，并向调用程序返回更新的值
--根据传入的员工编号，查询出员工信息并以输出参数的方式返回
create or replace procedure p4(v_emp in out emp%rowtype)
is
begin
	select * into v_emp from emp where empno = v_emp.empno;
end;
-- 使用
declare
	v_e emp%rowtype;
begin
	v_e.empno := 7369;
	p4(v_e);
	dbms_output.put_line(v_e.empno||','||v_e.ename);
end;


-- 其他用户调用scott用户的存储过程
-- 创建用户
create user test6 identified by root;
grant connect,resource to test6;
-- 使用test6登录
begin
  scott.p1();
end;
-- 不能访问

-- 使用 scott用户授权才可以
grant execute on p1 to test1;

注意：如果需要在存储过程中访问其他用户下的表时，必须赋值权限 grant select on 表名 to 用户;

函数

函数是通过关键字function按照自己的需求把复杂的业务逻辑封装进PL/SQL函数中，函数提供一个返回值，返回给使用者。

--创建函数的语法
create [or replace] function 函数名称[(形式参数 参数类型,...)]
return 返回值类型
is
   声名变量;
begin
  plsql代码块;
  return 返回值;--return后面的内容不执行
end;

-- 无参数的函数
create or replace function  f1
return varchar2
is

begin
	return '我爱你';
end;	

-- 查看函数
select * from user_objects where object_ type = 'FUNCTION';

-- 使用函数，调用函数
1、用在sql语句中
select f1 from dual;
select f1 from emp;
select * from emp where f1()='我爱你';

2、在plsql块中使用
begin
	dbms_output.put_line(f1);
end;

-- 删除函数
-- drop function 函数名
drop function f1;


-- 有参数的函数
-- 编写函数，接受一个数，返回1-这个数的和。
create or replace function  f2(n number) 
return number
is
	v_sum number:=0;
begin
	for i in 1..n loop
		v_sum :=v_sum+i;
	end loop;
	
	return v_sum;
end;	

-- 使用
select f2(3) from dual;


-- 多个函数的写法
-- 编写函数，接受2个值，返回最大值
create or replace function  f3(n1 number,n2 number) 
return number
is
--	v number;	
begin
	if n1>n2 then
	--	v:=n1;
		return n1;
	else
	-- v:=n2;
		return n2;
	end if;
	--return v;
end;	

-- 使用
select f3(2,3) from dual;

触发器

什么是触发器？

我们生活就有许多触发器，比如：灯的开关，手枪扳机。。。

而数据库的触发器与表相关联的、存储的pl/sql程序。每当一个特定的数据操作语言在指定的表中发出时，Oracle自动执行触发器中定义的语句序列。

触发器在数据库中以独立的对象存储（就是可以在侧边栏找到），它与存储过程不同的是，存储过程通过其他程序来启动运行或直接运行，而触发器是由一个事件来启动运行。即触发器是当某个时间发生时自动地隐式运行。并且，触发器不能接收参数。Oracle事件指的是对数据库的表进行的 insert、 update、 delete操作或对视图进行类似的操作。

-- 触发器可用于
1、数据确认
2、实施复杂的安全性检查
3、做审计，跟踪表上所做的数据操作等
4、数据的备份和同步
-- 触发器分类
1、DML触发器：
	表级触发器（语句级触发器）
	行级触发器
	替换触发器
2、模式（DDL）触发器
3、数据库级触发器

创建触发器的语法

create or replace trigger 触发器名
before | after
delete [or] | insert[or] | update [of 列名]
on 	表名
[for each row][where(条件)]
declare
	....
begin
	PLSQL块
end;


for each row 作用时标注此触发器是行级触发器；没有标注就是语句级触发器

在触发器中触发语句与为记录变量的值

触发语句	:old	:new
insert	所有的字段都为空（null）	将要插入的值
update	更新以前该行的值	更新后的值
delete	删除以前该行的值	所有字段都为空（null）

案例

表级触发器

create or replace trigger tri1
before insert on dept --给这个表插入数据之前
begin
	dbms_output.put_line('触发器执行了');
end;

-- 查看触发器
select * from user_objects where object_type='TRIGGER';

-- 如何执行触发器，在特定的事件发生的时候执行
insert into dept values(50,'财务部','北京');--执行
update dept set loc='上海' where deptno=50;--不会执行
delete from dept where deptno=50;-- 不会执行

-- 如果我要实现增删改都要触发就这么做
create or replace trigger tri1
before insert or update or delete on dept --给这个表插入数据之前
begin
	dbms_output.put_line('触发器执行了');
end;


-- 也可以指定列名
create or replace trigger tri1
before insert or update of loc,dname or delete on dept --给这个表插入数据之前
begin
        dbms_output.put_line('触发器执行了');
end;

update dept set deptno=52 where deptno=50;--不会执行


before:表示在sql语句执行前，执行触发器的代码
after:表示在sql语句执行之后，执行触发器的代
以下三个都是boolean类型的
updating:如果触发这个触发器的是一条update语句，它值就是true
deleting:如果触发它是一条delete语句，它值就是true
inserting:如果触发它是一条insert语句，它值就是true

create or replace trigger tri1
before insert or update or delete on dept --给这个表插入数据之前
begin
	if updating then
		dbms_output.put_line('触发器执行了--改');
	end if;
	if deleting then
		dbms_output.put_line('触发器执行了--删');
	end if;
	if inserting then
		dbms_output.put_line('触发器执行了--增');
	end if;
end;
insert into dept values(50,'财务部','北京');
update dept set loc='上海' where deptno=50;
delete from dept where deptno=50;


-- 删除触发器
drop trigger 触发名;

行级触发器

对DML语句修改的每个行执行一次,有for each row语句，在begin代码段中可以使用:new和:old。
:new --是 一个引用最新的列值
:old --是 一个引用以前的列值
-- 为dept表创建行级触发器
create or replace trigger tri2
before update or delete or insert
on dept for each row
begin
	if updating then
		dbms_output.put_line('修改了');
	end if;
	if deleting then
		dbms_output.put_line('删除了');
	end if;
	if inserting then
		dbms_output.put_line('增加了');
	end if;
end;

insert into dept values(88,'技术部','北京');
update dept set loc='销售部' where deptno=56 or deptno=57;
delete from dept where deptno=50;

:new --是 一个引用最新的列值
:old --是 一个引用以前的列值
create or replace trigger tri2
before update or delete or insert
on dept for each row
begin
	if updating then
		dbms_output.put_line('修改了'||:old.dname||:new.dname);
	end if;
	if deleting then
		dbms_output.put_line('删除了'||:old.dname||:new.dname);
	end if;
	if inserting then
		dbms_output.put_line('增加了'||:old.dname||:new.dname);
	end if;
end;
insert into dept values(59,'技术部','广州');
update dept set dname='公关部' where deptno = 59 or deptno = 52;
delete from dept where deptno = 59;


before:表示在sql语句执行前，执行触发器的代码（前置）
create or replace trigger tri2
before update or delete or insert
on dept for each row
begin
	if updating then
		dbms_output.put_line('修改了'||:old.dname||:new.dname);
	end if;
	if deleting then
		dbms_output.put_line('删除了'||:old.dname||:new.dname);
	end if;
	if inserting then
		--dbms_output.put_line('增加了'||:old.dname||:new.dname);
		if :new.deptno <80 then
			:new.deptno:=80;
		end if;
	end if;
end;
insert into dept values(60,'技术部','广州');


after:表示在sql语句执行之后，执行触发器的代码（后置）
create or replace trigger tri2
after update or delete or insert
on dept for each row
begin
	if updating then
		dbms_output.put_line('修改了'||:old.dname||:new.dname);
	end if;
	if deleting then
		dbms_output.put_line('删除了'||:old.dname||:new.dname);
	end if;
	if inserting then
		--dbms_output.put_line('增加了'||:old.dname||:new.dname);
		if :new.deptno <80 then
			:new.deptno:=88;
		end if;
	end if;
end;


--一般我们都会用前置触发器
-- 可以这么玩：
-- 可以生成主键值使用的序列

create table seqtab(
	id number(11) primary key,
    name varchar2(30)
);
-- 创建序列
create sequence seq start with 1 increment by 1;
-- 查看下一个序列
select seq.nextval from dual;
-- 正常添加
insert into seqtab(id,name) values(seq.nextval,'tom');

create or replace trigger tri3
before insert on seqtab for each row
begin
	:new.id := seq.nextval;
end;

insert into seqtab(name) values('刘德华'); 

替换触发器

替换触发器使用在视图上，而且是行级的触发器。

视图的修改是有限制的：
1、视图的修改还是基于基表的
2、有约束限制不能修改
3、有聚合函数
4、有表达式，伪劣。（有的是表达式计算的）
5、复杂视图（多表联合查询的视图）键值保存表的列可以修改，非键值保存表不能修改。
-----------以下为键值保存表和非键值保存表的理解--------------------
-- 什么是非键值保存表？
create table mytable1(
	id number,
    name varchar2(100)
);
insert into mytable1 values(1,'张三');
insert into mytable1 values(2,'李四');
select * from mytable1;

create table mytable2(
	a_id number,
    address varchar2
);
insert into mytable2 values(1,'北京');
insert into mytable2 values(2,'上海');
select * from mytable2;
-- 创建视图
create or replace view myView
as select * from mytable1,mytable2
where mytable1.id = mytable2.a_id;
-- 查看视图
select * from myView1;
-- 普通的表，没有主键约束，不能修改的，都是非键值保存表
update myView set name = 'abc' where id = 1;

-- 键值保存表
create table mytable3(
	id number primary key,
    name varchar2(100)
);
insert into mytable3 values(1,'张三');
insert into mytable3 values(2,'李四');
select * from mytable3;


create table mytable4()
	a_id number primary key,
    address varchar2(100),
    m_id number
);
insert into mytable4 values(1,'北京',1);
insert into mytable4 values(2,'上海',2);
insert into mytable4 values(3,'广州',1);
insert into mytable4 values(4,'深圳',2);
select * from mytable3;

-- 创建视图
create or replace view myView2
as
select * from mytable3,mytable4 where mytable3.id=mytable4.id;
-- 查看视图
select * from myView2;--查询出来的id,name 为非键值保存列。
-- 在试图中可以修改键值保存表的列
update myView2 set address = 'a' where a_id=1;
-- 在视图中无法修改非键值保存表的列
update myView2 set name = 'abc' where a_id=1;

-----------以上为键值保存表和非键值保存表的理解---------------------
/*
替换触发器使用在视图上，当视图不能修改，使用这个触发器可以完成修改。
而且是行级的触发器

语法：
  create or replace trigger 触发器名 
  instead of insert or update or delete on 视图名 for each row
  begin
  end;
*/
-- 准备工作
-- 创建视图
create view de 
as 
select d.deptno dno, d.dname,d.loc,e.* from dept d,emp e where d.deptno=e.deptno;
-- 查询视图
select * from de;
-- 完成修改视图，把empno为7369的dname修改为财务部（改不了）
update de set dname = '财务部' where empno=7369;

-- 创建替换触发器（instead of 触发器）
create or replace trigger tri5
instead of update on de for each row
begin
	dbms_output.put_line('替换触发器执行了');
end;
-- 再次执行的时候，就不会报错了，触发器执行了，但是并没有更新成功
update de set dname = '财务部' where empno=7369;
-- 需要这个修改触发器
-- 创建替换触发器（instead of 触发器）
create or replace trigger tri5
instead of update on de for each row
	v_deptno dept.deptno%type;
begin
	dbms_output.put_line('替换触发器执行了');
	-- 根据7369查询部门编号
	select deptno into v_deptno from emp where empno = :new.empno;
	dbms_output.put_line(v_deptno);
	-- 更改dept表的数据
	update dept set dname = :new.dname where deptno = v_deptno;	
end;
/*
注意：触发器的代码中不能出现 commit，rollback，savapoint
*/

模式触发器（作为了解）

在模式中执行DDL语句时执行。

-- 创建表
CREATE TABLE dropped_obj (
obj_name VARCHAR2(30),   
obj_type VARCHAR2(20),
   drop_date DATE
);
select * from dropped_obj;
-- 创建触发器
CREATE OR REPLACE TRIGGER 触发器名字
before ALTER or DROP or CREATE ON SCHEMA
BEGIN
  INSERT INTO dropped_obj   VALUES(ORA_DICT_OBJ_NAME, 
  ORA_DICT_OBJ_TYPE, SYSDATE);
END;
create table table1(
	a number
);
create table table2(
	a number
);


常用系统变量：
Ora_client_ip_address 返回客户端的ip地址
Ora_database_name 返回当前数据库名
Ora_login_user 返回登录用户名
Ora_dict_obj_name 返回ddl操作所对应的数据库对象名 
Ora_dict_obj_type 返回ddl操作所对应的数据库对象的类型  

数据库级触发器（作为了解）

在发生打开、关闭、登录和退出数据库等系统事件时执行

-- 创建一张表
create table event_table(
	event varchar2(30),
    time date
);
select * from event_table;
-- 数据库启动触发器
create or replace trigger tr_startup
after startup on database
begin 
   insert into  event_table values(ora_sysevent, sysdate);
end;
-- 现在直接查看就查不出来的，因为我们的数据库是一只打开的

-- 利用cmd命令关闭再打开
sqlplus sys/sys as sysdba;--登录
shutdown immediate;-- 关闭
startup;--开启


--用户登陆触发器
-- 创建表
create table log_table(
    username  varchar2(20), 
    logon_time  date
);

create or replace trigger tr_logon
after logon on database
begin 
   insert into log_table(username,logon_time) values(ora_login_user,  sysdate);
end;
-- 为了方便 我用cmd登录
sqlplus scott/scott
注：启用、禁用和删除触发器
	启用和禁用触发器
		ALTER TRIGGER 触发器名字 DISABLE;
		ALTER TRIGGER 触发器名字 ENABLE;
	删除触发器
		DROP TRIGGER aiu_itemfile;
 

触发器练习题：如果实现让用户在星期一不能修改数据

select * from cs1;

create or replace trigger cscs
       before insert on cs1
declare
       v varchar2(50);
begin
  select to_char(sysdate,'day') into v from dual;
  if v = '星期一' then
    raise_application_error(-20001,'星期一不能insert');
  end if;
end;

insert into cs1 values(1,'a');

程序包

概述

程序包是对相关过程、函数、变量、游标和异常等对象的封装
程序包由规范(包头)和主体(包体)两部分组成
	规范中可以声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等
	其语法格式如下:
	create [or replace] package 包名 is
	[declare_variable];	-- 规范内声明变量
	[declare_type];	-- 规范内声明类型
	[declare_cursor];	-- 规范内声明游标
	[declare_function];	-- 规范内声明函数 只能定义函数和返回类型不包括函数体
	[declare_procedure];-- 规范内声明存储过程 只能定义参数不包括存储过程主体
	end [包名];
	
	主体中可以声明程序包私有对象和实现在包规范中声明的子程序和游标
	与创建‘规范’不同的是，创建‘程序包主体’使用 create [or replace] package body 语句，这一点需要注意：
	create [or replace] package body 包名 is
	[inner_variable] -- 程序包内部的变量
	[cursor_body] -- 游标主体
	[function_title] -- 从规范中引入的函数头部的声明
	{
	begin
		fun_plsql; -- plsql块
	[exception] -- 异常部分
		[dowith_sentences;] -- 异常处理语句
	end [fun_name] -- 函数名称
	}
	[procedure_title] -- 从规范中引入的存储过程的名称
	{
	begin
		pro_plsql; 
	[exception]
		[dowith_sentences;]
	end [pro_name]
	....
	}
	end [包名];
	

程序包创建的语法

-- 包头的创建语法
CREATE [OR REPLACE] PACKAGE package_name IS|AS
[Public item declarations] 
[Subprogram specification] 
END [package_name];

package_name:包的名称
Public item declarations：公共声明部分
Subprogram specification：声明PL/SQL子程序

-- 程序包主体的常见语法
CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS
[Private item declarations] 
[Subprogram bodies] 
[BEGIN
Initialization]
END [package_name];

package_name是包的名称
Private item declarations:私有声明部分
subprogram specificatons:子程序体


-- 举例创建一个简单的
-- 创建包头
create or replace package pack1
is
	-- 声明公共部分
	v_n number :=12;
	-- 声明PL/SQL子程序（过程函数）(注意：再这里创建函数或者过程的时候不用写create or replace)
	procedure pp;
	function ff return varchar2;
end;
-- 创建一个包体(包体是对包头的实现，名字必须和包头名字一致)
create or replace package body pack1
is
	--声明私有部分
	v_n2 number:=23;
	
	--声明实现部分（子程序体）
	procedure pp
		is
		begin
			dbms_output.put_line('Hello');
		end;
	function ff return varchar2
		is
		begin
			return '你好';
		end;
end;

-- 如和查询包头和包体
select * from user_objects;

-- 如何使用包？
1、可以通过 包名.对象名
begin
	dbms_output.put_line(pack1.v_n);-- 只能访问公共的，不能使用私有的
	pack1.pp();
	dbms_output.put_line(pack1.ff());
end;

--创建包头
create or replace package pack2
is
	v number :=50;
	-- 向dept表中添加数据
	procedure pp_insert(v_dept in dept%rowtype);
	-- 求2个数的和
	function ff_add(v1 number,v2 number) return number;
end;
-- 创建包体
create or replace package body pack2
is	
	-- 实现过程的代码
	procedure pp_insert(v_dept in dept%rowtype)
	is
	
	begin
		insert into dept values(v_dept.deptno,v_dept.dname,v_dept.loc);
	end;
	-- 实现函数的代码
	function ff_add(v1 number,v2 number) return number
	is
	begin
		return v1+v2;
	end;
end;

-- 使用
declare
	v_dept dept%rowtype;
begin
	dbms_output.put_line(pack2.v);
	v_dept.deptno :=100;
	v_dept.dname :='研发部';
	v_dept.loc :='青岛';
	pack2.pp_insert(v_dept);
	dbms_output.put_line(pack2.ff_add(5,6));
end;


-- 删除包
drop package pack2;

程序包中的游标

游标的定义分为游标规范和游标主体两部分
在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型 return 数据类型;
RETURN子句指定的数据类型可以是：
用 %ROWTYPE 属性引用表定义的记录类型
程序员定义的记录类型，例如 TYPE EMPRECTYP IS RECORD(emp_id INTEGER,salary REAL)	来定义的。
不可以是number, varchar2， %TYPE等类型。
如：
-- 创建包头
-- 显式游标
create or replace package pack3
is
	cursor cur_dept return dept%rowtype;
	procedure p;
end;
-- 创建包体
create or replace package body pack3 
is
	cursor cur_dept return dept%rowtype is select * from dept;
	procedure p
		v_dept dept%rowtype;
	is
	begin
		open cur_dept;
		loop
			fetch cur_dept into v_dept;
			dbms_output.put_line(v_dept.deptno||','||v_dept.dname||','||v_dept.loc);
			exit when cur_dept%notfound;
		end loop;
		close cur_dept;
	end;
end;

-- 使用
begin
	pack3.p();
end;


-- ref 游标
-- 创建包头
create or replace package pack4
is
	-- 声明ref游标类型
	type cur_type is ref cursor;
	procedure p;
end;
-- 创建包体
create or replace package body pack4
is 
  procedure p
  is
    -- 声明ref游标变量
    cur_my cur_type;
    v_dept dept%rowtype;
  begin
    open cur_my for select * from dept;
    loop
      fetch cur_my into v_dept;
      exit when cur_my%notfound;
      dbms_output.put_line(v_dept.deptno||','||v_dept.dname||','||v_dept.loc);
    end loop;
    close cur_my;
  end;
end;


--使用
begin
	pack4 p();
end;

内置程序包

可以扩展数据库的功能
为 PL/SQL 提供对 SQL 功能的访问
用户 SYS 拥有所有程序包
是公有同义词
可以由任何用户访问

就是Oracle提供的程序包


-- DBMS_RANDOM包的使用，来产生随机的数字、字符、日期
--产生随机的整数
select DBMS_RANDOM.random from dual;

begin
	dbms_output.put_line(DBMS_RANDOM.random);
end;
-- 产生0-100（不包括100）的随机数
select abs(mod(DBMS_RANDOM.random,100))from dual;
-- value函数会返回一个大于等于0但是小于1的数
select dbms_random.value from dual;
/*
 对于指定范围内的数，要加入参数low_value和
high_value
*/
select dbms_random.value(0,10) from dual;
select floor(dbms_randow.value(0,10)) from dual;
--0到100之间的小数
SELECT  DBMS_RANDOM.VALUE(0, 100)  FROM  DUAL; 
--产生0到100之间的整数
SELECT TRUNC(DBMS_RANDOM.VALUE(0, 100)) FROM  DUAL;

/*
STRING函数生成随机文本字符串，可以指定字符串的类型和所希望的长度。
  SELECT DBMS_RANDOM.STRING('A', 20) FROM DUAL; 
     下面是一些类型的代码：
‘U’用来生成大写字符，upper
‘L’用来生成小写字符，lower
‘A’用来生成大小写混合的字符，
’P’ 表示 字符串由任意可打印字符构成， +-#$%^^
’X’表示字符串由大写字符和数字构成。 
*/
select dbms_random.string('U',10) from dual;
select dbms_random.string('L',10) from dual;
select dbms_random.string('A',10) from dual;
select dbms_random.string('P',10) from dual;
select dbms_random.string('X',10) from dual;


/*
返回某年内的随机日期，分两步：
--日期对应的内部整数，用格式'J' -4712
1,   SELECT TO_CHAR(TO_DATE('01/01/21','mm/dd/yy'),'J')
FROM DUAL; 
2,  SELECT 
TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2459216,2459216+365)		),'J')  FROM DUAL; 
*/
select to_char(sysdate,'J') from dual; -- 2459307
select to_date(2459307,'J' from) dual;

-- 随机2021年的某一天
select to_char(to_date('2021-01-01','yyyy-MM-dd'),'J') from dual;
select to_date(trunc(dbms_random.value(2459307,2459306+365)),'J') from dual;

/*
dbms_job包的用法
	可以执行调度任务，比如执行定时任务
*/
-- 任务:每搁一段时间间隔，自动调用存储过程
-- 创建测试表
create table mytable11(a date);
-- 创建一个自定义过程
create or replace procedure test11
is

begin
	insert into mytable11 values(sysdate);
end;

/* submit()参数：
1 job     OUT BINARY_INTEGER, 输出的no 编号
2 what      IN  VARCHAR2,'存储过程名;' 
3 next_date IN  DATE DEFAULT sysdate,执行任务的时间
4 interval  IN  VARCHAR2 DEFAULT 'null', 间隔时间 单位是1天
*/
declare
  v binary_integer;
begin
  	----创建job
	dbms_job.submit(v,'test11;',sysdate,'sysdate+1/1440');
	-- 第一个参数是任务编号。
	-- 第二个参数是要执行的存储过程名，注意加；号
	-- 第三个参数是下次执行的时间
	-- 第四个参数是时间间隔
  	dbms_output.put_line(v);
end;
-- 如何查看任务
select * from user_jobs;
-- 如何删除任务
begin
	dbms_job.run(v);
end;
--运行JOB
begin
	dbms_job.run(v);
end;
--停止任务
begin
	dbms_job.broken(v,true);
end;

/*
UTL_FILE包的用法
可以操作系统的文本文件

准备工作：在D盘下创建一个文件夹sql
*/
/*
创建一个Oracle的目录
create or replace directory 目录名称 as '目录的路径';
*/
create or replace directory filepath as 'd:/sql';
/*
文件类型：
utl_file.file_type
打开文件：
utl_file.fopen('目录'，'文件名','文件的模式')
目录: 值是oracle的目录，需要加单引号
文件名：值是文件名，需要加单引号
文件的模式:w(可写)，r(读)，a(追加)

写入文件内容：utl_file.put_line(文件类型变量，要写的文件内容)
读取文件内容：utl_file.get_line(文件类型变量，要读的文件内容)
*/

-- 写
declare
   --声名文件类型的变量
   f utl_file.file_type;
begin
   --1.打开文件utl_file.fopen(创建的directory,文件名,打开方式字符串)打开方式分三种 w:写，r：读，a:追加
   f:=utl_file.fopen('FILEPATH','test.txt','w');
   --2.读或写文件utl_file.put_line(文件类型变量，要写的文件内容)
   utl_file.put_line(f,'a');
   utl_file.put_line(f,'b');
   utl_file.put_line(f,'c');
   utl_file.put_line(f,'d');
   --3.关闭文件utl_file.fclose(文件类型变量)
   utl_file.fclose(f);  
end;

-- 读
declare
   --声名文件类型的变量
   f utl_file.file_type;
   --声名一个变量保存文件中的一行记录
   str varchar2(200);
begin
   --1.打开文件utl_file.fopen(创建的directory,文件名,打开方式字符串)打开方式分三种 w:写，r：读，a:追加
   f:=utl_file.fopen('FILEPATH','test.txt','r');
   --2.读或写
   _file.get_line(f,str);
   dbms_output.put_line(str);
     utl_file.get_line(f,str);
   dbms_output.put_line(str);
     utl_file.get_line(f,str);
   dbms_output.put_line(str);
     utl_file.get_line(f,str);
   dbms_output.put_line(str);
   /*
   loop
   	utl_file.get_line(f.str);
   	dbms_output.put_line(str);
   end loop;
   	exception
   		when no_data_found then
   			dbms_output.put_line('找不到了');
   */
   
   --3.关闭文件utl_file.fclose(文件类型变量)
   utl_file.fclose(f);  
end;