[오라클]SQL*Plus Commands
01 14, 2007 03:20
| 출처 : [http://www.tek-tips.com/gfaqs.cfm/lev2/4/lev3/27/pid/186/fid/3481] -- ==================================================================== SQL*Plus Commands (faq186-3481) -- ==================================================================== Lately there have been a number of questions regarding Oracle SQL*Plus commands. I recommend purchasing a copy of the O'reilly Oracle SQL*Plus Pocket Reference (ISBN 1-56592-941-1, about $10). This manual was used as a reference in writing this FAQ. This is a Windows/Oracle 8i perspective. The purpose of this FAQ is to familiarize you with some of the most commonly used SQL*Plus commands. I will leave the more advanced features for another FAQ. It is important to recognize the difference between: * DDL statements such as CREATE TABLE, DROP TABLE, etc. which manage the database * SQL statements such SELECT, INSERT, UPDATE, DELETE, etc. which manipulate data in the database * PL/SQL which is a language that that allows you to write programs with procedural statements such as IF..THEN...ELSE and looping constructs * SQL*Plus commands which set the SQL*Plus environment SQL*Plus Commands 1. Are usually entered on one line and do not require a ??at the end. 2. Stay in effect until you reset them or exit the session. 3. May be entered in upper or lowercase. . SHOW When you start a SQL*Plus session it gets it뭩 initial settings from the LOGIN.SQL file, if one exists, in your directory path. To get a list of your current settings type: SQL> show all you should see a list similar to this: appinfo is ON and set to "SQL*Plus" arraysize 15 autocommit OFF autoprint OFF autotrace OFF shiftinout INVISIBLE blockterminator "." (hex 2e) btitle OFF and is the 1st few characters of the next SELECT statement cmdsep OFF colsep " " compatibility version NATIVE concat "." (hex 2e) copycommit 0 COPYTYPECHECK is ON define "&" (hex 26) echo OFF editfile "afiedt.buf" embedded OFF escape OFF FEEDBACK ON for 6 or more rows flagger OFF flush ON heading ON headsep "|" (hex 7c) linesize 100 lno 24 loboffset 1 long 80 longchunksize 80 newpage 1 null "" numformat "" numwidth 9 pagesize 24 PAUSE is OFF pno 0 recsep WRAP recsepchar " " (hex 20) release 800050000 repfooter OFF and is NULL repheader OFF and is NULL serveroutput OFF showmode OFF spool OFF sqlcase MIXED sqlcode 0 sqlcontinue "> " sqlnumber ON sqlprefix "#" (hex 23) sqlprompt "SQL> " sqlterminator ";" (hex 3b) suffix "sql" tab ON termout ON time OFF timing OFF trimout ON trimspool OFF ttitle OFF and is the 1st few characters of the next SELECT statement underline "-" (hex 2d) USER is "MY_SCHEMA_NAME" verify ON wrap : lines will be wrapped . STORE If you want to save your settings after you have made changes: SQL> store set my_settings_file.new create will store the settings in the new file which you have named. If you omit the file extension it will use ?sql?by default. The keyword 밹reate?may be changed to 뱑eplace?to overwrite an existing file or to 밶ppend?to add to an existing file. . DESCRIBE To describe most database objects: SQL> desc dual Name Null? Type ------------------------------- -------- ---- DUMMY VARCHAR2(1) . START or @ To run a script: SQL> start my_script.tst or SQL> @my_script.tst If you omit the file extension, it will use ?sql?by default. . / To run the SQL statement or PL/SQL block you typed: SQL> select sysdate from dual 2 SQL> / SYSDATE --------- 21-APR-03 . SPOOL To have displayed output written into an output file: SQL> spool my_output.txt If you omit the file extension, it will use ?lst?by default. To stop spooling: SQL> spool off . COLUMN This will format a column for output: SQL> column today head 'Todays Date' SQL> select sysdate today from dual; Todays Da --------- 21-APR-03 Since the heading is wider than the data you might add: SQL> column today format a11 SQL> / Todays Date ----------- 21-APR-03 or you could have set both in one statement: column today format a11 head 'Todays Date' The format parameter may be used to specify a smaller width for a character string which may cause it to wrap: column table_name format a15 SQL> select table_name from all_tables where owner = 'SYS' order by 1; TABLE_NAME --------------- ACCESS$ AQ$_MESSAGE_TYP ES ?/font> The format parameter also may assign a mask to a numeric field. SQL> column num format 99,990.00 SQL> select 12345.98 num from dual; NUM ---------- 12,345.98 Date masks are assigned by using the to_char function in your select statements, not in SQL*Plus. There are many optional parameters to the column command, so refer to the manual. . EDIT To edit the contents of the SQL buffer: ed To edit a file: ed my_script.tst If you omit the file extension, it will use ?sql?by default. . GET Used to retrieve the contents of a file into the buffer. SQL> get my_script.new 1* select sysdate from dual; or SQL> get my_script.new nolist SQL> If you omit the file extension it will use ?sql?by default. . LIST To list the contents of the SQL buffer: SQL> l 1* select sysdate from dual; . PROMPT To display a user message: SQL> prompt Welcome Back! Welcome Back! . SAVE Used to save the contents of the buffer to a file: SQL> save my_sql.new create This will store the buffer contents in the new file which you have named. If you omit the file extension it will use ?sql?by default. The keyword 밹reate?may be changed to 뱑eplace?to overwrite an existing file or to 밶ppend?to add to an existing file. . SET This sets the environment and there are many parameters. This is a list of some of the commonly used ones. See a manual for detailed more syntax information. . DEFINE Sets the special character used for substitution variables (default is ??. SQL> set def on ^ or SQL> set def off . ECHO Controls whether SQL*Plus commands from a command file are displayed when the command file is run. For example, if you have a file called xxx.sql which contains the SQL command: select sysdate from dual; Then: SQL> set echo on SQL> @xxx SQL> select sysdate from dual; SYSDATE --------- 21-APR-03 or SQL> set echo off SQL> @xxx SYSDATE --------- 21-APR-03 . FEEDBACK Controls whether SQL*Plus displays the number of rows affected. For example: SQL> set feedback on SQL> select sysdate from dual; SYSDATE --------- 21-APR-03 1 row selected. or SQL> set feedback off SQL> select sysdate from dual; SYSDATE --------- 21-APR-03 . HEADING Controls whether SQL*Plus displays headings. For example: SQL> set head on SQL> select sysdate from dual; SYSDATE --------- 21-APR-03 or SQL> set head off SQL> select sysdate from dual; 21-APR-03 . LINESIZE Specifies the number of characters on a line. SQL> set linesize 100 . LONG Specifies the maximum number of characters to display for a long datatype. SQL> set long 200 . NEWPAGE Specifies the number of lines between pages. A ??causes a formfeed. SQL> set newpage 0 . PAGESIZE Specifies the number of lines on a page. SQL> set pagesize 55 . SCAN Turns user variable substitution on/off. SQL> set scan on or SQL> set scan off . SERVEROUTPUT Controls whether PL/SQL blocks can print output. This is also used to set the buffer size. Server output must be set on for DBMS_OUTPUT to work correctly. SQL> set serveroutput on or SQL> set serveroutput off or SQL> set serveroutput 100000 . SHOWMODE Determines if the before and after values of settings are displayed. SQL> set show on new: showmode BOTH SQL> set linesize 100 old: linesize 100 new: linesize 100 SQL> set show off old: showmode BOTH SQL> set linesize 80 SQL> . TERMOUT Determines if output from a script is displayed. If file XXX.SQL contains: select sysdate from dual; then: SQL> set term on SQL> @xxx SYSDATE --------- 22-APR-03 SQL> set term off SQL> @xxx SQL> . TIME Controls whether time is displayed in the SQL prompt. SQL> set time on 13:31:33 SQL> . TIMING Controls whether the elapsed execution time displays. SQL> set timing off SQL> @xxx SYSDATE --------- 22-APR-03 SQL> set timing on SQL> / SYSDATE --------- 22-APR-03 real: 10 . TRIMOUT Determines if trailing spaces are trimmed from lines displayed on the screen. set trim on or set trim off . TRIMSPOOL Determines if trailing spaces are trimmed from lines spooled to a file. set trims on or set trims off . TRUNCATE Determines if long lines are truncated. If file XXX.SQL contains: select '&1' hi from dual; then: SQL> set truncate off SQL> set linesize 25 SQL> @xxx 123456789012345678901234567890 HI ------------------------- 1234567890123456789012345 67890 SQL> set truncate on SQL> / HI ------------------------- 1234567890123456789012345 . VERIFY Determines if before and after images of lines with substitution variables are displayed. If flat file XXX.SQL contains: select '&1' hi from dual; then: SQL> @xxx Hello! old 1: select '&1' greeting from dual new 1: select 'Hello!' greeting from dual HI ------ Hello! SQL> set verify off SQL> @xxx Greetings! HI ---------- Greetings! These settings work in combination to modify the SQL*Plus environment to suit your needs. If flat file XXX.SQL contains: select '&1' msg from dual; then: SQL> set verify off SQL> set feed off SQL> set echo off SQL> column msg format a25 head FAQ_Purpose SQL> @xxx 'Hope this helps!' FAQ_Purpose ------------------------- Hope this helps! |