Simple oracle Installation and SQLPLUS usage

Once the oracle database is successfully installed, the related database information is displayed:
Global Database Name: oracle
System identifier (SID): oracle
Server parameter file name: c: \ oracle \ product \ 10.2.0 \ db_1/dbs/spfileoracle. ora
Database Control URL: 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 the C: \ oracle \ product \ 10.2.0 \ db_1 \ install \ portlist. ini file to obtain the corresponding OEM:
Http: // 210.45.216.146: 1158/em/
2. SQLPLUS Tool
SQLPLUS provides the following functions: Enter, run, and debug SQL statements and PL/SQL blocks; execute database management commands; process data, generate reports, and format query results.
1. Start SQLPLUS:
Scott/tiger @ oracle
2. Get help from the SQLPLUS command:
SQL> help index; // obtain all oracle commands
SQL> help list; // displays the help information of the list command.
3. Exit SQLPLUS (use the commit command to submit the transaction before exiting SQLPLUS ):
SQL> quit or SQL> exit;
4. Input three types of commands in SQLPLUS:
1) SQL command: used to operate information in the database
2) PL/SQL block: used to operate information in the database
3) SQLPLUS command: used to edit, save, run SQL commands, PL/SQL blocks, format query results, and customize the SQLPLUS environment.
1), 2) You can access the database and temporarily store the commands in the SQL Buffer during execution.
3) The database cannot be accessed, and the SQLPLUS command cannot be stored in the SQL Buffer.

