达梦相关笔记

置顶岳麓丹枫001

已于 2023-06-29 10:27:41 修改

阅读量2.2k

点赞数 2

分类专栏：达梦文章标签： linux 服务器运维

于 2022-07-21 11:42:35 首次发布

本文链接：https://blog.csdn.net/yueludanfeng/article/details/125908270

版权

达梦专栏收录该内容

6 篇文章 0 订阅

订阅专栏

文章目录

达梦数据库基于 CentOS 安装
DiSQL使用
常见命令
- disql 执行 SQL文件
- disql 执行 SQL文件优化执行速度
开启慢查询日志(实际测试操作的SQL都会打印)
- 修改配置 /dm8/data/imos/sqllog.ini
- 设置开启慢日志立即生效
SQL 基础
DM存储过程与其他适配PG方案
与PG存储过程中的差异

达梦数据库基于 CentOS 安装

检查Linux(Unix)系统信息

## 获取系统位数
getconf LONG_BIT
## 查询操作系统release信息
lsb_release -a
## 查询系统信息
cat /etc/issue
## 查询系统名称
uname -a

创建安装用户

## 创建 dinstall 组
groupadd -g 12349 dinstall
## 创建 dmdba 用户
useradd -u 12345 -g dinstall -m -d /home/dmdba -s /bin/bash dmdba
## 修改 dmdba 用户密码
echo dmdba | passwd --stdin dmdba

创建安装目录及授权

mkdir -p /dm /soft
mkdir -p /dm{arch,bak,data}
chown -R dmdba.dinstall /dm /soft /dmdata /dmarch /dmbak
chmod -R 775 /dm{arch,bak,data} /dm

关闭防火墙和Selinux

## 关闭防火墙
systemctl stop firewalld.service
systemctl disable firewalld.service
## 关闭selinux
setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

关闭透明大页和numa

Linux 6

cat >>/etc/rc.d/rc.local <<EOF
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
EOF

Linux7

sed -i 's/quiet/quiet transparent_hugepage=never numa=off/' /etc/default/grub
grub2-mkconfig -o /boot/grub2/grub.cfg

配置系统参数

cat <<EOF >>/etc/sysctl.conf
fs.aio-max-nr = 1048576
fs.file-max = 6815744
#kernel.shmall = 2097152
#kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
vm.swappiness = 0
vm.dirty_background_ratio = 3
vm.dirty_ratio = 80
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
EOF
## 激活参数配置
sysctl -p

配置操作系统限制

## 配置pam.d
cat <<EOF >>/etc/pam.d/login
session required pam_limits.so 
session required /lib64/security/pam_limits.so
EOF
## 查看 pam.d
cat /etc/pam.d/login | grep -v "^$" | grep -v "^#"
## 查看操作系统资源限制
ulimit -a
## 解除 nice，as fsize，nproc，nofile，core，data 限制
cat <<EOF>>/etc/security/limits.conf
dmdba - nice   0     
dmdba - as     unlimited
dmdba - fsize  unlimited
dmdba - nproc  131072
dmdba - nofile 131072
dmdba - core   unlimited
dmdba - data   unlimited
root  - nice   0     
root  - as     unlimited
root  - fsize  unlimited
root  - nproc  131072
root  - nofile 131072
root  - core   unlimited
root  - data   unlimited
EOF
## 检查配置文件
cat /etc/security/limits.conf | grep -v "^$" | grep -v "^#"
## 查看 dmdba 用户的资源限制
su - dmdba -c "ulimit -a"

配置环境变量

## 配置 dmdba 环境变量
cat <<EOF>>/home/dmdba/.bash_profile
umask 022
export TMP=/tmp
export TMPDIR=\$TMP
export DM_HOME="/dm"
export LD_LIBRARY_PATH="\$LD_LIBRARY_PATH:\$DM_HOME/bin"
export PATH=/usr/sbin:\$PATH
export PATH=\$DM_HOME/bin:\$PATH
export PS1="[\`whoami\`@\`hostname\`:"'\$PWD]\$ '
alias ds='disql sysdba'
EOF
## 查看环境变量
cat /home/dmdba/.bash_profile | grep -v "^$" | grep -v "^#"

安装 DM8 数据库

安装同时支持图形化安装，命令行安装，静默安装三种方式。由于我没有安装图形化界面，因此使用 命令行安装 方式进行安装。

用户应登录或切换到安装系统用户，进行以下安装步骤的操作(注：不建议使用root系统用户进行安装)。

挂载 DM ISO 镜像

## 挂载 DM iso 镜像文件
cd /tools
mount -o loop dm*.iso /opt
## 拷贝安装文件至 /soft
cp /opt/DM* /soft
## 取消挂载
umount /opt
## 目录授权
chown -R dmdba:dinstall /soft
chmod -R 775 /soft
ll /soft

命令行安装 DM

## 切换至 dmdba 用户
su - dmdba
cd /soft/
## 执行命令行安装
./DMInstall.bin -i

安装完成之后, 使用root用户执行如下脚本

su - root

/dm/script/root/root_installer.sh

初始化

使用 dmdba 用户配置实例，使用 dminit 命令初始化实例。

su - dmdba
## 使用默认参数初始化实例，需要附加实例存放路径 /dmdata
dminit path=/dmdata

实际操作演示

[dmdba@localhost.localdomain:/home/dmdba]$ dminit path=/dmdata
initdb V8
db version: 0x7000c
file dm.key not found, use default license!
License will expire on 2022-03-12
open fail! path:./libcrypto.so
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL

 log file path: /dmdata/DAMENG/DAMENG01.log


 log file path: /dmdata/DAMENG/DAMENG02.log

write to dir [/dmdata/DAMENG].
create dm database success. 2021-09-17 15:46:43
[dmdba@localhost.localdomain:/home/dmdba]$

实例2 : 创建 imos 数据库

path 指定数据库所在数据目录 base_dir

db_name: 指定数据库名称

最终路径为 $base_dir/$ db_name

./dminit path=/dmdata db_name=imos instance_name=imos_instance port_num=5237

命令行注册服务

注册服务需使用 root 用户进行注册。使用 root 用户进入数据库安装目录的 /script/root 下，如下所示：

cd /dm/script/root
## 注册服务
./dm_service_installer.sh -t dmserver -dm_ini /dmdata/DAMENG/dm.ini -p DMSERVER


如果是 imos 数据库,对应注册命令为:
./dm_service_installer.sh -t dmserver -dm_ini /dmdata/imos/dm.ini -p imos

配置服务开机自启

systemctl enable DmServiceDMSERVER.service

如果是imos 数据库对应启动命令为
systemctl enable DmServiceimos.service

启停服务

## 开启服务
systemctl start DmServiceDMSERVER.service
## 查看服务状态
systemctl status DmServiceDMSERVER.service

如果是 imos 数据库,对应命令为

## 开启服务
systemctl start DmServiceimos.service
## 查看服务状态
systemctl status DmServiceimos.service

也可以通过以下命令执行：

DmServiceDMSERVER start/stop/restart/status

如果是 imos 数据库, 对应命令为

DmServiceimos start/stop/restart/status

重命名方便记忆

ln -s /dm/bin/DmServiceDMSERVER /dm/bin/dm_ctl
dm_ctl start/stop/restart/status

ln -s /dm/bin/DmServiceimos /dm/bin/dm_imos_ctl
dm_imos_ctl start/stop/restart/status

查看端口与进程

[root@localhost root]# netstat -anp |grep dmserver
tcp6       0      0 :::5236                 :::*                    LISTEN      7173/dmserver       
[root@localhost root]# 
[root@localhost root]# ps -ef|grep dmserver |grep -v grep
dmdba     7173     1  1 16:06 ?        00:00:01 /dm/bin/dmserver /dmdata/DAMENG/dm.ini -noconsole

通过客户端命令连接到 DM 服务器

方式1

disql
如果使用的是默认密码, 直接按两下回车即可连接上 DM 服务器

方式2
```
disql SYSDBA/SYSDBA
```

方式3

disql SYSDBA/SYSDBA@207.207.35.101:5236

DiSQL使用

常见命令

-- 关闭 & 功能 
SET DEFINE OFF;  

-- 关闭显示执行的SQL
SET ECHO OFF;   

-- 关闭影响多少行功能
SET FEEDBACK OFF;

--显示每个 SQL 语句花费的执行时间
SET TIMING OFF;  

-- 打开打印开关 (可选)
--SET SERVEROUTPUT ON;

-- 设置 单页显示的行数
set pagesize  50;

-- 设置单行显示的列数
set linesize 500;

disql 执行 SQL文件

