Oracle PL/SQL存储过程学习分享

PL/SQL(Procedural Language/SQL，过程语言/SQL)

　　是结合了Oracel过程语言和结构化查询语言(SQL)的一种扩展语言。

优点：

（1）PL/SQL具有编程语言的特点，它能把一组SQL语句放到一个模块中，使其更具模块化种序的特点。

（2）PL/SQL可以采用过程性语言控制程序的结构。

（3）PL/SQL有自动处理的异常处理机制。

（4）PL/SQL程序块具有更好的可移植性，可移植到另一个Oracle数据库中。

（5）PL/SQL程序减少了网络的交互，有助于提高程序性能。

PL/SQL引擎用来编译和执行PL/SQL块或子程序，该引擎驻留在Oracle服务器中，仅执行过程语句，面将SQL语句发送给Oracle服务器上的SQL语句执行器。

PL/SQL是一种块结构的语言，它将一组语句放在一个块中。匿名块是一个未在数据库中命名的PL/SQL块，在运行时被传递到PL/SQL引擎以便执行。在PL/SQL块中可以执行DML语句、TCL语句、SQL函数等。但PL/SQL块中不可以执行

DDL语句，该种语句只能由动态SQL来执行。

PL/SQL块由3部分组成：声明部分、执行部分、异常处理部分

[declare]

--声明部分：在此声明PL/SQL用到的变量、类型及游标，以及局部的存储过程和函数

begin

--执行部分：过程及SQL语句，即程序的主要部分，不可省略

[exception]

--异常处理部分：错误处理

end;

 

关系运算符

运算符	意义
=	等于
<>、!=、~=、^=	不等于
<	小于
>	大于
<=	小于等于
>=	大于等于

一般运算符

运算符	意义
+	加号
-	减号
*	乘号
/	除号
:=	赋值符号
=>	关系符号
..	范围运算符号
||	字符连接符号

逻辑运算符

运算符	意义
is null	是空值
between and	介于两者之间
in	在一列值中间
and	逻辑与
or	逻辑或
not	取反，如is not null,not in

 

变量和常量的声明：

变量：variable_name data_type[(size)][:= init_value];

--variable_name表示变量名称

--data_type表示变量的SQL或PL/SQL数据类型

--size指定变量的范围。

--init_value指定变量的初始值。

在声明变量时，可以用弹框的方式，给变量赋值。如：v_ename varchar2(10) :=&请输入名字;

--这里的” & ”符号就是关键字，后面的“当输入名字”是提示信息。默认值输入框中输入的是数字，如果要输入字符

串，则需要给要输入的数据加上双“’”单引号。

 

常量：variable_name CONSTANT data_type := value;

例，给常量和变量声明赋值：

declare

v_ename varchar2(20);

v_no number;

v_rate number(7,2);

c_rate_incr constant number(7,2) := 1.10;

begin

--方法一：通过select into 给变量赋值,查询结果只返回一条数据并赋值到变量中保存，返回多条或零条数据则报错。

select ename,sal*c_rate_incr into v_ename, v_rate

 　　from employee

--方法二：通过赋值操作符“:=”给变量赋值,在赋值时可以使用序列

v_ename := ‘SOCTT’;

v_no := myseq.nextval;

end;

 

 

PL/SQL中标识符定义的要求和限制：

1）标识符名称不能超过30个字符。

2）第一个字符必须是字母。

3）不区分大小写。

4）不能用”-”减号。

5）不能用SQL的保留字。

 

　　命名方法：

标识符	命名规则	例子
程序变量	v_name	v_student_name
程序常量	c_name	c_company_name
游标变量	cursor_name	cursor_emp
异常标志	e_name	e_too_many
表类型	name_table_type	emp_record_type
表	name_table	emp_table
记录类型	name_record	emp_record
绑定变量	g_name	g_year_sal

 

PL/SQL中编码规则：

（1）利用缩进排列展现逻辑结构。保留字后出现的列开始缩进三个空格，如declare下面声明变量行。

（2）利用大小写增强可读性。保留字统一大写; 应用程序专用名称或标识符统一小写。

（3）格式化单独语句。每行至多写一条语句; 声明语句中尽量保持声明的数据类型近变量名，而不是与数据类型对齐。

（4）格式化SQL语句。右对齐DML语句的子句中的保留字。

 

注释

1）使用双“-”减号加注释，只在一行有效，如：

　　v_sal NUMBER(12,2); --人员的工资变量

2）使用“/* */”来加一行或多行注释，建议使用如下方式

/*

||在注释首行只放斜线星号，标志注释开始

||，然后注释块每一行以双垂直线开头，突出后面的注释内容。可以不写，但为了可读性

*/

 

