存储过程

最新推荐文章于 2022-04-28 15:35:13 发布
最新推荐文章于 2022-04-28 15:35:13 发布
阅读量627 收藏 5
点赞数 1
分类专栏： 数据库 文章标签： 数据库 存储过程
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_23473123/article/details/72854189
版权

1.简介

存储过程不同于sql语句，它相当于一个function(…)可以有参数、流控制语句（IF，WHILE，LOOP，REPEAT，和CASE语句）、返回值，当然核心还是sql语句。这样的程序存储在数据的数据字典中。

存储过程的典型用途包括数据验证（集成到数据库中）或权限控制机制。此外，存储过程可以整合和集中最初在应用程序中实现的逻辑。为了节省时间和内存，需要执行多个SQL语句的广泛或复杂的处理可以保存到存储过程中，所有应用程序都调用该过程。可以通过从另一个执行一个存储过程来使用嵌套存储过程。

基本语法

各数据库语法会有所不同，下面介绍Oracle和Sql Server。

Oracle

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

is

--定义变量

变量 类型（值范围）; --vs_msg VARCHAR2(4000); 

--定义游标(简单的说就是一个可以遍历的结果集) 

CURSOR cur_1 IS 
SELECT ... 
FROM ...  
WHERE ...  
GROUP BY ... ; 

BEGIN 

--用输入参数给变量赋初值，用到了Oralce的SUBSTR TO_CHAR ADD_MONTHS TO_DATE 等很常用的函数。

vs_ym_beg := SUBSTR(is_ym,1,6); 
vs_ym_end := SUBSTR(is_ym,7,6); 
vs_ym_sn_beg := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,'yyyymm'), -12),'yyyymm'); 
vs_ym_sn_end := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,'yyyymm'), -12),'yyyymm'); 

--先删除表中特定条件的数据。 

DELETE FROM 表名 WHERE ym = is_ym; 

--然后用内置的DBMS_OUTPUT对象的put_line方法打印出影响的记录行数，其中用到一个系统变量SQL%rowcount 

DBMS_OUTPUT.put_line('del上月记录='||SQL%rowcount||'条'); 

INSERT INTO表名(area_code,ym,CMCODE,rmb_amt,usd_amt) 
SELECT area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000 
FROM BGD_AREA_CM_M_BASE_T 
WHERE ym >= vs_ym_beg 
AND ym <= vs_ym_end 
GROUP BY area_code,CMCODE; 
DBMS_OUTPUT.put_line('ins当月记录='||SQL%rowcount||'条'); 

--遍历游标处理后更新到表。遍历游标有几种方法，用for语句是其中比较直观的
一种。 

FOR rec IN cur_1 LOOP 
UPDATE 表名 
SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn 
WHERE area_code = rec.area_code 
AND CMCODE = rec.CMCODE 
AND ym = is_ym; 
END LOOP; 
COMMIT; 

--错误处理部分。OTHERS表示除了声明外的任意错误。SQLERRM是系统内置变量保存了当前错误的详细信息。 

EXCEPTION 

WHEN OTHERS THEN 
vs_msg := 'ERROR IN xxxxxxxxxxx_p('||is_ym||'):'||SUBSTR(SQLERRM,1,500); 

ROLLBACK; 

--把当前错误记录进日志表。 

INSERT INTO LOG_INFO(proc_name,error_info,op_date) 
VALUES('xxxxxxxxxxx_p',vs_msg,SYSDATE); 
COMMIT; 
RETURN; 

END 存储过程名;

注意事项：

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

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

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

  4. 用select…into…给变量赋值

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

已命名的异常

命名的系统异常产生原因
ACCESS_INTO_NULL未定义对象
CASE_NOT_FOUNDCASE 中若未包含相应的 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_DENIEDPL/SQL 应用程序连接到 oracle 数据库时，提供了不
正确的用户名或密码
NOT_LOGGED_ONPL/SQL 应用程序在没有连接 oralce 数据库的情况下
访问数据
PROGRAM_ERRORPL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL
系统包
ROWTYPE_MISMATCH宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
SELF_IS_NULL使用对象类型时，在 null 对象上调用对象方法
STORAGE_ERROR运行 PL/SQL 时，超出内存空间
SYS_INVALID_ID无效的 ROWID 字符串
TIMEOUT_ON_RESOURCEOracle 在等待资源时超时

