人大金仓数据库KingbaseES PLSQL基本对象学习与总结

关键字：

KingbaseES、PLSQL、基本对象

1.PLSQL基本对象

PLSQL基本对象包括匿名块、存储过程、函数、触发器和包。

2.匿名块

1.匿名块概念

●无程序块名声明。

●如果没有变量需定义，则DECLARE部分可省略。

●以BEGIN开始，以END结束，不可省略。

2.基本语法

[DECLARE]
        /* 声明部分—声明变量、常量等*/
BEGIN
         /*执行部分—PL/SQL语句和SQL语句*/
[EXCEPTION]
         /*异常处理部分—处理运行错误*/
 END; --块结束标记

 set serveroutput on;
\set SQLTERM /
DECLARE
   v1 int :=100;
   v2 varchar(50) :=‘kingbase’;
BEGIN
   DBMS_OUTPUT.PUT_LINE(v1);
   DBMS_OUTPUT.PUT_LINE(v2);
END;
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

3.示例

运行结果：

3.存储过程

1.存储过程概念

●相当于客户端的一段 SQL 批处理程序。

●无返回值。

●可接受多个参数，参数模式包括IN、OUT、INOUT三种。

●被存储在数据库中，供用户随时调用(sys_proc)。

2.基本语法

CREATE [OR REPLACE] PROCEDURE PRO_NAME[(PARA1,PARA2…)]
AS
    VAR_NAME VAR_TYPE;
BEGIN
     --statements
[EXCEPTION]
END;
1
2
3
4
5
6
7

3.存储过程调用

●使用call调用：call procedure_name();

●匿名块中直接使用procedure_name()调用
1
2

BEGIN
     procedure_name()
END;
1
2

4.删除存储过程：DROP PROCEDURE procedure_name;

\set SQLTERM /
CREATE OR REPLACE PROCEDURE pr1(p1 int) AS
   v1 int :=100;
   v2 varchar(50) :=‘kingbase’;
BEGIN
   DBMS_OUTPUT.PUT_LINE(v1+p1);
   DBMS_OUTPUT.PUT_LINE(v2);
END;
/
CALL pr1(200);
/
DROP PROCEDURE pr1;
/
1
2
3
4
5
6
7
8
9
10
11
12

5.示例

运行结果：

4.函数

1.函数概念

●这里所说的函数是指用户定义的函数。

●可有返回值。

●被存储在数据库中，供用户随时调用(sys_proc)。

2.基础语法

CREATE [OR REPLACE] FUNCTION <function name>[(param1,param2)]  return datatype IS
BEGIN
--statemements
return values;
[EXCEPTION]
END;
1
2
3
4
5
6

3.函数调用

●使用SELECT调用：SELECT function_name; SELECT function_name FROM DUAL;

DECLARE
      v1 datatype;
BEGIN
      v1 := function_name();
END;
1
2
3
4

●匿名块内调用函数，定义一个变量接收函数返回值：

4.函数删除：DROP FUNCTION function_name;

\set SQLTERM /
CREATE OR REPLACE FUNCTION func1(p1 int) return int AS
   v1 int :=100;
   v2 varchar(50) :=‘kingbase’;
BEGIN
   DBMS_OUTPUT.PUT_LINE(v1+p1);
   DBMS_OUTPUT.PUT_LINE(v2);
   RETURN 1;
END;
/
SELECT func1(200);
/
DECLARE
   v1 int;
BEGIN
   v1 :=func1(200);
END;
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

5.示例

运行结果

5.触发器

1.触发器概念

一种特殊的存储过程，定义了一些在数据库相关事件（如：select、update、delete等事件）发生时应执行的功能代码。

2.触发器的使用场景

●启用/禁用对表和视图的修改

●强制数据一致性

●提供审计和日志记录

●防止无效的事务处理

●启用复杂的业务逻辑

3.触发器的组成

●触发事件：引发触发器被触发的事件DML语句（ INSERT、UPDATE、 DELETE语句对表和视图执行数据处理操作）

●触发时间：即该触发器是在触发事件发生前（BEFORE）还是之后（AFTER）触发

●触发操作：触发器触发后要完成的事情

●触发对象：包括表、视图

●触发条件：有WHEN子句指定一个逻辑表达式，只有当该表达式为TRUE时，遇到触发事件才会自动执行触发操作

●触发频率：说明触发器内定义的动作被执行的次数。即语句级触发器和行级触发器

