PostgreSQL 存储过程 utl_smtp plsql存储过程包

转载

mob64ca13ff28f1 2024-02-26 15:29:02

PL/SQL学习整理

PL/SQL是 Procedure Language & Structured Query Language（过程语言与结构化查询语言）的缩写。PL/SQL是对SQL语言存储过程语言的扩展。

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

一、结构和组成

1、包

一个包由两个分开的部分组成：
包定义（PACKAGE）：包定义部分声明包内数据类型、变量、常量、游标、子程序和异常错误处理等元素，这些元素为包的公有元素。
包主体（PACKAGE BODY）：包主体则是包定义部分的具体实现，它定义了包定义部分所声明的游标和子程序，在包主体中还可以声明包的私有元素。
包定义和包主体分开编译，并作为两部分分开的对象存放在数据库字典中。

包定义的语法如下：

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

包体定义的语法如下：

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

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

2、PL/SQL

2.1、 PL/SQL程序由三个块组成

即声明部分、执行部分、异常处理部分。如下：

DECLARE      
 	/* 声明部分: 在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数 */ 
    BEGIN   
    /*  执行部分:  过程及SQL 语句  , 即程序的主要部分 */ 
    EXCEPTION   
     /* 执行异常部分: 错误处理  */
    END;

其中 执行部分是必须的。

2.2、 PL/SQL块可以分成三类：

匿名块：动态构造，只能执行一次
子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们
触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序

二、变量

1、在语句块的声明部分对变量声明，声明一个变量的语法是：

variable_name [ constant ]  type [ not null ]  [:=value]

其中：
    variable_name  为变量名
    type           为类型
    value          为变量的初值

DECLARE 	
 	V_Description         VARCHAR2(50); 	
 	V_Number              NUMBER := 45; 	
 	V_Counter             BINARY_INTEGER := 0;

注意：
如果变量在声明时使用了NOT NULL选项则必须为变量指定初值。
如果变量在声明时使用了CONSTANT选项则必须为变量指定初值，并且该初值不能被改变。

记录类型：记录类型是把逻辑相关的数据作为一个单元存储起来定义记录类型语法如下：

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

%TYPE:定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE
例：

DECLARE    
    -- 用 %TYPE 类型定义与表相配的字段    
    TYPE t_Record IS RECORD(
	              T_no emp.empno%TYPE,
	              T_name emp.ename%TYPE,
	              T_sal emp.sal%TYPE );   
               -- 声明接收数据的变量   
                v_emp t_Record;

PL/SQL 表, 或者称为索引表(index-table)，是可以在PL/SQL 程序中引用、能够模仿数组的非永久表。用户可以定义一个表类型，然后声明这种类型的变量。接下来，用户就可以将记录添加到用户的PL/SQL 表中，并且采用与引用数组元素大体相同的方法引用他们。
表包括两个基本成分：
数据处理类型为BINARY_INTEGER主键
标量或记录数据类型的列。
定义一个表：

TYPE type_name IS TABLE OF
	{column_type | variable%TYPE | table.column%TYPE } [NOT NULL] | table%ROWTYPE
	[ INDEX BY BINARY_INTEGER];

例：

DECLARE
	TYPE dept_table_type IS TABLE OF 
		dept%ROWTYPE INDEX BY BINARY_INTEGER;
	my_dname_table dept_table_type;

三、游标

为了处理 SQL 语句，ORACLE 必须分配一片叫上下文( context area )的区域来处理所必需的信息，其中包括要处理的行的数目，一个指向语句被分析以后的表示形式的指针以及查询的活动集(active set)。游标是一个指向上下文的句柄( handle)或指针。通过游标，PL/SQL可以控制上下文区和处理语句时上下文区会发生些什么事情。

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

1、显示游标

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

