oracle-数据库06

接05篇，本篇讲解存储过程。

简单介绍。存储过程的存在就如同函数的存在，特点在于仅编译一次，高效率

同样的方法先来看个例子了解一下

一、举例

--找出某学号学生选课数量以及平均成绩

--定义存储过程
create or replace procedure p1(           
    pid in Persons.id % type,cn out number,cav out number(4,2))
is
    begin
        select count(sc.class_id),avg(total_s) into cn,cav
        from sc,sc_score s
        where sc.id=s.id
        and s.id=pid
        and sc.class_id-s.class_id;
    end;




--执行存储过程
set serveroutput on;         --（命令窗口中）              
declare
    v_pid sc.id % type := &sno;
    v_cn number;
    v_cav number(4,2);
begin
    p1(v_pid,v_cn,v_cav);
    dbms_output.put_line('学生'||v_pid||'所选科目数为'||v_cn||',平均成绩为'||v_cav);
end;

或

set serveroutput on;        --(都在命令窗口中)
var cn number;
var cav number;
execute p1(10001,:cn,:cav);
--exec p1(10001,:cn,:cav);

对于一个语句集，分为定义和执行两部分，定义分为create、is/as(begin-end) ，执行主要讲在sql窗口中的写法，分为declare、begin-end 

二、存储过程详细说明

 1、定义存储过程

类似函数讲解，框架就是记

create [or replace] procedure p_name
[v in/out v_type,……]
is/as
    [说明部分]
    begin
        [操作语句]
        [exception 特殊处理]
    end;
--[]的内容可不要，/左右需选择

•  create or replace procedure：定义存储过程标识，定义了过程名p_name

or replace的意义： 如果已经存在，则替换

• [参数] 
• is/as：两个一般可随便换
• [说明部分]：一般用于定义一些变量或游标，定义完全属于存储过程内部的量 
• begin-end： 中间是具体操作
• [操作语句]：就是执行动作，参考游标里的部分讲解
• [异常处理] 

（1）参数

参数就是函数中需要接收传入值和发送传出值的变量，是与外界联系的变量

v_name in/out v_type,……

 其中标识in为传入变量，out为传出变量，就是函数的传入值和返回值。in参数需要在执行前赋值（在执行存储过程时给输入值或确定的初值），out参数需要在执行过程中赋值（在定义存储过程时的具体操作内赋值），返回给调用过程时给定的参数

还是以上面的例子为例

 存储过程参数调用情况

v_type部分只写类型，不写长度

（2）异常处理

在每一种语言学习的中期阶段都会学到异常处理，这是用来处理程序出现错误或是你不想要的异常的情况，接下来看看语法结构

• 预定异常

查看某异常是否出现，出现做什么操作 

exception
    when exc_name then statement1;
    when others then statement2;

exc_name是要查看的异常情况，others是其余异常情况 

列举一些常用异常名称及编号：

编码	名称	含义
ORA-06530（-06530）	ACCESS_INTO_NULL	直接引用未初始化的对象属性时，触发异常
ORA-06592	CASE_NOT_FOUND	CASE语句的WHEN子句没有包含必须条件分支或者ELSE子句时，触发异常
ORA-06531	COLLECTION_IS_NULL	没有初始化集合变量，触发异常
ORA-06511	CURSOR_ALREADY_OPEN	在已打开游标上执行OPEN操作时，触发异常
ORA-01001	INVALID_CURSOR	视图从未打开游标提取数据，或者关闭未打开游标时，触发异常
ORA-01722	INVALID_NUMBER	内嵌SQL语句不能将字符转变成数字时，触发异常
ORA-01017	LOGIN_DENIED	连接到Oracle数据库时，如果提供了不正确的用户名或口令，触发异常
ORA-01403	NO_DATA_FOUND	执行SELECT INTO未返回行，或者引用了未初始化的PL/SQL表元素时，触发异常
ORA-01012	NOT_LOGGED_ON	未连接到Oracle数据库，当执行内嵌SQL语句时，触发异常
ORA-06501	PROGRAM_ERROR	表示存在PL/SQL内部问题，在这种情况下需要重新安装数据字典视图和PL/SQL包。
ORA-016504	ROWTYPE_MISMATCH	执行赋值操作时，两变量返回类型不兼容，触发异常
ORA-30625	SELF_IS_NULL	使用对象类型时，如果在NULL实例上调用成员方法，触发异常
ORA-06500	STORAGE_ERROR	执行PL/SQL块时，如果超出内存空间或者内存被破坏，触发异常
ORA-06533	SUBSCRIPT_BEYOND_COUNT	使用嵌套表或者VARRAY元素时，如果下标超出了嵌套表或者VARRAY元素的范围，触发异常
ORA-06532	SUBSCRIPT_OUTSIDE_LIMIT	使用嵌套表或者VARRAY元素时，如果下标为负值，触发异常
ORA-01410	SYS_INVALID_ROWID	将字符串转变为ROWID时，使用无效字符串，触发异常
ORA-00051	TIMEOUT_ON_RESOURCE	等待资源时超时，触发异常
ORA-01422	TOO_MANY_ROWS	执行SELECT INTO语句时，返回超过一行，触发异常
ORA-06502	VALUE_ERROR	执行赋值操作时，如果实际数据超出变量长度，触发异常
ORA-01476	ZERO_DIVIDE	数字除以0，触发异常

• 非预定异常

也是查看某异常，只是在数据库中有些异常是只有错误编号和相关的错误描述，并没有名称，于是非预定异常可以为已有异常命名，解决了这个问题

create or replace procedure p1(
    v1 in char,v2 out char)
is
    --定义异常，并分配给某个已有异常
    my_exception exception;
    pragma exception_init(my_exception,-12899);
    begin
        insert into P values(10000002,null,null);
    --异常处理
        exception
            when my_exception then
            dbms_output.put_line('内容超出存储范围');
    end;

• 自定义异常 

 有时程序员们需要调试程序或者不希望出现某些有但不合理的值，可以通过自定义异常来处理

create or replace procedure p1(
    v1 in char,v2 out char)
is
    --定义异常
    my_exception exception;
    pragma procedure_init(my_exception,-20000);
    begin
        if v1='1' then v2:='Y';
        elsif v1='0' then v2:='N';
        --抛出异常
        else raise my_exception;
        end if;
        --处理异常
        exception
            when my_exception then
            dbms_output.put_line('v1值错误');
    end;

 有一步抛出异常，其意义在于赋予该异常情况的具体含义（告诉别人你的异常在什么情况下算是异常）

有些小拓展知识：

1. if语句完整结构：if-elsif-else-end if
2. 赋值符号：':='

2、执行存储过程

也就是所谓的调用函数该如何调用，执行存储过程该如何执行的问题

（1）sql窗口中 

declare
    [v v_type;……]
begin
    [具体操作（包含过程调用）]
end;

• declare：定义声明 
• [变量说明]：变量名 变量类型 （选择赋值）;存储过程的参数都需要有变量与他们在这里联系
• begin-end：具体操作
• [具体操作]

执行存储过程（调用）：pro_name(v,……);

（2）命令窗口中

set serveroutput on;
var v_name v_type;
exec pro_name(value,:v_name,……);

•  set serveroutput on：打开输出权限，如果有输出操作则可以在命令窗口中看到结果
• var：定义变量，用来接收输出参数
• exec或execute：执行存储过程（注意参数前面加冒号：）

现在再返回看举例应该ok了 

---

06结