第12章 存储过程

最新推荐文章于 2022-11-28 16:53:50 发布
最新推荐文章于 2022-11-28 16:53:50 发布
阅读量911 收藏 1
点赞数
分类专栏： SQL 文章标签： SQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_15788231/article/details/97897228
版权

第12章 存储过程

存储过程是一组预编译的SQL语句，是数据库的重要组成部分。在SQL中，函数和过程都能够被调用，其中，调用的过程称为存储过程。本章我们就一起来学习一下与存储过程相关的一些内容。

12.1 理解存储过程
在创建和使用存储过程之前，用户首先要对存储过程的基本概念、功能及其使用特性有一定的认识。本节中我们首先来了解一下存储过程的一些基础知识。

12.1.1 存储过程概述
存储过程是一组经过压缩处理的经常使用的命令。执行存储过程的一种方法是在本地存储设计好的SQL语句，然后创建应用程序，将命令发送到数据库并对结果进行处理。这大大提高了应用程序访问数据库的速度。

标准的SQL语言并不是完全的编程语言，它是面向数据的，没有控制循环的结构，没有条件执行的关键词。而在SQL Server数据库系统中，提供了存储过程对象，它以特定的顺序排列Transact-SQL语句序列，可为其指定名称、加以编译并保存在数据库中。

存储过程可以被C语言和其他编程语言调用和执行。存储过程在创建后就作为数据库的一部分存储起来，以便程序员能简单地将存储过程作为一个函数来调用。简而言之，存储过程是存放在服务器端数据库中的子程序，是经编译过的能执行特定功能的Transact-SQL集合，它作为一个单元来处理。
存储过程在第一次执行时，进行语法检查和编译，执行后，它的执行计划就驻留在高速缓存中，用于后续调用。存储过程可以接收和输出参数，返回执行存储过程的状态值，还可以嵌套调用。用户可以像使用函数一样重复调用这些存储过程，实现它所定义的操作。

12.1.2 存储过程的优缺点
存储过程存在着很多的优点和缺点，具体表现在以下几个方面。
1.存储过程的优点
存储过程主要具有如下几个优点。

  • 允许组件式编程。存储过程在被创建以后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句，从而极大地提高了程序的可移植性。
  • 执行速度快，改善系统性能。存储过程在服务器端运行，可以利用服务器强大的计算能力和速度，执行速度快。存储过程是预编译的，第一次执行后的存储过程会驻留在高速缓存中，以后直接调用，执行速度很快，如果某个操作需要大量的T-SQL语句或重复执行，那么使用存储过程比直接使用T-SQL语句执行得更快。
  • 减少网络流量。对于针对数据库对象的相同操作，如查询、修改表，如果这一操作所涉及的SQL语句被组织成存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，而不是多条SQL语句，从而大大增加了网络流量，降低网络负载。
  • 确保数据库的安全，提高数据可靠性。存储过程可被作为一种安全机制，可以通过编程方式控制所有操作对数据库信息访问的权限。

2.存储过程的缺点
使用存储过程也存在缺点，具体表现在以下两个方面。

  • 对存储过程的管理比较麻烦。在使用大量的存储过程时，如果想要区分每个存储过程会很困难。
  • 存储过程不能实现复杂的逻辑操作。

12.2 流程控制语句
存储过程实际上都是由SQL语句和程序流控制语句构成的语句串。前面的章节中已经简单介绍了Transact-SQL的一些语法要素，本节将详细介绍在SQL Server中和Oracle数据库中提供的程序流控制语句及其使用。
12.2.1 SQL SERVER中的流程控制语句
在SQL Server中，程序流控制语句用于控制Transact-SQL语句、语句块或存储过程的执行流程。SQL Server中提供的流控制语句及功能如表12.1所示。
表12.1 SQL Server中提供的流控制语句及功能

语    句	功    能
IF…ELSE	条件选择语句，条件成立，执行IF后语句，否则执行ELSE语句
BEGIN…END	用来定义一个语句块
GOTO	无条件转移语句
CASE结构	用于多条件分支选择，可完成计算多个条件并为每个条件返回单个值
WHILE	循环语句，重复执行命令行或程序块
WAITFOR	设置语句执行的延迟时间
BREAK	循环跳出语句
CONTINUE	重新启动循环语句
RETURN	无条件退出（返回）语句

下面将对一些常用的程序流控制结构进行简单介绍。
1．IF…ELSE语句
IF…ELSE语句用于控制程序的执行方向。
【语法说明】
IF…ELSE结构的语法格式如下所示。

IF bool_expression
Statement
[ELSE]
Statement

ELSE语句可以省略不写，省略ELSE语句后的语句格式如下所示。

IF bool_expression
Statement

上述语句中的bool_expression表示的是条件表达式，Statement表示执行的SQL语句。
【上机实战】
从员工信息表（tb_ygxx）中查询员工编号为1001员工的年龄是否超过了18岁。为了便于理解，首先查看tb_ygxx数据表中的数据信息。

select * from tb_ygxx

