PLSQL

PL/SQL

一、PL/SQL 程序设计简介

1、概述

• PL/SQL 是 Procedure Language & Structured Query Language 的缩写。
• PL/SQL是对 SQL 语言存储过程语言的扩展。
• 它现在已经成为一种过程处理语言，简称 PL/SQL。

2、PL/SQL 可用的 SQL 语句

• PL/SQL 是 ORACLE 系统的核心语言，现在 ORACLE 的许多部件都是由 PL/SQL 写成。
• 在 PL/SQL 中可以使用的 SQL 语句有：
  • INSERT，UPDATE，DELETE，SELECT … INTO，COMMIT，ROLLBACK，SAVEPOINT。
• 提示：在 PL/SQL 中只能用 SQL 语句中的 DML 部分，不能用 DDL 部分，如果要在 PL/SQL 中使用 DDL(如CREATE table 等)的话，只能以动态的方式来使用。

补充：大纲

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

2、记录类型

3、流程控制

3.1条件判断（两种）

方式一：if … then … elseif 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> set serveroutput on

1、PL/SQL 块

PL/SQL 程序由三个块组成，即声明部分、执行部分、异常处理部分

（1）PL/SQL 块的结构如下：

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

其中 执行部分是必须的。

• 输出helloworld

declare----声明的变量、类型、游标
begin----程序的执行部分（类似于java里的main（）方法）dbms_output.put_line('helloworld');
exception----针对begin块中出现的异常，提供处理的机制----when ... then ...----when ... then ...
end;

SQL> set serveroutput onbegindbms_output.put_line('helloworld');
end;helloworldPL/SQL procedure successfully completed

• 查询100号员工的工资

declare--声明变量v_sal number(20);  
begin--sql语句的操作：select ... into ... from ... where ...select salary into v_sal from employees where employee_id = 100;--打印dbms_output.put_line(v_sal);
end;

• 查询100号员工的工资，邮箱，入职日期

declarev_sal number(20);v_email varchar2(20);v_hire_date date;  
beginselect salary,email, hire_date into v_sal,v_email,v_hire_date from employees where employee_id = 100;dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);
end;24000,SKING,17-JUN-87PL/SQL procedure successfully completed

注意：变量之间要对应。

• 声明变量也可以动态声明

declarev_sal employees.salary%type;v_email employees.email%type;v_hire_date employees.hire_date%type;  
beginselect salary,email, hire_date into v_sal,v_email,v_hire_date from employees where employee_id = 100;dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);
end;

（2）PL/SQL 块可以分为三类：

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

2、标识符

PL/SQL 程序设计中的标识符定义与 SQL 的标识符定义的要求相同。要求和限制有：

• 标识符名不能超过 30 字符；
• 第一个字符必须为字母；
• 不分大小写；
• 不能用’-‘(减号);
• 不能是 SQL 保留字。

一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果。

例如：下面的例子将会删除所有的纪录，而不是 KING 的记录；

DECLAREEname varchar2(20) := ’KING’;
BEGINDELETE FROM emp WHERE ename=ename;
END;

变量命名在 PL/SQL 中有特别的讲究，建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求，使得整个

系统的文档在规范上达到要求。下面是建议的命名方法：

3、变量类型

（1）变量类型

补充：number类型

NUMBER（ precision， scale）

• precision表示数字中的有效位;如果没有指定precision的话，Oracle将使用38作为精度。

• 如果scale大于零，表示数字精确到小数点右边的位数；scale默认设置为0；如果scale小于零，Oracle将把该数字取舍到小数点左边的指定位数。

• Precision的取值范围为【1—38】；Scale的取值范围为【-84—127】。

• NUMBER整数部分允许的长度为（precision- scale），无论scale是正数还是负数。

• 如果precision小于scale，表示存储的是没有整数的小数。

• Precision表示有效位数，有效数位：从左边第一个不为0的数算起，小数点和负号不计入有效位数；scale表示精确到多少位，指精确到小数点左边或右边多少位(±决定)。

Number值类型举例：

• 关于precision， scale也可以作如下表述，定点数的精度§和刻度(s)遵循以下规则：

1. 当一个数的整数部分的长度 > p-s 时，Oracle就会报错

2. 当一个数的小数部分的长度 > s 时，Oracle就会舍入。

3. 当s(scale)为负数时，Oracle就对小数点左边的s个数字进行舍入。

4. 当s > p 时, p表示小数点后第s位向左最多可以有多少位数字，如果大于p则Oracle报错，小数点后s位向右的数字被舍入

• 与int的区别

1）oracle本来就没有int类型，为了与别的数据库兼容，新增了int类型作为number类型的子集。

2）int类型只能存储整数，int相当于number(22),存储总长度为22的整数。

3）number可以存储浮点数，也可以存储整数；

