欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Oracle SQL 语句收集

程序员文章站 2022-07-14 12:47:14
...
--返回当前日期年度的第一天

select trunc(sysdate,'year') from dual;


--返回当前日期月份的第一天

select trunc(sysdate,'month') from dual;


--上月最后一天

select last_day(add_months(sysdate, -1)) from dual;


--给定日期后最近星期几的日期

select next_day(to_date('2009-12-01', 'yyyy-mm-dd'), '星期一') next_day from dual;


--查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)


--删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId)>1)


--查找表中多余的重复记录(多个字段)
select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)


--删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)


--得到当前时间的前一分钟
select to_char(Sysdate-1/21/60,'HH24:MI:SS')from dual; 


--得到当前时间的后一分钟
select to_char(Sysdate+1/21/60,'HH24:MI:SS')from dual; 


--得到当前时间的前一小时
select to_char(Sysdate-1/24,'HH24:MI:SS')from dual; 


--得到当前时间的后一小时
select to_char(Sysdate+1/24,'HH24:MI:SS')from dual; 


--得到当前时间的后一天
select to_char(Sysdate+1,'YYYY-MM-DD')from dual; 


--列出本月所有日期
SELECT TO_CHAR(sysdate,'yyyyMM')
    ||lpad(level,2,'0') ymd
  FROM dual
    CONNECT BY 1=1
  AND level    <= to_number(TO_CHAR(last_day(sysdate),'dd'))


--得到当前日期的Julian Date(即数字)
select to_char(to_date('2011-1-1','yyyy-MM-dd'),'J') from dual;


--判断当前日期是否为周末
DECLARE
  l_day  VARCHAR2(10);
BEGIN
  l_day := TRIM(TO_CHAR(SYSDATE, 'DAY'));

  IF l_day IN ('SATURDAY', 'SUNDAY') THEN
    DBMS_OUTPUT.put_line('It''s the weekend!');
  ELSE
    DBMS_OUTPUT.put_line('It''s not the weekend yet!');
  END IF;
END;


--根据出生年月日获得年龄
select trunc(months_between(sysdate,to_date('1983-01-30','yyyy-MM-dd'))/12) from dual;


--查看正在运行的sql
SELECT sess.program,
  sess.sid,
  sess.serial#,
  sess.username,
  sess.osuser,
  p.spid OSProcessID,
  sa.sql_text
FROM v$session sess,
  v$process p,
  v$sqlarea sa
WHERE sess.paddr=p.addr AND sess.sql_hash_value=sa.hash_value AND sess.username IS NOT NULL and sess.sql_id is not null  order by username,sid;


--查看各个表空间的*空间
select tablespace_name,sum(bytes)/1024/1024 free_Mbytes
from dba_free_space
GROUP BY tablespace_name
order by free_Mbytes


--查看表所处的表空间
SELECT TABLE_NAME,TABLESPACE_NAME FROM all_tables WHERE table_name = 'TEST';


--逻辑快速删除表中的字段
ALTER TABLE TEST SET UNUSED (COLUMN1,COLUMN2,COLUMN3);
alter table TEST  drop unused columns checkpoint 1000;


--查看共享池中缓存的SQL
SELECT SQL_TEXT,HASH_VALUE,PARSE_CALLS FROM V$SQLAREA WHERE SQL_TEXT LIKE 'INSERT INTO PRE_TEST_TABLE VALUES%';


