【赵渝强老师】达梦数据库的数据库对象

达梦数据库中包含各种数据库对象，主要分为两大类型：基本数据库对象和复杂数据库对象。下面分别进行介绍。

一、 基本数据库对象

常见的基本数据库对象有：表、索引、视图、序列、同义词等。之所以叫做基本数据库对象是因为这些对象直接使用一条DDL（Data Definition Language，数据定义语言）语句即可创建和管理。

1.1 表

表是一种非常重要的数据库对象，DM数据库的数据都是存储在表中。DM数据库的表是一种二维结构，由行和列组成。表有列组成，列有列的数据类型。达梦数据库支持的数据类型主要有常规数据类型、位串数据类型、日期时间数据类型和多媒体数据类型。
这里值得说明的是，char(n)和varchar(n)中括号中n代表字符的个数，并不代表字节个数，比如char(30) 就可以存储30个字符。char和varchar 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
下面通过一个例子来说明char和varchar在存储字符的时候的区别。
（1）创建表一张新的表

SQL> create table test1(v1 char(5),v2 varchar(5));

（2）往表中插入数据

SQL> insert into test1 values('abc  ','abc  ');

（3）查询表中的数据

SQL> select concat(v1,'*'),concat(v2,'*') from test1;

# 输出的结果如下：
行号     CONCAT(V1,'*') CONCAT(V2,'*')
---------- -------------- --------------
1          abc  *         abc*

# 从输出的结果可以看出，char同varchar 的区别在于前者长度不足时，系统自动填充空格，
# 而后者只占用实际的字节空间。另外，实际插入表中的列长度要受到记录长度的约束，
# 每条记录总长度不能大于页面大小的一半。

1.2 索引

索引是与表相关的可选的结构（聚簇索引除外），它能使对应于表的SQL语句执行得更快，因为有索引比没有索引能更快地定位信息。DM8数据库的索引能提供访问表的数据的更快路径，可以不用重写任何查询而使用索引，其结果与不使用索引是一样的，但速度更快。DM数据库官方对索引的定义为：索引（Index）是帮助DM数据库高效获取数据的数据结构。DM数据库中默认索引类型是B+树索引。达梦数据库提供了几种最常见类型的索引，对不同场景有不同的功能，它们是：

• 聚集索引：每一个普通表有且只有一个聚集索引；
• 唯一索引：索引数据根据索引键唯一；
• 函数索引：包含函数/表达式的预先计算的值；
• 位图索引：对低基数的列创建位图索引；
• 位图连接索引：针对两个或者多个表连接的位图索引，主要用于数据仓库中；
• 全文索引：在表的文本列上而建的索引。

提示：索引在逻辑上和物理上都与相关的表的数据无关，作为无关的结构，索引需要存储空间。 
创建或删除一个索引，不会影响基本的表、数据库应用或其他索引。当插入、更改和删除相关的表的行时，
达梦数据库会自动管理索引。如果删除索引，所有的应用仍继续工作，
但访问以前被索引了的数据时速度可能会变慢。

可以用create index语句明确地创建索引。下面通过一个简单的示例来演示如何创建普通的索引。
（1）在emp表的ename列上创建一个名为index_emp_ename的索引。

SQL> create index index_emp_ename on emp(ename);

（2）获取索引的ID号。

SQL> select name,id from sysobjects 
      where name='INDEX_EMP_ENAME' and subtype$='INDEX';

# 输出的信息如下：
行号     		NAME            	ID         
---------- --------------- -----------
1          	INDEX_EMP_ENAME 33555654

（3）通过indexdef系统函数查看索引的定义。

SQL> select indexdef(33555654, 0);