number(8,1)存储小数位为1位，总长度为8的浮点数，如果小数位数不足，则用0补全；

number(8)存储总长度为8的整数；

• number类型的子类

a) oracle本来就没有int类型，为了与别的数据库兼容，新增了int类型作为number类型的子集。

b) 在oracle数据库建表的时候，decimal,numeric不带精度，oracle会自动把它处理成INTEGER；带精度，oracle

会自动把它处理成number。

c) Oracle只用NUMBER(m,n)就可以表示任何复杂的数字数据。

d) decimal,numeric,int等都为SQL、DB2等数据库的数据类型，ORACLE为了兼容才将其引入；但实际上在

ORACLE内部还是以NUMBER的形式将其存入。

declarev_sal number(8,2) :=0;v_emp_id number(10);v_email varchar2(20);v_hiredate date;
beginselect salary,employee_id,email,hire_date into v_sal,v_emp_id,v_email,v_hiredatefrom employeeswhere employee_id = 123;dbms_output.put_line('employee_id:'||v_emp_id||' salary:'||v_sal||' email:'||v_email||' hire_date:'||v_hiredate);end;employee_id:123 salary:6500 email:SVOLLMAN hire_date:10-OCT-97PL/SQL procedure successfully completed

（2）复合类型 ------记录和表

• 记录类型

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

同但逻辑相关的信息。

定义记录类型语法如下:

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

declare
----声明一个记录类型type emp_record is record(v_sal employees.salary%type,v_email employees.email%type,v_hire_date employees.hire_date%type);----定义一个记录类型的成员变量v_emp_record emp_record;beginselect salary,email, hire_date into v_emp_record from employees where employee_id = 100;dbms_output.put_line(v_emp_record.v_sal||','||v_emp_record.v_email||','||v_emp_record.v_hire_date);
end;SQL> /24000,SKING,17-JUN-87PL/SQL procedure successfully completed

注：相当于是一个类里面有三个成员变量，java里面不能通过类去调用成员变量，需要通过对象来调用。

declare
----声明一个记录类型type emp_record is record(v_sal number(8,2) :=0,v_emp_id number(10),v_email varchar2(20),v_hiredate date);----声明一个记录类型的变量v_emp_record emp_record;
beginselect salary,employee_id,email,hire_date into v_emp_recordfrom employeeswhere employee_id = 123;dbms_output.put_line('employee_id:'||v_emp_record.v_emp_id||' salary:'||v_emp_record.v_sal||' email:'||v_emp_record.v_email||' hire_date:'||v_emp_record.v_hiredate);end;employee_id:123 salary:6500 email:SVOLLMAN hire_date:10-OCT-97PL/SQL procedure successfully completed

注：声明记录类型时一定要注意里面的标点

declaretype salary_record is record(v_name varchar2(20),v_salary number(10,2));v_sal_record salary_record;beginv_sal_record.v_name :='刘德华';v_sal_record.v_salary :=12000;dbms_output.put_line('name:'||v_sal_record.v_name||'salary:'||v_sal_record.v_salary);end;  name:刘德华salary:12000PL/SQL procedure successfully completed

• 使用%TYPE

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

优点：

所引用的数据库列的数据类型可以不必知道；

所引用的数据库列的数据类型可以实时改变。

（3）使用%ROWTYPE

PL/SQL 提供%ROWTYPE 操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。

优点：

所引用的数据库中列的个数和数据类型可以不必知道；

所引用的数据库中列的个数和数据类型可以实时改变。

declarev_emp_record employees%rowtype;beginselect * into v_emp_recordfrom employeeswhere employee_id = 123;dbms_output.put_line('employee_id:'||v_emp_record.employee_id||' salary:'||v_emp_record.salary||' email:'||v_emp_record.email||' hire_date:'||v_emp_record.hire_date);end;  

declarev_emp_record employees%rowtype;beginselect * into v_emp_recordfrom employeeswhere employee_id = 123;dbms_output.put_line('employee_id:'||v_emp_record.employee_id||' salary:'||v_emp_record.salary||' email:'||v_emp_record.email||' hire_date:'||v_emp_record.hire_date);end;  

（4）PL/SQL 表(嵌套表)

（emmmm，暂不掌握）

PL/SQL 程序可使用嵌套表类型创建具有一个或多个列和无限行的变量, 这很像数据库中的表。

声明嵌套表类型的一般语法如下:

TYPE type_name IS TABLE OF
{datatype | {variable | table.column} % type | table%rowtype};

4、运算符和表达式（数据定义）

（1）关系运算符

（2）一般运算符

（3）逻辑运算符

5、变量赋值

在 PL/SQL 编程中，变量赋值是一个值得注意的地方，它的语法如下：

variable := expression;

variable 是一个 PL/SQL 变量, expression 是一个 PL/SQL 表达式。

（1）字符及数字运算特点