代码执行以后，其执行结果如图12.1所示。
在这里插入图片描述
图12.1 tb_ygxx数据表中的数据信息
IF…ELSE语句应用示例的语句如下所示。

IF (SELECT DATEDIFF (year,出生日期,GetDate()) AS '年龄' FROM tb_ygxx WHERE 编号='1005')>18
print '编号为1001员工的年龄超过了18岁'
ELSE

print ‘编号为1001员工的年龄小于18岁’
代码执行以后，其执行结果如图12.2所示。
在这里插入图片描述
图12.2 IF…ELSE语句的应用示例
上述代码中的ELSE及其后面的语句可以省略不写，如下面的代码所示。

IF (SELECT DATEDIFF (year,出生日期,GetDate()) AS '年龄' FROM tb_ygxx WHERE 编号='1005')>18
print '编号为1001员工的年龄超过了18岁'

代码执行以后，只提示语句运行成功，并不提示编号为1001员工的年龄是否超过18岁的信息，说明在省略了ELSE及其后面的语句之后，如果不符合IF语句的判断条件，DBMS将没有可执行的SQL语句，因此，将无法提示语句执行信息。
IF…ELSE语句可以按照语句的先后顺序进行执行。
从员工信息表（tb_ygxx）中查询员工的整体年龄情况，具体实现的代码如下所示。

IF(SELECT AVG(年龄) FROM tb_ygxx)<18
PRINT '员工平均年龄过低'
ELSE
IF(SELECT AVG(年龄) FROM tb_ygxx)>=18 and (SELECT AVG(年龄) FROM tb_ygxx)<50
PRINT '员工整体趋向年轻化'
ELSE
IF(SELECT AVG(年龄) FROM tb_ygxx)>=50 
PRINT '员工整体年龄偏大'
ELSE
PRINT '员工整体年龄不合理'

代码执行以后，其执行结果如图12.3所示。
在这里插入图片描述
图12.3 IF…ELSE语句的顺序执行

说明：有关tb_ygxx数据表中的数据信息请参照图12.1。

IF…ELSE语句的另一种形式是IF…ELSE IF…ELSE语句形式。上面的示例代码也可以写成下列的语句形式。

IF(SELECT AVG(年龄) FROM tb_ygxx)<18
PRINT '员工平均年龄过低'
ELSE IF(SELECT AVG(年龄) FROM tb_ygxx)>=18 and (SELECT AVG(年龄) FROM tb_ygxx)<50 
PRINT '员工整体趋向年轻化'
ELSE IF(SELECT AVG(年龄) FROM tb_ygxx)>=50
PRINT '员工整体年龄偏大'
ELSE
PRINT '员工整体年龄不合理'

代码执行以后，其执行结果如图12.3所示。
2．BEGIN…END语句
BEGIN…END用来定义一个语句块（类似于其他高级语言中的复合句），位于BEGIN和END之间的Transact-SQL语句都属于这个语句块，可视作一个单元来执行。执行BEGIN… END经常在条件语句（如IF…ELSE）中使用，在BEGIN…END中可嵌套，使用另外的BEGIN…END来定义另一个程序块。
BEGIN…END的语法格式如下。
【语法说明】

BEGIN
  Statement block
END

代码中的Statement block表示顺序执行的SQL语句，这些语句被放在BEGIN…END语句中顺序执行，完成某些特有的功能。
【上机实战】
使用BEGIN…END语句将员工信息表（tb_ygxx）中员工姓名为“李娜”的籍贯更改为“吉林省辽源市”，其实现的代码如下所示。

BEGIN
  UPDATE tb_ygxx
  SET 籍贯='吉林省辽源市'
  WHERE 姓名='李娜'
END

代码执行以后，查询将员工信息表（tb_ygxx）中的数据信息如图12.4所示。
在这里插入图片描述
图12.4 使用BEGIN…END语句更新数据
在代码中，使用BEGIN…END语句与其中的语句一起构成了一个SQL语句块。通常情况下，BEGIN…END语句应用在比较复杂的语句中，以便于增强SQL语句的可读性。实际上在上述代码中应用BEGIN…END语句并没有什么意义，这里只是为了讲解如何应BEGIN…END语句才这样写的。

3．GOTO语句
GOTO语句的作用是将执行语句转到用户自定义的标号（Label）处。
【语法说明】
GOTO语句的语句结构如下所示。

Label :GOTO Label

【上机实战】
将员工信息表（tb_ygxx）中员工姓名为“李娜”的联系地址更改为“吉林省辽源市”，当修改完之后使用GOTO语句提示信息修改完成，其实现的代码如下所示。

declare @Mums int
SELECT @Mums=1
UPDATE tb_ygxx
SET 联系地址='吉林省辽源市'
WHERE 姓名='李娜'
ShowInfo:
PRINT '信息修改完成！'
SELECT @Mums=@Mums+1
IF(@Mums=1)
GOTO ShowInfo

代码执行以后，其执行结果如图12.5所示。
在这里插入图片描述
图12.5 GOTO语句的应用示例
此时查看员工信息表（tb_ygxx）中的数据信息如图12.6所示。
在这里插入图片描述
图12.6 更新数据之后的数据信息

