Oracle存储过程

oracle 存储过程的基本语法


1.基本结构
​ 

CREATE OR REPLACE PROCEDURE 存储过程名字

(

    参数1 IN NUMBER,

    参数2 IN NUMBER

) IS

变量1 INTEGER :=0;

变量2 DATE;

BEGIN

END 存储过程名字


2.SELECT INTO STATEMENT

  将select查询的结果存入到变量中,可以同时将多个列存储多个变量中,必须有一条

  记录,否则抛出异常(如果没有记录抛出NO_DATA_FOUND)

  例子: 

  BEGIN

  SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;

  EXCEPTION

  WHEN NO_DATA_FOUND THEN

      xxxx;

  END;

  ...


3.IF 判断

  IF V_TEST=1 THEN

    BEGIN 

       do something

    END;

  END IF;


4.while 循环

  WHILE V_TEST=1 LOOP

  BEGIN

 XXXX

  END;

  END LOOP;


5.变量赋值
​  V_TEST := 123;


6.用for in 使用cursor

  ...

  IS

  CURSOR cur IS SELECT * FROM xxx;

  BEGIN

 FOR cur_result in cur LOOP

  BEGIN

   V_SUM :=cur_result.列名1+cur_result.列名2

  END;

 END LOOP;

  END;


7.带参数的cursor

  CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;

  OPEN C_USER(变量值);

  LOOP

 FETCH C_USER INTO V_NAME;

 EXIT FETCH C_USER%NOTFOUND;

    do something

  END LOOP;

  CLOSE C_USER;


8.用pl/sql developer debug
​  连接数据库后建立一个Test WINDOW

  在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试


关于oracle存储过程的若干问题备忘

1.在oracle中,数据表别名不能加as,如:

Oracle存储过程【面试+工作】_oracle

 也许,是怕和oracle中的存储过程中的关键字as冲突的问题吧


2.在存储过程中,select某一字段时,后面必须紧跟into,如果select整个记录,利用游标的话就另当别论了。

Oracle存储过程【面试+工作】_sql_02


3.在利用select...into...语法时,必须先确保数据库中有该条记录,否则会报出"no data found"异常。

   可以在该语法之前,先利用​select count(*) from​ 查看数据库中是否存在该记录,如果存在,再利用select...into...


4.在存储过程中,别名不能和字段名称相同,否则虽然编译可以通过,但在运行阶段会报错

Oracle存储过程【面试+工作】_sql_03


5.在存储过程中,关于出现null的问题

假设有一个表A,定义如下:

Oracle存储过程【面试+工作】_oracle_04

如果在存储过程中,使用如下语句:

Oracle存储过程【面试+工作】_oracle_05

如果A表中不存在bid="xxxxxx"的记录,则fcount=null(即使fcount定义时设置了默认值,如:fcount number(8):=0依然无效,fcount还是会变成null),这样以后使用fcount时就可能有问题,所以在这里最好先判断一下:

Oracle存储过程【面试+工作】_存储过程_06

这样就一切ok了。


6.Hibernate调用oracle存储过程

Oracle存储过程【面试+工作】_sql_07


Oracle存储过程学习

存储过程创建语法:

create or replace procedure 存储过程名(param1 in type,param2 out type)

    as

        变量1 类型(值范围);

        变量2 类型(值范围);

Begin

    Select count(*) into 变量1 from 表A where列名=param1;

    If (判断条件) then

       Select 列名 into 变量2 from 表A where列名=param1;

       Dbms_output。Put_line(‘打印信息’);

    Elsif (判断条件) then

       Dbms_output。Put_line(‘打印信息’);

    Else

       Raise 异常名(NO_DATA_FOUND);

    End if;

Exception

    When others then

       Rollback;

End;


注意事项:

1,  存储过程参数不带取值范围,in表示传入,out表示输出

2,  变量带取值范围,后面接分号

3,  在判断语句前最好先用count(*)函数判断是否存在该条操作记录

4,  用select 。。。into。。。给变量赋值

5,  在代码中抛异常用 raise+异常名


以命名的异常

命名的系统异常                          产生原因

ACCESS_INTO_NULL                 未定义对象

CASE_NOT_FOUND                   CASE 中若未包含相应的 WHEN ,并且没有设置

ELSE 时

COLLECTION_IS_NULL              集合元素未初始化

CURSER_ALREADY_OPEN          游标已经打开

DUP_VAL_ON_INDEX         唯一索引对应的列上有重复的值

INVALID_CURSOR                    在不合法的游标上进行操作

INVALID_NUMBER                   内嵌的 SQL 语句不能将字符换为数字

NO_DATA_FOUND                   使用 select into 未返回行,或应用索引表未初始化的 

TOO_MANY_ROWS                  执行 select into 时,结果集超过一行

ZERO_DIVIDE                           除数为 0

SUBSCRIPT_BEYOND_COUNT   元素下标超过嵌套表或 VARRAY 的最大值

SUBSCRIPT_OUTSIDE_LIMIT     使用嵌套表或 VARRAY 时,将下标指定为负数

VALUE_ERROR                         赋值时,变量长度不足以容纳实际数据

LOGIN_DENIED                       PL/SQL 应用程序连接到 oracle 数据库时,提供了不

正确的用户名或密码

NOT_LOGGED_ON                  PL/SQL 应用程序在没有连接 oralce 数据库的情况下

访问数据

PROGRAM_ERROR                  PL/SQL 内部问题,可能需要重装数据字典& pl./SQL

系统包

ROWTYPE_MISMATCH           宿主游标变量与 PL/SQL 游标变量的返回类型不兼容

SELF_IS_NULL                         使用对象类型时,在 null 对象上调用对象方法

STORAGE_ERROR                   运行 PL/SQL 时,超出内存空间

SYS_INVALID_ID                     无效的 ROWID 字符串

TIMEOUT_ON_RESOURCE       Oracle 在等待资源时超时


Oracle存储过程【面试+工作】_sql_08