oracle基础必备知识点——PL/SQL知识点详细总结（匿名块、变量、流程控制、游标、异常）

第一部分：基础知识

一、匿名块：

PL/SQL块由三个部分组成：声明部分、执行体和异常处理。其中声明和异常处理是可选的，执行部分是必须的。

DECLARE    --标记声明部分
……          --此处用来定义常量、变量、类型和游标等
BEGIN      --标记程序体部分开始
……           --此处用来编写各种PL/SQL语句、函数和存储过程
EXCEPTION --标记异常处理部分开始
……           --此处用来编写异常处理代码（when…then结构）
END;        --标记程序体部分结束

输出语句：dbms_output.put_line

替代变量：&用于接收用户的输入

连接符：||

显示输出结果：set serveroutput on

1、简例：根据员工编号输出员工的工资

set serveroutput on
declare
	v_sal number;
	v_no number:=&no; --提醒：如果该变量为字符类型，则需加引号，写成'&no'的形式
begin
    select sal into v_sal from emp where empno=v_no;
    dbms_output.put_line('编号为'||v_no||'的员工工资是：'||v_sal);
    EXCEPTION
    when no_data_found then
    dbms_output.put_line('没有这个员工');
end;

二、变量(基本变量、复合变量、自定义变量)和数据类型

数据类型： number 、varchar2、 char、 int、 date、 long 、Boolean

变量：
（一）基本变量：变量名 数据类型
（二）复合变量：
%type 以表中字段定义变量类型，或以已经声明过的变量作为变量类型
%rowtype 记录变量，表示的是一行数据。可以是表中的一条记录，也可以是自定义的一条记录。

表示表中一行记录：emp%rowtype ，意思是该变量中保存的数据结构和emp表完全一样。

自定义记录变量语法：

TYPE type_name IS RECORD (field_name field_type,….);
Identifier type_name; --identifier是标识符，表示一个变量名，type_name就是自定义的记录类型。这句话其实就是声明一个变量，变量的类型是自定义的记录类型。

案例：

DECLARE
	TYPE emp_record_type IS RECORD(name emp.ename%type,sal number(5,1),job emp.job%type);--自定义一个记录类型，包含emp表中部分字段。
	myemp emp_record_type;--使用自定义的类型声明一个记录变量
BEGIN
	select ename,sal,job  into myemp from emp where empno=7369;
  	DBMS_OUTPUT.PUT_LINE(myemp.name||' '||myemp.sal );
END;
/

提醒：

1. select语句后面不能用*，因为emp表中包含的字段更多，必须表明有哪些字段。
2. 输出语句中不能只写myemp，必须用加点形式输出每一个字段值。

三、流程控制

（一）选择结构：if语句和case语句

1. 最简单的分支：

if condition then
	Statement1
	….
	Statement
End if

2. 双分支：

IF  condition THEN
Statement1
ELSE
Statement2
END IF
Statement3

如果条件为真，则执行语句1，如果为假，则执行语句2，无论执行了哪一条语句，在执行结束后，都会继续执行语句3。

3. 多分支

if condition1 then
	Statement1
elsif condition2 then
	Statement2
elsif condition3 then
	Statement3
….
else
	StatementN
end if

提醒： elsif之间没有空格，尤其els后面没有e，end if之间必须有空格

1、案例：查询emp表中JAMES的工资，如果大于1500元，则发放奖金100元；如果工资大于900元，则发放奖金800元，否则发放奖金400元。

分析： 通常需要判断条件的字段，应该被定义为变量。

DECLARE
	v_sal emp.sal%type;
BEGIN
	select sal into v_sal from emp where ename='JAMES';
  	IF v_sal>1500 THEN
    UPDATE emp set comm=100 where ename='JAMES';
  	ELSIF v_sal>900 THEN
    UPDATE emp set comm=800 where ename='JAMES';
  	ELSE
    UPDATE emp set comm=400 where ename='JAMES';
  	END IF ;
END;

4. case 表达式

分为两种：一种叫选择器，一种叫搜索式

选择器形式的语法

case selector
	when expr1 then statement1
	when expr2 then statement2
	…
	else statement
end case;

Selector：待求值的表达式，即选择器(是一个确定的值，或者是一个有确定值的算术表达式)

expr1是与选择器进行比较的表达式。如果二者相等，则执行then后面的语句1（expr1是一个确定的值）

依此类推，一旦有一个表达式与选择器值相等，则执行其后面的语句，而其他语句则不再进行比较和执行。

如果所有表达式都与选择器不匹配，则执行else后面的语句。

搜索式语法：