定义游标：就是执行游标所对应的SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。
OPEN cursor_name
其中：PL/SQL 程序不能用OPEN 语句重复打开一个游标
打开游标：就是执行游标所对应的SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。
OPEN cursor_name
其中：PL/SQL 程序不能用OPEN 语句重复打开一个游标
提取游标：就是检索结果集合中的数据行，放入指定的输出变量中。
FETCH cursor_name INTO {variable_list | record_variable };
关闭游标：当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用FETCH 语句取其中数据。关闭后的游标可以使用OPEN 语句重新打开。
CLOSE cursor_name;

参数化游标
例：

DECLARE
	v_ename  emp.ename%TYPE;
   	v_sal      emp.sal%TYPE;
   	CURSOR c_cursor(P_sal emp.sal%type)
          IS SELECT ename, sal FROM emp WHERE sal >= P_sal;
BEGIN
OPEN c_cursor(1000);
FETCH c_cursor INTO v_ename, v_sal;
WHILE c_cursor %FOUND LOOP
   DBMS_OUTPUT.PUT_LINE(v_ename||’---‘||to_char(v_sal) );
   FETCH c_cursor INTO v_ename, v_sal;
END LOOP;
CLOSE c_cursor;
END;

2、隐式游标

例：DECLARE
			V_deptno emp.deptno%TYPE :=&p_deptno;
BEGIN
	DELETE FROM emp WHERE deptno=v_deptno;
	IF SQL%NOTFOUND THEN
			DELETE FROM dept WHERE deptno= 20;
	END IF;
END;

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

index_variable为游标FOR 循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句返回的结构集合的结构相同。在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据，index_variable中各元素的名称与游标查询语句选择列表中所制定的列名相同。如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后才能通过游标FOR 循环语句中的索引变量来访问这些列数据。
例：

DECLARE
   	CURSOR c_sal IS SELECT empno, ename, sal FROM emp ;
BEGIN
--隐含打开游标
   FOR v_sal IN c_sal LOOP
   --隐含执行一个FETCH语句
   DBMS_OUTPUT.PUT_LINE( to_char(v_sal.empno)||’---‘||v_sal.ename||’---‘||to_char(v_sal.sal)) ;
   --隐含监测c_sal%NOTFOUND
   END LOOP;
--隐含关闭游标
END;

SELECT … INTO 语句触发 NO_DATA_FOUND；当一个显式游标的WHERE子句未找到时触发%NOTFOUND；当UPDATE或DELETE 语句的WHERE 子句未找到时触发 SQL%NOTFOUND；在提取循环中要用 %NOTFOUND 或%FOUND 来确定循环的退出条件，不要用 NO_DATA_FOUND.

SELECT FOR UPDATE 游标
例：

DECLARE 
	V_deptno emp.deptno%TYPE :=&p_deptno;
	CURSOR emp_cursor IS SELECT empno, sal 
		FROM emp WHERE deptno=v_deptno FOR UPDATE NOWAIT;
BEGIN
	FOR emp_record IN emp_cursor LOOP
		IF emp_record.sal < 1500 THEN
			UPDATE emp SET sal=1500 WHERE CURRENT OF 				emp_cursor;
		END IF;
	END LOOP;
	--	COMMIT;
END;

3、游标变量

与游标一样，游标变量也是一个指向多行查询结果集合中当前数据行的指针。但与游标不同的是，游标变量是动态的，而游标是静态的。游标只能与指定的查询相连，即固定指向一个查询的内存处理区域，而游标变量则可与不同的查询语句相连，它可以指向不同查询语句的内存处理区域（但不能同时指向多个内存处理区域，在某一时刻只能与一个查询语句相连），只要这些查询语句的返回类型兼容即可。

3.1、声明游标变量

游标变量为一个指针，它属于参照类型，所以在声明游标变量类型之前必须先定义游标变量类型。在PL/SQL中，可以在块、子程序和包的声明区域内定义游标变量参照类型。
TYPE ref_type_name IS REF CURSOR [ RETURN return_type]
其中：ref_type_name为新定义的游标变量类型名称；
retuen_type 为游标变量的返回值类型，它必须为记录变量。
例：声明两个强类型定义游标变量和一个弱类型游标变量

