Oracle存储过程基础

函数

定义：与过程相似，遵循了相同的规则

参数传递：只能带有in参数，不能使用out, in out 函数的主要特性是必须返回一个值

语法

CREATE [OR REPLACE] FUNCTION function_name (参数) RETURN datatype IS | AS [PRAGMA AUTONOMOUS_TRANACTION;] --声明自主事务处理。 [本地变量声明] BEGIN 执行语句部分 [EXCEPTION] 错误处理部分 END[name];
过程

过程

命了名的PL/SQL块，可以被赋予参数，存储在数据库中，然后由另 一个应用或者PL/SQL例程调用

语法

CREATE [OR REPLACE] PROCEDURE procedure_name (参数) IS | AS [PRAGMA AUTONOMOUS_TRANACTION;] --声明自主事务处理 [本地变量声明] BEGIN 执行语句部分 [EXCEPTION] 错误处理部分 END[name];
Oracle存储过程基础
目录
PL/SQL基础 异常 游标
过程、函数和包
PL/SQL基础




DECLARE 声明变量、常量的程序块 声明部分不能是oracle的保留字 BEGIN 执行语句部分的程序块，至少有一句执行语句。比如 null; EXCEPTION 异常处理部分的程序块，如果声明和执行部分出现错误， oracle停止这些块的处理，跳转到异常块，是一种回退机 制 END; /


不能改变 必须在声明的时候初始化常量 必须在数据类型的左边规定constant 编译错误情况：
• 没有初始化 • 执行过程中改变常量
PL/SQL基础-声明部分

为常量变量赋值 := 声明部分可以指定默认值 default expression

作用域
只在声明之后的begin end块内有效


PL/SQL基础-逻辑比较
控制语句:

CASE 语句 二
CASE WHEN 条件1 THEN …; ELSE …; END CASE;


PL/SQL基础-逻辑比较
循环:
无条件循环
Declare l_Loops Number := 0; Begin Loop If l_Loops > 4 Then Exit; End If; Dbms_Output.Put_Line('Looped '|| l_Loops || 'times'); l_Loops := l_Loops + 1; End Loop; End;
• 弱类型（非限制）REF CURSOR，不规定返回类型， 可以获取任何结果集


TYPE ref_cursor_name IS REF CURSOR [RETURN return_type] PACKAGE PKG_GLOBAL_CONFIG
游标
单独select

使用INTO获取值，只能返回一行 Declare l_Empno Emp.Empno%Type; Begin Select Empno Into l_Empno From Emp Where Rownum = 1; Dbms_Output.Put_Line(l_Empno); End;

使用位置表示
• exec insert_into_t (102, 202);

使用混合表示
• exec default_values(‘Tom’, p3=>’Joel’);
过程
例子

使用scott.emp表，编写搜索过程，输入empno, 返回ename , sal 参数：一个in, 两个out 参数类型：in number, out emp.ename%type , out emp.sal%type
分析

过程



例1． --节选自在线代码modetest.sql REM 作者： Scott Urman. CREATE OR REPLACE PROCEDURE ModeTest ( p_InParameter IN NUMBER, p_OutParameter OUT NUMBER, p_InOutParameter IN OUT NUMBER) IS v_LocalVariable NUMBER; BEGIN /* 分配 p_InParameter 给 v_LocalVariable. */ v_LocalVariable := p_InParameter; -- Legal /* 分配 7 给 p_InParameter. 这是非法的,因为声明是IN */ p_InParameter := 7; -- Illegal /* 分配 7 给 p_InParameter. 这是合法的,因为声明是OUT */ p_OutParameter := 7; -- Legal /* 分配 p_OutParameter 给 v_LocalVariable.这是非法的,因为声明是IN */ v_LocalVariable := p_outParameter; -- Illegal /* 分配 p_InOutParameter 给 v_LocalVariable. 这是合法的,因为声明是IN OUT */ v_LocalVariable := p_InOutParameter; -- Legal /*分配 7 给 p_InOutParameter. 这是合法的,因为声明是IN OUT */ p_InOutParameter := 7; -- Legal END ModeTest; /


IN：参数通过调用者传入，只能由过程读取， 不能改变 OUT：参数有过程写入 。用于过程需要向调用 者返回多条信息的时候。不能是具有默认值的 变量，也不能是常量，必须向OUT参数传递返 回值。 IN OUT 具有两者的特性，可以读取和写入。
过程
参数传递方法

使用名称表示
• exec insert_into_t (p2=> 101, p1=>201);
PL/SQL基础-逻辑比较

循环: FOR循环 Declare Begin For i In 1 .. 4 Loop Dbms_Output.Put_Line(i); End Loop; End; --降序：for i in reverse 4.. 1 Loop


