目录
-
达梦
1、增量建表语句
DROP TABLE IF EXISTS tableName1; create table tableName1 ( ID VARCHAR2(36) not null, NAME VARCHAR2(36), constraint PK_tableName1 primary key (ID) ); comment on table tableName1 is '表1'; DROP TABLE IF EXISTS tableName2; create table tableName2 ( ID VARCHAR2(36) not null, NAME VARCHAR2(36), constraint PK_tableName2 primary key (ID) ); comment on table tableName2 is '表2';
2、增量添加字段语句
DECLARE num number; begin select count(1) into num from user_tab_columns where table_name = upper('tableName') and column_name = upper('cloumName'); if num = 0 then EXECUTE IMMEDIATE 'alter table tableName add cloumName VARCHAR2(500)'; end if; end; COMMENT ON COLUMN tableName.cloumName IS '字段1'; COMMIT; DECLARE num number; begin select count(1) into num from user_tab_columns where table_name = upper('tableName2') and column_name = upper('cloumName2'); if num = 0 then EXECUTE IMMEDIATE 'alter table tableName2 add cloumName2 VARCHAR2(500)'; end if; end; COMMENT ON COLUMN tableName2.cloumName2 IS '字段2'; COMMIT;
3、修改字段长度语句
alter table tableName modify cloumName VARCHAR2(2000);
4、添加注释语句
COMMENT ON COLUMN tableName2.cloumName2 IS '字段2';
5 、删除锁
SELECT VTW.ID AS TRX_ID, VS.SESS_ID, VS.SQL_TEXT, VS.APPNAME, VS.CLNT_IP FROM V$TRXWAIT VTW LEFT JOIN V$TRX VT ON(VTW.ID=VT.ID) LEFT JOIN V$SESSIONS VS ON(VT.SESS_ID=VS.SESS_ID); sp_close_session('sess_id');
6、安装教程
7、存储过程
CREATE OR REPLACE PROCEDURE TONGSHIINFO(nodeid varchar(40)) is begin --创建满足条件的临时表 execute immediate 'delete from temp_temptable'; insert into temp_temptable SELECT get_uuid() rid, A00, TO_CHAR(A1601, 'yyyy.MM') starttime, TO_CHAR(A1602, 'yyyy.MM') endtime, A1603 renzhidanwei FROM A16 where A00 IN (SELECT DISTINCT A00 FROM IPAD_RESUME where NODEID = nodeid) and A1603 is not null and A1601 is not NULL; -- for y in (select distinct dmcpt from IPAD_B01 where status <>'2' and dmcpt not in ('') AND b00 IN (SELECT a0201b FROM ipad_a02_function)order by length(dmcpt) desc) loop begin --每次删除查询后的数据临时表 DELETE FROM tempdb_table1; --插入满足条件的数据临时表 INSERT INTO tempdb_table1(temptablea00, starttime,endtime,flag,remark) SELECT a00,starttime,endtime,0, y.dmcpt FROM temp_temptable WHERE renzhidanwei LIKE '%'|| y.dmcpt || '%'; -- delete from temp_temptable WHERE renzhidanwei LIKE '%'|| y.dmcpt || '%'; -- if exists (select 1 from tempdb_table1 ) then INSERT INTO IPAD_RelationShip_R (RID,RNAME,A001,A002,REMARK,NODEID) SELECT get_uuid(),'',aid,bid,aremark,nodeid FROM ( ); end if; end; end loop; end;
8、函数
CREATE OR REPLACE FUNCTION demofunction( --传入参数 COL1 date, COL2 date ) RETURN VARCHAR2 IS --函数内使用的临时变量 Col3 date; V_COL1 date; DMVAL VARCHAR2(200); BEGIN --函数体 COL3 := COL2; DMVAL := ''; V_COL1 := COL1; IF V_COL1 is null then RETURN DMVAL; ELSE DMVAL := LTRIM(RTRIM(TO_CHAR(V_COL1,'YYYY')))||'.'||LTRIM(RTRIM(TO_CHAR(V_COL1,'MM')))||CHR(13)||CHR(10)||'('||LTRIM(RTRIM(floor(MONTHS_BETWEEN (TRUNC (COL3, 'MONTH'),TRUNC (V_COL1, 'MONTH')) / 12)))||'岁)'; RETURN DMVAL; end if; END;
-
金仓
1、增量建表语句
DROP TABLE IF EXISTS tableName1; create table tableName1 ( ID VARCHAR2(36) not null, constraint PK_tableName1 primary key (ID) ); comment on table tableName1 is '表1'; DROP TABLE IF EXISTS tableName2; create table tableName2 ( ID VARCHAR2(36) not null, constraint PK_tableName2 primary key (ID) ); comment on table tableName2 is '表2';
2、增量添加字段语句
DECLARE num number; begin select count(1) into num from user_tab_columns where table_name = upper('tableName') and column_name = upper('columName'); if num = 0 then EXECUTE IMMEDIATE 'alter table tableName add columName varchar2(100)'; end if; end; COMMENT ON COLUMN tableName.columName IS '备注'; COMMIT;
3、修改字段长度语句
ALTER TABLE tableName ALTER COLUMN columName TYPE VARCHAR2(60);
4、添加注释语句
COMMENT ON COLUMN tableName1.columName1 IS '字段';
5、删除锁
SELECT pid,usename,application_name app,client_addr,xact_start,wait_event_type,wait_event,state,query FROM sys_stat_activity WHERE pid<>(SELECT sys_backend_pid()) AND datname='gistest' AND usename='sde';--数据库名和用户名 SELECT sys_terminate_backend(6120);--注意,数据库查出来的pid,逗号要去掉!!!
6、安装教程
7、存储过程
CREATE OR REPLACE PROCEDURE TONGSHIINFO(NODEID CHARACTER VARYING) LANGUAGE PLSQL AS begin --创建满足条件的历临时表 DROP TABLE IF EXISTS temp_temptable; CREATE TABLE temp_temptable as SELECT get_uuid() rid, A00, TO_CHAR(A1601, 'yyyy.MM') starttime, TO_CHAR(A1602, 'yyyy.MM') endtime, A1603 renzhidanwei FROM A16 where A00 IN (SELECT DISTINCT A00 FROM IPAD_RESUME where NODEID = nodeid) and A1603 is not null and A1601 is not NULL; --创建每次循环时的数据临时表 DROP TABLE IF EXISTS tempdb_table1; CREATE TABLE tempdb_table1 ( id SERIAL, temptablea00 VARCHAR(200 char), starttime VARCHAR(500 char), endtime VARCHAR(500 char), flag INT, remark VARCHAR(500 char) ); -- for y in (select distinct dmcpt from IPAD_B01 where status <>'2' and dmcpt not in ('去除机构') AND b00 IN (SELECT a0201b FROM ipad_a02_function)order by length(dmcpt) desc) loop begin --每次删除查询后的数据临时表 DELETE FROM tempdb_table1; --插入满足条件的数据临时表 INSERT INTO tempdb_table1(temptablea00, starttime,endtime,flag,remark) SELECT a00,starttime,endtime,0, y.dmcpt FROM temp_temptable WHERE renzhidanwei LIKE '%'|| y.dmcpt || '%'; -- delete from temp_temptable WHERE renzhidanwei LIKE '%'|| y.dmcpt || '%'; --满足条件的数据临时表存在数据则进行插入 if exists (select 1 from tempdb_table1 ) then INSERT INTO IPAD_RelationShip_R (RID,RNAME,A001,A002,REMARK,NODEID) SELECT get_uuid(),'',aid,bid,aremark,nodeid FROM ( ); end if; end; end loop; end;
8、函数
CREATE OR REPLACE FUNCTION demofunction( --传入参数 COL1 date, COL2 date ) RETURN VARCHAR2 IS --函数内使用的临时变量 Col3 date; V_COL1 date; DMVAL VARCHAR2(200); BEGIN --函数体 COL3 := COL2; DMVAL := ''; V_COL1 := COL1; IF V_COL1 is null then RETURN DMVAL; ELSE DMVAL := LTRIM(RTRIM(TO_CHAR(V_COL1,'YYYY')))||'.'||LTRIM(RTRIM(TO_CHAR(V_COL1,'MM')))||CHR(13)||CHR(10)||'('||LTRIM(RTRIM(floor(MONTHS_BETWEEN (TRUNC (COL3, 'MONTH'),TRUNC (V_COL1, 'MONTH')) / 12)))||'岁)'; RETURN DMVAL; end if; END;
9、设置默认模式
ALTER database 数据库名称 SET search_path TO “$USER”, 模式名称, my_schema;
10、表收缩
-- 查询表膨胀情况 SELECT relname,n_live_tup,n_dead_tup FROM sys_stat_user_tables ORDER BY n_dead_tup DESC -- 针对表膨胀较大的表进行单表收缩,收缩后更新统计(ANALYZE 可以全库收缩) VACUUM FULL tablename; ANALYZE tablename;
-
瀚高
1、增量建表语句
DROP TABLE IF EXISTS tableName1; create table tableName1 ( ID VARCHAR(36) not null, constraint PK_tableName1 primary key (ID) ); comment on table tableName1 is '表1'; DROP TABLE IF EXISTS tableName2; create table tableName2 ( ID VARCHAR(36) not null, constraint PK_tableName2 primary key (ID) ); comment on table tableName2 is '表2';
2、增量添加字段语句
do $$ begin if not exists (select * from user_tab_columns where table_name = 'tableName' and column_name = 'columName') then alter table tableName add columName VARCHAR(4000); end if; end $$;
3、修改字段长度语句
ALTER TABLE tableName ALTER COLUMN columName TYPE VARCHAR(60);
4、添加注释语句
comment on table tableName2 is '表2';
5、删除锁
6、安装教程
7、存储过程
drop function tongshiinfo; CREATE OR REPLACE FUNCTION TONGSHIINFO(nnodeid character varying) RETURNS character varying LANGUAGE plpgsql AS $function$ DECLARE y RECORD; begin --创建满足条件的临时表 DROP TABLE IF EXISTS temp_temptable; CREATE TABLE temp_temptable as SELECT get_uuid() rid, A00, TO_CHAR(A1601, 'yyyy.MM') starttime, TO_CHAR(A1602, 'yyyy.MM') endtime, A1603 renzhidanwei FROM A16 where A00 IN (SELECT DISTINCT A00 FROM IPAD_RESUME where NODEID = nnodeid) and A1603 is not null and A1601 is not NULL; --创建每次循环时带入名称查询后的数据临时表 DROP TABLE IF EXISTS tempdb_table1; CREATE TABLE tempdb_table1 ( id SERIAL, temptablea00 VARCHAR(200 ), starttime VARCHAR(500 ), endtime VARCHAR(500 ), flag INT, remark VARCHAR(500 ) ); -- for y in (select distinct dmcpt,length(dmcpt) from IPAD_B01 where status <>'2' and dmcpt not in ('') AND b00 IN (SELECT a0201b FROM ipad_a02_function)order by length(dmcpt) desc) loop begin --每次删除查询后的数据临时表 DELETE FROM tempdb_table1; --插入满足条件的数据临时表 INSERT INTO tempdb_table1(temptablea00, starttime,endtime,flag,remark) SELECT a00,starttime,endtime,0, y.dmcpt FROM temp_temptable WHERE renzhidanwei LIKE '%'|| y.dmcpt || '%'; -- delete from temp_temptable WHERE renzhidanwei LIKE '%'|| y.dmcpt || '%'; --满足条件的数据临时表存在数据则进行插入 if exists (select 1 from tempdb_table1 ) then INSERT INTO IPAD_RelationShip_R (RID,RNAME,A001,A002,REMARK,NODEID) SELECT get_uuid(),'同事',aid,bid,aremark,nnodeid FROM ( ); end if; end; end loop; return 0; end; $function$;
8、函数
drop function if exists demofunction; CREATE OR REPLACE FUNCTION demofunction( --传入参数 COL1 date, COL2 date ) RETURNS VARCHAR2 LANGUAGE plpgsql AS $function$ declare --函数内使用的临时变量 Col3 date; V_COL1 date; DMVAL VARCHAR2(200); BEGIN --函数体 COL3 := COL2; DMVAL := ''; V_COL1 := COL1; IF V_COL1 is null then RETURN DMVAL; ELSE DMVAL := LTRIM(RTRIM(TO_CHAR(V_COL1,'YYYY')))||'.'||LTRIM(RTRIM(TO_CHAR(V_COL1,'MM')))||CHR(13)||CHR(10)||'('||LTRIM(RTRIM(to_char(floor(MONTHS_BETWEEN (TRUNC (COL3, 'MONTH'),TRUNC (V_COL1, 'MONTH')) / 12))))||'岁)'; RETURN DMVAL; end if; END; $function$;
-
ORACLE
1、增量建表语句
declare tableExist number; begin select count(1) into tableExist from user_tables where table_name=upper('tableName1') ; if tableExist > 0 then execute immediate 'DROP TABLE tableName1'; end if; end; / create table tableName1 ( ID VARCHAR2(36) not null, NODEID VARCHAR2(36), constraint PK_tableName1 primary key (ID) ); / comment on table tableName1 is '表1'; /
2、增量添加字段语句
declare tableExist number; begin select count(1) into tableExist from user_tab_columns where table_name=upper('tableName')and COLUMN_NAME='columName' ; if tableExist < 0 then execute immediate 'alter table tableName add(columName varchar2(4000));'; end if; end; /
3、修改字段长度语句
alter table tableName modify cloumName VARCHAR2(2000);
4、添加注释语句
comment on table tableName1 is '表1';
COMMENT ON COLUMN tableName.columName IS '字段';
5、删除锁
6、安装教程
7、存储过程
create or replace PROCEDURE TongShiInfo(nodeid varchar) is begin DECLARE i INT := 0; --记录总数 j INT := 0; s4 varchar(200 char); --存放中间表的 s5 VARCHAR(20 char); --存放开始时间 s6 VARCHAR(20 char);--存放结束时间 num int :=0; begin DELETE FROM temp_temptable; commit; for t in (SELECT DISTINCT A00 FROM IPAD_RESUME where NODEID = nodeid) loop begin INSERT INTO temp_temptable(rid, a00,shijianduan, renzhidanwei) SELECT get_uuid(), A00, TO_CHAR(A1601, 'yyyy.MM')||'-'||TO_CHAR(A1602, 'yyyy.MM'),A1603 FROM A16 where A00=t.A00 and A1603 is not null and A1602 is not null; end; end loop; -- for y in (select distinct dmcpt from IPAD_B01 where status <>'2' and dmcpt not in ('')order by length(dmcpt) desc) loop begin DELETE FROM tempdb_table1; j := 1; INSERT INTO tempdb_table1(id, temptablea00, starttime,endtime,flag,remark) SELECT row_number() over(order by RID), a00,SUBSTR(shijianduan,0,7),NVL(SUBSTR(shijianduan,9,7),NULL),0, y.dmcpt FROM temp_temptable WHERE dbms_lob.substr(renzhidanwei) LIKE '%'|| y.dmcpt || '%'; delete from temp_temptable WHERE dbms_lob.substr(renzhidanwei) LIKE '%'|| y.dmcpt || '%'; SELECT MAX(id) into i FROM tempdb_table1; WHILE j<i loop BEGIN select count(*) into num from tempdb_table1 where id = j; if num > 0 then --拿到需要比对的记录 SELECT temptablea00,starttime, endtime into s4,s5,s6 FROM tempdb_table1 WHERE id = j; --和除自己以外的序号大的记录进行比较 UPDATE tempdb_table1 SET flag = isCrossTime(s5,s6,starttime,endtime) WHERE id > j; --将flag为1的记录列为历任同事 --对是否存在记录做查询 INSERT INTO IPAD_RelationShip_R (RID,RNAME,A001,A002,REMARK,NODEID) SELECT DISTINCT get_uuid(),'同事',s4,temptablea00,NVL(REMARK,'同事'),nodeid FROM tempdb_table1 WHERE flag = 1;commit; INSERT INTO IPAD_RelationShip_R (RID,RNAME,A002,A001,REMARK,NODEID) SELECT DISTINCT get_uuid(),'同事',s4,temptablea00,NVL(REMARK,'同事'),nodeid FROM tempdb_table1 WHERE flag = 1;commit; delete from tempdb_table1 where flag = 1;commit; end if; j := j + 1; END; end loop; end; end loop; --删除重复记录 delete from IPAD_RelationShip_R a where (a.rname,a.a001,a.a002,a.remark,a.nodeid) in (select rname,a001,a002,remark,nodeid from IPAD_RelationShip_R group by rname,a001,a002,remark,nodeid having count(1) > 1) and rowid not in (select min(rowid) from IPAD_RelationShip_R group by rname,a001,a002,remark,nodeid having count(1)>1) and a.RName ='同事'; commit; end; end; /
8、函数
CREATE OR REPLACE FUNCTION demofunction1( --传入参数 COL1 date, COL2 date ) RETURN VARCHAR2 IS --函数内使用的临时变量 Col3 date; V_COL1 date; DMVAL VARCHAR2(200); BEGIN --函数体 COL3 := COL2; DMVAL := ''; V_COL1 := COL1; IF V_COL1 is null then RETURN DMVAL; ELSE DMVAL := LTRIM(RTRIM(TO_CHAR(V_COL1,'YYYY')))||'.'||LTRIM(RTRIM(TO_CHAR(V_COL1,'MM')))||CHR(13)||CHR(10)||'('||LTRIM(RTRIM(floor(MONTHS_BETWEEN (TRUNC (COL3, 'MONTH'),TRUNC (V_COL1, 'MONTH')) / 12)))||'岁)'; RETURN DMVAL; end if; END; / CREATE OR REPLACE FUNCTION demofunction2( DATETIME DATE ) RETURN VARCHAR2 IS DATEYM VARCHAR2(20); BEGIN DATEYM := ''; IF DATETIME IS NULL THEN RETURN DATEYM; ELSE DATEYM := LTRIM(RTRIM(TO_CHAR(DATETIME,'YYYY.MM'))); RETURN DATEYM; END IF; END; /
-
MySql
1、增量建表语句
DROP TABLE IF EXISTS "TABLENAME"; CREATE TABLE "TABLENAME" ( "ID" varchar(36) NOT NULL, "NAME" varchar(50) DEFAULT NULL, PRIMARY KEY ("ID") USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='表名称';
2、增量添加字段语句
3、修改字段长度语句
alter table tableName modify column columnName 字段类型(长度)
4、添加注释语句
alter table tableName add columName 字段类型(长度)default null comment '备注'
5、删除锁
6、安装教程
7、存储过程
-- 创建TONGSHIINFO()存储过程 DELIMITER $$ DROP PROCEDURE IF EXISTS TONGSHIINFO$$ CREATE PROCEDURE TONGSHIINFO ( nodeid VARCHAR(40)) BEGIN -- 定义变量 DECLARE s int DEFAULT 0; DECLARE unitName varchar(255); -- 定义游标,并将sql结果集赋值到游标中 DECLARE report CURSOR FOR select distinct dmcpt from IPAD_B01 where status <>'2' and dmcpt not in ('去除机构') AND b00 IN (SELECT a0201b FROM ipad_a02_function) order by length(dmcpt) desc; -- 声明当游标遍历完后将标志变量置成某个值 DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1; -- 清空临时表后插入数据 delete from temp_temptable; insert into temp_temptable SELECT uuid() rid, A00, date_format(A1601, '%Y.%m') starttime, date_format(A1602, '%Y.%m') endtime, A1603 renzhidanwei FROM A16 where A00 IN (SELECT DISTINCT A00 FROM IPAD_RESUME where NODEID = nodeid) and A1603 is not null and A1601 is not NULL; -- 打开游标 open report; -- 将游标中的值赋值给变量,注意:变量名不要和返回的列名同名,变量顺序要和sql结果列的顺序一致 fetch report into unitName; while s<>1 do -- 每次删除查询后的数据临时表 DELETE FROM tempdb_table1; -- 插入满足条件的数据临时表 INSERT INTO tempdb_table1(temptablea00, starttime,endtime,flag,remark) SELECT a00,starttime,endtime,0, unitName FROM temp_temptable WHERE renzhidanwei LIKE CONCAT('%', unitName , '%'); -- 插入满足条件的数 delete from temp_temptable WHERE renzhidanwei LIKE CONCAT('%', unitName , '%'); -- 满足条件的数据临时表存在数据则进行插入 if exists (select 1 from tempdb_table1 ) then INSERT INTO IPAD_RelationShip_R (RID,RNAME,A001,A002,REMARK,NODEID) SELECT uuid(),'同事',aid,bid,aremark,nodeid FROM ( )T; end if; -- 当s等于1时表明遍历以完成,退出循环 fetch report into unitName; end while; -- 关闭游标 close report; END;$$ DELIMITER ;
8、函数
DELIMITER $$ DROP FUNCTION IF EXISTS demofunction$$ CREATE FUNCTION demofunction( COL1 date,COL2 date) RETURNS VARCHAR(5000) BEGIN -- 函数内使用的临时变量 DECLARE Col3 date default COL2; DECLARE V_COL1 date default COL1; DECLARE DMVAL VARCHAR(200) default ''; IF (V_COL1 is null) then set DMVAL = ''; ELSE set DMVAL = CONCAT_WS('',LTRIM(RTRIM(date_format(V_COL1,'%Y'))),'.',LTRIM(RTRIM(date_format(V_COL1,'%m'))),CHAR(13),CHAR(10),'(',LTRIM(RTRIM(TIMESTAMPDIFF( YEAR, V_COL1,COL3))),'岁)'); end if; return DMVAL; END ;$$ DELIMITER ;