PL/SQL学习笔记 « 爬坑历程

1、基础知识

PLSQL是一种类Pascal语言，每一段程序都是由Block（代码块）组成

declare
     变量定
begin
    sql语句
    pl语句
exception
    异常处理
end;

PLSQL块分为三种：

匿名块 (Anonymous)
存储过程 (Procedure)
函数 (function)

上面举得例子就是匿名块,因为没有名字，所以叫匿名块。存储过程和函数都是有名字的，函数有返回值。

2、PLSQL变量

PLSQL变量主要有以下四种：

系统内置的常规简单变量类型
用户自定义复杂类型变量
引用类型变量（保存了一个指针值）
大对象类型（LOB）

变量类型举例：

布尔类型
日期类型
BFILE的二进制文件类型
日期类型
BLOB类型
long类型，长字符串
字符串类型

变量声明举例：

DECLARE v_hiredateDATE;
v_deptno NUMBER(2) NOT NULL := 10;
v_location VARCHAR2(13) := 'Atlanta';
c_comm CONSTANT NUMBER := 1400;

变量声明后再没有赋值之前其值为NULL

同一个块中，应该避免多个变量使用相同的名字

常规变量的申明：

DECLARE
	v_job VARCHAR2(9);
	v_count BINARY_INTEGER := 0;
	v_total_sal NUMBER(9,2) := 0;
	v_orderdate DATE := SYSDATE + 7;
	c_tax_rate CONSTANT NUMBER(3,2) := 8.25;
	v_valid BOOLEAN NOT NULL := TRUE;
	... 
-- 特殊申明变量方式：
	v_name employees.last_name%TYPE;
	v_min_balance v_balance%TYPE := 10;
begin
   null;
end;

PLSQL中的游标

游标概论：

游标是一个私有的SQL工作区域，Oracle数据库中有两种游标，分别是隐式游标和显式游标，隐式游标不易被用户和程序员察觉和意识到，实际上Oracle服务器使用隐式游标来解析和执行我们提交的SQL语句；而显式游标是程序员在程序中显式声明的；通常我们说的游标均指显式游标。

显示游标

对于返回多行结果的SQL语句的返回结果，可使用显式游标独立的处理器中每一行的数据。

此处图片缺失

显式游标控制的一般过程：

DECLARE
	v_empno employees.employee_id%TYPE;
    v_ename employees.last_name%TYPE;
    CURSOR emp_cursor IS --1.创建游标
      SELECT employee_id, last_name FROM employees;
BEGIN
    OPEN emp_cursor; --2.打开游标
    LOOP
      FETCH emp_cursor
        INTO v_empno, v_ename; --3.提取变量  fetch cursor_name into value1,value2;

      EXIT WHEN emp_cursor%ROWCOUNT > 10 OR emp_cursor%NOTFOUND; --emp_cursor%NOTFOUND 4.用来测试是否有数据
      DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_empno) || ' ' || v_ename);
    END LOOP;
    CLOSE emp_cursor; --5.关闭游标
END;

`for`循环控制游标

举例1：直接使用for+sql创建并适用游标

   --一般格式：
   --for record_name in cursor_name loop ... end loop;
   BEGIN
   FOR emp_record IN (SELECT last_name, department_id
   FROM employees) LOOP
   -- implicit open and implicit fetch occur
   IF emp_record.department_id = 80 THEN
   ...
   END LOOP; -- implicit close occurs
   END;

举例2：先创建游标emp_cursor，再用for调用

--这种情况适用于多次调用游标的情况
 DECLARE
   CURSOR emp_cursor IS --先创建游标
   SELECT last_name, department_id
   FROM employees;
BEGIN
   FOR emp_record IN emp_cursor LOOP --再调用游标访问
   -- implicit open and implicit fetch occur
   IF emp_record.department_id = 80 THEN
   ...
   END LOOP; -- implicit close occurs
END;

###游标带参数直接返回和参数相关的查询结果

 DECLARE
   	CURSOR emp_cursor
       (p_deptno NUMBER, p_job VARCHAR2) IS
   		SELECT employee_id, last_name
   		FROM employees
   		WHERE department_id = p_deptno
   		AND job_id = p_job;
BEGIN
   	OPEN emp_cursor (80, 'SA_REP');
   	. . .
   	CLOSE emp_cursor;
   	OPEN emp_cursor (60, 'IT_PROG');
   	. . .
END;

###在游标中使用FOR UPDATE NOWAIT

有的时候我们打开一个游标是为了更新或者删除一些记录，这种情况下我们希望在打开游标的时候即锁定相关记录，应该使用for update nowait语句，倘若锁定失败我们就停止不再继续，以免出现长时间等待资源的死锁情况。

举例：

DECLARE
   	CURSOR emp_cursor IS
   		SELECT employee_id, last_name, department_name
   		FROM employees,departments
   		WHERE employees.department_id =
   			  departments.department_id
   		AND employees.department_id = 80
   		FOR UPDATE OF salary NOWAIT;