判断语句:

if 比较式 then begin end; end if; 

create or replace procedure test_if(v_outlook in varchar2, c out varchar2) is
begin
  if v_outlook = 'rain' then
    begin
      select count(*)
        into c
        from golfproblemdatarecord g
       where g.outlook = v_outlook; --变量赋值
    end;
  else
    Dbms_output.put_line('输入的outlook不对');
  end if;
end test_if;

For 循环

For … in … LOOP
– 执行语句
end LOOP;
(1) 循环遍历游标 

create or replace procedure test_for is
  cursor cu is
    select outlook from golfproblemdatarecord;
begin
  for outlook in cu loop
    begin
      dbms_output.put_line(outlook.outlook);
    end;
  end loop;
end test_for;

(2) 循环遍历数组 

create or replace procedure test(varArray in myPackage.TestArray) as 
--(输入参数varArray 是自定义的数组类型，具体见数组一节) 

i number; 

begin 

i := 1; -- 存储过程数组是起始位置是从1 开始的，与java 、C 、C++ 等语言不同。因为在Oracle 中本是没有数组的概念的，数组其实就是一张

-- 表(Table), 每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历 

for i in 1..varArray.count LOOP 
dbms_output.putline('The No.'|| i || 'record in varArray is:'||varArray(i)); 
end LOOP; 
end test;

While 循环

while 条件语句 LOOP 

begin 
end; 
end LOOP; 
E.g 
create or replace procedure test(i in number) as 
begin 
while i < 10 LOOP 
begin 
i:= i + 1; 
end; 
end LOOP; 
end test;

数组

首先明确一个概念：Oracle 中本是没有数组的概念的，数组其实就是一张表(Table), 每个数组元素就是表中的一个记录。

使用数组时，用户可以使用Oracle 已经定义好的数组类型，或可根据自己的需要定义数组类型。

(1) 使用Oracle 自带的数组类型

x array; – 使用时需要需要进行初始化

e.g: 

create or replace procedure test(y out array) 
is 
x array; 
begin 
x := new array(); 
y := x; 
end test;

(2) 自定义的数组类型 ( 自定义数据类型时，建议通过创建Package 的方式实现，以便于管理) 

create or replace package myPackage is 
Public type declarations type info is record( name varchar(20), y number); 
type TestArray is table of info index by binary_integer;

-- 此处声明了一个TestArray 的类型数据，其实其为一张存储Info 数据类型的Table 而已，及TestArray 就是一张表，有两个字段，一个是name ，一个是y 。需要注意的是此处使用了Index by binary_integer 编制该Table 的索引项，也可以不写，直接写成：type TestArray is table of info ，如果不写的话使用数组时就需要进行初始化：varArraymyPackage.TestArray; varArray := new myPackage.TestArray(); 

end TestArray;

游标的使用

Oracle 中Cursor 是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性也很多，现仅就常用的用法做一二介绍：

(1)Cursor 型游标( 不能用于参数传递) 

create or replace procedure test() is 

cusor_1 Cursor is select std_name from student where ...;--Cursor 的使用方式1 

cursor_2 Cursor; 

begin 

select class_name into cursor_2 from class where ...; 
--Cursor 的使用方式2 可使用For x in cursor LOOP .... end LOOP; 来实现对Cursor 的遍历 

end test;

(2)SYS_REFCURSOR 型游标，该游标是Oracle 以预先定义的游标，可用于参数进行传递 

create or replace procedure test_SYS_REFCURSOR(rsCursor out SYS_REFCURSOR)

 is

  l_cur   SYS_REFCURSOR;
  l_empno VARCHAR2(20);
