达梦——常见问题及解决
rehoni / 2022-04-06
1、获取表名和字段名
select table_name from user_tables; //当前用户拥有的表
select table_name from all_tables; //所有用户的表
select table_name from dba_tables; //包括系统表
获取表字段：
select * from user_tab_columns where Table_Name='用户表';
select * from all_tab_columns where Table_Name='用户表';
select * from dba_tab_columns where Table_Name='用户表';
获取表注释：
select * from user_tab_comments
相应的还有dba_tab_comments，all_tab_comments，这两个比user_tab_comments多了ower列。
获取字段注释：
select * from user_col_comments
相应的还有dba_col_comments，all_col_comments，这两个比user_col_comments多了ower列。
举例如下
-- 获取表名和表注释
select * from all_tab_comments where owner='HIS' and Table_Name='OPS_EQUIPMENT_SUMMARY';
-- 获取字段名和详情
select * from all_tab_columns where owner='HIS' and Table_Name='OPS_EQUIPMENT_SUMMARY';
-- 获取字段名和字段注释
select * from all_col_comments where owner='HIS' and Table_Name='OPS_EQUIPMENT_SUMMARY';
2、SQL语句指定用户当前模式
MySQL或者SQLSERVER程序在连接时可以在连接串中指定对应的数据库名，达梦数据库中是指定对应的模式名，如果表等对象存在默认模式下，可以不用在连接串中指定默认模式名，达梦数据库用户登录后默认为当前用户的默认模式且模式名与用户名相同。
查看当前模式的对象：查看模式下拥有的对象，可以通过DBA_OBJECTS、ALL_OBJECTS、SYSOBJECTS等视图根据OWNER=‘模式名’去查看。需要注意的是，user_tables等USER视图是查看当前用户下所有模式的对象。
ALTER SESSIONS SET CURRENT_SCHEMA=模式名;
或者 set schema 模式名；
#查看TEST用户下的模式
select USERNAME,NAME AS SCHEMA_NAME,TYPE$
FROM SYSOBJECTS A,DBA_USERS B
WHERE A.PID=B.USER_ID AND A.TYPE$='SCH' AND B.USERNAME='TEST';
#默认模式名与用户名相同，且用户登录之后不指定当前模式或者操作的对象前不加模式名“模式名.对象名”，默认是操作默认模式下的对象；
----------
#查看用户会话当前的模式名：
SQL> SELECT SF_GET_SCHEMA_NAME_BY_ID(CURRENT_SCHID);
行号 SF_GET_SCHEMA_NAME_BY_ID(CURRENT_SCHID)
---------- ---------------------------------------
1 TEST
或者:
SQL> select sys_context('USERENV','CURRENT_SCHEMA');
行号 SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
---------- ---------------------------------------
1 TEST
#指定当前模式
SQL> alter session set current_schema=TEST2;
或者
SQL> set schema TEST2;
参考
在使用达梦数据库时，可能存在一个用户下有多个模式（schema）的情况，当用户登录之后访问非默认模式下的表等对象时，需要使用"模式名.对象名"的方式进行访问等操作，或者可以执行alter session set current_schema=模式名;或者SET SCHEMA <模式名>;指定当前模式。 有时候可能从SQLSERVER或者MYSQL数据库中迁移到达梦数据库时，没有创建与每个数据库对应的梦数据库用户，可能在使用达梦迁移工具迁移时直接将所有MYSQL中的数据库迁移到一个达梦数据库用户下的不同模式中（默认是模式名与MYSQL中数据库名相同）。这种情况下使用达梦数据库用户登录之后访问非默认模式下的对象时，需要"模式名.对象名"的方式，这样对与程序来说可能代码修改量比较大。针对这种情况，在达梦数据库中可以通过指定JDBC URL中的schema属性配置来指定当前模式。
简单演示
SQL语句指定用户当前模式
语句：ALTER SESSIONS SET CURRENT_SCHEMA=模式名; 或者 set schema 模式名；
查询用户下拥有的模式：
select USERNAME,NAME AS SCHEMA_NAME,TYPE$
FROM SYSOBJECTS A,DBA_USERS B
WHERE A.PID=B.USER_ID AND A.TYPE$='SCH' AND B.USERNAME='TEST';
------------------------------------------------------------------------
#查看TEST用户下的模式：
[dmdba@localhost bin]$ ./disql /nolog
disql V8
SQL> login
服务名:
用户名:TEST
密码:
SSL路径:
SSL密码:
UKEY名称:
UKEY PIN码:
MPP类型:
是否读写分离(y/n):
协议类型:
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 2.850(ms)
SQL> select USERNAME,NAME AS SCHEMA_NAME,TYPE$ FROM SYSOBJECTS A,DBA_USERS B WHERE A.PID=B.USER_ID AND A.TYPE$='SCH' AND B.USERNAME='TEST';
行号 USERNAME SCHEMA_NAME TYPE$
---------- -------- ----------- -----
1 TEST TEST SCH ###TEST用户下有TEST、TEST2两个模式
2 TEST TEST2 SCH
已用时间: 18.982(毫秒). 执行号:701.
------------------------
查看当前模式：
#默认模式名与用户名相同，且用户登录之后不指定当前模式或者操作的对象前不加模式名“模式名.对象名”，默认是操作默认模式下的对象；
----------
#查看用户会话当前的模式名：
SQL> SELECT SF_GET_SCHEMA_NAME_BY_ID(CURRENT_SCHID);
行号 SF_GET_SCHEMA_NAME_BY_ID(CURRENT_SCHID)
---------- ---------------------------------------
1 TEST
或者:
SQL> select sys_context('USERENV','CURRENT_SCHEMA');
行号 SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
---------- ---------------------------------------
1 TEST
指定当前模式：
SQL> alter session set current_schema=TEST2;
或者
SQL> set schema TEST2;
SQL> select sys_context('USERENV','CURRENT_SCHEMA');
行号 SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
---------- ---------------------------------------
1 TEST2
或者
SQL> SELECT SF_GET_SCHEMA_NAME_BY_ID(CURRENT_SCHID);
行号 SF_GET_SCHEMA_NAME_BY_ID(CURRENT_SCHID)
---------- ---------------------------------------
1 TEST2
查看当前模式的对象： 查看模式下拥有的对象，可以通过DBA_OBJECTS、ALL_OBJECTS、SYSOBJECTS等视图根据OWNER=‘模式名’去查看。需要注意的是，user_tables等USER视图是查看当前用户下所有模式的对象。
JDBC中指定用户当前模式
JDBC URL属性为：schema，指定用户登录后的当前模式，默认为用户的默认模式。
JDBC URL为：jdbc:dm://ip:port?schema=模式名
简单测试示例如下：
在TEST用户TEST2模式下创建表并插入数据，然后查询：
String name="dm.jdbc.driver.DmDriver";
String url="jdbc:dm://192.168.15.35:5236?schema=TEST2"; //使用schema指定当前模式名
String user="TEST";
String password="123456789";
简单的建表、插入数据、查询
class Table extends Connec{
public void create() throws SQLException{
String sql_1="DROP TABLE TABLE1;";
String sql_2="CREATE TABLE TABLE1(C1 INT,C2 VARCHAR(50));"; //创建表TABLE1
Connection testcon=getConn();
Statement state=testcon.createStatement();
try{
state.executeUpdate(sql_2);
}catch(SQLException e){
state.executeUpdate(sql_1);
state.executeUpdate(sql_2);
}
System.out.print("建表成功" + "\n");
state.close();
testcon.close();
}
public void insert() throws SQLException{
String sql="INSERT INTO TABLE1 SELECT 1,SF_GET_SCHEMA_NAME_BY_ID(CURRENT_SCHID);"; //插入一条数据其中一个值为当前模式名
Connection testcon=getConn();
PreparedStatement pre=testcon.prepareStatement(sql);
try{
pre.executeUpdate();
}catch(SQLException e){
System.out.print(e);
}
System.out.print("插入成功" + "\n");
pre.close();
testcon.close();
}
public void select() throws SQLException{
String sql="SELECT * from TABLE1;";
Connection testcon=getConn();
Statement state=testcon.createStatement();
ResultSet res=state.executeQuery(sql);
while(res.next()){
System.out.print("查询结果：" + "\n");
System.out.println(
res.getInt(1)+","+
res.getString(2)
);
}
}
}
public class GetTable {
public static void main(String[] args) {
Table table=new Table();
try{
table.create();
table.insert();
table.select();
}catch(SQLException e){
System.out.println(e);
}
}
}
总结
MySQL或者SQLSERVER程序在连接时可以在连接串中指定对应的数据库名，达梦数据库中是指定对应的模式名，如果表等对象存在默认模式下，可以不用在连接串中指定默认模式名，达梦数据库用户登录后默认为当前用户的默认模式且模式名与用户名相同。
3、创建视图
CREATE VIEW HIS.CIM_DEVICE AS
select * from (
select id,name,'变压器' as cim_dev_type_name ,'0301' as pms_dev_type_code from "HIS"."CIM_POWERTRANSFORMER"
union
select id,name,'断路器','0305' from "HIS"."CIM_BREAKER"
union
select id,name,'隔离开关','0306' from "HIS"."CIM_DISCONNECTOR"
union
select id,name,'接地刀闸','0306' from "HIS"."CIM_GROUND_DISCONNECTOR"
union
select id,name,'交流线路/线段端点','xl' from "HIS"."CIM_ACLINEEND"
union
select id,name,'交流线路/线段端点','xl' from "HIS"."CIM_ACLINESEGMENT"
)
4、达梦数据库导出表格形式（Excel）方式
5、删除聚集主键及聚集主键索引
现状描述
直接删除聚集主键，则会报错“试图删除聚集主键”。聚集主键索引无法直接删除，会提示“没有删除表[xx]上索引的权限”。
解决办法
两种办法：
第一种：在表上新建一个聚集索引，那么聚集主键索引会变为非聚集，这样可以直接删除主键和索引；
第二种：重新建表，建表时显示指定主键为非聚集；或者在建表之前修改ini参数PK_WITH_CLUSTER为0，这样默认创建主键时为非聚集型；
具体操作如下：
第一种方法：
CREATE TABLE tab1 (A INT PRIMARY KEY,B VARCHAR,C VARCHAR);
---首先创建新的聚集索引
CREATE CLUSTER INDEX IDX01 ON tab1(b);
---然后删除主键约束，主键索引会自动删除(无法直接删除主键索引)
alter table tab1 drop constraint CONS134218785;
---删除新建的聚集索引
drop index IDX01;
---更改主键为非聚集主键
alter table tab1 add NOT CLUSTER primary key(A);
第二种方法：
（1）创建表时指定主键为非聚集主键
CREATE TABLE tab1 (A INT NOT CLUSTER PRIMARY KEY,B VARCHAR,C VARCHAR);
（2）建表之前修改PK_WITH_CLUSTER参数值为0
SP_SET_PARA_VALUE(1,'PK_WITH_CLUSTER',0);
CREATE TABLE tab2 (A INT PRIMARY KEY,B VARCHAR,C VARCHAR);
6、仅当指定列列表,且SET IDENTITY_INSERT为ON时,才能对自增列赋值
- 自增长的SQL表格里面插入指定ID的数据的时候，会禁止你操作
- 插入数据时，自增长列是系统自动处理，不需要你来指定数值，你也指定不了。只有将IDENTITY_INSERT 为 ON 时插入数据时，自增长列你才可以指定一个值
直接上手解决方案： 一直要注意一个点就是，一定要有“；”，还有value后哦面也需要加上“；”这个很多人都会忘了。
set IDENTITY_INSERT ms_server_log_info ON;
insert 表名
value（******）;
SET IDENTITY_INSERT ms_server_log_info OFF
7、查询结果部分替换 成指定的值
将查询结果中的某个字段值部分的替换掉. 如:将 t_student 标准中的id字段中的 ‘1994’ 全部要替换成 ‘2019’,
update t_student set id= replace(id,'19940317','20190317');
这样 所有的id字段的 ‘1994****‘全部被替换成 ‘2019****’