Java项目中Oracle数据库开发过程中相关内容

目录

1、连接数据库

2、创建用户和授权

3、统计的时候——把列变成行

4、Oracle12c数据库中，根据时间倒序返回最新一条数据

5、其他SQL相关记录

总结一些和Oracle相关的内容

1、连接数据库

使用oracle12c数据库自带的SQL Plus 链接数据库
打开SQL Plus工具，输入：

sqlplus /nologconn sys as sysdba

然后输入Oracle系统管理员（sysdba）的密码，即可成功登录SQL Plus

当然也是可以用其他工具连接的，如PLSQL、DataGrip 等也是可以的

2、创建用户和授权

具体代码如下：

sql">--创建用户
CREATE USER1 gslzyt IDENTIFIED BY USER1;
--授权
GRANT CONNECT, RESOURCE TO USER1;
--授权这个用户所有权限
GRANT ALL PRIVILEGES TO USER1;语法： grant connect, resource to 用户名;
例子： grant connect, resource to test;
-- 撤销权限
语法： revoke connect, resource from 用户名;
列子： revoke connect, resource from test;--
--允许test用户查询任何其他用户的表
GRANT SELECT ANY TABLE TO test;
--为用户test授予无限制的表空间权限
GRANT UNLIMITED TABLESPACE TO test;-- 删除用户
drop user test cascade ;
-- 创建用户、密码为test123
create user test identified by test123 ;
-- 授权dba 权限
grant dba to test ;

3、统计的时候——把列变成行

sql">-- 同一个统计，一个是横着查询
select COUNT(CASE WHEN ALARM_GRADE IN ('0') THEN 'IV' END) IV,COUNT(CASE WHEN ALARM_GRADE IN ('1') THEN 'III' END) III,COUNT(CASE WHEN ALARM_GRADE IN ('2') THEN 'II' END) II,COUNT(CASE WHEN ALARM_GRADE IN ('3') THEN 'I' END) Ifrom ALARM_WATER_RECORDWHERE ALARM_CATEGORY='技术预警';-- 一个是竖着查询条数
SELECT COUNT(ID) AS SUM,CASE ALARM_GRADEWHEN '0' THEN 'IV'WHEN '1' THEN 'III'WHEN '2' THEN 'II'WHEN '3' THEN 'I'ELSE ALARM_GRADEEND AS ALARM_GRADE
FROM ALARM_WATER_RECORD
WHERE ALARM_CATEGORY='技术预警'
GROUP BY ALARM_GRADE;

4、Oracle12c数据库中，根据时间倒序返回最新一条数据

sql">-- 最后面添加，如下内容即可
ORDER BY DATE ASC
FETCH NEXT 1 ROW ONLY

5、其他SQL相关记录

sql">-- SQL将多字段拼接返回
SELECT PIPELINE_ID || '~' || GATEWELLS_ID || '~' || STATION_ID AS CODE,PIPELINE_NAME || '~' || DEVICE_ID || '~' || DEVICE_NAME AS name
FROM SHARE_GATEWELLS_DEVICE

6、查询新增

sql">-- 将查询结果新增到其他表
INSERT INTO BU_JIANCEDIAN_FOTC (SENSOR_ID,PID , TYPE_NAME, SENSOR_POSITION,POSTITION_ID,STATION_ID,TUNNEL_ID)
SELECT DISTINCT SENSOR_ID,'CE21CCBC579047A6AB0BB0A0B54BCB8F' PID , TYPE_NAME, SENSOR_POSITION,POSTITION_ID,STATION_ID,TUNNEL_ID
FROM FOTC_BUSINESS
WHERE TO_CHAR(SENSOR_DATE, 'YYYY-MM-DD') = '2023-11-01'
AND TUNNEL_ID LIKE '%15#渡槽（76+128.03）%';

7、查询最新1小时的数据