PL/SQL数据类型

1.标量数据类型，包含单个值，没有内部组件。包括数字、字符、布尔值和日期时间值。

类型	子类	说明	范围
CHAR	Character Nchar	定长字符串 民族语言字符集	0~32767 可选，默认为1
VARCHAR2	Varchar String NVARCHAR2	可变字符串 民族语言字符集	0~32767
BINARY_INTEGER		带符号整数，为整数计算优化性能
NUMBER(p,s)	Dec Double Precision Integer Int Numeric Real Small int	小数，NUMBER的子类型 高精度实数 整数，NUMBER的子类型 整数，NUMBER的子类型 与NUMBER等价 与NUMBER等价 整数，比Integer小
LONG		可变长度字符串	0~2147483647
DATE		日期型	公元前4712年1月1日至 公元后4712年12月31日
BOOLEAN		布尔型	TRUE,FALSE,NULL

 

2.LOB(Large OBject)数据类型,用于存储大的数据对象的类型，主要支持BFILE、BLOB、CLOB、NCLOB类型。

3.属性类型，属性用于引用变量或数据库列的数据类型，以及表示表中一行的记录类型。

1）%TYPE

　　定义一个变量，其数据类型与已经定义的某个数据变量（尤其是表的一列）的数据类型一致。

2）%ROWTYPE

　　返回一个记录类型，其数据类型和数据库表的数据结构一致。

例，根据员工编号查询员工信息：

DECLARE

v_empno employee.empno%TYPE := 7788;

v_rec employee%ROWTYPE;

BEGIN

SELECT * INTO v_rec FROM employee WHERE empno=v_empno;

DBMS_OUTPUT.PUT_LINE(‘姓名:’||v_rec.ename||’工资:’||v_rec.sal);--输出一行,只在PL/SQL块中

END;

 

PL/SQL控制语句

1.条件控制，用于根据条件执行一系列语句。条件控制包括IF语句和CASE语句。

　　IF语句语法：

IF <布尔表达式> THEN --if

   --PL/SQL和SQL语句

END IF;

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

IF <布尔表达式> THEN --if-else

--PL/SQL和SQL语句

ELSE

--其他语句

END IF;

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

IF <布尔表达式> THEN --if-else if-else

--PL/SQL和SQL语句

ELSIF <其他表达式> THEN -- 这里是elsif 而不是elseif，注意!!!

--其他语句

ELSIF <其他表达式> THEN

--其他语句

ELSE

--其他语句

END IF;

CASE语句语法：

--格式一

　　CASE 条件表达式

WHEN 条件表达式结果1 THEN 语句段1

WHEN 条件表达式结果2 THEN 语句段2

.....

WHEN 条件表达式结果n THEN 语句段n

[ELSE 语句段]

END CASE;

--格式二

CASE

WHEN 条件表达式1 THEN 语句段1

WHEN 条件表达式2 THEN 语句段2

.....

WHEN 条件表达式n THEN 语句段n

[ELSE 语句段]

END CASE;

2.循环控制，用于重复执行的系列语句。包括LOOP和EXIT语句，使用EXIT语句可以立即退出循环; 使用EXIT WHEN

语句可以根据条件结束循环。有3种类型循环，包括LOOP循环、WHILE循环、FOR循环。

LOOP循环语法：

LOOP

要执行的语句;

EXIT WHEN <条件语句>  --条件满足，退出循环语句

END LOOP;

WHILE循环语法：

WHILE <布尔表达式> LOOP

要执行的语句;

END LOOP;

FOR循环语法：

FOR 循环计数器 IN [REVERSE] 下限 .... 上限 LOOP

要执行的语句;

END LOOP; 

3.顺序控制，用于按顺序执行语句。顺序控制包括NULL语句和GOTO语句。GOTO语句不推荐使。

NULL语句：是一个可执行语句，相当于一个占位符或不执行任何操作的空语句，可以便某些语句变得有意义，提高程

序的可读性，保证其他语句结构的完整性和正确性。

例，显示变量v_counter的值，如果该变量小于10,则增加10并显示该变量改变后的值。

DECLARE

v_counter NUMBER := 5;

BEGIN

DBMS_OUTPUT.PUT.LINE(‘v_counter的当前值为：’||v_counter);

IF v_counter>=10 THEN

NULL; --为了使语法变得有意义，去掉NULL会报语法错误

ELSE

v_counter := v_counter+10;

DBMS_OUTPUT.PUT.LINE(‘v_counter的改变后值为：’||v_counter);

END IF;

END;

 

异常处理

