Oracle PL/SQL 学习笔记

    存储过程编写经验和优化措施 | 谈谈2010年网站百度优化新技术 2010-09-25

一、PL/SQL 块
    块定义语法：
DECLARE
/*
       定义部分——定义常量、变量、复杂数据类型、游标
*/
BEGIN
/*
       执行部分——PL/SQL语句和SQL语句
*/
EXCEPTION
/*
       异常处理部分——处理运行错误
*/
END;
   注：PL/SQL中结束输入使用‘/’字符
        dbms_output.put_line(“输出的字符串内容”);
        dbms_output是oracle提供的系统包；put_line    是该包中的过程，用于输出字符串信息。当使用dbms_output包时必须要先激活服务器输出
        激活服务器输出语句： set serveroutput on

示例：
DECLARE
    str          varchar(5)    --变量的定义
BEGIN
    Select name INTO str FROM emp        --INTO是将name的值赋值给str
where empno=$no;    --$no是替代变量
EXCEPTION
    When NO_DATA_FOUND then         --PL/SQL预定义的异常类型
    dbms_output.put_line(“输出的字符串内容”);
END;

变量定义语法：
   变量名 [CONSTANT] 数据类型 [NOT NULL] [:=|DEFAULT 默认值或函数]
   --CONSTANT表示常量；
   --:=设置默认值

%TYPE属性
示例：
Name    emp.ename%TYPE          --name与emp表中ename列的类型和长度相同
Sex         Name%TYPE                    --sex与name类型和长度相同

%ROWTYPE属性
语法：
变量名         表名%ROWTYPE
示例：
Str   T1%ROWTYPE           
--str变量对应T1表中所有列，并且该表中的所有列的类型与长度相同

PL/SQL中使用的操作符
:=           --赋值操作符
=>      --关联操作符
||           --连接操作符

PL/SQL游标
游标定义语法：
CURSOR              游标名         IS    SQL语句
游标名%ISOPEN        判断游标是否已经打开
游标名%FOUND        SQL语句有作用行市，其属性值为TRUE；
游标名%NOTFOUND SQL语句没有作用行市，其属性值为TRUE；
游标名%ROWCOUNT       返回SQL语句所作用的总行数
注：游标名默认为‘SQL’

二、PL/SQL控制语句
1、分支语句
IF  条件  THEN     执行操作
ELSEIF 条件THEN     执行操作
ELSE  执行操作
END  IF;
2、多重分支语句
CASE      表达式
WHEN    要判断的值        THEN     执行操作
WHEN    要判断的值        THEN    执行操作
ELSE      执行操作
END      CASE;
3、循环语句一
WHILE   条件      LOOP
执行操作···
END      LOOP;
4、循环语句二
FOR              变量      IN    [REVERSE]    起点值　．．终点值 LOOP
执行操作
END              LOOP;
注：REVERSE表示由终点值到起点值循环；　．．是固定格式；

三、           存储过程
创建过程语法：
create [or replace] procedure procedure_name
[ (argment [ { in| in out }] type,
argment [ { in | out | in out } ] type
{ is | as }
<类型.变量的说明>
( 注: 不用 declare 语句 )
Begin
<执行部分>
exception
<可选的异常处理说明>
end;
l 这里的IN表示向存储过程传递参数，OUT表示从存储过程返回参数。而IN OUT 表示传递参数和返回参数；
l 在存储过程内的变量类型只能指定变量类型；不能指定长度；
l 在AS或IS 后声明要用到的变量名称和变量类型及长度；
l 在AS或IS 后声明变量不要加declare 语句。
示例：
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.；p_InParamete表示值*/
v_LocalVariable := p_InParameter; -- 合法
/* 分配 7 给 p_InParameter. 这是非法的,因为声明是IN ；p_InParamete表示值，即值不能給值*/
p_InParameter := 7; -- 非法
/* 分配7给p_ OutParameter. 这是合法的,因为声明是OUT；p_ OutParameter表示变量 */
p_OutParameter := 7; -- 合法
/* 分配 p_OutParameter 给 v_LocalVariable.这是非法的,因为声明是OUT；p_ OutParameter表示变量，即变量不能给变量*/
v_LocalVariable := p_outParameter; -- 非法
/* 分配 p_InOutParameter 给 v_LocalVariable. 这是合法的,因为声明是IN OUT */
v_LocalVariable := p_InOutParameter; --合法
/*分配 7 给 p_InOutParameter. 这是合法的,因为声明是IN OUT */
p_InOutParameter := 7; -- 合法
END ModeTest;
/

过程的调用：
EXEC [UTE] 过程名 (参数1 ,参数2…);

四、函数
创建语法：
create [or replace ] function 函数名
[(参数1 [in | out | in out] 参数类型，参数2 [in | out | in out] 参数类型，……)]
return 数据类型
is | as
begin
执行部分
exception
异常处理部分
end [函数名];

五、 包
1、  包规范创建语法
create [or replace] package 程序包名
is | as
procedure 过程名（过程参数列表）；
function 函数名（函数参数列表）；
……
end 程序包名；
2、  包体创建语法
create [or replace] package body 程序包名
is | as
之前定义的函数、过程的具体实现部分
end 程序包名；
3、包的调用
exec 程序包名.过程名（参数列表）；