case
	when condition1 then statement1
	when condition2 then statement2
	…
	else statement
end case

搜索式case语句没有选择器，并且，它的when子句只能包含结果为布尔类型的表达式，产生其他类型结果的表达式是不允许的。

当搜索条件（condition）的计算结果为真时，执行其then后的语句。如果任何搜索条件都为false，则就会执行与else子句相关的语句。其中else子句是可选的。

1、案例：根据输入的工资，输出对应的等级

DECLARE
	sal NUMBER:=&a;
	sal_gradeVARCHAR2(10);
BEGIN
	case
		when sal between 1000 and 3000 THEN
		dbms_output.put_line('middle');
		when sal>3000 THEN
		dbms_output.put_line('high');
		else
		dbms_output.put_line('lower');
	end CASE;
END;

（二）循环结构

1. 基本循环

Loop
	Statement
	…
	Exit when condition --exit语句必须使用在循环内。
End loop;

其中statement是循环体，condition是循环条件，当条件为真时，退出循环，如果为假，继续循环。

2. while循环

While condition loop
	Statement
	…
End loop;

其中statement是循环体，condition是循环条件，当条件为真时，执行循环体，如果为假，退出循环。

3. for循环

适合于知道循环的次数。
语法：

For index in [reverse] lower..upper 
loop
  	Statement
 	…
End loop;

其中，index是for循环计数器变量，计数器从最小值（lower）到最大值（upper）按步进递增，循环将会执行计数器递增的次数。双点号在PL/SQL中是范围操作符。

Reverse是反转的意思，正常的循环计数器从小到大增，使用反转后，将使计数器从大到小递减。

在使用for循环语句时，要注意一些基本规则：
（1）在循环中使用的计数器变量不需要声明，Oracle自动为for循环声明计数器变量
（2）计数器的值只能为整数，所以for循环也叫数值型循环
（3）计数器变量只能在循环体内引用，在循环体外不识别这个变量
（4）如果需要在循环体外引用这个计数器的值，可以使用变量记录它的值

1、案例：计算1—100的和

a、Loop循环

提醒： exit语句后面必须有循环条件更改语句，以保证循环结束。

在pl/sql程序中，不能用sum作变量名。

i:=i+1; 不能写成i++形式，在oracle中，没有自增或自减的语法

注意循环结束条件的终止值。在下面的循环中不能写成i>100

declare
	i number:=1;
	s number:=0;
begin
    loop
    s:=s+i;
    exit when i>99; --也可以写成i=100
    i:=i+1;
    end loop;
    dbms_output.put_line('1到100的和为：'||s);
end;

b、while 循环

declare
	i number:=1;
	s number:=0;
begin
	while i<=100 loop　–此句一定要有等号
  	s:=s+i;
  	i:=i+1;　--这是循环条件变更语句，不要漏掉
  	end loop;
  	dbms_output.put_line('1到100的和为：'||s);
end;

c、for循环

提醒：该循环中不要定义循环变量，也不能写i:=i+1 这样的语句

declare
	s number:=0;
begin
	for i in 1..100 loop
  		s:=s+i;
 	end loop;
  	dbms_output.put_line('1到100的和为：'||s);
end;

四、游标

（一）游标的四个属性（%ROWCOUNT、%FOUND、%NOTFOUND、%ISOPEN）

是指向查询结果集的一个指针，或者说游标就是一个结果集

分为显示游标和隐式游标

游标有四个属性：

%ROWCOUNT：
该属性表示游标从查询结果集中已经获取到的记录总数，返回数值类型的值。注意：不是结果集中的总行数，而是提取的行数。

%FOUND：判定游标是否从结果集中提取到数据，返回布尔类型。找到为真，否则为假。

%NOTFOUND：与前者相反，表示是否没有数据。如果提取到数据则返回false，如果没有提取到数据，则返回true

%ISOPEN：判断游标是否打开，打开为真，否则为假。

使用隐式游标中的属性时，其名称为sql。

1、案例：在emp表中对某部门的员工薪水增加100元，并显示本次更新操作共涉及了多少员工。

DECLARE
  	v_no emp.deptno%type:=&no;
BEGIN
  	update emp set sal=sal+100 where deptno=v_no;
  	dbms_output.put_line('共更新了'||sql%rowcount||'行');
END;

（二）显示游标

对于返回多条记录的查询，需要显示声明游标。

它可以用来逐行获取select语句中返回的多行数据，其使用主要遵循4个步骤：声明（cursor）、 打开（ open ）、 检索（fetch）、 关闭（close）

1、案例：声明一个游标，用于取得部门10的员工的姓名和薪水信息