在程序运行时出现的错误叫异常。发生异常后，语句将停止执行，PL/SQL引擎立即将控制权转到PL/SQL块的异常处理。

PL/SQL预定义异常

异常	说明
ACCESS_INTO_NULL	在未初始化对象时出现
CASE_NOT_FOUND	CASE语句中的选项与用户输入的数据不匹配时出现
COLLECTION_IS_NULL	给尚未初始化的表或数组赋值时出现
CORSOR_ALREADY_OPEN	在用户试图重新打开已经打开的游标时出现。在重新打开游标前必须先将其关闭
DUP_VAL_ON_INDEX	在用户试图将重复的值存储在使用唯一索引的数据库列中时出现
INVALID_CURSOR	在执行非法游标运算（如打开一个尚未打开的游标）时出现
INVALID_NUMBER	在将字符串转换为数字时出现
LOGIN_DENIED	在输入的用户名或密码无效时出现
NO_DATA_FOUND	在表中不存在请求的行时出现。此外，当程序引用已经删除的元素时，也会引发NO_DATA_FOUND异常
STORAGE_ERROR	在内存损环或PL/SQL耗尽内存时出现
TOO_MANY_ROWS	在执行SELECT INTO语句后返回多行时出现
VALUE_ERROR	产生大小限制错误时出现。例如，变量中的列值超出变量的大小
ZERO_DIVIDE	以零作为除数时出现

异常处理语法：

BEGIN

sequence_of_statements;

EXCEPTION

WHEN <exception_name1> THEN

sequence_of_statements;

WHEN <exception_name2> THEN

sequence_of_statements;

WHEN OTHERS THEN --这里的OTHERS处理程序除之前异常类型外的所有异常。PL/SQL块只能有一个OTHERS

sequence_of_statements;

END;

--可以使用函数SQLCODE和SQLERRM来返回错误代码和错误文本信息。

用户自定义异常：

步聚如下

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

<异常情况> EXCEPTION;

（2）抛出异常情况：

RAISE <异常情况>;

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

例，查询编号为7788的雇员的福利补助comm列。

DECLARE

v_comm employee.comm%TYPE;

e_comm_is_null EXCEPTION; --定义异常类型变量

BEGIN

SELECT comm INTO v_comm FROM employee WHERE empno=7788;

IF v_comm IS NULL THEN

RAISE e_comm_is_null;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE(‘雇员不存在！错误为：’||SQLCODE||SQLERRM);

WHEN E_COM_IS_NULL THEN

DBMS_OUTPUT.PUT_LINE(‘该雇员无补助！’);

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(‘出现其他异常’);

END;

RAISE_APPLICATION_ERROR存储过程可以重新定义异常错误消息，为应用程序提供一种与Oracle交互的方法。

　　语法：

　　　　RAISE_APPLICATION_ERROR(error_number, error_message);

--error_number表示用户为指定的编号。该编号必须是-20999~-20000之间的负整数。

--error_message表示用户为异常指定的消息文本。消息长度可达2048字节，是与error_number相关的。

例:

DECLARE

......

BEGIN

......

IF v_com IS NULL THEN

RAISE_APPLICATION_ERROR(-20001,’该雇员无补助’);

END IF;

END;

 

显示游标

Oracle会在内在中分配一个缓冲区，将执行结果放在这个缓冲区，而游标是指向该区的一个指针。游标为应用程序提供了一种对多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程式。

分类：

1）静态游标，是在编译时知道明确的SELECT语句的游标。静态游标分为隐式游标、显式游标。

2）动态游标

显式游标的使用步聚：

1）声明游标

CURSOR cursor_name [(parameter [,paramter]....)]

[RETURN return_type] IS select_statements;

--cursor_name指游标的名称。

--parameter用于为游标指定输入参数。在指定数据类型时，不能使用长度约束。

--return_type用于定义游标提取的行的类型。

--select_statement 指游标定义的查询语句。

2）打开游标

　　OPEN cursor_name[ (parameters) ];

3）提取游标

　　FETCH cursor_name INTO variables;

　　--variables是变量名

4）关闭游标

　　CLOSE cursor_name;

例：

 

DECLARE
name employee.ename%TYPE;
sal employee.sal%TYPE; --定义两个变量来存放ename和sal的内容
CURSOR emp_cursor  --声明游标
IS SELECT ename,sal FROM employee;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO name, sal;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘第’||emp_cursor%ROWCOUNT||’个雇员：’||name||sal);
END LOOP;
CLOSE emp_cursor;
END;

 

 

 

显式游标属性

1）%FOUND：只有在DML语句影响一行或多行时，%FOUND属性才返回TRUE;