5. Buffer command:
SQL> SELECT; // Save the SELECT statement to the buffer.
SQL> EDIT; // EDIT the content in the buffer
SQL> RUN or SQL> // content in the execution Buffer
SQL> CLEAR SCREEN // CLEAR the SCREEN and SCREEN buffer of SQLPLUS
6. Run the command
In SQLPLUS, you can run SQL statements and PL/SQL blocks in three ways: 1) Command Line Mode 2) SQL Buffer Mode 3) script file Mode
1) command line:
1.1) run the SQL command in the command line mode. You only need to enter a semicolon (;) after entering the SQL statement, or enter a forward slash (/) in a new line and press the Enter key.
SQL> SELECT ename, sal FROM emp
2 WHERE deptno = 20;
1.2) run the PL/SQL block in the command line mode. after entering the PL/SQL block, press the Enter key to forward the slash (/) in the new line.
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE ('Hello Oracle! ');
3 END;
4/
1.3) EXECUTE (or EXEC) command, you can EXECUTE a single PL/SQL statement, do not need to EXECUTE from the 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 currently stored in the SQL Buffer, and displays the running result,
And make the last row in the SQL Buffer into the current row
SQL> SELECT ename, sal FROM emp
2 WHERE deptno = 20
3;
ENAME SAL
--------------------
The SMITH 800
JONES 2975
SCOTT 1, 3000
ADAMS 1100
FORD 3000
SQL> RUN
1 SELECT ename, sal FROM emp
2 WHERE deptno = 20
3 *
ENAME SAL
--------------------
The SMITH 800
JONES 2975
SCOTT 1, 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 running result,
Without displaying SQL commands or PL/SQL blocks in the SQL Buffer, 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 is successfully completed.
SQL>/
HELLO, ORACLE!
The PL/SQL process is successfully completed.
(2.3) menu commands
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:
SELECT ename, sal FROM emp
WHERE deptno = & 1
AND sal> & 2;
Run 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 1, 3000
FORD 3000
3.2) @ command
The @ command is similar to the START command. The difference is that the @ command can be run either within the SQLPLUS session or in the command line,
The START command can only run in the SQLPLUS session.
7. Replace variable commands
1) replace a variable: The format is to add an & before the variable name, so that you are prompted to enter the replace data when running the SQL command,
Then run the SQL command according to the input data, such:
SQL> SELECT & a FROM emp
2 WHERE deptno = 20
3 AND job = '& B ';
Enter the value of a: ename
Original Value 1: SELECT & a FROM emp
New value 1: SELECT ename FROM emp
Enter the value of B: CLERK
Original Value 3: AND job = '& B'
New Value 3: AND job = 'cler'
ENAME
----------
SMITH
ADAMS
2) DEFINE a replacement variable: Use the DEFINE command to DEFINE a replacement variable, for example:
SQL> DEFINE a = CLERK
SQL> SELECT ename FROM emp
2 WHERE deptno = 20
3 AND job = '& ';
Original Value 3: AND job = '&'
New Value 3: AND job = 'cler'
ENAME
----------
SMITH
ADAMS
3) view replacement Variables
SQL> DEFINE
Define a = "CLERK" (CHAR)
4) Clear replacement Variables
SQL> UNDEFINE
8. communicate with users
You can use the PROMPT, PAUSE, and ACCEPT commands to communicate with users.
1) The PROMPT command is used to output PROMPT information so that you can understand the functions and running status of the script file.
2) The PAUSE command is used to PAUSE the running of the script file.
3) The ACCEPT command allows you to specify the type of the variable to replace (such as CHAR, NUMBER, DATE), and use the PROMPT option to specify the PROMPT information,
Use the HIDE option to HIDE input so that user input can replace variables.
Example:
Script File: D: \ test. SQL:
ACCEPT pwd PROMPT 'enter the password 'hide
PROMPT
The PROMPT displays the name of the XX employee in the XX Department
PROMPT =
Press <Enter> to continue with the PROMPT.
PAUSE
ACCEPT a number prompt 'Enter the Department :'
ACCEPT B CHAR PROMPT 'Enter the type of work :'
SELECT ename FROM emp
WHERE deptno = &
AND job = '& B ';
The script file runs as follows:
SQL> start d: \ test. SQL
Enter the password *********
Display the employee name of XX job type in XX Department
======================================
Press <Enter> to continue
Enter team: 20
Enter the job type: CLERK
Original Value 2: WHERE deptno = &
New Value: 2: WHERE deptno = 20
Original Value 3: AND job = '& B'
New Value 3: AND job = 'cler'
ENAME
----------
SMITH
ADAMS
9. bind variables
1) define binding Variables
You can use the VARIABLE command to define the VARIABLE to bind. When a VARIABLE is used in an SQL statement or PL/SQL block,
You must add a colon (:) before binding the variable. When assigning values directly to the bound variable, you must use the EXECUTE Command, for example:
SQL> VARIABLE no NUMBER
SQL> EXECUTE: no: = 7788
The PL/SQL process is successfully completed.
SQL> SELECT ename FROM emp WHERE empno =: no;
ENAME
----------
SCOTT
2) Output Bind Variable
Use the PRINT command to output the Bind Variable, for example:
SQL> PRINT no
NO
----------
7788
10. Customize the SQLPLUS Environment
SQL> SHOW ALL // view ALL environment variables
SQL> SHOW linesize pagesize
SQL> SET linesize 100 pagesize 24 // linesize indicates the row width. The default value is 80. pagesize indicates the number of lines displayed on each page. The default value is 24.
SQL> SHOW arraysize // arraysize is used to set the number of rows extracted from the database. The default value is 15.
SQL> SET arraysize 25
SQL> SHOW autocommit // autocommit is used to set whether to submit automatically. The default value is OFF.
SQL> SET autocommit on
SQL> SET colsep | // colsep is used to SET the delimiter between the selected columns. The default Delimiter is space.
SQL & gt; SELECT ename, sal FROM emp WHERE empno = 7788;
ENAME | SAL
---------- | ----------
SCOTT | 3000
SQL> SET heading off // heading indicates whether to display the column title. The default value is ON.
SQL & gt; SELECT ename, sal FROM emp WHERE empno = 7788;
SCOTT | 3000
SQL> SET serveroutput on // SEVEROUTPUT is used to control whether the output of the stored procedure is displayed. The default value is OFF.
SQL> EXEC dbms_output.put_line ('Hello oracle ');
Hello oracle
The PL/SQL process is successfully completed.
SQL> SET sqlprompt inputSQL> // sqlprompt is used to SET the SQLPLUS command prompt.
InputSQL>
SQL> SET time on // whether to display the system time before the SQLPLUS command prompt. The default value is OFF.
19:02:35 SQL>

SQL> SET underline = // underline is used to SET the underline character. The default value is "_"
SQL> SELECT count (*) FROM emp;
COUNT (*)
============
14
SQL> store set d: \ setfile. SQL // Save the current SQLPLUS Environment
File D: \ setfile. SQL has been created.
11. format the query results
1) format Columns
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 = 20 AND sal> 2500;
Employee | monthly salary
======|================================
JONE | ￥2975.00

SCOT | ￥3000.00
T |
FORD | ￥3000.00
SQL> COLUMN sal CLEAR // restore the COLUMN format
2) restrict duplicate rows
When you limit duplicate rows, you can also insert n blank rows between groups. The syntax is as follows:
Break on break_column SKIP n
Go to the new page, and the syntax is:
Break on break_column SKIP PAGE
Insert n blank lines after each line. The syntax is as follows:
Break on row skip n
Insert n blank rows after the report. The syntax is as follows:
Break on report 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
------------------------------
10 CLARK 2450
MILLER 1300
20 ADAMS 1100
The SMITH 800
30 WARD 1250
MARTIN 1250
JAMES 950
TURNER 1500
ALLEN 1600
Nine rows have been selected.