空值加数字仍是空值：NULL + < 数字> = NULL

空值加（连接）字符，结果为字符：NULL || <字符串> = < 字符串>

（2）BOOLEAN 赋值

布尔值只有 TRUE, FALSE 及 NULL 三个值。

（3）数据库赋值

数据库赋值是通过SELECT语句来完成的，每次执行SELECT语句就赋值一次，一般要求被赋值的变量与SELECT中的列名要一一对应。如：

DECLARE
emp_id emp.empno%TYPE :=7788;
emp_name emp.ename%TYPE;
wages emp.sal%TYPE;BEGIN
SELECT ename, NVL(sal,0) + NVL(comm,0) INTO emp_name, wages 
FROM emp WHERE empno = emp_id;
DBMS_OUTPUT.PUT_LINE(emp_name||’----‘||to_char(wages));
END;

提示：不能将SELECT语句中的列赋值给布尔变量。

（4）可转换的类型赋值

• CHAR 转换为 NUMBER

使用 TO_NUMBER 函数来完成字符到数字的转换，如：

v_total := TO_NUMBER(‘100.0’) + sal;

• NUMBER 转换为 CHAR

使用 TO_CHAR 函数可以实现数字到字符的转换，如：

v_comm := TO_CHAR(‘123.45’) || ’元’ ;

• 字符转换为日期

使用 TO_DATE 函数可以实现字符到日期的转换，如：

v_date := TO_DATE(‘2001.07.03’,‘yyyy.mm.dd’);

• 日期转换为字符

使用 TO_CHAR 函数可以实现日期到字符的转换，如：

v_to_day := TO_CHAR(SYSDATE, ‘yyyy.mm.dd hh24:mi:ss’) ;

6、变量作用范围及可见性

在 PL/SQL 编程中，如果在变量的定义上没有做到统一的话，可能会隐藏一些危险的错误，这样的原因主要是变量的作用范围所致。与其它高级语言类似，PL/SQL 的变量作用范围特点是：

• 变量的作用范围是在你所引用的程序单元（块、子程序、包）内。即从声明变量开始到该块的结束。

• 一个变量（标识）只能在你所引用的块内是可见的。

• 当一个变量超出了作用范围，PL/SQL 引擎就释放用来存放该变量的空间（因为它可能不用了）。

• 在子块中重新定义该变量后，它的作用仅在该块内。

7、注释

1）使用双 ‘-‘ ( 减号) 加注释

PL/SQL允许用 – 来写注释，它的作用范围是只能在一行有效。如：

V_Sal NUMBER(12,2); – 工资变量。

2）使用 /************/ 来加一行或多行注释，如：

/* 文件名： department_salary.sql */

/***********************************************/

提示：被解释存放在数据库中的 PL/SQL 程序，一般系统自动将程序头部的注释去掉。

只有在 PROCEDURE 之后的注释才被保留；另外程序中的空行也自动被去掉。

8、简单例子

通过变量实现delete，update，insert

（1）数据插入

DECLARE
v_ename VARCHAR2(20) := ‘Bill’;
v_sal NUMBER(7,2) :=1234.56;
v_deptno NUMBER(2) := 10;
v_empno NUMBER(4) := 8888;
BEGIN
INSERT INTO emp ( empno, ename, JOB, sal, deptno , hiredate ) 
VALUES ( v_empno, v_ename, ‘Manager’, v_sal, v_deptno, 
TO_DATE(’1954.06.09’,’yyyy.mm.dd’) );
COMMIT;
END;

（2）数据更新

declarev_emp_id number(10);begin v_emp_id :=123;update employeesset salary = salary + 100where employee_id = v_emp_id;dbms_output.put_line('执行成功');
end;执行成功PL/SQL procedure successfully completed

（3）数据删除

DECLARE
v_empno number(4) := 8888;
BEGIN
DELETE FROM emp WHERE empno=v_empno;
COMMIT;
END;

三、PL/SQL 流程控制语句

介绍 PL/SQL 的流程控制语句, 包括如下三类:

• 控制语句: IF 语句
• 循环语句: LOOP 语句, EXIT 语句
• 顺序语句: GOTO 语句, NULL 语句

1、条件语句

（1）if表达式

（更具有通用性）

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

注意：ELSIF 不能写成 ELSEIF

要求：查询出 150号 员工的工资, 若其工资大于或等于 10000 则打印 ‘salary >= 10000’;

若在 5000 到 10000 之间, 则打印 ‘5000<= salary < 10000’; 否则打印 ‘salary < 5000’

