Oracle 常用脚本和查询命令

《Oracle 常用脚本和查询命令》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、一、Prompt，pause，accept交互操作脚本1.输入数值prompt显示工资高于输入值的员工信息pause'按继续'acceptvar_valuenumberprompt'输入工资:'select*fromempwheresal>&var_value;2.输入字符串prompt显示输入姓名的员工信息pause'按继续'acceptvar_valuecharprompt'输入姓名：'select*fromempwhereename='&var_value';3.输入日期rompt显示晚于输入日期参加工作的员工信息pause

2、'按继续'acceptvar_valueDATEprompt'输入日期：'select*fromempwherehiredate>'&var_value';二、格式设置脚本Setlinesize120setpagesize40TTitlecenter'SampleReport

3、ofemp'BTitleskip2center'EndofSampleReport

4、

5、ofemp'columnsalheadingsalarycolumnenameformatA12truncateheading'employee

6、name'breakondeptnosk

7、ip2select*fromemporderbydeptno;columnsalclearcolumnenameclearclearbreak;clearcompute;三、基于breakon的分组统计脚本（小计+总计）1、基于部门的分组小计和总计breakondeptnoskippageonreportcomputesumofsalondeptno;computesumofsalonreport;Selectdeptno,salfromemporderbydeptno;clearbreak;clearcompute;2、基于部门的分组小计TTitlecen

8、ter'EmployeeRecord

9、hitechcorp'BTitleright'ReportBy

10、SQL*PLUSstudent'breakondeptnoskip2computesumofsalondeptnoselectename,sal,deptnofromemporderbydeptno;3、统计函数：sum,avg,max,min,count,num四、报表案例脚本1、示例1setpagesize15setlinesize60TTitlecenter'工资报表'skip2BTitleskip2right'ReportBy会计部'columnen

11、ameheading'姓名'formatA12columnhiredheading'雇佣时间'columnsalheading'工资'format$9999.9columndeptnoheading'部门编号'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