sql">-- 查询最新1小时
SELECT * FROM ALARM_WATER_RECORD
WHERE ALARM_TIME  > (SYSDATE - TO_DSINTERVAL('0 1:00:00')) AND ALARM_TIME  <  SYSDATE;

8、oracle中建表语句

语句一：建表时主键使用UUID

sql">--树结构表
-- DROP TABLE BU_TREE_NODE
CREATE TABLE BU_TREE_NODE(ID 			VARCHAR2(32) DEFAULT SYS_GUID() PRIMARY KEY,TNAME 		VARCHAR2(100),PARENT_ID 	VARCHAR2(255),DATA_CONTENT 	VARCHAR2(255),STATUS            VARCHAR2(10),REMARKS           VARCHAR2(255),RES_ONE           VARCHAR2(100),RES_TWO           VARCHAR2(100),RES_THREE         VARCHAR2(100),RES_FOUR          VARCHAR2(100),CREATE_BY         VARCHAR2(64),     CREATE_TIME 	    DATE,             UPDATE_BY         VARCHAR2(64),UPDATE_TIME       DATE
);
-- 建表字段注释
COMMENT ON TABLE  BU_TREE_NODE                  IS '树结构表';
COMMENT ON COLUMN BU_TREE_NODE.ID               IS '表主键ID';
COMMENT ON COLUMN BU_TREE_NODE.TNAME            IS '名称';
COMMENT ON COLUMN BU_TREE_NODE.PARENT_ID        IS '父ID';
COMMENT ON COLUMN BU_TREE_NODE.DATA_CONTENT     IS '数据内容';COMMENT ON COLUMN BU_TREE_NODE.STATUS           IS '状态';					      
COMMENT ON COLUMN BU_TREE_NODE.REMARKS          IS '备注';
COMMENT ON COLUMN BU_TREE_NODE.RES_ONE          IS '预留字段一';
COMMENT ON COLUMN BU_TREE_NODE.RES_TWO          IS '预留字段二';
COMMENT ON COLUMN BU_TREE_NODE.RES_THREE        IS '预留字段三';
COMMENT ON COLUMN BU_TREE_NODE.RES_FOUR         IS '预留字段四';
COMMENT ON COLUMN BU_TREE_NODE.CREATE_BY        IS '创建者';
COMMENT ON COLUMN BU_TREE_NODE.CREATE_TIME      IS '创建时间';
COMMENT ON COLUMN BU_TREE_NODE.UPDATE_BY        IS '更新者';
COMMENT ON COLUMN BU_TREE_NODE.UPDATE_TIME      IS '更新时间';
/

建表语句二：主键使用序列ID

