Basic Sqlplus command

Here are some Sqlpus commands

Remark Comment lines
Set HEADSEP header delimiter
Ttitle Header Title
Btitle Tail Title
column headings and columns format settings
Break to insert spaces between parts of the report or to disconnect subtotals and grand totals
Compute SUM calculation subtotal
Set Linesize maximum number of characters in a row
Set pagesize number of rows in a page

The number of empty rows between set NewPage pages
Spool writing a report to a file
/**/Multi-line comments
--Single line comment
Pause between set pause pages
Save saves the created SQL query to the file
Host executes external commands
Start or @ Execute Sqlplus script
Edit into the custom editor
Define_editor Defining editors
Exit or quit Quit Sqlplus

An example

REM Bookshelf Activity Report

Set HEADSEP!

Ttitle ' Checkout Log for 1/1/02-3/31/2 '
Btitle ' from the Bookself '

Column Name format A20
Column Title format A20 word_wrapped
Column Daysout format 999.99
Column Daysout heading ' day! Out '

Break on Name Skip 1 on report
Compute avg of Daysout on Name
Compute avg of Daysout on report

Set Linesize 80
Set pagesize 60
Set NewPage 0
Set Feedback off

Spool Activity.lst

Select Name,title,checkoutdate,returneddate,
Returneddate-checkoutdate as Dayout
From Bookself_checkout
Order BY Name,checkoutdate;

Spool off

The basic structure of this script:

Comments

Title setting

Column headings and format settings

Subtotal and total set each part interval setting

Page settings

Print to File

Select Data Alter procedure to edit the stored procedure.
ALTER TABLE adds table columns, redefine table columns, and change the given table store allocations.
Analyze collects performance statistics for database objects and is sent to the basic optimizer.
ALTER TABLE ADD constraint adds a constraint on an existing table.
The CREATE table creates tables.
Create INDEX.
Drop INDEX to delete indexes.
drop table deletes tables.
Grant grants permissions and roles to users or roles.
Revoke the role from the user or database role.

Build table and Delete table:sql> CREATE Table A (cola char (1));

Table created.

sql> drop table A;

Table dropped.

Sql> CREATE TABLE Newhire (
2 lname VARCHAR2 (30),
3 state_cd varchar (2),
4 HireDate Date,
5 Salary number (8,2));

Table created.

Sql> desc Newhire
Name Null? Type
----------------------------------------- -------- ----------------------------
LNAME VARCHAR2 (30)
STATE_CD VARCHAR2 (2)
HireDate DATE
SALARY Number (8,2)

You need to specify the data type of the column in the process of building the table, and the normal Oracle data types are:
char (size) holds the fixed character data;
varchar (size) stores variable-length character data;
Number (I,D) holds numeric data, I represents the total number of digits, and D represents the number of decimal digits;
Blob binary large object;
Raw (size) raw binary data;
Date of storage;
Long stores variable-length character data.

DML data manipulation statements: Includes SQL statements that begin with SELECT, INSERT, UPDATE, delete.
Here is an example of inserting data:
sql> INSERT into Newhire values (' Corey ', ' MA ', ' 01-jan-01 ', ' 20000 ');

1 row created.

Sql> INSERT into state (State_cd,state_name) VALUES (' MA ', ' Massachusetts ');

1 row created.

The SELECT statement is used to retrieve data:
Sql> select * from Newhire;

LNAME ST hiredate SALARY
------------------------------ -- --------- ----------
Corey MA 01-jan-01 20000
Calisi NJ 10-jun-01 30000

The SELECT statement consists of five parts:
Select (must) give the retrieved column;
From (must) give the source of the data (name of one or more Oracle tables);
where (optional) set conditions;
Group BY (optional) summary data;
Order BY (optional) sort data.

Retrieving the data will inevitably involve formatting the output, sqlplus using the column statement to format the SELECT statement output. The column command consists of four parts:

Columns < column names > specify the columns to format;
Format < Application mask > Specify the size of the input and the mask used;
Heading ' title ' specifies column headings;
WRAP/TRUNC specifies how the overflow is handled: trunc truncation, wrap overflow.

sql> column lname format A10;
Sql> select * from Newhire;

LNAME ST hiredate SALARY
---------- -- --------- ----------
Corey MA 01-jan-01 20000
Calisi NJ 10-jun-01 30000

The following example headings are set and overflow handled.
sql> column HireDate format a5 heading ' DATE ' wrap;
Sql> select * from Newhire;

LNAME ST DATE SALARY
---------- -- ----- ----------
Corey MA 01-ja 20000
N-01

Calisi NJ 10-ju 30000
N-01


sql> column HireDate format a5 heading ' DATE ' trunc;
Sql> select * from Newhire;

LNAME ST DATE SALARY
---------- -- ----- ----------
Corey MA 01-ja 20000
Calisi NJ 10-ju 30000

The following is a definition of a common format mask:
Format A10 trunc a The right data determines the width of the display;

Basic Sqlplus command