提醒：游标声明中的select子句的字段，通常要建立变量，或者建立记录变量

方案一：

DECLARE
	--游标声明，后加is和查询语句
	--提醒：游标声明中的select子句的字段，通常要建立变量，或者建立记录变量
  	cursor emp_cursor is select ename,sal from emp where deptno=10;　
	v_name emp.ename% TYPE;
  	v_sal emp.sal% TYPE;
	emp_rec emp_cursor%rowtype;
BEGIN
  	open emp_cursor;　--打开游标，相当于执行了查询语句
  	loop
		--fetch语句有两个作用：第一，取出一条记录，第二，指针指向下一条记录。
		--其结构为fetch…into，从游标中取出相应字段值，赋给定义的变量
    	fetch emp_cursor into v_name,v_sal;
    	exit when emp_cursor%notfound; --此处属性使用游标的名称
    	dbms_output.put_line(v_name||'的薪水是：'|| v_sal);
  	end loop;
  	dbms_output.put_line('共取出了'||emp_cursor%rowcount||'条记录');
  	close emp_cursor;
END;

方案二：

declare
	cursor mycur is select ename,sal from emp where deptno=10;
	my mycur%rowtype;
begin
	open mycur;
	loop
	fetch mycur into my;
	exit when mycur%notfound;
	dbms_output.put_line('员工' || my.ename || '的薪水为:' || my.sal);
	end loop;
	dbms_output.put_line('共取出了'||mycur%rowcount||'条记录');
	close mycur;
end;

方案三：

declare
	cursor mycur is select ename,sal from emp where deptno=10;
begin
	for my in mycur loop
	dbms_output.put_line('员工' || my.ename || '的薪水为:' || my.sal);
	end loop;
	--dbms_output.put_line('共取出了'||mycur%rowcount||'条记录');
	--上面这个语句是不能正常输出的，因为当游标for循环结束后，其游标自动关闭，所以无法获得提取的记录。
end;

（三）参数游标

语法：

Cursor cursor_name(parmeter_name datatype…) is select_ statement

其中parmeter_name datatype为参数名称和类型。

当游标打开时，通过传入参数，生成查询。

1、案例：查询某部门的员工编号和职务。

DECLARE
  	cursor emp_cursor(v_no emp.deptno%type) is select empno,job from emp where deptno=v_no;
 	emp_record emp_cursor%rowtype;
BEGIN
  	--在打开游标时给定参数值，也可以采用键盘输入方式：open emp_cursor(&no)
	open emp_cursor(20);
  	loop
    	fetch emp_cursor into emp_record;
    	exit when emp_cursor%notfound;
    	dbms_output.put_line(emp_record.empno||'的职务是：'|| emp_record.job);
  	end loop;
  	dbms_output.put_line('共取出了'||emp_cursor%rowcount||'条记录');
  	close emp_cursor;
END;

想一下，上面loop循环通过游标的%rowcount属性能够获取所提取到的记录数，那么能不能使用for循环进行输出提取到的记录数：

declare 
    cursor mycur(v_no emp.deptno%type) is select empno,job from emp where deptno=v_no;
begin
    for mycursor in mycur(10)
    loop
    dbms_output.put_line(mycursor.empno||'的职务是：'|| mycursor.job);
    end loop;
    dbms_output.put_line('共取出了'||mycur%rowcount||'条记录');
end;

是不是觉得上面的代码没有问题，但是为什么会报错呢，这是因为：
对于for循环，end loop之后游标就自动关闭了，所以这个时候再使用游标来获取提取到的记录数的话就会显示无效的游标，根本的原因就是：for循环后，游标自动关闭。

（四）强型游标

游标声明时，虽未设定其查询定义，但是已经指定了游标的返回类型。其返回类型可以是Oracle内置类型，也可以是自定义类型。

语法：

type 游标名 is ref cursor return 返回类型

Type mycursor is ref cursor return dept%rowtype –返回已有的表的结构

--声明一个记录类型
Type emp_record is record(ename emp.ename%type,empjob varchar2(10),sal emp.sal%type);

--声明一个强型游标，该游标返回的类型就是自定义类型
Type ref_cursor_emp is ref cursor return emp_record;

点击我，学习游标与强型游标的使用。
点击我，学习游标与强型游标的使用。
点击我，学习游标与强型游标的使用。
点击我，学习游标与强型游标的使用。
点击我，学习游标与强型游标的使用。

强型游标的操作也是按声明、打开、检索和关闭四个步骤。

1、案例：显示部门表中的所有记录