sql">-- 创建表序列 
-- DROP SEQUENCE SEQ_BU_MACA_PLAN;
CREATE SEQUENCE SEQ_BU_MACA_PLANINCREMENT BY 1START WITH 1NOMAXVALUENOMINVALUECACHE 20;-- 创建表 DROP TABLE BU_MACA_PLAN;
DROP TABLE BU_MACA_PLAN CASCADE CONSTRAINTS;
CREATE TABLE BU_MACA_PLAN (ID                NUMBER(20)       NOT NULL,SCHEME_ID         VARCHAR2(64)     DEFAULT NULL,START_TIME        VARCHAR2(255)    DEFAULT '',END_TIME          VARCHAR2(255)    DEFAULT '',CAL_SETP          NUMBER(10)       DEFAULT '',OUT_ZJSZ_FLOW     CLOB,OUT_JZZ_FLOW      CLOB,OUT_FSZ_FLOW      CLOB,MODEL_NAME        VARCHAR2(255)    DEFAULT '',SCHEME_NAME       VARCHAR2(255)    DEFAULT '',RES_ONE           VARCHAR2(255)    DEFAULT NULL,RES_TWO           VARCHAR2(255)    DEFAULT '',CREATE_BY         VARCHAR2(64),CREATE_TIME 	    DATE,UPDATE_BY         VARCHAR2(64)     DEFAULT '',UPDATE_TIME       DATE
);
-- 指定表主键和序列关联
ALTER TABLE BU_MACA_PLAN ADD CONSTRAINT PK_BU_MACA_PLAN PRIMARY KEY (ID);COMMENT ON TABLE  BU_MACA_PLAN                  IS '总干常规调度表';
COMMENT ON COLUMN BU_MACA_PLAN.ID               IS '表主键SEQ_BU_MACA_PLAN.NEXTVAL';COMMENT ON COLUMN BU_MACA_PLAN.SCHEME_ID        IS '方案号';
COMMENT ON COLUMN BU_MACA_PLAN.START_TIME       IS '开始时间';
COMMENT ON COLUMN BU_MACA_PLAN.END_TIME         IS '结束时间';
COMMENT ON COLUMN BU_MACA_PLAN.CAL_SETP         IS '计算步长';
COMMENT ON COLUMN BU_MACA_PLAN.OUT_ZJSZ_FLOW    IS 'ZJSZ';
COMMENT ON COLUMN BU_MACA_PLAN.OUT_JZZ_FLOW     IS 'JZZ';
COMMENT ON COLUMN BU_MACA_PLAN.OUT_FSZ_FLOW     IS 'FSZ';
COMMENT ON COLUMN BU_MACA_PLAN.MODEL_NAME       IS '模型名称';
COMMENT ON COLUMN BU_MACA_PLAN.SCHEME_NAME      IS '方案名称';COMMENT ON COLUMN BU_MACA_PLAN.RES_ONE          IS '创建者';
COMMENT ON COLUMN BU_MACA_PLAN.RES_TWO          IS '创建者';
COMMENT ON COLUMN BU_MACA_PLAN.CREATE_BY        IS '创建者';
COMMENT ON COLUMN BU_MACA_PLAN.CREATE_TIME      IS '创建时间';
COMMENT ON COLUMN BU_MACA_PLAN.UPDATE_BY        IS '更新者';
COMMENT ON COLUMN BU_MACA_PLAN.UPDATE_TIME      IS '更新时间';
/

上面建表中字段类型是clob，在Oracle中，可以使用CLOB（Character Large Object）数据类型来存储大文本数据，包括JSON字符串。以下是示例代码，演示如何将JSON数据以字符串形式存储到数据库：

sql">-- 创建表
CREATE TABLE json_data (id NUMBER PRIMARY KEY,json_str CLOB
);-- 插入数据
INSERT INTO json_data (id, json_str) VALUES (1, '{"name": "John", "age": 30}');

9、新增表字段（增加字段时，只能排在已有字段的后面，不能插到已有字段之间）
增加一个字段：

sql">-- 语法：Alter Table 表名 Add 字段名称  字段类型；
ALTER   TABLE    BU_MACA_PLAN    ADD    SLCH_STATUS   VARCHAR2(255);
COMMENT ON COLUMN BU_MACA_PLAN.SLCH_STATUS   IS '状态';ALTER   TABLE    BU_MACA_PLAN    ADD    LENGTH    NUMBER(8,2);
COMMENT ON COLUMN BU_MACA_PLAN.LENGTH   IS '长度';ALTER   TABLE    BU_MACA_PLAN    ADD    STAGE    NUMBER(2);
COMMENT ON COLUMN BU_MACA_PLAN.STAGE   IS '阶段';

增加两个字段；

sql">-- 语法：Alter Table 表名 Add (字段名称  字段类型,字段名称  字段类型)；
Alter Table BU_MACA_PLAN Add (RES_ONE VARCHAR2(255),RES_TWO VARCHAR2(255));

各种实际SQL：