注意：由于GOTO语句很难控制，所以通常情况下，在应用SQL语句时，尽量少用或者不用GOTO语句来实现某些操作功能。

4．DECLARE语句
在SQL中，使用DECLARE语句定义局部变量。
【语法说明】
DECLARE语句定义的变量必须以@字符开头，其实现的语法结构如下所示。

DECLARE @ Var_Name datetype
[,@ Var_Name datetype] …

语句中的Var_Name表示的是一个合法的标识符，datetype表示的是一个数据类型或者用户自定义的数据类型。
可以用SELECT语句给DECLARE语句定义的局部变量赋初始值，其实现的语法格式如下所示。

SELECT @ Var_Name={expression | (select_statement)}
[,@ Var_Name={expression | (select_statement)}…]
[FROM Table_name]
[WHERE expression]

通过SELECT语句给变量赋的初值可以是表达式或者是SQL语句，也可以是SELECT查询语句。代码中的expression表示的是表达式，select_statement表示的是SELECT查询语句，Table_name则表示数据表的名称。

注意：局部变量的定义与赋值必须在同一个存储过程当中。

【上机实战】
从员工信息表（tb_ygxx）中查询所有员工的最大年龄、最小年龄和平均年龄，并且将结果显示出来，其实现的代码如下所示。

DECLARE 
@MaxAge INT,
@MinAge INT,
@AvgAge INT
SELECT
@MaxAge=MAX(年龄),
@MinAge=MIN(年龄),
@AvgAge=AVG(年龄)
FROM tb_ygxx

–查询结果

SELECT DISTINCT
@MaxAge AS '最大年龄',@MinAge AS '最小年龄',@AvgAge AS '平均年龄'

代码执行以后，其执行结果如图12.7所示。
在这里插入图片描述
图12.7 DECLARE语句的应用示例

5．WAITFOR语句
WAITFOR语句指定延迟一段时间（时间间隔或一个时刻）来执行（触发）一个Transact-SQL语句、语句块、存储过程或事务。
【语法说明】
WAITFOR语句的语法如下所示。

WAITFOR{ DELAY 'time_to_pass' | TIME 'time_to_execute'  }

在上述代码中：

  • DELAY指定可以继续执行批处理、存储过程或事务之前必须经过的时段，最长可为24小时。
  • TIME指定运行批处理、存储过程或事务的时间。

WAITFOR语句用来暂时停止程序执行，直到所设定的等待时间已到或者已过才继续往下执行，时间必须为DATETIME类型的数据，且不允许指定DATETIME值的日期部分。
【上机实战】
将员工信息表（tb_ygxx）中员工姓名为“李娜”的籍贯更改为“吉林省辽源市”，但是要求该操作在语句执行以后延迟2个小时在执行，其实现的代码如下所示。

BEGIN
  WAITFOR DELAY '02:00'
  UPDATE tb_ygxx
  SET 籍贯='吉林省辽源市'
  WHERE 姓名='李娜'
END

代码执行以后，系统会一直处于等待状态，直到2个小时之后才执行更新数据的操作，如图12.8所示。
在这里插入图片描述
图12.8 WAITFOR语句的应用示例
也可以使用WAITFOR语句定时修改数据信息，如下面的示例所示。
下面的代码演示的是，将员工信息表（tb_ygxx）中员工姓名为“李娜”的籍贯更改为“吉林省辽源市”，并且要求该操作等到晚上20:37在执行。

BEGIN
   WAITFOR TIME '20:37'
  UPDATE tb_ygxx
  SET 籍贯='吉林省辽源市'
  WHERE 姓名='李娜'
END

代码执行以后，等到晚上20:37的时候，员工信息表（tb_ygxx）中员工姓名为“李娜”的籍贯被更改为“吉林省辽源市”。

6．PRINT语句
PRINT语句的作用是在屏幕上显示字符信息，并且显示字符信息的最大长度为255个字符。
【语法说明】
PRINT语句的语法格式如下所示。

PRINT {str_expression |@Local_Var | @Global_Var }

代码中的str_expression表示的是字符表达式，@Local_Var表示的是局部变量，@Global_Var表示的是全局变量。
【上机实战】
使用PRINT语句在屏幕上输出“努力学好SQL语言！”几个字符。
PRINT ‘努力学好SQL语言！’
代码执行以后，其执行结果如图12.9所示。
在这里插入图片描述
图12.9 PRINT语句的应用示例
通过PRINT语句还可以输出局部变量中的信息，如下面的示例所示。
从员工信息表（tb_ygxx）中查询所有员工的最大年龄、最小年龄和平均年龄，并且将其结果通过PRINT语句在屏幕上显示出来，其实现的代码如下所示。

DECLARE 
@MaxAge INT,
@MinAge INT,
@AvgAge INT
SELECT
@MaxAge=MAX(年龄),
@MinAge=MIN(年龄),
@AvgAge=AVG(年龄)
FROM tb_ygxx

–查询结果

PRINT '最大年龄：' + str(@MaxAge)
PRINT '最小年龄：' + str(@MinAge)
PRINT '平均年龄：' +str(@AvgAge)