begin

  OPEN l_cur FOR
    select outlook from golfproblemdatarecord;
  loop
    fetch l_cur
      into l_empno;
    exit when l_cur%notfound;

    dbms_output.put_line(l_empno);
  end loop;
  rsCursor := l_cur;

end test_SYS_REFCURSOR;

Sql Server

--------------创建存储过程-----------------

CREATE PROC [ EDURE ] procedure_name [ ; number ]
    [ { @parameter data_type }
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ]

[ WITH
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

--------------调用存储过程-----------------

EXECUTE Procedure_name '' --存储过程如果有参数，后面加参数格式为：@参数名=value，也可直接为参数值value

--------------删除存储过程-----------------

drop procedure procedure_name    --在存储过程中能调用另外一个存储过程，而不能删除另外一个存储过程

创建存储过程的参数

1.procedure_name ：存储过程的名称，在前面加#为局部临时存储过程，加##为全局临时存储过程。

2.; number：是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。

3.@parameter： 存储过程的参数。可以有一个或多个。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2.100 个参数。
使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。有关更多信息，请参见 EXECUTE。

4.data_type：参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。有关 SQL Server 提供的数据类型及其语法的更多信息，请参见数据类型。
说明 对于可以是 cursor 数据类型的输出参数，没有最大数目的限制。

5.VARYING： 指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。

6.default： 参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（%、_、[] 和 [^]）。

7.OUTPUT ：表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。

8.RECOMPILE: 表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。

9.ENCRYPTION: 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。 说明 在升级过程中，SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。

10.FOR REPLICATION :指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。

11.AS :指定过程要执行的操作。

12.sql_statement :过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。

小结：看过这些基本语法后，下面我就根据语法创建各式的存储过程。

UserIDUserNamePassWordRegisterTimeRegisterIP
12662012-12-314
18552013-12-314
19112014-12-312

针对上面的表，我使用存储过程对它做一些操作：

简单的增删查改

create Procedure SimpleCRUDUserAccount
as
select * from UserAccount
/insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(9,9,'2013-01-02',9)
/......
go

-------------执行上面的存储过程----------------
exec GetUserAccount

有返回值的存储过程

-------------创建名为GetUserAccount的存储过程----------------

create Procedure inUserAccountRe
as
insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(10,10,'2013-01-02',10)
return @@rowcount
go

-------------执行上面的存储过程----------------

exec inUserAccountRe

解释：这里的@@rowcount为执行存储过程影响的行数，执行的结果是不仅插入了一条数据，还返回了一个值即 return value =1 ，这个可以在程序中获取，稍后在c#调用存储过程中会有说到。

有输入参数和输出参数的存储过程

————-创建名为GetUserAccount的存储过程—————-

create Procedure GetUserAccountRe
@UserName nchar(20),
@UserID int output
as
if(@UserName>5)
select @UserID=COUNT(*) from UserAccount where UserID>25
else
set @UserID=1000
go

-------------执行上面的存储过程----------------

exec GetUserAccountRe '7',null

解释：@UserName为输入参数，@UserID为输出参数。 运行结果为@userID为COOUT（*）即 =1。

同时具有返回值、输入参数、输出参数的存储过程

————-创建名为GetUserAccount的存储过程—————-

create Procedure GetUserAccountRe1
@UserName nchar(20),
@UserID int output
as
if(@UserName>5)
select @UserID=COUNT(*) from UserAccount where UserID>25
else
set @UserID=1000
return @@rowcount
go

-------------执行上面的存储过程----------------

exec GetUserAccountRe1 '7',null

结果：@userID为COOUT（*）即 =1，Retun Value=1。

同时返回参数和记录集的存储过程

-------------创建名为GetUserAccount的存储过程----------------

create Procedure GetUserAccountRe2
@UserName nchar(20),
@UserID int output
as
if(@UserName>5)
select @UserID=COUNT(*) from UserAccount where UserID>25
else
set @UserID=1000
select * from UserAccount
return @@rowcount
go

-------------执行上面的存储过程----------------

exec GetUserAccountRe2 '7',null

结果：返回执行 select * from UserAccount 这句代码的结果集，同时@userID为COOUT（*）即 =1，Retun Value=9。

返回多个记录集的存储过程

-------------创建名为GetUserAccount的存储过程----------------

create Procedure GetUserAccountRe3
as
select * from UserAccount
select * from UserAccount where UserID>5
go

-------------执行上面的存储过程----------------

exec GetUserAccountRe3

结果：返回两个结果集，一个为 select * from UserAccount，另一个为 select * from UserAccount where UserID>5 。

qq_23473123
关注
专栏目录
如何使用mysql存储过程批量造压测数据？
媛测
07-22 5332
一、 压测数据的准备方式有哪些？   在执行压测脚本之前，我们通常要准备数据库存量数据，一般是以生产数据为依据成比例增加，少则需要上百万，多则需要上千万、上亿，经常有很多时间要花在造数据上。   目前常用的造数据方法有： 1、通过编程语言写代码，如用java、python等语言，用自己熟悉的语言写就行，灵活快速 2、用jmeter、loadrunner等压测工具，需要数据量较大时，此...
Oracle存储过程基本语法介绍-存储过程创建语法
Vision_xie的博客
03-31 528
create or replace procedure 存储过程名（param1 in type，param2 out type）  as  变量1 类型（值范围）; --vs_msg VARCHAR2(4000);  变量2 类型（值范围）; Begin  Select count(*) into 变量1 from 表A where列名=param1；  If (判断条件) th
存储过程语法及实例
Axn_很优秀
11-27 507
存储过程如同一门程序设计语言，同样包含了数据类型、流程控制、输入和输出和它自己的函数库。 --------------------基本语法--------------------一.创建存储过程 create procedure sp_name() begin … end二.调用存储过程 1.基本语法：call sp_name() 注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递三.删...
Mysql 存储过程
Ranger的博客
08-25 1万+
一、SQL记录 # 使用test数据库 USE test # 创建t_user数据表 CREATE TABLE t_user(id INT PRIMARY KEY,username VARCHAR(20),age INT) # 插入数据 INSERT INTO t_user VALUES(1,'root',21); INSERT INTO t_user VALUES(2,'admin',18); INSERT INTO t_user VALUES(3,'zhangsan',29); INSERT.
MySQL的存储过程
热门推荐
weixin_60589106的博客
01-27 5万+
概念 MySQL5.0版本开始支持存储过程,存储过程就是一组SQL语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于JAVA语言中的方法,存储过就是数据库SQL与层层面的代码封装与重用 特性 1.有输入输出参数,可以声明变量,有if/else/case/while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能 2.函数的普通特性:模块化,封装,代码复用 3.速度快,只有首次执行需要经过编译和优化步骤,后续被调用可以直接执行,省去以上步骤 格式 创建存储过程 -- 创建存储过
MySQL操作之存储过程
lydms的博客
09-19 3962
一、概念 二、存储过程的创建 2.1创建存储过程 2.2 变量的使用 2.3 定义条件和处理程序 2.4 光标的使用 2.5、流程控制的使用 三、存储过程的使用 3.1、调用存储过程 3.2、查看存储过程 3.3、修改存储过程 3.4、删除存储过程 四、综合案例 4.1、创建数据库 4.2、创建存储过程
数据库存储过程
qq_51605826的博客
04-28 6799
存储过程 从sql语句执行的流程中分析存在的问题 1、如果我们需要重复多次执行相同的SQL语句，sql执行都需要通过链接传递到MySql 并且需要经过编译和执行的步骤 2、如果我们需要连续执行多个Sql命令，并且第二个Sql指令需要使用第一个sql指令执行的结果作 为参数 将能够完成特定功能的SQL指令进行封装，编译之后存储到数据库服务器上，并为之设置姓名 当客户端需要执行这个功能是，不用编写SQL指令，直接通过封装过的SQL指令的名字完成调用 即可 优点： SQL指令无需客户端编写
mysql 存储过程fetch_MySQL之存储过程
weixin_39829166的博客
01-25 1309
引言在Mysql中无论是在定义上还是在使用上，自定义函数和存储过程有很多相似的地方，所以我们来简单了解下什么是存储过程以及存储过程的实现方式。以及他们之间的区别。定义存储过程的定义是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。它首先是一组能够完成特定功能的SQL集合，经过编译后存储在数据库中，是通过用户指定的...
帆软报表Oracle存储过程解决storeParameter1参数试用插件
最新发布
12-12
在帆软报表的设计过程中，当用户尝试调用一个Oracle数据库中的存储过程时，可能会遇到一个问题：即使存储过程本身并未声明任何输入或输出参数，设计界面仍会提示需要提供一个名为"storeParameter1"的参数。...
Sql Server 存储过程调用存储过程接收输出参数返回值
09-09
在SQL Server中，存储过程是预编译的SQL语句集合，它们允许开发者封装一系列复杂的数据库操作，并且可以通过参数传递数据。本篇文章将详细讲解如何在SQL Server中调用一个存储过程并接收输出参数以及返回值。 首先...
oracle存储过程unwrap解密工具.zip
04-28
Oracle存储过程unwrap解密工具主要用于处理Oracle数据库中的加密存储过程。在Oracle数据库系统中，为了保护敏感代码或数据，开发人员有时会选择对存储过程进行加密。然而，当需要查看、调试或恢复这些加密的存储过程...
sqlserver存储过程语法详解
09-10
SQLServer存储过程是一种预编译的SQL代码集合，它封装了一组特定的操作，可以在需要时重复执行，从而提高数据库应用的性能和安全性。存储过程在数据库中作为一个可执行的对象存在，能够接受输入参数，处理数据，并...
该不该用存储过程
java_gchsh的博客
01-23 6605
转：减少存储过程封装业务逻辑-web开发与传统软件开发的思维模式不同 本篇文章讨论并不是：不要使用存储过程,因为有些事情还是要存储过程来完成,不可能不用。而是关于："业务逻辑是不是要封装在存储过程中实现，这样子php、java等就是调用存储过程"。 业务逻辑，通俗说就是：比如要取数据的操作，取出会员编号为x的数据，原来我们一般是封装成函数，或者直接编写sql语句查询。现在是交给数据库
Join连接条件放在on后面好和where的区别
王者之峰
05-29 7534
背景 有一次跑数据发现连接条件放在on后比放在where慢
mysql InnoDB引擎的行锁和表锁
王者之峰
04-25 6722
引入 之前是有接触行锁和表锁但是由于没有实际应用过也只是大概了解，前两天就遇到了并发同时对一条记录进行修改。mysql肯定会让修改请求排队，也就是说加了锁，但是mysql默认加的是表锁，但是会影响效率，所以我们需要用行锁。 行锁和表锁 表锁：顾名思义就是对整张表进行加锁，同一时刻整张表所有记录都被霸占，虽然不会出现死锁问题但是锁冲突高堵塞高，并发低。 行锁：很明显只对某一行进行加锁，这...
数据库该不该使用外键
王者之峰
02-06 6682
问题引入 以前的意识里都是需要建立外键，外键能起到约束作用，能保证数据的完整性和一直性，比如如果没有外键约束，你自己程序控制又不到位把基本信息都删除了，详情却存在，人的基本信息不存在了，工资信息里却存在这个人，想要找这个人究竟是谁都找不到。 今天看到原来的外键都被去掉了，问了下组长，结果回答就两个字“效率”，虽然感觉很诧异，但是毕竟人家比我有经验并没有去争论，下面将站在两个对立面去赞成和反对建
MySQL存储过程深度解析
"MySQL存储过程详解" MySQL存储过程是一种强大的数据库编程工具，它允许数据库管理员和开发者将一组SQL语句组合在一起，形成一个可重用的逻辑单元，以完成特定的任务。存储过程在数据库中预先编译，提高了执行效率...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值