sql">-- 从A 表中查询出 根据B 表的名称模糊搜索
SELECT A.SITE_NAME,A.SITE_ID, B.CAME_ALIAS AS NAME, B.SITE_ID AS ID
FROM BASE_SITE_GATE A
JOIN BAS_CAME_TREE B ON A.SITE_NAME LIKE '%' || B.CAME_ALIAS || '%';

sql">-- 查询单个字段 多条件模糊查询
SELECT * FROM BASE_SITE_GATE
WHERE REGEXP_LIKE(SITE_NAME, '(香泉)七支渠旁下渠通道|13号庙坪下渠通道内|总干一干渠末端|17#安监房|2#总分水闸后水下球机|三干渠6#倒虹吸出口|三干渠6#倒虹吸进口');

sql">-- 条件筛选出最大值、最小值、和平均值
SELECT FSZ_ID, FSZ_NAME, FSZ_VALUE, SCHEME_ID, MODEL_NAME, YOY_VALUE, QOQ_VALUE, RES_ONE, RES_TWO, MAX_VALUE, MIN_VALUE, AVG_VALUE
FROM (SELECT FSZ_ID, FSZ_NAME, FSZ_VALUE, SCHEME_ID, MODEL_NAME, YOY_VALUE, QOQ_VALUE, RES_ONE, RES_TWO,MAX(TO_NUMBER(FSZ_VALUE)) OVER () AS MAX_VALUE,MIN(TO_NUMBER(FSZ_VALUE)) OVER () AS MIN_VALUE,ROUND(AVG(TO_NUMBER(FSZ_VALUE)) OVER (), 3) AS AVG_VALUEFROM BU_OUT_FSZ_FLOWWHERE SCHEME_ID = 'c1a47cbd270a4ede89d6ecd5ec0aff4f' and FSZ_ID='15039'GROUP BY FSZ_ID, FSZ_NAME, FSZ_VALUE, SCHEME_ID, MODEL_NAME, YOY_VALUE, QOQ_VALUE, RES_ONE, RES_TWO
) t;SELECT FSZ_NAME AS NAME, SCHEME_ID,FSZ_VALUE AS VALUE, CUR_DATE, MODEL_NAME, YOY_VALUE, QOQ_VALUE, RES_ONE,RES_TWO, MAX_VALUE AS ZUIDAZHI,MIN_VALUE AS ZUIXIAOZHI,AVG_VALUE AS PINJUNZHIFROM(SELECT distinct FSZ_NAME, SCHEME_ID, FSZ_VALUE,CUR_DATE, MODEL_NAME, YOY_VALUE, QOQ_VALUE, RES_ONE, RES_TWO,MAX(TO_NUMBER(FSZ_VALUE)) OVER () AS MAX_VALUE,MIN(TO_NUMBER(FSZ_VALUE)) OVER () AS MIN_VALUE,AVG(TO_NUMBER(FSZ_VALUE)) OVER () AS AVG_VALUEFROM BU_OUT_FSZ_FLOWwhere SCHEME_ID='c1a47cbd270a4ede89d6ecd5ec0aff4f'AND FSZ_NAME = '总干')--
SELECT FSZ_NAME, SCHEME_ID, FSZ_VALUE,CUR_DATE, MODEL_NAME, YOY_VALUE, QOQ_VALUE, RES_ONE, RES_TWO, MAX_VALUE AS MAX_VALUE,MIN_VALUE AS MIN_VALUE,AVG_VALUE AS AVG_VALUE
FROM(SELECT distinct FSZ_NAME, SCHEME_ID, FSZ_VALUE,CUR_DATE, MODEL_NAME, YOY_VALUE, QOQ_VALUE, RES_ONE, RES_TWO,MAX(TO_NUMBER(FSZ_VALUE)) OVER () AS MAX_VALUE,MIN(TO_NUMBER(FSZ_VALUE)) OVER () AS MIN_VALUE,AVG(TO_NUMBER(FSZ_VALUE)) OVER () AS AVG_VALUEFROM BU_OUT_FSZ_FLOW)
ORDER BY CUR_DATE ASC;

