SQL plus commands (2)

Oracle SQL * plus is a client tool that interacts with oracle. In SQL * plus, you can run the SQL * plus command and the SQL * plus statement.

12. Set command: This command contains many sub-commands: SET system_variable value System_variable value can be one of the following clauses: APPI [NFO] {ON | OFF | text} ARRAY [SIZE] {15 | n} AUTO [COMMIT] {ON | OFF | IMM [EDIATE] | n} AUTOP [RINT] {ON | OFF} AUTORECOVERY [ON | OFF] AUTOT [RACE] {ON | OFF | TRACE [ONLY]} [EXP [LAIN] [STAT [ISTICS] BLO [CKTERMINATOR] {. | c} CMDS [EP] {; | c | ON | OFF} COLSEP {_ | text} COM [PATIBILITY] {V7 | V8 | NATIVE} CON [CAT] {. | c | ON | OFF} COPYC [OMMIT] {0 | n} COPYTYPECHECK {ON | OFF} DEF [INE] {& | c | ON | OFF} DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}] ECHO {ON | OFF} EDITF [ILE] file_name [. ext] EMB [EDDED] {ON | OFF} ESC [APE] {\ | c | ON | OFF} FEED [BACK] {6 | n | ON | OFF} FLAGGER {OFF | ENTRY | INTERMED [IATE] | FULL} FLU [SH] {ON | OFF} HEA [DING] {ON | OFF} HEADS [EP] {| c | ON | OFF} INSTANCE [instance_path | LOCAL] LIN [ESIZE] {80 | n} LOBOF [FSET] {n | 1} LOGSOURCE [pathname] LONG {80 | n} LONGC [HUNKSIZE] {80 | n} MARK [UP] HTML [ON | OFF] [HEAD text] [BODY text] [ENTMAP {ON | OFF}] [SPOOL {ON | OFF}] [PRE [FORMAT] {ON | OFF}] NEWP [AGE] {1 | n | NONE} NULL text NUMF [ORMAT] format NUM [WIDTH] {10 | n} PAGES [IZE] {24 | n} PAU [SE] {ON | OFF | text} RECSEP {WR [APPED] | EA [CH] | OFF} RECSEPCHAR {_ | c} SERVEROUT [PUT] {ON | OFF} [SIZE n] [FOR [MAT] {WRA [PPED] | WOR [D _ WRAPPED] | TRU [NCATED]}] SHIFT [INOUT] {VIS [visible] | INV [ISIBLE]} SHOW [MODE] {ON | OFF} SQLBL [ANKLINES] {ON | OFF} SQLC [ASE] {MIX [ED] | LO [WER] | UP [PER]} SQLCO [NTINUE] {> | text} SQLN [UMBER] {ON | OFF} SQLPRE [FIX] {# | c} SQLP [ROMPT] {SQL> | text} SQLT [ERMINATOR] {; | c | ON | OFF} SUF [FIX] {SQL | text} TAB {ON | OFF} TERM [OUT] {ON | OFF} TI [ME] {ON | OFF} TIMI [NG] {ON | OFF} TRIM [OUT] {ON | OFF} TRIMS [POOL] {ON | OFF} UND [ERLINE] {-| c | ON | OFF} VER [IFY] {ON | OFF} WRA [P] {ON | OFF} 1) set whether the current session will automatically submit the modified data SQL> SET AUTO [COMMIT] {ON | OFF | IMM [EDIATE] | n} 2) Whether to display the SQL statements being executed in the script when you run an SQL script using the start command SQL> SET ECHO {ON | OFF} 3). Whether to display the number of rows queried or modified by the current SQL statement SQL> SET FEED [BACK] {6 | n | ON | OFF} By default, only rows with more than 6 results are displayed. If set feedback 1, no matter how many All rows are returned. When it is off, the number of queried rows is not displayed. 4). Whether to display the column title SQL> SET HEA [DING] {ON | OFF} When set heading off, Column Titles are not displayed on each page, instead of blank rows. 5) set the number of characters that a row can hold SQL> SET LIN [ESIZE] {80 | n} If the output content of a row is greater than the number of characters that can be accommodated in a set row, the line is displayed. 6). Set the separation between pages SQL> SET NEWP [AGE] {1 | n | NONE} When set newpage 0, a small black box is displayed at the beginning of each page. When set newpage n is set, there are n blank lines between the page and the page. When set newpage none is set, there is no interval between the page and the page. 7). Replace the NULL value with the text value during display. SQL> SET NULL text 8). Set the number of rows on a page. SQL> SET PAGES [IZE] {24 | n} If it is set to 0, all the output content is a page, and the column title is not displayed. 9). Whether to display the output information using the DBMS_OUTPUT.PUT_LINE package. SQL> SET SERVEROUT [PUT] {ON | OFF} When writing a stored procedure, we sometimes use dbms_output.put_line to output necessary information After the serveroutput variable is set to on, the information can be displayed on the screen. 10) Whether to intercept the SQL statement when the length of the SQL statement is greater than LINESIZE. SQL> SET WRA [P] {ON | OFF} When the output line length is greater than the set line length (set with the set linesize n command), when set wrap On, more than the characters in the output line are displayed in another line. Otherwise, more than the characters in the output line are removed and not displayed. . 11). Whether to display the output content on the screen is mainly used in combination with SPOOL. SQL> SET TERM [OUT] {ON | OFF} When you use the spool command to output the content of a large table to a file, it will be very expensive to output the content on the screen. Set termspool off, the output content will only be saved in the output file, not displayed in The spool speed is greatly improved on the screen. 12) Remove unnecessary spaces behind each line in the SPOOL output. SQL> SET TRIMS [OUT] {ON | OFF} 13) display the execution time of each SQL statement Set TIMING {ON | OFF} 14. Modify the first string in the current row of SQL buffer C [HANGE]/old_value/new_value SQL> l 1 * select * from dept SQL> c/dept/emp 1 * select * from emp 15. Edit the SQL statement in SQL buffer EDI [T] 16. Displays SQL statements in SQL buffer. list n displays the nth row in SQL buffer. Limit indicates that the nth row is the current row. Line L [IST] [n] 17. Add one or more rows under the current row of SQL buffer I [NPUT] 18. Add the specified text to the current row of the SQL buffer. A [PPEND] SQL> select deptno, 2 dname 3 from dept; DEPTNO DNAME ------------------------ 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS SQL> L 2 2 * dname SQL> a, loc 2 * dname, loc SQL> L 1 select deptno, 2 dname, loc 3 * from dept SQL>/ DEPTNO DNAME LOC ------------------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 19. Save the SQL statement in SQL buffer to a file. SAVE file_name 20. Import SQL statements in a file into SQL buffer GET file_name 21. Execute the SQL statement that has just been executed again RUN Or / 22. Execute a stored procedure EXECUTE procedure_name 23. Connect to the specified database in SQL * plus CONNECT user_name/passwd @ db_alias 24. Set the top title of each report TTITLE 25. Set the end title of each report BTITLE 26. Write a comment REMARK [text] 27. output the specified information or an empty row to the screen. PROMPT [text] 28. Pause the execution process and wait for the user to respond. PAUSE [text] SQL> PAUSE Adjust paper and press RETURN to continue. 29. Copy data from one database to another (for example, copy data from one table to another) Database) COPY {FROM database | TO database | FROM database TO database} {APPEND | CREATE | INSERT | REPLACE} destination_table [(Column,...)] USING query SQL> COPY FROM SCOTT/TIGER @ HQ TO JOHN/CHROME @ WEST Create emp_temp Using select * FROM EMP 30. If you do not exit SQL * plus, execute an operating system command in SQL * plus: HOST SQL> host hostname This command may be supported in windows. 31. In SQL * plus, switch to the operating system command prompt and run the operating system command. Back to SQL * plus: ! SQL>! $ Hostname $ Exit SQL> This command is not supported in windows. 32. display the help of the SQL * plus command HELP How to install the Help file: SQL> @? \ Sqlplus \ admin \ help \ hlpbld. SQL? \ Sqlplus \ admin \ help \ helpus. SQL SQL> help index 33. display the SQL * plus System variable value or SQL * plus environment variable value Syntax SHO [W] option Where option represents one of the following terms or clses: System_variable ALL BTI [TLE] ERR [ORS] [{FUNCTION | PROCEDURE | package body | TRIGGER | VIEW | type body} [schema.] name] LNO PARAMETERS [parameter_name] PNO REL [statement] REPF [OOTER] REPH [EADER] SGA SPOO [L] SQLCODE TTI [TLE] USER 1). display the value of the current environment variable: Show all 2). displays the error information of the currently created function, stored procedure, trigger, package, and other objects. Show error If an error occurs when a function or stored procedure is created, you can use this command to view the errors and corresponding errors in that place. Information. 3). display the value of the initialization parameter: Show PARAMETERS [parameter_name] 4). display the database version: Show REL [Rules] 5). display the SGA size Show SGA 6). display the current user name Show user