logo - 跳到首页

VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

CREATE PACKAGE BODY

功能描述

PACKAGE是一个模式对象，用于组织过程、函数和变量的一种方式，PACKAGE由两部分组成：包（PACKAGE）和包体（PACKAGE BODY）组成。

包头是包的说明部分，是对外的操作接口，对应用是可见的；包体是包的代码和实现部分，对应用来说是不可见的。

注意事项

  • 该功能仅在数据库兼容模式为Oracle时支持（即数据库实例初始化时指定DBCOMPATIBILITY='A'）。

  • 当包体存在初始化块时，Vastbase支持不同包体的初始化块/包体函数进行相互调用。

语法格式

CREATE [ OR REPLACE ] PACKAGE BODY [ schema ] package_name
    { IS | AS } declare_section [ initialize_section ] END package_name;

参数说明

  • schema

    已经存在的模式名称。

  • package_name

    自定义包体名称。

  • declare_section

    声明私有变量和私有子程序。

  • initialize_section

    初始化变量并设置一次性的步骤。在包第一次被加载时执行的初始化代码。

示例

示例1： package在初始块中互相调用变量。

1、创建包pkg1。

CREATE OR REPLACE PACKAGE pkg1 is
--创建共有函数，可以被调用
function pkg1_fun(i int) return int; 
--公有变量
id1 int:= 1;  
end pkg1;
/

2、创建包pkg2。

CREATE OR REPLACE PACKAGE pkg2 is
function pkg2_fun(i number) return number;
id2 int:= 1; 
end pkg2;
/

3、给pkg1包创建包体实现pkg1_fun函数，调用pkg2包中定义的变量id2。

CREATE OR REPLACE PACKAGE BODY pkg1 is
function pkg1_fun(i int) return int is
res int;
begin  
res := pkg2.id2;  --调用pkg2.id2的值赋予res
return res;
end;
end pkg1;
/

4、给pkg2包创建包体实现pkg2_fun函数。

CREATE OR REPLACE PACKAGE BODY pkg2 is
function pkg2_fun(i number) return number is
res int;
begin
res := pkg1.id1; --调用pkg1.id1的值赋予res
return res;
end;
begin
id2 := 20;  --给id2赋值20
end pkg2;
/

5、调用包函数可见pkg1_fun函数调用了变量id2。

select pkg1.pkg1_fun(1) from dual;

返回结果为：

pkg1_fun
--------
      20
(1 row)

示例2： package包函数互相调用包变量，初始化块互相调用包函数。

1、创建包pkg1。

CREATE OR REPLACE PACKAGE pkg1 is
function pkg1_fun1(i int) return int;
function pkg1_fun2(i int) return int;
id1 int:= 1;
end pkg1;
/

2、创建包pkg2。

CREATE OR REPLACE PACKAGE pkg2 is
function pkg2_fun1(i number) return number;
function pkg2_fun2(i numeric) return numeric;
id2 int:= 2;
end pkg2;
/

3、创建包体pkg1，调用包pkg2中定义的函数pkg2_fun2。

CREATE OR REPLACE PACKAGE BODY pkg1 is
function pkg1_fun1(i int) return int is 
res int;
begin
res := pkg2.id2;
return res;
end;
function pkg1_fun2(i int) return int is
res int;
begin
res := i;
return res;
end;
begin
id1 := pkg2.pkg2_fun2(5);  --调用包pkg2中定义的函数pkg2_fun2
end pkg1;
/

4、创建包体pkg2，调用包pkg1中定义的函数pkg1_fun2。

CREATE OR REPLACE PACKAGE BODY pkg2 is
function pkg2_fun1(i number) return number is
res int;
begin
res := pkg1.id1;
return res;
end;
function pkg2_fun2(i numeric) return numeric is
res int;
begin
res := i;
return res;
end;
begin
id2 := pkg1.pkg1_fun2(10);  --调用包pkg1中定义的函数pkg1_fun2
end pkg2;
/

5、调用包函数。

select pkg1.pkg1_fun1(100) from dual;
select pkg1.pkg1_fun2(100) from dual;
select pkg2.pkg2_fun1(100) from dual;
select pkg2.pkg2_fun2(100) from dual;

返回结果分别为：

pkg1_fun1
--------
      10
(1 row)

pkg1_fun2
--------
     100
(1 row)

pkg2_fun1
--------
       5
(1 row)

pkg2_fun2
--------
     100
(1 row)

关于CREATE PACKAGE BODY的更多示例请参考CREATE PACKAGE示例1。

包体中支持INSERT ALL INTO语法

功能描述

