数据存储过程

最新推荐文章于 2023-06-01 09:24:31 发布

躲在角落里的猫

最新推荐文章于 2023-06-01 09:24:31 发布

阅读量581

点赞数 1

文章标签：存储过程数据库 oracle存储过程

本文链接：https://blog.csdn.net/yangpingping94/article/details/71055494

版权

存储过程：

将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来, 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute，

即可自动完成命令。

简单来说存储过程就是一些存好的SQL语句，但是为什么要存储过程呢，接下来就说到他的好处了:

优点：

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时）,可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

3.存储过程可以重复使用,可减少数据库开发人员的工作量

4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权

存储过程有以下三种种类：

种类：

1.系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作,

如 sp_help就是取得指定对象的相关信息

2.扩展存储过程以XP_开头,用来调用操作系统提供的功能

exec master..xp_cmdshell 'ping 10.8.16.1'

3.用户自定义的存储过程,这是我们所指的存储过程

为了满足在工作中的业务需求大部分我们所说的存储过程就是指我们自定义的存储过程，就是按照需求封装好了一些我们自己所需要的SQL语句，即为自定义存储过程。

自定义存储过程：

存储过程创建语法：

create （or replace） procedure 存储过程名（param1 in type，param2 out type）

as/is

变量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 NO_DATA_FOUND then;

DBMS_output.put_line('数据未找到异常错误！');

When others then

DBMS_output.put_line('其他任何异常错误！');

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 在等待资源时超时

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
do something;
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单步调试

9.关系运算

= 等于
<> != 不等于
< 小于
> 大于
<= 小于等于
>= 大于等于

10.一般运算

+ 加
- 减
* 乘
/ 除
:= 赋值号
=> 关系号
.. 范围
|| 字符连接

11. 逻辑运算

is null 空值

between 在两者之间
in 在一列值中
and 逻辑与
or 逻辑或
not 逻辑非

12.符号运用例子

赋值号的用法为
varible :=value;

数据库赋值可以通过 select......into ............ 来完成,其中赋给的变量要先定义
V_name varchar2(20);
select name into V_name from table;

范围的用法
1..8 表示1到8

---------------------------------------------------------------使用存储过程中若干问题备注-----------------------------------------------------------------------------

1.在oracle中，数据表别名不能加as，如：