PL/SQL学习笔记

PL / SQL学习笔记

1.pl/sql 基本的语法格式

2.记录类型 type … is record(，，，);

3.流程控制：

• 3.1 条件判断（两种）

  • 方式一：if … then elsif … then… else … end if;
  • 方式二：case … when … then … end;

• 3.2 循环结构（三种）

  • 方式一：loop … exit when … end loop;
  • 方式二：while … loop … end loop;
  • 方式三：for i in … loop … end loop;

• 3.3 goto 、exit

4.游标的使用（类似java中的Iterator）

5.异常的处理（三种方式）

6.会写一个存储函数（有返回值）、存储过程（无返回值）

7.会写一个触发器

一、简介

PL / SQL代表“过程语言扩展到结构化查询语言”。

SQL用于查询和更新关系数据库。

PL / SQL与SQL紧密集成。

PL / SQL是SQL的Oracle过程扩展，是一种便携式，高性能的事务处理语言。

使用PL / SQL，可以使用所有SQL数据操作，游标控制和事务控制语句，以及所有SQL函数，运算符和伪列。

优点

• SQL是标准的数据库语言和PL/SQL极力使用SQL集成。PL/SQL支持静态和动态SQL。静态SQL支持DML操作和事务PL/SQL块控制。动态SQL是SQL允许嵌入PL/SQL块的DDL语句。

• PL/SQL允许一次发送语句的整块到数据库。这降低了网络流量，并提供高性能的应用程序。

• PL/SQL给编程人员高的生产效率，因为它可以查询，转换并在数据库中更新数据。

• PL/SQL强劲的功能，如异常处理，封装，数据隐藏和面向对象数据类型可以节省设计和调试的时间。

• 编写PL/SQL应用程序是完全可移植的。

• PL/SQL提供了高的安全级别。

• PL/SQL提供了访问预定义SQL包。

• PL/SQL提供了面向对象的编程支持。

• PL/ SQL提供了用于开发Web应用程序和服务器页面的支持。

二、块结构

declare
	--声明的变量、类型、游标
begin
	--程序真正的执行部分，类似于java的main（）方法
exception 
	--正对begin出现的异常，提供处理的机制
	--when....then...
	--when....then...
end;

三、基本语法

输出hello world

begin
	dbms_output.put_line('hello world');
end;

查询员工号为7369的工资

select empno,sal from emp where empno =7369 ;

查询员工号为7369的工资

declare
	v_sal number(20);
begin
	select sal into v_sal from emp where empno =7369;
  
  dbms_output.put_line(v_sal);
  
end;

查询员工号为100的工资，工作，入职时间

declare
	v_sal number(20);
	v_job varchar2(20);
	v_hire_date date;
	
begin
	select sal,job,hiredate into v_sal,v_job,v_hire_date from emp where empno =7369;
  
  dbms_output.put_line(v_sal||','||v_job||','||v_hire_date);
  
end;

自动获取数据类型%type

declare
	v_sal emp.empno%type;
	v_job emp.job%type;
	v_hire_date emp.hiredate%type;
	
begin
	select sal,job,hiredate into v_sal,v_job,v_hire_date from emp where empno =7369;
  
  dbms_output.put_line(v_sal||','||v_job||','||v_hire_date);
  
end;

四、记录类型

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

定义记录类型语法如下:
TYPE record_type IS RECORD(
 Field1 type1 [NOT NULL] [:= exp1 ],
 Field2 type2 [NOT NULL] [:= exp2 ],
 . . . . . .
 Fieldn typen [NOT NULL] [:= expn ] ) ;

记录类型(类似于java中类的概念)

declare
  --声明一个记录类型
  type emp_record is record(
	   v_sal emp.empno%type,
	   v_job emp.job%type,
	   v_hire_date emp.hiredate%type
	);
	--定义一个记录类型的成员变量
	v_emp_record emp_record;
	
begin
	select sal,job,hiredate into v_emp_record from emp where empno =7369;
  
  dbms_output.put_line(v_emp_record.v_sal||','||v_emp_record.v_job||','||v_emp_record.v_hire_date);
  
end;

declare
  type emp_record is record(
	v_ename varchar2(20),
	v_sal number(10,2)
	);
	v_emp_record emp_record;
	
begin
	v_emp_record.v_ename :='张三';
    v_emp_record.v_sal :=3000;
 
  dbms_output.put_line(v_emp_record.v_ename||','||v_emp_record.v_sal);

end;

--使用%type
declare
  type emp_record is record(
	     v_ename emp.ename%type,
		 v_sal emp.sal%type
	);
	v_emp_record emp_record;
	
