Progressive scan for you to explain a few basic sqlplus commands

Source: Internet
Author: User
Tags format count end header log sql sqlplus

This article helps you to fully understand the Sqlplus command.

Remark: Tell Sqlplus the next word is a comment, not an order.

Set Haedsep: The title separator identifies a character that tells Sqlplus to break a heading into two or more rows.

Ttitle: Sets the header header for each page of the report.

Btitle: Sets the tail caption for each page of the report.

Column: Gives sqlplus various instructions about the title, format, and processing of the column.

Break on: Tells Sqlplus to insert spaces between parts of the report, or to disconnect subtotals and grand totals.

Compute sum: Sqlplus calculates subtotals.

Set Linesize: Sets the maximum number of characters for a report's rows.

Set pagesize: Sets the maximum number of rows for the page.

Set NewPage: Sets the number of blank lines between pages.

Spool: Moves a report that is usually displayed on the screen into a file for printing.

/**/marks the beginning and end of a comment within a SQL item. Similar to remark.

--marks the beginning of an online comment within a SQL item. All content that begins at the end of the line is treated as a comment. Similar to remark.

Set pause: Causes the screen to appear paused between pages.

Save: Saves the SQL query you are building to a selected file.

Host: Sends commands to the main operating system.

Start or @: tells Sqlplus to execute instructions that have been stored in the file.

Edit: Allows you to quickly leave Sqlplus and enter the editor you choose.

Define_editor: Tell Sqlplus the name of the editor you chose.

Exit or quit: terminates Sqlplus.

A simple example of a report:

The following are the referenced contents:
Activity.lst
Sql>
Sql> Select Name, Title, Checkoutdate, Returneddate,
2 returneddate-checkoutdate as Daysout/*count days*/
3 from Bookshelf_checkout
4 ORDER by Name,checkoutdate;
Mon Aug Page 1
Checkout Log for 1/1/02-3/31/02
Days
NAME TITLE checkoutdate returneddate out
------------ --------       ------------ ------------ ----
Dorah TALBOT either/or 02-jan-02 10-jan-02 8.00
POLAR EXPRESS 01-feb-02 15-feb-02 14.00
Good DOG, CARL 01-feb-02 15-feb-02 14.00.
My Ledger 15-feb-02 03-mar-02 16.00
********************                                -------
Avg 13.00
EMILY TALBOT ANNE of GREEN Gables 02-jan-02 20-jan-02 18.00
Midnight MAGIC 20-jan-02 03-feb-02 14.00
HARRY POTTER and 03-feb-02 14-feb-02 11.00
The Goblet of FIRE
********************                                -------
Avg 14.33
FRED FULLER JOHN ADAMS 01-feb-02 01-mar-02 28.00
TRUMAN 01-mar-02 20-mar-02 19.00
********************                                -------
Avg 23.50
Gerhardt Kentgen Wonderful Life 02-jan-02 02-feb-02 31.00
Midnight MAGIC 05-feb-02 10-feb-02 5.00
The Mismeasure of 13-feb-02 05-mar-02 20.00
Mans
********************                                -------
Avg 18.67
JED HOPKINS innumeracy 01-jan-02 22-jan-02 21.00
To KILL A 15-feb-02 01-mar-02 14.00
Mockingbird
********************                              -------
Avg 17.50
PAT Lavay the SHIPPING NEWS 02-jan-02 12-jan-02 10.00
The Mismeasure of 12-jan-02 12-feb-02 31.00
Mans
********************                              -------
Avg 20.50
ROLAND BRANDT the SHIPPING NEWS 12-jan-02 12-mar-02 59.00
The discoverers 12-jan-02 01-mar-02 48.00
West with the NIGHT 12-jan-02 01-mar-02 48.00
********************                               -------
Avg 51.67
-------
Avg 22.58
From the Bookshelf
Sql>
Start Activity.sql
REM Bookshelf Activity
Set HEADSEP!
Ttitle ' Checkout Log for 1/1/02-3/31/02 '
Btitle ' from the Bookshelf '
Column Name format A20
Column Title format A20 word_wrapped
Column Daysout format 999.99
Column Daysout heading ' days! Out '
Break on Name Skip 1 on
Compute avg of Daysout on Name
Compute avg of Daysout on
Set Linesize 100
Set pagesize 60
Set NewPage 0
Set Feedback off
Spool Activity.lst
Select Name, Title, Checkoutdate, Returneddate,
Returneddate-checkoutdate as Daysout/**//*count days*/
From Bookshelf_checkout
Order BY Name,checkoutdate;
Spool off





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.