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

达梦数据库中包罗各种数据库对象，重要分为两大范例：基本数据库对象和复杂数据库对象。下面分别举行介绍。
视频讲解如下                 【赵渝强老师】达梦数据库的数据库对象
      一、 基本数据库对象

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

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

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

复制代码

（2）往表中插入数据

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

复制代码

（3）查询表中的数据

1. SQL> select concat(v1,'*'),concat(v2,'*') from test1;
3. # 输出的结果如下：
4. 行号     CONCAT(V1,'*') CONCAT(V2,'*')
5. ---------- -------------- --------------
6. 1          abc  *         abc*
8. # 从输出的结果可以看出，char同varchar 的区别在于前者长度不足时，系统自动填充空格，
9. # 而后者只占用实际的字节空间。另外，实际插入表中的列长度要受到记录长度的约束，
10. # 每条记录总长度不能大于页面大小的一半。

复制代码

1.2 索引

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

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

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

复制代码

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

1. SQL> create index index_emp_ename on emp(ename);

复制代码

（2）获取索引的ID号。

1. SQL> select name,id from sysobjects
2.       where name='INDEX_EMP_ENAME' and subtype$='INDEX';
4. # 输出的信息如下：
5. 行号                     NAME                    ID         
6. ---------- --------------- -----------
7. 1                  INDEX_EMP_ENAME 33555654

复制代码

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

