[oracle] 学习_持续更新

oracle教程目录（我要自学网）
https://blog.csdn.net/qq_33404395/article/details/79862815

[oracle] 学习_持续更新
1.创建调试表

-- Create table
create table TEST_PARA
(
  name        VARCHAR2(100) not null,
  sql         CLOB,
  create_time DATE
)；

1.1修改字段长度

alter table WX_INVOICES modify shop_director_tel varchar2(50) ;

1.2 增加字段

alter table jp_invoices add (contract_newstart_date date,CONTRACT_NEWEND_DATE date);

1.3关于序列

--创建
 create sequence SEQ_TABLE
 　 minvalue 1
  　maxvalue 999999999999999999999999999
  　start with 15
  　increment by 1
  　nocache;
 --查询
 select SEQ_TABLE.nextval from dual;
 --删除
 drop sequence SEQ_TABLE;

2.动态sql，强制执行语句

EXECUTE IMMEDIATE ('TRUNCATE TABLE TABLE');
-- 动态执行存储过程，只能有一行返回值：
V_SQL = 'BEGIN PROC(I_YF,O_RET_CODE,O_RET_NOTE);END;';
EXECUTE IMMEDIATE V_SQL USING (IN) I_YF,OUT O_RET_CODE,OUT O_RET_NOTE;
--从动态语句检索值：
EXECUTE IMMEDIATE 'select count(1) from table ';
into v_sql;
execute immediate 'insert into table (int) values (:X)' using i;--(i从1到1000循环)

2.1 extract 函数截取日期年月日

select --文本转日期to_date('201904', 'YYYYMM')
       extract(year from to_date('201904', 'YYYYMM')) year,
       extract(month from to_date('201904', 'YYYYMM')) month,
       extract(day from to_date('201904', 'YYYYMM')) day,
       --sysdate
       extract(year from sysdate) year,
       extract(month from sysdate) month,
       extract(day from sysdate) day,
       --date '2015-05-04'
       extract(year from date '2015-05-04') year,
       extract(month from date '2015-05-04') month,
       extract(day from date '2011-05-04') day,
       --systimestamp
       extract(year from systimestamp) year,
       extract(month from systimestamp) month,
       extract(day from systimestamp) day,
       extract(minute from systimestamp) minute,
       extract(second from systimestamp) second,
       extract(timezone_hour from systimestamp) th,
       extract(timezone_minute from systimestamp) tm,
       extract(timezone_region from systimestamp) tr,
       extract(timezone_abbr from systimestamp) ta,
       --interval 类型特定部分
       extract(year from interval '21' year) year
  from dual;
  
