Oracle常用语句及函数

----建表语句
create table EACH_POST_GRADE_WAGE_TOTAL(
        ID number(16),        
        UNITNAME varchar2(255),  
        CODE varchar2(255),           
        POST varchar2(255),  
        A210001 varchar2(25),        
        PEOPLES VARCHAR2(255) DEFAULT '0',
        A210052 VARCHAR2(255) DEFAULT '0',
        A210005 VARCHAR2(255) DEFAULT '0',
        TGJT VARCHAR2(255) DEFAULT '0',
        JXGZHJ VARCHAR2(255) DEFAULT '0',
        A210017 VARCHAR2(255) DEFAULT '0',
        A210019 VARCHAR2(255) DEFAULT '0',
        A210021 VARCHAR2(255) DEFAULT '0',
        AVG_SALARY VARCHAR2(255) DEFAULT '0'
    );
---设置主键    
ALTER TABLE "ZJRSGZ"."EACH_POST_GRADE_WAGE_TOTAL" ADD PRIMARY KEY ("ID");

-----oracle设置id自增
create sequence seq_each_post_grade_wage_total
 increment by 1
 start with 1
 maxvalue 999999999999999999
 minvalue 1;

 -----创建索引-------------------------
create index IN_EH_POST_GDE_WE_TOTAL on EACH_POST_GRADE_WAGE_TOTAL (CODE);

----加注释
comment on column EACH_POST_GRADE_WAGE_TOTAL.POST is '岗位等级';

----- 修改数据类型
alter table tableName modify (cloumnName 数据类型); 
例：alter table EACH_POST_GRADE_WAGE_TOTAL modify PEOPLES VARCHAR2(255) DEFAULT '0';

-----替换，修改字段名
alter table tableName rename column oldCName to newCName; -- 修改字段名

alter table EACH_POST_GRADE_WAGE_TOTAL rename column CODE to UNITNAME;

----------------新字段add
ALTER TABLE REPORT_INFO_PDF ADD (
            iswarehousing number(1) DEFAULT 0 null
        );
        
ALTER TABLE REPORT_INFO_PDF ADD (
  tablename varchar2(255)
);
---删字段
alter table EACH_POST_GRADE_WAGE_TOTAL drop column CODE;

 -------------查表索引-----------------------------
select * from user_ind_columns where TABLE_NAME='A210';

--------------查看数据库锁表、释放死锁、查运行SQL
select INST_ID,
       SID,
       SERIAL#,
             osuser,
       username,
       MACHINE,
       PROGRAM,
       sql_id,
       wait_class,
       event,
       BLOCKING_SESSION
  from gv$session
 where wait_class <> 'Idle'
 order by username;

select SID, SERIAL#, STATUS, LOCKWAIT, PROCESS, PROGRAM, PREV_SQL_ID
  from gv$session where SID = 1610;
    
select sql_text from gv$sql where sql_id = 'dqzv0wtc4kxv3';

---释放
alter system kill session '3199,3467';

alter system kill session 'SID','SERIAL#';

-----查SQL----------------------------------------

select sql_text from gv$sql where sql_id in (
select PREV_SQL_ID
  from gv$session where SID in (select SID
  from gv$session
 where wait_class <> 'Idle')
)

select sql_text from gv$sql where sql_id in (
select PREV_SQL_ID
  from gv$session where SID in (select SID
  from gv$session
 where wait_class <> 'Idle')
)

-------常用函数部分-----------------
months_between()两个时间月份差
select months_between(to_date('2004-05-03','yyyy-mm-dd'),to_date('2004-04-20','yyyy-mm-dd')) from dual;

-------每月最后一天-----
SELECT last_day(ADD_MONTHS(TO_DATE(TO_CHAR(sysdate, 'yyyy') || '-01','yyyy-MM'),ROWNUM - 1)) as year_month
FROM DUAL
CONNECT BY ROWNUM <=months_between(TO_DATE(TO_CHAR(sysdate, 'yyyy-MM'), 'yyyy-MM'),TO_DATE(TO_CHAR(sysdate, 'yyyy') || '-01','yyyy-MM'))
-------
wmsys.wm_concat()  (1,2,4)
例子：
select u_id, wmsys.wm_concat(goods) goods_sum   from shopping   group by u_id

SELECT cid, replace(WMSYS.WM_CONCAT(sage), ',', '|') FROM student GROUP BY cid;

select u_id, wmsys.wm_concat(goods || '(' || num || '斤)' ) goods_sum   from shopping   group by u_id

------
in（1000上限解决方案(拼接字符)）REGEXP_REPLACE(source_char, pattern [, replace_string [, position [, occurrence [, match_parameter ] ] ] ] )

示例1：
SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,LEVEL) AS STR FROM DUAL  CONNECT BY  LEVEL <= LENGTH('17,20,23')-LENGTH(REGEXP_REPLACE('17,20,23',',','')) + 1 

示例2：
SELECT SUBSTR('大家好',LEVEL,1)
      FROM DUAL
    CONNECT BY LEVEL<=LENGTH('大家好');

------------
case when 表达式 then 结果1 else 结果2 end

decode('表达式','条件','ture结果1','false结果2')

分区函数:row_number() over(partition by xxx order by xxx)

----24时制格式
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual; 
------------小数处理-----------
regexp_like(B001.B001039,'^[0-9\.]+$')
to_number(regexp_substr(B001.B001039,'(((\d).\d+)|(\d+.(\d)|\d+))',1))as B001039