新建存储过程

概述

存储过程是一条或多条语句的集合,对数据库进行一系列复杂操作时,存储过程可以在数据库内将这类复杂操作封装成一个代码块,以便重复使用,从而减少数据库开发人员的工作量。

存储过程旨在完成特定功能的 SQL 语句集,经编译创建并保存在数据库中,用户可以通过指定存储过程名并指定所需参数来调用执行,利用存储过程可以加速 SQL 语句的执行。

新建存储过程,即由用户创建并能够完成某一特定功能的存储过程,存储过程可以包含参数和返回值。

存储过程与函数的区别

  • 函数的返回值显示具体结果值,而存储过程的返回值仅指明执行是否成功。

  • 可以直接通过 SELECT 语句调用函数,而存储过程的调用需要使用 CALL 语句。

存储过程作用

  • 提高应用程序的通用性和可移植性:新建存储过程完成后,可以在程序中被多次调用,无需重新编写该存储过程的 SQL 语句,且支持修改存储过程,对程序源代码无影响,从而提高程序的可移植性。

  • 提高 SQL 执行速度:存储过程编译完成,如果某一个操作包含大量的 SQL 代码或者分别被执行多次,则使用存储过程比直接使用单条 SQL 语句执行速度更快速。

  • 减轻服务器的负担:在进行数据库对象操作时,如果使用单条调用的方式,则网络上必须传输大量的 SQL 语句;如果使用存储过程,则可以直接发送过程的调用命令,从而降低网络负担。

新建存储过程-概述

如上图所示,创建存储过程包含以下 5 个步骤:

  1. 指定存储过程名称

  2. 添加参数

  3. 确定新建存储过程

  4. 编辑存储过程

  5. 完成新建存储过程

操作步骤

以在 ODC 中创建部门预算存储过程(proc_total)为例,存储过程 proc_total 中包含 budget_r 和 budget_s 两个 INT 类型的输入参数,计算两个参数之和。具体操作步骤如下:

步骤 1:指定存储过程名称

在 OceanBase 开发者中心(OceanBase Developer Center,ODC)单击连接名进入连接后,在左导航栏中单击 存储过程 标签可以查看存储过程列表。在存储过程列表的右上角,单击 + 创建存储过程,或是在顶部导航栏中单击 新建 > 存储过程 以创建所需的对象。

新建存储过程 中,输入存储过程名称。

数据库对象-新建存储对象

步骤 2:添加参数

  1. 参数即调用函数时传入的信息,需指定参数信息:

    • Oracle 模式:需指定参数的 名称模式类型 默认值

    • MySQL 模式: 需指定参数的 名称模式类型长度

  2. 添加函数提供三种功能操作:

    功能项

    说明

    弹窗引导辅助菜单

    通过弹窗引导辅助菜单,可添加、删除、上下移动参数。

    单击表序号

    • 单击表序号,选中整行,显示辅助菜单(删除、上/下移动)。

    • 单击并选中表序号,可拖动整行参数以调整顺序。

    右键操作

    • 拖动鼠标选中整行,右键单击鼠标,进行复制行 / 向下移动一行。

    • 选择单元格,右键单击鼠标,进行复制。

  3. 参数 中需指定的 模式 指参数类型

    MySQL/Oracle 模式下支持三种参数模式 IN(输入)、OUT(输出)、INOUT(输入输出):

    CREATE PROCEDURE proc_name ([[IN |OUT |INOUT ] parameter_name parameter_type...])
    • 参数说明

      参数

      类型

      IN输入参数。

      调用存储过程时将参数的值传入存储过程供执行存储过程时使用。

      IN 类型参数一般只用于传入,在调用存储过程中一般不作修改和返回。

      OUT输出参数。

      调用存储过程时,存储过程会忽略输出参数本身的值并传入一个空值,执行结束后,输出参数会被赋予存储过程对其修改的值。一般用输出参数获取存储过程的执行结果数据。

      OUT 类型参数可以用于调用存储过程中需要修改和返回值。

      INOUT输入输出参数。

      同时具备输入参数和输出参数的功能。

    • 参数设置

      属性

      是否必填

      默认

      模式

      名称

      必填

      Oracle/MySQL

      模式

      必填

      IN

      Oracle/MySQL

      类型

      必填

      VARCHAR

      Oracle/MySQL

      长度

      必填

      45

      MySQL

      默认值

      非必填

      Oracle

步骤 3:确定新建存储过程

单击 确定,进入 SQL 创建确认页面。

步骤 4:编辑存储过程

新建存储过程-操作步骤

在 SQL 创建确认页面 ,编辑存储过程语句。

同时,在编辑页面的工具栏中提供了以下功能键:

功能

说明

格式化

单击该按钮对选中的 SQL 语句或当前 SQL 窗口中的所有 SQL 语句进行统一的格式化(缩进、换行和高亮关键字等操作)。

查找/替换

在查找框内输入内容以对脚本进行检索,查找后可在替换框内输入内容以对查找内容进行替换。

撤销

将脚本回退到上一步操作执行的结果。

重做

执行 撤销 操作后,对脚本重新执行撤销前的操作。

大小写

提供 全部大写全部小写首字母大写 三种效果,为脚本中选中的语句转换为对应形式。

缩进

提供 添加缩进删除缩进 两种效果,为脚本中选中的语句添加或删除包含的缩进。

注释

提供 添加注释删除注释 两种效果,将脚本中选中的语句转换为注释或转换为 SQL 语句。

用户可以在 SQL 创建确认页面编辑创建的存储过程语句。语法格式如下:

CREATE PROCEDURE proc_name (
  [proc_parameter[,...]]) 
IS
BEGIN
proc_body:
  Valid SQL routine statement
END [end_label]

参数说明:

参数

说明

proc_name

存储过程的名称,默认在当前数据库中创建。

如需在特定数据库中创建存储过程,需要在名称前加上数据库名称,即 db_name.sp_name。

注意

名称应当尽量避免选取与 MySQL 内置函数相同的名称,否则会发生错误。

[proc_parameter[,…] ]

存储过程的参数列表,包括 [IN|OUT|INOUT] parameter_name parameter_type

其中,parameter_name 为参数名,parameter_type 为参数的类型(可以是任何有效的 MySQL 数据类型)。当有多个参数时,参数列表中彼此间用逗号分隔。存储过程可以无参数(此时存储过程的名称后仍需加上一对括号),也可以有 1 个或多个参数。

注意

参数名避免与数据表的列名相同,否则存储过程的 SQL 语句会将参数名看作列名,从而可能出错。

proc_body

存储过程的主体部分,包含在过程调用的时候必须执行的 SQL 语句。

此部分以 BEGIN 开始,END 结束。如存储过程体中只有一条 SQL 语句,则可以省略 BEGIN-END 标志。

步骤 5:完成新建存储过程

单击 创建,完成新建存储过程后,可以如同调用系统内置函数一样,使用关键字 CALL 调用用户自定义的存储过程。

创建存储过程-步骤五-1
说明

在左侧导航栏中,右键鼠标单击存储过程列表中的存储过程名,通过弹出的管理操作列表(包括 查看新建编辑运行下载删除刷新 等),可快速管理和操作目标对象。

存储过程具体操作,请参见 管理存储过程

语法格式:

CALL proc_name ([proc_parameter [,...]]);

示例:

CALL proc_total (30000, 20000);
新建存储过程-操作步骤五-调用存储过程

相关信息

阿里云首页 云数据库 OceanBase 相关技术圈