SELECT    
  W_OPTY_DS.PAR_INTEGRATION_ID,
  W_OPTY_DS.ACCNT_ST_ADDRESS,  
  W_OPTY_DS.ACCNT_ZIPCODE,  
  CASE WHEN G11.VAL IS NULL THEN W_OPTY_DS.APPL_OWNER_TYPE ELSE G11.VAL END AS APPL_OWNER_TYPE, 
  CASE WHEN G11.NAME IS NULL THEN W_OPTY_DS.APPL_OWNER_TYPE ELSE G11.NAME END AS APPL_OWNER_TYPE_I,
  W_OPTY_DS.CURR_SSTAGE_ID,  
  CASE WHEN G6.VAL IS NULL THEN W_OPTY_DS.DEAL_HORIZON ELSE G6.VAL END AS DEAL_HORIZON,  
  CASE WHEN G6.NAME IS NULL THEN W_OPTY_DS.DEAL_HORIZON ELSE G6.NAME END AS DEAL_HORIZON_I,  
  CASE WHEN G5.VAL IS NULL THEN W_OPTY_DS.DECISION_LVL ELSE G5.VAL END AS DECISION_LVL,  
  CASE WHEN G5.NAME IS NULL THEN W_OPTY_DS.DECISION_LVL ELSE G5.NAME END AS DECISION_LVL_I,  
  W_OPTY_DS.DESC_TEXT,  
  W_OPTY_DS.EXEC_PRIORITY_FLG,  
  W_OPTY_DS.EXIST_CUST_FLG,  
  W_OPTY_DS.FCST_CLS_DT,  
  CASE WHEN G2.VAL IS NULL THEN W_OPTY_DS.LEAD_QUALITY_CD ELSE G2.VAL END AS LEAD_QUALITY_CD,
  CASE WHEN G2.NAME IS NULL THEN W_OPTY_DS.LEAD_QUALITY_CD ELSE G2.NAME END AS LEAD_QUALITY_CD_I,
  W_OPTY_DS.MAX_DAYS_IN_STG,  
  W_OPTY_DS.METHOD_ID,  
  MD.ROW_WID AS MINI_WID,  
  W_OPTY_DS.REVN_PROB,  
  CASE WHEN G7.VAL IS NULL THEN W_OPTY_DS.RISK ELSE G7.VAL END AS RISK,  
  CASE WHEN G7.NAME IS NULL THEN W_OPTY_DS.RISK ELSE G7.NAME END AS RISK_I,  
  CASE WHEN G13.VAL IS NULL THEN W_OPTY_DS.WIN_PROB_MODE ELSE G13.VAL END AS WIN_PROB_MODE,
  CASE WHEN G13.NAME IS NULL THEN W_OPTY_DS.WIN_PROB_MODE ELSE G13.NAME END AS WIN_PROB_MODE_I,
W_OPTY_DS.X_REL_TIER
FROM    W_OPTY_DS
LEFT OUTER JOIN W_LOV_G G1 ON W_OPTY_DS.STG_STATUS_CD = G1.NAMEVAL AND G1.R_TYPE = 'SALES_STAGE_STATUS' 
LEFT OUTER JOIN W_LOV_G G2 ON W_OPTY_DS.LEAD_QUALITY_CD = G2.NAMEVAL AND G2.R_TYPE = 'LEAD_QUALITY' 
LEFT OUTER JOIN W_LOV_G G3 ON W_OPTY_DS.REASON_WL_CD = G3.NAMEVAL AND G3.R_TYPE = 'REASON_WON_LOST' 
LEFT OUTER JOIN W_LOV_G G4 ON W_OPTY_DS.CHANNEL_TYPE = G4.NAMEVAL AND G4.R_TYPE = 'OPTY_CHANNEL_TYPE' 
LEFT OUTER JOIN W_LOV_G G5 ON W_OPTY_DS.DECISION_LVL  = G5.NAMEVAL AND G5.R_TYPE = 'DECISION_LVL' 
LEFT OUTER JOIN W_LOV_G G6 ON W_OPTY_DS.DEAL_HORIZON = G6.NAMEVAL AND G6.R_TYPE = 'DEAL_HORIZON' 
LEFT OUTER JOIN W_LOV_G G7 ON W_OPTY_DS.RISK = G7.NAMEVAL AND G7.R_TYPE = 'RISK' 
LEFT OUTER JOIN W_LOV_G G8 ON W_OPTY_DS.STRATEGIC = G8.NAMEVAL AND G8.R_TYPE = 'STRATEGIC' 
LEFT OUTER JOIN W_LOV_G G9 ON W_OPTY_DS.REVENUE_CLASS = G9.NAMEVAL AND G9.R_TYPE = 'REVENUE_CLASS' 
LEFT OUTER JOIN W_LOV_G G10 ON W_OPTY_DS.OPTY_STATUS_CD = G10.NAMEVAL AND G10.R_TYPE = 'OPTY_STATUS' 
LEFT OUTER JOIN W_LOV_G G11 ON W_OPTY_DS.APPL_OWNER_TYPE = G11.NAMEVAL AND G11.R_TYPE = 'FINS_APPL_OWNERSHIP_TYPE_MLOV' 
LEFT OUTER JOIN W_LOV_G G12 ON W_OPTY_DS.OPTY_TYPE = G12.NAMEVAL AND G12.R_TYPE = 'REVENUE_TYPE' 
LEFT OUTER JOIN W_LOV_G G13 ON W_OPTY_DS.WIN_PROB_MODE = G13.NAMEVAL AND G13.R_TYPE = 'SALES_METHOD_MODE_TYPE' 

