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 = ***;