#记录达梦数据库的一些基本操作#
1.创建表
建表主键自增id就用IDENTITY(1,1)
CREATE TABLE 模式名称(也可以理解成数据库名称).表名称(
ID BIGINT IDENTITY(1,1) PRIMARY KEY,
字段1 VARCHAR(100) NULL,
);
2.创建存储过程
CREATE OR REPLACE PROCEDURE "模式名称"."存储过程名称"("参数1" IN VARCHAR)
IS
BEGIN
select/update/delete/insert....
END;
如果执行不了,试试SP_INIT_DBMS_SCHEDULER_SYS(1);后再执行。
3.创建定时任务(计划任务)
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => '名称1',
job_type => 'STORED_PROCEDURE',
job_action => '存储过程方法名',
start_date => SYSDATE,
repeat_interval => 'FREQ=WEEKLY;', --执行时间
enabled => TRUE);
END;
定时任务创建完成在SCHEDULER中查看任务信息以及日志等等。
运行/启用/禁用/停止任务。
--运行
dbms_scheduler.run_job('COLA_JOB',TRUE); -- true代表同步执行
--启用
dbms_scheduler.enable('BACKUP_JOB');
--启用某个scheduler job,当重新启用某个job时,不用担心next_run_date,会自动调整成下次执行时间
--禁用
dbms_scheduler.disable('BACKUP_JOB'); --暂时停止某个scheduler job
--停止
dbms_scheduler.stop_job(job_name => 'COLA_JOB',force => TRUE);
--删除
dbms_scheduler.drop_job(job_name => 'COLA_JOB',force => TRUE);
#收集达梦表数据库:模式名称、表名称、表名称注释、字段名称、字段名称注释、字符类型、字符长度、是否可为空#
1.建表,建表语句跟注释语句只能分开执行,目前没研究原因。
CREATE TABLE HZ_PROJECT_MANAGEMENT1.t1 (
TABLE_SCHEMA VARCHAR2(100) DEFAULT NULL,
TABLE_NAME VARCHAR2(100) DEFAULT NULL,
TABLE_COMMENT VARCHAR2(500) DEFAULT NULL,
COLUMN_NAME VARCHAR2(100) DEFAULT NULL,
COLUMN_COMMENT VARCHAR2(500) DEFAULT NULL,
COLUMN_TYPE VARCHAR2(100) DEFAULT NULL,
IS_NULLABLE VARCHAR2(10) DEFAULT NULL
);
BEGIN
EXECUTE IMMEDIATE 'COMMENT ON TABLE ndkj_ro.T1 IS ''数据库表字段基本信息表''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ndkj_ro.t1.TABLE_SCHEMA IS ''数据库名称''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ndkj_ro.t1.TABLE_NAME IS ''表名称''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ndkj_ro.t1.TABLE_COMMENT IS ''表注释''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ndkj_ro.t1.COLUMN_NAME IS ''字段名称''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ndkj_ro.t1.COLUMN_COMMENT IS ''字段注释''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ndkj_ro.t1.COLUMN_TYPE IS ''字段类型''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ndkj_ro.t1.IS_NULLABLE IS ''字段是否为NULL''';
END;
2.达梦表信息/注释/字段信息/字段注释等
SELECT c.owner AS TABLE_SCHEMA, --'数据库名称'
c.table_name AS TABLE_NAME, --'表名称'
b.comments AS TABLE_COMMENT, --'表注释'
c.column_name AS COLUMN_NAME, --'字段名称'
a.comments AS COLUMN_COMMENT, --'字段注释'
CONCAT(c.data_type, '(',c.data_length, ')') AS COLUMN_TYPE, --'字段类型'
c.nullable AS IS_NULLABLE --'字段是否为NULL'
FROM user_col_comments a
LEFT JOIN user_tab_comments b ON a.table_name = b.table_name
LEFT JOIN all_tab_columns c ON c.table_name = a.table_name AND a.COLUMN_NAME = c.COLUMN_NAME
WHERE c.owner = '模式名' AND c.table_name = '表名'
3.创建存储过程(db_name为模式名称,可以根据自定参数进行调整)
CREATE
OR REPLACE PROCEDURE "HZ_PROJECT_MANAGEMENT1"."updateT1OfNdkjRo"("db_name" IN VARCHAR)
IS
BEGIN
TRUNCATE TABLE HZ_PROJECT_MANAGEMENT1.T1; --清空表
insert into HZ_PROJECT_MANAGEMENT1.t1
SELECT c.owner AS TABLE_SCHEMA, --'数据库名称'
c.table_name AS TABLE_NAME, --'表名称'
b.comments AS TABLE_COMMENT, --'表注释'
c.column_name AS COLUMN_NAME, --'字段名称'
a.comments AS COLUMN_COMMENT, --'字段注释'
CONCAT(c.data_type, '(', c.data_length, ')') AS COLUMN_TYPE, --'字段类型'
c.nullable AS IS_NULLABLE --'字段是否为NULL'
FROM user_col_comments a
INNER JOIN user_tab_comments b ON a.table_name = b.table_name
INNER JOIN all_tab_columns c ON c.table_name = a.table_name AND a.COLUMN_NAME = c.COLUMN_NAME
WHERE c.owner in(db_name);
END;
4.创建定时任务(我是每周执行一次)
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'updateT1OfNdkjRo_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN updateT1OfNdkjRo; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN',
enabled => TRUE,
comments => 'Weekly job to execute updateT1OfNdkjRo stored procedure');
END;
#启动定时任务
BEGIN
DBMS_SCHEDULER.RUN_JOB('updateT1OfNdkjRo_job');
END;
内容均为网上查阅,个人感觉达梦类似oracle,以上均为个人见解,可能存在问题,希望有路过的兄弟们指点指点。
参考:
plsql怎么用字段查表明_如何将oracle,达梦表中的字段类型、字段注释说明、字段名一起查询出来..._plsql查看字段类型-CSDN博客
达梦、Oracle、PostgreSQL查询全部表备注,表字段,全部字段备注,全部索引,全部字段类型_达梦数据库查询表及表备注-CSDN博客dba_scheduler_job相关,创建,暂停,重启,删除_dbms_scheduler.stop_job-CSDN博客
达梦:创建用户并授予用户只读权限_达梦创建用户并授权-CSDN博客
感谢分享。