| 订阅 | 在线投稿
分享
 
 
当前位置: 王朝网络 >> oracle >> Oracle zhong SQLPLUS de chang yong zhi ling shou ji yu ji qiao
 

Oracle zhong SQLPLUS de chang yong zhi ling shou ji yu ji qiao

2008-08-15 06:29:04 编辑來源:互联网 评论
 
本文为【Oracle中SQLPLUS的常用指令收集与技巧】的拼音翻译版
  an zhuang le oracle9i, jin ru sal*plus xu yao shu ru yong hu ming 、 kou ling he zhu ji biao zhi fu
  system/manager
  sys/change_on_intall
  scott/tiger
  internal/oracle
  yi shang wei chu shi mi ma
  zhu ji zi fu chuan kong huo zhe shi @+ ni de fu wu ming , zai ben di deng lu bu xu yao ben di fu wu ming 。
  xia mian jiu jie shao yi xia yi xie chang yong de sql*plus ming ling :
  shou xian cha kan dang qian shi yong de shu ju ku shi li :
  select name from V$database;
  qie huan liang ge shu ju ku shi li
  zai sqlplus li connect username/password@sid
  huo host set ORACLE_SID=sid
  huo cmd li set ORACLE_SID=sid
  huo xiu gai zhu ce biao li ORACLE_SID xiang
  ◆1. zhi xing yi ge SQL jiao ben wen jian
  SQL>start file_name
  SQL>@ file_name
  wo men ke yi jiang duo tiao sql yu ju bao cun zai yi ge wen ben wen jian zhong , zhe yang dang yao zhi xing zhe ge wen jian zhong de suo you de sql yu ju shi , yong shang mian de ren yi ming ling ji ke , zhe lei si yu dos zhong de pi chu li 。
  ◆2. dui dang qian de shu ru jin xing bian ji
  SQL>edit
  ◆3. chong xin yun xing shang yi ci yun xing de sql yu ju
  SQL>/
  ◆4. jiang xian shi de nei rong shu chu dao zhi ding wen jian
  SQL> SPOOL file_name
  zai ping mu shang de suo you nei rong dou bao han zai gai wen jian zhong , bao kuo ni shu ru de sql yu ju 。
  ◆5. guan bi spool shu chu
  SQL> SPOOL OFF
  zhi you guan bi spool shu chu , cai hui zai shu chu wen jian zhong kan dao shu chu de nei rong 。
  ◆6. xian shi yi ge biao de jie gou
  SQL> desc table_name
  ◆7. COL ming ling :
  zhu yao ge shi hua lie de xian shi xing shi 。
  gai ming ling you xu duo xuan xiang , ju ti ru xia :
  COL[UMN] [{ column|expr} [ option ...]]
  Option xuan xiang ke yi shi ru xia de zi ju :
  ALI[AS] alias
  CLE[AR]
  FOLD_A[FTER]
  FOLD_B[EFORE]
  FOR[MAT] format
  HEA[DING] 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). gai bian que sheng de lie biao ti
  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). jiang lie ming ENAME gai wei xin lie ming EMPLOYEE NAME bing jiang xin lie ming fang zai liang xing shang :
  Sql>select * from emp
  Department name Salary
  ---------- ---------- ----------
  10 aaa 11
  SQL> COLUMN ENAME HEADING ’Employee|Name’
  Sql>select * from emp
  Employee
  Department name Salary
  ---------- ---------- ----------
  10 aaa 11
  note: the col heading turn into two lines from one line.
  3). gai bian lie de xian shi chang du :
  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). she zhi lie biao ti de dui qi fang shi
  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
  dui yu NUMBER xing de lie , lie biao ti que sheng zai you bian , qi ta lei xing de lie biao ti que sheng zai zuo bian
  5). bu rang yi ge lie xian shi zai ping mu shang
  NOPRI[NT]|PRI[NT]
  SQL> col job noprint
  SQL> /
  EMPNO ENAME
  ---------- ----------------------------------------
  7369 SMITH
  7499 ALLEN
  7521 WARD
  6). ge shi hua NUMBER lei xing lie de xian shi :
  SQL> COLUMN SAL FORMAT $99,990
  SQL> /
  Employee
  Department Name Salary Commission
  ---------- ---------- --------- ----------
  30 ALLEN $1,600 300
  7). xian shi lie zhi shi , ru guo lie zhi wei NULL zhi , yong text zhi dai ti NULL zhi
  COMM NUL[L] text
  SQL>COL COMM NUL[L] text
  8). she zhi yi ge lie de hui rao fang shi
  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). xian shi lie de dang qian de xian shi shu xing zhi
  SQL> COLUMN column_name
  10). jiang suo you lie de xian shi shu xing she wei que sheng zhi
  SQL> CLEAR COLUMNS
  ◆8. ping bi diao yi ge lie zhong xian shi de xiang tong de zhi
  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. zai shang mian ping bi diao yi ge lie zhong xian shi de xiang tong de zhi de xian shi zhong , mei dang lie zhi bian hua shi zai zhi bian hua zhi qian cha ru n ge kong xing 。
  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. xian shi dui BREAK de she zhi
  SQL> BREAK
  ◆11. shan chu 6、7 de she zhi
  SQL> CLEAR BREAKS
  ◆12. Set ming ling :
  gai ming ling bao han xu duo zi ming ling :
  SET system_variable value
  system_variable value ke yi shi ru xia de zi ju zhi yi :
  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[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). she zhi dang qian session shi fou dui xiu gai de shu ju jin xing zi dong ti jiao
  SQL>SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}
  2). zai yong start ming ling zhi xing yi ge sql jiao ben shi , shi fou xian shi jiao ben zhong zheng zai zhi xing de SQL yu ju
  SQL> SET ECHO {ON|OFF}
  3). shi fou xian shi dang qian sql yu ju cha xun huo xiu gai de xing shu
  SQL> SET FEED[BACK] {6|n|ON|OFF}
  mo ren zhi you jie guo da yu 6 xing shi cai xian shi jie guo de xing shu 。 ru guo set feedback 1 , ze bu guan cha xun dao duo shao xing dou fan hui 。 dang wei off shi , yi lv bu xian shi cha xun de xing shu
  4). shi fou xian shi lie biao ti
  SQL> SET HEA[DING] {ON|OFF}
  dang set heading off shi , zai mei ye de shang mian bu xian shi lie biao ti , er shi yi kong bai xing dai ti
  5). she zhi yi xing ke yi rong na de zi fu shu
  SQL> SET LIN[ESIZE] {80|n}
  ru guo yi xing de shu chu nei rong da yu she zhi de yi xing ke rong na de zi fu shu , ze zhe xing xian shi 。
  6). she zhi ye yu ye zhi jian de fen ge
  SQL> SET NEWP[AGE] {1|n|NONE}
  dang set newpage 0 shi , hui zai mei ye de kai tou you yi ge xiao de hei fang kuang 。
  dang set newpage n shi , hui zai ye he ye zhi jian ge zhe n ge kong xing 。
  dang set newpage none shi , hui zai ye he ye zhi jian mei you ren he jian ge 。
  7). xian shi shi , yong text zhi dai ti NULL zhi
  SQL> SET NULL text
  8). she zhi yi ye you duo shao xing shu
  SQL> SET PAGES[IZE] {24|n}
  ru guo she wei 0, ze suo you de shu chu nei rong wei yi ye bing qie bu xian shi lie biao ti
  9). shi fou xian shi yong DBMS_OUTPUT.PUT_LINE bao jin xing shu chu de xin xi 。
  SQL> SET SERVEROUT[PUT] {ON|OFF}
  zai bian xie cun chu guo cheng shi , wo men you shi hui yong dbms_output.put_line jiang bi yao de xin xi shu chu , yi bian dui cun chu guo cheng jin xing tiao shi , zhi you jiang serveroutput bian liang she wei on hou , xin xi cai neng xian shi zai ping mu shang 。
  10). dang SQL yu ju de chang du da yu LINESIZE shi , shi fou zai xian shi shi jie qu SQL yu ju 。
  SQL> SET WRA[P] {ON|OFF}
  dang shu chu de xing de chang du da yu she zhi de xing de chang du shi ( yong set linesize n ming ling she zhi ), dang set wrap on shi , shu chu xing de duo yu de zi fu hui ling qi yi xing xian shi , fou ze , hui jiang shu chu xing de duo yu zi fu qie chu , bu yu xian shi 。
  11). shi fou zai ping mu shang xian shi shu chu de nei rong , zhu yao yong yu SPOOL jie he shi yong 。
  SQL> SET TERM[OUT] {ON|OFF}
  zai yong spool ming ling jiang yi ge da biao zhong de nei rong shu chu dao yi ge wen jian zhong shi , jiang nei rong shu chu zai ping mu shang hui hao fei da liang de shi jian , she zhi set termspool off hou , ze shu chu de nei rong zhi hui bao cun zai shu chu wen jian zhong , bu hui xian shi zai ping mu shang , ji da de ti gao le spool de su du 。
  12). jiang SPOOL shu chu zhong mei xing hou mian duo yu de kong ge qu diao
  SQL> SET TRIMS[OUT] {ON|OFF}
  13) xian shi mei ge sql yu ju hua fei de zhi xing shi jian
  set TIMING {ON|OFF}
  ◆14. xiu gai sql buffer zhong de dang qian xing zhong , di yi ge chu xian de zi fu chuan
  C[HANGE] /old_value/new_value
  SQL> l
  1* select * from dept
  SQL> c/dept/emp
  1* select * from emp
  ◆15. bian ji sql buffer zhong de sql yu ju
  EDI[T]
  ◆16. xian shi sql buffer zhong de sql yu ju ,list n xian shi sql buffer zhong de di n xing , bing shi di n xing cheng wei dang qian xing
  L[IST] [n]
  ◆17. zai sql buffer de dang qian xing xia mian jia yi xing huo duo xing
  I[NPUT]
  ◆18. jiang zhi ding de wen ben jia dao sql buffer de dang qian xing hou mian
  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. jiang sql buffer zhong de sql yu ju bao cun dao yi ge wen jian zhong
  SAVE file_name
  ◆20. jiang yi ge wen jian zhong de sql yu ju dao ru dao sql buffer zhong
  GET file_name
  ◆21. zai ci zhi xing gang cai yi jing zhi xing de sql yu ju
  RUN
  or
  /
  ◆22. zhi xing yi ge cun chu guo cheng
  EXECUTE procedure_name
  ◆23. zai sql*plus zhong lian jie dao zhi ding de shu ju ku
  CONNECT user_name/passwd@db_alias
  ◆24. she zhi mei ge bao biao de ding bu biao ti
  TTITLE
  ◆25. she zhi mei ge bao biao de yi bu biao ti
  BTITLE
  ◆26. xie yi ge zhu shi
  REMARK [text]
  ◆27. jiang zhi ding de xin xi huo yi ge kong xing shu chu dao ping mu shang
  PROMPT [text]
  ◆28. jiang zhi xing de guo cheng zan ting , deng dai yong hu xiang ying hou ji xu zhi xing
  PAUSE [text]
  Sql>PAUSE Adjust paper and press RETURN to continue.
  ◆29. jiang yi ge shu ju ku zhong de yi xie shu ju kao bei dao ling wai yi ge shu ju ku ( ru jiang yi ge biao de shu ju kao bei dao ling yi ge shu ju ku )
  COPY {FROM database | TO database | FROM database TO database}
  {APPEND|CREATE|INSERT|REPLACE} destination_table
  [(column, column, column, ...)] USING query
  sql>COPY FROM SCOTT/TIGER@HQ TO JOHN/CHROME@WEST
  create emp_temp
  USING SELECT * FROM EMP
  ◆30. bu tui chu sql*plus, zai sql*plus zhong zhi xing yi ge cao zuo xi tong ming ling :
  HOST
  Sql> host hostname
  gai ming ling zai windows xia ke neng bei zhi chi 。
  ◆31. zai sql*plus zhong , qie huan dao cao zuo xi tong ming ling ti shi fu xia , yun xing cao zuo xi tong ming ling hou , ke yi zai ci qie huan hui sql*plus:
  !
  sql>!
  $hostname
  $exit
  sql>
  gai ming ling zai windows xia bu bei zhi chi 。
  ◆32. xian shi sql*plus ming ling de bang zhu
  HELP
  ru he an zhuang bang zhu wen jian :
  Sql>@ ?sqlplusadminhelphlpbld.sql ?sqlplusadminhelphelpus.sql
  Sql>help index
  ◆33. xian shi sql*plus xi tong bian liang de zhi huo sql*plus huan jing bian liang de zhi
  Syntax
  SHO[W] option
  where option represents one of the following terms or clauses:
  system_variable
  ALL
  BTI[TLE]
  ERR[ORS] [{FUNCTION|PROCEDURE|PACKAGE|PACKAGE BODY|
  TRIGGER|VIEW|TYPE|TYPE BODY} [schema.]name]
  LNO
  PARAMETERS [parameter_name]
  PNO
  REL[EASE]
  REPF[OOTER]
  REPH[EADER]
  SGA
  SPOO[L]
  SQLCODE
  TTI[TLE]
  USER
  1) . xian shi dang qian huan jing bian liang de zhi :
  Show all
  2) . xian shi dang qian zai chuang jian han shu 、 cun chu guo cheng 、 chu fa qi 、 bao deng dui xiang de cuo wu xin xi
  Show error
  dang chuang jian yi ge han shu 、 cun chu guo cheng deng chu cuo shi , bian ke yi yong gai ming ling cha kan zai na ge di fang chu cuo ji xiang ying de chu cuo xin xi , jin xing xiu gai hou zai ci jin xing bian yi 。
  3) . xian shi chu shi hua shen shu de zhi :
  show PARAMETERS [parameter_name]
  4) . xian shi shu ju ku de ban ben :
  show REL[EASE]
  5) . xian shi SGA de da xiao
  show SGA
  6). xian shi dang qian de yong hu ming
  show user
  SQL>SET DEFINE ON
  jing chang yong yu yun xing SQL jiao ben zhong , dui bian liang de hong ti huan (&)
  SQL>DEFINE TSTEMP=TEMPTS
  jiang yun xing jiao ben zhong de &TSTEMP ti huan cheng TEMPTS
  SQL>SET DEFINE OFF
  guan bi hong ti huan , jing chang yong yu yun xing PACKAGE huo cun chu guo cheng
  Sql*plus zhong wen cang zhe hao duo ji qiao , ru guo zhang wo zhe xie ji qiao , dui yu zai oracle shu ju ku xia jin xing kuai su kai fa yu you xiao wei hu shu ju ku dou shi you yi de 。
  ◆1. shi yong SQL*PLUS dong tai sheng cheng pi liang jiao ben
  jiang spool yu select ming ling jie he qi lai shi yong , ke yi sheng cheng yi ge jiao ben , jiao ben zhong bao han you ke yi pi liang zhi xing mou yi ren wu de yu ju 。
  li 1:
  sheng cheng yi ge jiao ben , shan chu SCOTT yong hu xia de suo you de biao :
  a. chuang jian gen_drop_table.sql wen jian , bao han ru xia yu ju :
  SPOOL c:drop_table.sql
  SELECT 'DROP TABLE '|| table_name ||';' FROM user_tables;
  SPOOL OFF
  b. yi SCOTT yong hu deng lu shu ju ku
  SQLPLUS > @ …..gen_dorp_table.sql
  c. zai c pan gen mu lu xia hui sheng cheng wen jian drop_table.sql wen jian , bao han shan chu suo you biao de yu ju , ru xia suo shi :
  SQL> SELECT 'DROP TABLE '|| table_name ||';' FROM user_tables;
  'DROPTABLE'||TABLE_NAME||';'
  --------------------------------------------------------------------------------
  DROP TABLE DEPT;
  DROP TABLE EMP;
  DROP TABLE PARENT;
  DROP TABLE STAT_VENDER_TEMP;
  DROP TABLE TABLE_FORUM;
  5 rows selected.
  SQL> SPOOL OFF
  d. dui sheng cheng de drop_table.sql wen jian jin xing bian ji qu diao bu bi yao de bu fen , zhi liu xia drop table ….. yu ju
  e. zai scott yong hu xia yun xing dorp_table.sql wen jian , shan chu scott yong hu xia suo you de biao 。
  SQLPLUS > @ c:dorp_table.sql
  zai shang mian de cao zuo zhong , zai sheng cheng de jiao ben wen jian zhong hui you duo yu de zi fu , ru yun xing de sql yu ju , biao ti , huo fan hui de xing shu , xu yao wo men bian ji gai jiao ben hou zai yun xing , gei shi ji de cao zuo dai lai zhu duo bu bian 。 lan duo shi ren de ben xing , zhe cu shi wo men yong geng jian dan de ban fa lai shi xian shang mian de ren wu 。
  a. chuang jian gen_drop_table.sql wen jian , bao han ru xia yu ju :
  set echo off
  set feedback off
  set newpage none
  set pagesize 5000
  set linesize 500
  set verify off
  set pagesize 0
  set term off
  set trims on
  set linesize 600
  set heading off
  set timing off
  set verify off
  set numwidth 38
  SPOOL c:drop_table.sql
  SELECT 'DROP TABLE '|| table_name ||';' FROM user_tables;
  SPOOL OFF
  b. yi SCOTT yong hu deng lu shu ju ku
  SQLPLUS > @ …..gen_dorp_table.sql
  c. zai c pan gen mu lu xia hui sheng cheng wen jian drop_table.sql wen jian , bao han shan chu suo you biao de yu ju , ru xia suo shi :
  DROP TABLE DEPT;
  DROP TABLE EMP;
  DROP TABLE PARENT;
  DROP TABLE STAT_VENDER_TEMP;
  DROP TABLE TABLE_FORUM;
  d. zai scott yong hu xia yun xing dorp_table.sql wen jian , shan chu scott yong hu xia suo you de biao 。
  SQLPLUS > @ c:dorp_table.sql
  ◆2. jiang yi ge biao zhong de shu ju dao chu sheng cheng yi ge wen ben wen jian , lie yu lie zhi jian yi ”,” ge kai
  set echo off
  set feedback off
  set newpage none
  set pagesize 5000
  set linesize 500
  set verify off
  set pagesize 0
  set term off
  set trims on
  set linesize 600
  set heading off
  set timing off
  set verify off
  set numwidth 38
  SPOOL c:drop_table.sql
  select DEPTNO || ',' || DNAME FROM DEPT;
  SPOOL OFF
  jiang shang mian de nei rong bao cun wei yi ge wen ben wen jian hou , yi scott deng lu , zhi xing gai wen jian hou xian shi jie guo :
  10,ACCOUNTING
  20,RESEARCH
  30,SALES
  40,OPERATIONS
  tong guo shang mian de liang ge li zi , wo men ke yi jiang :
  set echo off
  set feedback off
  set newpage none
  set pagesize 5000
  set linesize 500
  set verify off
  set pagesize 0
  set term off
  set trims on
  set linesize 600
  set heading off
  set timing off
  set verify off
  set numwidth 38
  SPOOL c: ju ti de wen jian ming
  ni yao yun xing de sql yu ju
  SPOOL OFF
  zuo wei yi ge mo ban , zhi yao jiang bi yao de yu ju jia ru zhe ge mo ban jiu ke yi le 。
  zai oracle de jiao xin ban ben zhong , huan ke yi yong set colsep ming ling lai shi xian shang mian de gong neng :
  SQL> set colsep ,
  SQL> select * from dept;
  10,ACCOUNTING ,NEW YORK
  20,RESEARCH ,DALLAS
  30,SALES ,CHICAGO
  40,OPERATIONS ,BOSTON
  35,aa ,bb
  ◆3. dong tai sheng cheng spool ming ling suo xu de wen jian ming
  zai wo men shang mian de li zi zhong ,spool ming ling suo xu yao de wen jian ming dou shi gu ding de 。 you shi wo men xu yao mei tian spool yi ci , bing qie mei ci spool de wen jian ming dou bu xiang tong , ru wen jian ming bao han dang tian de ri qi , gai ru he shi xian ne ?
  column dat1 new_value filename;
  select to_char(sysdate,'yyyymmddhh24mi') dat1 from dual;
  spool c:&&filename..txt
  select * from dept;
  spool off;
  ◆4. ru he cong jiao ben wen jian zhong de dao WINDOWS huan jing bian liang de zhi :
  zai windos zhong :
  spool c:temp%ORACLE_SID%.txt
  select * from dept;
  ...
  spool off
  zai shang mian de li zi zhong , tong guo %ORACLE_SID% de fang shi yin yong huan jing bian liang ORACLE_SID de zhi , ru guo ORACLE_SID de zhi wei orcl, ze sheng cheng de spool wen jian ming wei :orcl.txt
  zai UNIX zhong :
  spool c:temp$ORACLE_SID.txt
  select * from dept;
  ...
  spool off
  zai shang mian de li zi zhong , tong guo $ORACLE_SID de fang shi yin yong huan jing bian liang ORACLE_SID de zhi , ru guo ORACLE_SID de zhi wei orcl, ze sheng cheng de spool wen jian ming wei :orcl.txt
  ◆5. ru he zhi ding que sheng de bian ji jiao ben de mu lu
  zai sql*plus zhong , ke yi yong save ming ling , jiang shang yi tiao zhi xing de sql yu ju bao cun dao yi ge wen jian zhong , dan shi ru he she zhi gai wen jian de que sheng mu lu ne ?
  tong guo SQL> set editfile c:tempfile.sql ming ling , ke yi she zhi qi que sheng mu lu wei c:tmpe, que sheng wen jian ming wei file.sql。
  ◆6. ru he chu qu biao zhong xiang tong de xing
  zhao dao xiang tong de xing :
  SELECT * FROM dept a
  WHERE ROWID <> (SELECT MAX(ROWID)
  FROM dept b
  WHERE a.deptno = b.deptno
  AND a.dname = b.dname -- Make sure all columns are compared
  AND a.loc = b.loc);
  zhu shi :
  ru guo zhi zhao deptno lie xiang tong de xing , shang mian de cha xun ke yi gai wei :
  SELECT * FROM dept a
  WHERE ROWID <> (SELECT MAX(ROWID)
  FROM dept b
  WHERE a.deptno = b.deptno)
  shan chu xiang tong de xing :
  DELETE FROM dept a
  WHERE ROWID <> (SELECT MAX(ROWID
  FROM dept b
  WHERE a.deptno = b.deptno
  AND a.dname = b.dname -- Make sure all columns are compared
  AND a.loc = b.loc);
  zhu yi : shang mian bing bu shan chu lie zhi wei null de xing 。
  ◆7. ru he xiang shu ju ku zhong cha ru liang ge dan yin hao (’’)
  Insert inot dept values(35,’aa’’’’bb’,’a’’b’);
  zai cha ru shi , yong liang ge ’ biao shi yi ge ’。
  ◆8. ru he she zhi sql*plus de sou xun lu jing , zhe yang zai yong @ ming ling shi , jiu bu yong shu ru wen jian de quan lu jing 。
  she zhi SQLPATH huan jing bian liang 。
  ru :
  SQLPATH = C:ORANTDBS;C:APPSSCRIPTS;C:MYSCRIPTS
  ◆9.@ yu @@ de qu bie shi shen me ?
  @ deng yu start ming ling , yong lai yun xing yi ge sql jiao ben wen jian 。
  @ ming ling tiao yong dang qian mu lu xia de , huo zhi ding quan lu jing , huo ke yi tong guo SQLPATH huan jing bian liang sou xun dao de jiao ben wen jian 。
  @@ yong zai jiao ben wen jian zhong , yong lai zhi ding yong @@ zhi xing de wen jian yu @@ suo zai de wen jian zai tong yi mu lu , er bu yong zhi ding quan lu jing , ye bu cong SQLPATH huan jing bian liang zhi ding de lu jing zhong xun zhao wen jian , gai ming ling yi ban yong zai qian tao jiao ben wen jian zhong 。
  ◆10.& yu && de qu bie
  & yong lai chuang jian yi ge lin shi bian liang , mei dang yu dao zhe ge lin shi bian liang shi , dou hui ti shi ni shu ru yi ge zhi 。
  && yong lai chuang jian yi ge chi jiu bian liang , jiu xiang yong yong define ming ling huo dai new_vlaue zi ju de column ming ling chuang jian de chi jiu bian liang yi yang 。 dang yong && ming ling yin yong zhe ge bian liang shi , bu hui mei ci yu dao gai bian liang jiu ti shi yong hu jian ru zhi , er zhi shi zai di yi ci yu dao shi ti shi yi ci 。
  ru , jiang xia mian san xing yu ju cun wei yi ge jiao ben wen jian , yun xing gai jiao ben wen jian , hui ti shi san ci , rang shu ru deptnoval de zhi :
  select count(*) from emp where deptno = &deptnoval;
  select count(*) from emp where deptno = &deptnoval;
  select count(*) from emp where deptno = &deptnoval;
  jiang xia mian san xing yu ju cun wei yi ge jiao ben wen jian , yun xing gai jiao ben wen jian , ze zhi hui ti shi yi ci , rang shu ru deptnoval de zhi :
  select count(*) from emp where deptno = &deptnoval;
  select count(*) from emp where deptno = &deptnoval;
  select count(*) from emp where deptno = &deptnoval;
  ◆11. yin ru copy de mu de
  Copy ming ling zai liang ge shu ju ku zhi jian kao bei shu ju shi te bie you yong , te bie shi gai ming ling ke yi zai liang ge shu ju ku zhi jian chuan di long xing zi duan de shu ju 。
  que dian :
  zai liang ge shu ju ku zhi jian chuan di shu ju shi , you ke neng diu shi jing du (lose precision)。
  ◆12. wen shen me zai xiu gai da liang de xing shi , wo de jiao ben hui bian de hen man ?
  dang tong guo PL/SQL kuai xiu gai yi ge biao zhong de xu duo xing shi , ni hui chuang jian zai biao shang chuang jian yi ge cursor, dan shi zhi you zai ni guan bi cursor shi , cai hui shi fang ROLLBACK SEGMENT, zhe yang , dang cursor reng ran da kai shi , xiu gai guo cheng hui bian man , zhe shi yin wei shu ju ku bu de bu sou xun da liang de rollback segment yi bian yu wei hu du yi zhi xing 。 wei le bi mian zhe yang qing kuang , shi zhe zai biao shang jia yi ge biao zhi zi duan lai miao shu gai xing shi fou yi jing bei xiu gai , ran hou guan bi gai cursor, ran hou zai da kai gai cursor。 mei ci ke yi xiu gai 5000 xing .【原文】【汉音对照
 
 
 
 
 
 
 
 
日版宠物情人插曲《Winding Road》歌词

日版宠物情人2017的插曲,很带节奏感,日语的,女生唱的。 最后听见是在第8集的时候女主手割伤了,然后男主用嘴帮她吸了一下,插曲就出来了。 歌手:Def...

兄弟共妻,我成了他们夜里的美食

老钟家的两个儿子很特别,就是跟其他的人不太一样,魔一般的执着。兄弟俩都到了要结婚的年龄了,不管自家老爹怎么磨破嘴皮子,兄弟俩说不娶就不娶,老父母为兄弟两操碎了心...

如何磨出破洞牛仔裤?牛仔裤怎么剪破洞?

把牛仔裤磨出有线的破洞 1、具体工具就是磨脚石,下面垫一个硬物,然后用磨脚石一直磨一直磨,到把那块磨薄了,用手撕开就好了。出来的洞啊很自然的。需要猫须的话调几...

我就是扫描下图得到了敬业福和爱国福

先来看下敬业福和爱国福 今年春节,支付宝再次推出了“五福红包”活动,表示要“把欠大家的敬业福都还给大家”。 今天该活动正式启动,和去年一样,需要收集“五福”...

冰箱异味产生的原因和臭味去除的方法

有时候我们打开冰箱就会闻到一股异味,冰箱里的这种异味是因为一些物质发出的气味的混合体,闻起来让人恶心。 产生这些异味的主要原因有以下几点。 1、很多人有这种习...

 
 
an zhuang le oracle9i, jin ru sal*plus xu yao shu ru yong hu ming 、 kou ling he zhu ji biao zhi fu system/manager sys/change_on_intall scott/tiger internal/oracle yi shang wei chu shi mi ma zhu ji zi fu chuan kong huo zhe shi @+ ni de fu wu ming , zai ben di deng lu bu xu yao ben di fu wu ming 。 xia mian jiu jie shao yi xia yi xie chang yong de sql*plus ming ling : shou xian cha kan dang qian shi yong de shu ju ku shi li : select name from V$database; qie huan liang ge shu ju ku shi li zai sqlplus li connect [url=mailto:username/password@sid]username/password@sid[/url] huo host set ORACLE_SID=sid huo cmd li set ORACLE_SID=sid huo xiu gai zhu ce biao li ORACLE_SID xiang ◆1. zhi xing yi ge SQL jiao ben wen jian SQL>start file_name SQL>@ file_name wo men ke yi jiang duo tiao sql yu ju bao cun zai yi ge wen ben wen jian zhong , zhe yang dang yao zhi xing zhe ge wen jian zhong de suo you de sql yu ju shi , yong shang mian de ren yi ming ling ji ke , zhe lei si yu dos zhong de pi chu li 。 ◆2. dui dang qian de shu ru jin xing bian ji SQL>edit ◆3. chong xin yun xing shang yi ci yun xing de sql yu ju SQL>/ ◆4. jiang xian shi de nei rong shu chu dao zhi ding wen jian SQL> SPOOL file_name zai ping mu shang de suo you nei rong dou bao han zai gai wen jian zhong , bao kuo ni shu ru de sql yu ju 。 ◆5. guan bi spool shu chu SQL> SPOOL OFF zhi you guan bi spool shu chu , cai hui zai shu chu wen jian zhong kan dao shu chu de nei rong 。 ◆6. xian shi yi ge biao de jie gou SQL> desc table_name ◆7. COL ming ling : zhu yao ge shi hua lie de xian shi xing shi 。 gai ming ling you xu duo xuan xiang , ju ti ru xia : COL[UMN] [{ column|expr} [ option ...]] Option xuan xiang ke yi shi ru xia de zi ju : ALI[AS] alias CLE[AR] FOLD_A[FTER] FOLD_B[EFORE] FOR[MAT] format HEA[DING] 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). gai bian que sheng de lie biao ti 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). jiang lie ming ENAME gai wei xin lie ming EMPLOYEE NAME bing jiang xin lie ming fang zai liang xing shang : Sql>select * from emp Department name Salary ---------- ---------- ---------- 10 aaa 11 SQL> COLUMN ENAME HEADING ’Employee|Name’ Sql>select * from emp Employee Department name Salary ---------- ---------- ---------- 10 aaa 11 note: the col heading turn into two lines from one line. 3). gai bian lie de xian shi chang du : 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). she zhi lie biao ti de dui qi fang shi 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 dui yu NUMBER xing de lie , lie biao ti que sheng zai you bian , qi ta lei xing de lie biao ti que sheng zai zuo bian 5). bu rang yi ge lie xian shi zai ping mu shang NOPRI[NT]|PRI[NT] SQL> col job noprint SQL> / EMPNO ENAME ---------- ---------------------------------------- 7369 SMITH 7499 ALLEN 7521 WARD 6). ge shi hua NUMBER lei xing lie de xian shi : SQL> COLUMN SAL FORMAT $99,990 SQL> / Employee Department Name Salary Commission ---------- ---------- --------- ---------- 30 ALLEN $1,600 300 7). xian shi lie zhi shi , ru guo lie zhi wei NULL zhi , yong text zhi dai ti NULL zhi COMM NUL[L] text SQL>COL COMM NUL[L] text 8). she zhi yi ge lie de hui rao fang shi 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). xian shi lie de dang qian de xian shi shu xing zhi SQL> COLUMN column_name 10). jiang suo you lie de xian shi shu xing she wei que sheng zhi SQL> CLEAR COLUMNS ◆8. ping bi diao yi ge lie zhong xian shi de xiang tong de zhi 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. zai shang mian ping bi diao yi ge lie zhong xian shi de xiang tong de zhi de xian shi zhong , mei dang lie zhi bian hua shi zai zhi bian hua zhi qian cha ru n ge kong xing 。 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. xian shi dui BREAK de she zhi SQL> BREAK ◆11. shan chu 6、7 de she zhi SQL> CLEAR BREAKS ◆12. Set ming ling : gai ming ling bao han xu duo zi ming ling : SET system_variable value system_variable value ke yi shi ru xia de zi ju zhi yi : 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[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). she zhi dang qian session shi fou dui xiu gai de shu ju jin xing zi dong ti jiao SQL>SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n} 2). zai yong start ming ling zhi xing yi ge sql jiao ben shi , shi fou xian shi jiao ben zhong zheng zai zhi xing de SQL yu ju SQL> SET ECHO {ON|OFF} 3). shi fou xian shi dang qian sql yu ju cha xun huo xiu gai de xing shu SQL> SET FEED[BACK] {6|n|ON|OFF} mo ren zhi you jie guo da yu 6 xing shi cai xian shi jie guo de xing shu 。 ru guo set feedback 1 , ze bu guan cha xun dao duo shao xing dou fan hui 。 dang wei off shi , yi lv bu xian shi cha xun de xing shu 4). shi fou xian shi lie biao ti SQL> SET HEA[DING] {ON|OFF} dang set heading off shi , zai mei ye de shang mian bu xian shi lie biao ti , er shi yi kong bai xing dai ti 5). she zhi yi xing ke yi rong na de zi fu shu SQL> SET LIN[ESIZE] {80|n} ru guo yi xing de shu chu nei rong da yu she zhi de yi xing ke rong na de zi fu shu , ze zhe xing xian shi 。 6). she zhi ye yu ye zhi jian de fen ge SQL> SET NEWP[AGE] {1|n|NONE} dang set newpage 0 shi , hui zai mei ye de kai tou you yi ge xiao de hei fang kuang 。 dang set newpage n shi , hui zai ye he ye zhi jian ge zhe n ge kong xing 。 dang set newpage none shi , hui zai ye he ye zhi jian mei you ren he jian ge 。 7). xian shi shi , yong text zhi dai ti NULL zhi SQL> SET NULL text 8). she zhi yi ye you duo shao xing shu SQL> SET PAGES[IZE] {24|n} ru guo she wei 0, ze suo you de shu chu nei rong wei yi ye bing qie bu xian shi lie biao ti 9). shi fou xian shi yong DBMS_OUTPUT.PUT_LINE bao jin xing shu chu de xin xi 。 SQL> SET SERVEROUT[PUT] {ON|OFF} zai bian xie cun chu guo cheng shi , wo men you shi hui yong dbms_output.put_line jiang bi yao de xin xi shu chu , yi bian dui cun chu guo cheng jin xing tiao shi , zhi you jiang serveroutput bian liang she wei on hou , xin xi cai neng xian shi zai ping mu shang 。 10). dang SQL yu ju de chang du da yu LINESIZE shi , shi fou zai xian shi shi jie qu SQL yu ju 。 SQL> SET WRA[P] {ON|OFF} dang shu chu de xing de chang du da yu she zhi de xing de chang du shi ( yong set linesize n ming ling she zhi ), dang set wrap on shi , shu chu xing de duo yu de zi fu hui ling qi yi xing xian shi , fou ze , hui jiang shu chu xing de duo yu zi fu qie chu , bu yu xian shi 。 11). shi fou zai ping mu shang xian shi shu chu de nei rong , zhu yao yong yu SPOOL jie he shi yong 。 SQL> SET TERM[OUT] {ON|OFF} zai yong spool ming ling jiang yi ge da biao zhong de nei rong shu chu dao yi ge wen jian zhong shi , jiang nei rong shu chu zai ping mu shang hui hao fei da liang de shi jian , she zhi set termspool off hou , ze shu chu de nei rong zhi hui bao cun zai shu chu wen jian zhong , bu hui xian shi zai ping mu shang , ji da de ti gao le spool de su du 。 12). jiang SPOOL shu chu zhong mei xing hou mian duo yu de kong ge qu diao SQL> SET TRIMS[OUT] {ON|OFF} 13) xian shi mei ge sql yu ju hua fei de zhi xing shi jian set TIMING {ON|OFF} ◆14. xiu gai sql buffer zhong de dang qian xing zhong , di yi ge chu xian de zi fu chuan C[HANGE] /old_value/new_value SQL> l 1* select * from dept SQL> c/dept/emp 1* select * from emp ◆15. bian ji sql buffer zhong de sql yu ju EDI[T] ◆16. xian shi sql buffer zhong de sql yu ju ,list n xian shi sql buffer zhong de di n xing , bing shi di n xing cheng wei dang qian xing L[IST] [n] ◆17. zai sql buffer de dang qian xing xia mian jia yi xing huo duo xing I[NPUT] ◆18. jiang zhi ding de wen ben jia dao sql buffer de dang qian xing hou mian 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. jiang sql buffer zhong de sql yu ju bao cun dao yi ge wen jian zhong SAVE file_name ◆20. jiang yi ge wen jian zhong de sql yu ju dao ru dao sql buffer zhong GET file_name ◆21. zai ci zhi xing gang cai yi jing zhi xing de sql yu ju RUN or / ◆22. zhi xing yi ge cun chu guo cheng EXECUTE procedure_name ◆23. zai sql*plus zhong lian jie dao zhi ding de shu ju ku CONNECT [url=mailto:user_name/passwd@db_alias]user_name/passwd@db_alias[/url] ◆24. she zhi mei ge bao biao de ding bu biao ti TTITLE ◆25. she zhi mei ge bao biao de yi bu biao ti BTITLE ◆26. xie yi ge zhu shi REMARK [text] ◆27. jiang zhi ding de xin xi huo yi ge kong xing shu chu dao ping mu shang PROMPT [text] ◆28. jiang zhi xing de guo cheng zan ting , deng dai yong hu xiang ying hou ji xu zhi xing PAUSE [text] Sql>PAUSE Adjust paper and press RETURN to continue. ◆29. jiang yi ge shu ju ku zhong de yi xie shu ju kao bei dao ling wai yi ge shu ju ku ( ru jiang yi ge biao de shu ju kao bei dao ling yi ge shu ju ku ) COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)] USING query sql>COPY FROM [url=mailto:SCOTT/TIGER@HQ]SCOTT/TIGER@HQ[/url] TO [url=mailto:JOHN/CHROME@WEST]JOHN/CHROME@WEST[/url] create emp_temp USING SELECT * FROM EMP ◆30. bu tui chu sql*plus, zai sql*plus zhong zhi xing yi ge cao zuo xi tong ming ling : HOST Sql> host hostname gai ming ling zai windows xia ke neng bei zhi chi 。 ◆31. zai sql*plus zhong , qie huan dao cao zuo xi tong ming ling ti shi fu xia , yun xing cao zuo xi tong ming ling hou , ke yi zai ci qie huan hui sql*plus: ! sql>! $hostname $exit sql> gai ming ling zai windows xia bu bei zhi chi 。 ◆32. xian shi sql*plus ming ling de bang zhu HELP ru he an zhuang bang zhu wen jian : Sql>@ ?sqlplusadminhelphlpbld.sql ?sqlplusadminhelphelpus.sql Sql>help index ◆33. xian shi sql*plus xi tong bian liang de zhi huo sql*plus huan jing bian liang de zhi Syntax SHO[W] option where option represents one of the following terms or clauses: system_variable ALL BTI[TLE] ERR[ORS] [{FUNCTION|PROCEDURE|PACKAGE|PACKAGE BODY| TRIGGER|VIEW|TYPE|TYPE BODY} [schema.]name] LNO PARAMETERS [parameter_name] PNO REL[EASE] REPF[OOTER] REPH[EADER] SGA SPOO[L] SQLCODE TTI[TLE] USER 1) . xian shi dang qian huan jing bian liang de zhi : Show all 2) . xian shi dang qian zai chuang jian han shu 、 cun chu guo cheng 、 chu fa qi 、 bao deng dui xiang de cuo wu xin xi Show error dang chuang jian yi ge han shu 、 cun chu guo cheng deng chu cuo shi , bian ke yi yong gai ming ling cha kan zai na ge di fang chu cuo ji xiang ying de chu cuo xin xi , jin xing xiu gai hou zai ci jin xing bian yi 。 3) . xian shi chu shi hua shen shu de zhi : show PARAMETERS [parameter_name] 4) . xian shi shu ju ku de ban ben : show REL[EASE] 5) . xian shi SGA de da xiao show SGA 6). xian shi dang qian de yong hu ming show user SQL>SET DEFINE ON jing chang yong yu yun xing SQL jiao ben zhong , dui bian liang de hong ti huan (&) SQL>DEFINE TSTEMP=TEMPTS jiang yun xing jiao ben zhong de &TSTEMP ti huan cheng TEMPTS SQL>SET DEFINE OFF guan bi hong ti huan , jing chang yong yu yun xing PACKAGE huo cun chu guo cheng Sql*plus zhong wen cang zhe hao duo ji qiao , ru guo zhang wo zhe xie ji qiao , dui yu zai oracle shu ju ku xia jin xing kuai su kai fa yu you xiao wei hu shu ju ku dou shi you yi de 。 ◆1. shi yong SQL*PLUS dong tai sheng cheng pi liang jiao ben jiang spool yu select ming ling jie he qi lai shi yong , ke yi sheng cheng yi ge jiao ben , jiao ben zhong bao han you ke yi pi liang zhi xing mou yi ren wu de yu ju 。 li 1: sheng cheng yi ge jiao ben , shan chu SCOTT yong hu xia de suo you de biao : a. chuang jian gen_drop_table.sql wen jian , bao han ru xia yu ju : SPOOL c:drop_table.sql SELECT 'DROP TABLE '|| table_name ||';' FROM user_tables; SPOOL OFF b. yi SCOTT yong hu deng lu shu ju ku SQLPLUS > @ …..gen_dorp_table.sql c. zai c pan gen mu lu xia hui sheng cheng wen jian drop_table.sql wen jian , bao han shan chu suo you biao de yu ju , ru xia suo shi : SQL> SELECT 'DROP TABLE '|| table_name ||';' FROM user_tables; 'DROPTABLE'||TABLE_NAME||';' -------------------------------------------------------------------------------- DROP TABLE DEPT; DROP TABLE EMP; DROP TABLE PARENT; DROP TABLE STAT_VENDER_TEMP; DROP TABLE TABLE_FORUM; 5 rows selected. SQL> SPOOL OFF d. dui sheng cheng de drop_table.sql wen jian jin xing bian ji qu diao bu bi yao de bu fen , zhi liu xia drop table ….. yu ju e. zai scott yong hu xia yun xing dorp_table.sql wen jian , shan chu scott yong hu xia suo you de biao 。 SQLPLUS > @ c:dorp_table.sql zai shang mian de cao zuo zhong , zai sheng cheng de jiao ben wen jian zhong hui you duo yu de zi fu , ru yun xing de sql yu ju , biao ti , huo fan hui de xing shu , xu yao wo men bian ji gai jiao ben hou zai yun xing , gei shi ji de cao zuo dai lai zhu duo bu bian 。 lan duo shi ren de ben xing , zhe cu shi wo men yong geng jian dan de ban fa lai shi xian shang mian de ren wu 。 a. chuang jian gen_drop_table.sql wen jian , bao han ru xia yu ju : set echo off set feedback off set newpage none set pagesize 5000 set linesize 500 set verify off set pagesize 0 set term off set trims on set linesize 600 set heading off set timing off set verify off set numwidth 38 SPOOL c:drop_table.sql SELECT 'DROP TABLE '|| table_name ||';' FROM user_tables; SPOOL OFF b. yi SCOTT yong hu deng lu shu ju ku SQLPLUS > @ …..gen_dorp_table.sql c. zai c pan gen mu lu xia hui sheng cheng wen jian drop_table.sql wen jian , bao han shan chu suo you biao de yu ju , ru xia suo shi : DROP TABLE DEPT; DROP TABLE EMP; DROP TABLE PARENT; DROP TABLE STAT_VENDER_TEMP; DROP TABLE TABLE_FORUM; d. zai scott yong hu xia yun xing dorp_table.sql wen jian , shan chu scott yong hu xia suo you de biao 。 SQLPLUS > @ c:dorp_table.sql ◆2. jiang yi ge biao zhong de shu ju dao chu sheng cheng yi ge wen ben wen jian , lie yu lie zhi jian yi ”,” ge kai set echo off set feedback off set newpage none set pagesize 5000 set linesize 500 set verify off set pagesize 0 set term off set trims on set linesize 600 set heading off set timing off set verify off set numwidth 38 SPOOL c:drop_table.sql select DEPTNO || ',' || DNAME FROM DEPT; SPOOL OFF jiang shang mian de nei rong bao cun wei yi ge wen ben wen jian hou , yi scott deng lu , zhi xing gai wen jian hou xian shi jie guo : 10,ACCOUNTING 20,RESEARCH 30,SALES 40,OPERATIONS tong guo shang mian de liang ge li zi , wo men ke yi jiang : set echo off set feedback off set newpage none set pagesize 5000 set linesize 500 set verify off set pagesize 0 set term off set trims on set linesize 600 set heading off set timing off set verify off set numwidth 38 SPOOL c: ju ti de wen jian ming ni yao yun xing de sql yu ju SPOOL OFF zuo wei yi ge mo ban , zhi yao jiang bi yao de yu ju jia ru zhe ge mo ban jiu ke yi le 。 zai oracle de jiao xin ban ben zhong , huan ke yi yong set colsep ming ling lai shi xian shang mian de gong neng : SQL> set colsep , SQL> select * from dept; 10,ACCOUNTING ,NEW YORK 20,RESEARCH ,DALLAS 30,SALES ,CHICAGO 40,OPERATIONS ,BOSTON 35,aa ,bb ◆3. dong tai sheng cheng spool ming ling suo xu de wen jian ming zai wo men shang mian de li zi zhong ,spool ming ling suo xu yao de wen jian ming dou shi gu ding de 。 you shi wo men xu yao mei tian spool yi ci , bing qie mei ci spool de wen jian ming dou bu xiang tong , ru wen jian ming bao han dang tian de ri qi , gai ru he shi xian ne ? column dat1 new_value filename; select to_char(sysdate,'yyyymmddhh24mi') dat1 from dual; spool c:&&filename..txt select * from dept; spool off; ◆4. ru he cong jiao ben wen jian zhong de dao WINDOWS huan jing bian liang de zhi : zai windos zhong : spool c:temp%ORACLE_SID%.txt select * from dept; ... spool off zai shang mian de li zi zhong , tong guo %ORACLE_SID% de fang shi yin yong huan jing bian liang ORACLE_SID de zhi , ru guo ORACLE_SID de zhi wei orcl, ze sheng cheng de spool wen jian ming wei :orcl.txt zai UNIX zhong : spool c:temp$ORACLE_SID.txt select * from dept; ... spool off zai shang mian de li zi zhong , tong guo $ORACLE_SID de fang shi yin yong huan jing bian liang ORACLE_SID de zhi , ru guo ORACLE_SID de zhi wei orcl, ze sheng cheng de spool wen jian ming wei :orcl.txt ◆5. ru he zhi ding que sheng de bian ji jiao ben de mu lu zai sql*plus zhong , ke yi yong save ming ling , jiang shang yi tiao zhi xing de sql yu ju bao cun dao yi ge wen jian zhong , dan shi ru he she zhi gai wen jian de que sheng mu lu ne ? tong guo SQL> set editfile c:tempfile.sql ming ling , ke yi she zhi qi que sheng mu lu wei c:tmpe, que sheng wen jian ming wei file.sql。 ◆6. ru he chu qu biao zhong xiang tong de xing zhao dao xiang tong de xing : SELECT * FROM dept a WHERE ROWID <> (SELECT MAX(ROWID) FROM dept b WHERE a.deptno = b.deptno AND a.dname = b.dname -- Make sure all columns are compared AND a.loc = b.loc); zhu shi : ru guo zhi zhao deptno lie xiang tong de xing , shang mian de cha xun ke yi gai wei : SELECT * FROM dept a WHERE ROWID <> (SELECT MAX(ROWID) FROM dept b WHERE a.deptno = b.deptno) shan chu xiang tong de xing : DELETE FROM dept a WHERE ROWID <> (SELECT MAX(ROWID FROM dept b WHERE a.deptno = b.deptno AND a.dname = b.dname -- Make sure all columns are compared AND a.loc = b.loc); zhu yi : shang mian bing bu shan chu lie zhi wei null de xing 。 ◆7. ru he xiang shu ju ku zhong cha ru liang ge dan yin hao (’’) Insert inot dept values(35,’aa’’’’bb’,’a’’b’); zai cha ru shi , yong liang ge ’ biao shi yi ge ’。 ◆8. ru he she zhi sql*plus de sou xun lu jing , zhe yang zai yong @ ming ling shi , jiu bu yong shu ru wen jian de quan lu jing 。 she zhi SQLPATH huan jing bian liang 。 ru : SQLPATH = C:ORANTDBS;C:APPSSCRIPTS;C:MYSCRIPTS ◆9.@ yu @@ de qu bie shi shen me ? @ deng yu start ming ling , yong lai yun xing yi ge sql jiao ben wen jian 。 @ ming ling tiao yong dang qian mu lu xia de , huo zhi ding quan lu jing , huo ke yi tong guo SQLPATH huan jing bian liang sou xun dao de jiao ben wen jian 。 @@ yong zai jiao ben wen jian zhong , yong lai zhi ding yong @@ zhi xing de wen jian yu @@ suo zai de wen jian zai tong yi mu lu , er bu yong zhi ding quan lu jing , ye bu cong SQLPATH huan jing bian liang zhi ding de lu jing zhong xun zhao wen jian , gai ming ling yi ban yong zai qian tao jiao ben wen jian zhong 。 ◆10.& yu && de qu bie & yong lai chuang jian yi ge lin shi bian liang , mei dang yu dao zhe ge lin shi bian liang shi , dou hui ti shi ni shu ru yi ge zhi 。 && yong lai chuang jian yi ge chi jiu bian liang , jiu xiang yong yong define ming ling huo dai new_vlaue zi ju de column ming ling chuang jian de chi jiu bian liang yi yang 。 dang yong && ming ling yin yong zhe ge bian liang shi , bu hui mei ci yu dao gai bian liang jiu ti shi yong hu jian ru zhi , er zhi shi zai di yi ci yu dao shi ti shi yi ci 。 ru , jiang xia mian san xing yu ju cun wei yi ge jiao ben wen jian , yun xing gai jiao ben wen jian , hui ti shi san ci , rang shu ru deptnoval de zhi : select count(*) from emp where deptno = &deptnoval; select count(*) from emp where deptno = &deptnoval; select count(*) from emp where deptno = &deptnoval; jiang xia mian san xing yu ju cun wei yi ge jiao ben wen jian , yun xing gai jiao ben wen jian , ze zhi hui ti shi yi ci , rang shu ru deptnoval de zhi : select count(*) from emp where deptno = &deptnoval; select count(*) from emp where deptno = &deptnoval; select count(*) from emp where deptno = &deptnoval; ◆11. yin ru copy de mu de Copy ming ling zai liang ge shu ju ku zhi jian kao bei shu ju shi te bie you yong , te bie shi gai ming ling ke yi zai liang ge shu ju ku zhi jian chuan di long xing zi duan de shu ju 。 que dian : zai liang ge shu ju ku zhi jian chuan di shu ju shi , you ke neng diu shi jing du (lose precision)。 ◆12. wen shen me zai xiu gai da liang de xing shi , wo de jiao ben hui bian de hen man ? dang tong guo PL/SQL kuai xiu gai yi ge biao zhong de xu duo xing shi , ni hui chuang jian zai biao shang chuang jian yi ge cursor, dan shi zhi you zai ni guan bi cursor shi , cai hui shi fang ROLLBACK SEGMENT, zhe yang , dang cursor reng ran da kai shi , xiu gai guo cheng hui bian man , zhe shi yin wei shu ju ku bu de bu sou xun da liang de rollback segment yi bian yu wei hu du yi zhi xing 。 wei le bi mian zhe yang qing kuang , shi zhe zai biao shang jia yi ge biao zhi zi duan lai miao shu gai xing shi fou yi jing bei xiu gai , ran hou guan bi gai cursor, ran hou zai da kai gai cursor。 mei ci ke yi xiu gai 5000 xing .
󰈣󰈤
  免责声明:本文仅代表作者个人观点,与王朝网络无关。王朝网络登载此文出于传递更多信息之目的,并不意味着赞同其观点或证实其描述,其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
温柔美女韩雪写真
杨幂全新写真
网易红人PLMM草梅队长
河智苑Compagna09春装
看 这就是我的学校
惠山古迹区
雪后看花仙
元宵烟火
 
>>返回首页<<
 为你推荐
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 热帖排行
 
 
 
 
©2005- 王朝网络 版权所有