示例：PKG_PARSE_REQUEST.FUNC_AUTH 1313 行 FOR I IN 1 .. V_COND_PARA_COUNT LOOP
注：程序块可以在可执行部分和异常处理
PL/SQL基础-目录
声明部分 数据类型


记录 使用%TYPE和%ROWTYPE 控制语句
• 条件 • 循环
逻辑比较

PL/SQL基础-声明部分
变量和常量都必须在声明( declare )部分定义，必 须规定名称和数据类型 也可以在声明部分为变量赋值，即初始化变量 常量
控制语句:

IF .. THEN .. ELSIF 语句


IF ... THEN …; ELSIF … THEN …; ELSE …; END IF;
PL/SQL基础-逻辑比较
控制语句:

CASE 语句 一
CASE 变量 WHEN 值1 THEN …; WHEN 值2 THEN …; ELSE …; END CASE;
游标
显式和隐式游标的区别


尽量使用隐式游标，避免编写附加的游标控制 代码（声明，打开，获取，关闭） 也不需要声明变量来保存从游标中获取的数据
游标
REF

CURSOR游标
动态游标，在运行的时候才能确定游标使用的 查询 ，分类
• 强类型（限制）REF CURSOR，规定返回类型

强类型举例 见备注
异常

自定义

declare
l_exc exception;

begin … raise l_exc; exception when l_exc then … end;
异常

异常传播

见注释例子 见注释例子二 EXCEPTION WHEN OTHERS THEN RET := SQLCODE; ERR_MSG := ‘错误信息:' ||SQLERRM(SQLCODE);
异常
exception
when <exception_expression> then … when <exception_expression> then … end;

异常
exception_expression包括：


1. 2. 3.
预定义表达式 用户定义表达式 PRAGMA EXCEPTION_INIT
过程

执行存储过程

execute my_proc exec my_proc begin my_proc end; 只有将EXECUTE 特权赋予用户，用户才可以运行它 将它赋予PUBLIC用户，则所有用户都可以运行

权限:具有EXECUTE特权

过程
参数

三种模式:IN、OUT、 IN OUT
PL/SQL基础-逻辑比较

循环: WHILE循环
Declare l_Loops Number := 0; Begin While l_Loops < 5 Loop Dbms_Output.Put_Line('looped '|| l_Loops || 'times'); l_Loops := l_Loops + 1; End Loop; End;
作用域和可视性

when others then最后一个错误处理柄

游标
定义

用来查询数据库，获取记录集合（结果集）的指 针，可以让开发者一次访问一行结果集，在每条 结果集上作操作 静态游标
• 显式游标 • 隐式游标
分类


REF游标
• 是一种引用类型，类似于指针 • PKG_CACHE_COMMANDS.RTCUR
游标

显式游标

CURSOR 游标名 ( 参数 ) [返回值类型] IS Select 语句

生命周期

打开游标（OPEN）：
• 解析，绑定----不会从数据库检索数据

从游标中获取记录(FETCH INTO)：
• 执行查询，返回结果集 • 通常定义局域变量作为从游标获取数据的缓冲区

关闭游标(CLOSE)
PL/SQL基础-逻辑比较
控制语句:

IF .. THEN 语句

IF .. THEN …; END IF;
PL/SQL基础-逻辑比较
控制语句:

IF .. THEN .. ELSE语句


IF ... THEN …; ELSE …; END IF;
PL/SQL基础-逻辑比较
PL/SQL基础-数据类型

PL/SQL与SQL使用相同的数据类型，只是界限上 有些不同 记录

TYPE <record_name>ቤተ መጻሕፍቲ ባይዱIS RECORD( 字段 类型。。。 );

使用%TYPE和%ROWTYPE

%ROWTYPE: 声明表、视图或者游标的某个行的记录变量 %TYPE：声明表、视图或者游标的某个列
异常
预定义

No_data_found
• select语句检索不到满足条件的数据行

Too_many_rows
• 由于隐式游标每次只能检索一行数据，使用隐式游标 时，这个异常检测到有多行数据存在

dup_val_on_index
• 如果某索引中已有某键列值，若还要在该索引中创建 该键码值的索引项时，出现此异常
• 完成游标处理，用户不能从游标中获取行 • 还可以重新打开

选项：参数和返回类型
游标
隐式游标


在PL/SQL中使用DML语言，使用ORACLE提 供的名为SQL的隐示游标 CURSOR FOR LOOP，用于for loop 语句
游标
游标属性




%FOUND：变量最后从游标中获取记录的时候， 在结果集中找到了记录 %NOTFOUND：变量最后从游标中获取记录 的时候，在结果集中没有找到记录 %ROWCOUNT：当前时刻已经从游标中获取 的记录数量 %ISOPEN：是否打开