1. SQL> select indexdef(33555654, 0);
3. # 输出的信息如下：
4. 行号                     INDEXDEF(33555654,0)                                                                  
5. ---------- ----------------------------------------------
6. 1                         CREATE INDEX "INDEX_EMP_ENAME"
7.                 ON "EMP"("ENAME" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;
9. # 函数indexdef的定义如下：indexdef(index_id int, preflag int);
10. # 其中：
11. # index_id：索引id
12. # preflag：对象前缀个数。1表示导出模式名；0表示只导出对象名

复制代码

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

1. SQL> explain select * from emp where ename='KING';
3. # 输出的信息如下：
4. 1   #NSET2: [1, 1, 172]
5. 2     #PRJT2: [1, 1, 172]; exp_num(9), is_atom(FALSE)
6. 3       #BLKUP2: [1, 1, 172]; INDEX_EMP_ENAME(EMP)
7. 4         #SSEK2: [1, 1, 172]; scan_type(ASC),
8.                                    INDEX_EMP_ENAME(EMP),
9.                                    scan_range['KING','KING']
11. # 从输出的执行计划可以看出，该查询使用了索引index_emp_ename。

复制代码

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

1. SQL> sp_rebuild_index('SCOTT', 33555660);
3. # 提示：当一个表经过大量的增删改操作后，表的数据在物理文件中可能存在大量碎片，从而影响访问速度。
4. # 另外，当删除表的大量数据后，若不再对表执行插入操作，索引所处的段可能占用了大量并不使用的簇，
5. # 从而浪费了存储空间。可以使用重建索引来对索引的数据进行重组，使数据更加紧凑，并释放不需要的空间，
6. # 从而提高访问效率和空间效率。DM8提供的重建索引的系统函数为：
7. # sp_rebuild_index(schema_name varchar(256), index_id int);
8. # 其中：
9. # scheam_name为索引所在的模式名
10. # index_id为索引id。
12. # 使用说明：
13. # 水平分区子表，临时表和系统表上建的索引不支持重建。
14. # 虚索引和聚集索引不支持重建。

复制代码

1.3 视图

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

1. SQL> create or replace view view1
2. as
3. select * from dmhr.employee where department_id=1001;
5. # 视图也可以基于多表进行创建，例如：
6. SQL> create or replace view view2
7. as
8. select employee.employee_name,employee.salary,department.department_name
9. from dmhr.employee,dmhr.department
10. where employee.department_id=department.department_id;

复制代码

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

1. SQL> select employee_name,salary from view1;
3. # 输出的信息如下：
4. 行号     EMPLOYEE_NAME SALARY     
5. ---------- ------------- -----------
6. 1          龚顺超     8000
7. 2          陈伟婷     15853
8. 3          何杭菊     15868
9. 4          欧锋利     15878
10. 5          薛辉明     15888
12. SQL> select * from view2 where rownum<=10;
13. 行号     EMPLOYEE_NAME                 SALARY      DEPARTMENT_NAME
14. ------ ------------------ ----------- ---------------
15. 1          马学铭                                12345       总经理办
16. 2          程擎武                                9000        行政部
17. 3          郑吉群                                15000       开发部
18. 4          陈仙                                12000       市场部
19. 5          金纬                                10000       技术支持部
20. 6          李慧军                                10000       总经理办
21. 7          常鹏程                                5000        行政部
22. 8          谢俊人                                5000        市场部
23. 9          苏国华                                30000       总经理办
24. 10         强洁芳                                10000       行政部
26. 10 rows got
28. # 这里使用了行号rownum只显示前10条记录。

复制代码

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

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

复制代码

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

1. SQL> create or replace view view3
2.       as
3.       select * from dmhr.employee where salary <5000
4.       with check option;
6. # 提示：WITH CHECK OPTION表示对视图所做的DML操作，不能违反视图的WHERE条件的限制。

复制代码

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

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

复制代码

1.4 序列

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

1. SQL> create sequence myseq;

复制代码

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

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

比方：

1. SQL> select myseq.nextval;
3. # 输出的信息如下：
4. 行号     NEXTVAL            
5. ---------- --------------------
6. 1          1
8. SQL> select myseq.currval;  
10. # 输出的信息如下：
11. 行号     CURRVAL            
12. ---------- --------------------
13. 1          1
15. # 提示：序列可以是循环的，当序列的值达到最大值/最小值时，序列将从最小值/最大值计数。
16. # 使用一个序列时，不保证将生成一串连续不断递增的值。例如，如果查询一个序列的
17. # 下一个值供insert使用，则该查询是能使用这个序列值的唯一会话。
18. # 如果未能提交事务处理，则序列值就不被插入表中，以后的insert将继续使用该序列随后的值。

复制代码

1.5 同义词

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

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

复制代码

比方，创建一个同义词代表员工表。

1. SQL> create synonym emp for dmhr.employee;

复制代码

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

1. SQL> select count(*) from emp;
3. # 输出的信息如下：
4. 行号                     COUNT(*)            
5. ---------- --------------------
6. 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参数来指定返回值。
创建存储过程的语法格式如下：

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

复制代码

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

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

复制代码

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

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

1. SQL> create or replace procedure dmhr.sayhelloworld
2. as
3.     --说明部分
4. begin
5.     dbms_output.put_line('Hello World');
6. end;
7. /

复制代码

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

1. SQL> begin
2.      dmhr.sayhelloworld();
3.      dmhr.sayhelloworld();
4. end;
5. /
7. # 输出的信息如下：
8. Hello World
9. Hello World
11. DMSQL 过程已成功完成
12. 已用时间: 0.909(毫秒). 执行号:3528.

复制代码

（3）存储过程也可以是exec命令单独举行调用，比方：

1. SQL> exec dmhr.sayhelloworld;
3. # 输出的信息如下：
4. Hello World
6. DMSQL 过程已成功完成
7. 已用时间: 0.455(毫秒). 执行号:3530.

复制代码

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

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

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

复制代码

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

1. SQL> select dmhr.queryEmpTotalIncome(1001) "该员工的年收入";         
3. # 输出的信息如下：
4. 行号                     该员工的年收入
5. ---------- ---------------------
6. 1                  361200

复制代码

2.2 触发器

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

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

DM数据库触发器分为两种不同的范例语句级触发器和元组级触发器，这两种不同范例的触发器在界说是通过for each row举行区分。

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

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

复制代码

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

1. SQL> create or replace trigger dmhr.securityemp
2. before insert
3. on dmhr.employee
4. begin
5. -- 判断当前时间是否是非工作时间
6.   if to_char(sysdate,'day') in ('星期六','星期日') or
7.      to_number(to_char(sysdate,'hh24')) not between 9 and 18 then
8.      --如果是在非工作时间，禁止insert操作抛出异常
9.      raise_application_error(-20001,'此时不允许插入员工数据');
10.   end if;
11. end;
12. /
14. # 触发器dmhr.securityemp中指定的非工作时间有两个：一个是周末；另一个是早9点前和晚18点后。

复制代码

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

1. SQL> insert into dmhr.employee
2. (employee_id,employee_name,email,hire_date,job_id,salary,department_id)
3. values(1234,'Tom','tom@126.com',sysdate,42,7000,706);
5. # 输出的信息如下：
6. [-20001]:此时不允许插入员工数据
7. -20001: SECURITYEMP line 9 .
8. 已用时间: 2.673(毫秒). 执行号:0.

复制代码

（3）删除触发器。

1. SQL> drop trigger dmhr.securityemp;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。