begin
	v_emp_record.v_ename :='张三';
    v_emp_record.v_sal :=3000;
 
  dbms_output.put_line('name：'||v_emp_record.v_ename||',sal:'||v_emp_record.v_sal);

end;


--使用%rowtype
declare
	
	v_emp_record emp%rowtype;
	
begin
	select * into v_emp_record
	from emp
	where empno = 7369;
 
  dbms_output.put_line('name：'||v_emp_record.ename||',sal:'||v_emp_record.sal);

end;


--使用动态参数
declare
  
  v_emp_record emp%rowtype;
  v_empno number(20);
  
begin
  v_empno :=7369;

  select * into v_emp_record
  from emp
  where empno = v_empno;
  dbms_output.put_line('name：'||v_emp_record.ename||',sal:'||v_emp_record.sal);

end;


--更新语句
declare

	v_empno number(10);
begin
  v_empno :=7369;
  update emp
  set sal = sal+100
  where empno = v_empno;
 
  dbms_output.put_line('success');

end;

五、流程控制

方式一：if … then elsif … then … else … end if;

--查询出7566号员工的工资，若其工资大于或等于10000 则打印'salary>=10000";
--若在5000 到10000 之间，则打印 *5000<= salary < 10000*: 否则打印"salary < 5000"

declare
   v_sal emp.sal%type;
	
begin
	select sal into v_sal from emp where empno = 7566;
	
	if v_sal >= 10000 then dbms_output.put_line('salary>=10000');

  elsif v_sal >= 5000 then dbms_output.put_line('5000<= salary < 10000');
	
	else dbms_output.put_line('salary<5000');
  
	end if;

end;



--要求:查询出7566号员工的JOB,若其值为CLERK, 则打印'GRADE: A';
--'SALESMAN',打印'GRADE B',
--'MANAGE',打印'GRADE C',
--否则打印'GRADE D',

declare
  v_job emp.job%type;
	
begin
	select job into v_job from emp where empno = 7566;

  if v_job = 'CLERK' then dbms_output.put_line('GRADE A');

  elsif v_job = 'SALESMAN' then dbms_output.put_line('GRADE B');
	
	elsif v_job = 'MANAGER' then dbms_output.put_line('GRADE C');
	
	else dbms_output.put_line('GRADE D');
  
	end if;

end;

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

declare
   v_sal emp.sal%type;
   v_temp varchar2(30);	

begin
	select sal into v_sal from emp where empno = 7566;
	v_temp:=
	case trunc(v_sal/5000) when 0 then 'salary<5000'
	                       when 1 then '5000<= salary < 10000'
						 	           else 'salary>=10000'
							           end ;
	dbms_output.put_line(v_sal||','||v_temp);										 
end;

六、循环

方式一：loop … exit when … end loop;

--使用循环语句打印1-100（三种方式)
declare

  v_i number(10) :=1;
	
begin

  loop
		dbms_output.put_line(v_i);
		
	exit when v_i >=100;
	     v_i := v_i + 1;
  end loop;

end;

方式二：while … loop … end loop;

--使用循环语句打印1-100（三种方式)
declare

  v_i number(10) :=1;
	
begin

  while v_i <= 100 loop
			 dbms_output.put_line(v_i);
		   v_i := v_i + 1;
     end loop;
		 
end;

--输出2-100的质数

方式三：for i in … loop … end loop;

--使用循环语句打印1-100（三种方式)
begin
	for c in 1..100 loop
		dbms_output.put_line(c);
   end loop;

end;


--输出2-100的质数
declare

v_flag number(1) := 1;

begin
	
   for v_i in 2..100 loop
		 for v_j in 2..sqrt(v_i) loop
			 if mod(v_i,v_j)= 0 then v_flag := 0;
			 end if;
		end loop;
		
		if v_flag = 1 then dbms_output.put_line(v_i);
	  end if;
		
		v_flag := 1;
		
	end loop;

end;

七、游标

定义

​ 游标是一个指向上下文的句柄( handle)或指针。通过游标，PL/SQL 可以控制上下文区和处理语句时上下文区会发生些什么事情。

对于不同的 SQL 语句，游标的使用情况不同：

SQL语句	游标
非查询语句	隐式的
结果是单行的查询语句	隐式的或显示的
结果是多行的查询语句	显示的

1.显示游标

（1）显式游标的处理

显式游标处理需四个 PL/SQL 步骤:

