KStudio快速开发指南.pdf
KStudio快速开发指南.pdf
KStudio 快速开发指南 金仓数据库管理系统 KingbaseES 文档版本：V9（V009R001C001B0024） 发布日期：2023 年 10 月 12 日 北京人大金仓信息技术股份有限公司 目 目 录 录 第 1 章 前言 1 1.1 适用读者 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.2 相关文档 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.3 术语 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 1.4 手册约定 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 第 2 章 准备工作 2.1 3 运行数据库开发管理工具 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 2.1.1 Windows 系统启动运行数据库开发管理工具 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 2.1.2 Linux 系统启动运行数据库开发管理工具 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 第 3 章 连接到数据库 4 第 4 章 创建数据库 9 第 5 章 建模式 12 第 6 章 建表 15 6.1 新建表面板来创建表 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 6.2 新建查询来创建表 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 6.3 网上商城其他表信息 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 6.3.1 地址表 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 6.3.2 商品类别表 category . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 6.3.3 购物车表 cart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 6.3.4 订单表 orders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 6.3.5 订单明细表 orderitem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 6.3.6 收货信息表 shopping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 6.3.7 日志信息表 loginfo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 6.4 设置外键 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 第 7 章 添加测试数据 31 37 7.1 手工添加数据 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 7.2 文件导入添加数据 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 I 目 录 7.3 新建查询添加数据 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 7.4 其他新增记录 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 第 8 章 数据库开发 8.1 函数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 8.1.1 新建函数-添加购物车 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 8.1.2 执行函数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 执行结果查看 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 8.1.3 函数调试 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 8.1.4 其他函数的信息 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 8.1.2.1 8.2 存储过程 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 8.2.1 新建存储过程-下单 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 8.2.2 执行存储过程 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 执行结果查看 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 8.2.3 调试存储过程 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 8.2.4 其他存储过程 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 视图 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 新建视图-最受用户喜爱的前十名商品 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 查询视图 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 新建视图-订单和订单地址表合并查看 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 查询视图 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 触发器 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 8.4.1 新建触发器-新建订单触发添加日志记录 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 8.4.2 执行触发器 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 程序包 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 8.5.1 新建程序包-统计功能程序包 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 8.5.2 调试程序包 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 8.2.2.1 8.3 8.3.1 8.3.1.1 8.3.2 8.3.2.1 8.4 8.5 52 第 9 章 备份与还原 113 9.1 逻辑备份 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 9.2 逻辑还原 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114 第 10 章 结束语 118 版权声明 119 服务周期承诺 120 II 第 1 章 前言 1 第 章 前言 欢迎使用本手册，KStudio 是一款功能强大的数据库管理工具，可为数据库开发人员、DBA 提供数据库开发、 调试、维护等各项功能，完美支持 KingbaseES 数据库。本手册旨在利用一个简易的网上商城案例，通过 KStudio 完 成网上商城的数据库设计工作，从而让开发人员快速上手 KStudio 数据库开发工具。 网上商城的功能是支持多用户下单购买不同的商品，并邮寄到不同的收货地址，因而需要有用户表、商品表、订 单表等表结构来存储基础数据，同时定义一个视图以方便查询销量前十的商品。为在多平台（iOS、Android 或微信 小程序等）开发时节省类似的业务逻辑代码，将网上商城的关键操作抽象成函数或存储过程，业务开发时直接调用函 数或存储过程即可，抽象出来操作有新增商品到购物车、下订单等，同时将一些统计类的函数封装到程序包内方便调 用。网上商城的日志信息主要通过触发器完成，不需要程序主动干预，降低开发成本。 下面就以该网上商城的案例来演示 KStudio 数据库开发工具的使用。 前言部分包含以下主题： • 适用读者 • 相关文档 • 术语 • 手册约定 1.1 适用读者 KStudio 快速开发手册面向所有使用 KingbaseES 的用户，主要是数据库管理员和应用程序开发人员。 在开始阅读本文档之前，您需要了解以下内容： • 《KingbaseES 数据库开发指南》 1.2 相关文档 无 1 第 1 章 前言 术语 1.3 无 1.4 手册约定 本文档中可能出现“注意、提示、警告、另请参阅”等标志，它们所代表的含义如下： 注意: 用于突出重要/关键信息、最佳实践等。 提示: 用于突出小窍门、捷径等。 警告: 用于传递设备或环境安全警示信息，若不避免，可能会导致设备损坏、数据丢失、设备性能降低或其 它不可预知的结果。 另请参阅: 用于突出参考、参阅等。 以下程序代码书写约定适用于本文档： 符号 说明 [] 表示包含一个或多个可选项。不需要输入中括号本身。 {} 表示包含两个以上（含两个）的候选，必须在其中选取一个。不需要输入花括号本身。 | 分割中括号或者花括号中的两个或两个以上选项。不需要输入“|”本身。 ... 表示其之前的元素可以被重复。 斜体 表示占位符或者需要提供特定值的变量。 大写 表示系统提供的元素，以便与用户定义的元素相互区分。除出现在方括号中的元素外，应当按 照顺序逐字输入。当然，部分元素在系统中是大小写不敏感的，因此用户可以根据系统说明以 小写形式输入。 小写 表示由用户提供的元素。 2 第2章 准备工作 2 第 章 准备工作 完成 KingbaseES 数据库和数据库开发管理工具（KStudio）的部署安装。 Linux 系统详见《基于 Linux 系统的数据库软件安装指南》。 Windows 系统详见《基于 Windows 系统的数据库软件安装指南》。 2.1 运行数据库开发管理工具 2.1.1 Windows 系统启动运行数据库开发管理工具 Windows 中启动数据库开发管理工具的三种方式如下所示： 1. 选择『开始』|『程序』|『KingbaseES V9』|『Client Tools』|『数据库开发管理工具』，启动数据库开发管理 工具。 2. 在 Windows 资源管理器中进入到数据库安装目录下 $InstallDir/ClientTools/guitools/KStudio，KStudio.exe 或 KStudio.exe 上右键打开，启动数据库开发管理工具，此方式只限于 windows 版本。 3. 命令行进入到安装目录下 $InstallDir/ClientTools/guitools/KStudio，执行 KStudio.exe，启动数据库开发管理 工具。 2.1.2 Linux 系统启动运行数据库开发管理工具 Linux 中启动数据库开发管理工具的方式如下所示： 1. 命令行进入到安装目录下 $InstallDir/ClientTools/guitools/KStudio，执行./KStudio，启动数据库开发管理工 具。 3 第3章 3 第 章 连接到数据库 连接到数据库 首先双击打开 KStudio，点击工具栏上的“连接”按钮以连接到 KingbaseES 数据库服务器，如下图所示： 在打开的连接窗口上设置连接信息，选择“KingbaseES”，点击“下一步”： 4 第3章 连接到数据库 填写 KingbaseES 数据库服务器的信息（system 是默认的管理员用户，为了安全起见，可以填入用于网上商城案 例的普通用户进行数据库操作）： 5 第3章 连接到数据库 点击“测试链接”按钮，测试是否能连接成功，测试失败请确认上一步中的 KingbaseES 数据库信息是否填写准 确，测试成功如下图所示： 6 第3章 连接到数据库 之后点击“完成”按钮完成数据库的连接，左侧“数据库导航”树出现新创建的连接，如下图所示。 依次点击“展开”按钮展开该数据库的详细信息，展开后如下图所示，可以看到数据库的详细信息： 7 第3章 连接到数据库 至此，已经连接到要操作的 KingbaseES 数据库，后续的所有操作都是在该数据库上进行的；KStudio 是支持同 时连接多个 KingbaseES 数据库的，连接方式也是如此，操作时需要注意选中的是哪个数据库。 8 第 4 章 创建数据库 4 第 章 创建数据库 网上商城案例使用名为“onlinemarket”的数据库来存储其具体信息，因此在创建表、创建视图等操作之前先要 创建用于该项目的数据库。 首先，在数据库导航树上展开找到“数据库”节点，右键选择“新建数据库”选项，如下图所示： 找到新出现的“新建数据库”操作面板，在“基本属性”->“名称”上填写数据库的名称“onlinemarket”，保 持默认选择的所属用户、数据库编码方式。 9 第 4 章 创建数据库 填写完成后点击“确定”按钮，KStudio 弹出创建成功的对话框： 同时左侧“数据库导航”树上出现新创建的数据库，如下所示： 10 第 4 章 创建数据库 至此，网上商城案例的数据库创建完成。若需要创建其他数据库，也是一样的操作。 11 第5章 建模式 5 第 章 建模式 网上商城示例将放置到“market”模式下，区别于 KingbaseES 数据库预置的其他模式，以方便后续的管理操 作。 首先在左侧“数据库导航”树上，展开“onlinemarket”数据库的子节点，在“模式”节点上右键“新建模 式”，详见下图： 然后，在右侧“新建模式”面板上填写模式的名称“market”，点击“确定”按钮创建完成。 12 第5章 建模式 点击“确定”按钮之后 KStudio 会提示“生成模式成功”，如下图所示。 最后，在左侧“数据库导航”树的模式节点，可以看到新创建的模式“market”，如下图所示： 13 第5章 建模式 至此，网上商城的模式创建完毕，下面将进行该示例的具体数据库对象的创建。 14 第 6 章 建表 6 第 章 建表 本章节包含以下内容： • 新建表面板来创建表 • 新建查询来创建表 • 网上商城其他表信息 • 设置外键 创建表的方式有两种，一种是通过 KStudio 提供的新建表面板来创建表，另一种是借助 KStudio 的新建查询来 实现。分别以网上商城案例中的用户表、商品表为例，演示两种不同的建表方式。 表关系结构图如下所示： 15 第 6 章 建表 16 第 6 章 建表 6.1 新建表面板来创建表 假定网上商城中的用户表定义如下表所示，KStudio 是怎样将其在数据库中创建出来呢？ 列名 列类型 说明 userid serial 用户 id username varchar(50) 用户名 password varchar(50) 密码 phone varchar(20) 联系电话 createtime datetime 创建时间 updatetime datetime 更新时间 首先，在左侧“数据库导航”树中节点数据库->onlinemarket-> 模式->market-> 表上右键单击选择“新建 表”，见下图所示： 17 第 6 章 建表 然后，就会出现下图所示的界面，在这里可以设置表的基本属性、分区、约束等信息，用户表只需要关心基础属 性和字段设置，其他默认即可。在基本属性内填入表名 user，然后在字段区域设置用户表的各个字段信息，字段的类 型可在下拉列表中选择。 18 第 6 章 建表 其中，比如 userid 字段的添加是先点击“新增”按钮，在新出现的一行记录上填写名称，选择类型，是否为空选 择否。 其次，用户表的其他字段参考上述方式添加后，用户表的信息已经填写完成，如下图所示。 19 第 6 章 建表 添加主键约束，到左侧选项卡上选择“约束”面板，如下图所示： 点击上方的“新增”按钮，填写内容如下，主键包含的列通过如下图所示方式选择，可以设置联合主键，此处选 20 第 6 章 建表 择 userid 作为主键列，接下去的每一个表都会创建主键约束。 最后，点击“确定”即可完成用户表的创建，弹出成功的提示信息如下所示： 至此，用户表已经创建完成，通过该方式可以创建其他的表，下面演示另一种创建表的方式。 21 第 6 章 建表 6.2 新建查询来创建表 假定网上商城中的商品表定义如下表所示，可以通过 SQL 语句将其创建出来。 列名 列类型 说明 proid serial 商品编号 cateid int4 类别编号 name varchar 商品名称 detail text 商品详情 price numeric(20,2) 商品价格 stock int4 商品库存 status int2 商品状态：1 在售，0 下架 createtime datetime 创建时间 updatetime datetime 更新时间 首先，单击左侧“数据库导航”树中节点数据库->onlinemarket-> 模式->market，再点击顶部的“新建查询” 按钮，会出现查询窗口，如下所示： 然后，在查询窗口内输入下表中的创建商品表的 SQL 语句，然后点击“执行”按钮即可创建该商品表，注意底 部的面板会有执行结果的显示，若执行失败可以查看具体原因。 CREATE TABLE "market"."product" ( "proid" serial, "cateid" integer NOT NULL, "name" varchar NOT NULL, "detail" text NULL, "price" numeric(20,2) NOT NULL, "stock" integer NOT NULL, "status" smallint NOT NULL, "createtime" datetime NOT NULL, "updatetime" datetime NOT null, 22 第 6 章 建表 CONSTRAINT "market_product_constraint_1" PRIMARY KEY (proid) ); 23 第 6 章 建表 24 第 6 章 建表 最后，在左侧“数据库导航”树的“表”节点中可以看见刚刚创建的商品表，若没有主动刷新出来，可以在 “表”节点上右键选择“刷新”按钮，这样就可看到新创建的商品表了。 至此，商品表已经通过新建查询的方式创建完成，其他表的创建也可以通过这种方式来完成。值得一提的是“新 建查询”功能提供的 SQL 编辑器功能强大，不仅仅可以执行创建表的操作，其他任何 SQL 操作都可以在这里完成， 底部面板也会实时显示执行结果；同时也支持执行计划、执行日志的操作，更加详尽的功能说明请参见《KStudio 数 据库开发工具用户使用手册》。 网上商城示例还需要其他表的支持，在这里将分别以表格的形式列出，并给出相应的创建表的 SQL 语句，但通 过 KStudio 创建的过程不再一一表述。 25 第 6 章 建表 6.3 网上商城其他表信息 6.3.1 地址表 列名 列类型 说明 addressid serial 地址编号 userid integer 用户编号 realname varchar(50) 收货人姓名 telephone varchar(20) 收货人手机号 province varchar(20) 收货人省份 city varchar(20) 收货人市 district varchar(20) 收货人区/县 address text 收货人详细地址 CREATE TABLE "market"."address" ( "addressid" serial, "userid" integer NOT NULL, "realname" character varying(50 char) NOT NULL, "telephone" character varying(20 char) NOT NULL, "province" character varying(20 char) NOT NULL, "city" character varying(20 char) NOT NULL, "district" character varying(20 char) NOT NULL, "address" text NOT NULL, CONSTRAINT "market_address_constraint_1" PRIMARY KEY (addressid) ); 26 第 6 章 建表 6.3.2 商品类别表 category 列名 列类型 说明 catid serial 类别编号 parentid integer 父类别编号 name varchar(50) 类别名称 status int2 类别状态：1 可用 0 弃用 setorder integer 类别排序 createtime datetime 创建时间 updatetime datetime 更新时间 CREATE TABLE "market"."category" ( "catid" serial, "parentid" integer NOT NULL, "name" character varying(50 char) NOT NULL, "status" smallint NOT NULL, "setorder" integer NULL, "createtime" datetime NOT NULL, "updatetime" datetime NOT NULL, CONSTRAINT "market_category_constraint_1" PRIMARY KEY (catid) ); 6.3.3 购物车表 cart 列名 列类型 说明 carid serial 购物车编号 userid int4 用户 id proid int4 商品 id quantity int4 数量 checked bool 是否选中 createtime datetime 创建时间 updatetime datetime 更新时间 27 第 6 章 建表 CREATE TABLE "market"."cart" ( "carid" serial, "userid" integer NOT NULL, "proid" integer NOT NULL, "quantity" integer NOT NULL, "checked" boolean NOT NULL, "createtime" datetime NOT NULL, "updatetime" datetime NOT NULL, CONSTRAINT "market_cart_constraint_1" PRIMARY KEY (carid) ); 6.3.4 订单表 orders 列名 列类型 说明 orderid serial 订单编号 userid int4 用户编号 payment numeric(20,2) 实付金额 status int2 订单状态：1-已取消，2-未付款，3-已付款，4-已发货，5-交易成功，6-交易关闭 paymenttime datetime 支付时间 sendtime datetime 发货时间 endtime datetime 订单完成时间 closetime datetime 交易关闭时间 createtime datetime 创建时间 updatetime datetime 更新时间 CREATE TABLE "market"."orders" ( "orderid" serial, "userid" integer NOT NULL, "payment" numeric(20,2) NOT NULL, "status" smallint NOT NULL, "paymenttime" datetime NULL, "sendtime" datetime NULL, "endtime" datetime NULL, "closetime" datetime NULL, "createtime" datetime NOT NULL, 28 第 6 章 建表 "updatetime" datetime NOT NULL, CONSTRAINT "market_orders_constraint_1" PRIMARY KEY (orderid) ); 6.3.5 订单明细表 orderitem 列名 列类型 说明 id serial 子订单编号 orderid int4 订单编号 userid int4 用户编号 proid int4 产品编号 proname varchar(50) 产品名称 currentunitprice numeric(20,2) 创建订单时的单价 quantity int4 产品数量 totalprice numeric(20,2) 产品总价 createtime datetime 创建时间 updatetime datetime 更新时间 CREATE TABLE "market"."orderitem" ( "id" serial, "orderid" integer NOT NULL, "userid" integer NOT NULL, "proid" integer NOT NULL, "proname" character varying(50 char) NOT NULL, "currentunitprice" numeric(20,2) NOT NULL, "quantity" integer NOT NULL, "totalprice" numeric(20,2) NOT NULL, "createtime" datetime NOT NULL, "updatetime" datetime NOT NULL, CONSTRAINT "market_orderitem_constraint_1" PRIMARY KEY (id) ); 29 第 6 章 建表 6.3.6 收货信息表 shopping 列名 列类型 说明 shoppingid serial 收货信息编号 userid int4 用户编号 orderid int4 订单编号 receivername varchar(50) 收货人姓名 receiverphone varchar(20) 收货人电话 receiverprovince varchar(20) 收货人省份 receivercity varchar(20) 收货人市 receiverdistrict varchar(20) 收货人区 receiverstreet text 收货人详细地址 createtime datetime 创建时间 updatetime datetime 更新时间 CREATE TABLE "market"."shopping" ( "shoppingid" serial, "userid" integer NOT NULL, "orderid" integer NOT NULL, "receivername" character varying(50 char) NOT NULL, "receiverphone" character varying(20 char) NOT NULL, "receiverprovince" character varying(20 char) NOT NULL, "receivercity" character varying(20 char) NOT NULL, "receiverdistrict" character varying(20 char) NOT NULL, "receiverstreet" text NOT NULL, "createtime" datetime NOT NULL, "updatetime" datetime NOT NULL， CONSTRAINT "market_shopping_constraint_1" PRIMARY KEY (shoppingid) ); 30 第 6 章 建表 6.3.7 日志信息表 loginfo 列名 列类型 说明 logid serial 日志编号 loglevel int2 日志级别，0-信息，1-警告，2-错误 logtype varchar(20) 日志类型 logdetail text 日志详情 logtime datetime 日志创建时间 CREATE TABLE "market"."loginfo" ( "logid" serial, "loglevel" smallint NOT NULL, "logtype" character varying(20 char) NOT NULL, "logdetail" text NULL, "logtime" datetime NOT NULL, CONSTRAINT "market_loginfo_constraint_1" PRIMARY KEY (logid) ); 6.4 设置外键 针对 product 表设置外键，product 表中的分类编号是与 category 表相关联的，在 product 表上右键选择“编辑 表”，如下图所示： 31 第 6 章 建表 在左侧“外键”选项卡上点击，如下图所示： 点击“新建”按钮，设置“列”，点击如下图所示的... 32 第 6 章 建表 弹出如下界面，选择“cateid”到右侧，点击“确定”按钮，“列”字段值选择完毕 “依赖表”选择 market.category，如下图所示 33 第 6 章 建表 注意此 category 表需要已经设置了主键约束或唯一约束，如果没设置约束则依赖表不进行显示。 然后选择“依赖列”，点击…按钮，在弹出框内选择 cateid 34 第 6 章 建表 点击“确定”按钮，即可创建外键完成。 其他外键的创建： 1. orderitem 表上创建外键，“列”为 orderid，“依赖表”为 market.orders，“依赖列”为 orderid，如下图所 示： 35 第 6 章 建表 2. shopping 表上创建外键，“列”为 orderid，“依赖表”为 market.orders，“依赖列”为 orderid，如下图所 示： 3. address 表上创建外键，“列”为 userid，“依赖表”为 market.user，“依赖列”为 userid，如下图所示： . 以上外键创建完毕。 36 第7章 7 第 章 添加测试数据 添加测试数据 现在，网上商城案例的所有表结构已经创建完毕了，下一步需要创建视图、函数、触发器等数据库对象，但在创 建这些数据库对象之前，先灌入一些测试数据，方便后面的功能验证。 灌入数据的方式可以分为三种: • 第一种是手工添加数据 • 第二种是通过文件导入添加数据 • 第三种是通过新建查询添加数据 • 其他新增记录 7.1 手工添加数据 这种方式适合于添加几条简易数据，操作比较直观但效率低下。 首先在左侧“数据库导航”树上，依次展开直到出现用户表，在用户表 user 上右键选择“编辑数据”，如下图 所示： 37 第7章 添加测试数据 38 第7章 添加测试数据 然后，在打开的 user 表的编辑面板中，点击“添加新行”按钮，增加用户表 user 的一条用户记录，如下图所 示： 然后，在带有底色的那条记录内，填入下表所示的用户信息： userid Username password phone createtime updatetime 1 zhangsan 123456 15999999999 2022-04-28 15:00:00 2022-04-28 15:00:00 最后，填写完之后点击左上角的“保存”按钮进行记录的保存操作，如下图所示： 现在，用户表的这条记录已经保存成功，可以在左侧“数据库导航”树的用户表 user 上右键-> 查询数据检验该 记录是否存在，如下图所示。 39 第7章 添加测试数据 40 第7章 添加测试数据 KStudio 会自动显示出查询结果，具体如下图所示： 现在，成功通过这种手工的方式添加了一条记录到用户表 user 中，并演示了“查询数据”功能。 7.2 文件导入添加数据 KStudio 支持更高效的文件导入（具体可参考《KStudio 数据库开发工具用户使用手册》），可以方便快速导入 大量数据用于后面的函数、存储过程的测试，或是应用程序的测试。 以 csv 格式文件为例，将文件内容导入到用户表中，首先需要创建一个 csv 文件“userdata.csv”，第一行为用 户表的列名，将要灌入的每条记录按照列名顺序按行写入该 csv 文件，比如该 csv 文件内容如下所示： 然后在左侧“数据库导航”树的用户表 user 上右键选择“导入数据”，如下图所示： 41 第7章 添加测试数据 42 第7章 添加测试数据 然后在弹出的界面中选择导入类型为 csv，点击下一步，如下图所示： 再然后，点击下一步选择刚刚创建的的 csv 文件“userdata.csv”，如下图所示： 43 第7章 添加测试数据 选定 csv 文件后，KStudio 会弹出如下对话框，确认字段分隔符等信息后，点击“下一步”： 44 第7章 添加测试数据 KStudio 弹出“表映射”窗口，确认是要导入的用户表信息，点击“下一步”继续操作： 45 第7章 添加测试数据 KStudio 弹出“数据传输设置”界面，这里保持默认选项即可，点击“下一步”继续执行： 46 第7章 添加测试数据 KStudio 最终弹出“检查结果”界面，在这个界面可以做最后的数据导入的检查，确认无误后点击“开始”按钮 进行导入操作，如下图所示： 47 第7章 添加测试数据 最后，数据导入成功。 至此，通过 csv 形式导入用户表操作完成，可以在左侧“数据库导航”树中的用户表 user 上右键选择“查询数 据”，发现用户表中的数据是有对应增加的。 48 第7章 7.3 添加测试数据 新建查询添加数据 在上一章节中提到“新建查询”功能支持任意 SQL 的支持，所以也可以通过该功能做测试数据的导入。 以网上商城的地址表 address 为例，需要增加记录如下表所示： addressid userid realname telephone province city district address 1 1 张三 17688889999 山东省 青岛市 市北区 中海大厦 2 1 李四 15988889999 山东省 青岛市 市南区 五四广场 首先，点击 KStudio 顶部的“新建查询”按钮，如下图所示： 然后，在打开的脚本编辑页面编写 INSERT SQL 语句（详见下表）进行插入数据的操作，编写完成后点击“执 行”按钮，如下图所示： 49 第7章 添加测试数据 INSERT INTO "market"."address" ("userid", "realname", "telephone", "province", "city", "district", "address") VALUES(1, '张三', '17688889999', '山东省', '青岛市', '市北区', '中海大厦'); INSERT INTO "market"."address" ("userid", "realname", "telephone", "province", "city", "district", "address") VALUES(1, '李四', '15988889999', '山东省', '青岛市', '市南区', '五四广场'); 最后可以通过上一小节提到的“查询数据”功能查看刚刚插入的新记录，见下图所示。 至此，三种添加表记录的方式介绍完毕，为了后续网上商城案例的函数、触发器等功能的正常测试，需要灌入额 外的一些数据，比如商品表新增一些商品，商品分类表也要增加新类别等，这些操作不再赘述，表记录信息参考下一 小节，请自行添加到相应的表中。 50 第7章 添加测试数据 其他新增记录 7.4 商品分类表 category 增加如下记录： catid parentid name status setorder createtime updatetime 1 0 水果 0 1 2022-04-28 15:00:00 2022-04-28 15:00:00 2 0 衣服 0 2 2022-04-28 15:00:00 2022-04-28 15:00:00 商品表 product 增加如下记录，留意 cateid 字段的值设置过外键，此列值需要在商品分类表 category 中存在： proid cateid name 1 1 2 2 detail price stock status createtime updatetime 草莓 10.9 70 1 2022-04-28 15:00:00 2022-04-28 15:00:00 T恤 50 80 1 2022-04-28 15:00:00 2022-04-28 15:00:00 51 第 8 章 数据库开发 8 第 章 数据库开发 本章节包含以下内容： • 函数 • 存储过程 • 视图 • 触发器 • 程序包 8.1 函数 网上商城主要的功能是用户选择商品添加到购物车，挑选结束后提交订单。商品加入购物车的操作可以抽象成一 个函数，通过加入购物车操作可以了解到如何通过 KStudio 来进行函数的添加。 8.1.1 新建函数-添加购物车 加入购物车函数的具体信息如下表所示： 函数 addtocart 功 能 描 商品加入购物车 述 操 作 的 购物车表：每条记录对应了某个用户加入到购物车的某一件商品、该商品的数量和是否被选中要购买 表 的标记 实 现 原 往购物车表内增加记录，或修改现有记录内的商品数量，并选中该商品要购买 理 52 第 8 章 数据库开发 下面演示该函数的创建过程，首先，在左侧“数据库导航”树上“数据库”->“onlinemarket”->“模式”-> “market”->“函数”上右键选择“新建函数”，如下图所示： KStudio 出现新建函数的界面，在其“基本属性”面板上填写函数名称“addtocart”，返回值类型为 bool，它 表示加入购物车是否成功；模式和属主保持默认值，如下图所示： 依据加入购物车函数的说明，需要如下表所述的三个参数，因此在“参数面板”上增加函数的这三个参数，如下 图所示： 53 第 8 章 数据库开发 参数名 参数类型 参数模式 参数说明 入参示例 user_id integer IN 用户编号，针对哪个用户进行加入购物车的操作 1 product_id integer IN 加入购物车的商品编号 1 product_quantity integer IN 加入购物车的商品数量 2 然后，在“SQL 内容”窗口填入该函数的具体 SQL 语句（见下表）： AS tempcount integer default 0; begin select count(1) into tempcount from cart where userid=user_id and proid=product_id; if tempcount > 0 then--如果购物车中已经有此商品，则将购物车内此商品的数量进行增加 update cart set quantity=quantity+product_quantity where userid=user_id and proid=product_id; else -- 购物车中无此商品，那么就新增一条购物车商品数据 insert into "cart" (userid, proid, quantity, checked, "createtime", "updatetime") values(user_id, product_id, product_quantity, true, now(), now()); end if; return true; exception when others then return false; END 54 第 8 章 数据库开发 此时，完成了加购物车函数的创建，整体截图如下： 55 第 8 章 数据库开发 最后，点击“确定”按钮添加完成，弹出创建成功的对话框如下所示。 56 第 8 章 数据库开发 这就是整个函数的创建过程，最后可以使用 KStudio 的“函数调用”功能验证一下该函数是否可以执行成功。 8.1.2 执行函数 在 左 侧 “数 据 库 导 航” 树 的 “数 据 库”->“onlinemarket”->“模 式”->“market”->“函 数”->“addtocart”节点上右键选择“执行函数”： 57 第 8 章 数据库开发 在弹出的对话框内填写入参信息，如下图所示： 58 第 8 章 数据库开发 然后，点击“确定”按钮进入到函数调用的脚本页面，点击“执行”按钮进行“加入购物车”函数调用操作，如 下图所示： 函数执行后打印输出为“t”如下图所示，表示加入购物车成功： 59 第 8 章 数据库开发 8.1.2.1 执行结果查看 “函数执行”功能演示完毕，现在查看 cart 表的记录是否有添加，有则证明添加至购物车操作成功。在“数据 库”->“onlinemarket”->“模式”->“market”->“表”-> “cart”节点上右键选择“查询数据” 60 第 8 章 数据库开发 打开后界面如下，购物车内新增了一条记录： 61 第 8 章 数据库开发 至此，创建函数、函数执行的功能已经演示完毕，其他函数也可以仿照上述步骤完成创建或执行。 8.1.3 函数调试 针对上面刚刚创建的 addtocart 函数进行调试，首先准备数据，上一步已经将商品编号为 1 的商品加入到用户 编号为 1 的用户购物车内，此记录保持住，代表某个用户已经加入某个商品到购物车了，如果再次执行加入购物车操 作，则应跳转到增加购物车中此商品数量的分支上去执行。 以上购物车数据准备完成，下面进行“添加购物车 addtocart”函数的调试步骤 在“数据库”->“onlinemarket”-> 模式->“market”->“函数”->“addtocart”上右键选择“调试”选项 62 第 8 章 数据库开发 输入参数，和前面操作的购物车记录吻合，仍然是用户 1 添加 n 件商品 id 为 1 的商品到购物车（这里设置的 n 件为 3 件），输入参数如下图所示： 63 第 8 章 数据库开发 点击“确定”进入到调试页面，整体截图如下： 64 第 8 章 数据库开发 点击工具栏中的“开始调试” 按钮，进行函数的调试，点击后界面发生如下变化 65 第 8 章 数据库开发 执行工具栏中的“单步跳过” 按钮执行到下一步 继续单击“单步跳过”按钮进行参数变量的赋值 66 第 8 章 数据库开发 继续单击“单步跳过”按钮执行到 returnVar:= market.addtocart(user_id,product_id,product_quantity); 在这里单击“单步跳入”按钮 跳入到 addtocart 函数的代码执行界面 67 第 8 章 数据库开发 单击“单步跳过”，右上角的“变量”面板查看 tempcount 的值，因为购物车中之前添加了一条记录，所以此值 应该为 1 68 第 8 章 数据库开发 此时 tempcount 的值大于 0，则应该执行 update 操作更新购物车中的商品数量，单击“单步跳过”查看运行到 哪一步，如下图所示运行到 update 命令执行了更新操作。 继续“单步跳过”一直到此函数运行完成。运行结束后，到购物车表中查看商品数量是否有增加，如下图所示商 品数量增加为 5 了： 69 第 8 章 数据库开发 8.1.4 其他函数的信息 在商品详情展示的时候，需要显示商品的当月销售量 函数 monthsale 功能描述 商品的当月销量 操作的表 订单详情表 实现原理 统计某个商品的从月初 1 号开始的销售量 创建过程参考上一章节，内容填写如下： “基础属性”面板填写名称（monthsale）、返回值类型（integer 类型，代表返回当月的销量值）、模式和属主 保持默认 函数参数代表指定的商品 id，目的就是为了获取此指定商品的月销量： 参数名 参数类型 参数模式 说明 入参示例 pro_id integer IN 针对哪个商品获取月销量 1 函数 SQL 内容： AS quantity integer; 70 第 8 章 数据库开发 recordcount integer default 0; begin select count(1) into recordcount from orderitem where createtime>trunc(sysdate,'mm') and proid=pro_id; if recordcount > 0 then select sum("quantity") into quantity from orderitem where createtime>trunc(sysdate,'mm') group by proid having proid=pro_id limit 1; --trunc() 获取当月的第一天 return quantity; else return 0; end if; END 整体截图如下： 点击“确定”按钮完成添加： 71 第 8 章 数据库开发 调用函数，在左侧“数据库导航”树节点“数据库”->“onlinemarket”-> 模式->“market”->“函数”-> “monthsale”上右键选择“执行函数”： 72 第 8 章 数据库开发 输入入参为 1，表示获取 id 为 1 的商品的月销量： 73 第 8 章 数据库开发 点击“确定”按钮后，出现脚本窗口，点击执行按钮即可执行此函数，输出商品 1 的当月销量： 74 第 8 章 数据库开发 8.2 存储过程 用户针对购物车中的商品进行下单操作，这里我们将下单抽象出一个存储过程，通过创建新订单的操作可以了解 如何通过 KStudio 来创建存储过程。 8.2.1 新建存储过程-下单 下订单的存储过程具体描述如下。 75 第 8 章 数据库开发 存 储 neworder 过程 功 能 购物车中选中的商品进行下单操作 描述 操 作 商品表：商品库存减少指定的数量 的表 购物车表：对应选中的商品记录进行清空 订单表：增加订单记录 订单地址表：增加订单地址记录 订单详情表：增加订单详情记录 实 现 传入用户编号和地址编号，会将用户对应的购物车内选中的商品进行下单操作 原理 下面介绍存储过程的创建过程： 在“数据库导航”树节点“数据库”->“onlinemarket”->“模式”->“market”->“存储过程”上右键选择 “新建存储过程”选项 76 第 8 章 数据库开发 创建面板上“基础属性”填写名称为“neworder”，模式和属主保持默认值 “参数”面板增加四个参数，前两个为入参，后两个为出参，入参：user_id（用户编号，针对哪个用户的购物 车进行下单操作），address_id（用户下单时选择的地址编号），success 作为 OUT 出参，代表新订单创建是否成 功，orderid 出参（如果成功创建订单，则返回订单的 id），编写参数如下图所示： 77 第 8 章 数据库开发 表 8.2.1: 参数 参数名 参数类型 参数模式 参数说明 入参示例 user_id integer IN 用户编号 1 address_id integer IN 地址编号 1 success bool OUT 下单成功与否 orderid integer OUT 返回订单编号 SQL 内容： AS declare cart_ids integer[]; mypro_id integer; myquantity integer; mystock integer; totalpayment decimal(20,2) default 0.00; myprice decimal(20,2) default 0.00; pro_ids integer[]; quantitys integer[]; realname varchar(50); telephone varchar(50); province varchar(50); city varchar(50); district varchar(50); address text; shoppingid_int integer; orderid_int integer; myproid integer; myproname varchar(50); myproprice decimal(20,2) default 0.00; 78 第 8 章 数据库开发 BEGIN /** * 1. 获取用户购物车内选择的商品, 将相应信息放到数组中 * 2. 将商品的库存减去购物车内设置的值 * 3. 将订单表增加一条订单记录 * 4. 将订单详情表增加购物车内的商品 */ cart_ids := array(select carid from cart where userid=user_id and checked=true); if(array_length(cart_ids,1) > 0) then for i in 1..array_length(cart_ids,1) loop select proid,quantity into mypro_id,myquantity from cart where carid=cart_ids[i]; delete from cart where carid=cart_ids[i];-- 清空购物车 pro_ids[i] = mypro_id; quantitys[i] = myquantity; select stock,price into mystock,myprice from product where proid=mypro_id; if mystock-myquantity<0 then raise exception '库存量不足'; else update product set stock=stock-myquantity where proid=mypro_id; totalpayment := totalpayment+myprice*myquantity; end if; end loop; insert into orders(userid,payment,status,createtime,updatetime) values (user_id, totalpayment,2,now(),now()) returning orderid into orderid_int; select realname,telephone,province,city,district,address into realname,telephone,province,city,district, address from address where addressid=address_id limit 1; -- 增加订单收货地址表信息 insert into shopping(userid,orderid,receivername,receiverphone,receiverprovince,receivercity, receiverdistrict,receiverstreet,createtime,updatetime) values (user_id,orderid_int,realname,telephone,province,city,district,address,now(),now()); -- orderitem 表进行增加 for i in 1..array_length(pro_ids,1) loop select proid,name,price into myproid,myproname,myproprice from product where proid=pro_ids[i]; insert into orderitem(orderid,userid,proid,proname,currentunitprice,quantity,totalprice,createtime, updatetime) values (orderid_int,user_id,myproid,myproname,myproprice,quantitys[i],quantitys[i]*myproprice,now(),now()); end loop; success = true; 79 第 8 章 数据库开发 orderid = orderid_int; else raise notice '购物车为空'; success = false; end if; exception when others then success = false; END 整体截图如下： 点击右下角的“确定”按钮创建完成存储过程。 80 第 8 章 数据库开发 以上关于“添加新订单”的存储过程创建完毕，可以使用“执行存储过程”的方式调用创建的存储过程，调用完 毕后可以去相应的订单表、订单详情表中查看数据是否有对应的变动。 8.2.2 执行存储过程 在 左 侧 “数 据 库 导 航” 树 的 “数 据 库”->“onlinemarket”->“模 式”->“market”->“存 储 过 程”-> “neworder”节点上右键，选择“执行存储过程”, 如下图所示： 81 第 8 章 数据库开发 入参填写如下，代表 id 为 1 的用户使用 id 为 1 的地址进行下单操作： 82 第 8 章 数据库开发 在脚本中增加一行 raise notice '创建订单：%，订单 id 为：%',success,orderid; 可以查看订单是否创建成功，以及创建成功后的订单 id： 83 第 8 章 数据库开发 如果购物车为空，则先执行将商品“加入购物车”的操作，执行“addtocart”函数。然后再执行此新建订单的 操作。 8.2.2.1 执行结果查看 查看执行结果，到 orders 表、orderitem 表中查看记录是否有增加，cart 表是否删除了对应的数据，product 表 的库存量是否有减少，使用“新建查询”功能即可完成，这里不再截图展示。 8.2.3 调试存储过程 存储过程的调试步骤和函数的调试步骤一致。 84 第 8 章 数据库开发 8.2.4 其他存储过程 其他存储过程也是上面的方式去新建即可，下面简述其他的存储过程。 存储过程 categorysales 功能描述 统计某个类别的商品们在指定时间内的销售额 操作的表 订单详情表：统计某个类别商品们的销售额 实现原理 传入商品分类 id，以及开始结束时间，会将此分类商品在指定时间内的销售额计算出来 同上一章节的步骤，关于内容填写如下： 基本属性填写： 参数，注意 totalprices 是 OUT 模式的，代表输出的销售额： 85 第 8 章 数据库开发 参数名称 参数类型 参数模式 说明 参数示例 categoryid integer IN 分类编号 1 starttime datetime IN 统计金额的开始时间 2022-04-20 00:00:00 （根据实际情况填写） endtime datetime IN 统计金额的结束时间 2022-05-07 00:00:00 （根据实际情况填写） totalprices decimal OUT 某类别商品销售的总金额 SQL 内容： AS prices decimal(20,2)[]; begin totalprices := 0.0; prices := array(select totalprice from orderitem where proid in (select proid from product where cateid=categoryid) and createtime>starttime and createtime