Oracle SQL * PLUS commands
1. Common Oracle SQL * Plus commands
(1) Environment commands
SET command format: SET <option> <value or switch status>
The SHOW command can display the SQL * Plus environment parameter values.
(2) common functions
SQL> set linesize x SQL> SET PAGESIZE y SET the row width to X and the page length to Y
The SQL> SET ECHO ON command is displayed ON the screen. The SQL> SET ECHO OFF command is not displayed on the screen.
SQL> SET PAUSE ON stops at the beginning of each page output. Press enter to continue scrolling.
SQL> SET TIME ON indicates that the current TIME is displayed before each command prompt.
SQL> SET NUMFORMAT: sets the default format of numbers displayed in the query results.
Store set file_name; Save System Variables
(3) Replacement command
Life order |
Definition |
DEFINE Variable = value |
Create definition variables and assign values |
DEFINE Variable |
Display variables, variable values, and Data Types |
DEFINE |
Show all predefined Variables |
Use the UNDEFINE command to delete the replace variable City.
You can use double & Symbol variables (&) to re-use a variable and do not want to re-enter the value (&&)
For example: SQL> SELECT EmployeeID, Name, Address, & Column
FROM Employees
Order by & Column DESC;
The set verify on/OFF command sets whether to display the execution replacement value. You can observe the SQL statements before and after the replacement variable value.
(4) Other commands
Spool spool_file_name off-line output
For example, output the query results of the Employees table and the Departments table to the file d: \ spool_test.prn.
SQL> Spool d: \ spool_test.prn
SQL> SELECT name, birthday, address FROM Employees;
SQL> SELECT distinct mentid, DepartmentName FROM orders;
SQL> Spool off
Desc table name display table structure
2. A typical SQL * Plus Configuration
Define_editor = vi
Set serveroutput on
Set trimspool on
Sets long 5000
Set pagesize 9999
Column plan_plus_exp format a80
Column global_name new_value gname
Set termout off
Define gname = idle
Column global_name new_value gname
Select lower (user) | '@' | substr (global_name, 1, decode (dot, 0, length (global_name), dot-1) global_name
From (
Select global_name, instr (global_name, '.') dot
From global_name
);
Set sqlprompt '& gname>'
Alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';
Set termout on