自己整理oracle有关练习

最新推荐文章于 2024-04-26 15:51:14 发布

Persistence_x

最新推荐文章于 2024-04-26 15:51:14 发布

阅读量777

点赞数

分类专栏： oracle相关资料文章标签： oracle 数据库数据库管理

本文链接：https://blog.csdn.net/xiaohuaidan1988/article/details/14447777

版权

oracle相关资料专栏收录该内容

2 篇文章 0 订阅

订阅专栏

oracle自我整理不断更新中

1.goto与各种循环

-----------------Loop 循环(do…while)
格式：
LOOP
循环的语句 ;
EXIT WHEN 终止条件 ;
循环条件必须更改 ;
END LOOP ;
--------------------
DECLARE
cou NUMBER ;
BEGIN
-- 必须给一个初始值
cou := 1 ;
LOOP
DBMS_OUTPUT.put_line('cou = '||cou) ;
EXIT WHEN cou>10 ;
cou := cou + 1 ;
END LOOP ;
END ;
----------------while 循环
格式：
while(判断循环的条件) loop
循环的语句 ;
循环条件的改变 ;
End loop ;
-------------------
DECLARE
cou NUMBER ;
BEGIN
-- 必须给一个初始值
cou := 1 ;
WHILE(cou<10) LOOP
DBMS_OUTPUT.put_line('cou = '||cou) ;
cou := cou + 1 ;
END LOOP ;
END ;
------------for循环
格式：
FOR 变量名称 in 变量的初始值..结束值 LOOP
循环语句 ;
END LOOP ;
-------------
DECLARE
cou NUMBER ;
BEGIN
FOR cou IN 1..10 LOOP
DBMS_OUTPUT.put_line('cou = '||cou) ;
END LOOP ;
END ;

----------------------------go to 语句 goto标签后面的语句会全部执行完
DECLARE
eno emp.empno%TYPE;
sal emp.sal%TYPE;
BEGIN
	SELECT sal INTO sal FROM emp WHERE empno=7839;
	IF sal>3500 THEN
		GOTO po1;
		ELSIF sal>2000 AND sal<3500 THEN 
		GOTO po2;
		ELSE
			GOTO po3;
			END IF;
			<<po1>>
			dbms_output.put_line('高工资');	
			  GOTO aaa;
			<<po2>>
			dbms_output.put_line('中工资');	
				GOTO aaa;	
			<<po3>>
			dbms_output.put_line('低工资');
				GOTO aaa;
			<<aaa>>
			NULL;
			END;

2.Level以及CONNECT BY使用

--查询编号7876的上级消息的层级显示
SELECT LEVEL,e.* from emp e CONNECT BY PRIOR e.mgr=e.empno START WITH e.empno='7876' 
ORDER BY LEVEL DESC

3.oracle top10的记录

-----------oracle top10的记录
SELECT * FROM 
(SELECT row_number()OVER (ORDER BY sal DESC) row_n,e.*  from emp e) where row_n<=10;

4.查主键在哪张表

ORA-00001: 违反唯一约束条件 (WMS.PK_15)

select constraint_name,table_name,column_name from user_cons_columns
where constraint_name='PK_15'

5.表结构查询

在 oracle 中描述数据字典视图
select table_name ,comments from dictionary where table_name like '%TABLE%';
--创建主外键关系
alter table Orders add constraint fk_id foreign key(UUid) References Users(ID);
--查询表中的约束
SELECT * FROM  user_constraints WHERE lower(table_name)='book';
select table_name FROM user_tables;
查询某用户下所有表
select table_name from all_tables where owner='SCOTT';
查询 EMP 表中所有字段
select * from all_tab_columns where table_name='EMP';
--列出表的索引列
select * from sys.all_ind_columns where table_name='DEPT';
--列出表中约束
select * from all_constraints where table_name='EMP'
5.为所有人长工资，标准是：10 部门长 10%；20 部门长 15%；30 部门长 20%其他部门长
18%（要求用 DECODE 函数）
update emp
set sal=decode(deptno,'10',sal*(1+0.1), '20',sal*(1+0.15), '30',sal*(1+0.2),sal*(1+0.18));
6.根据工作年限长工资，标准是：为公司工作了几个月就长几个百分点。
update emp set sal= round(sal * (1+(sysdate - hiredate)/365/12/100),2)
2.云计算是分布式原理,并行原理，网格计算的发展;形象的比喻：不需要家家备发电机，只需要去电力公司购买即可

