SQL*Plus break与compute的简单用法
来源：互联网 发布：dl领域的图像分割算法 编辑：程序博客网 时间：2024/05/24 22:42
在SQL*Plus提示符下输出求和报表，我们可以借助break与compute两个命令来实现。这个两个命令简单易用，可满足日常需求，其实质也相当于在编写SQL语句时使用分组及聚合函数。不同的是在报表中的分组的最下方或整个报表的最下方我们可以得到如sum,avg以及自定义的聚合字样。见下面的演示。
1、break的用法
a、获取帮助信息 --如果帮助不可用，需要安装SQL*Plus help，参考: SQL*PLus 帮助手册(SP2-0171) http://blog.csdn.net/robinson_0612/article/details/8852568 goex_admin@SYBO2SZ> help break BREAK ----- Specifies where changes occur in a report and the formatting action to perform, such as: - suppressing display of duplicate values for a given column - skipping a line each time a given column value changes (In iSQL*Plus, only when Preformatted Output is ON) - printing computed figures each time a given column value changes or at the end of the report. Enter BREAK with no clauses to list the current BREAK definition. BRE[AK] [ON report_element [action [action]]] ... where report_element has the following syntax: {column | expression | ROW | REPORT} and where action has the following syntax: [SKI[P] n | [SKI[P]] PAGE] [NODUP[LICATES] | DUP[LICATES]] The SKIP option is not supported in iSQL*Plusb、命令特性描述break 命令主要用于过滤重复列，正如单词所表达的意思及中断，也就是说中断显示重复的列。当下一行记录的上指定的列与上一行相同，不显示该列，否则显示该列。当使用break时，通常建议sql语句使用Order by 子句。可以基于order by子句使用多个列，同样break 也可以使用多个列。report_element表明可以基于列，表达式，行，以及report等多种不同类型来进行中断显示，也就是说break on对哪个进行分组。action则表示 skip[n],在每个分组的最后，自动跳过n个空行。 skip page, 在每个分组的最后，自动换页。 break on row skip[n],每一行后面跳过n个空行。 nodup 重复的显示空，dup重复的也显示。c、演示break用法--基于列deptno进行中断显示goex_admin@SYBO2SZ> break on deptnogoex_admin@SYBO2SZ> break --break用于查看当前break的设置信息break on deptno nodup--下面查询中，deptno列被中断显示goex_admin@SYBO2SZ> select * from emp order by deptno; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ----------------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10 7839 KING PRESIDENT 19811117 00:00:00 5100 7934 MILLER CLERK 7782 19820123 00:00:00 1400 7566 JONES MANAGER 7839 19810402 00:00:00 3075 20 7902 FORD ANALYST 7566 19811203 00:00:00 3100 7876 ADAMS CLERK 7788 19870523 00:00:00 1200 7369 SMITH CLERK 7902 19801217 00:00:00 900 7788 SCOTT ANALYST 7566 19870419 00:00:00 3100 7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30 7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0 7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300 7900 JAMES CLERK 7698 19811203 00:00:00 1050 7698 BLAKE MANAGER 7839 19810501 00:00:00 2950 7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 140014 rows selected.--指定skip参数为n，则每一个新的分组之后插入指定的行数，如下面的查询为1，则新分组后插入1空行goex_admin@SYBO2SZ> break on deptno skip 1goex_admin@SYBO2SZ> breakbreak on deptno skip 1 nodupgoex_admin@SYBO2SZ> select * from emp order by deptno; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ----------------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10 7839 KING PRESIDENT 19811117 00:00:00 5100 7934 MILLER CLERK 7782 19820123 00:00:00 1400 7566 JONES MANAGER 7839 19810402 00:00:00 3075 20 7902 FORD ANALYST 7566 19811203 00:00:00 3100 7876 ADAMS CLERK 7788 19870523 00:00:00 1200 7369 SMITH CLERK 7902 19801217 00:00:00 900 7788 SCOTT ANALYST 7566 19870419 00:00:00 3100 7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30 7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0 7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300 7900 JAMES CLERK 7698 19811203 00:00:00 1050 7698 BLAKE MANAGER 7839 19810501 00:00:00 2950 7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 140014 rows selected.--上面的查询结果中分组deptno 30之后也被跳过一行，所以显示的结果尾部与"14 rows selected"有两行间隙--下面基于row来分组，且插入1空行goex_admin@SYBO2SZ> break on row skip 1;goex_admin@SYBO2SZ> select * from dept; DEPTNO DNAME LOC---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON--此时设置pagesize为8goex_admin@SYBO2SZ> set pagesize 8goex_admin@SYBO2SZ> break on deptno skip page -->基于页面进行跳页--下面的查询基于deptno被分为了4个页面goex_admin@SYBO2SZ> select * from emp order by deptno; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ----------------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10 7839 KING PRESIDENT 19811117 00:00:00 5100 7934 MILLER CLERK 7782 19820123 00:00:00 1400 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ----------------- ---------- ---------- ---------- 7566 JONES MANAGER 7839 19810402 00:00:00 3075 20 7902 FORD ANALYST 7566 19811203 00:00:00 3100 7876 ADAMS CLERK 7788 19870523 00:00:00 1200 7369 SMITH CLERK 7902 19801217 00:00:00 900 7788 SCOTT ANALYST 7566 19870419 00:00:00 3100 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ----------------- ---------- ---------- ---------- 7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30 7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0 7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300 7900 JAMES CLERK 7698 19811203 00:00:00 1050 7698 BLAKE MANAGER 7839 19810501 00:00:00 2950 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ----------------- ---------- ---------- ---------- 7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400 3014 rows selected.--基于多列的break--下面的查询中除了基于deptno分组之外，还增加了基于job进行分组goex_admin@SYBO2SZ> break on deptno on job skip 1;goex_admin@SYBO2SZ> select * from emp order by deptno,job; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ----------------- ---------- ---------- ---------- 7934 MILLER CLERK 7782 19820123 00:00:00 1400 10 7782 CLARK MANAGER 7839 19810609 00:00:00 2550 7839 KING PRESIDENT 19811117 00:00:00 5100 7788 SCOTT ANALYST 7566 19870419 00:00:00 3100 20 7902 FORD 7566 19811203 00:00:00 3100 7876 ADAMS CLERK 7788 19870523 00:00:00 1200 7369 SMITH 7902 19801217 00:00:00 900 7566 JONES MANAGER 7839 19810402 00:00:00 3075 7900 JAMES CLERK 7698 19811203 00:00:00 1050 30 7698 BLAKE MANAGER 7839 19810501 00:00:00 2950 7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400 7521 WARD 7698 19810222 00:00:00 1350 500 7499 ALLEN 7698 19810220 00:00:00 1700 300 7844 TURNER 7698 19810908 00:00:00 1600 014 rows selected.2、compute的用法
a、获取帮助信息goex_admin@SYBO2SZ> help compute COMPUTE ------- In combination with the BREAK command, calculates and prints summary lines using various standard computations. Also lists all COMPUTE definitions. COMP[UTE] [function [LAB[EL] text] ... OF {expr|column|alias} ... ON {expr|column|alias|REPORT|ROW} ...] b、命令特性描述compute用于分组值计算指定的列上的数值，实际上等同于对分组列执行group by，然后调用聚合函数。function为常用的聚合函数，如sum,avg,maximum,minimum,std,count等等。of为指定的计算列，也就是说要计算哪一列。on为分组条件，基于哪个列，表达式，report，row等进行分组。compute通常结合break来用，否则相当于没有分组，聚合也就没有任何意义。--Author : Robinson--Blog : http://blog.csdn.net/robinson_0612c、演示compute用法goex_admin@SYBO2SZ> set pagesize 80goex_admin@SYBO2SZ> clear break -->清除break的设置breaks cleared goex_admin@SYBO2SZ> break on deptno skip 1goex_admin@SYBO2SZ> compute sum of sal on deptno -->基于deptno对sal求和goex_admin@SYBO2SZ> select * from emp order by deptno; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ----------------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10 7839 KING PRESIDENT 19811117 00:00:00 5100 7934 MILLER CLERK 7782 19820123 00:00:00 1400 ---------- ********** 9050 sum 7566 JONES MANAGER 7839 19810402 00:00:00 3075 20 7902 FORD ANALYST 7566 19811203 00:00:00 3100 7876 ADAMS CLERK 7788 19870523 00:00:00 1200 7369 SMITH CLERK 7902 19801217 00:00:00 900 7788 SCOTT ANALYST 7566 19870419 00:00:00 3100 ---------- ********** 11375 sum 7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30 7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0 7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300 7900 JAMES CLERK 7698 19811203 00:00:00 1050 7698 BLAKE MANAGER 7839 19810501 00:00:00 2950 7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400 ---------- ********** 10000 sumgoex_admin@SYBO2SZ> break on report skip 1 goex_admin@SYBO2SZ> compute sum of sal on report -->对整个report的sal进行求和goex_admin@SYBO2SZ> select * from emp order by deptno; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ----------------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10 7839 KING PRESIDENT 19811117 00:00:00 5100 10 7934 MILLER CLERK 7782 19820123 00:00:00 1400 10 7566 JONES MANAGER 7839 19810402 00:00:00 3075 20 7902 FORD ANALYST 7566 19811203 00:00:00 3100 20 7876 ADAMS CLERK 7788 19870523 00:00:00 1200 20 7369 SMITH CLERK 7902 19801217 00:00:00 900 20 7788 SCOTT ANALYST 7566 19870419 00:00:00 3100 20 7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30 7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0 30 7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300 30 7900 JAMES CLERK 7698 19811203 00:00:00 1050 30 7698 BLAKE MANAGER 7839 19810501 00:00:00 2950 30 7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400 30 ----------sum 3042514 rows selected. goex_admin@SYBO2SZ> compute sum avg of sal on report -->对整个report求和以及求平均goex_admin@SYBO2SZ> select * from emp order by deptno; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ----------------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10 7839 KING PRESIDENT 19811117 00:00:00 5100 10 7934 MILLER CLERK 7782 19820123 00:00:00 1400 10 7566 JONES MANAGER 7839 19810402 00:00:00 3075 20 7902 FORD ANALYST 7566 19811203 00:00:00 3100 20 7876 ADAMS CLERK 7788 19870523 00:00:00 1200 20 7369 SMITH CLERK 7902 19801217 00:00:00 900 20 7788 SCOTT ANALYST 7566 19870419 00:00:00 3100 20 7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30 7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0 30 7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300 30 7900 JAMES CLERK 7698 19811203 00:00:00 1050 30 7698 BLAKE MANAGER 7839 19810501 00:00:00 2950 30 7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400 30 ----------avg 2173.21429sum 3042514 rows selected.goex_admin@SYBO2SZ> compute sum avg of sal on deptno -->对deptno分组进行求和，同时求平均goex_admin@SYBO2SZ> break on deptno skip 1;goex_admin@SYBO2SZ> select * from emp order by deptno; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ----------------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10 7839 KING PRESIDENT 19811117 00:00:00 5100 7934 MILLER CLERK 7782 19820123 00:00:00 1400 ---------- ********** 3016.66667 avg 9050 sum 7566 JONES MANAGER 7839 19810402 00:00:00 3075 20 7902 FORD ANALYST 7566 19811203 00:00:00 3100 7876 ADAMS CLERK 7788 19870523 00:00:00 1200 7369 SMITH CLERK 7902 19801217 00:00:00 900 7788 SCOTT ANALYST 7566 19870419 00:00:00 3100 ---------- ********** 2275 avg 11375 sum 7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30 7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0 7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300 7900 JAMES CLERK 7698 19811203 00:00:00 1050 7698 BLAKE MANAGER 7839 19810501 00:00:00 2950 7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400 ---------- ********** 1666.66667 avg 10000 sum14 rows selected.goex_admin@SYBO2SZ> break on deptno skip 1;goex_admin@SYBO2SZ> compute sum of sal comm on deptno --对sal以及comm基于分组deptno同时求和goex_admin@SYBO2SZ> select * from emp order by deptno; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ----------------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10 7839 KING PRESIDENT 19811117 00:00:00 5100 7934 MILLER CLERK 7782 19820123 00:00:00 1400 ---------- ---------- ********** 9050 sum 7566 JONES MANAGER 7839 19810402 00:00:00 3075 20 7902 FORD ANALYST 7566 19811203 00:00:00 3100 7876 ADAMS CLERK 7788 19870523 00:00:00 1200 7369 SMITH CLERK 7902 19801217 00:00:00 900 7788 SCOTT ANALYST 7566 19870419 00:00:00 3100 ---------- ---------- ********** 11375 sum 7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30 7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0 7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300 7900 JAMES CLERK 7698 19811203 00:00:00 1050 7698 BLAKE MANAGER 7839 19810501 00:00:00 2950 7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400 ---------- ---------- ********** 10000 2200 sumgoex_admin@SYBO2SZ> compute avg of sal on deptno; --对sal列基于分组deptno求平均goex_admin@SYBO2SZ> compute sum of comm on deptno; --对comm列基于分组deptno求和goex_admin@SYBO2SZ> select * from emp order by deptno; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ----------------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10 7839 KING PRESIDENT 19811117 00:00:00 5100 7934 MILLER CLERK 7782 19820123 00:00:00 1400 ---------- ---------- ********** 3016.66667 avg sum 7566 JONES MANAGER 7839 19810402 00:00:00 3075 20 7902 FORD ANALYST 7566 19811203 00:00:00 3100 7876 ADAMS CLERK 7788 19870523 00:00:00 1200 7369 SMITH CLERK 7902 19801217 00:00:00 900 7788 SCOTT ANALYST 7566 19870419 00:00:00 3100 ---------- ---------- ********** 2275 avg sum 7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30 7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0 7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300 7900 JAMES CLERK 7698 19811203 00:00:00 1050 7698 BLAKE MANAGER 7839 19810501 00:00:00 2950 7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400 ---------- ---------- ********** 1666.66667 avg 2200 sum
更多参考
DML Error Logging 特性
PL/SQL --> 游标
PL/SQL --> 隐式游标(SQL%FOUND)
批量SQL之 FORALL 语句
批量SQL之 BULK COLLECT 子句
PL/SQL 集合的初始化与赋值
PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录
SQL tuning 步骤
高效SQL语句必杀技
父游标、子游标及共享游标
绑定变量及其优缺点
dbms_xplan之display_cursor函数的使用
dbms_xplan之display函数的使用
执行计划中各字段各模块描述
使用 EXPLAIN PLAN 获取SQL语句执行计划
- SQL*Plus break与compute的简单用法
- 【SQL*Plus】使用BREAK和COMPUTE在SQL*Plus中得到分组统计结果
- BREAK ON 与compute语句的使用技巧
- BREAK ON 与compute语句的使用技巧
- break,continue,break的用法与区别
- break,continue,break的用法与区别
- break,continue,break的用法与区别
- break,continue,break的用法与区别
- sql/plus 与 sql/plus的区别
- DataTable.Compute的用法
- sql Compute by语句用法
- compute和compute by的用法
- continue与break 的用法
- Break 与continue的用法
- break的作用与用法
- SQL Plus简单的操作
- T-SQL compute与聚集函数的注意细节
- SQL compute by 的使用
- 虚析构函数的学习心得
- Java 日期时间 Date类型,long类型,String类型表现形式的转换
- interview---在eBay的实习生活
- ORA-01502 state unusable錯誤成因和解決方法(一)
- 向量空间模型
- SQL*Plus break与compute的简单用法
- DLL的源文件的开头要添加#pragma comment (lib,"setupapi.lib")一条语句
- 新近手机测试工具速递
- OpenGL图形管线和坐标变换
- Cookie工作原理
- 无法加载 DLL "SQLite.Interop.DLL" 解决方案
- Linux下CentOS 6.3中安装与配置Jdk 1.6和Tomcat 6.0.32
- BCB提供的文件操作的库函数
- ASP.NET2.0 文本编辑器FCKeditor的冰冷之心
