关于数据库表、视图、存储过程、函数、分区表等的创建使用

  我使用的数据库是达梦数据库dm8，这里分享数据库的一些基本操作，内容较详细，也是作为笔记方便以后查阅。

创建基本行表food并插入数据

CREATE TABLE food
(
 food_id CHAR(3) NOT NULL,
 food_name VARCHAR(40) NULL,
);

创建food_id、food_name列，设置前者不可为空，后者可以为空，都是字符类型。

向表中插入若干数据，方便后面创建视图和触发器使用：

INSERT INTO food(food_id,food_name) VALUES('1','苹果');
INSERT INTO food(food_id,food_name) VALUES('2','香蕉');
INSERT INTO food(food_id,food_name) VALUES('3','西瓜');
INSERT INTO food(food_id,food_name) VALUES('4','猕猴桃');
INSERT INTO food(food_id,food_name) VALUES('5','橘子');
INSERT INTO food(food_id,food_name) VALUES('6','梨子');

创建视图

对food表创建一个视图，名为food_view保存food_id大于4的数据，列名包括food_id、food_name。

CREATE VIEW food_view AS
SELECT
        food_id,
        food_name
FROM
        food
WHERE
        food_id > 4;

  对于视图的定义：视图是一种逻辑表，它不存储任何数据，通过select语句查询视图时得到的是该视图所依赖的基表的数据。如果对基表进行update,insert等操作，相应的视图也会发生改变。视图实际上就是一条已经编译好了的sql语句，通过视图调用sql语句去查询数据。

创建触发器

基于food表创建一个触发器，名为food_t，当对food表进行更新时，定义的触发器被激活，打印对应语句：

CREATE TRIGGER food_t AFTER
        UPDATE ON food
        FOR EACH ROW
                BEGIN
                PRINT 'UPDATE OPERATION ON FOOD !!';
             END;

  实例的默认设置中print是没有打开的，需要set serveroutput on，再去调用存储过程才可以正常运行，可以临时开启也可以在$DM_HOME/bin/disql_conf/glogin.sql中插入，这样用户只要登录了系统就默认开启print。

  FOR EACH ROW的作用是当修改很多行数据时，修改了几行数据触发器就会print几次消息，如果没有就print一行消息

创建函数

  创建一个名为 fun_1 的存储函数。该函数的返回类型为 INT 类型。两个参数 A、B 的和赋给了变量 S，RETURN 语句则将变量 S 的值作为函数的返回值返回。示例语句如下所示：

CREATE
        FUNCTION fun_1
                (a INT, b INT)
                RETURN INT
        AS
                s INT;
        BEGIN
                s:=a+b;
                RETURN s;
        END;

调用函数：SELECT .fun_1(4,5);得到的结果就是9。

创建序列

创建序列 seq，起始值为 5，增量值为 2，最大值为 200。示例语句如下所示：

CREATE SEQUENCE seq START WITH 5 INCREMENT BY 2 MAXVALUE 200;

查询序列下一个值，示例语句如下所示：

SELECT seq.nextval FROM dual;

得到的结果是：7。

创建存储过程

创建一个名为 proc_1 的存储过程，入参数据类型为 INT，变量 B 赋予初始值 10，输出变量 A 的值为输入的变量 A 值与变量 B 值之和。示例语句如下所示：

CREATE
PROCEDURE proc_1
        (a IN OUT INT)
AS
        b INT:=10;
BEGIN
        a:=a+b;
        PRINT 'PROC_1调用结果:'||a;
END;

调用存储过程，输入变量值3，得到的结果是13。||在DM中可以作为拼接符使用，将前后两个数据结合在一起。

CALL proc_1(3);
PROC_1调用结果:13

创建分区表

表名为ages，字段有name,age，超过分区限制以每个分区间隔为10自动创建新分区。小于等于18的为p1分区，小于等于35的为p2分区，小于等于60的为p3分区，小于等于100的为p4分区

create table ages( 
name varchar(30), 
age int 
)
partition by range(age) interval(10)( 
partition p1 values equ or less than (18), 
partition p2 values equ or less than (35), 
partition p3 values equ or less than (60), 
partition p4 values equ or less than (100)); 

插入数据并查询

insert into ages values('张三',19);
select * from ages partition(p2);

行号     NAME   AGE        
---------- ------ -----------
1          张三 19

张三的age大于19，小于35所以被插入p2分区。

插入数据并查询

insert into ages values('李四',120);

可以看出120不属于任何一个分区，这时系统会自动创建分区存储数据，因为我们不知道数据存储在哪个分区所以不能通过select查询，下面就是查询扩展分区的方法：

select name,id from sysobjects where name=’AGES’; 

因为开启了大小写敏感，所以在系统表中ages会被大写，所以在查找时要输入大写AGES，否则报错。

select name,id from sysobjects where name='AGES';

行号     NAME ID         
---------- ---- -----------
1          AGES 1054

通过上面的查找语句，我们就得到了ages表的id号，接着通过id号找到分区名称：

select base_table_id,part_table_id,partition_name from syshparttableinfo 
where base_table_id=1054; 

行号     BASE_TABLE_ID PART_TABLE_ID PARTITION_NAME
---------- ------------- ------------- --------------
1          1054          1055          P1
2          1054          1056          P2
3          1054          1057          P3
4          1054          1058          P4
5          1054          1060          SYS_P1054_1059

查找到了120所在的分区名称：SYS_P1054_1059。

select * from ages partition(SYS_P1054_1059);

行号     NAME   AGE        
---------- ------ -----------
1          李四 120

我们就能够找到“李四”所在的分区。

关于多列分区表

create table ages( 
name varchar(30), 
age int，
id int 
)
partition by range(age,id) ( 
partition p1 values equ or less than (10,10), 
partition p2 values equ or less than (20,20), 
partition p3 values equ or less than (30,30)); 

如果分区表包含多个分区列，首先比较第一个分区列值，如果在第一个分区值的范围内， 就以第一个分区进行分区，分到p1;如果等于第一个分区的边界值， 那么需要比较第二个分区的值， 根据第二分区的值进行分区;如果第二列的值也是边界值，需要继续比较后续分区，以此类推。

 具体分区方式如下图：

有关技术方面交流请关注社区：达梦数据库 - 新一代大型通用关系型数据库 | 达梦在线服务平台达梦数据库产品体验站，DM8在线试玩，达梦数据库全系列产品免费下载，官方权威的快速上手文档和产品手册，最活跃的达梦技术社区，面向全行业ISV厂商免费的云适配服务。https://eco.dameng.com