# 输出的信息如下：
行号     		INDEXDEF(33555654,0)                                                                  
---------- ----------------------------------------------
1         		CREATE INDEX "INDEX_EMP_ENAME" 
                ON "EMP"("ENAME" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;

# 函数indexdef的定义如下：indexdef(index_id int, preflag int);
# 其中：
# index_id：索引id
# preflag：对象前缀个数。1表示导出模式名；0表示只导出对象名

（4）查询名叫KING的员工数据，并输出对应的SQL执行计划。

SQL> explain select * from emp where ename='KING';

# 输出的信息如下：
1   #NSET2: [1, 1, 172] 
2     #PRJT2: [1, 1, 172]; exp_num(9), is_atom(FALSE) 
3       #BLKUP2: [1, 1, 172]; INDEX_EMP_ENAME(EMP)
4         #SSEK2: [1, 1, 172]; scan_type(ASC), 
                                   INDEX_EMP_ENAME(EMP), 
                                   scan_range['KING','KING']

# 从输出的执行计划可以看出，该查询使用了索引index_emp_ename。

（5）使用系统函数sp_rebuild_index重建索引。

SQL> sp_rebuild_index('SCOTT', 33555660);

# 提示：当一个表经过大量的增删改操作后，表的数据在物理文件中可能存在大量碎片，从而影响访问速度。
# 另外，当删除表的大量数据后，若不再对表执行插入操作，索引所处的段可能占用了大量并不使用的簇，
# 从而浪费了存储空间。可以使用重建索引来对索引的数据进行重组，使数据更加紧凑，并释放不需要的空间，
# 从而提高访问效率和空间效率。DM8提供的重建索引的系统函数为：
# sp_rebuild_index(schema_name varchar(256), index_id int);
# 其中：
# scheam_name为索引所在的模式名
# index_id为索引id。

# 使用说明：
# 水平分区子表，临时表和系统表上建的索引不支持重建。
# 虚索引和聚集索引不支持重建。

1.3 视图

视图（VIEW）是一种虚表，其本身并不包含数据。它将作为一个select语句保存在数据字典中的。视图依赖的表叫做基表。通过视图可以展现基表的部分数据；视图数据来自定义视图的查询中使用的基表。在了解的视图的作用后，下面通过具体的步骤来演示如何使用视图。
（1）基于员工表emp创建视图。

SQL> create or replace view view1
as
select * from dmhr.employee where department_id=1001;

# 视图也可以基于多表进行创建，例如：
SQL> create or replace view view2
as
select employee.employee_name,employee.salary,department.department_name 
from dmhr.employee,dmhr.department
where employee.department_id=department.department_id;

（2）从视图中查询数据。

SQL> select employee_name,salary from view1;

# 输出的信息如下：
行号     EMPLOYEE_NAME SALARY     
---------- ------------- -----------
1          龚顺超     8000
2          陈伟婷     15853
3          何杭菊     15868
4          欧锋利     15878
5          薛辉明     15888

SQL> select * from view2 where rownum<=10;
行号     EMPLOYEE_NAME 		SALARY      DEPARTMENT_NAME
------ ------------------ ----------- ---------------
1          马学铭				12345       总经理办
2          程擎武				9000        行政部
3          郑吉群				15000       开发部
4          陈仙				12000       市场部
5          金纬				10000       技术支持部
6          李慧军				10000       总经理办
7          常鹏程				5000        行政部
8          谢俊人				5000        市场部
9          苏国华				30000       总经理办
10         强洁芳				10000       行政部

10 rows got

# 这里使用了行号rownum只显示前10条记录。

（3）通过视图执行DML操作，例如：给1001号部门员工涨100块钱工资。

SQL> update view1 set salary=salary+100;

# 并不是所有的视图都可以执行DML操作。在视图定义时含义以下内容，视图则不能执行DML操作：
# （1）查询子句中包含distinct和组函数
# （2）查询语句中包含group by子句和order by子句
# （3）查询语句中包含union 、union all等集合运算符
# （4）where子句中包含相关子查询
# （5）from子句中包含多个表
# （6）如果视图中有计算列，则不能执行update操作
# （7）如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作

（4）创建视图时使用WITH CHECK OPTION约束。

SQL> create or replace view view3
      as
      select * from dmhr.employee where salary <5000
      with check option;

# 提示：WITH CHECK OPTION表示对视图所做的DML操作，不能违反视图的WHERE条件的限制。

（5）在view3上执行update操作。

SQL> update view3 set salary=10000;

# 此时将出现下面的错误信息：
update view3 set salary=10000;
[-6603]:违反视图[VIEW3]CHECK约束.
已用时间: 4.332(毫秒). 执行号:0.

1.4 序列

序列（sequence）是DM数据库中的数据库实体之一。通过使用序列，多个用户可以产生和使用一组不重复的有序整数值。比如可以用序列来自动地生成主关键字值。序列通过提供唯一数值的顺序表来简化程序设计工作。例如，下面的语句将创建一个序列。

SQL> create sequence myseq;

一旦序列生成，用户就可以在SQL语句中用以下伪列来存取序列的值：

• currval：返回当前的序列值；
• nextval：如果为升序序列，序列值增加并返回增加后的值；如果为降序序列，序列值减少并返回减少后的值。

例如：

SQL> select myseq.nextval;

# 输出的信息如下：
行号     NEXTVAL             
---------- --------------------
1          1

SQL> select myseq.currval;  

# 输出的信息如下：
行号     CURRVAL             
---------- --------------------
1          1

# 提示：序列可以是循环的，当序列的值达到最大值/最小值时，序列将从最小值/最大值计数。
# 使用一个序列时，不保证将生成一串连续不断递增的值。例如，如果查询一个序列的
# 下一个值供insert使用，则该查询是能使用这个序列值的唯一会话。
# 如果未能提交事务处理，则序列值就不被插入表中，以后的insert将继续使用该序列随后的值。

1.5 同义词

同义词(Synonym)让用户能够为数据库的一个模式下的对象提供别名。同义词通过掩盖一个对象真实的名字和拥有者，并且对远程分布式的数据库对象给予了位置透明特性以此来提供了一定的安全性。同时使用同义词可以简化复杂的SQL语句。同义词可以替换模式下的表、视图、序列、函数、存储过程等对象。创建同义词的语法格式如下：

create [or replace] [public] synonym [<模式名>.]<同义词名> for [<模式名>.]<对象名>

例如，创建一个同义词代表员工表。

SQL> create synonym emp for dmhr.employee;

接下来就可以通过同义词查询员工数据了。

SQL> select count(*) from emp;

# 输出的信息如下：
行号     		COUNT(*)            
---------- --------------------
1          	858

关于同义词的使用需要注意以下两点：

• 同义词分为全局同义词(public synonym)和非全局同义词。用户在自己的模式下创建同义词，必须有create synonym权限。用户要创建全局同义词(public synonym)，必须有create public synonym权限；
• 同义词创建时，并不会检查他所指代的同义词对象是否存在，用户使用该同义词时候，如果不存在指代对象或者对该指代对象不拥有权限，则会报错。

二、复杂数据库对象

达梦数据库提供的复杂数据库对象主要包括：存储过程、存储函数和触发器。之所以叫做复杂数据库对象，是因为这些对象需要使用DMSQL语言进行开发和管理。DMSQL程序是达梦数据库对标准SQL语言的扩展，是一种过程化SQL语言。在DMSQL程序中，包括一整套数据类型、条件结构、循环结构和异常处理结构等，DMSQL程序中可以执行SQL语句，SQL语句中也可以使用DMSQL函数。

2.1 存储过程和存储函数

存储过程（Stored Procedure）和存储函数（Stored Function）指存储在数据库中供所有用户调用的子程序，它们事先经过经编译后存储在数据库系统中。因此，调用存储过程和存储函数来完成业务逻辑，是可以提高性能的。存储过程和存储函数的结构类似，但是存储函数必须要有一个return子句用于返回函数的值；而存储过程没有return子句。尽管存储过程没有return子句，但却可以通过指定一个或者多个out参数来指定返回值。

创建存储过程的语法格式如下：

create [or replace] procedure 存储过程名称(参数列表)
as
  DMSQL子程序体;

创建存储函数的基本语法格式如下：

create [or replace] function 存储函数名称(参数列表)
return 函数返回值类型
as
  DMSQL子程序体;

2.1.1 【实战】创建和使用存储过程

下面通过具体的步骤来演示如何创建存储过程，以及如何在DM数据库中调用它。
（1）创建第一个存储过程sayhelloworld，输出Hello World字符串。

SQL> create or replace procedure dmhr.sayhelloworld
as
    --说明部分
begin
    dbms_output.put_line('Hello World');
end;
/

（2）存储过程创建成功后，可以在PL/SQL程序中调用它。例如下面的代码调用了两次存储过程dmhr.sayhelloworld()。

SQL> begin
     dmhr.sayhelloworld();
     dmhr.sayhelloworld();
end;
/

# 输出的信息如下：
Hello World
Hello World

DMSQL 过程已成功完成
已用时间: 0.909(毫秒). 执行号:3528.

（3）存储过程也可以是exec命令单独进行调用，例如：

SQL> exec dmhr.sayhelloworld;

# 输出的信息如下：
Hello World

DMSQL 过程已成功完成
已用时间: 0.455(毫秒). 执行号:3530.

2.1.2 【实战】创建和使用存储函数

存储函数与存储过程的最大区别就在于存储函数可以通过reture子句返回函数的值，而存储过程没有return子句。下面将通过一个具体的示例来演示如何使用存储函数，并且如何调用它。
（1）创建存储函数dmhr.queryEmpTotalIncome查询指定员工的年收入。

SQL> create or replace function dmhr.queryEmpTotalIncome(eno in number)
return number
as
   --定义引用型变量保存月薪和奖金
   psal dmhr.employee.salary%type;
   pcomm dmhr.employee.commission_pct%type;
begin
   -- 查询指定员工的薪水和奖金，并赋值给变量。
   select salary,commission_pct into psal,pcomm from dmhr.employee 
   where employee_id=eno;
  
   --返回年收入
   return psal*12+nvl(pcomm,0);
end;
/

（2）调用存储函数dmhr.queryEmpTotalIncome查询员工号是1001的年收入。

SQL> select dmhr.queryEmpTotalIncome(1001) "该员工的年收入";          

# 输出的信息如下：
行号     		该员工的年收入
---------- ---------------------
1          	361200

2.2 触发器

触发器是一种特殊的存储过程，它在创建后就存储在数据库中。触发器的特殊性在于它是建立在某个具体的表或视图之上的，或者是建立在各种事件前后的，而且是自动激发执行的，如果用户在这个表上执行了某个DML操作（insert、delete、update），触发器就被激发执行。
触发器常用于自动完成一些数据库的维护工作。例如，触发器可以具有以下功能：

• 可以对表自动进行复杂的安全性、完整性检查；
• 可以在对表进行DML操作之前或者之后进行其它处理；
• 进行审计，可以对表上的操作进行跟踪；
• 实现不同节点间数据库的同步更新。

DM数据库触发器分为两种不同的类型语句级触发器和元组级触发器，这两种不同类型的触发器在定义是通过for each row进行区分。

• 语句级触发器
  语句级触发器是指在指定的操作语句之前或者之后执行一次，不管这个操作影响了多少行记录。换句话说，语句级触发器针对是表。
• 元组级触发器
  元组级触发器是指触发语句作用的每一条记录都被触发。换句话说，行级级触发器就是针对是表中的每一行。在行级触发器中可以使用:old和:new关键字来表示同一行数据在操作之前和操作之后的值。以员工表（dmhr.employee）为例，:old.salary操作该行之前员工的薪水，而:new.salary表示操作该行之后员工的薪水。

# 提示:old和:new表示的表中同一行。区别是:old表示操作之前，而:new表示操作之后。
# 注意这里的冒号不能少。

下面是利用数据库的触发器可以实现安全性的检查。这里的需求是：禁止在非工作时间往员工表中插入数据。例如：今天如果是星期天就不允许在员工表上执行insert操作。
（1）创建语句级触发器dmhr.securityemp禁止在非工作时间往员工表中插入数据。

SQL> create or replace trigger dmhr.securityemp
before insert
on dmhr.employee
begin
 -- 判断当前时间是否是非工作时间
  if to_char(sysdate,'day') in ('星期六','星期日') or
     to_number(to_char(sysdate,'hh24')) not between 9 and 18 then
     --如果是在非工作时间，禁止insert操作抛出异常
     raise_application_error(-20001,'此时不允许插入员工数据');
  end if;
end;
/

# 触发器dmhr.securityemp中指定的非工作时间有两个：一个是周末；另一个是早9点前和晚18点后。

（2）非工作时间在员工表上执行insert操作。

SQL> insert into dmhr.employee
(employee_id,employee_name,email,hire_date,job_id,salary,department_id) 
values(1234,'Tom','tom@126.com',sysdate,42,7000,706);

# 输出的信息如下：
[-20001]:此时不允许插入员工数据
-20001: SECURITYEMP line 9 .
已用时间: 2.673(毫秒). 执行号:0.

（3）删除触发器。

SQL> drop trigger dmhr.securityemp;