6.oracle分页

select * from (select 
        externreceiptkey,orderkey,sku,storerkey,signname,signdate,paperwork,sign_notes,
        ROW_NUMBER() over(order by signdate desc) as num from customer_sign_detail where externreceiptkey='WMS0000000038')  
         where num between 15 and 20 order by signdate desc

7.oracle根据月份得到其他的月份

select to_char(sysdate,'yyyy')||lpad(level,2,0) from dual connect by level <13
minus
select to_char(sysdate,'yyyymm') from dual

8.oracle截取字符串并且排序

with a as
(
select '西直门北大街58号7号楼1001' col1 from dual union all 
select '西直门北大街58号7号楼102' col1 from dual union all 
select '西直门北大街59号8号楼10-1002' col1 from dual union all 
select '西直门北大街59号8号楼1-103' col1 from dual 
)


select instr(col1,'楼')-length(replace(col1,'-','')) from a 
select col1 from a
order by  to_number(substr(replace(col1,'-',''),instr(col1,'楼')-length(replace(col1,'-',''))))

9.求xxx,yyy,zzz分割后的字符

10G
SELECT REGEXP_SUBSTR('xxx,yyy,zzz', '[^,]+', 1, LEVEL)
         FROM DUAL
       CONNECT BY LEVEL <= LENGTH('xxx,yyy,zzz') -
                  LENGTH(REPLACE('xxx,yyy,zzz', ',', '')) + 1;


11G

SELECT REGEXP_SUBSTR('xxx,yyy,zzz', '[^,]+',1,LEVEL)
         FROM DUAL
       CONNECT BY LEVEL <= REGEXP_COUNT('xxx,yyy,zzz', ',') + 1;

10.截取字符串分组

with tt as
(
select 1 objID, 'aa,bb' str from dual
union all
select 2, 'aa,cc' from dual
union all
select 3, 'bb,cc' from dual
union all
select 4, 'bb,dd' from dual
union all
select 5, 'cc,ee' from dual
union all 
select 6, 'cccdd,ff'from dual
)


select str ,count(*) from (

select objid ,str from (
select objid ,regexp_substr(str,'\w+',1,1) str from tt
union all
select objid ,regexp_substr(str,'\w+',1,2) str from tt where regexp_substr(str,'\w+',1,2) is not null 
union all
select objid ,regexp_substr(str,'\w+',1,3) str from tt where regexp_substr(str,'\w+',1,3) is not null)

) group by str

11.列行之间转换

//行变列
SELECT user_name,
sum(DECODE(course,'语文',score,0)) AS 语文,
sum(DECODE(course,'数学',score,0)) AS 数学,
sum(DECODE(course,'英语',score,0)) AS 英语
FROM test_tb_grade GROUP BY user_name



---先查出科目,分数范围 和 count(范围数)
SELECT t.course,
CASE WHEN t.score>=0 AND t.score<60 THEN '00-60'
     WHEN t.score>=60 AND t.score<80 THEN '60-80'
     WHEN t.score>=80 AND t.score<=100 THEN '80-100'
		 END  AS SCORE_GP ,COUNT(t.score) AS countnum
FROM test_tb_grade t GROUP BY t.course,
CASE WHEN t.score>=0 AND t.score<60 THEN '00-60'
     WHEN t.score>=60 AND t.score<80 THEN '60-80'
     WHEN t.score>=80 AND t.score<=100 THEN '80-100' END 
		 ORDER BY T.COURSE