把表中的两个字段拼接返回JSON结果：

sql">SELECT JSON_OBJECT('demandQ' VALUE JSON_ARRAYAGG(DEMAND_Q),'name' VALUE MAX(PLAN_NAME)) AS waterPlan
FROM BU_WATER_PLAN
GROUP BY PLAN_NAME;

示例：{"demandQ":[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0],"name":"四干"}

对某个字段查询出平均值并保留4位小数：

sql">SELECT ROUND(AVG(TO_NUMBER(FSZ_VALUE)) OVER () ,4) FROM BU_OUT_FSZ_FLOW Where FSZ_ID='15039';

查询不同时间的数据

sql">SELECT
CUR_DATE,
(SELECT SUM(FSZ_VALUE)
FROM BU_OUT_FSZ_FLOW
WHERE SCHEME_ID ='3e2b89d1088d4aa0ae66c7f4cdf266f4'
AND CUR_DATE = t.CUR_DATE) AS OLD_SUM_DAY_VALUE,
(SELECT SUM(FSZ_VALUE)
FROM BU_OUT_FSZ_FLOW
WHERE SCHEME_ID ='682735a043e64d8fa5cb272cb4e64eff'
AND CUR_DATE = t.CUR_DATE) AS NEW_SUM_DAY_VALUE
FROM (SELECT DISTINCT CUR_DATE
FROM BU_OUT_FSZ_FLOW
WHERE SCHEME_ID IN ('3e2b89d1088d4aa0ae66c7f4cdf266f4', '682735a043e64d8fa5cb272cb4e64eff')) t
ORDER BY CUR_DATE ASC;
-- ↑ 优化后的如下 ↓
SELECTCUR_DATE,SUM(CASE WHEN SCHEME_ID ='3e2b89d1088d4aa0ae66c7f4cdf266f4' THEN FSZ_VALUE ELSE 0 END) AS OLD_SUM_DAY_VALUE,SUM(CASE WHEN SCHEME_ID ='682735a043e64d8fa5cb272cb4e64eff' THEN FSZ_VALUE ELSE 0 END) AS NEW_SUM_DAY_VALUE
FROM BU_OUT_FSZ_FLOW
WHERE SCHEME_ID IN ('3e2b89d1088d4aa0ae66c7f4cdf266f4', '682735a043e64d8fa5cb272cb4e64eff')
GROUP BY CUR_DATE
ORDER BY CUR_DATE ASC;

sql">-- 根据不同类别查询数据
SELECT ID, TNAME AS LABEL, PARENT_ID AS PID, RES_ONE AS SURL,CASEWHEN DATA_CONTENT ='行政区划' OR DATA_CONTENT ='水系' AND  PARENT_ID !='1' THEN MODEL_TYPE ELSE DATA_CONTENTEND AS TYPEFROM BU_TREE_NODEwhere (MODEL_TYPE IN ('3dtiles','wms','point')or MODEL_TYPE is null )AND DATA_CONTENT !='闸门' AND DATA_CONTENT !='监测' AND DATA_CONTENT !='waterworks';

查询和时间相关SQL

sql">-- 查询当前时间之前的3小时
SELECT SYSDATE - INTERVAL '3' HOUR FROM DUAL;
-- 查询当前时间之后的3天
SELECT SYSDATE + INTERVAL '3' DAY FROM DUAL;
-- 查询当时时间转成字符串
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

sql">-- 查询出最大日期的数据
WITH MAX_DATE AS (SELECT MAX(SENSOR_DATE) - 1 AS MAX_SENSOR_DATE FROM TC_BUSINESS)
SELECT ID,SOR_ID,IPMENT_ID,SOR_MODEL,TION_ID,E_NAME,NEL_ID,TITION_ID,SOR_POSITION,SOR_UNIT,SOR_WARN,ABS(SENSOR_VALUE) AS SENSOR_VALUE,SOR_DATE,RE3,ATE_TIME,ATE_TIME,SXYZ,SSENID,PLAY_POSTITION
FROM TC_BUSINESS, MAX_DATE
WHERE SENSOR_ID = 'DC11-S1' AND SENSOR_DATE >= MAX_DATE.MAX_SENSOR_DATE
ORDER BY SENSOR_DATE ASC;