法一：
declarev_sal employees.salary%type;beginselect salary into v_sal from employees where employee_id = 150;if v_sal >=1000 then dbms_output.put_line('salary >=10000');elsif v_sal >=5000 then dbms_output.put_line('5000=10000PL/SQL procedure successfully completed

法二：
declarev_sal employees.salary%type;v_temp varchar2(30);beginselect salary into v_sal from employees where employee_id = 150;if v_sal >=1000 then v_temp := 'salary >=10000';elsif v_sal >=5000 then v_temp := '5000=10000PL/SQL procedure successfully completed

（2）case表达式

限制因素很多，when和then后面不能写范围，只能写具体的值

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

上一题的法三：错误写法：
declarev_sal employees.salary%type;v_temp varchar2(30);beginselect salary into v_sal from employees where employee_id = 150;case v_sal when v_sal >=10000 then v_temp := 'salary >=10000'when v_sal >=5000 then v_temp := '5000<=salary<10000'else v_temp := 'salary<5000'end;dbms_output.put_line(v_sal||','||v_temp);end;注意：用case when的时候，when和then后面不可以放变量，只能写具体的值。并且，then后面不可以写dbms_output.put_line()正确写法：
declarev_sal employees.salary%type;v_temp varchar2(30);beginselect salary into v_sal from employees where employee_id = 150;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;

查询出 122 号员工的 JOB_ID, 若其值为 'IT_PROG', 则打印 'GRADE A'; 'AC_MGT', 打印 'GRADE B', 'AC_ACCOUNT', 打印 'GRADE C'; 否则打印 'GRADE D'declarev_job_id varchar2(10);v_temp varchar2(10);beginselect job_id into v_job_id from employees where employee_id = 122;v_temp:=case v_job_id when 'IT_PROG' then 'GRADE A'when 'AC_MGT' then 'GRADE B'when 'AC_ACCOUNT' then 'GRADE C'else 'GRADE D'end;dbms_output.put_line(v_job_id || ',' ||v_temp);end;                     ST_MAN,GRADE DPL/SQL procedure successfully completed

2、循环语句

（1）简单循环

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

• 使用简单循环打印1-100

--①初始化条件②循环体③循环条件④迭代条件
declare--①v_i number(5):=1;
beginloop--②dbms_output.put_line(v_i);--③    exit when v_i >=100;--④v_i :=v_i+1;end loop;end;

--①初始化条件②循环体③循环条件④迭代条件
declare--①v_i number(5):=1;
beginloop--②dbms_output.put_line(v_i);--④v_i :=v_i+1;--③    exit when v_i >=100;end loop;end;
此时打印出了1-99

（2）while循环

（相较简单循环，推荐使用while循环）

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

• 使用while循环打印1-100

--①初始化条件②循环体③循环条件④迭代条件
declare--①v_i number(5):=1;
begin--③while v_i <=100 loop--②dbms_output.put_line(v_i);--④v_i :=v_i+1;  end loop;end;

（3）for循环

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

每循环一次，循环变量自动加 1；使用关键字REVERSE，循环变量自动减 1。跟在 IN REVERSE 后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或表达式。可以使用 EXIT 退出循环。

• 使用for循环打印1-100

beginfor c in 1 ..100 loopdbms_output.put_line(c);end loop;end;

• 打印100-1

beginfor c in reverse 1 ..100 loopdbms_output.put_line(c);end loop;end;

（4）练习

输出2-100之间的质数（质数：只能被1和本身整除）

while循环：declarev_i number(3) :=2;v_j number(3) :=2;v_flag number(1) :=1;
beginwhile v_i <=100 loopwhile v_j <=sqrt(v_i) loopif v_i mod v_j = 0 then v_flag :=0;end if;v_j := v_j +1;end loop;if v_flag = 1 then dbms_output.put_line(v_i);end if;v_j :=2;v_i := v_i+1;v_flag :=1;end loop;end;

for循环：declarev_flag number(1) :=1;
beginfor v_i in 2 .. 100 loopfor v_j in 2 .. sqrt(v_i) loopif 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;

3、标号和 GOTO

PL/SQL 中 GOTO 语句是无条件跳转到指定的标号去的意思。语法如下：

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

使用goto语句输出2-100之间的质数：
declarev_flag number(1) :=1;
beginfor v_i in 2 .. 100 loopfor v_j in 2 .. sqrt(v_i) loopif mod(v_i,v_j) = 0 then v_flag :=0;goto label;end if;end loop;<>if v_flag = 1 then dbms_output.put_line(v_i);end if;v_flag :=1;end loop;end;

打印1——100的自然数，当打印到50时，跳出循环，输出“打印结束”

beginfor i in 1 .. 100 loopif i = 50 then goto label;end if;dbms_output.put_line(i);end loop;<>dbms_output.put_line('打印结束');
end;

beginfor i in 1 .. 100 loopif i = 50 then dbms_output.put_line('打印结束'); exit;end if;dbms_output.put_line(i);end loop;end;

四、游标的使用

在 PL/SQL 程序中，对于处理多行记录的事务经常使用游标来实现。

1、游标的概念

游标是一个指向上下文的句柄( handle)或指针。

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

2、显式游标

（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 子句。

例：打印出 80 部门的所有的员工的工资:salary: xxx

declarev_sal employees.salary%type;v_empid employees.employee_id%type;--定义游标cursor emp_sal_cursor is select salary,employee_id from employees where department_id = 80;
begin--打开游标open emp_sal_cursor;--提取游标fetch emp_sal_cursor into v_sal,v_empid;while emp_sal_cursor%found loopdbms_output.put_line('empid:'||v_empid||' salary:'||v_sal);fetch emp_sal_cursor into v_sal,v_empid;end loop;----关闭游标close emp_sal_cursor;
end;

declare--声明一个记录类型type emp_record is record(v_sal employees.salary%type,v_empid employees.employee_id%type,v_hiredate employees.hire_date%type    );--声明一个记录类型的变量v_emp_record emp_record;--定义游标cursor emp_sal_cursor is select salary,employee_id,hire_date from employees where department_id = 80;
begin--打开游标open emp_sal_cursor;--提取游标fetch emp_sal_cursor into v_emp_record;while emp_sal_cursor%found loopdbms_output.put_line('empid:'||v_emp_record.v_empid||' salary:'||v_emp_record.v_sal||' hire_date:'||v_emp_record.v_hiredate);fetch emp_sal_cursor into v_emp_record;end loop;----关闭游标close emp_sal_cursor;
end;     

（2）游标属性

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

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

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

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

• 给工资低于 3000 的员工工资调为 3000。

（3）游标的for循环

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

格式：

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

其中：
index_variable 为游标 FOR 循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句返回的结构集合的结构相同。

在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据，index_variable 中各元素的名称与游标查询语句选择列表中所制定的列名相同。

如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后才能通过游标 FOR 循环语句中的索引变量来访问这些列数据。

注：不要在程序中对游标进行人工操作；不要在程序中定义用于控制 FOR 循环的记录

• 打印出 80 部门的所有的员工的工资:salary: xxx

declare--定义游标cursor emp_sal_cursor is select salary,employee_id,hire_date from employees where department_id = 80;
beginfor c in emp_sal_cursor loopdbms_output.put_line('empid:'||c.employee_id||' salary:'||c.salary||' hire_date:'||c.hire_date);end loop;end;     

• 利用游标, 调整公司中员工的工资:

declarecursor emp_sal_cursor is select employee_id,salary from employees;--用于记录调整基数v_temp number(4,2);v_empid employees.employee_id%type;v_sal employees.salary%type;
beginopen emp_sal_cursor;fetch emp_sal_cursor into v_empid,v_sal;while emp_sal_cursor%found loopif v_sal < 5000 then v_temp :=0.05;elsif v_sal < 10000 then v_temp :=0.03;elsif v_sal < 15000 then v_temp :=0.02;else v_temp :=0.01;end if;update employeesset salary = salary * (1 + v_temp)where employee_id = v_empid;fetch emp_sal_cursor into v_empid,v_sal;end loop;close emp_sal_cursor     
end;

使用SQL中的 decode 函数update employees set salary = salary * (1 + (decode(trunc(salary/5000), 0, 0.05,1, 0.03,2, 0.02,0.01)))

使用游标的for循环：declarecursor emp_sal_cursor is select employee_id,salary from employees;--用于记录调整基数v_temp number(4,2);beginfor c in emp_sal_cursor loopif c.salary < 5000 then v_temp :=0.05;elsif c.salary < 10000 then v_temp :=0.03;elsif c.salary < 15000 then v_temp :=0.02;else v_temp :=0.01;end if;update employeesset salary = salary * (1 + v_temp)where employee_id = c.employee_id;end loop;     end;

1）带参数的游标

declare--定义游标cursor emp_sal_cursor(dept_id number, sal number) is select salary + 1000 sal, employee_id id from employees where department_id = dept_id and salary > sal;--定义基数变量temp number(4, 2);
begin--处理游标的循环操作for c in emp_sal_cursor(sal => 4000, dept_id => 80) loop--判断员工的工资, 执行 update 操作--dbms_output.put_line(c.id || ': ' || c.sal);if c.sal <= 5000 thentemp := 0.05;elsif c.sal <= 10000 thentemp := 0.03;   elsif c.sal <= 15000 thentemp := 0.02;elsetemp := 0.01;end if;dbms_output.put_line(c.sal || ': ' || c.id || ', ' || temp);--update employees set salary = salary * (1 + temp) where employee_id = c.id;end loop;
end;

当所声明的游标带有参数时，通过游标 FOR 循环语句为游标传递参数。

或者：

2）使用子查询

PL/SQL 还允许在游标 FOR 循环语句中使用子查询来实现游标的功能。

beginfor v_emp in (select last_name,salary from employees) loopdbms_output.put_line(v_emp.last_name||','||v_emp.salary);end loop;
end;

3、隐式游标

​ 显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由 ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为 SQL，这是由 ORACLE 系统定义的。

​ 对于隐式游标的操作，如定义、打开、取值及关闭操作，都由 ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条 SQL 语句所包含的数据。

• 格式调用为： SQL%

• 隐式游标属性

  • SQL%FOUND 布尔型属性,当最近一次读记录时成功返回，则值为 TRUE；
  • SQL%NOTFOUND 布尔型属性,与%FOUND 相反；
  • SQL %ROWCOUNT 数字型属性, 返回已从游标中读取得记录数；
  • SQL %ISOPEN 布尔型属性, 取值总是 FALSE。SQL 命令执行完毕立即关闭隐式游标

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

beginupdate employeesset salary = salary + 10where employee_id = 101;if sql%notfound then dbms_output.put_line('查无此人');end if;
end;PL/SQL procedure successfully completedbeginupdate employeesset salary = salary + 10where employee_id = 1001;if sql%notfound then dbms_output.put_line('查无此人');end if;
end;查无此人PL/SQL procedure successfully completed

4、NO_DATA_FOUND 和 %NOTFOUND 的区别

• SELECT … INTO 语句触发 NO_DATA_FOUND；

• 当一个显式游标的 WHERE 子句未找到时触发%NOTFOUND；

• 当 UPDATE 或 DELETE 语句的 WHERE 子句未找到时触发 SQL%NOTFOUND；

• 在提取循环中要用 %NOTFOUND 或%FOUND 来确定循环的退出条件，不要用 NO_DATA_FOUND。

五、异常错误处理

一个优秀的程序都应该能够正确处理各种出错情况，并尽可能从错误中恢复。

ORACLE 提供异常情况(EXCEPTION)和异常处理(EXCEPTION HANDLER)来实现错误处理。

1、异常处理概念

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

（1）有三种类型的异常错误：

• 预定义 ( Predefined )错误

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

• 非预定义 ( Predefined )错误

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

• 用户定义(User_define) 错误

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

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

EXCEPTIONWHEN first_exception THEN WHEN second_exception THEN WHEN OTHERS THEN 
END;

declarev_salary employees.salary%type;beginselect salary into v_salaryfrom employeeswhere employee_id > 100;dbms_output.put_line(v_salary);end;ORA-01422: exact fetch returns more than requested number of rows实际返回的行数超出请求的行数
ORA-06512: at line 5

declarev_salary employees.salary%type;beginselect salary into v_salaryfrom employeeswhere employee_id > 100;dbms_output.put_line(v_salary);exceptionwhen too_many_rows then dbms_output.put_line('输出的行数太多了');when others then dbms_output.put_line('出现其他类型的异常');  
end;输出的行数太多了PL/SQL procedure successfully completed

SQL> delete from employees2  where employee_id = 100;
delete from employees
where employee_id = 100ORA-02292: integrity constraint (SCOTT.DEPT_MGR_FK) violated - child record found
违反完整约束条件(SCOTT.DEPT_MGR_FK) - 已找到子记录

declaree_deleteid_exception exception;pragma exception_init(e_deleteid_exception,-2292);
begindelete from employees where employee_id = 100;
exceptionwhen e_deleteid_exception then dbms_output.put_line('违反完整性约束条件，故不可删除此用户');
end;  	 

declaree_too_high_sal exception;v_sal employees.salary%type;beginselect salary into v_sal from employees where employee_id = 100;if v_sal > 10000 thenraise e_too_high_sal;end if;exceptionwhen e_too_high_sal then dbms_output.put_line('工资太高了');  end;

declare--自定义异常                                   no_result exception;   
beginupdate employees set salary = salary + 100 where employee_id = 1001;--使用隐式游标, 抛出自定义异常if sql%notfound thenraise no_result;end if;  exception--处理程序抛出的异常when no_result thendbms_output.put_line('更新失败');
end;

declaree_too_high_sal exception;v_sal employees.salary%type;e_deleteid_exception exception;pragma exception_init(e_deleteid_exception,-2292); begindelete from employees where employee_id = 100;select salary into v_sal from employees where employee_id = 100;if v_sal > 10000 thenraise e_too_high_sal;end if;exceptionwhen e_too_high_sal then dbms_output.put_line('工资太高了');  when e_deleteid_exception then dbms_output.put_line('违反完整性约束条件，故不可删除此用户');when others then dbms_output.put_line('发生其他类型的异常');
end;先执行delete操作，所以结果如下：违反完整性约束条件，故不可删除此用户PL/SQL procedure successfully completed

declarev_sal employees.salary%type;beginselect salary into v_sal from employees where employee_id = 1001;dbms_output.put_line(v_sal);end;ORA-01403: no data found
ORA-06512: at line 5

declarev_sal employees.salary%type;beginselect salary into v_sal from employees where employee_id = 1001;dbms_output.put_line(v_sal);
exceptionwhen no_data_found then dbms_output.put_line('查无此人');
end;

declarev_sal employees.salary%type;beginselect salary into v_sal from employees where employee_id = 1001;if v_sal < 300 then update employees set salary = salary + 100 ;end if;
exceptionwhen no_data_found then dbms_output.put_line('查无此人');  when too_many_rows then dbms_output.put_line('输出的行数太多');  
end;

CREATE TABLE errors (errnum NUMBER(4), errmsg VARCHAR2(100));
DECLAREerr_msg VARCHAR2(100);
BEGIN/* 得到所有 ORACLE 错误信息 */FOR err_num IN -100 .. 0 LOOPerr_msg := SQLERRM(err_num);INSERT INTO errors VALUES(err_num, err_msg);END LOOP;
END;
DROP TABLE errors;

BEGININSERT INTO emp(empno, ename, hiredate, deptno)VALUES(2222, ‘Jerry’, SYSDATE, 20);DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');INSERT INTO emp(empno, ename, hiredate, deptno)VALUES(2222, ‘Jerry’, SYSDATE, 20);DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');
EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);
END

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 }<类型.变量的说明>BEGINFUNCTION_bodyEXCEPTION其它语句END;

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

