Oracle installation and Sqlplus simple usage _oracle

when an Oracle database is installed successfully, related database information is displayed:
Global Database name: Oracle
System identifier (SID): Oracle
Server parameter filename: C:\oracle\product\10.2.0\db_1/dbs/spfileoracle.ora
The Database control URL is http://210.45.216.146:1158/em
Database account: Sys,system,dbsnmp,sysman
Password: Oracle
The Isql*plus URL is:
Http://210.45.216.146:5561/isqlplus
The Isql*plus DBA URL is:
Http://210.45.216.146:5561/isqlplus/dba
View C:\oracle\product\10.2.0\db_1\install\portlist.ini files to obtain the appropriate OEM for:
http://210.45.216.146:1158/em/
Two Sqlplus tools
The main functions of sqlplus are: input, run and debug SQL statements and Pl/sql blocks; Execute management database commands; Process data, generate reports, format query results
1. Start Sqlplus:
Scott/tiger@oracle
2. Get help for the Sqlplus command:
sql> Help Index; Get all Oracle Commands
sql> Help list; Displays help for the list command
3. Exit Sqlplus (You should commit the transaction using the commit command before exiting Sqlplus):
Sql> quit or sql> exit;
4. Enter three kinds of commands in Sqlplus:
1 SQL command: Used to manipulate information in the database
2) Pl/sql block: Used to manipulate information in the database
3 sqlplus command: For editing, saving, running SQL commands, pl/sql blocks, formatting query results, customizing sqlplus environment, etc.
1), 2 the database can be accessed, and the command is temporarily stored in the SQL buffer when executed.
3 The database cannot be accessed, nor can the Sqlplus command be stored in the SQL buffer.