DECLARE
	TYPE deptrecord IS RECORD(
		Deptno dept.deptno%TYPE,
		Dname dept.deptno%TYPE,
		Loc dept.loc%TYPE );
	TYPE deptcurtype IS REF CURSOR RETURN dept%ROWTYPE;
	TYPE deptcurtyp1 IS REF CURSOR RETURN deptrecord;
	TYPE curtype IS REF CURSOR;
	Dept_c1 deptcurtype;
	Dept_c2 deptcurtyp1;
	Cv curtype;

3.2、游标变量应用举例

例：强类型参照游标变量类型

DECLARE
	TYPE emp_job_rec IS RECORD(
		Employee_id emp.empno%TYPE,
		Employee_name emp.ename%TYPE,
		Job_title emp.job%TYPE);
	TYPE emp_job_refcur_type IS REF CURSOR RETURN emp_job_rec;
	Emp_refcur emp_job_refcur_type ;
	Emp_job emp_job_rec;  
BEGIN
	OPEN emp_refcur FOR 
		SELECT empno, ename, job FROM emp ORDER BY deptno;
	FETCH emp_refcur INTO emp_job;
	WHILE emp_refcur%FOUND LOOP
		DBMS_OUTPUT.PUT_LINE(emp_job.employee_id||’: ‘||emp_job.employee_name||‘ is a ’||emp_job.job_title);
		FETCH emp_refcur INTO emp_job;
	END LOOP;
END;

例：弱类型参照游标变量类型

DECLARE
	Type refcur_t IS REF CURSOR;
	Refcur refcur_t;
	TYPE sample_rec_type IS RECORD (
		Id number,
		Description VARCHAR2 (30) );
	sample sample_rec_type;
	selection varchar2(1) := UPPER (SUBSTR (‘&tab’, 1, 1)); 
BEGIN
	IF selection=’D’ THEN
	    OPEN refcur FOR SELECT deptno, dname FROM dept;
	    DBMS_OUTPUT.PUT_LINE(‘Department data’);
	ELSIF selection=’E’ THEN
	    OPEN refcur FOR SELECT empno, ename||’ is a ‘||job FROM emp;
	    DBMS_OUTPUT.PUT_LINE(‘Employee data’);
	ELSE
	    DBMS_OUTPUT.PUT_LINE(‘Please enter ‘‘D’’ or ‘‘E’’ ’);
	    RETURN;
	END IF;
	DBMS_OUTPUT.PUT_LINE(‘----------------------‘);
	FETCH refcur INTO sample;
	WHILE refcur%FOUND LOOP
	    DBMS_OUTPUT.PUT_LINE(sample.id||’: ‘||sample.description);
	    FETCH refcur INTO sample;
	END LOOP;
	CLOSE refcur;
END;

3、游标属性

PostgreSQL 存储过程 utl_smtp plsql存储过程包_数据库

四、触发器

触发器在数据库里以独立的对象存储，它与存储过程不同的是，存储过程通过其它程序来启动运行或直接启动运行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。
触发器的限制：

CREATE TRIGGER语句文本的字符长度不能超过32KB
触发器体内的SELECT 语句只能为SELECT … INTO …结构，或者为定义游标所使用的SELECT 语句
触发器中不能使用数据库事务控制语句 COMMIT， ROLLBACK, SVAEPOINT 语句
由触发器所调用的过程或函数也不能使用数据库事务控制语句
触发器中不能使用LONG, LONG RAW 类型
触发器内可以参照LOB类型列的列值，但不能通过 :NEW 修改LOB列中的数据

1、触发器类型

主要的触发器种类有三种：

DML触发器：ORACLE可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发
替代触发器：由于在ORACLE里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。它就是ORACLE专门为进行视图操作的一种处理方法。
系统触发器：它可以在ORACLE数据库系统的事件中进行触发，如ORACLE系统的启动与关闭等。

2、触发器的组成

触发事件：即在何种情况下触发TRIGGER; 例如：INSERT, UPDATE, DELETE。
触发时间：即该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER
的操作顺序。
触发器本身：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。例如：PL/SQL 块。
触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。
语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；
行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