create or replace function test_funreturn dateisv_date date;
beginselect sysdate into v_datefrom dual;dbms_output.put_line('我是函数哈');return v_date;end;declarev_date date;
beginv_date :=test_fun();dbms_output.put_line(v_date);
end;我是函数哈
17-DEC-20PL/SQL procedure successfully completed

create or replace function get_salary(dep_id employees.department_id%type,emp_count out number)return numberisv_sum number;beginselect sum(salary),count(*) into v_sum,emp_countfrom employeeswhere department_id = dep_id;return v_sum;exceptionwhen no_data_found thendbms_output.put_line('您需要的数据不存在'); when others then dbms_output.put_line(sqlcode || ',' || sqlerrm);end;         

argument_value1[,argument_value2 …]

argument => parameter [,…]

create or replace function get_salary(dep_id employees.department_id%type default 50,emp_count out number)return numberisv_sum number;beginselect sum(salary),count(*) into v_sum,emp_countfrom employeeswhere department_id = dep_id;return v_sum;exceptionwhen no_data_found thendbms_output.put_line('您需要的数据不存在'); when others then dbms_output.put_line(sqlcode || ',' || sqlerrm);end;         

declarev_num number;v_sum number;
beginv_sum :=get_salary(emp_count => v_num);dbms_output.put_line('50号部门是工资总和'||v_sum||',人数'||v_num);
end;50号部门是工资总和156400,人数45PL/SQL procedure successfully completed 

