Oracle 基础 <1> --存储过程
一、子程序 子程序是已命名的PL/SQL块,它们存储在数据库中,可以Wie它们指定参数,可以从任何数据库客户端和应用程序中调用它们。子程序包括存储过程和函数。 子程序包括: 1、声明部分:声明部分包括类型、游标、常量、变量、异常和嵌套子程序的声明。这些项都是局部的,在退出后就不复存在。 2、可执行部分:可执行部分包括赋值、控制执行过程以及操纵ORacle数据的语句。 3、异常处理部分: 异常处理部分包括异常处理程序,负责处理执行存储过程中出现的异常。
子程序的优点: 1、模块化:通过子程序,可以将程序分解为可管理的、明确的逻辑模块。 2、可重用性:子程序在创建并执行后,就可以再任意数目的应用程序中使用。 3、可维护性:子程序可以简化维护操作,因为如果一个子程序受到影响,则只需修改该子程序的定义。 4、安全性:用户可以设置权限,使得访问数据的唯一方式就是通过用户提供的存储过程和函数。不仅可以让数据更安全,而且可以保证它的正确性。 |
一:存储过程的优点
(1.模块化:通过子程序,可以对用来实现一种业务逻辑,一个业务功能。
(2.可重用性:存储过程被创建之后,就可以在任意数目的应用程序中使用。
(3.可维护性:可以简化维护操作
(4.安全性:用户可以设置权限。
缺点:
1.移植问题
2.重新编译问题
3.需求变更问题
二:存储过程的用法
1.存储过程的创建
创建语法:
CREATE [OR REPLACE] PROCEDURE procedure_name --名称
[(paraameter_list)] --要用的参数
{IS/AS}
[local_declarations]--局部声明的变量
BEGIN
executable_statements;--执行的内容
[EXCEPTION]
[exception_handlers] --异常处理部分
END [procedure_name] --名称
说明:
procedure_name:为存储过程的名字;
paraameter_list:参数列表,参数中可以使用in和out表示输入和输出参数。可选
AS表示其他变量声明,IS表示显示游标声明
local_declarations:局部声明,可选
executable_statements:可执行语句
exception_handlers:异常处理语句,可选
OR REPLACE:可选。如果不包含,创建存储过程如果存在会报错,包含存在会替换。
2.存储过程的调用
调用语法:
execute procedure_name (parameters_list);
说明:
execute:执行命令,可以缩写为exec。
procedure_name:存储过程的名称。
parameters_list:存储过程的参数列表。
1)命令行方式:2)PLSQL方式: --PL/SQL方式,不需要使用exec
3、存储过程的参数模式:
(1)in:输入
(2)out:输出
(3)in out:输入\输出
定义存储过程参数语法:
parameter_name [IN|OUT|IN OUT] dateType [{:= | default} expression]
注意:
1)参数IN模式是默认模式。如果未指定参数模式,则默认为IN。对于OUT和IN OUT参数,必须明确指定。
2)可以再参数列表中为IN参数指定默认值,OUT和IN OUT不可用。
4、存储过程的授权
语法:
*grant execute on procedure_name(存储过程名称) to user(用户) --赋予用户 执行权
*grant execute on procedure_name(存储过程名称) to user(用户) with grant option --该用户也可以有把该权限授予其他用户
5.存储过程的删除
语法:
dropp procedure 名称;
三.程序块的跟踪和调试
1、在SQL*plus下调试
语法:
show errors (查看的程序块的类型procedure) (程序块的名称); --查看某个程序块是否存在编译错误
2、在PL/SQL Developer 工具调试
F9:开始调试
Ctrl+R:执行
Ctrl+N:单步进入
Ctrl+O:单步跳过
Ctrl+T:单步跳出
四.程序过程和游标的用法
--带游标的存储过程
CREATE OR REPLACE PROCEDURE PRO_EMP_IN (EMP_CUR SYS_REFCURSOR) AS
V_NAME EMP.ENAME%TYPE;
V_SAL EMP.SAL%TYPE;
BEGIN
LOOP
FETCH EMP_CUR
INTO V_NAME, V_SAL;
DBMS_OUTPUT.PUT_LINE('name:' || V_NAME || ' sal:' || V_SAL);
EXIT WHEN EMP_CUR%NOTFOUND;
END LOOP;
END;
--调用带游标的存储过程:
DECLARE
EMP_CUR SYS_REFCURSOR;
BEGIN
OPEN EMP_CUR FOR
SELECT ENAME, SAL FROM EMP;
PRO_EMP_IN(EMP_CUR => EMP_CUR);
CLOSE EMP_CUR;
END;
--调用带输出类型的游标的存储过程
DECLARE
V_NAME EMP.ENAME%TYPE;
V_SAL EMP.SAL%TYPE;
EMP_CUR SYS_REFCURSOR; --声明系统游标类型
BEGIN
PRO_EMP(EMP_CUR => EMP_CUR);
LOOP
FETCH EMP_CUR INTO V_NAME, V_SAL;
DBMS_OUTPUT.PUT_LINE('name:' || V_NAME || ' sal:' || V_SAL);
EXIT WHEN EMP_CUR%NOTFOUND;
END LOOP;
CLOSE EMP_CUR;
END;
五.程序过程和事务的用法
(1)自主事务:是由另一个事务处理(主事务处理)启动的独立事务处理。
自主事务:是由零一个事务启动的独立事务处理。自主事务处理可以暂停主事务处理,也就是处理自己存储过程内部的事务,当自主事务处理完之后会恢复主事务处理。
PRAGMA AUTONOMOUS_TRANSACTION; --定义为自主事务,不受其他事务提交,回滚的影响
总结自主事务:
1、自主事务处理结果的变化不依赖于主事务处理的状态或最终配置。
2、自主事务处理提交或回滚时,不影响主事务处理的结果。
3、自主事务提交一旦提交,该自主事务处理结果的变化对于其他事务处理就是课件的。这意味着,用于可以访问已更新的信息,无需等待主事务处理提交。
4、自主事务处理可以启动其它自主事务处理。