1、SET SET APPINFOON | OFF | text DBMS_APPLICATION_INFO APPINFO ( SET OFF)SET APPINFO ONSHOW APPINFOAPPINFO is ON and set to SQL*PlusSET APPINFO This is SQL*PlusVARIABLE MOD VARCHAR2(50)VARIABLE ACT VARCHAR2(40)EXECUTE DBMS_APPLICATION_INFO.READ_MODULE(:MOD, :ACT);PL/SQL procedure successfully complete
2、d.PRINT MODMOD-This is SQL*Plus APPINFO SET APPINFO OFFSET ARRAYSIZE 15 | n SQL*Plus SET AUTOCOMMITON | OFF | IMMEDIATE | n SQL PL/SQL Oracle SQL*Plus SET AUTOCOMMIT SET AUTOPRINT ON | OFFSET AUTORECOVERY ON | OFF ON RECOVER 佫鵵 AUTORECOVERY ON iSQL*Plus RECOVER AUTOMATICSET AUTORECOVERY ON
3、RECOVER DATABASESET AUTOTRACE ON | OFF | TRACEONLY EXPLAIN STATISTICS SQL DML (SELECTINSERTUPDATE DELETE) 档SET BLOCKTERMINATOR . | c | ON | OFF PL/SQL cSET CMDSEP ; | c | ON | OFF SQL*Plus c TTITLE COLUMN SET CMDSEP +TTITLE LEFT SALARIES + COLUMN SALARY FORMAT $99,999SELECT LAST_NAME, SALARY FROM EM
4、P_DETAILS_VIEWWHERE JOB_ID = SH_CLERK;SALARIESLAST_NAME SALARY- -Taylor $3,200Fleaur $3,100Sullivan $2,500Geoni $2,800Sarchand $4,200Bull $4,100Dellinger $3,400Cabrio $3,000Chung $3,800Dilly $3,600Gates $2,900Perkins $2,500Bell $4,000Everett $3,900McCain $3,200Jones $2,800SALARIESLAST_NAME SALARY- -
5、Walsh $3,100Feeney $3,000OConnell $2,600Grant $2,60020 rows selected.SET COLSEP | text (SET MARKUP HTML PREFORMAT) iSQL*Plus (SET COLSEP)|SET MARKUP HTML PREFORMAT ONSET COLSEP |SELECT LAST_NAME, JOB_ID, DEPARTMENT_IDFROM EMP_DETAILS_VIEWWHERE DEPARTMENT_ID = 20;LAST_NAME |JOB_ID |DEPARTMENT_ID-|-|-
6、Hartstein |MK_MAN | 20Fay |MK_REP | 20SET CONCAT . | c | ON | OFFSQL*Plus SET COPYCOMMIT 0 | n COPY SET COPYTYPECHECK ON | OFF COPY SET DEFINE & | c | ON | OFF cSET DESCRIBE DEPTH 1 | n | ALL LINENUM ON | OFF INDENT ON | OFF ADDRESSCREATE TYPE ADDRESS AS OBJECT ( STREET VARCHAR2(20), CITY V
7、ARCHAR2(20) );/Type created ADDRESS (EMPADDR) EMPLOYEECREATE TABLE EMPLOYEE (LAST_NAME VARCHAR2(30), EMPADDR ADDRESS, JOB_ID VARCHAR2(20), SALARY NUMBER(7,2) );/Table created EMPLOYEESET DESCRIBE DEPTH 2 LINENUM ON INDENT ONDESCRIBE employee Name Null? Type - - -1 LAST_NAME VARCHAR2(30)2 EMPADDR ADD
8、RESS3 2 STREET VARCHAR2(20)4 2 CITY VARCHAR2(20)5 JOB_ID VARCHAR2(20)6 SALARY NUMBER(7,2)SET ECHO ON | OFF START ON OFF ECHO iSQL*PlusSET EMBEDDED ON | OFF濪SET ESCAPE | c | ON | OFF (!)SET ESCAPE !ACCEPT v1 PROMPT Enter !&1:Enter &1: ()SET ESCAPE ONSET FEEDBACK 6 | n | ON | OFF n SET FLAGGER OFF | E
9、NTRY | INTERMEDIATE | FULL SQL ANSI/ISO SQL92 SET HEADING ON | OFFSET HEADING OFF SQL SELECT SELECT LAST_NAME, SALARYFROM EMP_DETAILS_VIEWWHERE JOB_ID = AC_MGR;Higgins 12000SET HEADING ONSET HEADSEP | | c | ON | OFF(SET MARKUP HTML PREFORMAT) iSQL*Plus (SET HEADSEP)SET INSTANCE instance_path | LOCAL
10、PROD1DISCONNECTSET INSTANCE PROD1 localSET INSTANCE localSET LINESIZE 150 | niSQL*Plus SET LOBOFFSET 1 | n CLOB NCLOB CLOB 22 SET LOBOFFSET 22CLOB SQL*Plus 23 SET LOGSOURCE pathname鵵/usr/oracle10/dbs/archSET LOGSOURCE /usr/oracle10/dbs/archRECOVER DATABASESET LONG 80 | n CLOBLONGNCLOB XMLType () LON
11、G () LONG 500SET LONG 500LONG SQL*Plus 501 LONG 80 SET LONGCHUNKSIZE 80 | n SQL*Plus CLOBLONGNCLOB XMLType () SQL*Plus LONG 100 SET LONGCHUNKSIZE 100 100 LONG SET LONG ()SET MARKUP HTML ON | OFF HEAD text BODY text TABLE text ENTMAP ON | OFF SPOOL ON | OFF PREFORMAT ON | OFF HTML iSQL*Plus SET NULL
12、text SQL SELECT SET NUMFORMAT formatformat COLUMN SET NUMFORMAT SET NUMWIDTH 顣SET NUMWIDTH 10 | n COLUMN COLUMN FORMAT SET NUMFORMAT SET NUMWIDTH SET PAGESIZE 14 | niSQL*Plus 24SET PAUSE ON | OFF | textSET PAUSE ON PAGESIZE SET RECSEP WRAPPED | EACH | OFFSQL*Plus RECSEP (SET MARKUP HTML PREFORMAT) i
13、SQL*Plus (SET RECSEP)SET RECSEPCHAR | cSET SERVEROUTPUT ON | OFF SIZE n | UNLIMITED FORMAT WRAPPED | WORD_WRAPPED | TRUNCATED SQL*Plus 洢 PL/SQL ( DBMS_OUTPUT.PUT_LINE) PL/SQL DBMS_OUTPUT.PUT_LINE SET SERVEROUTPUT ON SET SERVEROUTPUT ON BEGIN DBMS_OUTPUT.PUT_LINE(Task is complete);END;/Task is comple
14、te.PL/SQL procedure successfully completed.SET SQLCASE MIXED | LOWER | UPPER SQL PL/SQL SET SQLPLUSCOMPATIBILITY x.y.z x.y.z 汾SQL*Plus SQL*Plus SQL*Plus SQL*Plus SET SQLPLUSCOMPATIBILITY glogin.sql SET SQLPLUSCOMPATIBILITY 10.2 SQLPLUS -COMPATIBILITY x.y.z SET SQLPLUSCOMPATIBILITY x.y.z SQ
15、L*Plus SQL*Plus 汾 SQLPLUSCOMPATIBILITY 磬 VARIABLE 9.0.1 SQL*Plus 汾 9.0.1 SQLPLUSCOMPATIBILITY SQLPLUSCOMPATIBILITY 7.3.4 =10.1 SHOW ERRORS Oracle 10g PL/SQL 10.1=10.1 SPOOL CREATEREPLACESAVE 10.1=10.1 SET SQLPROMPT 10.1=10.1 Windows Windows 10.1=10.1 Glogin/login 10.1 10.1= 9.2 FOLD_AFTE 9.2.= 9.0 S
16、QL (/) () 9.0.1.4.= 9.0 NCHAR NVARCHAR2 () 9.0.1SET SQLTERMINATOR ; | c | ON | OFF PL/SQL SQL SET TIMING ON | OFFSET UNDERLINE - | c | ON | OFFON OFF ON c - SET MARKUP HTML PREFORMAT ON iSQL*Plus SET UNDERLINESET VERIFY ON | OFF SQL PL/SQL ON OFF SET WRAP ON | OFFOFF ON
17、COLUMN WRAPPED TRUNCATED WRAP (SET MARKUP HTML PREFORMAT) iSQL*Plus (SET WRAP)SET XQUERY BASEURI text URI URI XQuery XQUERY BASEURI SET XQUERY BASEURI /public/scottxquery for $i in doc(foo.xml) return $i/select column_Value from xmltable(declare base-uri /public/scott; for $i in doc(foo.xml) return
18、$i ) ;SET XQUERY ORDERING UNORDERED | ORDERED | DEFAULT XQuery UNORDERED ORDERED XQuery DEFAULT UNORDEREDSET XQUERY ORDERING ORDEREDxquery for $i in doc(foo.xml) return $i/select column_value from xmltable(declare ordering ordered; for $i in doc(foo.xml) return $i );SET XQUERY NODE BYVALUE | BYREFER
19、ENCE | DEFAULT (磬) (磬) BYVALUE (磬) () BYREFERENCE 塣DEFAULT BYVALUESET XQUERY NODE BYREFERENCExquery for $i in doc(foo.xml) return $i/select column_value from xmltable(declare node byreference; for $i in doc(foo.xml) return $i );SET XQUERY CONTEXT text XQuery (fn:doc(bib.xml)/bookfn:count(./author)1)
20、 (1 100). mod 5 eq 0) XQUERY CONTEXT SET XQUERY CONTEXT doc(foo.xml)xquery for $i in /a return $i/select column_value from xmltable(for $i in /a return $i passing XMLQuery(doc(foo.xml);SHOWSHOW option option system_variableALLBTITLEERRORS FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIE
21、W | TYPE | TYPE BODY | DIMENSION | JAVA CLASS schema.nameLNOPARAMETERS parameter_namePNORECYCLEBIN original_nameRELEASEREPFOOTERREPHEADERSGASQLCODETTITLEUSERXQUERY SQL*Plus SQL*Plus SHOW SGA DBA SGA SHOW SGATotal System Global Area 7629732 bytesFixed Size 60324 bytesVariable Size 6627328 bytesDataba
22、se Buffers 409600 bytesRedo Buffers 532480 bytes洢CONNECT SYSTEM/MANAGERCREATE PROCEDURE HR.PROC1 ASBEGIN:P1 := 1;END;/Warning: Procedure created with compilation errors.SHOW ERRORS PROCEDURE PROC1NO ERRORS.SHOW ERRORS PROCEDURE HR.PROC1Errors for PROCEDURE HR PROC1:LINE/COL ERROR-3/3 PLS-00049: bad
23、bind variable P1 FLASHBACK ( CJ1 ABC )SHOW RECYCLEBINORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME- - - -CJ1 RB$29458$TABLE$0 TABLE 2003-01-22:14:54:07ABC RB$29453$TABLE$0 TABLE 2003-01-20:18:50:29 CJ1FLASHBACK TABLE CJ1 TO BEFORE DROP;SHUTDOWNSHUTDOWN ABORT | IMMEDIATE | NORMAL | TRANSACTIONA
24、L LOCAL Oracle SHUTDOWNDatabase closed.Database dismounted.Oracle instance shut down.STARTSTART url.ext arg. Web URL PROMOTE SQL () SELECT FIRST_NAME, LAST_NAME, JOB_ID, SALARYFROM EMP_DETAILS_VIEWWHERE JOB_ID=&1 AND SALARY&2;START PROMOTE ST_MAN 7000 Web START HTTP:/machine_name.domain:port/PROMOTE
25、.SQL ST_MAN 7000 Web host.domain 滻 machine_name.domain滻 portSELECT LAST_NAME, LAST_NAMEFROM EMP_DETAILS_VIEWWHERE JOB_ID=ST_MAN AND SALARY7000;STARTUPSTARTUP options | upgrade_optionsoptions FORCE RESTRICT PFILE=filename QUIET MOUNT dbname | OPEN open_options dbname | NOMOUNT open_options READ ONLY
26、| WRITE RECOVER | RECOVER upgrade_options PFILE=filename UPGRADE | DOWNGRADE QUIET Oracle STARTUPSTARTUP OPEN databaseSTARTUP FORCE RESTRICT MOUNT TESTPARM STARTUP PFILE=testparm NOMOUNT RESTRICTED SESSION MYINIT.ORASTARTUP FORCE RESTRICT PFILE=myinit.ora OPEN databaseTIMI
27、NGTIMING START text | SHOW | STOP SQL_TIMER TIMING START SQL_TIMERTIMING SHOWTIMING STOPTTITLETTITLE printspec text | variable . ON | OFFprintspec BOLDCENTERCOL nFORMAT textLEFTRIGHTSKIP nTAB n TTITLE 塣 TTITLE TTITLEMonthly AnalysisData in ThousandsTTITLE LEFT Monthly Analysis CENTER 01 Jan 2003 -RI
28、GHT Page: FORMAT 999 SQL.PNO SKIP CENTER -Data in ThousandsMonthly Analysis 01 Jan 2003 Page: 1 Data in Thousands壬TTITLE OFFUNDEFINEUNDEFINE variable . variable ( DEFINE ) ( START ) POS 壬UNDEFINE POS MYVAR1 MYVAR2 壬UNDEFINE MYVAR1 MYVAR2VARIABLEVARIABLE variable type type NUMBERCHARCHAR (n CHAR | BY
29、TE)NCHARNCHAR (n)VARCHAR2 (n CHAR | BYTE)NVARCHAR2 (n)CLOBNCLOBREFCURSORBINARY_FLOATBINARY_DOUBLE PL/SQL İ CLOB NCLOB LOBEXECUTE DBMS_LOB.FREETEMPORARY(:cv)VARIABLE ret_val NUMBER SQL*Plus PL/SQL 飺BEGIN :ret_val:=4;END;/PL/SQL procedure successfull
30、y completed. SQL*Plus PRINT ret_val RET_VAL- 4VARIABLE id NUMBERBEGIN :id := EMP_MANAGEMENT.HIRE (BLAKE,MANAGER,KING,2990,SALES);END;/洢 :id PRINT PL/SQL SET AUTOPRINT ONVARIABLE a REFCURSORBEGIN OPEN :a FOR SELECT LAST_NAME, CITY, DEPARTMENT_ID FROM EMP_DETAIL
31、S_VIEW WHERE SALARY 12000 ORDER BY DEPARTMENT_ID;END;/PL/SQL procedure successfully completed.LAST_NAME CITY DEPARTMENT_ID- - -Hartstein Toronto 20Russell Oxford 80Partners Oxford 80King Seattle 90Kochhar Seattle 90De Haan Seattle 906 rows selected. PRINT WHENEVER OSERRORWHENEVER OSERROR EXIT SUCCES
32、S | FAILURE | n | variable | :BindVariable COMMIT | ROLLBACK | CONTINUE COMMIT | ROLLBACK | NONE () iSQL*Plus WHENEVER OSERROR EXITSTART no_such_fileWHENEVER SQLERRORWHENEVER SQLERROR EXIT SUCCESS | FAILURE | WARNING | n | variable | :BindVariable COMMIT | ROLLBACK | CONTINUE COMMIT | ROLLBACK | NON
33、E SQL PL/SQL () SQL UPDATE iSQL*Plus WHENEVER SQLERROR EXIT SQL.SQLCODEUPDATE EMP_DETAILS_VIEW SET SALARY = SALARY*1.1; SQL*Plus WHENEVER SQLERROR SQL PL/SQL WHENEVER SQLERROR EXIT SQL.SQLCODEcolumn LAST_name headIing Employee NameUnknown COLUMN option headiingSHOW non_existed_optionXQUERYXQUERY xquery_statementSQL*Plus XQUERY XQuery 1.0 Oracle 10g ( 2) 汾 XQUERY Oracle 汾 XQUERYSP2-614 汾 SET XQUERY SET XQUERY BASEURI text, SET XQUERY ORDERING UNORDERED | ORDERED | DEFAULT SET XQUERY NODE BYVALUE | BYREFERENCE | DEFAULT SET XQUERY CONTEXT text
