Oracle's SQL * PLUS is a client tool that interacts with Oracle. In SQL * Plus, you can run the SQL * plus command to the SQL * PLUS statement. We usually say DML, DDL, DCL statements are SQL * PLUS statements, they can be saved in a memory area called SQL Buffer, and can only save a recently executed SQL statement, we You can modify the SQL statement saved in SQL Buffer and then execute again, SQL * Plus is generally dealing with the database. In addition to the SQL * PLUS statement, other statements executed in SQL * Plus We call SQL * Plus commands. After they are executed, they are not saved in the memory area of SQL Buffer, which is generally used to format the results of the output to make reports. Let's introduce some common SQL * Plus commands: 1. Perform a SQL script file SQL> Start file_name SQL> @ file_name We can save multiple SQL statements in a text file, so when you want to perform all of this file When the SQL statement, uses any of the above commands, which is similar to the batch of DOS. 2. Edit SQL> Edit 3. Re-run the last run SQL statement SQL> / 4. Output displayed content to specified file SQL> spool file_name All content on the screen is included in this file , Including the SQL statement you entered. 5. Turn off Spool Output SQL> Spool Off Only the output content will be seen in the output file. 6. Display a table structure SQL> Desc Table_name 7. col command: The main formatted column is displayed.
There are many options that are as follows: col [umn] [{colorn | evr} [option ...]] Option options can be as follows: Ali [As] Alias Cle [Ar] fold_a [fter] fold_b [ EFORE] TEXT JUS [TIFY] {L [EFT] | C [ENTER] | C [ENTRE] | R [IGHT]} like {expr | alias} newl [ine] new_v [alue ] Variable NoPri [NT] | PRI [NT] NUL [L] TEXT OLD_V [Alue] Variable On | Off WRA [PPED] | WOR [D_Wrapped] | TRU [ncated] 1). Change the default column title Column Column_name Heading Column_Heading for Example: SQL> Select * from Dept; Deptno DNAME LOC ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ - - --------- 10 Accounting New York SQL> Col Loc Heading Location SQL> Select * from Dept; Deptno DName Location -------------------------------------------------------------------------------------------------------------- ---------------------------- 10 Accounting New York 2). Change the column name ENAME to the new column name Employe Name and will Column names on two lines: SQL> Select * from Emp Department Name Salry -------------------------- 10 AAA 11 SQL> Column Ename Heading 'EMPLOYEE | NAME 'SQL> Select * from Emp Employee Department Name Salry ---------- ------------------ 10 AAA 11 NOTE: The col Heading Turn INTO TWO LINES from One Line. 3). Change the length of the column: for [MAT] Format SQL> SELECT Empno, Ename, Job from Emp; Empno Ename Job ---------- ----- ----- --------- 7369 Smith Clerk 7499 Allen Salesman 7521 WARD SALESMAN SQL>
COL ENAME FORMAT A40 Empno Ename Job ----------------------------------------- ------- --------- 7369 Smith Clerk 7499 Allen Salesman 7521 Ward Salesman 4). Setting the column title Align Jus [TIFY] {L [EFT] | C [Enter] | C [Entre] | R [Ight]} SQL> Col Ename Justify Center SQL> / Empno Ename Job --------------------------- ----------------------------- 7369 Smith Clerk 7499 Allen Salesman 7521 Ward Salesman For the Number type column, the column header is default On the right, other types of columns are default on the left 5). Do not let a column display on the screen NOPRI [NT] | PRI [NT] SQL> Col JName --------- - - ---------------------------------------- 7369 Smith 7499 Allen 7521 Ward 6). Format Number Type Column Display: SQL> Column Sal Forma T $ 99,990 SQL> / Employee Department Name Saliff Commission ------------------------------------------ 30 Allen $ 1,600 300 7) When the column value is displayed, if the column value is a null value, replace the NULL value Comm number with a text value Comm Nul Nul [L] Text 8). Set a column wrap Wra [ PPED] | WOR [D_Wrapped] | TRU [ncated] col1 -------------------- How Are you? SQL> Col Col1 Format A5 SQL> Col Col1 Wrapped Col1 - ---- How A Re Yo U? SQL> Col Col1 Word_Wrapped Col1 ----- How Are you? SQL> Col Col1 Word_Wrapped COL1 ----- How A 9). Show the current display attribute value SQL > Column column_name 10). Set all columns to the default value SQL> CLUMNS 8. Mask off the same value displayed in a column Break on break_column sql> Break on Deptno SQL>
Select Deptno, Ename, Sal from Emp Where Sal <2500 ORDER BY Deptno; DePtno Ename Sal ---------------------------- 10 Clark 2450 Miller 1300 20 Smith 800 ADAMS 1100 9. In the display of the same value displayed in a column, insert N space lines before the value change is changed.
Break on Break_Column Skip N SQL> Break on Deptno Skip 1 SQL> / Deptno ENAME SAL --------------------------- 10 CLARK 2450 Miller 1300 20 SMITH 800 ADAMS 1100 10. Show settings for Break SQL> Break 11. Delete 6,7 Settings SQL> Clear Breaks 12. SET Command: This command contains many subcommand: set system_variable value system_variable value can be as follows One of the 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 [ESC [APE] {/ | c | ON | Off} feed [back] {6 | n | on | Off} flagger {OFF | Entry | Intermediate [ite] | Full} Flu [SH] {ON | OFF} HEA [Ding] {ON | Off} Heads [EP] {|| C | ON | Off} instance [instance_path | local] Lin [eSize] {80 | n} LOBOF [Fs " ET] {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 [}e} null text Numf [omat] 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 ] [MAT] {WRA [PPED] | WOR [D_ Wrapped] | TRU [Ncated]}] Shift [inout] {VIS [IBLE] | 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 is automatically submitted to the modified data to SQL> Set Auto [Commit] {ON | OFF | IMM [EDIATE] | N} 2). When performing a SQL script with the start command, whether the SQL statement SQL> set echo {on | OFF} 3 in the script is displayed. Whether the current SQL statement query or modification is displayed SQL> set feed [back] { 6 | N | ON | OFF} The number of rows of the result is displayed only when the result is greater than 6 rows. If SET feedback 1, no matter how many rows are queried. When it is OFF, the number of queries is not displayed 4). Whether to display column headings SQL> Set Hea [ding] {on | OFF} When set heading off, the column header is not displayed on each page, but Blank line replaces 5). Set the number of characters that can accommodate SQL> SET Lin [ESIZE] {80 | N} If the output content of the line is greater than the number of set lines that can be accommodated, the fold is displayed. 6). Separation between settings page and pages SQL> SET newP [AGE] {1 | N | NONE} When SET NewPage 0, there is a small black box at the beginning of each page. When SET NewPage N, there is a n space between the pages and pages. When Set NewPage None, there is no interval between page and pages. 7). When displaying, use the text value to replace the NULL value SQL> Set Null Text 8). Setting up a page SQL> SET PAGES [Ize] {24 | n} If set to 0, all output content is One page and does not display the column header 9). Whether to display the information output with 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 the necessary information in order to debug the stored procedure, only after setting the serveroutput variable to ON, information can be displayed on the screen. 10). When the length of the SQL statement is greater than the LINSIZE, it is intercepted the SQL statement when displayed. SQL> SET WRA [P] {ON | OFF} When the length of the output row is greater than the length of the set row (set with the set linesize n command), the more of the output rows will be otherwise One line shows that otherwise, the output row will be more than character resection, which is not displayed. 11). Whether to display the output on the screen, mainly used in conjunction with SPOOL. SQL> SET TERM [OUT] {ON | OFF} When using the spool command to output a large table to a file, the content output will consume a lot of time on the screen, and then output the set termspool OFF, output The content will only be saved in the output file, which is not displayed on the screen, which greatly increases the speed of Spool.
12). Remove SQL> Set TriMs [OUT] {on | OFF} 13 in the SPOOL output in the SPOOL output to display the execution time of each SQL statement cost set timing {on | OFF} 14. Modify the current row in SQL Buffer, the first string C [hange] / old_value / new_value SQL> L 1 * Select * from dept SQL> C / DEPT / EMP 1 * SELECT * FROM EMP 15. Edit SQL statement EDI [T 16 in SQL Buffer. Displays the SQL statement in SQL Buffer, List N displays the Nth line in SQL Buffer and causes the nth line to become the current line L [IST] [N] 17. Add a row or multi-line i [NPUT] 18 below the current row of SQL Buffer. Add the specified text to the current line of SQL Buffer, the current line 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 statements in SQL Buff to save file_name 20 in a file. Import the SQL statement in a file into the Get file_name 21 in SQL Buffer. Perform the SQL statement run or / 22 that has just been executed again. Perform a stored procedure execute procedure_name 23. Connect to the specified database Connect user_name / passwd @ db_alias 24 in SQL * Plus. Set the top header Ttitle 25 for each report. Set the tail of each report Btitle 26. Write a comment Remark [Text] 27. Out the specified information or a space line to the screen PROMPT [TEXT] 28. Pause the process of execution, wait for the user response to continue executing the PAUSE [TEXT] SQL> PAUSE Adjust Paper and Press Return to Continue. 29. Copy some of the data in a database to another database (such as copying a table to another) COPY | from database to database} {append | create | insert | replace} destination_table [ Column, Column, Column, ...) USING Query SQL>