3、创建触发器

触发器名与过程名和包的名字不一样，它是单独的名字空间，因而触发器名可以和表或过程有相同的名字，但在一个模式中触发器名不能相同。
创建触发器的一般语法是：

CREATE [OR REPLACE] TRIGGER trigger_name
			{BEFORE | AFTER }
			{INSERT | DELETE | UPDATE [OF column [, column …]]}
			ON [schema.] table_name 
	[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
	[FOR EACH ROW ]
	[WHEN condition]
	trigger_body;

BEFORE和AFTER指出触发器的触发时序分别为前触发和后触发方式，前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。
FOR EACH ROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则为行触发器。
REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号，但在WHEN子句中则不能加冒号。
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

3.1、创建DML触发器

例：

CREATE OR REPLACE TRIGGER del_emp_after 
   			BEFORE DELETE ON scott.emp FOR EACH ROW
		BEGIN
   				--   将修改前数据插入到日志记录表 del_emp ,以供监督使用。
   				INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal, comm , hiredate )
       	VALUES( :old.deptno, :old.empno, :old.ename , :old.job,
               :old.mgr, :old.sal, :old.comm, :old.hiredate );
		END;

当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、后列的值，可以使用：
:NEW 修饰符访问操作完成后列的值
:OLD 修饰符访问操作完成前列的值

PostgreSQL 存储过程 utl_smtp plsql存储过程包_数据_02

3.2、创建替代触发器

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

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column [, column …]]}
ON [schema.] view_name
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
trigger_body;

BEFORE 和AFTER指出触发器的触发时序分别为前触发和后触发方式，前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。
INSTEAD OF 选项使ORACLE激活触发器，而不执行触发事件。只能对视图和对象视图建立INSTEADOF触发器，而不能对表、模式和数据库建立INSTEAD OF 触发器。
FOR EACH ROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则为行触发器。
REFERENCING子句说明相关名称，在行触发器的PL/SQL块和WHEN子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号，但在WHEN子句中则不能加冒号。
WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL函数。WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中，不能用在INSTEAD OF行触发器和其它类型的触发器中。
INSTEAD_OF用于对视图的DML触发，由于视图有可能是由多个表进行联结(join)而成，因而并非是所有的联结都是可更新的。但可以按照所需的方式执行更新。

例：

--创建视图
!CREATE OR REPLACE VIEW emp_view AS 
			SELECT deptno, count(*) total_employeer, sum(sal) total_salary 
			FROM emp GROUP BY deptno; 
--创建触发器
CREATE OR REPLACE TRIGGER emp_view_delete
   INSTEAD OF DELETE ON emp_view FOR EACH ROW
	BEGIN
   			DELETE FROM emp WHERE deptno= :old.deptno;
	END emp_view_delete;
--删除视图的一条数据，触发
DELETE FROM emp_view WHERE deptno=10;

3.3、创建系统触发器

系统触发器可以在DDL或数据库系统上被触发。DDL指的是数据定义语言，如CREATE 、ALTER及DROP 等。而数据库系统事件包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。
创建系统触发器的语法如下：

CREATE OR REPLACE TRIGGER [sachema.] trigger_name
   	{BEFORE|AFTER}  
	{ddl_event_list | database_event_list}
   	ON { DATABASE | [schema.] SCHEMA }
   	[WHEN_clause] 
   	trigger_body;

ddl_event_list为一个或多个DDL事件，事件间用 OR 分开；
database_event_list为一个或多个数据库事件，事件间用 OR 分开；
系统事件触发器既可以建立在一个模式上，又可以建立在整个数据库上。当建立在模式(SCHEMA)之上时，只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器,默认时为当前用户模式。当建立在数据库(DATABASE)之上时，该数据库所有用户的DDL操作和他们所导致的错误，以及数据库的启动和关闭均可激活触发器。要在数据库之上建立触发器时，要求用户具有ADMINISTER DATABASE TRIGGER权限。

例：

