Simple usage of SQL * Plusbreak and compute

Run the break and compute commands to output the sum report at the SQL * Plus prompt. These two commands are easy to use and can meet daily needs.

Run the break and compute commands to output the sum report at the SQL * Plus prompt. These two commands are easy to use and can meet daily needs.

Run the break and compute commands to output the sum report at the SQL * Plus prompt. These two commands are easy to use and can meet daily needs. Their essence is equivalent to using grouping and Aggregate functions when writing SQL statements. The difference is that we can get sum, avg, and custom aggregation words at the bottom of the group in the report or at the bottom of the entire report. See the demo below.

1. break usage

A. Get help information
-- If the help is unavailable, You Need To Install SQL * Plus help, refer to: SQL * PLus help manual (SP2-0171)
Goex_admin @ SYBO2SZ> help break

BREAK
-----

Specifies where changes occur in a report and the formatting
Action to perform, such:
-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 clses 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 * Plus

B. Command features
The break command is mainly used to filter duplicate columns, just as the meaning and interruption of words are expressed, that is, the interruption displays duplicate columns.
The column specified on the current record is the same as the previous one. This column is not displayed; otherwise, this column is displayed.
When break is used, we recommend that you use the Order by clause in SQL statements. Multiple columns can be used based on the order by clause, and multiple columns can also be used for break.
Report_element indicates that interruption display can be performed based on different types of columns, expressions, rows, and reports. That is to say, which type can be grouped by break on.
Action indicates
Skip [n] automatically skips n blank lines at the end of each group.
Skip page, which is automatically updated at the end of each group.
Break on row skip [n]. n blank lines are skipped after each row.
Duplicate nodup display is empty, and duplicate dup display is also displayed.

C. Demonstrate break usage
-- Display interruption Based on column deptno
Goex_admin @ SYBO2SZ> break on deptno
Goex_admin @ SYBO2SZ> break -- break is used to view the current break settings.
Break on deptno nodup

-- In the following query, the deptno column is interrupted.
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 1400

14 rows selected.

-- If the skip parameter is set to n, the specified number of rows is inserted after each new group. If the following query is set to 1, a blank row is inserted after the new group.
Goex_admin @ SYBO2SZ> break on deptno skip 1
Goex_admin @ SYBO2SZ> break
Break on deptno skip 1 nodup

Goex_admin @ SYBO2SZ> select * from emp order by deptno;