2）%NOTFOUND：%NOTFOUND属性与%FOUND属性的作用正好相反。如果DML语句没有影响任何行，则%NOTFOUND属性返回TRUE;

3）%ROWCOUNT：%ROWCOUNT属性返回DML语句影响的行数。如果DML语句没有影响任何行，则%ROWCOUNT属性将返回0;

4）%ISOPEN：%ISOPEN属性返回游标是否已打开。

使用显式游标删除或更新

　　使用游标时，如果处理过程中需要删除或更新行，在定义游标时必须使用SELECT....FOR UPDATE语句; 而在执行DELETE和UPDATE时使用WHERE CURRENT OF子句指定游标的当前行。

　　声明更新游标语法：

CURSOR cursor_name IS 

select_statement FOR UPDATE [OF culumns];

--SELECT...  FOR UPDATE[OF columns]为更新查询，锁定选择的行。[OF columns]可以不是SELECT后的列

--（1）当选择单表更新查询时，可以受省略OF子句;

--（2）当选择多个表更新查询时，被锁定的行来源于OF 子句后声明的列所在的表中的行。

更新执行行语法：

UPDATE table_name

SET column_name= column_value

WHERE CURRENT OF cursor_name;

　　--多表查询更新时，更新表为锁定列所在的表。

　　例: 　　

 

DECLARE
CURSOR emp_cursor IS SELECT ename, sal FROM employee e INNER JOIN dept d ON 
e.deptno= d.deptno
FOR UPDATE OF SAL;
....
UPDATE employee SET sal=sal+100 WHERE CURRENT OF emp_cursor;

 

 

　　使用循环游标简化游标的读取，循环游标隐式打开游标，自动从活动集获取行，在处理实所有行时自动关闭游标。

　　语法：

FOR record_index IN cursor_name
LOOP
executable_statements
END LOOP;

 

　　--record_index是PL/SQL声明的记录变量。此变量的属性声明为%ROWTYPE类型作用域在循环之内。

循环游标的特性：

1）在游标中提取了所有记录之后自动终止。

2）提取和处理游标中的每一条记录。

3）如果在提取记录之后%NOTFOUND属性返回TRUE，则终止循环。

4）如果未返回行，则不进入循环。

例，显示雇员表中所有雇员的姓名和薪水：

 

DECLARE
CURSOR emp_cursor IS 
SELECT ename, sal FROM  employee;
BEGIN
FOR emp_record IN emp_cursor 
LOOP
DBMS_OUTPUT.PUT_LINE(‘第’||emp_cursor%ROWCOUNT||’个雇员：’||emp_record.ename||
emp_record.sal);
END LOOP;
END;

 

 

NOT_DATA_FOUND和%NOTFOUND的区别

1）SELECT ··· INTO语句返回0条和多条记录时触发NO_DATA_FOUND。

2）当UPDATE或DELETE语句的WHERE 子句未找到时，触发%NOTFOUND。

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

 

存储过程

子程序

子程序是已命名的PL/SQL块，它们存储在数据库中，包括存储过程和函数。使用存储过程执行操作，使用函数执行操作并返回值。

1.声明部分，包括类型、游标、常量、异常和嵌套子程序的使用。退出子程序后雩不复存在。

2.可执行部分，包括赋值、控制执行过程以及操纵Oracle数据的语句。

3.异常处理部分，包括异常处理程序，负责处理执行存储过程中出现的异常。

子程序的优点：

1）模块化：通过子种序，可以将程序分解为可管理的、明确的逻辑模块。

2）可重用性：子程序在创建并执行后，就可以在任意数目的应用程序中使用。

3）可维护性：子程序可以简化维护操作，因为如果一个子程序受到影响，则只需要修改该子程序的定义。

4）安全性：用户可以设置权限，使得访问数据的唯一方式就是用户提供的存储过程。这样也可以保证正确性。

存储过程：

　　存储过程是执行某些程序操作的子程序，是执行特定任务的模块。从根本上来讲，存储过程就是命名后的PL/SQL块。

　　创建存储过程：

 

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_list)]
[IS|AS]
[local_declarations]
BEGIN
execetable_statements
[EXCEPTION]
[exception_handlers]
END [procedure_name];

 

 

--procedure_name：存储过程的名称。

--parameter_list：参数列表，可选。

--local_declarations：局部声明，可选。

--executable_statements：可执行语句。

--excetption_handlers：异常处理程序，可选。

--OR REPLACE：可选。如果包含OR REPLACE语句，当系统中有这个存储过程时，将覆盖。如果不包含，当系统中有这个存储过程时，将报异常。