DECLARE
  	type dept_cursor_type is ref CURSOR RETURN dept%rowtype;--声明一个游标变量
  	dept_cursor dept_cursor_type;
  	dept_record dept%rowtype;
BEGIN
  	open dept_cursor for select * from dept;
  	LOOP
    	FETCH dept_cursor into dept_record;
    	exit when dept_cursor%notfound;
    	dbms_output.put_line(dept_record.deptno||','||dept_record.dname||','||dept_record.loc);
  	end LOOP;
  	dbms_output.put_line('表中共有'|| dept_cursor%rowcount||'条记录');
  	close dept_cursor;
END;

（五）弱型游标

游标变量没有声明返回类型，当定义游标所对应的SQL语句时可以返回任意结构的数据，更自由和方便。

（六）游标for循环

可以简化游标的控制过程。

语法：

FOR record_name in cursor_name 
loop
	Satatement1
	…
End loop;

其中record_name是记录类型变量，不需要声明，Oracle会隐式声明，同样，当循环结束，它也会被隐式关闭。在for循环中，也不需要open\fetch\close操作。

1、案例：显示雇员表中某一部门的员工的薪水

DECLARE
  	CURSOR emp_cursor(v_no emp.deptno% TYPE) is SELECT ename,sal from emp where deptno=v_no;
BEGIN
  	FOR emp_record in emp_cursor(20) 
  	loop
    	dbms_output.put_line(emp_record.ename||'的薪水是：'||emp_record.sal);
  	end LOOP;
END;

（七）用游标更新数据（for update、where current of）

通常for update和where current of子句同时用。该子句表示是当前的游标记录。其好处是不需要写条件语句

例1：修改游标查出的的数据，对经理职位的员工薪水增加100元

DECLARE
  	CURSOR emp_cursor(v_no emp.deptno% TYPE) is SELECT * from emp where deptno= v_no　FOR UPDATE;
BEGIN
  	FOR emp_record in emp_cursor(20) 
  	loop
    	IF emp_record.job='MANAGER' THEN
      		UPDATE EMP set sal=sal+100 where current of emp_cursor;
    	end if;
    	dbms_output.put_line('员工'||emp_record.ename||'的薪水是：'||emp_record.sal);
  	end LOOP;
  	--commit;
END;

上面是通过一些特定的形式(for update、where current of)进行修改员工的薪水的，其实也可以不用这种方法，我们依然使用以前的方法的话，代码如下：

declare
	cursor mycur(v_no emp.deptno%type) is select * from emp where deptno=v_no;
begin
	for emp_record in mycur(10)
	loop
	    if emp_record.job='MANAGER' then
		update emp set sal=sal+1000 where empno=emp_record.empno;
	    end if;
	    dbms_output.put_line('员工：' || emp_record.ename || '薪水：' || emp_record.sal+1000);
	end loop;
end;

五、异常

在Oracle 中，异常通过异常名称来捕获。异常一般写在执行体的最下面。而when others一般是最后一条子句，确保异常在程序块中都被捕捉。

（一）预定义异常

指Oracle已经预先定义好名称的异常.

常用异常：
no_data_found：没有找到数据
too_many_rows：多于一行数据
zero_divide：被零除
value_error：发生算术、转换等值错误

（二）非预定义异常

使用该异常包括三步：

1. 在程序块的声明部分定义一个异常名称
2. 在声明部分使用伪过程将异常名称和错误编号关联
3. 在异常处理部分捕获异常并对异常情况做出相应的处理。

伪过程的语法：

pragma exception_init(exception_name,oracle_error_number)

（三）自定义异常

主要包括两步：

1. 异常定义
   在声明部分采用exception关键字声明异常：myexception exception
2. 异常引发
   在程序执行体部分，使用raise关键字进行引发：raise myexception

综上： 使用用户自定义异常，需要在声明部分定义异常的名称，然后在执行体中，通过一些逻辑判断决定是否违反了用户的规则，如果违反规则，则调用raise命令人为触发自定义的异常，并在异常处理部分捕捉到这个异常进行处理。

案例：在emp表中查询james的工资，如果工资小于5000，则引发自定义异常

DECLARE
  	myexception exception;
  	v_sal emp.sal% TYPE;
BEGIN
  	select sal into v_sal from emp where ename='JAMES';
  	IF v_sal<5000 THEN
    	raise myexception;
  	end if;
  	dbms_output.put_line('JAMES的工资是：'||v_sal);
	EXCEPTION
  	when myexception then
    	dbms_output.put_line('工资太少了');
  	when no_data_found THEN
    	dbms_output.put_line('没有这个员工');
END;