案例教学讲稿sqlplus

1、Using SQL*Plus ObjectivesAfter completing this appendix, you should be able to do the following:Log in to SQL*PlusEdit SQL commandsFormat output using SQL*Plus commandsInteract with script filesSQL and SQL*Plus InteractionBufferServerSQL statementsQuery resultsSQL scriptsSQL*PlusSQL Statements Versu

2、sSQL*Plus Commands SQLA languageANSI-standardKeywords cannot beabbreviatedStatements manipulate dataand table definitions in thedatabaseSQLstatementsSQLbufferSQL*Plus commandsSQL*Plus bufferSQL*Plus An environmentOracle-proprietaryKeywords can be abbreviatedCommands do not allow manipulation of valu

3、es in the databaseOverview of SQL*Plus Log in to SQL*Plus.Describe the table structure.Edit your SQL statement.Execute SQL from SQL*Plus.Save SQL statements to files and append SQL statements to files.Execute saved files.Load commands from file to buffer to edit.sqlplus username/passworddatabaseLogg

4、ing in to SQL*Plus 12Changing the Settings of SQL*Plus EnvironmentDisplaying Table StructureUse the SQL*Plus DESCRIBE command to display the structure of a table:DESCRIBE tablenameDisplaying Table StructureName Null? Type- - -DEPARTMENT_ID NOT NULL NUMBER(4)DEPARTMENT_NAME NOT NULL VARCHAR2(30)MANAG

5、ER_ID NUMBER(6)LOCATION_ID NUMBER(4)DESCRIBE departmentsSQL*Plus Editing CommandsAPPEND textCHANGE / old / newCHANGE / text /CLEAR BUFFERDELDEL nDEL m nSQL*Plus Editing CommandsINPUTINPUT textLISTLIST nLIST m n RUNnn text0 textUsing LIST, n, and APPENDLIST 1 SELECT last_name 2* FROM employees1 1* SE

6、LECT last_nameA , job_id 1* SELECT last_name, job_idLIST 1 SELECT last_name, job_id 2* FROM employeesUsing the CHANGE CommandLIST 1* SELECT * from employeesc/employees/departments 1* SELECT * from departmentsLIST 1* SELECT * from departmentsSQL*Plus File CommandsSAVE filenameGET filenameSTART filena

7、me filenameEDIT filenameSPOOL filenameEXITUsing the SAVE, START, and EDIT CommandsLIST 1 SELECT last_name, manager_id, department_id 2* FROM employeesSAVE my_query Created file my_querySTART my_queryLAST_NAME MANAGER_ID DEPARTMENT_ID- - -King 90Kochhar 100 90.107 rows selected.Using the SAVE, START,

8、 and EDIT CommandsEDIT my_querySERVEROUTPUT CommandUse the SET SERVEROUTPUT command to control whether to display the output of stored procedures or PL/SQL blocks in SQL*Plus. The DBMS_OUTPUT line length limit is increased from 255 bytes to 32767 bytes.The default size is now unlimited.Resources are

9、 not pre-allocated when SERVEROUTPUT is set.Because there is no performance penalty, use UNLIMITED unless you want to conserve physical memory.SET SERVEROUTPUT ON | OFF SIZE n | UNLIMITED FORMAT WRAPPED | WORD_WRAPPED | TRUNCATEDUsing the SQL*Plus SPOOL CommandSPOOL file_name.ext CREATE | REPLACE |

10、APPEND | OFF | OUTOptionDescriptionfile_name.extSpools output to the specified file name.CREATECreates a new file with the name specified.REPLACEReplaces the contents of an existing file. If the file does not exist, REPLACE creates the file.APPENDAdds the contents of the buffer to the end of the fil

11、e you specify.OFFStops spooling.OUTStops spooling and sends the file to your computers standard (default) printer.Using the AUTOTRACE CommandDisplays a report after the successful execution of SQL DML statements such as SELECT, INSERT, UPDATE or DELETE.The report can now include execution statistics and the query execution path.SET AUTOTRACE ON | OFF | TRACEONLY EXPLAIN STATISTICSSET AUTOTRACE ON- The AUTOTRACE report includes both the optimizer- execution path and the SQL statement execution - statistics.Sum