--根据查询出的结果在换转列
SELECT t2.score_gp,
SUM(DECODE(t2.course,'语文',t2.countnum,NULL))AS 语文,
SUM(DECODE(t2.course,'数学',t2.countnum,NULL))AS 数学,
SUM(DECODE(t2.course,'英语',t2.countnum,NULL))AS 英语
FROM 
(SELECT t.course,
CASE WHEN t.score>=0 AND t.score<60 THEN '00-60'
     WHEN t.score>=60 AND t.score<80 THEN '60-80'
     WHEN t.score>=80 AND t.score<=100 THEN '80-100'
		 END  AS SCORE_GP ,COUNT(t.score) AS countnum
FROM test_tb_grade t GROUP BY t.course,
CASE WHEN t.score>=0 AND t.score<60 THEN '00-60'
     WHEN t.score>=60 AND t.score<80 THEN '60-80'
     WHEN t.score>=80 AND t.score<=100 THEN '80-100' end 
		 ORDER BY T.COURSE)  t2  GROUP BY t2.score_gp ORDER BY t2.score_gp;


---列变行

SELECT user_name,'语文' course,YW_SCORE  score from test_tb_grade2
UNION 
SELECT user_name,'数学' course,SX_SCORE  SCORE FROM TEST_TB_GRADE2
UNION 
SELECT USER_NAME,'英语' course,YU_SCORE  SCORE FROM TEST_TB_GRADE2
--其实不用排序
ORDER BY USER_NAME,COURSE

12.随机时间后面的时分秒

SELECT TO_DATE('20130922' || LPAD(FLOOR(DBMS_RANDOM.VALUE(0, 24)), 2, '0') ||
                LPAD(FLOOR(DBMS_RANDOM.VALUE(0, 60)), 2, '0') ||
                LPAD(FLOOR(DBMS_RANDOM.VALUE(0, 60)), 2, '0'),
                'YYYYMMDD HH24:MI:SS')
   FROM DUAL;

13.删除重复自增数据ID

delete from t where id not in (select min(id) from t group by name,age);
--删除重复自增的ID数据 (这种效果更佳!)
DELETE FROM A a1 WHERE NOT EXISTS
(SELECT * FROM A a2 WHERE a1.id>a2.id AND a1.name=a2.name AND a1.age=a2.age);

14.输入一个员工名字，判断该名字在员工表中是否存在。存在返回 1，不存在返回 0

--写一个函数 输入一个员工名字，判断该名字在员工表中是否存在。存在返回 1，不存在返回 0
CREATE OR REPLACE FUNCTION GetTTT(enm emp.ename%TYPE) RETURN NUMBER
AS
 rnumber NUMBER;
 BEGIN
 SELECT COUNT(*) INTO rnumber FROM emp WHERE ename=UPPER(enm);
 IF rnumber >=1 THEN
   RETURN 1;
 ELSE
   RETURN 0;
   END IF;
 END;

15.分页的存储过程

exec usp_selectByPage 'Users','id asc',3,2,'1=1'
create proc  usp_selectByPage
@tName nvarchar(20),
@sort  nvarchar(1000),
@pageCount int,
@pageNum int,
@where nvarchar(Max)
as
declare @sql nvarchar(Max)
declare @begin int
declare @end   int
set @begin = (@pageNum-1)*@pageCount+1
set @end  = @pageCount*@pageNum

set @sql='select * from(select *,row_number() over(order by '+@sort+')as nid from '+@tName+') as temp where temp.nid>='+Convert(varchar(20),@begin)+' and temp.nid<='+Convert(varchar(20),@end)
exec  sp_executesql @sql
go

16.替换123到321截取前后的逗号

//正则和replace和substr运用
WITH T1 AS
 (SELECT 1 ID, '123,236,569' ROUTE
    FROM DUAL
  UNION ALL
  SELECT 2 ID, '123' ROUTE
    FROM DUAL
  UNION ALL
  SELECT 3 ID, '1234,569' ROUTE
    FROM DUAL
  UNION ALL
  SELECT 4 ID, '423,123'
    FROM DUAL
  UNION ALL
  SELECT 5 ID, '423,44123' FROM DUAL
  union all
  select 6 ID, '221,123,432' from dual
  )

select regexp_substr(regexp_replace(','||ROUTE||',',',[1]{1}[2]{1}[3]{1},',',321,'),'\d+.*\d') from t1



select substr(REPLACE(','||route||',',',123,',',321,'),2,length(route)) from t1

17.top和取出50%的记录

--取出50%的记录
SELECT a.* FROM 
(SELECT ROWNUM NO,e.* FROM (SELECT * from emp) e) a  WHERE a.no<=(SELECT COUNT(*)/2 from emp);