• 定义游标：就是定义一个游标名，以及与其相对应的 SELECT 语句。

  格式：

  CURSOR cursor_name[(parameter[, parameter]…)] IS select_statement;

  游标参数只能为输入参数，其格式为：

  parameter_name [IN] datatype [{:= | DEFAULT} expression]

  在指定数据类型时，不能使用长度约束。如 NUMBER(4)、CHAR(10) 等都是错误的。

• 打开游标：就是执行游标所对应的 SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。如果游标查询语句中带有 FOR UPDATE 选项，OPEN 语句还将锁定数据库表中

  游标结果集合对应的数据行。

  格式：

  OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];

  在向游标传递参数时，可以使用与函数参数相同的传值方法，即位置表示法和名称表示 法。PL/SQL 程序不能用 OPEN 语句重复打开一个游标。

• 提取游标数据：就是检索结果集合中的数据行，放入指定的输出变量中。

  格式：

  FETCH cursor_name INTO {variable_list | record_variable };

• 对该记录进行处理；

• 继续处理，直到活动集合中没有记录

• 关闭游标：当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，

  并使该游标的工作区变成无效，不能再使用 FETCH 语句取其中数据。关闭后的游标可以使用 OPEN 语句重新打开。

  格式：CLOSE cursor_name;

  **注：**定义的游标不能有 INTO 子句。

实例：

--打印10号部门员工的工号，姓名，工资

declare

  v_sal emp.sal%type;
	v_empno emp.sal%type;
	v_ename emp.ename%type;
	
	--1.定义游标
	cursor emp_sal_cursor is
	select empno,ename,sal from emp where deptno = 10;
	
begin
	
	--2.打开游标
	open emp_sal_cursor;
	
	--3.提取游标
	fetch emp_sal_cursor into v_empno,v_ename,v_sal;
	
	while emp_sal_cursor%found loop
		dbms_output.put_line('empno:'||v_empno||',ename:'||v_ename||',sal:'||v_sal);
	  fetch emp_sal_cursor into v_empno,v_ename,v_sal;
	end loop;
	
	--4.关闭游标
	close emp_sal_cursor;
	
end;

----------------------------------------

--使用记录类型
declare
  
  --声明一个记录类型 
	type emp_record is record (
		v_empno emp.empno%type,
		v_ename emp.ename%type,
		v_sal emp.sal%type
	);
  
  --定义记录类型
	v_emp_record emp_record;
	
  --1.定义游标
  cursor emp_sal_cursor is
  select empno,ename,sal from emp where deptno = 10;
  
begin
  
  --2.打开游标
  open emp_sal_cursor;
  
  --3.提取游标
  fetch emp_sal_cursor into v_emp_record;
  
  while emp_sal_cursor%found loop
		
    dbms_output.put_line('empno:'||v_emp_record.v_empno||',ename:'||v_emp_record.v_ename||',sal:'||v_emp_record.v_sal);
    fetch emp_sal_cursor into v_emp_record;
		
  end loop;
  
  --4.关闭游标
  close emp_sal_cursor;
  
end;

2.显示游标属性

%FOUND 布尔型属性，当最近一次读记录时成功返回,则值为 TRUE；

%NOTFOUND 布尔型属性，与%FOUND 相反；

%ISOPEN 布尔型属性，当游标已打开时返回 TRUE；

%ROWCOUNT 数字型属性，返回已从游标中读取的记录数。

3.游标的FOR 循环

​ PL/SQL 语言提供了游标 FOR 循环语句，自动执行游标的 OPEN、FETCH、CLOSE语句和循环语句的功能；当进入循环时，游标 FOR 循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标 FOR 循环语句自动提取下一行数据供程序处理，当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。

--格式：
FOR index_variable IN cursor_name[value[, value]…] LOOP
-- 游标数据处理代码
END LOOP;

--范例：
--游标的FOR 循环
declare
  
  --1.定义游标
  cursor emp_sal_cursor is
  select empno,ename,sal from emp where deptno = 10;
  
begin
  for c in emp_sal_cursor loop
	dbms_output.put_line('empno:'||c.empno||',ename:'||c.ename||',sal:'||c.sal);
      
  end loop;

end;

/*
  利用游标调整公司员工的工资
	工资范围     调整基数
	0-1000       5%
	1000-2000    4%
	2000-3000    3%
	3000-4000    2%
	4000-5000    1%
*/

declare
  cursor emp_sal_cursor is
	 select empno,sal from emp;
	 
  v_temp  number(10,2);
	v_empno emp.empno%type;
	v_sal   emp.sal%type;

