pl/sql基础知识学习笔记（三）

PLSQL 基础知识（三）——存储过程

一. 子程序的组成

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

子程序中包含声明部分，可执行部分，异常处理部分。

1 声明 ： 类型，游标，常量，变量和嵌套子程序的应用。 【局部的】

2 可执行部分 ： 赋值 ， 控制执行过程以及操纵oracle数据的语句

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

4 优点：模块化 可重用性 可维护性 安全性

二. 存储过程：

存储过程是执行某些操作的子程序，是执行特定任务的模块。

基本用法：

>1创建

CREATE [OR REPLACE] PROCEDURE procedure_name

[ (parameter_list) ]

{IS | AS}

[local declarations]

BEGIN

executable_statements

[EXCEPTION]

[exception_handlers]

END [procedure_name] ;

说明：

1. 存储过程参数只声明类型不声明大小

2.job 列有一个默认值CLERK ，调用时不提供该参数，则自动用默认值

3.不能直接写SQL语句，或复用游标来指定SQL语句

4.在存储过程中不能直接写select语句,可以使用select into

>2调用

存储过程创建完成后，通过授权，就可以被调用了

1） 用命令调用 【命令在SQL提示符下调用/在PL/SQL块中调用】

2） 执行存储过程

命令在SQL提示符下调用:

EXEC[UTE] procedure_name ( parameters_list) ;

参数传递的方式（3种）：

1. 按位置传递参数

eg：EXEC example('aaa',18);

2. 按名称传递参数

eg： EXCEC example(name=>'aaa',age=>18);

3. 混合方式传递参数

在PL/SQL块中调用

在PL/SQL块中调用存储过程时不需写EXEC ，直接写存储过程名称即可。

BEGIN

--按位置传递参数

example('aaa',18);

--按名称传递参数

example(name=>'aaa',age=>18);

END;

>3 存储过程的参数模式

参数传递方式有3种 ； IN(输入参数) , OUT（输出参数） , 和 IN OUT （输入或输出参数）

IN : 只能将实参传给形参，进入函数内部，但只读不能写，函数返回时实参值不变

OUT： 忽略调用时的实参值（该形参的初始值总是NULL），但内部可读或写，函数返回时形

参的值会赋予给实参

IN OUT：调用时，实参的值总是传递给形参，结束时，形参的值传递给实参

默认情况下为 IN

paramerter_name [ IN | OUT | IN OUT] datatype [ { := | DEFAULT } expression ]

可在参数列表中为IN 赋予一个默认值，但不能为OUT / IN OUT 参数赋予默认值

返回调用环境之前，必须给OUT /IN OUT 参数赋值

>4 存储过程的访问权限

存储过程创建之后，只有创建该存储过程的用户和管理员才有权使用它。其他用户如需使用，需要得到该存储过程的EXECUTE权限

GRANT EXECUTE ON xample TO username; -- 授权

REVOKE EXECUTE ON example FROM userrname --撤销授权

>5删除存储过程

DROP PROCEDURE procedure_name ;

三. 存储过程的调试与跟踪

>1 使用SQL*PLUS调试

SHOW ERRORS PROCEDURE example；

>2 使用PL/SQL Developer工具调试

获得DEBUG CONNECT SESSION 权限

进入测试窗口，编写调用存储过程的代码

F9静入调试状态

四. 存储过程规范

存储过程中不可以直接使用DDL语句，可以通过动态SQL实现。不建议频繁使用DDL

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

存储过程中变量在引用表字段的时候，需使用%TYPE ,%RAWTYPE

必须在存储过程中做出异常捕获，并将异常信息通过0s_Msg 变量输出

- 19999 ~ -1de异常为oracle的异常代码

存储过程必须包含两个输出参数，即on_Flag(number) 和 os_Msg(varchar2) , 分别用于标识过

程的执行状态以及过程提示信息

on_Flag三种状态：

0 ： 执行成功但无提示信息

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

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

“WHEN OTHERS” 必须放置在异常处理代码的最后作为默认处理器没有显示处理的异常