-- 表2可以是复合表
INSERT INTO 表 (字段1,字段2,字段3) SELECT 字段1,字段2,字段3 FROM 表2;
-- 将表2的数据插入到表1
INSERT INTO 表1 SELECT * FROM 表2;
create table 备份表名 as SELECT * FROM 需要备份的表
更新
UPDATE 表 A SET A.字段1 = 'G03', A.字段2='株洲车辆段' WHERE A.字段3 = '字段3' AND A.字段4 = '字段4';
SELECT a.*,a.rowid FROM 表 a -- 防止锁表直接修改数据
SELECT * FROM 表 for update --直接修改数据，有锁表风险
查询
SELECT 字段1,字段2,字段3 FROM 表1
删除
DELETE FROM 表1 WHERE 字段3='2024-06';
简单函数
sys_guid() -- 生成 uuid VARCHAR2(40)
sysdate -- 当前时间 数据库
distinct -- 去重
SUBSTR('123456789', 1, 2) --字符串截取方法 结果 12 负数表示由后截取几位
|| -- 字符串拼接
DUAL --虚拟表
GROUP BY -- 分组
ORDER BY -- 排序
-- 对于结果进行转换显示
SELECT CASE 字段1
WHEN '字段1' THEN '字段1'
ELSE '字段2'
END 别名
from 表;
-- 对于结果进行转换显示
SELECT
DECODE(字段1, '字段1', '字段1', '字段2') 别名
from 表;
-- 时间格式化为字符串
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;
-- 字符串格式化为时间
select to_date('2024-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;
-- 返回的是自然周于本年的周数，一年周数最少不低于52
select to_char(SYSDATE,'iw') from dual;
--返回季度
select to_char(sysdate,'q') from dual;
--当前时间的年
select to_char(sysdate,'yyyy') from dual;
--当前时间的月
select to_char(sysdate,'mm') from dual;
--当前时间的日
select to_char(sysdate,'dd') from dual;
--当前时间的时
select to_char(sysdate,'hh24') from dual;
--当前时间的分
select to_char(sysdate,'mi') from dual;
--当前时间的秒
select to_char(sysdate,'ss') from dual;
用户相关
--建立临时表空间
CREATE TEMPORARY TABLESPACE PUBLIC_HMIS_TEMP --创建名为PUBLIC_HMIS_TEMP的临时表空间
TEMPFILE 'F:\oracle_kongjian\PUBLIC_HMIS_TEMP.DBF' -- 临时文件
SIZE 50M -- 其初始大小为50M
AUTOEXTEND ON -- 支持自动扩展
NEXT 50M MAXSIZE 20480M -- 每次增量为50M ,最大2048M
EXTENT MANAGEMENT LOCAL;
--建立表空间
CREATE TABLESPACE PUBLIC_HMIS_NAME --创建名为PUBLIC_HMIS_NAME的表空间
LOGGING
DATAFILE 'F:\oracle_kongjian\PUBLIC_HMIS_NAME.DBF'
SIZE 50M -- 其初始大小为50M
AUTOEXTEND ON -- 支持自动扩展
NEXT 50M MAXSIZE 20480M -- 每次增量为50M ,最大2048M
EXTENT MANAGEMENT LOCAL;
-- 查看表空间
SELECT *
FROM DBA_TABLESPACES
WHERE tablespace_name IN ('PUBLIC_HMIS_TEMP','PUBLIC_HMIS_NAME');
-- 删除表空间（不删除文件）
drop tablespace PUBLIC_HMIS_NAME;
-- 创建用户并指定表空间
create user PUBLIC_HMIS identified by PUBLIC_HMIS1 default tablespace PUBLIC_HMIS_NAME temporary tablespace PUBLIC_HMIS_TEMP ACCOUNT UNLOCK;
-- 给用户权限
grant connect,resource,DBA TO PUBLIC_HMIS;
-- 删除用户及表
drop user PUBLIC_HMIS cascade
我写的第一个存储过程
CREATE OR REPLACE PROCEDURE MYDEMO02(NY IN VARCHAR2)
IS
BEGIN
-- 先删除表中旧数据
DELETE FROM SA_OP_YYWX_BASE1 WHERE REPORT_NY = SUBSTR(NY, 1, 4) || '-' || SUBSTR(NY, -2, 2);
-- 将海口机辆轮渡段的数据修改为株洲车辆段，便于数据计算入库
UPDATE M_LABOR_CB2_INFO A SET A.CLDDM = 'G03', A.CLDMC='株洲车辆段' WHERE A.CLDDM = 'QE7' AND A.FQBS = NY;
UPDATE M_LABOR_CB21_INFO A SET A.CLDDM = 'G03', A.CLDMC='株洲车辆段' WHERE A.CLDDM = 'QE7' AND A.FQBS = NY;
UPDATE M_LABOR_CB3_INFO_NEW A SET A.CLDDM = 'G03', A.CLDMC='株洲车辆段' WHERE A.CLDDM = 'QE7' AND A.FQBS = NY;
INSERT INTO SA_OP_YYWX_BASE1 (REPORT_RQ, STATION_ID, LJ_SF, LJ_ZZ, LJ_DD, LJ_GJZJSJJ, LJ_JSJJ, LJ_FCBY, LJ_JLFLJ,
LJ_TSWZ, TFDS_RJFG, TFDS_TGZY, DT_TADS, DT_TPDS, DT_THDS, INSERT_TIME, REPORT_NY)
SELECT (SUBSTR(G.FQBS, 1, 4) || '-' || TO_CHAR(TO_DATE(G.FQBS, 'YYYYMM'), 'Q')) AS REPORT_RQ,
D.STATION_ID,
D.LJ_SF,
D.LJ_ZZ,
D.LJ_DD,
F.GJZCL,
F.JSCL,
F.FCJBJCCL,
F.JLJCFLJCL,
F.TSWZLCJXCL,
E.TFDS_RJFG,
E.TFDS_TGZY,
E.DT_TADS,
E.DT_TPDS,
E.DT_THDS,
(SYSDATE) AS INSERT_TIME,
(SUBSTR(G.FQBS, 1, 4) || '-' || SUBSTR(G.FQBS, -2, 2)) AS FQBS
FROM (SELECT CASE A.CLDMC
WHEN '包西车辆段' THEN 'E03'
ELSE (SELECT STATION_ID FROM MF_STATION T WHERE T.STATION_NAME = A.CLDMC)
END STATION_ID,
A.CLDMC,
((SUM(A.DDRGGT) - SUM(A.DDRGTH) - SUM(A.DDRGDQ) - SUM(A.DDRGJG) - SUM(A.DDRGHJGS)) +
(SUM(A.DDRJGT) - SUM(A.DDRJTH) - SUM(A.DDRJDQ) - SUM(A.DDRJJG) - SUM(A.DDRJHJGS))) AS LJ_DD,
(SUM(A.SFGT) - SUM(A.SFTH) - SUM(A.SFDQ) - SUM(A.SFJG) - SUM(A.SFHJGS)) AS LJ_SF,
((SUM(A.ZZRGGT) - SUM(A.ZZRGTH) - SUM(A.ZZRGDQ) - SUM(A.ZZRGJG) - SUM(A.ZZRGHJGS)) +
(SUM(A.ZZRJGT) - SUM(A.ZZRJTH) - SUM(A.ZZRJDQ) - SUM(A.ZZRJJG) - SUM(A.ZZRJHJGS))) AS LJ_ZZ
FROM M_LABOR_CB2_INFO A
WHERE A.FQBS = NY
AND A.CLDMC IN (SELECT STATION_NAME
FROM MF_STATION T
WHERE T.STATION_TYPE_ID = 'D'
UNION
SELECT '包西车辆段' AS STATION_NAME
FROM DUAL)
GROUP BY A.CLDMC
ORDER BY A.CLDMC) D,
(SELECT C.CLDMC, C.TFDS_RJFG, C.DT_THDS, C.DT_TPDS, C.DT_TADS, C.TFDS_TGZY
FROM (
SELECT A.CLDMC,
(SUM(A.TFRJGT) - SUM(A.TFRJTH) - SUM(A.TFRJDQ) - SUM(A.TFRJJG) -
SUM(A.TFRJHJGS)) AS TFDS_RJFG,
(SUM(A.TFDTGT) - SUM(A.TFDTTH) - SUM(A.TFDTDQ) - SUM(A.TFDTJG) -
SUM(A.TFDTHJGS)) AS TFDS_TGZY,
(SUM(A.THGT) - SUM(A.THTH) - SUM(A.THDQ) - SUM(A.THJG) - SUM(A.THHJGS)) AS DT_THDS,
(SUM(A.TPGT) - SUM(A.TPTH) - SUM(A.TPDQ) - SUM(A.TPJG) - SUM(A.TPHJGS)) AS DT_TPDS,
(SUM(A.TAGT) - SUM(A.TATH) - SUM(A.TADQ) - SUM(A.TAJG) - SUM(A.TAHJGS)) AS DT_TADS
FROM M_LABOR_CB21_INFO A
WHERE A.FQBS = NY
AND A.CLDMC IN (SELECT STATION_NAME
FROM MF_STATION T
WHERE T.STATION_TYPE_ID = 'D'
UNION
SELECT '包西车辆段' AS STATION_NAME
FROM DUAL)
GROUP BY A.CLDMC
ORDER BY A.CLDMC
) C) E,
(SELECT A.CLDMC,
SUM(A.JSCL) AS JSCL,
SUM(A.GJZCL) AS GJZCL,
SUM(A.ZBCL) AS ZBCL,
SUM(A.FCJBCL) AS FCJBJCCL,
SUM(A.TSWZLCJXCL) AS TSWZLCJXCL,
SUM(A.JTYCL) AS JLJCFLJCL
FROM M_LABOR_CB3_INFO_NEW A
WHERE A.FQBS = NY
AND A.CLDMC IN (SELECT STATION_NAME
FROM MF_STATION T
WHERE T.STATION_TYPE_ID = 'D'
UNION
SELECT '包西车辆段' AS STATION_NAME
FROM DUAL)
GROUP BY A.CLDMC
ORDER BY A.CLDMC) F,
(SELECT NY AS FQBS FROM DUAL) G
WHERE D.CLDMC = E.CLDMC
AND E.CLDMC = F.CLDMC;
-- 将包西车辆段数据修改回海口车辆段
UPDATE M_LABOR_CB3_INFO_NEW A SET A.CLDDM = 'QE7', A.CLDMC='海口机辆轮渡段' WHERE A.CLDDM = 'G03' AND A.FQBS = NY;
UPDATE M_LABOR_CB2_INFO A SET A.CLDDM = 'QE7', A.CLDMC='海口机辆轮渡段' WHERE A.CLDDM = 'G03' AND A.FQBS = NY;
UPDATE M_LABOR_CB21_INFO A SET A.CLDDM = 'QE7', A.CLDMC='海口机辆轮渡段' WHERE A.CLDDM = 'G03' AND A.FQBS = NY;
COMMIT;
END;
If you find the article useful, feel free to appreciate
Comments