begin
  open 	emp_sal_cursor;
	
	fetch emp_sal_cursor into v_empno,v_sal;
	
	while emp_sal_cursor%found loop
		if v_sal<1000 then v_temp := 0.05;
		elsif v_sal<2000 then v_temp := 0.04;
        elsif v_sal<3000 then v_temp := 0.03;
		elsif v_sal<4000 then v_temp := 0.02;
		else v_temp := 0.01;
		
		end if;
		
		dbms_output.put_line('v_empno:'||v_empno||',v_sal:'||v_sal);
		
		update emp 
		set sal = sal*(1+v_temp)
		where empno = v_empno;
	
	  fetch emp_sal_cursor into v_empno,v_sal;

    end loop;
	
	close emp_sal_cursor;
	
end;


--使用游标for循环

declare
  cursor emp_sal_cursor is
   select empno,sal from emp;
   
  v_temp  number(10,2);
  v_empno emp.empno%type;
  v_sal   emp.sal%type;

begin

  for c in emp_sal_cursor loop
	if c.sal<1000 then v_temp := 0.05;
    elsif c.sal<2000 then v_temp := 0.04;
    elsif c.sal<3000 then v_temp := 0.03;
    elsif c.sal<4000 then v_temp := 0.02;
    else v_temp := 0.01;
		
	end if;
	
	update emp 
    set sal = sal*(1+v_temp)
    where empno = c.empno;
		
  end loop;
	
end;

4.隐式游标

​ 显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由 ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标**，**隐式游标的名字为 SQL，这是由 ORACLE 系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由 ORACLE 系统自动地完成，无需用户进行处理。**用户只能通过隐式游标的相 ** 关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条 SQL 语句所包含的数据。

格式调用为： SQL%

5.隐式游标属性

SQL%FOUND 布尔型属性,当最近一次读记录时成功返回，则值为 TRUE；

SQL%NOTFOUND 布尔型属性,与%FOUND 相反；

SQL%ROWCOUNT 数字型属性, 返回已从游标中读取得记录数；

SQL%ISOPEN 布尔型属性, 取值总是 FALSE。SQL 命令执行完毕立即关闭隐式游标。

--隐式游标:更新指定员工salary(涨工资10)，
--如果该员工没有找到，则打印”查无此人”信息

begin
	update emp 
	set sal = sal +100
	where empno =7369;
	
	if sql%found then dbms_output.put_line('更新成功！');
	elsif sql%notfound then dbms_output.put_line('查无此人！');
	end if;
	
end;

八、异常错误处理

定义

​ 异常情况处理(EXCEPTION)是用来处理正常执行过程中未预料的事件,程序块的异常处理预定义的错误和自定义错误,由于 PL/SQL 程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行.

有三种类型的异常错误：

• 预定义错误

  ORACLE预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。

• 非预定义错误

  即其他标准的ORACLE错误。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其发。

• 用户定义错误

  程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义,然后显式地在程序中将其引发。

异常处理部分一般放在 PL/SQL 程序体的后半部,结构为:

EXCEPTION
 WHEN first_exception THEN <code to handle first exception >
 WHEN second_exception THEN <code to handle second exception >
 WHEN OTHERS THEN <code to handle others exception >
END;

异常处理可以按任意次序排列,但 OTHERS 必须放在最后。

1.预定义的异常处理

预定义说明的部分 ORACLE 异常错误

错误号	异常错误信息名称	说明
ORA-0001	Dup_val_on_index	试图破坏一个唯一性限制
ORA-0051	Timeout-on-resource	在等待资源时发生超时
ORA-0061	Transaction-backed-out	由于发生死锁事务被撤消
ORA-1001	Invalid-CURSOR	试图使用一个无效的游标
ORA-1012	Not-logged-on	没有连接到 ORACLE
ORA-1017	Login-denied	无效的用户名/口令
ORA-1403	No_data_found	SELECT INTO 没有找到数据
ORA-1422	Too_many_rows	SELECT INTO 返回多行
ORA-1476	Zero-divide	试图被零除
ORA-1722	Invalid-NUMBER	转换一个数字失败
ORA-6500	Storage-error	内存不够引发的内部错误
ORA-6501	Program-error	内部错误
ORA-6502	Value-error	转换或截断错误
ORA-6504	Rowtype-mismatch	宿主游标变量与 PL/SQL 变量有不兼容行类型
ORA-6511	CURSOR-already-OPEN	试图打开一个已存在的游标
ORA-6530	Access-INTO-null	试图为 null 对象的属性赋值
ORA-6531	Collection-is-null	试图将 Exists 以外的集合( collection)方法应用于一个 null pl/sql 表上或 varray 上
ORA-6532	Subscript-outside-limit	对嵌套或 varray 索引得引用超出声明范围以外
ORA-6533	Subscript-beyond-count	对嵌套或 varray 索引得引用大于集合中元素的个数.