-----top几条数据
SELECT ROWNUM,e.* FROM(SELECT * from emp1 ORDER BY hiredate) e WHERE ROWNUM<=2;

18.随机数

//savepoint//保存点
//设置oracle日期格式
ALTER SESSION SET Nls_date_format='YYYY-MM-DD';



NVL 如果值为空用这个方法进行计算
UNION：并集，所有的内容都查询，重复的显示一次
UNION ALL：并集，所有的内容都显示，包括重复的
INTERSECT：交集：只显示重复的
MINUS：差集：只显示对方没有的 （跟顺序是有关系的）


//返回1000-2000的随机数
--SELECT round(dbms_random.value(1000,2000)) from dual;
--随机返回5条记录
SELECT ename,job FROM (SELECT * from emp ORDER BY dbms_random.value()) WHERE ROWNUM<=5 
--连续求和sum(sal) over(order by ename)指的是连续求和.是以 ename 来排序的。若有两个这
样的窗口函数，以后面的排序为主。
select ename,sal,sum(sal) over(), sum(sal) over(order by ename) FROM emp;
--分部门连续求和
select deptno,sal ,sum(sal) over (partition by deptno order by ename) as s from emp;
--得到上一行和下一行数据工资列
select ename,sal,lead(sal) over(order by sal) aaa ,lag(sal) over(ORDER by sal) bbb from emp;

19.查询2个时间中相差的时间

WITH r AS
  (SELECT (1343873132 - 1343706107) x FROM dual)


 SELECT lpad(trunc(x / 60 / 60 / 24), 2,0) || '天' ||
        lpad(trunc(MOD(x, 60 * 60 * 24) / 60 / 60), 2,0) || '小时' ||
        lpad(trunc(MOD(x, 60 * 60) / 60), 2,0) || '分'
      
FROM   r


select mod(167025,60*60) from dual

20.触发器创建以及实用

--------------触发器
CREATE SEQUENCE PRODUCT_DYNAMIC_s START WITH 1 INCREMENT BY 1;


---
CREATE OR REPLACE TRIGGER trg_mytable_increment
BEFORE INSERT ON PRODUCT_DYNAMIC FOR EACH ROW
BEGIN
  SELECT PRODUCT_DYNAMIC_s.nextval INTO :new.表ID FROM dual;
END;

21.创建oracle序列

1.创建oracle序列
CREATE SEQUENCE Seq_TestUid
  INCREMENT BY 1 -- 每次加几个
  START WITH 1   从1开始计数
  NOMAXVALUE   不设置最大值
  NOCYCLE      一直累加，不循环
  nocache      不建缓冲区

22.递归日历表

select 
max(decode(to_char(dt,'day'),'星期日',to_char(dt,'dd'))) "星期日",
max(decode(to_char(dt,'day'),'星期一',to_char(dt,'dd'))) "星期一",
max(decode(to_char(dt,'day'),'星期二',to_char(dt,'dd'))) "星期二",
max(decode(to_char(dt,'day'),'星期三',to_char(dt,'dd'))) "星期三",
max(decode(to_char(dt,'day'),'星期四',to_char(dt,'dd'))) "星期四",
max(decode(to_char(dt,'day'),'星期五',to_char(dt,'dd'))) "星期五",
max(decode(to_char(dt,'day'),'星期六',to_char(dt,'dd'))) "星期六"
from
(
select 
to_char(dt,'d')-row_number() over(order by dt) gcol,
to_char(dt,'d'),row_number() over(order by dt),
dt 
from 
(select trunc(to_date('2010-12-15','yyyy-mm-dd'),'mm')+level-1 dt
from dual
connect by nocycle level<=to_char(last_day(to_date('2010-12-15','yyyy-mm-dd')),'dd'))
)
group by gcol
order by abs(gcol)

23.函数大全

Initcap 首字母大写函数
Concat函数连接字符串
--------------分部门求和
sum(sal) over (partition by deptno) 
sum(sal) over(order by ename)指的是连续求和.是以 ename 来排序的。若有两个这样的窗口函数，以后面的排序为主
。
SELECT Substr('abcde',LENGTH('abcde')-3) from dual;(字符串 第几位开始)
SELECT SUBSTR('abcde',0,1 ) from dual; 截取字符串 (字符串,从第几位(倒数的),截取几位)