代码执行以后，其执行结果如图12.10所示。
在这里插入图片描述
图12.10 通过PRINT语句输出变量中的值
7．RETURN语句
RETURN语句的作用是用来无条件地退出一个SELECT语句或者一个过程。
RETURN语句的语法格式如下所示。
【语法说明】

RETURN [int_expression]

代码中的int_expression表示返回一个状态值，该值为整数。
【上机实战】
当变量@Nums加1之后，终止操作。

DECLARE 
@Nums INT
SELECT @Nums=1
WHILE(@Nums!=0)
BEGIN
 SELECT @Nums = @Nums + 1
 SELECT @Nums AS '查询结果'
 RETURN
END

代码执行以后，其执行结果如图12.11所示。
在这里插入图片描述
图12.11 RETURN语句的应用示例

8．WHILE语句
WHILE语句通过布尔表达式设置重复执行SQL语句或语句块的循环条件。WHILE命令在设定的条件成立时，会重复执行SQL语句或程序块。可以使用BREAK和CONTINUE关键字在循环内部控制WHILE循环中语句的执行。WHILE语句也可以嵌套。
【语法说明】
WHILE循环结构的语法可表示如下。

 WHILE <布尔表达式>
 BEGIN
       <SQL语句或程序块>
       [BREAK]
       [CONTINUE]
       [SQL语句或程序块]
    END

【上机实战】
更新员工工资（tb_yggz）数据表中的数据，即如果员工的平均工资少于1600元，则将所有员工的工资不断提高一个百分点，直到员工工资达到1600元。这时，就可以使用WHILE循环语句来实现，其实现的代码如下所示。
为了便于比较，现将员工工资（tb_yggz）数据表中的数据信息显示出来。

select * from tb_yggz

代码执行以后，其执行结果如图12.12所示。
在这里插入图片描述
图12.12 员工工资（tb_yggz）数据表中的数据信息
WHILE循环结构语句的应用示例代码如下所示。

WHILE(SELECT AVG(yggz) FROM tb_yggz)<1600	    --如果平均工资少于1600元
	BEGIN
      UPDATE tb_yggz
         SET yggz = yggz*1.01					--平均工资提高1%
      IF (SELECT MAX(yggz) FROM tb_yggz)>2000	--如果最高工资高于2000元
        BREAK									--跳出循环
      ELSE
        CONTINUE								--继续循环
	END

代码执行以后，查询员工工资（tb_yggz）数据表中的数据信息如图12.13所示。
在这里插入图片描述
图12.13 WHILE循环结构语句的应用示例
此时可以查询所有员工的平均工资，如下面的代码所示。
select avg(yggz) AS 平均工资 from tb_yggz
代码执行以后，可以看到所有员工的平均工资超过了1600元，如图12.14所示。
在这里插入图片描述
图12.14 查看员工的平均工资
12.2.2 ORACLE中的流程控制语句
在Oracle的PL/SQL中，控制流语句主要包括条件语句、循环语句和GOTO语句等，下面分别介绍一下这几种处理流程的语句结构。

  1. IF条件语句
    在Oracle中，IF语句主要有3种形式的表示形式，其具体的语法结构如下所示。
    （1）IF条件语句表达形式1。
    【语法说明】

    IF<布尔表达式>
    THEN PL/SQL语句
    END IF；

【上机实战】
假设变量sAge表示的是员工的年龄，通过该变量判断员工的年龄，判断之后输出判断结果，其实现的代码如下所示。