select
--两个日期之间间隔天数
extract (day from dt2 - dt1) day,
--两个时辰之间间隔小时数
extract (hour from dt2 - dt1) hour,
--两个分钟之间间隔分钟数
extract (minute from dt2 - dt1) minute,
--两个秒数之间间隔秒数
extract (second from dt2 - dt1) second
from
(
select
to_timestamp ('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1,
to_timestamp ('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2
from
dual
);

2.1_1每周日期范围

  select WEEKLY,'['||TO_CHAR(FIRST_DAY_WEEK,'YYYY-MM-DD')||'] -- ['||TO_CHAR(LAST_DAY_WEEK,'YYYY-MM-DD')||']'  DATE_RANGE FROM
    (   
    WITH PARAMS AS (SELECT trunc(TO_DATE('2019'||'0101','YYYYMMDD')) AS SD FROM DUAL)  
    SELECT LEVEL WEEKLY,  
    DECODE(SIGN(5-DECODE(TO_CHAR(PM.SD,'D'),'1','7',TO_CHAR(PM.SD,'D'))),-1,  
    NEXT_DAY(PM.SD+(LEVEL-1)*7,2),NEXT_DAY(PM.SD+(LEVEL-1)*7-7,2))  
     FIRST_DAY_WEEK,  
    DECODE(SIGN(5-DECODE(TO_CHAR(PM.SD,'D'),'1','7',TO_CHAR(PM.SD,'D'))),-1,  
    NEXT_DAY(PM.SD+(LEVEL-1)*7,2),NEXT_DAY(PM.SD+(LEVEL-1)*7-7,2)) + 6  
    LAST_DAY_WEEK  
    FROM DUAL D  
    LEFT JOIN PARAMS PM ON 1=1  
    CONNECT BY LEVEL<=53
    ) ;

3.for循环

  for x in 1..v_cnt loop
    end loop;

4.字符串根据;号转列
转化类型
CAST(COUNT(1) AS INT)

select REGEXP_SUBSTR(str, '[^;]+', 1, LEVEL) brand_id
from (select '123;12333;3435;234' str from dual
 CONNECT BY LEVEL <= REGEXP_COUNT(str, '[^;]+'));
 SELECT DISTINCT REGEXP_SUBSTR('1;2;3;4','[^;]+', 1, level)  str from DUAL CONNECT BY REGEXP_SUBSTR('1;2;3;4','[^;]+', 1, level)  IS NOT NULL;

* 行转列

SELECT DISTINCT REGEXP_SUBSTR('1;2;3;4','[^;]+', 1, level) FRON DUAL CONNECT BY REGEXP_SUBSTR('1;2;3;4','[^;]+', 1, level)  IS NOT NULL

--使用pivot/unpivot函数
SELECT * FROM users 
pivot (max(user_id) for dep_id in ('01' as  财务部 ,'02' as  应用部 , '03' as  平台部 ))--行转列
unpivot (aa for bb in (财务部,应用部,平台部)) --列转行
;

5.根据某个字段a.column 排序，取最大值/最小值

SELECT max(a.column) keep(dense_rank LAST order by a.column)
FROM table a;
SELECT min(a.column) keep(dense_rank FIRSTorder by a.column)
FROM table a;
 
 /* dense_rank()over(order by 列名 排序)的结果是连续的，如果有4个人，其中有3个是并列第1名，那么最后的排序结果如：1 1 1 2*/
select 成绩，dense_rank() over (order by 成绩 desc ) as 排名 from 成绩表
  /* rank()over(order by 列名排序)的结果是不连续的，如果有4个人，其中有3个是并列第1名，那么最后的排序结果结果如：1 1 1 4*/
select 成绩，rank() over (order by 成绩 desc ) as 排名 from 成绩表
  

6.根据某字段a.column排序，取第x条记录

 SELECT *FROM   ( SELECT  A.*, ROWNUM RN   FROM table a  order by a.column asc) where rn=x 

6_1.ROWNUM和ORDER BY的优先级比较
ROWNUM和ORDER BY的优先级比较是要分情况的，order by 索引，主键时，即可让oracle先按该字段排序，然后再用rownum标号 ；order by 普通列时，先rownum后order by.

7.有效年月校验

select max(1) as flag  
  from dual
 where  trunc('1233' / 100) > '2000'
   and trunc('1233' / 100) < '9999'
   and mod('1233', 100) > '0'
   and mod('1233', 100) < '13'  

8.手机正则表达式验证

 SELECT max(1) as flag FROM dual 
    where regexp_like(:MOBILE,'^[1]{1}[3456789]{1}[[:digit:]]{9}$')

9.数字类正则表达式

 --筛选出纯数字
    SELECT *   FROM TUPDATELOG t   WHERE REGEXP_LIKE(t.rec_id,'(^[+-]?\d{0,}\.?\d{0,}$)');  
    
    --筛选出含字符（非纯数字）
    select *  
    from TUPDATELOG
    Where trim(translate(RTRIM(LTRIM(rec_id)), '#0123456789', '#')) is not null
    
    ^\d+$　　//匹配非负整数（正整数 + 0）
    ^[0-9]*[1-9][0-9]*$　　//匹配正整数
    ^((-\d+)|(0+))$　　//匹配非正整数（负整数 + 0）
    ^-[0-9]*[1-9][0-9]*$　　//匹配负整数
    ^-?\d+$　　　　//匹配整数
    ^\d+(\.\d+)?$　　//匹配非负浮点数（正浮点数 + 0）
    ^(([0-9]+\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\.[0-9]+)|([0-9]*[1-9][0-9]*))$　　//匹配正浮点数
    ^((-\d+(\.\d+)?)|(0+(\.0+)?))$　　//匹配非正浮点数（负浮点数 + 0）
    ^(-(([0-9]+\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\.[0-9]+)|([0-9]*[1-9][0-9]*)))$　　//匹配负浮点数
    ^(-?\d+)(\.\d+)?$　　//匹配浮点数
    ^[A-Za-z]+$　　//匹配由26个英文字母组成的字符串
    ^[A-Z]+$　　//匹配由26个英文字母的大写组成的字符串
    ^[a-z]+$　　//匹配由26个英文字母的小写组成的字符串
    ^[A-Za-z0-9]+$　　//匹配由数字和26个英文字母组成的字符串
    ^\w+$　　//匹配由数字、26个英文字母或者下划线组成的字符串
    ^[\w-]+(\.[\w-]+)*@[\w-]+(\.[\w-]+)+$　　　　//匹配email地址
    ^[a-zA-z]+://匹配(\w+(-\w+)*)(\.(\w+(-\w+)*))*(\?\S*)?$　　//匹配url

10.全量替换

  select regexp_replace('xxyyzziioo','^xx|zz|oo$','') from dual;

11.如何分辨某个用户是从哪台机器上登陆Oracle的？

 select machine,terminal from v$session;

12.查看系统被锁的事务时间

 select * from v$locked_object;

13.数据库回退至某个时间点

---闪回至某个时间点
 INSERT INTO ecif.TJG_DX_bak SELECT *FROM ecif.TJG_DX AS OF TIMESTAMP to_timestamp('2017-09-05 12:30:00','YYYY-MM-DD HH24:mi:ss');
ALTER TABLE ecif.TJG_DX ENABLE ROW MOVEMENT;
FLASHBACK TABLE ecif.TJG_DX TO TIMESTAMP to_timestamp('2017-09-05 12:30:00','YYYY-MM-DD HH24:mi:ss');
 SELECT *FROM tsysparam y WHERE  y.paramnote LIKE '%会签%' FOR UPDATE

14.sqlserver 处理特殊字符：去空格 去回车

  select ltrim(rtrim(replace(replace('qwe123',chr(10),''),chr(13),''))) from dual

15.查看外键关联的表

 select * from user_constraints cc where cc.r_constraint_name in (
    select c.r_constraint_name
    from user_constraints c
    where c.constraint_type = 'R'
    and c.constraint_name = 'FK_MT_SCH_L_REFERENCE_MT_SCH' )

16.将原来字段修改为不允许为空/允许为空

alter table table_name modify column_name not null;
alter table table_name modify column_name null;

17.会话级临时表
会话级临时表是指临时表中的数据只在会话生命周期之中存在，当用户退出会话结束的时候，Oracle自动清除临时表中数据。

格式：

  Create Global Temporary Table Table_Name  
  (
   Col1 Type1,
   Col2 Type2
   ...
  ) 
  On Commit Preserve Rows;

18.事务级临时表
事务级临时表是指临时表中的数据只在事务生命周期中存在。

 Create Global Temporary Table Table_Name  
  (
    Col1 Type1,
    Col2 Type2
    ...
  ) 
  On Commit Delete Rows; 

当一个事务结束（commit or rollback），Oracle自动清除临时表中数据。

 Create Global Temporary Table Temptable  
  (
    C1 VARCHAR2(10),
    C2 VARCHAR2(20),
    C3 VARCHAR2(30),
    C4 VARCHAR2(60),
    C5 VARCHAR2(300),
    N1 NUMBER(6),
    N2 NUMBER(8),
    N3 NUMBER(10),
    N4 NUMBER(10,2)
  ) 
  On Commit Delete Rows; 

19.根据aaa排序取前3名：

row_number() over (partition by to_char(t.target_date, 'yyyymm') order by  sum(target_money)) as rn；
  select * from   (select ccc,
    row_number() over(partition by aaa order by bbb) rn from dual group by ccc) whre rn<2;
    SELECT * FROM (
    SELECT brand_id, to_char(t.target_date, 'yyyymm') aa,row_number() over (partition by to_char(t.target_date, 'yyyymm') order by  sum(target_money)) as rn FROM BRAND_SALES_TARGET t group by brand_id,target_date) where rn<3

20.写一个自定义函数，计算根据输入的出生日期计算年龄

create or replace function fun_ages(i_date in date,o_age out number) 
return number is
v_age number;
begin

SELECT  round(months_between(sysdate,i_date)/12,2) aa into v_age FROM dual   t;
return v_age;
end;

21.201904月份，销售目标数量最多的品牌、销售数量、总销售额，having

  SELECT brand_id, count(1), sum(target_money)
      FROM (SELECT brand_id,
                   to_char(t.target_date, 'yyyymm') target_date,
                   t.target_money
              FROM BRAND_SALES_TARGET t)
     where target_date = '201904'
     group by brand_id
    having count(1) >= (select max(count(1))
                          from BRAND_SALES_TARGET a
                         where to_char(target_date, 'yyyymm') = '201904'
                         group by brand_id)

22.触发器学习
[https://blog.csdn.net/IndexMan/article/details/8023740]

23.创建触发器

--先创建表Temptable_TEST_his
create table Temptable_TEST_his as SELECT * FROM Temptable_TEST where 1=2 ;

--创建触发器Temptable_TEST
create or replace trigger Temptable_TEST--命名有一套独立的名字空间，所以可以跟表名、过程、函数名一样
before /*insert or update or*/  delete on Temptable_TEST--一个表最多有12个触发器，不能同时触发
for each row
when (old.n1>100)
begin
  --存储过程以及触发器均不能带有commit等事务控制语句
 --pcx_shhtbcb(1,'001',24);--文本不能超过32k，所以如果内容太长，写成存储过程后调用
 
insert into   Temptable_TEST_his(C1,C2)
values('de_trig',:old.c2);

--限制对Temptable_TEST表的删除操作，（也可以限制增删改）
IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日', '星期三')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN
     RAISE_APPLICATION_ERROR(-20001, '不是上班时间，不能删除Temptable_TEST表');
 END IF;

case when deleting then 
  RAISE_APPLICATION_ERROR(-20002, '不能删除n1>100的记录');
  end case;

end;


  delete Temptable_TEST where c2='HY001ZL010025';
  SELECT * FROM Temptable_TEST_his where c2='HY001ZL040009';
  drop table Temptable_TEST_his;
  drop trigger Temptable_TEST;

树遍历结构：

select id from lborganization connect by prior id = fid start with id = ***;