資源描述：
《Oracle 常用腳本和查詢命令》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫。
1、一、Prompt，pause，accept交互操作腳本1.輸入數(shù)值prompt顯示工資高于輸入值的員工信息pause'按繼續(xù)'acceptvar_valuenumberprompt'輸入工資:'select*fromempwheresal>&var_value;2.輸入字符串prompt顯示輸入姓名的員工信息pause'按繼續(xù)'acceptvar_valuecharprompt'輸入姓名：'select*fromempwhereename='&var_value';3.輸入日期rompt顯示晚于輸入日期參加工作的員工信息pause
2、'按繼續(xù)'acceptvar_valueDATEprompt'輸入日期：'select*fromempwherehiredate>'&var_value';二、格式設(shè)置腳本Setlinesize120setpagesize40TTitlecenter'SampleReport
3、ofemp'BTitleskip2center'EndofSampleReport
4、
5、ofemp'columnsalheadingsalarycolumnenameformatA12truncateheading'employee
6、name'breakondeptnosk
7、ip2select*fromemporderbydeptno;columnsalclearcolumnenameclearclearbreak;clearcompute;三、基于breakon的分組統(tǒng)計(jì)腳本（小計(jì)+總計(jì)）1、基于部門的分組小計(jì)和總計(jì)breakondeptnoskippageonreportcomputesumofsalondeptno;computesumofsalonreport;Selectdeptno,salfromemporderbydeptno;clearbreak;clearcompute;2、基于部門的分組小計(jì)TTitlecen
8、ter'EmployeeRecord
9、hitechcorp'BTitleright'ReportBy
10、SQL*PLUSstudent'breakondeptnoskip2computesumofsalondeptnoselectename,sal,deptnofromemporderbydeptno;3、統(tǒng)計(jì)函數(shù)：sum,avg,max,min,count,num四、報(bào)表案例腳本1、示例1setpagesize15setlinesize60TTitlecenter'工資報(bào)表'skip2BTitleskip2right'ReportBy會(huì)計(jì)部'columnen
11、ameheading'姓名'formatA12columnhiredheading'雇傭時(shí)間'columnsalheading'工資'format$9999.9columndeptnoheading'部門編號(hào)'columnsalformat$99,999breakondeptnoskippageonreportcomputesumofsalondeptnocomputesumofsalonreportcomputesumofhiredondeptnocomputesumofhiredonreportspoolE:salaryselectename,trun
12、c((sysdate-hiredate)/7)hired,sal,deptnofromemporderbydeptno;spooloffTTitleoffBTitleoffclearbreakclearcomputeTTitleoffBTitleoffclearbreakclearcompute2、示例2setlinesize140breakondeptnoskip2computesumofsalondeptno;Select*fromemporderbydeptno;computemaxofsalondeptno;Select*fromemporderby
13、deptno;computeminofsalondeptno;Select*fromemporderbydeptno;computeavgofsalondeptno;Select*fromemporderbydeptno;clearbreak;clearcompute;五、常用的查詢語句1.基本查詢selectempno,ename,sal,deptno,commfromempwherecommisnotnullandsal>1500;selectename,job,salfromempwherejobin('SALESMAN','CLERK')andsal
14、>1500;SELECTempno,ename,sa