创建一张表goods用于存储商品种类，表结构如下：
create table goods(id int primary key,good_name varchar(50));
●创建一个序列用于后续数据
create sequence seq_id;
●创建一个行级触发器，该触发器在表goods插入数据时被触发，并在该触发器的主体中实现goods表id列的值。
●创建一个行级触发器
CREATE OR REPLACE TRIGGER tri_insert_good BEFORE INSERT ON goods FOR EACH ROW
BEGIN
   SELECT seq_id.nextval INTO :new.id FROM DUAL;
END;
/
●触发触发器：向表goods中插入两条记录，其中一条不指定id值由序列seq_id产生，另一条指定id值。
INSERT INTO goods(good_name) VALUES(‘apple’);
INSERT INTO goods VALUES(9,‘banana’);
●验证触发器执行，id字段数据由seq_id产生
SELECT * FROM goods;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

4.示例创建行级触发器

运行结果：

6.包

1.包的概念

●Oracle的兼容特性

●一种模式对象，是PL/SQL级别的代码封装手段

●由过程化语言书写，并经编译和优化后存储在数据库服务器

●变量、常量、游标、存储过程和函数的集合体

2.包的类型

●用户自定义包

●系统内置包

3.包的组成

（1）包规范

●声明了包中的公共对象，包括变量、常量、类型、游标、函数和存储过程。

●在包规范中声明的对象不仅可以在包的内部使用，也可以被应用程序调用。

●公共对象的作用域为包所在的模式。

（2）包体

●实现包规范中的函数和存储过程。

●包体中函数或存储过程的原型必须与包规范中声明的原型完全相同。

●可在程序包的主体中声明包规范中没有声明的变量、游标、类型、异常、函数和存储过程,称为 私有对象，只能由同一包中的函数或存储过程使用。

（3）包规范和包体关系

●包主体名称必须和包规范中的名称相同。

●没有存储过程和函数的包规范可没有包体。

4.基础语法

--包头
CREATE OR REPLACE PACKAGE package_name IS
   FUNCTION FUN1  ... RETURN ..
   REPOCEDURE PRO1 ...
   CURSOR ...
END 包名；
--包体
CREATE OR REPLACE PACKAGE BODY package_name IS
END package_name;
1
2
3
4
5
6
7
8
9

5.创建包规范

\set SQLTERM ;
CREATE TABLE t8(id int,name varchar(50));
INSERT INTO t8 VALUES(1,'kingbase');
INSERT INTO t8 VALUES(1,'beijing');
\set SQLTERM /
CREATE OR REPLACE PACKAGE pkg1 AS   
v1 INT;   
v2 VARCHAR(50);   
TYPE my_assoc_array_type1 IS TABLE OF VARCHAR(50) INDEX BY PLS_INTEGER;
CURSOR cs1 IS SELECT * FROM t1;   
PROCEDURE pr1;   
FUNCTION func1(p1 my_assoc_array_type1) RETURN INT;
END pkg1;
/
1
2
3
4
5
6
7
8
9
10
11
12
13

运行结果：

6.创建包体

CREATE OR REPLACE PACKAGE BODY pkg1 AS
   PROCEDURE pr1 AS
   BEGIN
      OPEN cs1;
      LOOP
         FETCH cs1 INTO v1,v2;
         EXIT WHEN cs1%notfound;
         DBMS_OUTPUT.PUT_LINE(v1);
         DBMS_OUTPUT.PUT_LINE(v2);
      END LOOP;
   END;
   FUNCTION func1(p1 my_assoc_array_type1) RETURN INT AS
   i INT;
   BEGIN
      i :=p1.first;
        WHILE i is not null LOOP
            DBMS_OUTPUT.PUT_LINE(p1(i));
            i :=p1.next(i);
        END LOOP;
      RETURN 1;
   END;
END pkg1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

7.使用包变量、调用函数、包存储过程：

调用包存储过程

CALL pkg1.pr1();

调用包函数

DECLARE
   a1 pkg1.my_assoc_array_type1;
   a2 int;
BEGIN
   a1(1) :='kingbase';
   a1(2) :='beijing';
   a1(3) :='tianjin';
   a2 :=pkg1.func1(a1);
END;
/
1
2
3
4
5
6
7
8
9

运行结果：

7.总结

1.PLSQL中的基本对象包括匿名块、存储过程、函数、触发器和包。

2.存储过程是存储在数据库中的子过程，无返回值。

3.函数是存储在数据库中的子过程，有返回值。

4.包可以容纳多个过程和函数的一个容器

5.触发器无需调用，满足条件情况下，自动执行。

更多信息，参见https://help.kingbase.com.cn/v8/index.html