SELECT ROUND(412.313,-2) from dual; (复数显示整数显示几位(四舍五入),整数就是小数点后显示几位)

SELECT to_char(sal,'L999,999,999') FROM dual （把数字800变成￥800）

select nvl('comm',2) from dual; 如果有值返回comm 如果等于空返回2 ；

//当前年的第一天                                //当前年的最后一天
SELECT trunc(SYSDATE,'yyyy') from dual;    ··SELECT add_months(trunc(SYSDATE,'yyyy'),12)-1 from dual;

--like 与 Instr函数使用
SELECT * from emp WHERE ename LIKE '%S%';

SELECT * from emp WHERE Instr(ename,'S')>0 ;

--Rank会跳过排序,而Dense则不会 例如1-2-2-4(RANK) 则Dense是1-2-2-3名
Rank() Over()科目来分组，然后以分数来排序，给排序的结果分配rank，取前三名的rank 即想要每门科目的前3名的分数
select * from (select rank() over(partition by 科目 order by 分数 desc) rk,a.* from a)  t where t.rk<=3;

24.oracle奇数偶数行

select *
 from (select customer_sign.*, rownum rn from CUSTOMER_SIGN)
 where mod(rn, 2) != 0

25.oracle性能总结

特别注意：通过把不等于操作符改成 OR 条件，就可以使用索引，以避免全表扫描

-会对oracle索引有影响的，显式阻止oracle使用索引
is null ; is not null ; not in; !=; like ;

26.oracle用户管理

提示用户第一次连接的时候需要修改密码，让用户的密码到期
|- ALTER USER test PASSWORD expire ;

 ALTER USER 用户名 ACCOUNT LOCK|UNLOCK
|- ALTER USER test ACCOUNT LOCK ;    锁定
|- ALTER USER test ACCOUNT UNLOCK ;  解锁

１．CONNECT, RESOURCE, DBA
这些预定义角色主要是为了向后兼容。其主要是用于数据库管理。oracle 建议
用户自己设计数据库管理和安全的权限规划，而不要简单的使用这些预定角色。
将来的版本中这些角色可能不会作为预定义角色。
２．DELETE_CATALOG_ROLE， EXECUTE_CATALOG_ROLE， SELECT_CATALOG_ROLE
这些角色主要用于访问数据字典视图和包。
３．EXP_FULL_DATABASE， IMP_FULL_DATABASE
这两个角色用于数据导入导出工具的使用。


查看权限
select * from user_sys_privs;

权限传递
Grant create session to test with admin option;(可以实现权限传递)

第一范式：字段要设计的不可再分
第二范式：两个表的关系，在第三张关系表中体现
第三范式：多张表中，只存关系，不存具体信息
总范式 数据库表关联越少越好，SQL 语句复杂度越低越好

27.查看oracle资源问题

//查看oracle资源问题
SELECT a.*,b.* FROM V$LOCKED_OBJECT a,V$SESSION b WHERE a.SESSION_ID=b.SID;



alter system kill session  '22,2427'

28.oracle11g空表无法导出

select 'alter table '||table_name||' allocate extent(size 64k);' sql_text,table_name,tablespace_name
 from user_tables
 where table_name not in
 (select segment_name from user_segments where segment_type = 'TABLE');


--空表设置一下延延迟加载为FALSE
--如果创建前没有将段延迟默认修改，则空表导不出来。
可以查询一下看有哪些表是在此前创建的

29.oracle导出导入

导出数据库 exp wms/wm#98lpm@jmc8 file=d:\data\abc.dmp tables=(wms_stock_dtl,wms_stock_bill,WMS_Trans_DTL)
log=d:\data\abc.log

导入数据库 imp wms/wms@jmcbak full=y file=d:\data\abc.dmp ignore=y

Persistence_x

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
自己整理oracle有关练习

oracle自我整理不断更新中1.goto与各种循环-----------------Loop 循环(do…while)格式：LOOP循环的语句 ;EXIT WHEN 终止条件 ;循环条件必须更改 ;END LOOP ;--------------------DECLAREcou NUMBER ;BEGIN-- 必须给一个初始值cou := 1 ;LOOPDBM
复制链接

扫一扫