​ 对这种异常情况的处理，只需在 PL/SQL 块的异常处理部分，直接引用相应的异常情况名，并对其完成

相应的异常错误处理即可。

--实例：
declare
	v_sal number(20);
begin
	select sal into v_sal from emp where empno >1;
  
  dbms_output.put_line(v_sal);
 
exception
  when No_data_found then dbms_output.put_line('没有找到数据');
  when Too_many_rows then dbms_output.put_line('返回多行');

end;

2.非预定义的异常处理

步骤

(1).在 PL/SQL 块的定义部分定义异常情况：

​ <异常情况> EXCEPTION;

(2).将其定义好的异常情况，与标准的 ORACLE 错误联系起来，使用 PRAGMA EXCEPTION_INIT 语句：

PRAGMA EXCEPTION_INIT(<异常情况>, <错误代码>);

(3).在 PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。

--删除指定部门的记录信息，以确保该部门没有员工。
--删除指定部门的记录信息，以确保该部门没有员工。

declare

  e_deleteid_exception exception;
	pragma exception_init(e_deleteid_exception,-2292);
	
begin
	delete from emp where empno = 7890;

exception
	when e_deleteid_exception then dbms_output.put_line('违反约束性条件故不可删除此用户！');


end;

3.用户自定义的异常处理

当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。用户定义的异常错误是通过显式使

用 RAISE 语句来触发。当引发一个异常错误时，控制就转向到 EXCEPTION 块异常错误部分，执行错误处

理代码。

对于这类异常情况的处理，步骤如下：

1． 在 PL/SQL 块的定义部分定义异常情况：

​ <异常情况> EXCEPTION;

2． RAISE <异常情况>；

3． 在 PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。

--用户自定义异常
--抛出工资太高异常

declare
  e_too_high_exception exception;
	
	v_sal emp.sal%type;
begin
	select sal into v_sal from emp where empno = 7566;
	
	if v_sal>2500 then
		raise e_too_high_exception;
		
	end if;	
		
exception
	when e_too_high_exception then dbms_output.put_line('工资太高了！');

end;

九、存储函数

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

**程或函数。**过程和函数统称为 PL/SQL 子程序，他们是被命名的 PL/SQL 块，均存储在数据库中，并

通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调

用者返回数据，而过程则不返回数据。

语法如下：