CREATE OR REPLACE TRIGGER trig4_ddl
			AFTER CREATE OR ALTER OR DROP  ON DATABASE
		DECLARE
				Event VARCHAR2(20); Typ VARCHAR2(20);
				Name VARCHAR2(30);  Owner VARCHAR2(30);
		BEGIN
				-- 读取DDL事件属性
				Event := ORA_SYSEVENT;
				Typ := ORA_DICT_OBJ_TYPE;
				Name :=ORA_DICT_OBJ_NAME;
				Owner := ORA_DICT_OBJ_OWNER;
				-- 将事件属性插入到事件日志表中
				INSERT INTO scott.eventlog(eventname,obj_type,obj_name,obj_owner)
				VALUES(event, typ, name, owner);
		END;

4、删除触发器

删除触发器使用下面的语句：
DROP TRIGGER trigger_name;
当删除其他用户模式中的触发器名称，需要具有DROP ANY TRIGGER系统权限，当删除建立在数据库上的触发器时，用户需要具有ADMINISTER DATABASE TRIGGER系统权限。
此外，当删除表或视图时，建立在这些对象上的触发器也随之删除。

5、触发器状态

有效状态(ENABLE)：当触发事件发生时，处于有效状态的数据库触发器TRIGGER 将被触发。
无效状态(DISABLE)：当触发事件发生时，处于无效状态的数据库触发器TRIGGER将不会被触发，此时就跟没有这个数据库触发器(TRIGGER) 一样。

数据库TRIGGER的这两种状态可以互相转换。格式为：

ALTER TIGGER trigger_name [DISABLE | ENABLE ];

例：

ALTER TRIGGER emp_view_delete DISABLE;

ALTER TRIGGER语句一次只能改变一个触发器的状态，而ALTER TABLE语句则一次能够改变与指定表相关的所有触发器的使用状态。
格式为：

ALTER TABLE [schema.]table_name {ENABLE|DISABLE} ALL TRIGGERS;

例：

--使表EMP 上的所有TRIGGER 失效
ALTER TABLE emp DISABLE ALL TRIGGERS;

相关数据字典：
USER_TRIGGERS、ALL_TRIGGERS、DBA_TRIGGERS

五、流程控制

1、条件语句IF

IF语句的基本形式为：

IF <布尔表达式> THEN
		PL/SQL 和 SQL语句
END IF;
或
IF <布尔表达式> THEN
		PL/SQL 和 SQL语句
ELSE
		其它语句
END IF;

IF语句的复杂形式为：

IF <布尔表达式> THEN
		PL/SQL 和 SQL语句
ELSIF < 其它布尔表达式> THEN
		其它语句
ELSIF < 其它布尔表达式> THEN
		其它语句
…… ……
ELSE
		其它语句
END IF;

例：

DECLARE
    v_empno emp.empno%TYPE :=&empno;
    V_salary emp.sal%TYPE;
    V_comment VARCHAR2(35);
 BEGIN
   SELECT sal INTO v_salary FROM emp WHERE empno=v_empno;
   IF v_salary<1500 THEN
       V_comment:= ‘Fairly less’;
   ELSIF v_salary <3000 THEN
      V_comment:= ‘A little more’;
   ELSE
      V_comment:= ‘Lots of salary’;
   END IF;
   DBMS_OUTPUT.PUT_LINE(V_comment);
 END;

CASE语句的基本形式为：

CASE selector
	WHEN expression1 THEN result1
	WHEN expression2 THEN result2
	WHEN expressionN THEN resultN
	[ ELSE resultN+1]
END;

其中：ELSE子句是可选的，但是当检测表达式与任何一个检测值都不匹配时，PL/SQL会产生预定义错误CASE_NOT_FOUND，其错误号是ORA-6592。
例：

DECLARE
	V_grade char(1) := UPPER(‘&p_grade’);
	V_appraisal VARCHAR2(20);