sql">-- 根据表中的日期统计出，不同等级的数量
SELECT TO_CHAR(A.ALARM_TIME, 'YYYY-MM-DD') AS TIME,COUNT( CASE WHEN A.ALARM_GRADE IN ( '0' ) THEN 'LOW' END ) L_SUM,COUNT( CASE WHEN A.ALARM_GRADE IN ( '1' ) THEN 'MID' END ) M_SUM,COUNT( CASE WHEN A.ALARM_GRADE IN ( '2' ) THEN 'HIGH' END ) H_SUM,COUNT( CASE WHEN A.ALARM_GRADE IN ( '3' ) THEN 'SERIOUS' END ) S_SUM,COUNT( 1 ) AS SUMFROMALARM_WATER_RECORD AWHERE1 = 1AND A.ALARM_TYPE LIKE concat( concat( '%', '安全预警' ), '%' )AND A.ALARM_SECTION LIKE concat( concat( '%', '实测' ), '%' )AND A.ALARM_TIME > TRUNC( SYSDATE - 7 )AND A.ALARM_TIME < SYSDATEGROUP BYTO_CHAR( A.ALARM_TIME, 'YYYY-MM-DD' )ORDER BYTIME ASC;

sql">-- 查询出一定时间内的最大值，然后联表查询出其他需要的信息
WITH MAX_VALUES AS (SELECT TYPE_NAME, MAX(SENSOR_VALUE) AS MAX_VALUEFROM FOTC_BUSINESSWHERE  TO_CHAR(SENSOR_DATE, 'YYYY-MM-DD') <= '2023-12-31'AND  TO_CHAR(SENSOR_DATE, 'YYYY-MM-DD') >= '2023-12-01'GROUP BY TYPE_NAME
),
MAX_SENSOR_VALUES AS (SELECT FB.TYPE_NAME, FB.SENSOR_VALUE AS svalue, BJD.STATION_ID,BJD.ID,BJD.SENSOR_ID,BJD.EQUIPMENT_ID,BJD.PID,BJD.TUNNEL_ID,BJD.POSTITION_ID,BJD.SENSOR_POSITION,BJD.SENSOR_UNIT,BJD.SENSOR_WARN,BJD.SENSOR_DATE,BJD.SPARE3,BJD.UPDATE_TIME,BJD.RES_ONE,BJD.RES_TWO,BJI.LATITUDE AS LATITUDE,BJI.LONGITUDE AS LONGITUDE,ROW_NUMBER() OVER (PARTITION BY BJD.STATION_ID ORDER BY FB.SENSOR_VALUE DESC) AS RNFROM FOTC_BUSINESS FBJOIN MAX_VALUES MV ON FB.TYPE_NAME = MV.TYPE_NAME AND FB.SENSOR_VALUE = MV.MAX_VALUEJOIN BU_JIANCEDIAN_FOTC BJD ON FB.SENSOR_ID = BJD.SENSOR_IDJOIN BU_JIANCEDIAN_INFO BJI ON BJD.PID = BJI.GID
)
SELECT * FROM MAX_SENSOR_VALUES WHERE RN = 1;

sql">-- 查询出当年该字段最大数据量和
WITH SUMCOUNT AS(SELECT SUM(TOTAL) AS WATERFROM GATE_YEAR_VALUEWHERE TO_CHAR(SYSDATE, 'YYYY') = YEARGROUP BY GATE_ID
)
SELECT SUM(WATER) FROM SUMCOUNT;