PLSQL中的异常

PLSQL中一般有两种异常，一种是由Oracle内部错误抛出的异常，分为预定义和非预定义两种；另外一种是由程序员显式的抛出。

常见的异常：

异常代码	异常类型
NO_DATA_FOUND	没有发现数据
INVALID_CURSOR	游标在被打开以前，引用%NOTFOUND属性会产生此异常
TOO_MANY_ROWS	返回数据过多
ZERO_DIVIDE	除数为零，出现异常
DUP_VAL_ON_INDEX	唯一索引上有重复值

对othres的处理

others表明我们未能预计的错误，所以全部归到othres中去，单发生这种情况时，我们希望了解错误号和相关信息，可以使用Oracle内置的函数SQLCODE和SQLERRM来返回错误号和错误描述，举例见下：

DECLARE
	v_error_code NUMBER;
	v_error_message VARCHAR2(255);
BEGIN
	...
EXCEPTION
	...
WHEN OTHERS THEN
	ROLLBACK;
	v_error_code := SQLCODE ;
	v_error_message := SQLERRM ;
	INSERT INTO errors
	VALUES(v_error_code, v_error_message);
END;

处理用户自定义异常

此处图片缺失

举例：

DECLARE
	e_invalid_department EXCEPTION;
BEGIN
	UPDATE departments
	SET department_name = &p_department_desc
	WHERE department_id = &p_department_number;
	IF SQL%NOTFOUND THEN
		RAISE e_invalid_department;
	END IF;
	COMMIT;
EXCEPTION
	WHEN e_invalid_department THEN
		DBMS_OUTPUT.PUT_LINE('No such department id.');
END;

传递异常到外层代码块

DECLARE
	. . .
	e_no_rows exception;
	e_integrity exception;
	PRAGMA EXCEPTION_INIT (e_integrity, -2292);
BEGIN
	FOR c_record IN emp_cursor LOOP
      --内层代码块
	  BEGIN
		SELECT ...
		UPDATE ...
		IF SQL%NOTFOUND THEN
			RAISE e_no_rows; --将异常扔到外层代码块中，使用关键字：RAISE
		END IF;
	  END;

	END LOOP;
EXCEPTION
	WHEN e_integrity THEN ...
	WHEN e_no_rows THEN ...
END;

存储过程

存储过程基本语法

语法：

CREATE [OR REPLACE] PROCEDURE procedure_name
	[(parameter1 [mode1] datatype1,
	parameter2 [mode2] datatype2,
	. . .)]
IS|AS
PL/SQL Block;

举例：

CREATE OR REPLACE PROCEDURE raise_salary
	(p_id IN employees.employee_id%TYPE)
IS
BEGIN
	UPDATE employees
	SET salary = salary * 1.10
	WHERE employee_id = p_id;
END raise_salary;

存储过程参数模式

IN	OUT	IN OUT
默认模式	必须显示指定	必须显示指定
用以把值传给过程	用以把值从过程返回给调用环境	用以把变量传递给过程，并返回给调用环境
参数可以是常数、变量、表	必须是个变量	必须是个变量
必须是个变量	不能赋予默认值	不能赋予默认值

举例：IN OUT型变量的应用

--程序功能为：将传入的电话号码字符串按xxx-xxx-xxxxx的形式返回
CREATE OR REPLACE PROCEDURE format_phone
	(p_phone_no IN OUT VARCHAR2)
IS
BEGIN
	p_phone_no := '(' || SUBSTR(p_phone_no,1,3) ||
	')' || SUBSTR(p_phone_no,4,3) ||
	'-' || SUBSTR(p_phone_no,7);
END format_phone;
/

存储过程的参数传递

使用默认值
按顺序传递
使用=>传递

举例：

首先创建存储过程add_dept，设定了两个参数，p_name和p_loc

--设定默认参数使用关键字：DEFAULT
CREATE OR REPLACE PROCEDURE add_dept
	(p_name IN departments.department_name%TYPE DEFAULT 'unknown',
	p_loc IN departments.location_id%TYPE DEFAULT 1700)
IS
BEGIN
	INSERT INTO departments(department_id,
				department_name, location_id)
	VALUES (departments_seq.NEXTVAL, p_name, p_loc);
END add_dept;
/

然后，使用调用add_dept，分别使用三种方式传递参数

BEGIN
	add_dept; --1.默认参数
	add_dept ('TRAINING', 2500); --2.顺序传递
	add_dept ( p_loc => 2400, p_name =>'EDUCATION');--3.使用=>传递
	add_dept ( p_loc => 1200) ;
END;
/
SELECT department_id, department_name, location_id
FROM departments; --sql查询验证

存储过程处理异常

此处图片缺失

函数

自治事务

关键字：