CREATE [OR REPLACE] FUNCTION function_name
	[ (argment [ { IN | IN OUT }] Type,
 	   argment [ { IN | OUT | IN OUT } ] Type ]
	[ AUTHID DEFINER | CURRENT_USER ]
	RETURN return_type 
{ IS | AS }
       <类型.变量的说明>
BEGIN
	FUNCTION_body
EXCEPTION
	其它语句
END;

说明:

• OR REPLACE 为可选. 有了它, 可以或者创建一个新函数或者替换相同名字的函数, 而不会出现冲突

• 函数名后面是一个可选的参数列表, 其中包含 IN, OUT 或 IN OUT 标记. 参数之间用逗号隔开. IN 参数

标记表示传递给函数的值在该函数执行中不改变; OUT 标记表示一个值在函数中进行计算并通过该参

数传递给调用语句; IN OUT 标记表示传递给函数的值可以变化并传递给调用语句. 若省略标记, 则参数

隐含为 IN。

• 因为函数需要返回一个值, 所以 RETURN 包含返回结果的数据类型.

--存储函数

create or replace function query_dept_sal (dept_id number,sal number)
return number
is
   --函数使用过程中，需要声明的变量、记录类型、cursor
begin
   --函数的执行体
exception
	 --处理函数执行过程中的异常
end;

无参数实例：

--定义函数 返回hello world

create or replace function helloworld
return varchar2
is
begin
   return 'hello world!';

end;

--执行定义的helloworld函数
begin
  dbms_output.put_line(helloworld); 
end;

--查询函数返回的内容
select helloworld from dual;



------------------------------------------------------------------
--创建一个存储函数，返回当前的系统时间

create or replace function systime 
return date
is
  v_date date;
begin
	v_date:=sysdate;
  return v_date;

end;

-------

begin
	dbms_output.put_line(systime); 
end;

-------

select systime from dual;

带参数实例：

--定义函数 带参数
create or replace function helloworld1(v_logo varchar2)
return varchar2
is
begin
  return 'hello world!'||v_logo;

end;
-----
begin
  dbms_output.put_line(helloworld1('法外狂徒张三')); 
end;
-----

select helloworld1('法外狂徒张三') from dual;

--------------------------------------------------------
--定义带参数的函数，两个数相加

create or replace function getSum(a number,b number)
return number
is
   v_sum number(10);
begin
	v_sum:=a+b;
	
	return v_sum;

end;
-------------
begin
	dbms_output.put_line(getSum(2,3)); 
end;
--------------
select getSum(2,3) from dual;

----------------------------------------------------
--定义一个函数，获取给定部门的工资总和

create or replace function get_sal(dept_no number)
return number
is
   v_sal number(10):=0;
   
	 cursor sal_cursor is 
	 select sal from emp where deptno  = dept_no;
	 
begin
	for c in sal_cursor loop
		v_sal :=v_sal+c.sal;
  end loop;
	
	return  v_sal;
end;

-------------
begin
	dbms_output.put_line(get_sal(10)); 
end;
--------------
select get_sal(10) from dual;

关于OUT型的参数:因为函数只能有一一个返回值，PL/SQL 程序可以通过OUT型的参数实现有多个返回值

/*
要求:定义-一个函数:获取给定部门的工资总和和该部门的员工总数(定义为OUT类型的参数). 
要求:部门号定义为参数，工资总额定义为返回值.*/
create or replace function get_sal1(dept_no number,total_count out number)
return number
is
   v_sal number(10):=0;
   
	 cursor sal_cursor is 
	 select sal from emp where deptno  = dept_no;
	 
begin
	  total_count:=0;
		for c in sal_cursor loop
			v_sal :=v_sal+c.sal;
			total_count :=total_count+1;
		end loop;
	
	return  v_sal;
end;

-------------

declare

  v_temp number(10):=0;

begin
		dbms_output.put_line(get_sal1(10,v_temp)); 
		dbms_output.put_line(v_temp);
end;

十、存储过程

定义

在 ORACLE SERVER 上建立存储过程,可以被多个应用程序调用,可以向存储过程传递参数,也可以向存储

过程传回参数.

创建过程语法:

CREATE [OR REPLACE] PROCEDURE Procedure_name
	[ (argment [ { IN | IN OUT }] Type,
	   argment [ { IN | OUT | IN OUT } ] Type ]
	[ AUTHID DEFINER | CURRENT_USER ]
	{ IS | AS }
       <类型.变量的说明>
BEGIN
	<执行部分>
EXCEPTION
	<可选的异常错误处理程序>
END;

--存储过程
--定义一个存储过程:
--获取给定部门的工资总和(通过out 参数)，要求:部门号和工资总额定义为参数
create or replace procedure get_sal2(dept_no number,sumsal out number)
is
	 cursor sal_cursor is 
	 select sal from emp where deptno  = dept_no;
	 
begin
	
  sumsal := 0;
	
	for c in sal_cursor loop
		sumsal := sumsal + c.sal;
  end loop;
	
	dbms_output.put_line(sumsal);
	
end;

-------------
declare

  v_temp number(10):=0;

begin
		get_sal2(10,v_temp);
end;
----------------------------------------------------------------------
/*
对给定部门(作为输入参数)的员工进行加薪操作，
若其到公司的时间在(?，95)期间，为其加薪%5
[95，98)  %3
[98，?)   %1
得到以下返回结果:为此次加薪公司每月需要额外付出多少成本(定义一个OUT 型的输出参数).
*/
create or replace procedure add_sal(dept_no number,temp_sal out number)
is
   cursor emp_cursor is select empno,sal,hiredate from emp where deptno = dept_no;
	 v_i number(4,2):=0;		  

begin
	
	temp_sal:=0;
	for c in emp_cursor loop
		if to_char(c.hiredate,'yyyy')<'1995' then v_i:= 0.05;
		elsif to_char(c.hiredate,'yyyy')<'1998' then v_i:= 0.03;
		else v_i:= 0.01;
		
		end if;
		
		--1.更新工资
		update emp set sal = sal*(1+v_i) where empno = c.empno;
		--2.付出的成本
		temp_sal := temp_sal+ c.sal*v_i;
	end loop;	
		dbms_output.put_line(temp_sal);
end;
------------------
declare

  v_temp number(10):=0;

begin
		add_sal(10,v_temp);
end;

十一、包

定义

​ 包是一组相关过程、函数、变量、常量和游标等 PL/SQL 程序设计元素的组合，它具有面向对象程序设

计语言的特点，是对这些 PL/SQL 程序设计元素的封装。**包类似于C++ 和 JAVA 语言中的类，其中变量相当于类中的成员变量，过程和函数相当于类方法。**把相关的模块归类成为包，可使开发人员利用面向对象的方法进行存储过程的开发，从而提高系统性能。

组成

一个包由两个分开的部分组成：

包定义（PACKAGE）：包定义部分声明包内数据类型、变量、常量、游标、子程序和异常错误处理等元

素，这些元素为包的公有元素。

包主体（PACKAGE BODY）：包主体则是包定义部分的具体实现，它定义了包定义部分所声明的游标和子

程序，在包主体中还可以声明包的私有元素。

包定义的语法

--创建包定义:
CREATE [OR REPLACE] PACKAGE package_name
    [AUTHID {CURRENT_USER | DEFINER}]
    {IS | AS}
    [公有数据类型定义[公有数据类型定义]…]
    [公有游标声明[公有游标声明]…]
    [公有变量、常量声明[公有变量、常量声明]…]
    [公有子程序声明[公有子程序声明]…]
END [package_name];

其中：AUTHID CURRENT_USER和AUTHID DEFINER选项说明应用程序在调用函数时所使用的权限模式，它们与
CREATE FUNCTION语句中invoker_right_clause子句的作用相同。

--创建包主体:
CREATE [OR REPLACE] PACKAGE BODY package_name
    {IS | AS}
    [私有数据类型定义[私有数据类型定义]…]
    [私有变量、常量声明[私有变量、常量声明]…]
    [私有子程序声明和定义[私有子程序声明和定义]…]
    [公有游标定义[公有游标定义]…]
    [公有子程序定义[公有子程序定义]…]
BEGIN
	PL/SQL 语句
END [package_name];

其中：在包主体定义公有程序时，它们必须与包定义中所声明子程序的格式完全一致。

---

包的开发步骤

1．将每个存储过程调式正确；

2． 用文本编辑软件将各个存储过程和函数集成在一起；

3． 按照包的定义要求将集成的文本的前面加上包定义；

4． 按照包的定义要求将集成的文本的前面加上包主体；

5． 使用 SQLPLUS 或开发工具进行调式

-- 创建包规范定义
create or replace package math_package
as
  --创建两个整数加法存储过程
  procedure add(a in int,b in int,c out int);
  --创建两个是整数的减法函数
  function sub(a in int,b in int ,c out int) return int;
  --创建乘法存储过程
  procedure mul(a in int,b in out int);
  
  --创建除法函数
  function div(a in int ,b in out number) return number;
end math_package;

--------------------------------------------------------------
--给包math_package创建包体
create or replace package body math_package
as
  --创建两个整数加法存储过程
  procedure add(a in int,b in int,c out int)
  is
  begin
    c:=a+b;
    dbms_output.put_line(a || ' + ' || b || ' = ' || c); 
  end add;
  --创建两个是整数的减法函数
  function sub(a in int,b in int ,c out int) return int 
  is
  begin
    if a>b then
       c:=a-b;
    else
       c:=b-a;
    end if;
    return c;
    exception 
       when VALUE_ERROR then dbms_output.put_line('The value is error!');     
  end sub;
  --创建乘法存储过程
  procedure mul(a in int,b in out int)
  is
  tmp int;
  begin
   tmp:=b;
   b:=a*b;
   dbms_output.put_line(a || ' * ' || tmp || ' = ' || b); 
  end mul;
  
  --创建除法函数
  function div(a in int ,b in out number) return number
  as
  begin
     if b!=0 then
        b:=a/b;
     else
        raise ZERO_DIVIDE;
     end if;
     return b;
  exception 
       when ZERO_DIVIDE then dbms_output.put_line('ZERO_DIVIDE');    
  end div;
end math_package;

--------------------------------------------------------------------------
declare 
  --定义几个变量
  a int;
  b int;
  c int;
  d number;
  tmp number;
begin
  --给a,b赋值
  a:=10;
  b:=20;
  d:=0;
  --调用math_package的add存储过程
  math_package.add(a,b,c);
  --调用math_package的mul存储过程
  math_package.mul(a,b);
  --重新给变量赋值
  a:=10;
  b:=20;
  --调用math_package的sub函数
  c:=math_package.sub(a,b,c);
  if a>b then
     dbms_output.put_line(a || ' - ' || b || ' = ' || c); 
  else
     dbms_output.put_line(b || ' - ' || a || ' = ' || c); 
  end if;
  
  --调用math_package的div函数
  d:=2;
  tmp:=d;
  d:=math_package.div(a,d);
  dbms_output.put_line(a || ' / ' || tmp || ' = ' || d); 
end;

结果：
10 + 20 = 30
10 * 20 = 200
20 - 10 = 10
10 / 2 = 5

十二、触发器

定义

​ 触发器是许多关系数据库系统都提供的一项技术。在 ORACLE 系统里，触发器类似过程和函数，都有

声明，执行和异常处理过程的 PL/SQL 块。

触发器组成:

• 触发事件：即在何种情况下触发 TRIGGER; 例如：INSERT, UPDATE, DELETE。

• 触发时间：即该 TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和

  该 TRIGGER 的操作顺序。

• 触发器本身：即该 TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。

  例如：PL/SQL 块。

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

  语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；

  行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一

  次。

---

1.创建触发器

创建触发器的一般语法是:

CREATE [OR REPLACE] TRIGGER trigger_name
	{BEFORE | AFTER }
	{INSERT | DELETE | UPDATE [OF column [, column …]]}
	ON [schema.] table_name 
	[FOR EACH ROW ]
    [WHEN condition]
	trigger_body;
	
其中：
	BEFORE 和 AFTER 指出触发器的触发时序分别为前触发和后触发方式，前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。
	FOR EACH ROW 选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个 DML 语句操做影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略 FOR EACH ROW 选项时，BEFORE 和 AFTER 触发器为语句触发器，而 INSTEAD OF 触发器则为行触发器。
	WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用 PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在 BEFORE 和 AFTER 行触发器中，不能用INSTEAD OF 行触发器和其它类型的触发器中。
	当一个基表被修改( INSERT, UPDATE, DELETE)时要执行的存储过程，执行时根据其所依附的基表改动而自动触发，因此与应用程序无关，用数据库触发器可以保证数据的一致性和完整性。

每张表最多可建立 12 种类型的触发器，它们是:
BEFORE INSERT
BEFORE INSERT FOR EACH ROW
AFTER INSERT
AFTER INSERT FOR EACH ROW
BEFORE UPDATE
BEFORE UPDATE FOR EACH ROW
AFTER UPDATE
AFTER UPDATE FOR EACH ROW
BEFORE DELETE
BEFORE DELETE FOR EACH ROW
AFTER DELETE
AFTER DELETE FOR EACH ROW

--hello world 触发器
create or replace trigger update_emp_trigger
	 after
	 update on emp
	 for each row
begin
	dbms_output.put_line('hello world');
end;
-------------
update emp set sal = 10000 where empno = 7369;

2.触发器触发次序

• 执行 BEFORE 语句级触发器;
• 对与受语句影响的每一行：
  • 执行 BEFORE 行级触发器
  • 执行 DML 语句
  • 执行 AFTER 行级触发器
• 执行 AFTER 语句级触发器

---

3.触发器的限制

• CREATE TRIGGER 语句文本的字符长度不能超过 32KB；
• 触发器体内的 SELECT 语句只能为 SELECT … INTO …结构，或者为定义游标所使用的 SELECT 语句。
• 触发器中不能使用数据库事务控制语句 COMMIT; ROLLBACK, SVAEPOINT 语句；
• 由触发器所调用的过程或函数也不能使用数据库事务控制语句；

问题：当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、 后列的值.

实现: :NEW 修饰符访问操作完成后列的值

​ :OLD 修饰符访问操作完成前列的值

特性	INSERT	UPDATE	DELETE
OLD	NULL	有效	有效
NEW	有效	有效	NULL

:old和:new

--:old和:new触发器
create or replace trigger update_emp_trigger1
	 after
	 update on emp
	 for each row --行级触发
begin
	dbms_output.put_line(':old'||:old.sal||':new'||:new.sal);

end;


update emp set sal = 6666 where empno = 7369;

--编写一个触发器，在对应的my_emp表中进行删除的时候，
--在my_emp_bak表中增加删除的记录

--创建my_emp表
create table my_emp
as
select empno,sal from emp;

--创建my_emp_bak表
create table my_emp_bak
as
select empno,sal from emp
where 1=2;

--创建delete_emp_trigger触发器
create or replace trigger delete_emp_trigger1
	 before
	 delete on my_emp
	 for each row --行级触发
begin
   insert into my_emp_bak
	 values(:old.empno,:old.sal);

end;

delete from my_emp;

select * from my_emp;

select * from my_emp_bak;