[dmdba@localhost.localdomain:/home/dmdba]$ cat test.sql
select sysdate;
exit;
[dmdba@localhost.localdomain:/home/dmdba]$ disql test/test \` test.sql
open fail! path:./libcrypto.so

服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 3.523(ms)
disql V8
SQL> select sysdate;

行号     SYSDATE            
---------- -------------------
1          2021-10-14 19:02:36

已用时间: 0.773(毫秒). 执行号:1400.
SQL> exit;

disql 执行 SQL文件优化执行速度

cat >  /dm8/bin/disql_conf/glogin.sql << "EOF"
SET DEFINE OFF;
SET ECHO OFF;
SET FEEDBACK OFF;
SET TIMING OFF;
set pagesize 5000;
set linesize 5000;
EOF

开启慢查询日志(实际测试操作的SQL都会打印)

修改配置 /dm8/data/imos/sqllog.ini

[root@localhost ~]# cat  /dm8/data/imos/sqllog.ini
BUF_TOTAL_SIZE          = 10240         #SQLs Log Buffer Total Size(K)(1024~1024000)
BUF_SIZE                = 1024          #SQLs Log Buffer Size(K)(50~409600)
BUF_KEEP_CNT            = 6             #SQLs Log buffer keeped count(1~100)

[SLOG_ALL]
    FILE_PATH    = /dm8/log/slog  # 修改点 1 , 如果该目录不存在, 需要预先创建, 注意权限
    PART_STOR    = 0
    SWITCH_MODE  = 2
    SWITCH_LIMIT   = 128
    ASYNC_FLUSH   = 1
    FILE_NUM = 5
    ITEMS    = 0
    SQL_TRACE_MASK  = 1
    MIN_EXEC_TIME = 0
    USER_MODE   = 0
    USERS =

两点，1是要到log目录下新建slog文件夹，2是USER_MODE=0 --为0代表不指定用户，为1时USERS要列出指定用户

设置开启慢日志立即生效

SP_SET_PARA_VALUE(1,'SVR_LOG',1);
/

SQL 基础

查询表空间大小

WITH wt1 AS (SELECT ts.TABLESPACE_NAME,
          df.all_bytes,
          fs.FREESIZ FREESIZ,
          df.MAXSIZ,
          ts.BLOCK_SIZE,
          ts.LOGGING,
          ts.FORCE_LOGGING,
          ts.CONTENTS,
          ts.EXTENT_MANAGEMENT,
          ts.SEGMENT_SPACE_MANAGEMENT,
          ts.RETENTION,
          ts.DEF_TAB_COMPRESSION,
          df.ts_df_count   FROM   dba_tablespaces ts,
          (SELECT TABLESPACE_NAME,
                  count(*) ts_df_count,
                  SUM(BYTES) all_bytes,
                  SUM(decode(MAXBYTES,
 0,
 BYTES,
 MAXBYTES)) MAXSIZ           FROM   dba_data_files d           GROUP  BY TABLESPACE_NAME) df,
          (SELECT TABLESPACE_NAME,
                  SUM(BYTES) FREESIZ           FROM   dba_free_space           GROUP  BY TABLESPACE_NAME) fs   WHERE  ts.TABLESPACE_NAME = df.TABLESPACE_NAME   AND    ts.TABLESPACE_NAME = fs.TABLESPACE_NAME) SELECT (SELECT A.ID         FROM   V$TABLESPACE A         WHERE  A.NAME = UPPER(t.TABLESPACE_NAME)) TS#,
        t.TABLESPACE_NAME TS_Name,
        CONTENTS,
        round(t.all_bytes / 1024 / 1024) ts_size_M,
        round(t.freesiz / 1024 / 1024) Free_Size_M,
        round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
        round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes,
 3) Used_per,
        round(MAXSIZ / 1024 / 1024/1024,
 3) MAX_Size_g,
        round(decode(MAXSIZ,
 0,
 to_number(NULL),
 (t.all_bytes - FREESIZ)) * 100 /              MAXSIZ,
              3) USED_per_MAX,
        round(t.BLOCK_SIZE) BLOCK_SIZE,
        t.LOGGING,
        t.ts_df_count FROM   wt1 t UNION ALL SELECT to_number('') TS#,
        'ALL TS:' TS_Name,
        null,
        round(SUM(t.all_bytes) / 1024 / 1024,
 3) ts_size_M,
        round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m,
        round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
        round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes),
 3) Used_per,
        round(SUM(MAXSIZ) / 1024 / 1024/1024) MAX_Size,
        to_number('') "USED,
        % of MAX Size",
        to_number('') BLOCK_SIZE,
        '' LOGGING,
        to_number('') ts_df_count FROM   wt1 t order by CONTENTS NULLS LAST,
        TS# NULLS LAST

查询文件情况

SELECT d.TABLESPACE_NAME, 
       (select CONTENTS from dba_tablespaces where TABLESPACE_NAME=d.TABLESPACE_NAME) CONTENTS, 
       (SELECT round(SUM(nb.BYTES) / 1024 / 1024, 
       2)           FROM dba_data_files nb          WHERE nb.TABLESPACE_NAME = d.TABLESPACE_NAME) ts_size_m, 
       d.FILE_NAME, 
       round(d.BYTES / 1024 / 1024, 
       2) file_size_m, 
       round(d.MAXBYTES / 1024 / 1024 / 1024, 
       2) file_max_size_G, 
       d.AUTOEXTENSIBLE, 
       round(d.INCREMENT_BY * 8 * 1024 / 1024 / 1024, 
       2) INCREMENT_m, 
       round(d.BYTES * 100 / decode(d.MAXBYTES, 
       0, 
BYTES, 
d.MAXBYTES), 
2) autoextend_ratio, 
       (SELECT b.CREATE_TIME           FROM v$datafile b          where b.path = file_name) CREATION_TIME, 
       d.INCREMENT_BY INCREMENT_BY_block, 
       d.BYTES, 
       d.blocks, 
       d.MAXBYTES, 
       d.MAXBLOCKS, 
       d.USER_BYTES, 
       d.USER_BLOCKS   FROM dba_data_files d;

查看当前实例名称

SQL> select * from v$instance;

行号     NAME     INSTANCE_NAME INSTANCE_NUMBER HOST_NAME             SVR_VERSION                DB_VERSION          START_TIME          STATUS$ MODE$  OGUID       DSC_SEQNO   DSC_ROLE
---------- -------- ------------- --------------- --------------------- -------------------------- ------------------- ------------------- ------- ------ ----------- ----------- --------
1          DMSERVER DMSERVER      1               localhost.localdomain DM Database Server x64 V8  DB Version: 0x7000c 2021-09-17 17:10:37 OPEN    NORMAL 0           0           NULL

已用时间: 8.642(毫秒). 执行号:500.
SQL>

查看当前数据库名称

SQL> select * from v$database;

行号     NAME   CREATE_TIME         ARCH_MODE LAST_CKPT_TIME      STATUS$     ROLE$       MAX_SIZE             TOTAL_SIZE           DSC_NODES   OPEN_COUNT  STARTUP_COUNT        LAST_STARTUP_TIME  
---------- ------ ------------------- --------- ------------------- ----------- ----------- -------------------- -------------------- ----------- ----------- -------------------- -------------------
1          DAMENG 2021-09-17 15:46:43 N         2021-09-17 17:25:37 4           0           0                    20480                1           4           4                    2021-09-17 17:10:37

查看当前用户名

SQL> select current_user;

行号     CURRENT_USER
---------- ------------
1          DMHR

创建表空间

create tablespace dmhr datafile 'dmhr.dbf' size 128;

创建用户

create user dmhr identified by 123456789 default tablespace dmhr;   
--授权
grant dba to dmhr;



-- 修改密码策略, 从而可以使用简单密码
select * from v$parameter where name='PWD_POLICY';
ALTER system set 'PWD_POLICY'=0;

-- 删除用户
drop user postgres cascade;

-- 创建用户
create user postgres identified by passwd;
grant dba to postgres;


drop user test cascade;
create user test identified by test;
grant dba to test;

drop user test2 cascade;
create user test2 identified by test2;
grant dba to test2;


drop user test1 cascade;
create user test1 identified by test1;
grant dba to test1;

密码策略

达梦数据库设置密码策略
达梦数据库跟Oracle数据库不一样，它的密码策略是通过设置参数的值来实现。这个参数是PWD_POLICY。这个参数有6个值分别代表不同的密码策略：
0 无策略
1 禁止与用户名相同
2 口令长度不小于 9
4 至少包含一个大写字母（A-Z）
8 至少包含一个数字（0-9）
16 至少包含一个标点符号（英文输入法状态下， 除“和空格外的所有符号）

select * from v$parameter where name='PWD_POLICY';
ALTER system set 'PWD_POLICY'=0;

序列的使用

--创建序列
CREATE SEQUENCE seq_test_id ;
-- 查看序列
SELECT * FROM dba_sequences WHERE SEQUENCE_NAME='SEQ_TEST_ID';

-- 使用序列 序列名.nextval
DROP TABLE IF EXISTS test;
CREATE TABLE test(id int, info text);
INSERT INTO test values(seq_test_id.nextval, 'a1');
INSERT INTO test values(seq_test_id.nextval, 'a2');
INSERT INTO test values(seq_test_id.nextval, 'a3');
INSERT INTO test values(seq_test_id.nextval, 'a4');
INSERT INTO test values(seq_test_id.nextval, 'a5');
SELECT * FROM test;

-- 查看当前值
select seq_test_id.currval();
设置序列初始值

时间相关运算

-- 日期间隔, 返回天数
SQL> select date '2021-09-11' - date '2021-08-11';

行号     DATE'2021-09-11'-DATE'2021-08-11'
---------- ---------------------------------
1          31

已用时间: 0.105(毫秒). 执行号:428.
SQL> 



SQL> SELECT (TIME'19:00:00'-TIME'10:00:00') HOUR;

行号     (TIME'19:00:00'-TIME'10:00:00')HOUR
---------- -----------------------------------
1          INTERVAL '9' HOUR(9)

-- 时间戳返回
SQL> select (timestamp '2021-09-11 00:00:00' - timestamp '2021-08-11 00:00:00' ) DAY;

行号     (DATETIME'2021-09-1100:00:00'-DATETIME'2021-08-1100:00:00')DAY
---------- --------------------------------------------------------------
1          INTERVAL '31' DAY(9)

已用时间: 0.341(毫秒). 执行号:439.
SQL> 
SQL> select (timestamp '2021-09-11 06:00:00' - timestamp '2021-09-11 00:00:00' ) hour;

行号     (DATETIME'2021-09-1106:00:00'-DATETIME'2021-09-1100:00:00')HOUR
---------- ---------------------------------------------------------------
1          INTERVAL '6' HOUR(9)

已用时间: 0.097(毫秒). 执行号:440.
SQL> 
SQL> select (timestamp '2021-09-11 00:05:00' - timestamp '2021-09-11 00:00:00' )MINUTE;

查看系统参数相关视图或者表

select * from v$parameter ;
select * from v$parameter where name='PWD_POLICY';

命令行执行 SQL文件

echo "select sysdate" >> test.sql

disql SYSDBA/SYSDBA@207.207.35.101:5236 << EOF 
> start test.sql
> EOF


[dmdba@localhost.localdomain:/home/dmdba]$ disql SYSDBA/SYSDBA@207.207.35.101:5236 << EOF 
> start test.sql
> EOF
open fail! path:./libcrypto.so

服务器[207.207.35.101:5236]:处于普通打开状态
登录使用时间 : 0.790(ms)
disql V8
SQL> SQL> select sysdate;

行号     SYSDATE            
---------- -------------------
1          2021-09-23 10:09:11

已用时间: 1.088(毫秒). 执行号:500.
SQL>

方式2

disql postgres/LXMlxm123@207.207.35.101:5236 \`test.sql

域 Domain 的使用

CREATE DOMAIN imos_id as bigint;
create table test2(id imos_id);
insert into test2 (id) values(1),(2);
select * from test2;

函数的使用

set serveroutput on;
create table test(id int, name varchar);
insert into test values(1,'a'),(2,'b');

CREATE OR replace FUNCTION func_test(v_id int)
RETURN varchar
AS 
	tmp_name varchar:='';
BEGIN
	SELECT name INTO tmp_name FROM test WHERE id=v_id;	
	print('hello');
	RETURN 'sdfsdf';
END;
/
SELECT func_test(1) ;


set serveroutput on;
CREATE OR replace FUNCTION func_test2()
RETURN varchar
AS 
	v_id ia_int32:=0; -- 在 存储过程与函数中不支持 domain 的使用, 会报错
	tmp_name varchar:='';
BEGIN
	SELECT name INTO tmp_name FROM test WHERE id=v_id;	
	print('hello');
	RETURN 'sdfsdf';
END;
/
SELECT func_test2(1) ;

存储过程的使用


set serveroutput on;
CREATE OR REPLACE PROCEDURE "pro_func_test"(V_INT INT)
AS
	VARNAME INT;
BEGIN
	DBMS_OUTPUT.PUT_LINE('HELLO');
END;
/

CALL "pro_func_test"(1)


CREATE OR REPLACE PROCEDURE "pro_func_test2"(V_INT INT)
AS
	VARNAME INT;
BEGIN
	print('hello world');
END;
/

CALL "pro_func_test2"(1)

其他维护性SQL

# 密码策略管理
-- 查看系统的密码策略
select * from v$dm_ini a where a.PARA_NAME = 'PWD_POLICY';
或者
select * from v$parameter where name='PWD_POLICY';

-- 修改系统的密码策略
SP_SET_PARA_VALUE(1,'PWD_POLICY',0);
或者
ALTER system set 'PWD_POLICY'=0;


# 用户管理
-- 创建用户
create user postgres  identified by passwd ;
grant dba to postgres;


-- 查看每个用户的密码策略以及账号状态
select username,password_versions,account_status from dba_users;
SYSSSO	0	OPEN
SYSDBA	0	OPEN
POSTGRES	0	OPEN
SYS	0	OPEN
SYSAUDITOR	0	OPEN


-- 查询授权截止有效期
select EXPIRED_DATE  from v$license;


-- 查看当前用户名称
select current_user;

--查看当前实例名称
select * from v$instance;

--查看当前实例(数据库)状态
select status$ from v$instance;

-- 查看当前数据库名称以及合适创建的, 是否开启了归档等等信息
select  from v$database;

-- 查看某个数据库是否开启了归档
select  arch_mode from v$database;


--建表
create table test(id int, name varchar);

-- 查看某个用户下的某张表在那个表空间中
SELECT owner, table_name, tablespace_name ,iot_name, status FROM dba_tables where owner='POSTGRES' AND table_name='TEST' ; 


/*查看用户下指定表占用空间*/
SELECT TABLE_USED_SPACE('POSTGRES','TEST')*SF_GET_PAGE_SIZE()/1024.0/1024||'M';


-- 查看数据库配置端
select para_name,para_value from v$dm_ini where para_name like '%PORT%';

-- 查询数据库最大连接数
 select SF_GET_PARA_VALUE(2,'MAX_SESSIONS');

-- 执行计划
explain select * from test;

-- 询用户密码限制登录次数和密码过期天数 (life_time为0，表示不限制密码过期天数)
select u.username,p.FAILED_NUM,p.life_time from SYSUSERS p,dba_users u where  p.FAILED_NUM not in ('0') order by 1,2 ;
 
-- 查看TEST用户的对象,之 约束
SELECT * FROM dba_objects WHERE owner='TEST' AND OBJECT_TYPE ='CONSTRAINT' ;

 
 
 # 表空间管理
 -- 查看表空间状态
 select ID,NAME,STATUS$ from  V$TABLESPACE;
 
 
 -- 查看数据库文件
 select * from v$datafile;
 
 -- 查看表空间及其所在数据库文件详细信息
 with t1 as (
 	select ID,NAME,STATUS$ from  V$TABLESPACE 
 ), t2 as (
 	select * from v$datafile
 )
  select * from t1 join t2 on (t1.id=t2.group_id);
  
-- 查看表空间与数据库文件对应关系
  select FILE_NAME,FILE_ID,TABLESPACE_NAME  from dba_data_files;

-- 创建表空间实例,默认自动扩展
create tablespace test1 datafile 'test1.dbf' size 50;  
-- 实例: create tablespace test1 datafile '/home/dmdba/opt/dmdbms/data/DAMENG/test1.dbf' size 50;
-- 注释：创建表空间语句，自动指定自动扩展，扩展最大大小，数据页的缓冲池名称。

-- 创建非自动扩展表空间
create tablespace test1 datafile '/home/dmdba/opt/dmdbms/data/DAMENG/test1.dbf' size 50 autoextend off;

-- 指定自动扩展最大值
create tablespace test1 datafile '/home/dmdba/opt/dmdbms/data/DAMENG/test1.dbf' size 50 autoextend on maxsize 100;

-- 指定表空间多个文件组成
create tablespace test1 datafile '/home/dmdba/opt/dmdbms/data/DAMENG/test1_01.dbf' size 50 autoextend on next 5 maxsize 100,'/home/dmdba/opt/dmdbms/data/DAMENG/test1_02.dbf' size 50 autoextend on next 5 maxsize 100;

-- 表空间新增数据文件
alter tablespace test1 add datafile '/home/dmdba/opt/dmdbms/data/DAMENG/test2.dbf' size 50 ;

-- resize原先数据文件大小(只能由小变大, 不能有大resize小)
alter tablespace test1 resize datafile '/home/dmdba/opt/dmdbms/data/DAMENG/test1.dbf' to 100 ;

-- 表空间位置更改
alter tablespace test1 offline;
alter tablespace test1 rename datafile '/home/dmdba/opt/dmdbms/data/DAMENG/test1.dbf' to '/home/dmdba/opt/dmdbms/data/test1.dbf';
alter tablespace test1 online;

-- 表空间删除
drop tablespace test1;

-- 查询数据库字符集
select SF_GET_UNICODE_FLAG();

# 数据库文件/空间信息
*  查询归档信息
select * from v$dm_arch_ini;

* 查看控制文件
select para_value name from v$dm_ini where para_name='CTL_PATH';

*查看日志文件
select GROUP_ID ,FILE_ID,PATH,CLIENT_PATH from v$rlogfile;

* 查询数据库占用空间
select sum(bytes/1024/1024)|| 'M' from dba_data_files;

* 查询数据文件位置
select GROUP_ID , ID ,path,STATUS$ from v$datafile;

* 查询表空间大小
select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024||'M'  from dba_data_files;


* 查看表空间使用情况
select t1.NAME tablespace_name,
    t2.FREE_SIZE*SF_GET_PAGE_SIZE()/1024/1024 ||'M' free_space,
    t2.TOTAL_SIZE*SF_GET_PAGE_SIZE()/1024/1024 ||'M' total_space,
    t2.FREE_SIZE*100/t2.total_size "% FREE" 
    from V$TABLESPACE t1, V$DATAFILE t2 where t1.ID=t2.GROUP_ID;

* 查看当前用户的模式    
SELECT SYS_CONTEXT ('userenv', 'current_schema') FROM DUAL;
    
    
    
# 数据库用户/角色信息
* 查询数据库有哪些用户
select username from dba_users;

* 查询数据库用户信息
select username,user_id,default_tablespace,profile from dba_users;

* 查询数据库中所有对象类型
select t2.name owner,t1.subtype$ object_type,t1.valid status,count(1) count# from sysobjects t1,sysobjects t2 where t1.schid=t2.id and t1.schid!=0 group by t2.name,t1.subtype$,t1.valid;

* 查询用户对象(表, 索引等等)
select * from user_objects;
 
* 查看有哪些角色
select * from dba_roles

* 查看某个用户的角色和权限
select * from sys.dba_role_privs where grantee='POSTGRES';

* 表空间脱机/在线
alter tablespace test01 offline;
alter tablespace test01 online;

* 查询总标数量
select count(*) from dba_tables;

* 关闭数据库
SQL> shutdown normal;  关闭数据库
SQL> shutdown immediate;  正常方式关闭数据库
SQL> shutdown abort;  立即方式关闭数据库。数据库并不立即关闭，而是在执行某些清除工作后才关闭（终止会话、释放会话资源），需要10到20S。
 
 
 
# 数据库表/列/视图信息
*查询当前用户所有表
select* from user_tables

* 查询表的大小 (注释：页数，默认8KB大小)
 SELECT TABLE_USED_SPACE ('SYS','SYSOBJECTS') ;
 
* 创建新表
create table tests ( id char not null) ;

* 新增列
select * from tests;
alter table tests add column ids int;
select * from tests;
alter table tests add info text;  

select * from tests;

* 删除列
alter table tests drop column info;
select * from tests;
alter table tests drop ids;
select * from tests;

* 重命名
alter table tests rename to test01;
* 添加表注释
comment on table test01 is 'test01';

* 查询表注释
select comments from user_tab_comments where table_name = 'TEST01';

* 增加列注释
comment on column test01.id is 'id';

* 查询列注释
select * from user_col_comments where  table_name = 'TEST01' and column_name = 'ID';
 
# 额外
## 表空间相关
	-- 查询所有表空间
	select * from V$TABLESPACE;
	-- 查询模式
	SELECT * FROM dba_segments;
	-- 通过系统视图模式查找
	SELECT object_name from all_objects where object_type = 'SCH';
	-- 查询系统视图
	select * from SYSTABLECOMMENTS; 
	-- 查询dba总表数量
	select count(*) from dba_tables;
	-- 查询用户所有表
	select * from user_tables;
	-- 查询dba表空间
	select * from dba_tablespaces;
	-- 查询用户表空间
	select * from user_tablespaces;
	-- 查询所有对象
	SELECT * from all_objects;
	-- 查询dba对象
	select * from dba_objects;
	-- 查询用户对象
	select * from user_objects;
	-- 查询dba用户
	select * from dba_users;

## 表属性相关操作
	-- 建表语句
	create table tests ( id char not null) 
	
	-- 获取表字段
	select * from user_tab_columns where Table_Name='test';
	select * from user_tab_columns where Table_Name='test_table';
	
	-- 表重命名
	alter table test rename to test_table
	
	-- 查询表注释
	select comments from user_tab_comments where table_name = 'test_table'
	
	-- 修改表注释
	comment on table test_table is '测试';

#  列属性相关操作
	-- 增加列
	alter table test_table add ids int
	
	-- 增加一列不为空
	alter table test_table add id_notnull int not null
	
	-- 类型int 小于9999 主键 
	alter table test_table add id_int int not null primary key check(id_int < 9999)
	
	-- 类型 varchar2 长度 2
	alter table test_table add id_varchar2 varchar(2)
	
	-- 删除列，列名：ids 
	alter table test_table drop ids
	
	-- 修改列名
	ALTER TABLE 表名 RENAME COLUMN 以后列名 TO 新列名;
	alter table test_table rename column idss to ids
	
	-- 查询列注释
	select * from user_col_comments where owner = 'zfgxpt' and table_name = 'test_table' and column_name = 'id'
	
	-- 修改列注释
	comment on column test_table.id is '主键'; 

select * from sysdba.test;
select * from postgres.test;
select dbms_metadata.get_ddl('TABLE','TEST2','POSTGRES');
CALL SP_TABLEDEF('POSTGRES','TEST2');
	
CREATE TABLE "POSTGRES"."TEST2"
(
"ID" BIGINT) STORAGE(ON "MAIN", CLUSTERBTR) ;


CREATE DOMAIN imos_id as bigint;
create table test2(id imos_id);
insert into test2 (id) values(1),(2);
select * from test2;

系统表

*判断某个表是否存在
select  * from DBA_TABLES where TABLE_NAME='TBL_STKEX_NONMOTOR';

* 判断某个表的某个字段是否存在
SELECT * FROM DBA_TAB_COLUMNS where TABLE_NAME='TBL_STKEX_NONMOTOR' and COLUMN_NAME = 'RENEW_TIME';

* 判断某个表的某个约束是否存在
	-- 主键
	SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE='P' AND OWNER='TEST'  AND TABLE_NAME='TA' and CONSTRAINT_NAME='PK_TA';

	-- 唯一键
	SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE='U' AND OWNER='TEST'  AND TABLE_NAME='TA' and CONSTRAINT_NAME='UK_TA_NAME';;

	-- 外键
	SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE='R' AND OWNER='TEST'  AND TABLE_NAME='TA' and CONSTRAINT_NAME='FK_TB_NAME';

	-- check 约束
	* 如果直接在表定义中设置check约束, 此时check约束名称不是 见名知意
	SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE='C' AND OWNER='TEST'  AND TABLE_NAME='TA' ;
	
	* 如果check约束是通过 atler table add constraint 来添加, 此时可以通过 CONSTRAINT_NAME 知道 check约束名称
	SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE='C' AND OWNER='TEST'  AND TABLE_NAME='TA' and constraint_name='CHECK_TA_AGE' ;


* 判断某个表的索引是否存在
select * from DBA_INDEXES WHERE OWNER='TEST' AND TABLE_NAME='TA'
	表默认有个cluster索引, 非唯一
	对于主键/唯一键约束,对应会生成一个索引, 该索引名称无法看出是主键还是唯一键, 可以通过 DBA_CONSTRAINTS 来查看
	对于普通索引,由于在定义的时候需要指定索引名称, 所以在 dba_indexes中是可以看到该索引具体名称的
	
* 判断某个函数是否存在
SELECT * FROM DBA_PROCEDURES WHERE OWNER='TEST' AND OBJECT_NAME='FUNC_TEST'

*判断某个触发器是否存在
select * from DBA_TRIGGERS where  OWNER='TEST' AND TRIGGER_NAME='TRI_STKDD';

* 判断某个视图是否存在
select * from DBA_VIEWS where  OWNER='TEST' AND  VIEW_NAME='T_STK' ;

* 判断某个类型是否存在
select * from DBA_SOURCE where name='TYPE_INT';
	TYPE定义如下:
		CLASS:自定义类型 通过 create type xx as object();来定义
		TRIG:触发器
		PROC: 函数
		PACKAGE: 包
		PACKAGE BODY: 包提
		TYPE: 通过 create type xx as ; 来定义

* 判断某个序列是否存在
select * FROM dba_sequences where  OWNER='TEST' AND  sequence_name='SEQ_Q';

表约束相关

1、修改数据表字段类型和长度
--设置字段类型和长度

alter table "SYSDBA"."MY_TABLE" modify "ID" VARCHAR2(50);

2、增加和去除唯一性设置
注意：唯一性与主键互斥，只能选其一。获取唯一性约束的KEY，参见后面的语句。

--增加唯一

alter table "SYSDBA"."MY_TABLE"  add constraint U_ID unique(id);

--去掉唯一

alter table "SYSDBA"."MY_TABLE"  drop constraint U_ID ;

3、设置可空或不可空特性
--可空

alter table "SYSDBA"."MY_TABLE" alter column "ID" set null;

--非空

alter table "SYSDBA"."MY_TABLE" alter column "ID" set not null;

4、增加主键和去除主键
--增加主键

alter table "SYSDBA"."MY_TABLE" add primary key("ID");

--去掉主键

alter table "SYSDBA"."MY_TABLE" drop constraint "CONS134237151";--主键ID的获取参见第7条

alter table "SYSDBA"."MY_TABLE" alter column "ID" set null;

5、查询数据库中的表
SELECT * FROM ALL_TABLES; --查询表

6、查看数据表定义
SELECT TABLEDEF('SYSDBA','MY_TABLE'); --查看表定义，入参：模式名,表名

7、查看表具有的约束
--查看所有主键约束

SELECT * FROM ALL_CONSTRAINTS WHERE CONSTRAINT_TYPE='P';

--所有约束种类

SELECT DISTINCT CONSTRAINT_TYPE FROM ALL_CONSTRAINTS;

CONSTRAINT_TYPE取值：

C：检验约束    
P：主键约束    
U：唯一性约束    
R：外键约束    
V：未知（编者注）

--查看某张表所有约束
SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME='MY_TABLE';

分页相关

SELECT TOP 1 * FROM test_proc ORDER BY name;  --top
SELECT TOP 0,2 * FROM test_proc ORDER BY name;  --top [start, end)
SELECT * FROM test_proc ORDER BY name  offset 0 LIMIT 1; -- limit offset

分析函数

构造数据

CREATE OR REPLACE VIEW v
AS
SELECT '10' deptno, 'CLARK' name, '800' sal FROM DUAL
UNION ALL
SELECT '10', 'KING', '900' FROM DUAL
UNION ALL
SELECT '20', 'JAMES', '1000' FROM DUAL
UNION ALL
SELECT '20', 'KATE', '2000' FROM DUAL
UNION ALL
SELECT '30', 'JONES', '1150' FROM DUAL;

正序

-- 正序
SELECT deptno,SUM (sal) AS total_sal,
LISTAGG (name, ',') WITHIN GROUP (ORDER BY name ) AS total_name
FROM v
GROUP BY deptno;

--结果
行号     DEPTNO TOTAL_SAL TOTAL_NAME
---------- ------ --------- ----------
1          10     1700      CLARK,KING
2          20     3000      JAMES,KATE
3          30     1150      JONES

逆序

SELECT deptno,SUM (sal) AS total_sal,
LISTAGG (name, ',') WITHIN GROUP (ORDER BY name desc) AS total_name
FROM v
GROUP BY deptno;

--结果
行号     DEPTNO TOTAL_SAL TOTAL_NAME
---------- ------ --------- ----------
1          10     1700      KING,CLARK
2          20     3000      KATE,JAMES
3          30     1150      JONES

日期相关

-- 日前加减 一段时间
SELECT CURRENT_DATE, ADD_DAYS(CURRENT_DATE,1) AS 后一天, ADD_DAYS(CURRENT_DATE,-1) AS 前一天;
SELECT ADD_MONTHS(CURRENT_DATE, 0), ADD_MONTHS(CURRENT_DATE, 1) AS 后一月 , ADD_MONTHS(CURRENT_DATE, -1) 前一月;
SELECT ADD_MONTHS(CURRENT_DATE, 0), ADD_MONTHS(CURRENT_DATE,12*1) 明年,     ADD_MONTHS(CURRENT_DATE,12*-1) 前年;


SELECT now(), DATEADD(YEAR, 1, now());-- 2021-10-11 10:46:00	2022-10-11 10:46:00
SELECT now(),DATEADD(MONTH, 1, now());-- 2021-10-11 10:46:09	2021-11-11 10:46:09
SELECT now(),DATEADD(DAY, 1, now());  -- 2021-10-11 10:46:17	2021-10-12 10:46:17
SELECT now(),DATEADD(HOUR, 1, now()); -- 2021-10-11 10:46:28	2021-10-11 11:46:28
SELECT now(),DATEADD(MINUTE, 1, now());--2021-10-11 10:46:42	2021-10-11 10:47:42
SELECT now(),DATEADD(SECOND, 1, now());--2021-10-11 10:46:48	2021-10-11 10:46:49


-- 日期间隔计算


-- 从日期中获取 元信息(年月日时分秒)
SELECT 
	CURRENT_TIMESTAMP , 
	extract(YEAR FROM CURRENT_TIMESTAMP) 年,
	extract(MONTH FROM CURRENT_TIMESTAMP) 月,
	extract(day FROM CURRENT_TIMESTAMP) 日,
	extract(hour FROM CURRENT_TIMESTAMP) 时,
	extract(minute FROM CURRENT_TIMESTAMP) 分,
	extract(second FROM CURRENT_TIMESTAMP) 秒;

	
行号     CURRENT_TIMESTAMP                 年         月         日         时         分         秒                      
---------- --------------------------------- ----------- ----------- ----------- ----------- ----------- -------------------------
1          2021-10-11 09:51:38.540971 +08:00 2021        10          11          9           51          3.854097100000000E+01


其他函数:
1.ADD_DAYS(data,n):返回日期 date加上相应天数n后的日期值。n可以是任意整数，date是日期类型(DATE)或时间戳类型(TIMESTAMP)，返回值为日期类型(DATE)。

select ADD_DAYS(date '2020-03-30',2);



2.ADD_MONTHS(date,n):返回日期 date加上n个月的日期时间值。n可以是任意整数，date是日期类 型(DATE)或时间戳类型(TIMESTAMP)，返回类型固定为日期类型(DATE)。如果相加之后的结果日期中月份所包含的天数比date日期中的日分量要少，那么结果日期的该月最后一 天被返回。

select ADD_MONTHS(date '2020-01-31',1);



3.ADD_WEEKS(date,n):返回日期 date加上相应星期数n后的日期值。n可以是任意整数，date 是日 期类型(DATE)或时间戳类型(TIMESTAMP)，返回类型固定为日期类型(DATE)。  

select ADD_WEEKS(date '2020-3-19',1);

 

4.CURDATE()/CURRENT_DATE：返回当前的日期。

5. CURTIME()/CURRENT_TIME/LOCALTIME (n):返回当前时间。

select CURTIME(); 



6.CURRENT_TIMESTAMP(n):返回当前带会话时区的时间戳，结果类型为TIMESTAMP WITH TIME ZONE。 参数：n：指定毫秒的精度。取值范围0-6，默认为6。 

SELECT   CURRENT_TIMESTAMP();

 

7.DATEADD(datepart,n,date): 向指定的日期date加上n个datepart指定的时间段，返回新的timestamp 值。datepart可以为YEAR(缩写YY或YYYY)、QUARTER(缩写QQ或Q)、MONTH(缩写 MM或M)、DAYOFYEAR(缩写DY或Y)、DAY(缩写DD或D)、WEEK(缩写WK或WW)、HOUR(缩 写HH)、MINUTE(缩写MI或N)、SECOND(缩写SS或S)和MILLISECOND(缩写MS)。 

8.DATEDIFF/BIGDATEDIFF(datepart,date1,date2):返回跨两个指定日期的日期和时间边界数。

9.DATEPART/DATE_PART(datepart,date) ：返回代表日期 data 的指定部分的整数。

10.DAYNAME(date)：返回日期对应星期几。

select DAYNAME(date '2020-03-19');



11.DAYOFMONTH(date):返回日期是当月的第几天。

select DAYOFMONTH(date '2020-03-19');

 

12.DAYOFWEEK(date)：返回日期是当前周第几天。

13.DAYOFYEAR(date):返回日期是当年第几天。

14.DAYS_BETWEEN(dt1,dt2):返回两个日期相差天数。

15.EXTRACT(dtfield FROM date):：EXTRACT从日期时间类型或时间间隔类型的参数date中抽取dtfield对应的 数值，并返回一个数字值。如果date是NULL，则返回NULL。Dtfiled 可以是YEAR、 MONTH、DAY、HOUR、MINUTE、SECOND。对于 SECOND 之外的任何域，函数返回整数， 对于SECOND返回小数。 

16.GETDATE(n)/NOW(n):返回系统的当前时间戳。

select GETDATE();



17.GREATEST(n1,n2,n3):求n1、n2和n3中的最大日期。

18.GREAT (n1,n2):求n1、n2中的最大日期。 

19.HOUR(time):返回时间中的小时值。

20.LAST_DAY(date):返回 date 所在月最后一天的日期,date 是日期类型(DATE)或时间戳类型 (TIMESTAMP)，返回类型与date相同。 

21.LEAST(n1,n2,n3):求n1、n2和n3中的最小日期。

22.MINUTE(time):返回时间中的分钟值。

23.MONTH(date):返回日期中的月份值。

24.MONTHNAME(date):返回日期中月份分量的名称。 

25.MONTHS_BETWEEN(date1,date2):返回date1和date2之间的月份值。如果date1比date2晚，返回正值，否 则返回负值。如果date1和date2这两个日期为同一天，或者都是所在月的最后一天，则 返回整数，否则返回值带有小数。date1 和 date2 是日期类型(DATE)或时间戳类型 (TIMESTAMP)。 

26.NEXT_DAY(date,char):返回在日期date 之后满足由 char 给出的条件的第一天。char 指定了一周中 的某一个天(星期几)，返回值的时间分量与date相同，char是大小写无关的。

select next_day(date '2020-03-19','FRI');



27. QUARTER(date):返回日期在所处年中的季度数。 

select QUARTER(date '2020-03-19');



28.SECOND(time):返回时间中的秒值。

29.ROUND(date[, fmt]):：将日期时间 date 四舍五入到最接近格式参数 fmt 指定的形式。如果没有指定 语法的话，到今天正午 12P.M.为止的时间舍取为今天的日期，之后的时间舍取为第二天 12A.M.。日期时间 12A.M.，为一天的初始时刻。参数 date 的类型可以是 DATE 或 TIMESTAMP，但应与 fmt相匹配。函数的返回结果的类型与参数date相同。

30.TIMESTAMPADD(interval,n,timestamp):返回时间戳timestamp加上n个interval代表的时间间隔的结果。interval 可以为 SQL_TSI_FRAC_SECOND 、 SQL_TSI_SECOND 、 SQL_TSI_MINUTE 、 SQL_TSI_HOUR 、 SQL_TSI_DAY 、 SQL_TSI_WEEK 、 SQL_TSI_MONTH 、 SQL_TSI_QUARTER和SQL_TSI_YEAR。

31.TIMESTAMPDIFF(interval,timestamp1,timestamp2):返回一个表明 timestamp2 与 timestamp1 之间的 interval 类型的时间间 隔的整数。interval 可以为 SQL_TSI_FRAC_SECOND 、 SQL_TSI_SECOND 、 SQL_TSI_MINUTE 、 SQL_TSI_HOUR 、 SQL_TSI_DAY 、 SQL_TSI_WEEK 、 SQL_TSI_MONTH、SQL_TSI_QUARTER和SQL_TSI_YEAR。 

32.SYSDATE():获取系统当前时间。

33.TO_DATE (char [,fmt]) /TO_TIMESTAMP (char [,fmt]):将CHAR或者VARCHAR类型的值转换为DATE/TIMESTAMP数据类型。

SELECT TO_DATE('2020/03/19','YYYY/MM/DD'); 



34.FROM_TZ(timestamp,timezone|tz_name]):：将时间戳类型timestamp和时区类型timezone（或时区名称 tz_name）转化 为timestamp with timezone类型 。 

35.TRUNC(date[, fmt]):：将日期时间date截断到最接近格式参数fmt 指定的形式。若 fmt 缺省，则返 回当天日期。语法与ROUND 类似，但结果是直接截断，而不是四舍五入。

36.WEEK(date):返回指定日期属于所在年中的第几周。

37.WEEKDAY(date)：返回指定日期的星期值。如果是星期日则返回0。 

38.WEEKS_BETWEEN(date1,date2):返回两个日期之间相差周数。

39.YEAR(date):返回日期的年份值。

40.YEARS_BETWEEN(date1,date2):返回两个日期之间相差年数。

41.OVERLAPS (date1,date2,date3,date4):返回两两时间段是否存在重叠，date1 为 datetime 类型、date2 可以为 datetime 类型也可以为 interval 类型，date3 为 datetime 类型，date4 可为 datetime类型，也可以interval类型，判断(date1,date2),(date3,date4)有无 重叠。其中date2与date4类型必须一致，如果date2为interval year to month， date4也必须是此类型。结果类型为BIT，若两个时间段存在重叠返回1，不重叠返回0。
42.TO_CHAR(date[,fmt]):：将日期数据类型DATE转换为一个在日期语法fmt中指定语法的VARCHAR类型字符串。

SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD');



43.SYSTIMESTAMP(n):返回系统当前的时间戳，带数据库的时区信息。结果类型为TIMESTAMP WITH TIME ZONE。 

44.WEEK(date, mode):根据指定的mode返回日期为所在年的第几周

常用函数

参考:https://www.modb.pro/db/34517

类型转换

to_number

SELECT to_number('123');

CREATE OR REPLACE FUNCTION GET_SEX(id_card IN VARCHAR(50)) 
RETURN CHAR(2) AS v_sex CHAR(2);
BEGIN
	IF to_number(SUBSTR(id_card, 17, 1))% 2 = 1 THEN v_sex := '男';
	ELSE v_sex := '女';
	END IF;

	RETURN v_sex;
END;

SELECT GET_SEX('421221199211200073');

to_char


SELECT to_Char(123);
SELECT to_Char('11.101','99.99'); -- 11.10
SELECT to_char(CURRENT_TIMESTAMP, 'yyyy-mm-dd');  -- 2021-10-11

其他

SELECT to_Date('2021-10-11'); --2021-10-11 00:00:00
SELECT to_timestamp('2021-10-11') ; --2021-10-11 00:00:00

SELECT TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND,'2003-02-14 12:10:10.000', '2003-02-14 12:09:09.300');
SELECT TIMESTAMPDIFF(SQL_TSI_QUARTER, '2003-06-01', DATE '2002-01-01');
SELECT TIMESTAMPDIFF(SQL_TSI_MONTH, '2001-06-01', DATE '2002-01-01');
SELECT TIMESTAMPDIFF(SQL_TSI_WEEK, DATE '2003-02-07',DATE '2003-02-19');

物化视图

实例

实例1


DROP TABLE IF EXISTS test_view cascade;
DROP TABLE IF EXISTS test_view2 cascade;
CREATE TABLE test_view (id int, name varchar);
CREATE TABLE test_view2 (id int, name varchar);
INSERT INTO test_view values(1,'a'),(2,'b');
INSERT INTO test_view values(3,'c');
INSERT INTO test_view2 values(1,'a'),(2,'b');

DROP MATERIALIZED   VIEW view_test CASCADE;
CREATE MATERIALIZED  VIEW view_TEST BUILD IMMEDIATE REFRESH WITH PRIMARY KEY ON COMMIT AS
SELECT test_View.id, test_view.name, test_view2.id AS id2, test_view2.name AS name2 FROM test_view JOIN test_view2 ON (test_view.id=test_View2.id);
SELECT * FROM view_test;
INSERT INTO test_view2 values(3,'c');
SELECT * FROM view_test;

实例2


DROP TABLE IF EXISTS test_view cascade;
DROP TABLE IF EXISTS test_view2 cascade;
CREATE TABLE test_view (id int, name varchar);
CREATE TABLE test_view2 (id int, name varchar);
INSERT INTO test_view values(1,'a'),(2,'b');
INSERT INTO test_view values(3,'c');
INSERT INTO test_view2 values(1,'a'),(2,'b');

DROP MATERIALIZED   VIEW view_TEST2 CASCADE;
CREATE MATERIALIZED  VIEW view_TEST2 BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT  SYSDATE + INTERVAL '1' day  AS
SELECT test_View.id, test_view.name, test_view2.id AS id2, test_view2.name AS name2 FROM test_view JOIN test_view2 ON (test_view.id=test_View2.id);
SELECT * FROM view_test2;
INSERT INTO test_view2 values(3,'c');
SELECT * FROM view_test2;

查看物化视图信息

SELECT * FROM user_MVIEWS ;

随机函数

 CREATE OR REPLACE FUNCTION random_password
    (pass_len IN NUMBER) RETURN VARCHAR2 AS
    l_pw VARCHAR2(128);
    BEGIN l_pw = dbms_random.string('x', pass_len);
    RETURN l_pw;
    END;

with function子句

WITH FUNCTION GetSalary(emp_id INT) RETURN INT AS
     DECLARE
     sal int;
     BEGIN
     SELECT salary into sal FROM dmhr.employee WHERE employee_id=emp_id;
     RETURN sal;
     END;
     SELECT GetSalary(2001) FROM DUAL；

DM存储过程与其他适配PG方案

集合类型

varray

VARRAY 是一种具有可伸缩性的数组，
数组中的每个元素具有相同的数据类型。
VARRAY 在定义时由用户指定一个最大容量
其元素索引是从 1 开始的有序数字

实例

TYPE my_array_type IS VARRAY(10) OF INTEGER;  -- 声明一个 int[10] 的数组类型 my_array_type
v MY_ARRAY_TYPE;

使用 v.COUNT()方法可以得到数组 v 当前的实际大小
v.LIMIT() 则可获得数组 v 的最大容量。
需要注意的是，VARRAY 的元素索引总是连续的
VARRAY 最初的实际大小为 0，使用 EXCTEND()方法可扩展 VARRAY 元素个数

实例

DECLARE
TYPE MY_ARRAY_TYPE IS VARRAY(10) OF VARCHAR(100);
v MY_ARRAY_TYPE;
BEGIN
v:=MY_ARRAY_TYPE();
PRINT 'v.COUNT()=' || v.COUNT();
FOR I IN 1..8 LOOP
v.EXTEND();
SELECT NAME INTO v(I) FROM PERSON.PERSON WHERE PERSON.PERSONID=I;
END LOOP;
PRINT 'v.COUNT()=' || v.COUNT();
FOR I IN 1..v.COUNT() LOOP
PRINT 'v(' || i || ')=' ||v(i);
END LOOP;
END;

嵌套表

嵌套表类似于一维数组，但与数组不同的是，嵌套表不需要指定元素的个数，其大小
可自动扩展。
嵌套表元素的下标从 1 开始

实例1

DECLARE
TYPE Info_t IS TABLE OF SALES.SALESPERSON%ROWTYPE;
info Info_t;
BEGIN
SELECT SALESPERSONID,EMPLOYEEID,SALESTHISYEAR,SALESLASTYEAR BULK COLLECT
INTO info FROM SALES.SALESPERSON WHERE SALESTHISYEAR>1000;
END;

索引表

异常处理

drop sequence  seq_test_proc_id;
create sequence seq_test_proc_id;

drop table if exists test_proc cascade;
create table test_proc (id int, name varchar, age int, birth_date date, created_time timestamp);

alter table test_proc alter column id set default seq_test_proc_id.nextval;

alter table test_proc add constraint pk_test_proc primary key(id);

insert into test_proc (name, age, birth_date, created_time) values('张三', 10, date '1999-11-11', current_timestamp);

SELECT * FROM test_proc;

insert into test_proc (name, age, birth_date, created_time) values('李四', 20, date '1998-01-11', current_timestamp);



CREATE OR REPLACE FUNCTION func_test() 
RETURN test_proc%rowtype
AS	
	TYPE rec_test_proc IS record
		(id test_proc.id%TYPE,
		name test_proc.name%TYPE,
		age test_proc.age%TYPE, 
		birth_date test_proc.birth_date%TYPE,
		created_time  test_proc.created_time%TYPE
		);
	rec rec_test_proc;
BEGIN
	SELECT * INTO rec FROM test_proc  ;
	print 'id='||rec.id ;
	PRINT 'name='||rec.name;
	PRINT 'age='||rec.age;
	PRINT 'birth_date='||rec.birth_date;
	PRINT 'created_time='||rec.created_time;
	RETURN NULL;
EXCEPTION
	WHEN TOO_MANY_ROWS THEN 
		PRINT 'TO MANY ROWS';

END;

call func_test();


CREATE OR REPLACE FUNCTION func_test2() 
RETURN test_proc%rowtype
AS	
	TYPE rec_test_proc IS record
		(id test_proc.id%TYPE,
		name test_proc.name%TYPE,
		age test_proc.age%TYPE, 
		birth_date test_proc.birth_date%TYPE,
		created_time  test_proc.created_time%TYPE
		);
	rec rec_test_proc;
BEGIN
	SELECT * INTO rec FROM test_proc WHERE ID=1 ;
	print 'id='||rec.id ;
	PRINT 'name='||rec.name;
	PRINT 'age='||rec.age;
	PRINT 'birth_date='||rec.birth_date;
	PRINT 'created_time='||rec.created_time;
	RETURN NULL;
EXCEPTION
	WHEN TOO_MANY_ROWS THEN 
		PRINT 'TO MANY ROWS';
	when others then 
		null;
END;
/
call func_test2();

动态 SQL

在 DMSQL 程序中，DDL 语句只能通过动态 SQL 执行。
下列语句不能作动态SQL 语句：CLOSE 、DECLARE 、FETCH 、OPEN 。
动态 SQL 中可以使用参数，并支持两种指定参数的方式：用“?”表示参数和用
“:variable”表示参数
当用“?”表示参数时，在指定参数值时可以是任意的值，但参数值个数一定要与“?”
的个数相同，同时数据类型一定要匹配（能够互相转换也可以），不然会报数据类型不匹配
的错误

实例

CREATE OR REPLACE FUNCTION func_test(v_id test_proc.id%TYPE, v_name test_proc.name%type) 
RETURN INT 
AS	
	sql_cmd varchar:='';
	rec test_proc%rowtype;
BEGIN
	sql_cmd = 'select * from test_proc where id=? and name=?';
	PRINT sql_cmd;
	EXECUTE IMMEDIATE sql_cmd into rec  USING v_id, v_name ; -- using必须放到最后面
	PRINT 'rec.id='||rec.id;
	PRINT 'rec.name='||rec.name;
	RETURN 0;
END;
/

CALL func_test(1, '张三');

结果如下:
select * from test_proc where id=? and name=?
rec.id=1
rec.name=张三

返回单条记录

CREATE OR REPLACE FUNCTION func_test(v_id test_proc.id%TYPE, v_name test_proc.name%type) 
RETURN  test_proc%rowtype
AS	
declare
	sql_cmd varchar:='';
	rec test_proc%rowtype;
BEGIN
	sql_cmd = 'select * from test_proc where id=? and name=?';
	PRINT sql_cmd;
	EXECUTE IMMEDIATE sql_cmd into rec  USING v_id, v_name ;
	PRINT 'rec.id='||rec.id;
	PRINT 'rec.name='||rec.name;
	RETURN rec;
END;
/


直接执行 select func_test(1, '张三'); 会报错

select func_test(1, '张三').id AS id , func_test(1, '张三').name AS name;


* 结果:
SQL> select func_test(1, '张三').id AS id , func_test(1, '张三').name AS name;
select * from test_proc where id=? and name=?
rec.id=1
rec.name=张三
select * from test_proc where id=? and name=?
rec.id=1
rec.name=张三


行号     ID          NAME  
---------- ----------- ------
1          1           张三

通过 piplined 返回单条记录

注意在DM 中如果通过 select * into rec

后面再使用 rec.id,res_name沟通 pipe row()的时候, 会导致函数返回为空

drop table if exists test cascade;
create table test(id int, name varchar);
insert into test values(1,'a'),(2,'b');

create or replace  type type_test as object(id int, name varchar);
create or replace  type type_test_list as table of type_test;

-- 通过 piplined 返回单条记录
CREATE OR REPLACE FUNCTION func_test() 
RETURN type_test_list PIPELINED
IS
	v_id int;
	v_name varchar;
	-- 必须对于每个查询的字段, 都定义接受变量, 而不能直接通过一个结构体类型对象来接受
BEGIN	
	select id, name INTO v_id, v_name from test where id=1;
	PIPE ROW (type_test(v_id, v_name));
exception
	when others then null;
END;

select * from table(func_test);

返回多条记录(结果集)

管道表函数是可以返回行集合的函数，用户可以像查询数据库表一样查询它。目前 DM
管道表函数的返回值类型暂时只支持 VARRAY 类型和嵌套表类型。

PIPE ROW 语句只能在管道表函数中使用，其语法为：PIPE ROW ( <值表达式> );

实例

create table test_res (id int, name varchar);
INSERT INTO test_res values(1,'a'),(2,'b');

DROP TYPE IF EXISTS type_test_res CASCADE;
DROP TYPE IF EXISTS  test_res_list CASCADE;
CREATE TYPE type_test_res AS object(id test_res.id%TYPE, name test_res.name%type);
CREATE TYPE test_res_list AS TABLE OF type_test_res;

CREATE OR REPLACE FUNCTION func_test_res()
RETURN test_res_list PIPELINED
IS	
BEGIN
	FOR rec IN (select * FROM test_res)  -- 此处必须要有括号
	-- 或者 FOR rec IN (select id,name FROM test_res)  -- 此处必须要有括号
	loop
		PIPE ROW (type_test_res(rec.id, rec.name));
	END loop;
EXCEPTION
	WHEN OTHERS THEN NULL;
END;

SELECT * FROM TABLE(func_test_res);


*  结果:
SQL> SELECT * FROM TABLE(func_test_res);

行号     ID          NAME
---------- ----------- ----
1          1           a
2          2           b

实例2: 多次返回集合


CREATE TABLE test_res2 AS SELECT * FROM test_res;
TRUNCATE TABLE test_res2;
INSERT INTO test_res2 values(1,'张三');
INSERT INTO test_res2 values(2,'李四');
SELECT * FROM test_res2;

CREATE OR REPLACE FUNCTION func_test_res2()
RETURN test_res_list PIPELINED
IS	
BEGIN
	FOR rec IN (select ID, NAME  FROM test_res)  -- 此处必须要有括号
	loop
		PIPE ROW (type_test_res(rec.id, rec.name));
	END loop;

	FOR rec2 IN (select ID, NAME  FROM test_res2)  -- 此处必须要有括号
	loop
		PIPE ROW (type_test_res(rec2.id, rec2.name));
	END loop;
EXCEPTION
	WHEN OTHERS THEN NULL;
END;

SELECT * FROM TABLE(func_test_res2);

结果:
SQL> SELECT * FROM TABLE(func_test_res2);

行号     ID          NAME  
---------- ----------- ------
1          1           a
2          2           b
3          1           张三
4          2           李四

实例3

# 实例1 
create or replace type pipelined_tms_info as table of tms_info;
/

CREATE or replace FUNCTION func_get_ipsan_and_tms_info(dev_code varchar) 
RETURN pipelined_tms_info PIPELINED
AS 
	sqlstr VARCHAR;
	cur_test CURSOR; -- 定义游标
	v_store_res_id BIGINT;
	v_store_res_name VARCHAR(64);
	v_dev_code VARCHAR(48);
	v_storage_type INT;
	v_res_usage INT;
	v_full_policy INT;
	v_res_status INT;
	v_total_capacity bigint;
	v_spare_capacity bigint;
	v_mount_point character varying(256);
	v_res_is_new INT;
begin
	sqlstr = 'SELECT store_res_id,
					store_res_name,
					dev_code,
					storage_type,
					res_usage,
					full_policy,
					res_status,
					total_capacity,
					spare_capacity,
					mount_point,
					res_is_new 
			FROM tbl_storage_res INNER JOIN tbl_str_res_mount USING (store_res_id) WHERE dev_code = :1
			union
			select 0 as store_res_id,
					a.res_name, 
					'''' as dev_code, 
					8 as res_type, 
					c.cds_purpose, 
					0 as full_policy, 
					b.dev_status, 
					c.total_capacity, 
					c.spare_capacity, 
					'''' as mount_point, 
					0 as res_is_new 
			from tbl_storage_tg_cds_info a 
			inner join tbl_device b on (b.dev_code = a.cds_code)
			inner join tbl_cds c on (a.cds_code = c.cds_code) ;';
			
	-- print sqlstr;
	
	open cur_test for sqlstr using dev_code;  --打开游标
	loop
		fetch cur_test into 				  --通过游标获取当前记录
				v_store_res_id,
				v_store_res_name,
				v_dev_code,
				v_storage_type,
				v_res_usage,
				v_full_policy,
				v_res_status,
				v_total_capacity,
				v_spare_capacity,
				v_mount_point,
				v_res_is_new;
		exit when cur_test%notfound;
		PIPE ROW (tms_info(
				v_store_res_id,
				v_store_res_name,
				v_dev_code,
				v_storage_type,
				v_res_usage,
				v_full_policy,
				v_res_status,
				v_total_capacity,
				v_spare_capacity,
				v_mount_point,
				v_res_is_new)
			);
	end loop;
	
	close cur_test; -- 关闭游标
END;
/
-- 调用实例:  select * from table(func_get_ipsan_and_tms_info('he') );



# 实例2 
CREATE or replace FUNCTION func_test2(v_dev_code varchar) 
RETURN pipelined_tms_info PIPELINED
AS 

begin

	-- print sqlstr;
	for rec in (SELECT store_res_id,
					store_res_name,
					dev_code,
					storage_type,
					res_usage,
					full_policy,
					res_status,
					total_capacity,
					spare_capacity,
					mount_point,
					res_is_new 
			FROM tbl_storage_res INNER JOIN tbl_str_res_mount USING (store_res_id) WHERE dev_code = v_dev_code
			union
			select 0 as store_res_id,
					a.res_name, 
					'' as dev_code, 
					8 as res_type, 
					c.cds_purpose, 
					0 as full_policy, 
					b.dev_status, 
					c.total_capacity, 
					c.spare_capacity, 
					'' as mount_point, 
					0 as res_is_new 
			from tbl_storage_tg_cds_info a 
			inner join tbl_device b on (b.dev_code = a.cds_code)
			inner join tbl_cds c on (a.cds_code = c.cds_code) )
	LOOP
		PIPE ROW (tms_info(
					rec.store_res_id,
					rec.store_res_name,
					rec.dev_code,
					rec.storage_type,
					rec.res_usage,
					rec.full_policy,
					rec.res_status,
					rec.total_capacity,
					rec.spare_capacity,
					rec.mount_point,
					rec.res_is_new)
				);
			
	end loop;
	
END;
select * from table(func_test2('he'));

# 实例3 
drop table if exists test cascade;
create table test(id int, name varchar);
insert into test values(1,'a'),(2,'b'),(3,'c');
select * from test;
create or replace type type_test as object(id int, name varchar);
/
create or replace type pipelined_type_test as table of type_test;
/

-- 字段值是变量, 可以使用 显示游标 或者 隐式游标
CREATE OR REPLACE FUNCTION func_test() 
RETURN pipelined_type_test pipelined
AS	
	cur cursor;
	v_id int:=1;
	v_name varchar;
BEGIN
	open cur for 'select * from test where id>'||v_id;
	loop
		fetch cur into v_id, v_name;
		exit when cur%notfound;
		pipe row(type_test(v_id,v_name));
	end loop;
END;
/
select * from table(func_test());


CREATE OR REPLACE FUNCTION func_test2() 
RETURN pipelined_type_test pipelined
AS	
	cur cursor;
	v_id int:=1;
	v_name varchar;
BEGIN
	open cur for select * from test where id>v_id;
	loop
		fetch cur into v_id, v_name;
		exit when cur%notfound;
		pipe row(type_test(v_id,v_name));
	end loop;
END;
/
select * from table(func_test2());


-- 字段名称或者表名称是变量, 只能使用显示游标, 并且字段名称与表名称需要通过 || 拼接起来
CREATE OR REPLACE FUNCTION func_test3() 
RETURN pipelined_type_test pipelined
AS	
	cur cursor;
	v_id int;
	v_name varchar;
	col_name varchar:='id';
	sql_cmd varchar:='select * from test where '|| col_name || '> 1 ';
BEGIN
	print sql_cmd;
	open cur for sql_cmd ;
	loop
		fetch cur into v_id, v_name;
		exit when cur%notfound;
		pipe row(type_test(v_id,v_name));
	end loop;
END;
/
select * from table(func_test3());


CREATE OR REPLACE FUNCTION func_test4() 
RETURN pipelined_type_test pipelined
AS	
	cur cursor;
	v_id int;
	v_name varchar;
	col_name varchar:='id';
	sql_cmd varchar:='select * from test where '|| col_name || '> 1 ';
	rec type_test;
BEGIN
	print sql_cmd;
	open cur for sql_cmd ;
	loop
		fetch cur into v_id, v_name;
		rec.id=v_id;
		rec.name=v_name;
		exit when cur%notfound;
		pipe row(type_test(rec.id, rec.name));-- 报错
		--pipe row(type_test(rec)); -- 也报错
	end loop;
END;
/

select * from table(func_test4());
[-7087]:Uninitialized object
-7087: func_test4 line 15 .


-- 示例
CREATE TYPE type_phy_res_code AS OBJECT (phy_res_code VARCHAR (48));
CREATE TYPE pipelined_phy_res_code AS TABLE OF type_phy_res_code;
CREATE OR REPLACE FUNCTION func_bank_getalldefs(usercode VARCHAR (48))
RETURN pipelined_phy_res_code PIPELINED
    AS 
declare
	phy_res_code VARCHAR (48);
	cur cursor;
BEGIN

   FOR dataRec IN
		(SELECT phy_res_code,res_level FROM tbl_res where phy_res_code IN 
		(
			SELECT alarm_source_code FROM tbl_bank_alarmsource_sub WHERE alarm_sub_id IN 
			(
				 SELECT alarm_sub_id FROM tbl_bank_alarm_subscribe WHERE alarm_sub_usercode IN
				 (
						SELECT CAST(group_id AS varchar) FROM tbl_bank_group_member WHERE member_code = usercode
				 )
			 )
			 GROUP BY alarm_source_code
		)
		union 

		SELECT phy_res_code,res_level FROM tbl_res WHERE phy_res_code IN
		(
			 SELECT alarm_source_code FROM tbl_bank_alarmsource_sub WHERE alarm_sub_id = 
			 (
				 SELECT alarm_sub_id FROM tbl_bank_alarm_subscribe WHERE alarm_sub_usercode = usercode
			  ) 
			  GROUP BY alarm_source_code
		)
		)
	
LOOP
  open  cur for 'SELECT A.phy_res_code FROM tbl_res A,(SELECT res_id FROM tbl_res WHERE phy_res_code = '''||dataRec.phy_res_code ||''' AND res_type_id = 1 ) AS B WHERE A.res_tree_level'||dataRec.res_level||'_id = B.res_id ' ;
  LOOP
  	fetch cur into phy_res_code;
    exit when cur%notfound;
    pipe row (type_phy_res_code(phy_res_code));
  END LOOP;
  close  cur;
END LOOP;
return;
END;

触发器

定义

触发器(TRIGGER)定义为当某些与数据库有关的事件发生时，数据库应该采取的操作

与存储过程区别

触发器与存储模块类似，都是在服务器上保存并执行的一段 DMSQL 程序语
句。不同的是：存储模块必须被显式地调用执行，而触发器是在相关的事件发生时由服务器
自动地隐式地激发

分类

触发器分为表触发器、事件触发器和时间触发器。表触发器是对表里数据操作引发的数
据库的触发；事件触发器是对数据库对象操作引起的数据库的触发；时间触发器是一种特殊
的事件触发器

个数限制

在一张基表上允许创建的表触发器的个数没有限制，
一共允许有 12 种类型。它们
分别是：BEFORE INSERT 行级、BEFORE INSERT 语句级、AFTER INSERT 行级、AFTER
INSERT 语句级、BEFORE UPDATE 行级、BEFORE UPDATE 语句级、AFTER UPDATE 行
级、AFTER UPDATE 语句级、BEFORE DELETE 行级、BEFORE DELETE 语句级、AFTER
DELETE 行级和 AFTER DELETE 语句级；

触发器执行顺序

1) 如果有语句级前触发器的话，先运行该触发器；
2) 对于受语句影响每一行：
a) 如果有行级前触发器的话，运行该触发器；
b) 执行该语句本身；
c) 如果有行级后触发器的话，运行该触发器。
3) 如果有语句级后触发器的话，运行该触发器。

实例

set serveroutput on ;
CREATE OR REPLACE FUNCTION func_test1() 
RETURN INT 
AS	

BEGIN
	print 'func_test1';
	return 0;
END;
/

CREATE OR REPLACE FUNCTION func_test2() 
RETURN INT 
AS	

BEGIN
	print 'func_test2';
	return 0;
END;
/

CREATE OR REPLACE FUNCTION func_test3() 
RETURN INT 
AS	

BEGIN
	print 'func_test3';
	return 0;
END;
/

CREATE OR REPLACE FUNCTION func_test4() 
RETURN INT 
AS	

BEGIN
	print 'func_test4';
	return 0;
END;
/

drop table if exists test cascade;
create table test (id int, name varchar);
insert into test values(1, 'a');
insert into test values(2, 'a');
insert into test values(3, 'c');

CREATE OR REPLACE TRIGGER trig_before_insert_stat_test
BEFORE update ON test
for each STATEMENT
BEGIN
	call func_test1();
END;

/


CREATE OR REPLACE TRIGGER trig_before_insert_row_test
BEFORE update ON test
FOR EACH ROW
BEGIN
	call func_test2();
END;

/


CREATE OR REPLACE TRIGGER trig_after_insert_row_test
BEFORE update ON test
FOR EACH ROW
BEGIN
	call func_test3();
END;

/


CREATE OR REPLACE TRIGGER trig_after_insert_stat_test
after update ON test
for each STATEMENT
BEGIN
	call func_test4();
END;

/

select * from test;

update test set name='test' where name='a';


结果:
func_test1
func_test2
func_test3
func_test2
func_test3
func_test4

instead of 触发器

INSTEAD OF 触发器仅允许建立在视图上，并且只支持行级触发

限制

表级触发器不支持跨模式，即<触发器名>必须和<触发表名>、<触发视图名>的模
式名一致；
水平分区子表、HUGE 表不支持表级触发器；

实例

普通触发器

-- 触发器实例
set serveroutput on ;
drop table if exists test cascade;
create table test(id int, name varchar);


create or replace trigger trig_insert_test after insert on test
for each row 
begin
	print 'insert row is '||:NEW.id;
end;
/

insert into test values(1,'a');


CREATE OR REPLACE FUNCTION func_test() 
RETURN INT 
AS	

BEGIN
	print('hello');
	return 0;

END;
/

create or replace trigger trig_insert_test after insert on test
for each row 
begin
	call func_test();
end;

/


CREATE OR REPLACE FUNCTION func_test2() 
RETURN INT 
AS	

BEGIN
	print('hello2');
	return 0;

END;
/


create or replace trigger trig_insert_test2 after insert on test
for each row 
begin
	call func_test2();
end;
/


SQL> insert into test values(1,'a');
hello
hello2

影响行数 1

已用时间: 1.936(毫秒). 执行号:1853112
触发时机: 哪个触发器先创建, 哪个触发器就先执行

instead of 触发器

create table t1(a int,b int);
insert into t1 values(10,10);
insert into t1 values(11,11);
create view v1 as select * from t1;

CREATE OR REPLACE TRIGGER tri1
INSTEAD OF UPDATE ON v1
BEGIN
insert into t1 values(111,111); ----替换动作
END;
/

update v1 set a=100 where a=10;


SQL> select * from t1;  

行号     A           B          
---------- ----------- -----------
1          10          10
2          11          11
3          111         111

INSTEAD OF 触发器仅允许建立在视图上，并且只支持行级触发

由上面的查询结果可以看出。更新操作并没有成功，而是被触发器中的替换动作替换了。
这就是 INSTEAD OF 的妙用之处

新、旧行值的引用

:OLD 表示记录被处理前的值，
:NEW 表示记录被处理后的

触发器谓词

INSERTING: 当触发语句为 INSERT 时为真，否则为假
DELETING: 当触发语句为 DELETE 时为真，否则为假
UPDATING:未指定列名时，当触发语句为 UPDATE 时为真，否则为假；指定某
一列名时，当触发语句为对该列的 UPDATE 时为真，否则为假

实例

CREATE OR REPLACE TRIGGER LogChanges
AFTER INSERT OR DELETE OR UPDATE ON OTHER.READER
FOR EACH ROW
DECLARE
	v_ChangeType CHAR(1);
BEGIN
/* ‗I‘表示 INSERT 操作，‘D‘表示 DELETE 操作，‘U‘表示 UPDATE 操作 */
IF INSERTING THEN
v_ChangeType := 'I';
ELSIF UPDATING THEN
v_ChangeType := 'U';
ELSE
v_ChangeType := 'D';
END IF;
/* 记录对 Reader 做的所有修改到表 ReaderAudit 中，包括修改人和修改时间 */
INSERT INTO OTHER.READERAUDIT
VALUES
(v_ChangeType, USER, SYSDATE,
:old.reader_id, :old.name, :old.age, :old.gender, :old.major,
:new.reader_id, :new.name, :new.age, :new.gender, :new.major);
END;

/

达梦分表

创建普通分区表

DROP TABLE IF EXISTS test01 CASCADE;
 create table test01( 
	id int, 
	insert_date date
 )
 partition by range(insert_date)(
	 partition p0 values less than ('2021-01-01'),
	 partition p1 values less than ('2021-02-01'),
	 partition p2 values less than ('2021-03-01'),
	 partition p3 values equ or less than ('2021-04-01'),
	 partition p_max values equ or less than (maxvalue) 
 );


* 查看主表对应的所有分区表
SELECT t.TABLE_OWNER, t.TABLE_NAME, t.HIGH_VALUE, t.PARTITION_NAME FROM dba_tab_partitions t  WHERE TABLE_NAME=upper('test01') ORDER BY t.PARTITION_NAME

* 查看主表的分表类型以及分表个数
select owner,table_name,partitioning_type ,partition_count from all_part_tables t;

创建自动分区表

按照天分表

-- 按照天自动分表
DROP TABLE IF EXISTS test_part_interval_day CASCADE;
create table test_part_interval_day
(
   ID NUMBER(20) not null,
   REMARK VARCHAR,
   create_time timestamp
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL(1, 'day'))
(partition partition_default values less than(to_date('2020-01-01', 'yyyy-mm-dd')));
INSERT INTO test_part_interval_day values(1,'100',timestamp '2009-01-02');
INSERT INTO test_part_interval_day values(1,'100',now());
INSERT INTO test_part_interval_day values(1,'100', now()+INTERVAL '1' day);

SELECT t.TABLE_OWNER, t.TABLE_NAME, t.HIGH_VALUE, t.PARTITION_NAME FROM dba_tab_partitions t  WHERE TABLE_NAME=upper('test_part_interval_day') ORDER BY t.PARTITION_NAME;
select owner,table_name,partitioning_type ,partition_count from all_part_tables t;

SELECT * FROM test_part_interval_day ;
SELECT * FROM test_part_interval_day PARTITION (PARTITION_DEFAULT);
SELECT * FROM test_part_interval_day PARTITION (SYS_P7592_7594);
SELECT * FROM test_part_interval_day PARTITION (SYS_P7592_7596);

ALTER TABLE test_part_interval_day TRUNCATE PARTITION PARTITION_default;

alter table test_part_interval_day enable row movement;

按照周分表


create table test_part_interval_week
(
   ID NUMBER(20) not null,
   REMARK VARCHAR2(1000),
   create_time timestamp
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL (7, 'day'))
(partition part_t01 values less than(to_date('2018-11-12', 'yyyy-mm-dd')));
alter table test_part_interval_week add constraint test_part_pk primary key (ID) ;

按照月分表


DROP TABLE IF EXISTS test_part_interval_week CASCADE;
create table test_part_interval_week
(
   ID NUMBER(20) not null,
   REMARK VARCHAR2(1000),
   create_time timestamp
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL (7, 'day'))
(partition part_t01 values less than(to_date('2018-11-12', 'yyyy-mm-dd')));
alter table test_part_interval_week add constraint test_part_pk primary key (ID)

按照年分表



create table test_part_interval_year
(
   ID NUMBER(20) not null,
   REMARK VARCHAR2(1000),
   create_time timestamp
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'year'))
(partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));

alter table test_part_interval_year add constraint test_part_interval_year_pk primary key (ID);

create index idx_test_part_interval_year_create_time on test_part_interval_year (create_time);

单表改为为分表

-- 1. 重命名
alter table test_part rename to test_part_temp;

-- 2. 创建 partition table
create table test_part
(
   ID NUMBER(20) not null,
   REMARK VARCHAR2(1000),
   create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
(partition part_t1 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));


-- 3. 创建主键
alter table test_part add constraint test_part_pk_1 primary key (ID) using INDEX;

-- 4. 将 test_part_temp 表里的数据迁移到 test_part 表中
insert into test_part select * from test_part_temp ;

-- 5. 为分区表设置索引
-- Create/Recreate indexes 
create index test_part_create_time_1 on TEST_PART (create_time); 

-- 6. 删除老的 test_part_temp 表
drop table test_part_temp purge;

-- 7. 作用是：允许分区表的分区键是可更新。
-- 当某一行更新时，如果更新的是分区列，并且更新后的列植不属于原来的这个分区，
-- 如果开启了这个选项，就会把这行从这个分区中 delete 掉，并加到更新后所属的分区，此时就会发生 rowid 的改变。
-- 相当于一个隐式的 delete + insert ，但是不会触发 insert/delete 触发器。
alter table test_part enable row movement;

达梦中使用 dblink

简介

数据库链接对象 (LINK) 是 DM 中的一种特殊的数据库实体对象，它记录了远程数据库的连接和路径信息，用于建立与远程数据的联系。

创建 DM 到 DM 的数据库链接，可以使用数据库链接对远程库做增、删、改、查操作。

实例1: (同构数据库)DM–> DM

两台服务器，其中一个为目的主机 A，另一个为测试机 B；

分别在这两台服务器上进入数据库安装目录下的库目录里修改

dm.ini 文件：MAL_INI=1，

实例名 INSTANCE_NAME 要对应，

配置 dmmal.ini（如无此文件，新建此文件）如下所示：

[mal_inst1]
mal_inst_name  = IMOS_INSTANCE    --A 的实例名 (可以通过  select name from v$instance; 查询得到)     
mal_host       = 207.207.35.101   --A 的ip 
mal_port       = 5281			  --A 上面设置的端口, 这个端口不是DM服务端口, 不能设置为 5236/5237 这种达梦服务端口
[mal_inst2]
mal_inst_name  = DMSERVER         --B 的实例名   
mal_host       = 207.207.35.101   --B 的ip  
mal_port       = 5282			  --B 上面设置的端口, 这个端口不是DM服务端口, 不能设置为 5236/5237 这种达梦服务端口

注意: A 和 B 的实例名不能一样；–中文注释部分只是方便解释含义，配置 dmmal.ini 时不必写，以免造成格式问题导致 dmserver 服务无法启动。
两台主机的 dmmal.ini 文件相同。配置成功之后分别重启 dmserver DM 数据库服务。

在 A 库中创建表 test

CREATE TABLE TEST(C1 INT,C2 VARCHAR(20));

在 B 上建立到 A 的数据库链接 LINK01，使用链接进行插入、更新和删除操作。如下所示：

CREATE PUBLIC LINK link_imos CONNECT WITH test IDENTIFIED BY test USING '207.207.35.101/5281';

INSERT INTO TEST@link_imos VALUES(1,'A');
INSERT INTO TEST@link_imos VALUES(2,'B');
UPDATE TEST@link_imos SET C2='C' WHERE C1=1;
DELETE FROM TEST@link_imos WHERE C1=2;

COMMIT;

-- 删除dblink
DROP LINK link_imos;

实际操作演示

SQL> CREATE PUBLIC LINK link_imos CONNECT WITH test IDENTIFIED BY test USING '207.207.35.101/5281';
操作已执行
已用时间: 64.169(毫秒). 执行号:405.
SQL> 
SQL> INSERT INTO TEST@link_imos VALUES(1,'A');
INSERT INTO TEST@link_imos VALUES(2,'B');影响行数 1

已用时间: 7.631(毫秒). 执行号:406.
SQL> 
影响行数 1

已用时间: 1.811(毫秒). 执行号:407.
SQL> 
SQL> INSERT INTO TEST@link_imos VALUES(2,'B');
影响行数 1

已用时间: 1.789(毫秒). 执行号:408.
SQL> select * from test@link_imos;

行号     C1          C2
---------- ----------- --
1          1           A
2          2           B
3          2           B

已用时间: 2.458(毫秒). 执行号:409.
SQL> 
SQL> UPDATE TEST@link_imos SET C2='C' WHERE C1=1;
影响行数 1

已用时间: 3.587(毫秒). 执行号:410.
SQL> select * from test@link_imos;

行号     C1          C2
---------- ----------- --
1          1           C
2          2           B
3          2           B

已用时间: 2.150(毫秒). 执行号:411.
SQL> 
SQL> DELETE FROM TEST@link_imos WHERE C1=2;
影响行数 2

已用时间: 0.794(毫秒). 执行号:412.
SQL> select * from test@link_imos;

行号     C1          C2
---------- ----------- --
1          1           C

已用时间: 2.153(毫秒). 执行号:413.
SQL> commit;
操作已执行

实例2 (异构数据库) DM–> Oracle

//DM 到 Oracle 的数据库连接创建
CREATE LINK LINK1 CONNECT 'ORACLE' WITH USER01 IDENTIFIED BY USER01PASSWD USING '127.0.0.1/orcl';
DM 到 Oracle 的数据库连接使用方法同 DM 到 DM 数据库连接。

注意

数据库连接目前只支持 DM、Oracle 或 ODBC。
DM-DM 的同构数据库链接不支持 MPP 环境，DM 与异构数据库的数据库链接支持 MPP 环境。
增删改不支持 INTO 语句。
不支持使用游标进行增删改操作。
不支持操作远程表的复合类型列。
DBLINK 理论上不支持 LOB 类型列的操作，但支持简单的增删改语句中使用常量来对 LOB 类型列进行操作。

事务相关

外部函数

开启 dmap

以 dmdba 身份执行

echo 'EXTERNAL_AP_PORT = 4237' >>  /dmdata/imos/dm.ini 
echo 'AP_PORT=4237' > /dmdata/imos/dmap.ini   # 与上面 dm.ini中的配置值要保持一致
/dm/bin/dmap dmap_ini=/dmdata/imos/dmap.ini  &

开启外部函数开关

disql中执行

select SF_GET_PARA_VALUE(1, 'ENABLE_EXTERNAL_CALL');

SP_SET_PARA_VALUE(2,'ENABLE_EXTERNAL_CALL',1);

重启 DM服务与 dmap 服务

DmSerivceimos restart
pkill dmap 
/dm/bin/dmap dmap_ini=/dmdata/imos/dmap.ini &

测试外部函数功能

以dmdba用户身份

cd /dm/bin

vim test.c
	#include <string.h>
	#include "stdlib.h"
	char* C_CONCAT (char* str1, char* str2)
	{
		char* str3;
		int len1;
		int len2;
		len1 = strlen(str1);
		len2 = strlen(str2);
		str3 = (char*)malloc(len1 + len2 + 1); //要多一个字节作为结尾 0
		memcpy(str3, str1, len1);
		memcpy(str3 + len1, str2, len2);
		str3[len1 + len2] = 0; //必须有结尾 0
		return str3;
	}
	
rm -f /dm/bin/libtest.so
gcc -o libtest.so -fPIC -shared test.c -I /dm/include

disql test/test:5237 
CREATE OR REPLACE FUNCTION   MY_CONCAT(A VARCHAR, B VARCHAR)
   RETURN VARCHAR   
   EXTERNAL '/dm/bin/libtest.so'   C_CONCAT USING CS;
/

SELECT MY_ONCAT('HELLO->','WORLD');

重要,注意事项:

USING 子句指明函数的类型，
如果 C 函数参数为结构体，使用 C
如果 C 函数参数为标量(比如字符串类型)，则使用 CS

中文转换为拼音

CREATE OR REPLACE FUNCTION cn2pinyin_jianpin(P_NAME IN VARCHAR) RETURN VARCHAR AS
     V_COMPARE VARCHAR(100);
     V_RETURN VARCHAR(4000):='';
 
     FUNCTION F_NLSSORT(P_WORD IN VARCHAR) RETURN VARCHAR AS
     BEGIN
      RETURN NLSSORT(P_WORD, 'NLS_SORT=SCHINESE_PINYIN_M');
     END;
    BEGIN
 
    FOR I IN 1..NVL(LENGTH(P_NAME), 0) LOOP
     V_COMPARE := F_NLSSORT(SUBSTR(P_NAME, I, 1));
     IF V_COMPARE >= F_NLSSORT('吖') AND V_COMPARE <= F_NLSSORT('驁') THEN
      V_RETURN := V_RETURN || 'A';
     ELSIF V_COMPARE >= F_NLSSORT('八') AND V_COMPARE <= F_NLSSORT('簿') THEN
      V_RETURN := V_RETURN || 'B';
     ELSIF V_COMPARE >= F_NLSSORT('嚓') AND V_COMPARE <= F_NLSSORT('錯') THEN
      V_RETURN := V_RETURN || 'C';
     ELSIF V_COMPARE >= F_NLSSORT('咑') AND V_COMPARE <= F_NLSSORT('鵽') THEN
      V_RETURN := V_RETURN || 'D';
     ELSIF V_COMPARE >= F_NLSSORT('妸') AND V_COMPARE <= F_NLSSORT('樲') THEN
      V_RETURN := V_RETURN || 'E';
     ELSIF V_COMPARE >= F_NLSSORT('发') AND V_COMPARE <= F_NLSSORT('猤') THEN
      V_RETURN := V_RETURN || 'F';
     ELSIF V_COMPARE >= F_NLSSORT('旮') AND V_COMPARE <= F_NLSSORT('腂') THEN
      V_RETURN := V_RETURN || 'G';
     ELSIF V_COMPARE >= F_NLSSORT('妎') AND V_COMPARE <= F_NLSSORT('夻') THEN
      V_RETURN := V_RETURN || 'H';
     ELSIF V_COMPARE >= F_NLSSORT('丌') AND V_COMPARE <= F_NLSSORT('攈') THEN
      V_RETURN := V_RETURN || 'J';
     ELSIF V_COMPARE >= F_NLSSORT('咔') AND V_COMPARE <= F_NLSSORT('穒') THEN
      V_RETURN := V_RETURN || 'K';
     ELSIF V_COMPARE >= F_NLSSORT('垃') AND V_COMPARE <= F_NLSSORT('擽') THEN
      V_RETURN := V_RETURN || 'L';
     ELSIF V_COMPARE >= F_NLSSORT('嘸') AND V_COMPARE <= F_NLSSORT('椧') THEN
      V_RETURN := V_RETURN || 'M';
     ELSIF V_COMPARE >= F_NLSSORT('拏') AND V_COMPARE <= F_NLSSORT('瘧') THEN
      V_RETURN := V_RETURN || 'N';
     ELSIF V_COMPARE >= F_NLSSORT('筽') AND V_COMPARE <= F_NLSSORT('漚') THEN
      V_RETURN := V_RETURN || 'O';
     ELSIF V_COMPARE >= F_NLSSORT('妑') AND V_COMPARE <= F_NLSSORT('曝') THEN
      V_RETURN := V_RETURN || 'P';
     ELSIF V_COMPARE >= F_NLSSORT('七') AND V_COMPARE <= F_NLSSORT('裠') THEN
      V_RETURN := V_RETURN || 'Q';
     ELSIF V_COMPARE >= F_NLSSORT('亽') AND V_COMPARE <= F_NLSSORT('鶸') THEN
      V_RETURN := V_RETURN || 'R';
     ELSIF V_COMPARE >= F_NLSSORT('仨') AND V_COMPARE <= F_NLSSORT('蜶') THEN
      V_RETURN := V_RETURN || 'S';
     ELSIF V_COMPARE >= F_NLSSORT('侤') AND V_COMPARE <= F_NLSSORT('籜') THEN
      V_RETURN := V_RETURN || 'T';
     ELSIF V_COMPARE >= F_NLSSORT('屲') AND V_COMPARE <= F_NLSSORT('鶩') THEN
      V_RETURN := V_RETURN || 'W';
     ELSIF V_COMPARE >= F_NLSSORT('夕') AND V_COMPARE <= F_NLSSORT('鑂') THEN
      V_RETURN := V_RETURN || 'X';
     ELSIF V_COMPARE >= F_NLSSORT('丫') AND V_COMPARE <= F_NLSSORT('韻') THEN
      V_RETURN := V_RETURN || 'Y';
     ELSIF V_COMPARE >= F_NLSSORT('帀') AND V_COMPARE <= F_NLSSORT('咗') THEN
      V_RETURN := V_RETURN || 'Z';
     END IF;
    END LOOP;
    RETURN lower(V_RETURN);
   END;
/
查询: SELECT cn2pinyin_jianpin('行人');
结果: xr

PG中ltree的用法

什么是ltree？

Ltree是PostgreSQL模块。它实现了一种数据类型ltree，用于表示存储在分层树状结构中的数据的标签。提供了用于搜索标签树的广泛工具。

为什么选择ltree？

ltree实现了一个物化路径，对于INSERT / UPDATE / DELETE来说非常快，而对于SELECT操作则较快
通常，它比使用经常需要重新计算分支的递归CTE或递归函数要快
如内置的查询语法和专门用于查询和导航树的运算符
索引！！！

实例

CREATE EXTENSION ltree;

CREATE TABLE comments (user_id integer, description text, path ltree);
INSERT INTO comments (user_id, description, path) VALUES ( 1, md5(random()::text), '0001');
INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0001.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0001.0001.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 1, md5(random()::text), '0001.0001.0001.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 5, md5(random()::text), '0001.0001.0001.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0002.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 8, md5(random()::text), '0001.0003.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 11, md5(random()::text), '0001.0003.0002.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0003.0002.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 5, md5(random()::text), '0001.0003.0002.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 7, md5(random()::text), '0001.0003.0002.0002.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 20, md5(random()::text), '0001.0003.0002.0002.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 31, md5(random()::text), '0001.0003.0002.0002.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 22, md5(random()::text), '0001.0003.0002.0002.0004');
INSERT INTO comments (user_id, description, path) VALUES ( 34, md5(random()::text), '0001.0003.0002.0002.0005');
INSERT INTO comments (user_id, description, path) VALUES ( 22, md5(random()::text), '0001.0003.0002.0002.0006');


CREATE INDEX path_gist_comments_idx ON comments USING GIST(path);
CREATE INDEX path_comments_idx ON comments USING btree(path);


* 在commenets表中找到path以‘0001.0003’开头的记录
test=#  SELECT user_id, path FROM comments WHERE path <@ '0001.0003';
 user_id |           path           
---------+--------------------------
       6 | 0001.0003
       8 | 0001.0003.0001
       9 | 0001.0003.0002
      11 | 0001.0003.0002.0001
       2 | 0001.0003.0002.0002
       5 | 0001.0003.0002.0003
       7 | 0001.0003.0002.0002.0001
      20 | 0001.0003.0002.0002.0002
      31 | 0001.0003.0002.0002.0003
      22 | 0001.0003.0002.0002.0004
      34 | 0001.0003.0002.0002.0005
      22 | 0001.0003.0002.0002.0006
(12 rows)

查看执行计划

test=# explain (timing, verbose, analyse, buffers, costs,timing)  SELECT user_id, path FROM comments WHERE path <@ '0001.0003';
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Seq Scan on public.comments  (cost=0.00..1.24 rows=1 width=36) (actual time=0.008..0.010 rows=12 loops=1)
   Output: user_id, path
   Filter: (comments.path <@ '0001.0003'::ltree)
   Rows Removed by Filter: 7
   Buffers: shared hit=1
 Planning Time: 0.030 ms
 Execution Time: 0.019 ms
(7 rows)

test=# set enable_seqscan to off;
SET
test=# explain (timing, verbose, analyse, buffers, costs,timing)  SELECT user_id, path FROM comments WHERE path <@ '0001.0003';
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using path_gist_comments_idx on public.comments  (cost=0.13..8.15 rows=1 width=36) (actual time=0.024..0.026 rows=12 loops=1)
   Output: user_id, path
   Index Cond: (comments.path <@ '0001.0003'::ltree)
   Buffers: shared hit=2
 Planning Time: 0.047 ms
 Execution Time: 0.044 ms
(6 rows)


可以将select “path <@ ‘0001.0003’” 换种实现方法：
test=# SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*';
 user_id |           path           
---------+--------------------------
       6 | 0001.0003
       8 | 0001.0003.0001
       9 | 0001.0003.0002
      11 | 0001.0003.0002.0001
       2 | 0001.0003.0002.0002
       5 | 0001.0003.0002.0003
       7 | 0001.0003.0002.0002.0001
      20 | 0001.0003.0002.0002.0002
      31 | 0001.0003.0002.0002.0003
      22 | 0001.0003.0002.0002.0004
      34 | 0001.0003.0002.0002.0005
      22 | 0001.0003.0002.0002.0006
(12 rows)

* 你不应该忘记数据的顺序，如下的例子：
INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0001.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0001.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0001.0003');

test=# SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*';
 user_id |           path           
---------+--------------------------
       6 | 0001.0003
       8 | 0001.0003.0001
       9 | 0001.0003.0002
      11 | 0001.0003.0002.0001
       2 | 0001.0003.0002.0002
       5 | 0001.0003.0002.0003
       7 | 0001.0003.0002.0002.0001
      20 | 0001.0003.0002.0002.0002
      31 | 0001.0003.0002.0002.0003
      22 | 0001.0003.0002.0002.0004
      34 | 0001.0003.0002.0002.0005
      22 | 0001.0003.0002.0002.0006
       9 | 0001.0003.0001.0001
       9 | 0001.0003.0001.0002
       9 | 0001.0003.0001.0003
(15 rows)

现在排序
 SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*' ORDER by path;
 
 test=#  SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*' ORDER by path;
 user_id |           path           
---------+--------------------------
       6 | 0001.0003
       8 | 0001.0003.0001
       9 | 0001.0003.0001.0001
       9 | 0001.0003.0001.0002
       9 | 0001.0003.0001.0003
       9 | 0001.0003.0002
      11 | 0001.0003.0002.0001
       2 | 0001.0003.0002.0002
       7 | 0001.0003.0002.0002.0001
      20 | 0001.0003.0002.0002.0002
      31 | 0001.0003.0002.0002.0003
      22 | 0001.0003.0002.0002.0004
      34 | 0001.0003.0002.0002.0005
      22 | 0001.0003.0002.0002.0006
       5 | 0001.0003.0002.0003
(15 rows)

可以在lquery的非星号标签的末尾添加几个修饰符，以使其比完全匹配更匹配：

“ @”-不区分大小写匹配，例如a @匹配A

“ *”-匹配任何带有该前缀的标签，例如foo *匹配foobar

“％”-匹配以下划线开头的单词

* 来为parent ‘0001.0003’找到所有直接的childrens

test=# SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*{1}' ORDER by path;
 user_id |      path      
---------+----------------
       8 | 0001.0003.0001
       9 | 0001.0003.0002
(2 rows)

* 为parent ‘0001.0003’找到所有的childrens
test=#  SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*' ORDER by path;
 user_id |           path           
---------+--------------------------
       6 | 0001.0003
       8 | 0001.0003.0001
       9 | 0001.0003.0001.0001
       9 | 0001.0003.0001.0002
       9 | 0001.0003.0001.0003
       9 | 0001.0003.0002
      11 | 0001.0003.0002.0001
       2 | 0001.0003.0002.0002
       7 | 0001.0003.0002.0002.0001
      20 | 0001.0003.0002.0002.0002
      31 | 0001.0003.0002.0002.0003
      22 | 0001.0003.0002.0002.0004
      34 | 0001.0003.0002.0002.0005
      22 | 0001.0003.0002.0002.0006
       5 | 0001.0003.0002.0003
(15 rows)

* 为children ‘0001.0003.0002.0002.0005’找到parent：
test=# SELECT user_id, path FROM comments WHERE path = subpath('0001.0003.0002.0002.0005', 0, -1) ORDER by path;
 user_id |        path         
---------+---------------------
       2 | 0001.0003.0002.0002
(1 row)

中文排序问题

PG中实现

create table test_chinese(name varchar);
insert into test_chinese values('中国'),('美国'),('日本');

test=# select * from test_chinese ;
 name 
------
 中国
 美国
 日本
(3 rows)

test=# select * from test_chinese  order by convert_to(name,'GBK');
 name 
------
 美国
 日本
 中国
(3 rows)

达梦中实现

DM8在对中文的排序，是默认按2进制编码来进行排序的. 达梦8增加了几种新的选择:

按中文拼音进行排序: SCHINESE_PINYIN_M
按中文部首进行排序: SCHINESE_RADICAL_M
按中文笔画进行排序: SCHINESE_STROKE_M

create table t (id int,c1 VARCHAR(100));

insert into t VALUES(1,'张三');
insert into t VALUES(2,'李四');
insert into t VALUES(6,'李六');
insert into t VALUES(3,'王五');
insert into t VALUES(4,'赵柳');

SELECT * FROM t ORDER BY NLSSORT(c1,'NLS_SORT = SCHINESE_PINYIN_M');  --按中文拼音进行排序


SELECT * FROM t ORDER BY NLSSORT(c1,'NLS_SORT = SCHINESE_RADICAL_M'); --按中文部首进行排序


SELECT * FROM t ORDER BY NLSSORT(c1,'NLS_SORT = SCHINESE_STROKE_M');  --按中文笔画进行排序

达梦存储过程返回值

drop table if exists test cascade;
create table test(id int, name varchar);
drop sequence seq_test_id;
create sequence seq_test_id ;

CREATE OR REPLACE procedure func_test(v_name varchar) 
AS
DECLARE 
    v_id int;
BEGIN
 v_id:=seq_test_id.nextval;
    insert into test values(v_id,v_name);
    select v_id;
END;
/
select * from test ;

call func_test('a');
select * from test ;


call func_test('b');
select * from test ;


SQL> select * from test ;
SQL>
call func_test('a');

LINEID     v_id
---------- -----------
1          1
SQL> select * from test ;

LINEID     id          name
---------- ----------- ----
1          1           a

SQL> call func_test('b');

LINEID     v_id
---------- -----------
1          2
SQL> select * from test ;

LINEID     id          name
---------- ----------- ----
1          1           a
2          2           b
SQL>

与PG存储过程中的差异

正常select 语句之后, 不能使用 if found或者if not found 来判断

select xx into xxx 如果没有结果, 则后面的SQL都不会执行

在函数或者存储过程以及自定义类型中都不能使用 domain

type的语法有区别

create  type type_person as (id int, name varchar);

create or replace type type_person as object(id int, name varchar);

select * into yy from xxx

drop table if exists test;
create table test(id int, name varchar);
insert into test values(1,'a'),(2,'b');
create or replace type type_person as object(id int, name varchar);
CREATE OR REPLACE FUNCTION func_test3() 
RETURN varchar 
AS	
	rec type_test;
BEGIN	
	select * into rec from test where id=1;
	return rec.name;
END;
/

会报错


CREATE OR REPLACE FUNCTION func_test4() 
RETURN varchar 
AS	
	TYPE type_test is record(id int, name varchar);
	rec type_test;
BEGIN	
	select * into rec from test where id=1;
	return rec.name;
END;
/
-- select func_test4();
-- 输出为 a

%rowtype 可以像PG那样使用

drop table if exists test;
create table test(id int, name varchar);
insert into test values(1,'a'),(2,'b');
CREATE OR REPLACE FUNCTION func_test2() 
RETURN varchar 
AS	
	rec test%rowtype;
BEGIN	
	select * into rec from test where id=1;

	return rec.name;
END;
/
-- select func_test2();
-- a

TYPE type_test is record(id test.id%type, test.name%type) 不能使用

CREATE OR REPLACE FUNCTION func_test5() 
RETURN varchar 
AS	
	TYPE type_test is record(id test.id%type, test.name%type);
	rec type_test;
BEGIN	
	select * into rec from test where id=1;
	return rec.name;
END;
/
会报错

函数调用方式

drop table if exists test cascade;
create table test(id int,name varchar);
insert into test values(1,'a');
insert into test values(2,'b');


CREATE OR REPLACE FUNCTION func_test() 
RETURN INT 
AS	

BEGIN
	return 0;
END;

select * from func_test(); -- 错误
select func_test();        -- 正确

create or replace type type_test as object(id int, name varchar);
/

create or replace type pipelined_type_test as table of type_test;
/

CREATE OR REPLACE FUNCTION func_test2() 
RETURN pipelined_type_test pipelined
AS	

BEGIN
	for rec in (select * from test) 
	loop
		pipe row (type_test(rec.id,rec.name));
	end loop;
	
END;
/
select * from func_test2(); -- 错误
select * from table(func_test2()); --正确

岳麓丹枫001

关注

2
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
达梦相关笔记

创建安装用户创建安装目录及授权关闭防火墙和Selinux关闭透明大页和numaLinux 6Linux7配置系统参数配置操作系统限制配置环境变量安装 DM8 数据库安装同时支持图形化安装，命令行安装，静默安装三种方式。由于我没有安装图形化界面，因此使用方式进行安装。用户应登录或切换到安装系统用户，进行以下安装步骤的操作(注：不建议使用root系统用户进行安装)。命令行安装 DM初始化命令行注册服务配置服务开机自启启停服务也可以通过
复制链接

扫一扫