create or replace function hello_world
return varchar2
is 
beginreturn 'helloworld';
end; 函数调用：
begindbms_output.put_line(hello_world);
end;helloworldPL/SQL procedure successfully completed或者用select语句调用：
SQL> select hello_world from dual;HELLO_WORLD
--------------------------------------------------------------------------------
helloworld

create or replace function hello_world(v_logo varchar2)
return varchar2
is 
begindbms_output.put_line('人家是函数的啦，么么');return 'helloworld '||v_logo;
end;       Function created函数调用法一：
SQL> select hello_world('MM') from dual;HELLO_WORLD('MM')
--------------------------------------------------------------------------------
helloworld MM人家是函数的啦，么么函数调用法二:
begindbms_output.put_line(hello_world('MM'));
end;人家是函数的啦，么么
helloworld MMPL/SQL procedure successfully completed

create or replace function get_sysdatereturn dateisv_date date;
begin v_date := sysdate;return v_date;
end;函数调用法1：
SQL> select get_sysdate from dual;GET_SYSDATE
-----------
2020/12/17函数调用法2：
declarev_date date;
beginv_date := get_sysdate();dbms_output.put_line(v_date);
end;17-DEC-20PL/SQL procedure successfully completed

create or replace function add_param(v_num1 number,v_num2 number)return numberisv_sum number(10);
beginv_sum := v_num1+v_num2;return v_sum;
end;       函数调用法1：SQL> select add_param(2,4) from dual;ADD_PARAM(2,4)
--------------6SQL> select add_param(2.1,2.2) from dual;ADD_PARAM(2.1,2.2)
------------------4   函数调用法2：
begin dbms_output.put_line(add_param(3,4));
end;7PL/SQL procedure successfully completed