BEGIN
	V_appraisal :=
	CASE v_grade
		WHEN ‘A’ THEN ‘Excellent’
		WHEN ‘B’ THEN ‘Very Good’
		WHEN ‘C’ THEN ‘Good’
		ELSE ‘No such grade’
	END;
	DBMS_OUTPUT.PUT_LINE(‘Grade:‘||v_grade||’ Appraisal: ‘|| v_appraisal);
END;

2、循环语句

1.1、LOOP

LOOP
  要执行的语句;
  EXIT WHEN <条件语句> /*条件满足，退出循环语句*/
END LOOP;

其中：EXIT WHEN 子句是必须的，否则循环将无法停止。
例：

DECLARE
    int NUMBER(2) :=0;
BEGIN
   LOOP
      int := int + 1;
      DBMS_OUTPUT.PUT_LINE('int 的当前值为:'||int);
      EXIT WHEN int =10;
   END LOOP;
END;

2.2、WHILE

WHILE 循环语句的一般形式：

WHILE <布尔表达式> LOOP
    要执行的语句;
END LOOP;

其中：循环语句执行的顺序是先判断<布尔表达式>的真假，如果为真则循环执行，否则退出循环。在WHILE循环语句中仍然可以使用EXIT或EXIT WHEN子句。
例：

DECLARE 
		x NUMBER :=1;
BEGIN
   WHILE x<=10 LOOP
      DBMS_OUTPUT.PUT_LINE('X的当前值为:'||x);
   	  x:= x+1;
   END LOOP;
END;

2.3、FOR

FOR循环语句的一般形式：

FOR 循环计数器 IN [ REVERSE ] 下限 .. 上限 LOOP
  要执行的语句;
END LOOP;

其中：

每循环一次，循环变量自动加1；使用关键字REVERSE，循环变量自动减1。
跟在IN REVERSE 后面的数字必须是从小到大的顺序，但不一定是整数，可以是能够转换成整数的变量或表达式。
可以使用EXIT WHEN子句退出循环，
例：

DECLARE
	V_counter NUMBER := 10;
BEGIN
   INSERT INTO temp_table(num_col) VALUES (v_counter );
   FOR v_counter IN 20 .. 25 LOOP
      INSERT INTO temp_table (num_col ) VALUES ( v_counter );
   END LOOP;
   INSERT INTO temp_table(num_col) VALUES (v_counter );
   FOR v_counter IN REVERSE 20 .. 25 LOOP
      INSERT INTO temp_table (num_col ) VALUES ( v_counter );
   END LOOP;
END ;

3、标号和GOTO语句

GOTO语句的一般形式：

GOTO   label;
 . . .  . . .
<<label>>　/*标号是用<<　>>括起来的标识符 */

GOTO语句是无条件跳转到指定的标号去的意思
例：

DECLARE
   V_counter NUMBER := 1;
BEGIN
   LOOP 
     DBMS_OUTPUT.PUT_LINE('V_counter的当前值为:'||V_counter);
 	 V_counter := v_counter + 1;
 	 IF v_counter > 10 THEN
     	GOTO l_ENDofLOOP;
 	 END IF;
   END LOOP;
   <<l_ENDofLOOP>>
     DBMS_OUTPUT.PUT_LINE('V_counter的当前值为:'||V_counter);
END ;

4、NULL语句

在PL/SQL 程序中，可以用 null 语句来说明“不用做任何事情”的意思，相当于一个占位符，可以使某些语句变得有意义，提高程序的可读性。
例：

DECLARE
	. . .
BEGIN
	…
	IF v_num IS NULL THEN
		GOTO print1;
	END IF;
	…
	<<print1>>
		NULL;  -- 不需要处理任何数据。
END;

六、异常

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

预定义 ( Predefined)错误：ORACLE预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。
非预定义 ( Predefined )错误：即其他标准的ORACLE错误。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。
用户定义(User_define) 错误：程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。

异常处理部分一般放在 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、预定义异常

对于预定义异常情况的处理，只需在PL/SQL块的异常处理部分，直接引用相应的异常情况名，并对其完成相应的异常错误处理即可。
例：

DECLARE
   v_empno emp.empno%TYPE :=&empno;
   v_sal    emp.sal%TYPE;