5. Buffer command:
Sql>select; To save a select to a buffer
sql>edit; Edit the contents of the buffer
Sql>run or sql>///execute the contents of the buffer
Sql>clear screen//clear sqlplus screens and screen buffers
6. Run command
There are three ways to run SQL statements and Pl/sql blocks in Sqlplus (1) command-line mode 2) SQL buffer mode 3 script file Way
1 The command line mode:
1.1 To run the SQL command at the command line, simply enter the semicolon (;) after entering the SQL statement, or enter a forward slash (/) on the new line, and press ENTER
sql> SELECT ename,sal from emp
2 WHERE deptno = 20;
1.2 To run the Pl/sql block in the command line, just enter the front slash (/) on the new line after entering the Pl/sql block, and press ENTER
Sql> SET Serveroutput on
Sql> BEGIN
2 Dbms_output. Put_Line (' Hello oracle! ');
3 END;
4/
1.3) Execute (or exec) command, which executes a single PL/SQL statement and does not need to be executed from a buffer or script file.
Sql> SET Serveroutput on
Sql> EXEC Dbms_output. Put_Line (' Hello ');
Hello
2) SQL buffer mode:
2.1 Run command: Displays and runs the SQL statement or Pl/sql block that is currently stored in the SQL buffer, showing the results of the run.
And make the last row in the SQL buffer the current line
Sql> SELECT ename, sal from EMP
2 WHERE Deptno = 20
3;
Ename SAL.
---------- ----------
SMITH 800
JONES 2975
SCOTT 3000
ADAMS 1100
FORD 3000
Sql> RUN
1 SELECT ename, sal from EMP
2 WHERE Deptno = 20
3*
Ename SAL.
---------- ----------
SMITH 800
JONES 2975
SCOTT 3000
ADAMS 1100
FORD 3000
2.2/Command: Run the SQL command or Pl/sql block currently stored in the SQL buffer to display the results of the operation.
The SQL command or Pl/sql block in the SQL buffer is not displayed, and the last row in the SQL buffer is not the current row
Sql> SET Serveroutput on
Sql> BEGIN
2 Dbms_output. Put_Line (' hello,oracle! ');
3 END;
4/
hello,oracle!
The PL/SQL process has completed successfully.
Sql>/
hello,oracle!
The PL/SQL process has completed successfully.
2.3) menu command
Select "File"-> "Run", which is the same as the Run command
3 Script File Mode
3.1) Start command
Format: START filename[.ext] [arg1 arg2 arg3 ...]
Script file: D:\test.sql contents are as follows:
SELECT ename, Sal from EMP
WHERE Deptno = &1
and Sal > &2;
Using the start command:
sql> START D:\test.sql 20 2500
Original value 2:where Deptno = &1
New value 2:where Deptno = 20
Original value 3:and sal > &2
New value 3:and sal > 2500
Ename SAL.
---------- ----------
JONES 2975
SCOTT 3000
FORD 3000
3.2) @ command
The @ command is similar to the start command, except that the @ command can run either within the Sqlplus session or at the command line,
The start command can only run within the Sqlplus session.
7. Replace variable command
1 Use a substitution variable: The format is to precede the variable name with an ampersand to prompt the user for alternate data when the SQL command is run.
Then run the SQL command by entering data, such as:
sql> SELECT &a from emp
2 WHERE Deptno = 20
3 and job = ' &b ';
Enter value for a: ename
Original value 1:select &a from emp
New value 1:select ename from emp
Enter value for B: Clerk
Original value 3:and job = ' &b '
New value 3:and job = ' Clerk '
Ename
----------
SMITH
Adams
2 Define substitution variables: Define substitution variables in advance with the Define command, such as:
sql> DEFINE A = Clerk
sql> SELECT ename from emp
2 WHERE Deptno = 20
3 and job = ' &a ';
Original value 3:and job = ' &a '
New value 3:and job = ' Clerk '
Ename
----------
SMITH
Adams
3 View Substitution variables
Sql> DEFINE A
DEFINE A = "Clerk" (CHAR)
4) Clear the substitution variable
Sql> Undefine A
8. Communication with users
You can use the prompt command, pause command, accept command to communicate with the user
1 The prompt command is used to output the hint information so that the user can understand the function and operation of the script file
2) The pause command is used to suspend script files from running
3 The Accept command allows the user to specify the type of the substitution variable (such as Char,number,date), specifying the hint information with the prompt option,
Hide the input with the Hide option to make it easy for the user to enter substitution variables
Examples are as follows:
Script file: D:\test.sql contents are as follows:
ACCEPT pwd PROMPT ' Please enter password ' HIDE
PROMPT
PROMPT Show employee Name of XX Department xx work type
PROMPT ==========================
PROMPT Press <Enter> Key to continue
PAUSE
ACCEPT a number PROMPT ' Please enter Department: '
ACCEPT b CHAR PROMPT ' Please enter a job type: '
SELECT ename from EMP
WHERE Deptno = &a
and job = ' &b ';
The script file runs the result:
Sql> START D:\test.sql
Please enter the password *********
Show employee name of XX department
==========================
Press the <Enter> key to continue
Please input department: 20
Please enter a job: Clerk
Original value 2:where Deptno = &a
New value 2:where Deptno = 20
Original value 3:and job = ' &b '
New value 3:and job = ' Clerk '
Ename
----------
SMITH
Adams
9. Binding variables
1) Define binding variables
You can use the variable command to define a binding variable. When you use a binding variable in an SQL statement or a pl/sql block,
You must add a colon to the binding variable (:). When assigning a value directly to a bound variable, you need to use the Execute command, for example:
Sql> VARIABLE No number
sql> Execute:no: = 7788
The PL/SQL process has completed successfully.
sql> SELECT ename from emp WHERE empno =: no;
Ename
----------
SCOTT
2) Output binding variable
Use the Print command to output bound variables, for example:
Sql> PRINT No
NO
----------
7788
10. Custom Sqlplus Environment
Sql> Show All//view all environment variables
Sql> Show Linesize pagesize
sql> SET linesize pagesize//linesize represents line width, and the default is 80,pagesize indicates the number of rows per page, default is 24
Sql> show ArraySize//arraysize is used to set the number of rows fetched from the database at one time, by default 15
sql> SET ArraySize 25
Sql> Show Autocommit//autocommit is used to set whether Autocommit is automatic, and the default is off
Sql> SET autocommit on
sql> SET Colsep | COLSEP is used to set the separator between the selected columns, with the default space
Sql> SELECT ename, sal from emp WHERE empno = 7788;
ename | SAL
----------|----------
SCOTT | 3000
Sql>set heading off//heading Indicates whether column headings are displayed, default to On
Sql> SELECT ename, sal from emp WHERE empno = 7788;
SCOTT | 3000
Sql> SET Serveroutput on//severoutput controls whether the output of the stored procedure is displayed, and the default is off
sql> EXEC dbms_output.put_line (' Hello Oracle ');
Hello Oracle
The PL/SQL process has completed successfully.
Sql> set sqlprompt inputsql>//sqlprompt command prompt for setting sqlplus
Inputsql>
Sql>set time on//time whether to display system times before the Sqlplus command prompt, by default set to OFF
19:02:35 sql>

Sql>set underline =//underline is used to set the underscore character, the default value is "_"
Sql>select Count (*) from EMP;
COUNT (*)
==========
14
Sql>store SET D:\setfile.sql//Save the current sqlplus environment
The file D:\setfile.sql was created
11. Format Query Results
1) Format column
Sql> COLUMN ename HEADING employee justify CENTER FORMAT A4
sql> COLUMN sal HEADING Monthly salary justify CENTER FORMAT L99999.99
Sql> SELECT ename, sal from emp WHERE deptno = and sal > 2500;
Employee | Monthly salary
====|===================
jone| ￥2975.00

Scot| ￥3000.00
T |
ford| ￥3000.00
Sql>column sal Clear/Restore column format
2) Limit Duplicate rows
When you limit duplicate rows, you can also insert n blank rows between groups, with the syntax:
Break on Break_column SKIP n
Run to a new page whose syntax is:
Break on Break_column SKIP PAGE
Insert n blank rows after each line, with the syntax:
Break on ROW SKIP n
After the report, insert n blank rows with the following syntax:
Break on "On" # SKIP N
Sql> Break on Deptno SKIP 1
Sql> SELECT deptno, ename, sal from emp WHERE Sal < 2500
2 order by Deptno;
DEPTNO ename SAL
---------- ---------- ----------
Ten CLARK 2450
MILLER 1300
ADAMS 1100
SMITH 800
WARD 1250
MARTIN 1250
JAMES 950
TURNER 1500
ALLEN 1600
9 rows have been selected.