--声明时，数据类型不能带大小。如NUMBER(4)，只能写成NUMBER。

--声明时可以给定默认值，如：job VARCHAR2 DEFAULT ‘CLEAK’ 如果调用时不揭定该参数，自动默认值。

　　调用存储过程（存储过程通过授权，才可以调用）

1.用命令调用

1）执行：

　　EXEC[UTE] procedure_name (parameter_list);

　　--EXECUTE：执行命令，可以缩写为EXEC

2）参数的传递方式

按位置传递，例：EXEC add_employee(1111,’MARY’,2000,’MANAGER’,10);

按名称传递，即在调用时按名称对应。名称的对应关系是最重要的，次序不重要。

例：EXEC add_employee(dno=>10,name=>’MARY’,salary=>2000,job=>’MANAGER’);

混合方法传递，

例：EXEC add_employee(1113,dno=>10,name=>’MARY’,salary=>200,job=>’MANAGER);

其中雇员编号为1113,后面如果有一个是按照名称传递，则之后的都要以名称传递。

　　　　2.PL/SQL块中使用

BEGIN
　add_employee(2111,’MARY’,2000,’MANAGER’,10);
END;

 

　　　　--在PL/SQL块中调用存储过程时不需要写EXEC，直接写存储过程名称即可，EXEC是命令行中调用存储过程

　　　　的命令。

　　　　3.存储过程的参数模式

在存储过程中参数传递的模式有3种：IN、OUT、IN OUT，即输入 、输出、输入/输出参数。

IN模式只能将实参传递给形参，进入函数内部，函数返回时，实参值不变。结论参数传递 模式。

OUT模式会忽略调用时的实参值，在函数内部可写，函数返回时实参的值改变。

IN OUT模式具有前再种模式的特性。即调用时，实参的值总是传递给形参;结束时，形参的值传递给实参

可以在参数列表中为IN参数赋默认值。但是OUT和INT OUT参数不可以赋默认值。

如：

 

CREATE OR REPLACE PROCEDURE QueryEmp
(
v_empno IN employee.empno%TYPE DEFAULT 1000, --默认编号为1000
v_ename OUT employee.ename%TYPE,
v_sal IN OUT employee.sal%TYPE
)
AS
......

 

 

 

　　　　4.存储过程的访问权限。

如果非创建该存储过程的用户想要访问该存储过程，则需要得到存储过程的EXECUTE权限。

--授权

GRANT EXECUTE ON add_employee TO A_oe;

--撤销

REVOKE EXECUTE ON add_employee FROM A_oe;

5.删除存储过程

DROP PROCEDURE procedure_name;

 

存储过程的调试与跟踪

1.在SQL*Plus下调试

如: SQL>SET SERVEROUTPUT ON;

SQL>--调用add_employee存储过程

SQL>SHOW ERRORS PROCEDURE add_employee;

2.用PL/SQL Developer工具调试

1）获得DEBUG CONNECT SESSION权限

GRANT DEBUG CONNECT SESSION TO A_hr;

2）打开一个测试窗口，在里面编写存储过程代码。

3）按“F9”键进入调试状态。或者测试窗口左上角的黄色齿轮按钮。

4）在开始调试按钮右边第二个按钮是单步进入按钮。

 

存储过程规范

1.存储过程不可以直接使用DDL语句。可以通手动态SQL实现，最好不要使用。

2.存储过程必须有相应的出错处理功能。

3.存储过程中变量在引用表字段的时候，需使用%TYPE和%ROWTYPE类型。

4.存储过程必须包含两个输出参数，即on_Flag(number)和 os_Msg(varchar2)，分别用于标识过程的执行状态及过程提示信息。

其中on_Flag有三种取值情况：

0  表示过程执行成功但无提示信息;

大于0  表示过程执行成功但有提示信息;

小于1  表示过程执行失败且有提示信息;

5.必须在存储过程中做异常捕获，并将异常信息通过os_Msg变量输出。

6.-1999~-1的异常为Oracle定义的异常代码。

7.“WHEN OTHERS”必须放贯在异常处理代码的最后作为默认处理器处理没有显式处理的异常。

　　例：

 

CREATE OR REPLACE PROCEDURE add_employee(
....
on_Flag OUT NUMBER, --执行状态
os_Msg OUT VARCHAR2
)

IS

BEGIN
INSERT INTO .....
on_Flag:=1;
os_Msg:=’添加成功’;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
on_Flag=-1;
os_Msg=’该雇员已存在’;
WHEN OTHERS THEN
on_Flag=-2;
os_Msg=’其他错误，与管理员联系。’;
END;