Oracle Sqlplus Command Detailed

Points of knowledge involved
A, the use of substitution variables with a &
b, substitution variable usage with two &
C,define command usage
d, accept command usage
E, custom Sql*plus environment
F. Save custom results in the Glogin.sql file
G,sqlplus Edit command

A, the use of substitution variables with a &
1), use a variable value with a & number to prompt the user to enter a value.
eg
Sql> SELECT empno, ename, Sal, deptno from emp WHERE empno = &empno;
Enter the value of Empno: 7369
Original value 1:select empno, ename, Sal, deptno from emp WHERE empno = &empno
New values 1:select empno, ename, Sal, deptno from emp WHERE empno = 7369

EMPNO ename SAL DEPTNO
———- ———- ———- ———-
7369 SMITH 8888 20

2), replace strings and date values in variables
Replaces the date and string values in a variable with a single quotation mark.
eg
Sql> SELECT ename, Deptno, sal*12 from emp WHERE job= ' &job ';
Enter the value of the job: ANALYST
Original value 1:select ename, Deptno, sal*12 from emp WHERE job= ' &job '
New values 1:select ename, Deptno, sal*12 from emp WHERE job= ' ANALYST '

Ename DEPTNO sal*12
———- ———- ———-
SCOTT 20 106656
FORD 20 36000

3), run-time specified column name, expression, text
SELECT empno, ename, Job, &column_name
From EMP
WHERE &condition
ORDER by &order_column;
Enter a value for column_name: Sal
Original value 1:select empno, ename, Job, &column_name
New values 1:select empno, ename, Job, Sal
Enter the value of the condition: sal>=3000
Original Value 3:where &condition
New Value 3:where sal>=3000
Enter the value of the Order_column: ename
Original Value 4:order by &order_column
New Value 4:order by ename

     EMPNO ename      job               SAL
———-———-——— ———-
      7902 FORD        analyst        
       7839 king       president       
      7788 scott      analyst          8888
      7369 smith      clerk           8888

B, substitution variable usage with two &
use a variable with two & to make the variable reusable, without having to alert the user to input each time it is used.
sql> SELECT empno, ename, Job, &&column_name from emp ORDER by &column_name;
Enter the value of column_name:  deptno
Original value     1:select empno, ename, Job, &&column_name from EMP OR DER by &column_name
New value     1:select empno, ename, Job, deptno from emp ORDER by Deptno

     EMPNO ename      job            DEPTNO
———-———-——— ———-
      7782 clark       manager          
       7839 king       president         10
      7934 miller     clerk             
...

C, define command usage
To create a user variable of type char, enclose the variable in single quotation marks when defining a variable that contains a space.
eg
sql> Define Deptname = Sales
Sql> define Deptname; – View the variable
DEFINE deptname = "Sales" (CHAR)
Sql> SELECT * FROM dept WHERE dname=upper (' &deptname ');
Original Value 1:select * from dept WHERE dname=upper (' &deptname ')
New Value 1:select * FROM dept WHERE dname=upper (' Sales ')

DEPTNO dname LOC
———- ————– ————-
SALES CHICAGO

Description: A variable will remain in the defined state until it is cleared or left sql*plus using the Undefine command.

d, accept command usage
Create customizable user prompts when receiving user input.
eg
– You are prompted to enter a value to Dept,dept is a variable, you can use the Define command to view the variable
Sql> Accept Dept Prompt ' provide the department name: '
Provide the department Name:sales
sql> Define Dept; – View the variable
DEFINE DEPT = "Sales" (CHAR)
Sql> SELECT * FROM dept WHERE dname = UPPER (' &dept ');
Original Value 1:select * FROM dept WHERE dname = UPPER (' &dept ')
New Value 1:select * FROM dept WHERE dname = UPPER (' Sales ')

DEPTNO dname LOC
———- ————– ————-
SALES CHICAGO

E, custom Sql*plus environment
Use the SET command to control the current session.
Syntax: SET system_variable value
Eg, sql> set arraysize 20;
You can use the show command to view set settings.
eg, sql> show arraysize
ArraySize 20
Description: For the SET command, see "The Oracle SET command in detail. txt"

F. Save custom results in the Glogin.sql file
The Glogin.sql file contains standard set commands and other commands that need to be executed at login, and we can change Glogin.sql to include other set commands.
For example: Set the linesize of the sqlplus environment to 300 and let this variable take effect permanently at $oracle_home/Sqlplus/admin/glogin.sql (D:\dev\oracle\product\ 10.2.0\db_1\sqlplus\admin) file, add the following: Set Linesize 300
A
G, sqlplus Edit command
1), perform the DEF command to view the settings of the editor:
Sql> def

2), summarized under:
L – Column SQL
n – Toggle Active row (n for row number)
A – Increase after activity line (append)
I – Insert new row after active row increment (input)
C – Replacement (change)
eg, c/emp_name/emp_age/
Del N – Delete row n
/– Execute SQL
eg
Sql> Select Deptno, dname from dept;

DEPTNO dname
———- ————–
Ten ACCOUNTING
About
SALES
OPERATIONS

sql> I where deptno>20
Sql> L
1 Select Deptno, dname from Dept
DEPTNO>20 where
Sql>/

DEPTNO dname
———- ————–
SALES
OPERATIONS

Sql> del 2
Sql> L
1* Select Deptno, dname from Dept
Sql> a WHERE deptno>30
1* Select Deptno, dname from dept where deptno>30
Sql> L
1* Select Deptno, dname from dept where deptno>30
Sql>/

DEPTNO dname
———- ————–
OPERATIONS