BEGIN
   SELECT sal INTO v_sal FROM emp WHERE empno=v_empno;
   IF v_sal<=1500 THEN 
        UPDATE emp SET sal=sal+100 WHERE empno=v_empno; 
        DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已更新!');     
   ELSE
			  DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已经超过规定值!');
   END IF; 
EXCEPTION
   WHEN NO_DATA_FOUND THEN  
     DBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||v_empno||'的员工');
   WHEN TOO_MANY_ROWS THEN
     DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);
END;

PostgreSQL 存储过程 utl_smtp plsql存储过程包_sql_03

2、非预定义的异常

对于这类异常情况的处理，首先必须对非定义的ORACLE错误进行定义。
步骤如下：

在PL/SQL 块的声明部分定义异常情况： <异常情况> EXCEPTION;
将其定义好的异常情况，与标准的ORACLE错误联系起来，使用EXCEPTION_INIT语句 PRAGMA
EXCEPTION_INIT(<异常情况>, <错误代码>)；
在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。

DECLARE
   v_deptno dept.deptno%TYPE :=&deptno;
   deptno_remaining EXCEPTION;
   PRAGMA EXCEPTION_INIT(deptno_remaining, -2292);
   /* -2292 是违反一致性约束的错误代码 */
BEGIN
   DELETE FROM dept WHERE deptno=v_deptno;
EXCEPTION
   WHEN deptno_remaining THEN 
      DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);
END;

3、用户自定义的异常

当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。用户定义的异常错误是通过显式使用 RAISE 语句来触发。当引发一个异常错误时，控制就转向到 EXCEPTION块异常错误部分，执行错误处理代码。
对于这类异常情况的处理，步骤如下：

在PL/SQL 块的声明部分定义异常情况： <异常情况> EXCEPTION;
RAISE <异常情况>
在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。
例：

DECLARE
   v_empno emp.empno%TYPE :=&empno;
   no_result  EXCEPTION;
BEGIN
   UPDATE emp SET sal=sal+100 WHERE empno=v_empno;
   IF SQL%NOTFOUND THEN
      RAISE no_result;
   END IF;
EXCEPTION
   WHEN no_result THEN 
      DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);
END;

注意：

某给定异常最多由异常处理部分的一个处理器进行处理。如果有多个异常处理器，则PL/SQL编译器产生PL-483错误。
SQLCODE返回当前错误代码，SQLERRM返回当前错误的消息文本。对于用户自定义异常SQLCODE返回值为‘1’，SQLERRM返回值为‘User-defined Exception’
Oracle错误信息的最大长度是512字节
SQLCODE和SQLERRM的值先赋给本地变量，不能直接用于SQL语句

4、异常错误传播

由于异常错误可以在声明部分和执行部分以及异常错误部分出现，因而在不同部分引发的异常错误也不一样。

4.1、可执行部分产生的异常

当一个异常错误在执行部分引发时，有下列情况：

如果当前块对该异常错误设置了处理，则执行它并成功完成该块的执行，然后控制转给包含块。
如果没有对当前块异常错误设置定义处理器，则通过在包含块中引发它来传播异常错误。然后对该包含块执行步骤1)。

4.2 声明部分产生的异常

如果在声明部分引起异常情况，即在声明部分出现错误，那么该错误就能影响到其它的块。
例：

DECLARE
	Abc number(3):=’abc’;
	其它语句
BEGIN
	其它语句
	EXCEPTION
	WHEN OTHERS THEN 
	其它语句
END; 
BEGIN
	DECLARE
	    Abc number(3):=’abc’;
	    其它语句
   	BEGIN
	    其它语句
   	EXCEPTION
	    WHEN OTHERS THEN 
	    其它语句
	END;
     EXCEPTION
	WHEN OTHERS THEN 
	其它语句
     END;

在该错误语句块的外部有一个异常错误，则该错误能被抓住。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：java map复制一个新map java map深拷贝

下一篇：python for循环外面定义一个变量 python循环定义多个变量

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