函数创建法1：
create or replace  function get_sal(dept_id number)return numberisv_sumsal number(10) :=0;cursor salary_cursor is select salary from employees where department_id = dept_id;beginfor c in salary_cursor loopv_sumsal :=v_sumsal + c.salary;end loop;return v_sumsal;end;函数创建法2：
create or replace  function get_sal(dept_id number)return numberisv_sumsal number(10) :=0;v_sal number;cursor salary_cursor is select salary from employees where department_id = dept_id;beginopen salary_cursor;loopfetch  salary_cursor into v_sal;exit when salary_cursor%notfound;v_sumsal :=v_sumsal + v_sal;end loop;close salary_cursor;return v_sumsal;end;函数调用法1：
SQL> select get_sal(80) from dual;GET_SAL(80)
-----------304500函数调用法2：
begindbms_output.put_line(get_sal(80));
end;函数调用法3：
declarev_deptid number(4) := 80;begin dbms_output.put_line(get_sal(v_deptid));end;

create or replace  function get_sal1(dept_id number,total_count out number)return numberisv_sumsal number(10) :=0;cursor salary_cursor is select salary from employees where department_id = dept_id;begintotal_count :=0;for c in salary_cursor loopv_sumsal :=v_sumsal + c.salary;total_count :=total_count + 1;end loop;return v_sumsal;end;函数调用：
declarev_num number(5) :=0;
begindbms_output.put_line(get_sal1(80,v_num));dbms_output.put_line(v_num);
end;    304500
34PL/SQL procedure successfully completed

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;

