Oracle SQL * PLUS commands

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