1.在sql
10年積累的網(wǎng)站制作、成都網(wǎng)站設(shè)計(jì)經(jīng)驗(yàn),可以快速應(yīng)對(duì)客戶(hù)對(duì)網(wǎng)站的新想法和需求。提供各種問(wèn)題對(duì)應(yīng)的解決方案。讓選擇我們的客戶(hù)得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認(rèn)識(shí)你,你也不認(rèn)識(shí)我。但先網(wǎng)站設(shè)計(jì)后付款的網(wǎng)站建設(shè)流程,更有鹿泉免費(fèi)網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。
plus上打開(kāi)你的存儲(chǔ)過(guò)程文件
2.在右側(cè)樹(shù)形文件中找到procedures里你的文件在文件名上右鍵test
(沒(méi)有刷新一下)
3.就會(huì)看到新的界面
4.輸入值
5.點(diǎn)擊運(yùn)行按鈕
這樣就ok了
一、通過(guò)PL/SQL Dev工具
1、直接File-New-Explain Plan Window,在窗口中執(zhí)行sql可以查看計(jì)劃結(jié)果。其中,Cost表示cpu的消耗,單位為n%,Cardinality表示執(zhí)行的行數(shù),等價(jià)Rows。
2、先執(zhí)行 EXPLAIN PLAN FOR select * from tableA where paraA=1,再 select * from table(DBMS_XPLAN.DISPLAY)便可以看到oracle的執(zhí)行計(jì)劃了,看到的結(jié)果和1中的一樣,所以使用工具的時(shí)候推薦使用1方法。
注意:PL/SQL Dev工具的Command window中不支持set autotrance on的命令。還有使用工具方法查看計(jì)劃看到的信息不全,有些時(shí)候我們需要sqlplus的支持。
二、通過(guò)sqlplus
1.最簡(jiǎn)單的辦法
Sql set autotrace on
Sql select * from dual;
執(zhí)行完語(yǔ)句后,會(huì)顯示explain plan 與 統(tǒng)計(jì)信息。
這個(gè)語(yǔ)句的優(yōu)點(diǎn)就是它的缺點(diǎn),這樣在用該方法查看執(zhí)行時(shí)間較長(zhǎng)的sql語(yǔ)句時(shí),需要等待該語(yǔ)句執(zhí)行成功后,才返回執(zhí)行計(jì)劃,使優(yōu)化的周期大大增長(zhǎng)。如果不想執(zhí)行語(yǔ)句而只是想得到執(zhí)行計(jì)劃可以采用:
Sql set autotrace traceonly
這樣,就只會(huì)列出執(zhí)行計(jì)劃,而不會(huì)真正的執(zhí)行語(yǔ)句,大大減少了優(yōu)化時(shí)間。雖然也列出了統(tǒng)計(jì)信息,但是因?yàn)闆](méi)有執(zhí)行語(yǔ)句,所以該統(tǒng)計(jì)信息沒(méi)有用處,如果執(zhí)行該語(yǔ)句時(shí)遇到錯(cuò)誤,解決方法為:
(1)在要分析的用戶(hù)下:
Sqlplus @ ?
dbmsadminutlxplan.sql
(2) 用sys用戶(hù)登陸
Sqlplus @ ?sqlplusadminplustrce.sql
Sqlplus grant plustrace to user_name;
- - user_name是上面所說(shuō)的分析用戶(hù)
2.用explain plan命令
(1) sqlplus explain plan for select * from testdb.myuser
(2) sqlplus select * from table(dbms_xplan.display);
上面這2種方法只能為在本會(huì)話(huà)中正在運(yùn)行的語(yǔ)句產(chǎn)生執(zhí)行計(jì)劃,即我們需要已經(jīng)知道了哪條語(yǔ)句運(yùn)行的效率很差,我們是有目的只對(duì)這條SQL語(yǔ)句去優(yōu)化。其實(shí),在很多情況下,我們只會(huì)聽(tīng)一個(gè)客戶(hù)抱怨說(shuō)現(xiàn)在系統(tǒng)運(yùn)行很慢,而我們不知道是哪個(gè)SQL引起的。此時(shí)有許多現(xiàn)成的語(yǔ)句可以找出耗費(fèi)資源比較多的語(yǔ)句,如:
SELECT ADDRESS, substr(SQL_TEXT,1,20) Text, buffer_gets, executions,
buffer_gets/executions AVG FROM v$sqlarea
WHERE executions0 AND buffer_gets 100000 ORDER BY 5;
ADDRESS TEXT BUFFER_GETS EXECUTIONS AVG
-------- ---------------------------------------- ----------- ---------- ------------------------------------------------------------
66D83D64 select t.name, (sel 421531 60104 7.01336017
66D9E8AC select t.schema, t.n 1141739 2732 417.913250
66B82BCC select s.synonym_nam 441261 6 73543.5
從而對(duì)找出的語(yǔ)句進(jìn)行進(jìn)一步優(yōu)化。當(dāng)然我們還可以為一個(gè)正在運(yùn)行的會(huì)話(huà)中運(yùn)行的所有SQL語(yǔ)句生成執(zhí)行計(jì)劃,這需要對(duì)該會(huì)話(huà)進(jìn)行跟蹤,產(chǎn)生trace文件,然后對(duì)該文件用tkprof程序格式化一下,這種得到執(zhí)行計(jì)劃的方式很有用,因?yàn)樗渌~外信息,如SQL語(yǔ)句執(zhí)行的每個(gè)階段(如Parse、Execute、Fetch)分別耗費(fèi)的各個(gè)資源情況(如CPU、DISK、elapsed等)。
3、啟用SQL_TRACE跟蹤所有后臺(tái)進(jìn)程活動(dòng):
全局參數(shù)設(shè)置: .OracleHome/admin/SID/pfile中指定: SQL_TRACE = true (10g)
當(dāng)前session中設(shè)置:
SQL alter session set SQL_TRACE=true;
SQL select * from dual;
SQL alter session set SQL_TRACE=false;
對(duì)其他用戶(hù)進(jìn)行跟蹤設(shè)置:
SQL select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
128 54521 B
開(kāi)啟跟蹤:SQL exec dbms_system.set_SQL_TRACE_in_session(127,31923,true);
關(guān)閉跟蹤:SQL exec dbms_system.set_SQL_TRACE_in_session(127,31923,false);
然后使用oracle自帶的tkprof命令行工具格式化跟蹤文件。
4、使用10046事件進(jìn)行查詢(xún):
10046事件級(jí)別:
Lv1 - 啟用標(biāo)準(zhǔn)的SQL_TRACE功能,等價(jià)于SQL_TRACE
Lv4 - Level 1 + 綁定值(bind values)
Lv8 - Level 1 + 等待事件跟蹤
Lv12 - Level 1 + Level 4 + Level 8
全局設(shè)定:
OracleHome/admin/SID/pfile中指定: EVENT="10046 trace name context forever,level 12"
當(dāng)前session設(shè)定:
開(kāi)啟:SQL alter session set events '10046 trace name context forever, level 8';
關(guān)閉:SQL alter session set events '10046 trace name context off';
對(duì)其他用戶(hù)進(jìn)行設(shè)置:
SQL select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
SQL exec dbms_system.set_ev(127,31923,10046,8,'A');
5、使用tkprof格式化跟蹤文件: (根據(jù)下面SQL語(yǔ)句得到的文件都不存在該目錄下,郁悶啊,懵懂啊...)
一般,一次跟蹤可以分為以下幾步:
1、界定需要跟蹤的目標(biāo)范圍,并使用適當(dāng)?shù)拿顔⒂盟韪櫋?/p>
2、經(jīng)過(guò)一段時(shí)間后,停止跟蹤。此時(shí)應(yīng)該產(chǎn)生了一個(gè)跟蹤結(jié)果文件。
3、找到跟蹤文件,并對(duì)其進(jìn)行格式化,然后閱讀或分析。
--使用一下SQL找到當(dāng)前session的跟蹤文件:
SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_namefrom( select p.spid from v$mystat m,v$session s, v$process pwhere m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,( select t.instance from v$thread t,v$parameter vwhere v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,( select value from v$parameter where name = 'user_dump_dest' ) d;-- 其它用戶(hù)的 session SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name from ( select p.spid from v$session s, v$process p where s.sid= '27' and s. SERIAL#= '30' and p.addr = s.paddr) p, ( select t.instance from v$thread t,v$parameter v where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i, ( select value from v$parameter where name = 'user_dump_dest' ) d;
--查找后使用tkprof命令,將TRACE文件格式為到D盤(pán)的explain_format.txt文件中
SQL $tkprof d:/oracle/admin/FZLGFM/udump/fzlgfm_ora_3468.trc d:/explain_format.txt
文件內(nèi)容大致如下(看不太懂....懵懂啊.....天啊....神啊.....過(guò)幾時(shí)就懂了/////////////)
TKPROF: Release 9.2.0.1.0 - Production on 星期二 4月 20 13:59:20 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: d:/oracle/admin/FZLGFM/udump/fzlgfm_ora_3468.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call********************************************************************************
alter session set events '10046 trace name context forever, level 8'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
查詢(xún)是數(shù)據(jù)的一個(gè)重要操作。用戶(hù)發(fā)送查詢(xún)請(qǐng)求,經(jīng)編譯軟件變異成二進(jìn)制文件供服務(wù)器查詢(xún),后返回查詢(xún)結(jié)果集給用戶(hù),查詢(xún)會(huì)產(chǎn)生一個(gè)虛擬表,看到的是表形式顯示的結(jié)果,但結(jié)果并不真正的存儲(chǔ),每次執(zhí)行查詢(xún)只是從數(shù)據(jù)表中提取數(shù)據(jù),并按照表的形式顯示出來(lái)。
SELECT 列名
FGROM 表名
[WHERE 查詢(xún)條件表達(dá)式]
[GROUP BY 分組表達(dá)式]
[HAVING 分組查詢(xún)表達(dá)式]
[ORDER BY 排序的列名 [ASC或DESC]]
group by 用于對(duì)查詢(xún)的結(jié)果分組統(tǒng)計(jì),通過(guò)對(duì)group by后面的名字進(jìn)行分組后輸出結(jié)果。
group by后面還可以跟多列表示 多列分組 ,在多列分組時(shí)放前面的優(yōu)先分組。
group by 列名,列名
having 子句用于限制分組顯示結(jié)果,其只能和group by一起連用。在where中沒(méi)有辦法直接使用聚合函數(shù),即sum avg等無(wú)法使用,所以引用了having,在having中可以使用這些函數(shù)。
order by 表示排序,后跟列名和排序方式。如果什么都不加默認(rèn)為升序。ASC表示升序,DESC表示降序。
在Oracle中還可以設(shè)置多列排序
order by 列名1 升降,列名2 升降;
前面的為主要排序,后面的為次一級(jí)排序。
注:碰到自己與自己比較的情況下,不能用having,可以創(chuàng)建一個(gè)新列。
注:如果select語(yǔ)句同時(shí)包含group by,having,order by,按group by,having,order by排序
分組和聚合一起使用,目的是為了統(tǒng)計(jì)信息。
where是為了from服務(wù)的,只能跟 真實(shí)的字段 ,用來(lái)篩選from子句中指定的操作所產(chǎn)生的行
group by 用來(lái)分組where子句的輸出
having 用來(lái)從分組的結(jié)果中篩選行
order by用來(lái)對(duì)篩選的結(jié)果進(jìn)行排序
(1)分組函數(shù):max min?avg sum count
max表示該列的最大值,min表示該列的最小值,avg表示該列的平均值,sum表示該列的和,count表示該列的行數(shù)。
注:分組函數(shù)(max、min、avg、count、sum)只能出現(xiàn)在選擇列表中having子句、order by子句、不能出現(xiàn)在where子句和group by子句中。
(2)多表查詢(xún)
多表查詢(xún)是指兩個(gè)和兩個(gè)以上的表或者是視圖的查詢(xún),在實(shí)際應(yīng)用中,當(dāng)查詢(xún)單個(gè)表不能滿(mǎn)足需求時(shí),一般使用多表查詢(xún)。如:顯示sales部門(mén)位置和其員工的姓名,這種情況下需要使用到(dept表和emp表)。
多表查詢(xún)的連接一般可以分為:內(nèi)連接、左外連接、右外連接、全連接。
注:在使用多表查詢(xún)的時(shí)候每個(gè)表可以設(shè)置別名,如果表指定了別名,那么語(yǔ)句中所有語(yǔ)句必須使用別名,而不能再使用實(shí)際表名。且在寫(xiě)屬性的時(shí)候如果屬性為其中一個(gè)表特有的屬性則不需要寫(xiě)別名,如果是兩個(gè)表都有則必須指定是哪一個(gè)表的哪個(gè)屬性格式為:表名.屬性名。
select 列名 from 表1 別名,表2 別名...
注:e是emp的別名,d是dept的別名。
但如果對(duì)表進(jìn)行了操作則需要設(shè)置別名,如:查詢(xún)每個(gè)部門(mén)中工資高于該部門(mén)平均工資的員工人數(shù)。在其中有一個(gè)avg表,這個(gè)表必須設(shè)置別名(提醒:如果僅有一個(gè)被修改的表,則可以不設(shè)置別名,但如果有多個(gè)表則必須設(shè)置別名)。
內(nèi)連接
內(nèi)連接通過(guò)使用比較運(yùn)算符來(lái)使每個(gè)表的通用列中的值匹配來(lái)組成一個(gè)新表,即:把兩個(gè)表中間共有的那些行拿出來(lái)進(jìn)行連接,如果某些行不是兩個(gè)表共有的,則不進(jìn)行連接。
select
from 表1
inner join 表2
on 匹配條件
或
select
from 表1 表2
where匹配條件
左外連接
左外連接與內(nèi)連接的區(qū)別是:設(shè)置左外連接的時(shí)候設(shè)置了主表和附表,主表在前,附表在后。內(nèi)連接是將兩個(gè)表匹配的地方輸出出來(lái),而左外連接則是主表全寫(xiě),附表一一對(duì)應(yīng),附表有則加上,沒(méi)有不寫(xiě)。
select
from 表1
left join 表2
on 匹配條件
右外連接
右外連接和左外連接基本相同只是右外連接的主表寫(xiě)在后邊。
select
from 表1
right join 表2
on 匹配條件
全連接
全連接是在等值連接的基礎(chǔ)上將左表和右表的未匹配數(shù)據(jù)都加上,使用的關(guān)鍵字為full outer join或者full join。
select
from 表1
full join 表2
on 匹配條件
自連接
還有一種特殊情況即自連接,在Oracle中一個(gè)表無(wú)法與自己進(jìn)行比較,所以當(dāng)需要自己表的兩個(gè)信息做比較的時(shí)候也需要使用連接來(lái)連接,即同一張表的連結(jié)查詢(xún)。
(3)子查詢(xún)
子查詢(xún)是指嵌套在其他sql語(yǔ)句中的select語(yǔ)句,也叫嵌套查詢(xún)。sql語(yǔ)句執(zhí)行順序?yàn)閺挠业阶髨?zhí)行,所以在執(zhí)行查詢(xún)時(shí)會(huì)先執(zhí)行左側(cè)的子查詢(xún)后進(jìn)行主查詢(xún)。
子查詢(xún)分為單行子查詢(xún)和多行子查詢(xún),單行子查詢(xún)是指返回一行數(shù)據(jù)的子查詢(xún)語(yǔ)句,多行子查詢(xún)是指返回多行數(shù)據(jù)的查詢(xún)語(yǔ)句。子查詢(xún)還可以分為多列子查詢(xún)、多行子查詢(xún)、多列多行子查詢(xún)。
在進(jìn)行子查詢(xún)時(shí)如果內(nèi)部查詢(xún)不返回任何記錄,則外部條件中字段DEPTNO與NULL比較永遠(yuǎn)為假,也就是說(shuō)外部查詢(xún)不返回任何結(jié)果。
總結(jié)為:
單行子查詢(xún)是指子查詢(xún)只返回單列、單行數(shù)據(jù)
多行子查詢(xún)是指返回單列多行數(shù)據(jù),都是針對(duì)單列而言的
多列子查詢(xún)則是指查詢(xún)返回多個(gè)列數(shù)據(jù)的子查詢(xún)語(yǔ)句
單行子查詢(xún)
where deptno = (單行數(shù)值)
多行子查詢(xún)
where deptno in ( 多行數(shù)值 )
多列子查詢(xún):
where (job,deptno)=(select job,deptno from emp where ename='KING')
多列多行子查詢(xún)
where (job,deptno) in (select job,deptno from emp where ename='KING')
單行子查詢(xún)
在單行子查詢(xún)的外部查詢(xún)中可以使用=、、、=、=、等比較運(yùn)算符。
內(nèi)部查詢(xún)返回的結(jié)果必須與外部查詢(xún)條件中字段(DEPTNO)相匹配。
多行子查詢(xún)
在WHERE子句中使用多行子查詢(xún)時(shí),可以使用多行比較運(yùn)算符(IN,ALL,ANY)。
IN:等于任何一個(gè)。
ALL:和子查詢(xún)返回的所有值比較。例如:salALL(1,2,3)等價(jià)于sal3,即大于所有。
ANY:和子查詢(xún)返回的任意一個(gè)值比較。例如:salANY(1,2,3)等價(jià)于sal1,即大于任意一個(gè)就可以。
注:ANY運(yùn)算符必須與單行比較運(yùn)算符結(jié)合使用,并且返回行只要匹配子查詢(xún)的任何一個(gè)結(jié)果即可。
多列子查詢(xún)
多列子查詢(xún)和多行子查詢(xún)相同,只是使用多列子查詢(xún)的時(shí)候會(huì)有多列進(jìn)行匹配。
(4)集合運(yùn)算
為了合并多個(gè)select語(yǔ)句的結(jié)果,可以使用集合操作符號(hào)union,union all,intersect,minus。
union:該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí),會(huì)自動(dòng)去掉結(jié)果集中重復(fù)行
union all:該操作與union相似,但是它不會(huì)取消重復(fù)行,而且不會(huì)排序
intersect:使用該操作符用于取得兩個(gè)結(jié)果集的交集
minus:使用該操作符用于取得兩個(gè)結(jié)果集的差集,它只會(huì)顯示存在第一個(gè)集合中,而不存在第二個(gè)集合中的數(shù)據(jù)
總結(jié)為集合運(yùn)算就是將兩個(gè)或者多個(gè)結(jié)果集組合成一個(gè)結(jié)果集。
intersect ?交集 返回兩個(gè)查詢(xún)共有的記錄
union all ?并集 返回各個(gè)查詢(xún)的所有記錄,包括重復(fù)的記錄
union ? ? ?交集 返回各個(gè)查詢(xún)的所有記錄,不包括重復(fù)的記錄
MINUS ? 補(bǔ)集 返回第一個(gè)查詢(xún)檢查出的記錄減去第二個(gè)查詢(xún)檢索出來(lái)的記錄之后剩余的記錄
注意:當(dāng)使用集合操作的時(shí)候,查詢(xún)所返回的列數(shù)以及列的類(lèi)型必須匹配,列名可以不同。
(1)Distinct關(guān)鍵字
在Oracle中,可能出現(xiàn)若干相同的情況,那么可以用Distinct消除重復(fù)行
(2)多表查詢(xún)與單行子查詢(xún)可以實(shí)現(xiàn)相同的功能
查詢(xún)出銷(xiāo)售部(sales)下面的員工姓名,工作,工資
(3)顯示高于自己部門(mén)平均工資的員工信息
分析:
1.找到所有部門(mén)的平均工資
select deptno,avg(sal) from emp group by deptno;
2.找到所有人的工資信息
select ename,sal,deptno from emp;
3.把兩個(gè)結(jié)果集使用多表連接組合組合起來(lái)
select * from emp,(select deptno,avg(sal) avgsal from emp group by deptno) damao where emp.deptno=damao.deptno;
4.去掉低于平均工資的那些數(shù)據(jù)即可:
select * from emp,(select deptno,avg(sal) avgsal from emp group by deptno) damao where emp.deptno=damao.deptno and salavgsal;
(4)emp表介紹
字段? ? ? ? ? ? ? 類(lèi)型? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 描述
empno ? ? ? ? ?NUMBER(4) ? ? ? ? ? ? ? ? 表示雇員編號(hào),是唯一編號(hào)
ename ? ? ? ? ?VAECHAR2(10) ? ? ? ? ? 表示雇員姓名
job ? ? ? ? ? ? ? ?VARCHAR2(9) ? ? ? ? ? ? 表示工作職位
mgr ? ? ? ? ? ? ?NUMBER(4) ? ? ? ? ? ? ? ? 表示一個(gè)雇員的領(lǐng)導(dǎo)編號(hào)
hiredate ? ? ? DATE ? ? ? ? ? ? ? ? ? ? ? ? ? ?表示雇傭日期
sal ? ? ? ? ? ? ? NUMBER(7,2) ? ? ? ? ? ? ? 表示月薪,工資
comm ? ? ? ? ?NUMBER(7,2) ? ? ? ? ? ? ? 表示獎(jiǎng)金,或者稱(chēng)為傭金
deptno ? ? ? ? NUMBER(2) ? ? ? ? ? ? ? ? 部門(mén)編號(hào)
網(wǎng)站題目:oracle怎么查找結(jié)果,oracle 數(shù)據(jù)查詢(xún)
文章網(wǎng)址:http://redsoil1982.com.cn/article44/hsoshe.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供微信公眾號(hào)、靜態(tài)網(wǎng)站、網(wǎng)站排名、云服務(wù)器、企業(yè)建站、網(wǎng)站內(nèi)鏈
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話(huà):028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來(lái)源: 創(chuàng)新互聯(lián)
全網(wǎng)營(yíng)銷(xiāo)推廣知識(shí)