Sqlplus Command usage Guide (iii)

Source: Internet
Author: User
Tags sqlplus

Sqlplus Command usage Guide (iii)

/*------------------------------------------------------------------------

Author: Ongyan

Time: 2004-3-9

Welcome reprint, please keep the author information

/*-----------------------------------------------------------------------------------------------------

(3) Break command

With break you can print out some of the content that the report needs, such as subtotals, duplicate column values do not appear, or insert a blank row for each group, and so on.

Clears the break setting, clear breaks.

Grammar:

Break

-----



Specifies where and how to make the format changes to a.



Bre[ak] [on report_element [action [action]]] ...



Where Report_element has the following syntax:

{column | expression | ROW | The



And where action has the following syntax:

[Ski[p] n | [Ski[p]] PAGE] [Nodup[licates] | Dup[licates]]

The description is as follows:

Sql>break on Deptno

The results obtained are shown in Figure 4



Figure 4

By setting the break command on a column, we removed the duplicate display of the DEPTNO value. Please carefully compare the absence of break. This usage is called the use of the break column. The columns in the break command should appear in the order by of the SQL statement, and if there are more than one column, there is also a break usage for multiple columns. As shown in Figure 5.

Description of the break parameter

¨skip[n], inserts n blank lines automatically before each grouped value appears

¨skip page, automatically swap pages before each group value appears

¨break on Row Skip[n], after each line, insert n blank lines.

¨nodup repeat display empty, DUP repeat also show.

Break is like defining an event, and after a change in the value, you can perform a specific action on the result of the query. Break and COMPUTE commands together, you can produce subtotals and other report effects.



Figure 5



(4) Compute command

Displays a row of statistics at the end of the result. If there are multiple compute, then only the last compute will work. Clear computes deletes all the set compute.

Grammar:

COMPUTE

-------



Calculates and prints summary lines, using various standard

Computations, on subsets of selected rows. It also lists all

COMPUTE definitions.



Comp[ute] [function [Lab[el] text] ...

of {Expr|column|alias} ...

on {expr|column|alias| Report| ROW} ...]



Compute and break are used together. Compute without a break statement has no effect. The general usage is as follows:

Break on Break_column

COMPUTE function LABEL label_name of Column column column

... On Break_column

The calculation operations performed by compute are mainly:


SUM

Computes the sum of the values in the column.

MINIMUM

Computes the minimum value in the column.

MAXIMUM

Computes the maximum value in the column.

Avg

Computes the average of the values in the column.

STD

Computes the standard deviation of the values in the column.

Variance

Computes the variance of the values in the column.

COUNT

Computes the number of non-null values in the column.

Number

Computes the number of rows in the column.


The following is an example of a compute calculation. As shown in Figure 6



Figure 6

Description of the COMPUTE parameter

¨label specifies a name for the calculation. such as compute sum LABEL ' total ' of the SAL on JOB, then in this line of subtotals, total is displayed, not the default SUM

You don't need to print any labels, you use dummy. The use of Figure 7,dummy is useful in Sql*plus report making.



Figure 7

To be Continued ...


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.