EXEC[UTE] Procedure_name( parameter1, parameter2…);

DROP PROCEDURE [user.]Procudure_name;

DROP FUNCTION [user.]Function_name;

create or replace procedure get_sal2(dept_id number,sumsal out number)iscursor salary_cursor is select salary from employees where department_id = dept_id;beginsumsal :=0;for c in salary_cursor loopsumsal :=sumsal + c.salary;end loop;dbms_output.put_line(sumsal);end;调用存储过程：
因为不需要传递变量，所以无法用select语句调用declarev_sal number(10) :=0;
beginget_sal2(80,v_sal);
end;

create or replace procedure add_sal(dept_id number,temp out number)
iscursor sal_cursor is select employee_id,salary,hire_date from employees where department_id = dept_id;v_i number(4,2) :=0;
begintemp :=0;for c in sal_cursor loop----纯数字的字符串有一个隐式转化，也可以比较大小if to_char(c.hire_date,'yyyy') < '1995' then  v_i := 0.05;elsif to_char(c.hire_date,'yyyy') < '1998' then v_i := 0.03;else v_i := 0.01;end if;----1、更新工资update employees set salary = salary * (1+v_i) where employee_id = c.employee_id;----2、付出的成本temp := temp + c.salary * v_i;                 end loop;dbms_output.put_line(temp);end;       注意：如果创建存储过程或者创建存储函数时报错，可以用show errors
SQL> show errors;
Errors for PROCEDURE SCOTT.ADD_SAL:
LINE/COL ERROR
-------- -------------------------------------------------------------------
6/8      PLS-00363: expression 'TEMP' cannot be used as an assignment target
6/8      PL/SQL: Statement ignored
17/12    PLS-00363: expression 'TEMP' cannot be used as an assignment target
17/12    PL/SQL: Statement ignored函数调用：
declarev_temp number(10):=0;
beginadd_sal(80,v_temp);
end;6121PL/SQL procedure successfully completed

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 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

create or replace trigger update_emp_trigger
afterupdate on employees
for each row
begindbms_output.put_line('helloworld');       
end;SQL> update employees set salary = salary + 100 where department_id = 80;
此时出现34条helloworld

create or replace trigger update_emp_trigger1
afterupdate on employees
--for each row
begindbms_output.put_line('helloworld');       
end;SQL> update employees set salary = salary + 100 where department_id = 80;helloworld34 rows updated

SQL> create table employees12  as3  select4  employee_id,salary,email from employees5  where department_id = 80;Table createdcreate or replace trigger update_emp_trigger2
afterinsert on employees1
--for each row
begindbms_output.put_line('helloworld');       
end;SQL> insert into employees12  values(1001,1000,'MM@mail.com');helloworld1 row inserted

create or replace trigger update_emp_trigger2
afterupdate on employees1
for each row
begindbms_output.put_line('old salary:'||:old.salary||','||'new salary:'||:new.salary);       
end;SQL> update employees1 set salary = salary + 100;

create table my_emp
as
select employee_id,salary from employeescreate table my_emp_back
as
select employee_id ,salary from employees
where 1=2create or replace trigger delete_emp_trigger
before
delete on my_emp
for each row
begininsert into my_emp_backvalues(:old.employee_id,:old.salary);
end;    SQL> delete from my_emp;105 rows deletedSQL> select * from my_emp_back;
此时有105条记录

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column [, column …]]}
ON [schema.] view_name
[FOR EACH ROW ]
[WHEN condition]
trigger_body;

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

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！