INNER JOIN W_OPTY_MD MD ON 
(CASE WHEN G1.NAME IS NULL   THEN W_OPTY_DS.STG_STATUS_CD ELSE G1.NAME END) = MD.STG_STATUS_I AND 
(CASE WHEN G2.NAME IS NULL   THEN W_OPTY_DS.LEAD_QUALITY_CD ELSE G2.NAME END) = MD.LEAD_QUALITY_I AND 
(CASE WHEN G3.NAME IS NULL   THEN W_OPTY_DS.REASON_WL_CD ELSE G3.NAME END) = MD.REASON_WL_I AND 
(CASE WHEN G4.NAME IS NULL   THEN W_OPTY_DS.CHANNEL_TYPE ELSE G4.NAME END) = MD.CHANNEL_TYPE_I AND 
(CASE WHEN G5.NAME IS NULL   THEN W_OPTY_DS.DECISION_LVL ELSE G5.NAME END) = MD.DECISION_LVL_I AND 
(CASE WHEN G6.NAME IS NULL   THEN W_OPTY_DS.DEAL_HORIZON ELSE G6.NAME END) = MD.DEAL_HORIZON_I AND 
(CASE WHEN G7.NAME IS NULL   THEN W_OPTY_DS.RISK ELSE G7.NAME END) = MD.RISK_I AND 
(CASE WHEN G8.NAME IS NULL   THEN W_OPTY_DS.STRATEGIC ELSE G8.NAME END) = MD.STRATEGIC_I AND 
(CASE WHEN G9.NAME IS NULL   THEN W_OPTY_DS.REVENUE_CLASS ELSE G9.NAME END) = MD.REVENUE_CLASS_I AND 
(CASE WHEN G10.NAME IS NULL THEN W_OPTY_DS.OPTY_STATUS_CD ELSE G10.NAME END) = MD.OPTY_STATUS_CD_I AND 
(W_OPTY_DS.OPTY_REVN_CAT_I) = MD.OPTY_REVN_CAT_I AND 
(W_OPTY_DS.CURR_SSTAGE) = MD.CURR_SSTAGE AND 
(W_OPTY_DS.CHAMPION_FLG) = MD.CHAMPION_FLG AND 
(W_OPTY_DS.BUDGETED_FLG) = MD.BUDGETED_FLG AND
(W_OPTY_DS.EXIST_CUST_FLG) = MD.EXIST_CUST_FLG AND 
W_OPTY_DS.COMMITTED_FLG = MD.COMMITTED_FLG; 


更改用户密码:
ALTER USER username IDENTIFIED BY new_pass replace old_pass;


查看数据库默认TimeZone:
select DBTIMEZONE from dual;


查看当前Session TimeZone:
select SESSIONTIMEZONE from dual;


赋予包的执行权限
grant execute on custprofile.CUST_INTERESTS_WS_PKG to mdm_xml_user;

相关标签: SQL Oracle

上一篇: Linux命令收集

下一篇: Vi命令