Vastbase 在Oracle兼容模式下支持包体中使用INSERT ALL INTO语法，该语法支持将子查询的结果数据插入多个表中，支持缺省values字段。

语法格式

INSERT ALL INTO table1(col_name[,...]) [values(col_name[,...])] INTO table2(col_name[,...]) [values(col_name[,...])] {SUBQUERY}

参数说明

  • col_name[,…]

    指定字段的名称序列。

  • SUBQUERY

    子查询语句。

示例

1、构建原始数据。

CREATE TABLE table_1192330_select(
EMPNO NUMBER(4) PRIMARY KEY NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
);
INSERT INTO table_1192330_select (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (1, 'dog', 'code', 7369, TO_DATE('05-06-2018', 'dd-mm-yyyy'), 5000, 5000, 10);
INSERT INTO table_1192330_select (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-12-1980', 'dd-mm-yyyy'), 800, NULL, 20);
INSERT INTO table_1192330_select (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);
INSERT INTO table_1192330_select (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
INSERT INTO table_1192330_select (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('02-04-1981', 'dd-mm-yyyy'), 2975, NULL, 20);
INSERT INTO table_1192330_select (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
INSERT INTO table_1192330_select (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('01-05-1981', 'dd-mm-yyyy'), 2850, NULL, 30);
INSERT INTO table_1192330_select (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('19-04-1987', 'dd-mm-yyyy'), 3000, NULL, 20);
INSERT INTO table_1192330_select (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('17-11-1981', 'dd-mm-yyyy'), 5000, NULL, 10);
INSERT INTO table_1192330_select (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);

2、创建两个插入数据的目标表。

CREATE TABLE table_1192330_insert_1(
EMPNO NUMBER(4) PRIMARY KEY NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
);
CREATE TABLE table_1192330_insert_2(
EMPNO NUMBER(4) PRIMARY KEY NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
);

3、创建包。

CREATE OR REPLACE PACKAGE pkg_1192330 IS
PROCEDURE pro_insert_1192330;
END;
/

4、创建包体。

CREATE OR REPLACE PACKAGE BODY pkg_1192330 IS
PROCEDURE pro_insert_1192330 as
BEGIN
insert all into table_1192330_insert_1(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
into table_1192330_insert_2(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) select * from table_1192330_select;
END;
END;
/

5、调用包。

call pkg_1192330.pro_insert_1192330();

6、查询步骤2中创建的目标表数据，验证插入结果。

select * from table_1192330_insert_1;
select * from table_1192330_insert_2;

两个表数据相同，结果返回如下：

 empno | ename  |    job    | mgr  |      hiredate       | sal  | comm | deptno
-------+--------+-----------+------+---------------------+------+------+--------
     1 | dog    | code      | 7369 | 2018-06-05 00:00:00 | 5000 | 5000 |     10
  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800 |      |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600 |  300 |     30
  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250 |  500 |     30
  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975 |      |     20
  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850 |      |     30
  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000 |      |     20
  7839 | KING   | PRESIDENT |      | 1981-11-17 00:00:00 | 5000 |      |     10
  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500 |    0 |     30
(10 rows)

包体中支持dup_val_on_index类型的预定义exception

功能描述

Vastbase在Oracle兼容模式下，包体支持dup_val_on_index，即可以在包体通过声明该预定义异常来判断并执行一些操作。

dup_val_on_index是一个预定义的异常，它用于处理在插入或更新操作中违反唯一性约束的情况，例如当我们尝试向一个已经存在唯一索引或主键约束的字段中插入重复的数值时会触发该异常。

示例

1、创建测试表并插入数据。

CREATE TABLE table_1187659(
EMPNO NUMBER(4) primary key not null,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
);
insert into table_1187659 (EMPNO, ename, job, mgr, hiredate, sal, comm, deptno)
values (1, 'dog', 'code', 7369, to_date('05-06-2018', 'dd-mm-yyyy'), 5000, 5000, 10);

2、创建包和包体。

CREATE OR REPLACE PACKAGE pkg_1187659 IS
PROCEDURE insert_1187659(
);
END;
/
 
CREATE OR REPLACE PACKAGE body pkg_1187659 IS
PROCEDURE insert_1187659(
) as
begin
insert into table_1187659 (EMPNO, ename, job, mgr, hiredate, sal, comm, deptno)
values (1, 'dog', 'code', 7369, to_date('05-06-2018', 'dd-mm-yyyy'), 5000, 5000, 10);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('主键冲突');
END ;
end;
/

3、设置serveroutput为on并调用包，验证结果。

 set serveroutput on;
 call pkg_1187659.insert_1187659();

结果返回如下：

主键冲突
 insert_1187659
----------------

(1 row)