一个事务A在另一个事务B内被调用，那个事务A是自治事务，自治事务A执行过程中会脱离其session内未执行完毕的事务的影响。如果session从B事务开始——A事务开始和结束——B事务结束，上述的A事务不受没有完成的B事务的影响，然后A事务执行完毕后再次回到B事务执行没有完成的B事务。

创建自治事务语法：

Create or replace procedure pro_name as pragma AUTONOMOUS_TRANSACTION;
Begin
 null;
End;

下面通过一个例子来对自治事务进行说明和解释，首先创建一个procedure—noautonomous_transaction01：

Create or replace procedure noautonomous_transaction01 as
Begin
	Insert into test01 select * from test01 where rownum=1;
	Commit;
End;
--进行查询测试
Select count（*） from test01
--结果：Count(*)|25

此时，发现test01有25条数据

declare
begin
	Insert into test01 select * from test01 where rownum=1;
	Noautonomous_transaction01;--调用事务
	Rollback;
End;

上述pl/sql程序块内嵌套了一个dml的procedure过程。执行pl/sql程序块结束后发现test01有__27__条数据，也就说明了procedure中的commit完成了上述procedure和pl/sql程序块的提交。最后的rollback也就没有任何效果。

结论：非自治事务内的procedure中得commit或者rollback会影响前面所有dml的影响。

现在创建一个自治事务：

--Create Procedure的autonomous_transaction01
Create or replace procedure autonomous_transaction01 as pragma autonomous_transaction01;
Begin
	Insert into test01select * from test01 where rownum=1;
	Commit;
End;
--查询进行测试
Select count(*) from test01
--Count(*)|25

此时test01有__25__条数据。然后，在pl/sql程序块中调用自治事务autonomous_transaction01：

Begin
Insert into test01 select * from test01 where rownum=1;
	Autonomous_transaction01;
	Rollback;--rollback并没有影响到自治事务内的操作
End;

自治事务内的commit只会影响自治事务内的没有提交的dml，调用自治事务完毕后又会回到调用自治事务的事务内，此时最后的rollback也只能回滚自治事务外的dml，所以此时的test01中还是只有__25__条数据。

自治事务与被调用事务完全独立，不能共享调用者事务使用的锁和其他资源，而且自治事务内还可以调用其他自治事务。自治事务与其调用者可能会发生死锁，oracle会检测此类死锁并返回错误信息。

文件操作

系统函数：

utl_file.fopen(三个参数：文件对象，文件名，打开方式)       打开文件
utl_file.put_line(文件对象，字符串)    输出文件
utl_file.get_line()    读取文件
utl_file.fclose()      关闭文件
utl_file.fflush()      强制输出缓冲

文件打开方式

也就是fopen()的第三个参数：

r -- read text
w -- write text
a -- append text
rb -- read byte mode
wb -- write byte mode
ab -- append byte mode
--如果指定'a'或者'ab'但是文件不存在会先创建。

举例：

CREATE OR REPLACE PROCEDURE FILE_TEMP IS
   FILEHANDLE UTL_FILE.FILE_TYPE;--定义文件对象
BEGIN
  --打开文件
  FILEHANDLE := UTL_FILE.fopen('FILENAME' ,'FILE_TEMP.TXT' ,'W' );
  --写入文件
  UTL_FILE.put_line(FILEHANDLE,'这是一个练习和测试');
  FOR TEMP_1 IN (SELECT * FROM EMPLOYEES) LOOP
   --写入文件
   UTL_FILE.put_line(FILEHANDLE,TEMP_1.LAST_NAME || '|' || TEMP_1.SALARY);
  END LOOP;
  UTL_FILE.FCLOSE(FILEHANDLE);--关闭文件
END;

引用游标

create or replace procedure test_ref1
  is
   type s_table is ref cursor;
   f  s_table;
   f_rec t1% ;
   stmt varchar2(100) := 'select empno,ename from emp';
begin
   insert into t1 select empno,ename,sal from emp;
   open f for select * from t1;
   loop
       fetch f into f_rec;
       dbms_output.put_line(f_rec.empno ||'  '||f_rec.ename);
       exit when f%rowcount=3 ;
   end loop;
   close f;
end;
/

上课记录

begin ...end; 为一个程序块
匿名块不能被调用，因为没有名字，一般用于测试，可以直接执行
存储过程和函数相比，没有返回值，两者都需要编译后进行调用，才可以执行。
返回值和参数定义不需要指定长度，定义变量需要指定长度

1、基础知识

2、PLSQL变量

PLSQL中的游标

显示游标

for循环控制游标

PLSQL中的异常

常见的异常：

对othres的处理

处理用户自定义异常

传递异常到外层代码块

存储过程

存储过程基本语法

存储过程参数模式

存储过程的参数传递

存储过程处理异常

函数

自治事务

文件操作

系统函数：

文件打开方式

引用游标

上课记录

`for`循环控制游标