--一 日期计算和转换
--1.1 系统时间
SELECT
SYSDATE --2021-01-27 DATE
,SYSTIMESTAMP --2021-01-27 18:01:57 DATE
FROM DUAL;
--1.2 字符串和日期相互转换
SELECT
DATE '2020-12-31' --2020/12/31
,TO_DATE('2020-12-31','YYYY-MM-DD') --2020/12/31
,TO_DATE('20201231', 'YYYYMMDD') --2020/12/31
,TO_DATE('2020/12/31','YYYY/MM/DD') --2020/12/31
,TO_DATE('2020/12/31','YYYY-MM-DD') --2020/12/31 可以实现但不推荐
,TO_DATE('20201231','YYYY-MM-DD') --2020/12/31 可以实现但不推荐
,TO_DATE('20211231122559','YYYY-MM-DD HH24:MI:SS') --2021/12/31 12:25:59
,TO_DATE('2021-12-31 12-25-59','YYYY-MM-DD HH24:MI:SS') --2021/12/31 12:25:59
,TO_DATE('20211231 122559','YYYY-MM-DD HH24:MI:SS') --2021/12/31 12:25:59
,TO_CHAR(SYSDATE,'YYYY-MM-DD') --2022-02-07
,TO_CHAR(SYSDATE,'YYYYMMDD') --20220207
,TO_CHAR(SYSDATE,'YYYY/MM/DD') --2022/02/07
,TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') --2022-02-07 17:56:40
,TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS') --2022-02-07 17:56:40
FROM DUAL;
--1.3 获取当前日期月初、月末、年初、年末
SELECT
TRUNC(SYSDATE,'MM') --2022/2/1 月初
,LAST_DAY(SYSDATE) --2022/2/28 14:44:53 月末
,TRUNC(SYSDATE,'YYYY') --2022/1/1 年初
,TRUNC(SYSDATE,'YYYY')
+ NUMTOYMINTERVAL(1,'YEAR')
- NUMTODSINTERVAL(1,'DAY') --2022/1/31 年末
,TRUNC(SYSDATE,'YYYY')
+ NUMTOYMINTERVAL(1,'YEAR')
- 1 --2022/1/31 年末
FROM DUAL;
--1.4 截取年月日时分秒
SELECT
SYSTIMESTAMP --07-FEB-22 05.41.30.682394 PM +08:00
,SYSDATE --2022/2/7 17:41:30
,EXTRACT(YEAR FROM SYSDATE) --2022
,EXTRACT(MONTH FROM SYSDATE) --2
,EXTRACT(DAY FROM SYSDATE) --7
--,EXTRACT(HOUR FROM SYSTIMESTAMP) --9 有问题?
,EXTRACT(MINUTE FROM SYSTIMESTAMP) --41
,EXTRACT(SECOND FROM SYSTIMESTAMP) --30.682394
FROM DUAL;
--1.5 获取当前日期月份的年月日以及时分秒加减操作
SELECT
SYSDATE --2022/2/7 15:43:42
,SYSDATE + 1 --2022/2/8 15:43:42
,ADD_MONTHS(SYSDATE,+1) --2022/3/7 15:43:42
,SYSDATE + NUMTODSINTERVAL(4,'SECOND') --常用单位 DAY HOUR MINUTE SECOND
,SYSDATE + NUMTOYMINTERVAL(1,'YEAR') --常用单位 YEAR MONTH
--,TO_DATE('2022-01-29','YYYY-MM-DD')+ NUMTOYMINTERVAL(1,'MONTH')--常用单位 YEAR MONTH MONTH参数使用不智能,EG: '2022-01-29'加一个月报错,因为2022-02没有29号
FROM DUAL;
--1.6 格式化数字--补齐位数并转化成日期
select to_char(10030,'FM099999') --补齐位数
,to_char(90305,'FM099999')
,TO_DATE(to_char(90305,'FM099999'),'HH24MISS') --数字转日期
,to_char(TO_DATE(to_char(90305,'FM099999'),'HH24MISS'),'hh24"时"mi"分"ss"秒"') --日期转字符串
from dual
--1.7 造数据常用
select TRUNC(DBMS_RANDOM.VALUE * 10000) --截取成整数
,cast(DBMS_RANDOM.VALUE * 10 as number(10, 2)) --截取成小数
,DBMS_RANDOM.STRING('U', 6) --6个大写字母组成随机串
,cast(TO_CHAR(sysdate, 'YYYYMMDD') as number(10)) --rsdateotc 日期格式 日期转成数字
,TO_CHAR(TO_DATE(20230403, 'YYYY/MM/DD'), 'YYYY"年"MM"月"DD"日"') --数字改成日期
,decode(trunc(DBMS_RANDOM.VALUE(0, 2)), 0, '流通股', 1, '限售股') --随机出现指定字符串
,to_char(to_date(to_char(substr('2151723', 1, length('2151723') - 2), --从开始到截至到倒数第二位
'FM099999'), --格式化输出占位
'hh24miss'), --将时分秒数字转化成日期
'hh24"时"mi"分"ss"秒"') --将时分秒日期转化成自定义服装
,to_date(TRUNC(DBMS_RANDOM.VALUE(to_number(to_char(to_date('20230101','yyyymmdd'),
'J')
),
to_number(to_char(to_date('20230403','yyyymmdd') + 1,
'J')
)
)
),
'J')
+
DBMS_RANDOM.VALUE(1, 3600) / 3600 --获取任意时间范围的日期
,cast(TO_CHAR(to_date(TRUNC(DBMS_RANDOM.VALUE(to_number(to_char(to_date('20230101',
'yyyymmdd'),
'J')),
to_number(to_char(to_date('20230403',
'yyyymmdd') + 1,
'J')))),
'J'),
'YYYYMMDD') as number(10)) --获取任意日期的数字格式
from dual;
--二 自动循环存储
--2.1从现有表中查询"insert into "数据并循环输出
create or replace procedure zxb_data_get_insert_sql(v_sp_table_name varchar2,
v_event_id varchar2) as
v_1 varchar2(4000);
begin
for i in (select ' insert into RRL_RLSUB (SUB_ID,RULE_ID,RS_DESC,CAL_TYPE,MATCHER_ID,SUB_INDEX,REC_GEN_TIME,REC_UPD_TIME,
RS_NAME,GEN_MODULE_ID,UPD_MODULE_ID,GEN_USER_ID,UPD_USER_ID) values (''' ||
"SUB_ID" || ''',''' || "RULE_ID" || ''',''' || "RS_DESC" ||
''',''' || "CAL_TYPE" || ''',''' || "MATCHER_ID" ||
''',''' || "SUB_INDEX" || ''',' || 'sysdate' || ',' ||
'sysdate' || ',''' || "RS_NAME" || ''',''' ||
"GEN_MODULE_ID" || ''',''' || "UPD_MODULE_ID" || ''',''' ||
"GEN_USER_ID" || ''',''' || "UPD_USER_ID" || ''')' v_1
from RRL_RLSUB
where RULE_ID in (SELECT to_number(regexp_substr(v_event_id,'[^,]+',1,ROWNUM)) FROM dual
CONNECT BY ROWNUM <= LENGTH(v_event_id) - LENGTH(REPLACE(v_event_id,',', ''))+1 )
order by RULE_ID)
loop
dbms_output.put_line(i.v_1 || ';');
end loop;
end;
--2.2游标取数
declare
cursor cur_emp is
select * from emp
where deptno=30; --检索部门编号为30的雇员信息
begin
for emp_record in cur_emp --遍历雇员信息
loop
dbms_output.put('雇员编号:'||emp_record.empno); --输出雇员编号
dbms_output.put(';雇员名称:'||emp_record.ename); --输出雇员名称
dbms_output.put_line(';雇员工资:'||emp_record.sal); --输出雇员工资
end loop;
end;
--2.3 for循环游标取数
begin
for emp_record in (select empno,ename,sal from emp where job='SALESMAN')
loop
dbms_output.put('雇员编号:'||emp_record.empno); --输出雇员编号
dbms_output.put(';雇员名称:'||emp_record.ename); --输出雇员名称
dbms_output.put_line(';雇员工资:'||emp_record.sal); --输出雇员工资
end loop;
end;
--2.4 批量向表中添加数据(测试样例)
declare
v_begin number;
v_end number;
begin
v_end := 100;
v_begin := 0;
while v_begin < v_end loop
INSERT INTO ZT_T_ZB_TGQY_DBTY
(SEQ_NO, FUND_ID)
select SEQ_ZT_T_ZB_TGQY_DBTY.NEXTVAL,
'2013955' || trunc(DBMS_RANDOM.value * 10000) --资金账号
from dual;
commit;
v_begin := v_begin + 1;
end loop;
end;
--三 存储中常用语法语句
--将oracle上的日期取成正常日期(t.rcv_date为数字类型;eg:11245265)
t.rcv_date + to_date('18991231','yyyymmdd')
--保留小数位两位和四位的方式
select to_char('0.031565','FM90D99') -- 0.03
,round('0.031565',4) --0.0316
,to_char(45456445.4564,'fm99,999,999,999,990.00')--45,456,445.46 '9,0'-表示占位符,'9'-该位置无数据时不显示;'0'-该位置无数据时显示为0
from dual;
--查看有限条数的sql语句
select * from CPDDP_PDATA.CTB_APMA t where rownum < 100;
--case when 写法常见错误
case when 中存在多个值要保证多个值得类型一致:例如:1 和 '1'
--开窗函数的使用,注意要加表的别名a
select ROW_NUMBER() OVER( ORDER BY a.txdate DESC) t,a.* from t4_kjzf_ewm_org_1_1_txdate a;
--正则regexp_like用法 '+'-表示前面的模式必须出现至少一次或更多次
regexp_like('尼玛顿珠', '.([a-z]+|[A-Z]+|[0-9])') then '尼玛顿珠'
--补全函数-根据指定长度和字符
rpad(substr('尼玛顿珠', 1, 1), length('尼玛顿珠'), '*') || substr('尼玛顿珠', -1, 1) --保留指定字符的第一位和最后一位,中间用“*”代替
--手动调用有入参和出参的存储过程
declare
biz_date varchar2(4000);
in_date varchar2(4000);
out_code varchar2(4000);
out_msg varchar2(4000);
begin
PRO_ZT_T_ZB_CONTRACT_NEW(BIZ_DATE ,IN_DATE ,OUT_CODE , OUT_MSG );
end;
--四 DDL、DML语句
--建表+注释
create table TMSD_SHZC_SHJY(
pos_dqdm VARCHAR2(500),
txdate CHAR(10));
comment on table TMSD_SHZC_SHJY is '收单商户资产及交易情况表';
comment on column TMSD_SHZC_SHJY.pos_dqdm is '地市';
--oracle增删改查
--增加字段
alter table TMSD_SHZC_SHJY add (TEST_D VARCHAR2(100) NOT NULL ,TEST_B NUMBER ,TEST_C VARCHAR2(100) NULL );
alter table TMSD_SHZC_SHJY add TEST_D varchar2(20);
--修改字段
alter table TMSD_SHZC_SHJY MODIFY (TEST_D NUMBER NULL ,TEST_B NUMBER NOT NULL ,TEST_C NUMBER );
alter table TMSD_SHZC_SHJY modify TEST_D varchar2(10);
--删除字段
alter table TMSD_SHZC_SHJY drop (TEST_D ,TEST_B ,TEST_C );
--添加唯一约束
alter table ZT_T_ZB_GGT_QZGSSG add constraint IDX_ZT_T_ZB_GGT_QZGSSG_test unique (CLIENT_ID, BRANCH_NO);
--删除唯一约束(修改约束,先删除再重建)
alter table ZT_T_ZB_GGT_QZGSSG drop constraint IDX_ZT_T_ZB_GGT_QZGSSG;
--赋权
grant execute on 存储 to 用户名;
grant all on table_name to 用户名;
--同义词
create synonym JD_ALL_H_LOGASSET for MOT_BASE.JD_ALL_H_LOGASSET;
--序列号
-- Create sequence
create sequence SEQ_ZT_T_ZB_BZQSYLCBTX
minvalue 1000
maxvalue 999999999999999999
start with 1301
increment by 1
cache 20;
--dblink相关
drop database link DBLINK_DEV;
-- eg:Create database link
create database link DBLINK_DEV
connect to DEV
identified by Js#dev_3604
using '21.144.56.183:1521/cpddpdb';
--五 优化,排错
--提高并行度,其中‘a’表示所查表的别名
/*+parallel(a,100) full(a)*/ /*+ parallel(16) */ /*+parallel(hh,8)(aa,8)*/
--误删回滚语句
flashback table T2_IND_FOR_CURR_DEBT_bak to before drop;
--高水位(越高越差)
select table_name ,(u.BLOCKS*8192/1024/1024)-(u.num_rows*u.AVG_ROW_LEN/1024/1024) from user_tables u;
--整理碎片
exec dbms_stats.gather_table_stats('MPM_GZ','TB_BATCH_TRAN_DTL'); --或者删掉备份重建
--六 进程,空间相关
--事务信息视图
select addr,xidusn,xidslot from v$transaction;
--查看被锁的表(锁对象信息表)
select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id;
--查看那个用户那个进程照成死锁
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;
--查看连接的进程
SELECT sid, serial#, username, osuser FROM v$session;
--进程的用户信息
SELECT SPID, OSUSER, S.PROGRAM
FROM V$SESSION S, V$PROCESS P
WHERE S.PADDR = P.ADDR AND S.SID = 561;
-- 杀掉对应进程
alter system kill session '561,1'; --其中1025为sid,41为serial#.
/* ps -ef | grep ora
kill -9 12231 */
COMMIT;
--查看正在执行的job
select * from dba_jobs_running
--回滚段正在处理的事务
select a.name,b.xacts,c.sid,c.serial#,d.sql_text
from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e
where a.usn=b.usn and b.usn=e.xidusn and c.taddr=e.addr
and c.sql_address=d.address and c.sql_hash_value=d.hash_value order by a.name,c.sid,d.piece;
-- 产看编码格式
SELECT USERENV('LANGUAGE') FROM DUAL;
--查看数据库的空间占用
SELECT Upper(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
|| '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
-- 查看表空间大小
SELECT OWNER,SEGMENT_NAME,partition_name ,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024/1024 SIZES
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'TBS_XMKF'
order by sizes DESC;
-- 不同用户中查询表在哪个存储过程使用
SELECT * FROM USER_SOURCE A WHERE UPPER(TEXT) LIKE '%TEMP_AMT_FIXLOSS_CUST%'
--七 创建分区表及使用
--创建LIST分区表
DROP TABLE T_ZP_TB_BUSINESS_TEST;
CREATE TABLE T_ZP_TB_BUSINESS_TEST
(
bankid Varchar2(14)
,cardno Varchar2(30)
,cnname Varchar2(50)
,didno Varchar2(50)
,updatephone Varchar2(20)
,updatedate Varchar2(10)
,bds_etl_job_dt DATE NOT NULL
)
PARTITION BY LIST(bds_etl_job_dt)
(PARTITION PART_T1 VALUES (TO_DATE('2000-01-01','YYYY-MM-DD')));
--创建RANGE自动分区表
DROP TABLE T_ZP_TB_BUSINESS_TEST;
CREATE TABLE T_ZP_TB_BUSINESS_TEST
(
bankid Varchar2(14)
,cardno Varchar2(30)
,cnname Varchar2(50)
,didno Varchar2(50)
,updatephone Varchar2(20)
,updatedate Varchar2(10)
,bds_etl_job_dt DATE NOT NULL
)
PARTITIONlTITION BY RANGE(bds_etl_job_dt) INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION PART_T1 VALUES LESS THAN(TO_DATE('2021-12-01','YYYY-MM-DD')))
-- PARTITION BY RANGE(bds_etl_job_dt) INTERVAL(NUMTODSINTERVAL(1,'DAY'))
-- (PARTITION PART_T1 VALUES LESS THAN(TO_DATE('2021-12-01','YYYY-MM-DD')))
;
comment on table T_ZP_TB_BUSINESS_TEST IS '苏州人行_手机号变更';
-- Add comments to the columns
comment on column T_ZP_TB_BUSINESS_TEST.bankid IS '所属银行机构号';
comment on column T_ZP_TB_BUSINESS_TEST.cardno IS '卡号' ;
comment on column T_ZP_TB_BUSINESS_TEST.cnname IS '姓名' ;
comment on column T_ZP_TB_BUSINESS_TEST.didno IS '身份证' ;
comment on column T_ZP_TB_BUSINESS_TEST.updatephone IS '变更手机号' ;
comment on column T_ZP_TB_BUSINESS_TEST.updatedate IS '变更日期' ;
comment on column T_ZP_TB_BUSINESS_TEST.bds_etl_job_dt IS '数据日期' ;
--查看表内的分区情况
SELECT * FROM USER_TAB_PARTITIONS T WHERE T.TABLE_NAME = 'T_ZP_TB_BUSINESS_TEST' ;
--查询分区表中的数据
SELECT * FROM T_ZP_TB_BUSINESS_TEST;
SELECT * FROM T_ZP_TB_BUSINESS_TEST PARTITION (P202111);
SELECT * FROM T_ZP_TB_BUSINESS_TEST PARTITION (SYS_P27);
SELECT * FROM T_ZP_TB_BUSINESS_TEST PARTITION (SYS_P25);
SELECT distinct t.bds_etl_job_dt FROM TB_BUSINESS_TRADEINFO PARTITION (SYS_P67) t;
SELECT * FROM T_ZP_TB_BUSINESS_TEST t where t.bds_etl_job_dt = date'2021-11-01';
--向结果表中插入数据
INSERT INTO T_ZP_TB_BUSINESS_TEST
SELECT '','','','','','', to_date('2021-11-01','YYYY-MM-DD') FROM DUAL;
COMMIT;
INSERT INTO T_ZP_TB_BUSINESS_TEST
SELECT '','','','','','', to_date('2021-12-01','YYYY-MM-DD') FROM DUAL;
COMMIT;
INSERT INTO T_ZP_TB_BUSINESS_TEST
SELECT '','','','','','', to_date('2022-01-01','YYYY-MM-DD') FROM DUAL;
COMMIT;
INSERT INTO T_ZP_TB_BUSINESS_TEST
SELECT '','','','','','', to_date('2022-02-01','YYYY-MM-DD') FROM DUAL;
COMMIT;
--插入分区数据前先创建,增加分区的语句
alter table czjz_file_exec_log add partition CZJZ_FILE_EXEC_LOG_20220216 values ('2022-02-16');
-----------------------------------------------------常见问题----------------------------------
-- left join on 以及 where的使用
left join A on B where C ;执行顺序是 A->B->C
1.1如果不考虑空值以外的情况,直接使用join 直接去关联,条件放在on还是where后结果一样;
1.2 left join充分匹配on后不加条件
使用left join;如果存在空值,限制条件放on后不影响总条数,限制条件放where后影响总条数
使用left join;如果想取空值和限制条件的数据,需要先'充分匹配'然后使用()将右表的条件用or条件连接
例如:
drop table CDP_T_TMSD_POS_sd1_1;
create table CDP_T_TMSD_POS_sd1_1 as
select * from
(select t1.*,t2.bla_avg
from CDP_T_TMSD_POS_sd1_20201226 t1
left join CDP_T_TMSD_POS_sd1_1_1 t2 on (t1.CARD_NO = t2.account)
where t1.pos_jszhlx = '1' -- 限制账号为对公账户
)
where bla_avg = 0 or bla_avg is null;
补充:
1.2.1 如果条件字段是查询的字段,则不能再on上加条件;
如果条件字段不是查询的字段,则既可以on上加条件,也可以在where后面加,不过应该考虑总条数的变化;
eg:
drop table CDP_T_TMSD_POS_sd1_2_1;
create table CDP_T_TMSD_POS_sd1_2_1 as
select distinct
a.cust_iden_no,
sum(d.mon_daily_avg_bal_amt) mon_amt
from CDP_T_TMSD_POS_sd001 a
left join t98_indpty_prod_stat d on a.conform_indparty_id = d.conform_indparty_id
left join T04_SYS_ORGANIZATION c on (d.STAT_ORG_ID = c.sys_org_id and C.SYS_ID = 'S01') --and c.agent_ind = '1'
where a.pos_jszhlx = '0' -- 个人客户账户类型的限制
group by a.cust_iden_no;
ORACLE经验汇总
于 2022-03-04 23:59:08 首次发布