IF sAge<20
THEN dbms_output.put_line('员工的年龄偏低'）
END IF;

代码执行以后，如果变量的值小于20，则输出“员工的年龄偏低”的提示信息，否则如果变量的值大于等于20，则语句执行后，则没有执行结果。
（2）IF条件语句表达形式2。
【语法说明】

IF<布尔表达式>
THEN PL/SQL语句
ELSE PL/SQL语句
END IF；

【上机实战】
使用IF条件语句表达式形式2的表示方法来判断员工的年龄，其实现的代码如下所示。

IF sAge<20
THEN dbms_output.put_line('该员工的年龄偏低'）
ELSE dbms_output.put_line('该员工的年龄符合规定'）
END IF;

代码执行以后，如果变量的值小于20，则输出“该员工的年龄偏低”的提示信息，否则提示“该员工的年龄符合规定”的提示信息。
（3）IF条件语句表达形式3。
【语法说明】

IF<布尔表达式>
THEN PL/SQL语句
ELSE IF <布尔表达式>
THEN PL/SQL语句
END IF；

【上机实战】
使用IF条件语句表达式形式3的表示方法来判断员工的年龄。

IF sAge<18
THEN dbms_output.put_line('该员工的年龄偏低'）
ELSE IF sAge>60 
THEN dbms_output.put_line('该员工的年龄偏高'）
ELSE dbms_output.put_line('该员工的年龄符合规定'）
END IF;

代码执行以后，如果变量的值小于20，则输出“该员工的年龄偏低”的提示信息，否则如果变量的值大于60，则输出“该员工的年龄偏高”的提示信息，否则提示“该员工的年龄符合规定”的提示信息。
从上述3个示例可以看出，从IF条件语句表达形式1到IF条件语句表达形式3中，IF语句的判断条件越来越细致、严谨。

  1. LOOP循环语句
    在Oracl中，LOOP语句用于循环控制。
    【语法说明】
    LOOP语句格式如下所示。

    LOOP
    PL/SQL语句;
    END LOOP;

【上机实战】
使用LOOP循环语句将1-9的自然数输出，其实现的代码如下所示。

declare
  m number;
  x number;
begin
  m:=0;
  loop
   m:=m+1;
   dbms_output.put_line('自然数：' || to_char(m));
   exit when x=9;
  end loop;
end;

代码执行以后，从1到9的自然数依次被输出，结果如下所示。

自然数：1
自然数：2
自然数：3
自然数：4
自然数：5
自然数：6
自然数：7
自然数：8
自然数：9

  1. FOR循环语句
    在Oracle中，也经常会用到FOR循环语句。
    【语法说明】
    FOR循环的语句格式如下所示。

    FOR loop_counter IN[REVERSE] low_bound … high_bound LOOP
    Statements;
    END LOOP;

【上机实战】
使用FOR循环语句将1-9的自然数输出，其实现的代码如下所示。

begin
  for I IN 1 .. 9 loop
  dbms_output.put_line('自然数：' || to_char(I));
  end loop;
end;

代码执行以后，从1到9的自然数依次被输出，结果如下所示：

自然数：1
自然数：2
自然数：3
自然数：4
自然数：5
自然数：6
自然数：7
自然数：8
自然数：9

  1. WHILE循环语句
    在Oracle中，WHILE循环语句也经常会被用到。
    【语法说明】
    WHILE循环语句的格式如下所示。

    WHILE <布尔表达式>
    LOOP
    PL/SQL语句；
    END LOOP；

【上机实战】
使用WHILE循环语句将1-9的自然数输出。

declare
  m number;
begin
  m:=1;
  while m<=9 loop
   dbms_output.put_line('自然数：' || to_char(m));
   m:=m+1;
  end loop;
end;

代码执行以后，从1到9的自然数依次被输出，结果如下所示。

自然数：1
自然数：2
自然数：3
自然数：4
自然数：5
自然数：6
自然数：7
自然数：8
自然数：9

  1. GOTO语句
    GOTO语句用于无条件跳转到指定的标号位置。
    【语法说明】
    GOTO语句的格式如下所示。
    GOTO label;
    ….
    <

    declare
   m number;
 begin
   m:=0;
   loop
    m:=m+1;
    dbms_output.put_line('输出自然数：' || to_char(m));
    if m>9 then
      Goto endloop;
    end if;
   end loop;
   <end loop>
   dbms_output.put_line('自然数输出结束');
 end;

代码执行以后，输出的结果如下所示。

输出自然数：1
输出自然数：2
输出自然数：3
输出自然数：4
输出自然数：5
输出自然数：6
输出自然数：7
输出自然数：8
输出自然数：9
自然数输出结束

12.3 创建存储过程
创建存储过程的目的是为了完成某一特定的功能。本节中主要向读者讲解创建存储过程的相关知识。
12.3.1 创建存储过程
创建存储过程使用CREATE PROCEDURE语句来实现。通常情况下，只有数据库的拥有者具有创建存储过程的权限。
【语法说明】
创建存储过程的语句结构如下所示。

CREATE PROCEDURE proce_name [;version number]
[{@parameter data_type}
[VARYING][=default value][OUTPUT]
][,…,N]
[WITH
 { RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION}]
[FOR REPLICATION]
AS sql_statement […n]

可以通过GRANT语句给创建的存储过程权限授予用户，其实现的语句结构如下所示。

GRANT CREATE PROCEDURE User_name

代码中的各项参数说明如下所示。

  • proce_name：表示存储过程的名称，规定名称最多可定义为128个字符。
  • version
    number：表示的是版本号，在创建相同名称的存储过程时，可以通过指定不同的版本号来区分所创建的存储过程。在执行存储过程时，通过指定版本号执行相对应的存储过程。
  • @parameter：表示存储过程的参数名。存储过程被创建之后，在调用该存储过程时，如果没有定义参数的缺省值，则用户必须定义出所有的参数值。在定义存储过程中的参数时不能用列名、表名或其他数据库中的对象名称，但可以用存储过程中的变量名。一个存储过程最多能有1024个参数。
  • data_type：表示参数的数据类型。在存储过程中如果参数为游标数据类型，则该参数必须被指定为VARING和OUTPUT。
  • VARYING：仅应用于游标型参数，表示的是指定由OUTPUT参数支持的结果集。
  • default value：表示参数的缺省值。
  • OUTPUT：表示该参数是一个返回参数。OUTPUT参数在存储过程中可以变化，其作用是向调用者返回信息。
  • RECOMPILE：表示SQL Server并不保存该存储过程中的执行计划，也就是说存储过程每执行一次都要进行重新编译一次。
  • ENCRYPTION：将SYSCOMMENTS表中的存储过程条目进行加密，从而防止用户查看编译后的语句。
  • FOR REPLICATION：表示指明该存储过程只能在复制过程中被执行。
  • sql_statement：表示存储过程中的SQL语句。

【上机实战】
创建一个简单的存储过程Show_GOODS，通过该存储过程可以查询货物信息表（T_GOODS）中的数据条数，其实现的代码如下所示。

create or replace procedure show_goods
as
begin
  declare nums number;
  begin
  select count(*) into nums from t_goods;
  dbms_output.put_line('数据表中的记录数' || nums);
  end;
end show_goods;

代码执行以后，存储过程被创建，此时在SQL Developer中可以看到创建的存储过程，如图12.16所示。
在这里插入图片描述
图12.16 查看创建的存储过程

注意：在给存储过程命名时，名称中如果使用“sp_”前缀。DBMS首先会在Master数据库中寻找存储过程，这样就会影响存储过程的执行效率。因此，在命名存储过程名称时应该尽量不使用以“sp_”开头的存储过程名称。

下面来看一下存储过程show_GOODS的执行效果。
右击存储过程show_GOODS，然后在弹出的菜单中选择“Test”命令，如图12.17所示。
在这里插入图片描述
图12.17 执行存储过程show_GOODS
按下“F8”键，执行存储过程。存储过程执行完成之后在输出窗口中的“DBMS Output”分页中可以查看执行结果，如图12.18所示。
在这里插入图片描述
图12.18 存储过程show_GOODS的执行结果
上面演示的是如何通过存储过程查询数据，下面再通过一个示例来讲解如何通过存储过程更新数据表中的数据信息。
创建一个名称为EDIT_GOODS的存储过程，通过该存储过程可以更新货物信息（T_GOODS）中的数据信息，其实现的代码如下所示。

CREATE OR REPLACE PROCEDURE EDIT_GOODS
AS
begin
--更新数据
UPDATE t_goods
SET g_value=2.4
WHERE g_value<2.4;
end EDIT_GOODS;

代码执行以后，存储过程被创建，如果执行该存储过程，将会更新货物信息表（T_GOODS）中的数据信息。
下面来看一下存储过程EDIT_GOODS的执行效果。为了便于比较，现将执行存储过程之前货物信息（T_GOODS）中的数据信息显示出来。

select * from t_goods

代码执行以后，其执行结果如图12.19所示。
在这里插入图片描述
图12.19 执行存储过程前货物信息（T_GOODS）中的数据信息
右击存储过程EDIT_GOODS，然后在弹出的菜单中选择“Test”命令，按下“F8”键，执行存储过程。存储过程执行之后，查看货物信息（T_GOODS）中的数据信息。

select * from t_goods

代码执行以后，其执行结果如图12.20所示。
在这里插入图片描述
图12.20 执行存储过程之后货物信息（T_GOODS）中的数据信息
从上述的执行结果中可以看出，存储过程执行以后，货物信息（T_GOODS）中货物单价小于2.4的单价信息都被修改为2.4，而这个操作是由存储过程EDIT_GOODS来完成的。

12.3.2 创建带有参数的存储过程
在前面讲解的示例中，创建的存储过程都不带有参数，从本节开始将向读者讲解有关带参数存储过程的创建方法。
存储过程中的参数可以分为输入参数和输出参数两种。用户可以通过输入参数把参数的值输入到存储过程当中，数据信息也可以通过存储过程中的输出参数从存储过程中返回。
存储过程当中的输入输出参数必须以@提示符开始，并且都是T-SQL中合法的数据类型。同时，输出参数名称的后面还必须带有OUTPUT关键字。存储过程可以在应用程序当中被调用执行，并且接受用户输入的参数，同时根据输入参数的值，将执行结果通过一个或多个输出参数返回。
【上机实战】
创建一个存储过程SEL_GOODS。并且通过向存储过程中输入参数值来按照货物地点（G_PLACE）查询货物信息表（T_GOODS）中的数据记录条数，其实现的代码如下所示。

create or replace procedure SEL_GOODS(G_PLACE1 in varchar2)
AS
begin
  declare nums number;
  begin
  select count(*) into nums from t_goods where g_place=G_PLACE1;
  dbms_output.put_line('数据表中的记录数' || nums);
  end;
end SEL_GOODS;

代码执行以后，存储过程被创建。此时可以向存储过程中输入“货物地址”参数值来查询货物信息表（T_GOODS）中的数据条数，创建的存储过程及其参数如图12.21所示。
在这里插入图片描述
图12.21 创建带有输入参数的存储过程
下面来看一下存储过程SEL_GOODS的执行效果。右击存储过程SEL_GOODS，然后在弹出的菜单中选择“Test”命令，此时将弹出存储过程的执行页面，在该页面的“g_place1”参数中输入参数值“吉林省长春市”，如图12.22所示。
在这里插入图片描述
图12.22 输入存储过程的参数值
按下“F8”键，执行存储过程。存储过程执行之后，在输出窗口中的“DBMS Output”分页中可以查看存储过程的执行结果，如图12.23所示。
在这里插入图片描述
图12.23 查看存储过程的执行结果
12.3.3 创建带返回参数的存储过程
在创建带有参数的存储过程时，存储过程输出参数名称的后面只要带有OUTPUT关键字，则创建的存储过程就带有返回参数。
【上机实战】
创建一个存储过程Show_yggzxx，通过该存储过程可以分析员工信息表（t_employ）中所有员工的工资情况。该存储过程中带有4个参数，分别是输入参数@bm（部门）、输出参数@max_gz（最高工资）、输出参数@min_gz（最低工资）和输出参数@avg_gz（平均工资），其实现的代码如下所示。

CREATE OR REPLACE PROCEDURE Show_yggzxx(bm in varchar2,max_gz out float,min_gz out float,avg_gz out float)
AS
begin
SELECT max(e_gz) into max_gz FROM t_employ where e_bm=bm;
SELECT min(e_gz) into min_gz FROM t_employ where e_bm=bm;
SELECT avg(e_gz) into avg_gz FROM t_employ where e_bm=bm;
IF (avg_gz <=20) then
 dbms_output.put_line('员工的总体工资偏高!' || '最高工资：' || max_gz || '最低工资' || min_gz || '平均工资' || avg_gz);
 end if;
IF (avg_gz >=20 and avg_gz <35) then
 dbms_output.put_line('员工的总体工资合理!' || '最高工资：' || max_gz || '最低工资' || min_gz || '平均工资' || avg_gz);
end if;
IF (avg_gz >=35) then
 dbms_output.put_line('员工的总体工资偏低!' || '最高工资：' || max_gz || '最低工资' || min_gz || '平均工资' || avg_gz);
 end if;
end Show_yggzxx;

代码执行以后，存储过程Show_yggzxx被创建，展开创建的存储过程，可以看到存储过程中包含有4个参数，如图12.24所示。
在这里插入图片描述
图12.24 创建带有返回参数的存储过程
下面来看一下存储过程Show_yggzxx的执行效果。右击存储过程Show_yggzxx，然后在弹出的菜单中选择“Test”命令，此时将弹出存储过程的执行页面，在该页面的“bm”参数中输入参数值“采购部”，如图12.25所示。
在这里插入图片描述
图12.25 输入存储过程的输入参数值
按下“F8”键，执行存储过程，执行之后的返回结果如图12.26所示。
在这里插入图片描述
图12.26 执行存储过程之后的返回结果
此时在输出窗口中的“DBMS Output”分页中可以查看执行结果，如图12.27所示。
在这里插入图片描述
图12.27 查看存储过程的执行结果

12.4 删除存储过程
当存储过程不在需要的时候，可以将其删除。删除存储过程通过DROP命令来实现，使用DROP命令可以从当前数据库中删除一个或多个存储过程。
【语法说明】
其实现的语法格式如下所示：

DROP PROCEDURE { pro_name }
[,…n]

代码中的pro_name表示存储过程的名称。
【上机实战】
删除存储过程Show_GOODS，其实现的代码如下所示。

DROP PROCEDURE Show_GOODS

代码执行以后，存储过程Show_GOODS被删除。

HeartBest丶
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
GBase 8s 存储过程解析之DECLARE
sinat_33233315的博客
02-23 658
GBase 8s 存储过程解析之DECLARE
第十三章：存储过程与函数
12-14
二、存储过程的创建 三、存储函数的创建 四、存储过程的调用与删除 五、存储函数的调用与删除 六、存储过程与函数的比较 一、概述 存储在数据库中的被命名的PLSQL块，供所有用户程序调用，完成特定功能的子程序。 ...
存储过程中的declare
a794679468的博客
08-24 2297
存储过程的使用（三）使用DECLARE定义局部变量
热门推荐
程序员ken的博客
07-06 3万+
使用DECLARE定义局部变量 在流程语句的分析中，我们在存储过程中使用变量的声明与设置，由于这些变量也只能在存储过程中使用，因此也称为局部变量，变量的声明可以使用以下语法： DECLARE 变量名[,变量名2...] 数据类型(type) [DEFAULT value]; DECLAREnumINTDEFAULT 10 ; -- 定义变量num，数据类型为INT型，默认值为10 DECLARE mult_a,mult_b int；//定义多个变量 其中...
存储过程语法及实例
sunnygrass2的专栏
02-17 285
存储过程如同一门程序设计语言，同样包含了数据类型、流程控制、输入和输出和它自己的函数库。 --------------------基本语法-------------------- 一.创建存储过程create procedure sp_name()begin.........end 二.调用存储过程1.基本语法：call sp_name()注意：存储过程名称后面必须加括号，哪怕该存储过程...
第十二章-应用程序接口API.doc
11-23
【第十二章-应用程序接口API】是关于MATLAB与外部应用程序交互的重要知识。MATLAB作为一个强大的科学计算软件，除了自身的丰富功能，还具备优秀的开放性。开放性体现在它支持多种专业工具包，并提供了应用程序接口...
第十二章电子商务项目跟踪与控制.ppt
11-12
【第十二章 电子商务项目跟踪与控制】 电子商务项目跟踪与控制是确保项目按预定目标有效推进的关键环节。项目跟踪管理涉及一系列活动，旨在通过实时、连续的信息收集和处理，为项目管理者提供关于项目执行状态的...
第十二章农业机械的信息化与精细农业.ppt
11-12
【第十二章 农业机械的信息化与精细农业】 随着20世纪下半期电子信息科学技术的飞速发展，农业机械的创新也开始与之紧密结合，引入了大量的先进技术。这些技术不仅提升了农业机械的性能，还实现了精确操作，改善了...
学习网页设计与制作第12章Flash基础.ppt
11-12
【网页设计与制作第12章Flash基础】的讲解涵盖了Flash设计的核心概念和操作，旨在帮助初学者掌握这款强大的动画制作工具。以下是本章详细的知识点解析： 12.1 使用Flash资源 Flash自带丰富的资源库，包括图像、动画...
浅谈MySQL存储过程中declare和set定义变量的区别
09-09
下面小编就为大家带来一篇浅谈MySQL存储过程中declare和set定义变量的区别。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧
mysql存储过程之游标（DECLARE）原理与用法详解
12-15
本文实例讲述了mysql存储过程之游标（DECLARE）原理与用法。分享给大家供大家参考，具体如下： 我们在处理存储过程中的结果集时，可以使用游标，因为游标允许我们迭代查询返回的一组行，并相应地处理每行。mysql的游标为只读，不可滚动和敏感三种模式，我们来看下： 只读：无法通过光标更新基础表中的数据。 不可滚动：只能按照select语句确定的顺序获取行。不能以相反的顺序获取行。 此外，不能跳过行或跳转到结果集中的特定行。 敏感：有两种游标：敏感游标和不敏感游标。敏感游标指向实际数据，不敏感游标使用数据的临时副本。敏感游标比一个不敏感的游标执行得更快，因为它不需要临时拷贝数据。但是
存储过程-----DECLARE---实用注意事项
weixin_34258078的博客
11-08 411
定义与使用变量时需要注意以下几点：◆ DECLARE语句必须用在DEGIN…END语句块中，并且必须出现在DEGIN…END语句块的最前面，即出现在其他语句之前。◆ DECLARE定义的变量的作用范围仅限于DECLARE语句所在的DEGIN…END块内及嵌套在该块内的其他DEGIN…END块。◆ 存储过程中的变量名不区分大小写。 ◆在 MySQL 命令行下运行 “delimiter //;...
存储过程(declare,set,lpad,concat,while)
weixin_44694538的博客
08-18 541
DECLARE 定义变量declare ------------------SET赋值-------INTO 查询结果赋值给变量 DECLARE语句必须用在DEGIN…END语句块中，并且必须出现在DEGIN…END语句块的最前面，即出现在其他语句之前。 DECLARE定义的变量的作用范围仅限于DECLARE语句所在的DEGIN…END块内及嵌套在该...
存储过程的使用（四）-- 使用DECLARE 定义条件和处理程序
程序员ken的博客
07-07 1285
定义条件和处理程序是事先定义程序执行过程中可能遇到的问题，并且可以在处理程序中定义解决这些问题的办法，可以简单理解为异常处理，这种方式可以提前预测可能出现的问题，并提出解决办法，从而增强程序健壮性，避免程序异常停止。MySQL通过DECLARE关键字来定义条件和处理程序。 定义条件 MySQL中可以使用DECLARE关键字来定义条件。其基本语法如下： -- 条件定义语法 DECLAREcondition_nameCONDITIONFORcondition_value...
MySQL----存储过程
最新发布
gaoqiandr的博客
11-28 2452
在MySQL中有一种结构可以帮助我们快速进行想要的操作，反之操作反复浪费时间同时也减少了与服务器的传输，减少数据在数据库和应用服务器之间的传输，对于高效数据处理 的效率是有好处的。而本文便是讲解了该结构。
mysql存储过程之变量（创建（DECLARE）和赋值（SET））
luyaran的博客
07-09 3万+
    我们都知道，变量是一个命名数据对象，变量的值可以在存储过程执行期间更改。我们接下来就尝试使用存储过程中的变量来保存直接/间接结果。 这些变量是存储过程的本地变量，但是我们得注意，变量必须先声明后，才能使用它。而我们如果要在存储过程中声明一个变量，可以使用DECLARE语句，来看下sql语法：DECLARE variable_name datatype(size) DEFAULT defau...
MySQL存储过程中declare和set定义变量的区别
dianluo4096的博客
11-06 143
　　在存储过程中常看到declare定义的变量和@set定义的变量。简单的来说，declare定义的类似是局部变量，@set定义的类似全局变量。 　　1、declare定义的变量类似java类中的局部变量，仅在方法中生效。即只在存储过程中的begin和end之间生效。 　　2、@set定义的变量，叫做会话变量，也叫用户定义变量，在整个会话中都起作用(比如某个应用的一个连接过程中...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

HeartBest丶

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值