SQL*Plus 浣跨敤鎶?宸_SQL

SQL*Plus 浣跨敤鎶?宸?

8. 浣跨敤&绗﹀彿琛ㄧず涓存椂鍙橀噺銆?鍦∣racle涓?彲浠ヤ娇鐢ㄥ彉閲忔潵缂栧啓閫氱敤鐨凷QL锛屽湪杩愯?璇?彞鏃讹紝涓哄彉閲忚緭鍏ュ?笺??   a. 涓存椂鍙橀噺鍙?湪浣跨敤瀹冪殑SQL璇?彞涓?湁鏁堬紝鍙橀噺鍊间笉鑳戒繚鐣欍?備复鏃跺彉閲忎篃琚?О涓烘浛鎹㈠彉閲忋??		select salary,&column_n		from employees		where rownum < &row   b. 鍦⊿QL涓??鏋滃笇鏈涢噸鏂颁娇鐢ㄦ煇涓?彉閲忓苟涓斾笉甯屾湜閲嶆柊鎻愮ず杈撳叆鍙橀噺鍊兼槸锛岄偅涔堝彲浠ヤ娇鐢?amp;&绗﹀彿鏉ュ畾涔変复鏃跺彉閲忋??   c. set verify off/on 鏄剧ず鍜屼笉鏄剧ず绫讳技濡備笅鐨勪俊鎭?細	鍘熷??   1: select salary,&column_name, first_name	鏂板??   1: select salary,last_name, first_name

9. 浣跨敤宸插畾涔夊彉閲?鍏锋湁鏄庣‘瀹氫箟鐨勫彉閲忥紝璇ュ彉閲忕殑鍊间細涓?鐩翠繚鐣欏埌琚?樉绀哄湴鍒犻櫎锛岄噸瀹氫箟鎴栭??鍑篠QL*Plus涓烘?)   a. 鍦⊿QL璇?彞涓?紝鍙?互鍦ㄤ娇鐢ㄥ彉閲忎箣鍓嶅?鍙橀噺杩涜?瀹氫箟锛岀劧鍚庡湪SQL璇?彞涓?彲浠ュ?鍚冧娇鐢ㄨ繖涓?彉閲忋??   b. define/accept 瀹氫箟鍙橀噺锛?c. undefine 鍒犻櫎鍙橀噺銆?     define 鏄剧ず鎵?鏈夊凡瀹氫箟鍙橀噺锛?define variable 鏄剧ず鎸囧畾鍙橀噺鍚嶇О锛屽?煎拰鍏舵暟鎹?被鍨嬨??   define variable = value 鍒涘缓涓?涓猚har 绫诲瀷鍙橀噺锛屽苟璧嬪垵濮嬪??		SQL> define sal = 4400		SQL> select * from employees		  2  where salary = &sal		  3  /   d銆?浣跨敤accept鍛戒护瀹氫箟鍙橀噺锛屽苟涓斿畾鍒朵竴涓?敤鎴锋彁绀恒?傚畾涔夋槸鍙?互鏄庣‘鎸囧畾鍙橀噺绫诲瀷銆?	acc[ept] variable [data_type][for[mat] format] [def[ault] default] [prompt text|nopr[ompt]][hide]	SQL> accept test char prompt 'Hi, you are required first_name which is '	Hi, you are required first_name which is 'Put'	SQL> select * from employees where first_name = &test;	鍘熷??   1: select * from employees where first_name = &test	鏂板??   1: select * from employees where first_name = 'Put'

10. column 鏍煎紡鍖栧垪鐨勬樉绀烘晥鏋滐紝鍖呮嫭鍒楁爣棰樺拰鍒楁暟鎹??備娇鐢╟lear columns 鍒犻櫎鏍煎紡鍖栥??    col[umn] [{column_name|ali[as] alias}[options]]    a銆俢olumn_name 鎸囧畾鍒楀悕銆?b.alias alias鎸囧畾瑕佹牸寮忓寲鐨勫垪鐨勫埆鍚嶃??    c. options 鎸囧畾鐢ㄤ簬鏍煎紡鍖栦簡鎴栧埆鍚嶇殑涓?涓?垨鑰呭?涓??夐」銆?	column salary heading 'sal' format L9999.99	column last_name heading 'Name' fromat a18column column_name for[mat] formatoption閫夐」锛?for[mat] format 灏嗗垪鎴栧垪鍚嶇殑鏄剧ず鏍煎紡璁剧疆涓虹敱format瀛楃?涓叉寚瀹氱殑鏍煎紡銆?	     hea[ding] text 璁剧疆鏈塼ext鏀?粯鎸囧畾鐨勫垪鏍囬?銆?	     jus[tify] [{left|center|right}] 灏嗗垪鐨勮緭鍑轰俊鎭?乏瀵归綈銆佸眳涓?拰鍙冲?榻愩??

11. pagesize 鍛戒护璁剧疆涓?椤垫樉绀哄?灏戣?鏁版嵁锛屼粠鑰屾帶鍒舵瘡涓?椤垫樉绀烘暟鎹?噺銆?    set pagesize n  (n 琛ㄧず姣忎竴椤靛ぇ灏忕殑姝ｆ暣鏁帮紝鏈?澶ф槸50000锛岄粯璁ゅ?兼槸14)    椤靛苟涓嶆槸浠呬粎鐢辫緭鍑虹殑鏁版嵁琛屾瀯鎴愶紝鑰屾槸sql*plus鏄剧ず鍒板睆骞曚笂鎵?鏈夌粨鏋滄瀯鎴愶紝鍖呭惈鏍囬?鍜岀┖琛岀瓑銆?12. linesize 鍛戒护 璁剧疆涓?琛屾暟鎹?彲浠ュ?绾崇殑瀛楃?鏁伴噺锛岄粯璁ゆ暟閲忔槸80. 濡傛灉linesize鍊艰緝灏忥紝閭ｄ箞涓?琛屾暟鎹?彲鑳介渶瑕佸?琛屾樉绀恒??    set linesize n (n琛ㄧず灞忓箷涓婁竴琛屾暟鎹?彲浠ュ?绾崇殑瀛楃?鏁伴噺锛屾湁鏁堣寖鍥存槸1 - 32767)	set pagesize 100;	set linesize 5678;	column employee_id justify center;	column manager_id jus center;	column department_id jus center;	select * from employees;

13. 鍒涘缓绠?鍗曟姤琛ㄣ??a 浣跨敤tti[tle] [printspec [text|variable]...]|[off|on] 璁剧疆鍑虹幇鍦ㄦ瘡涓?涓?〉闈㈤《绔?殑椤电湁銆?   printspec 鐨勫彲閫夊?兼湁left,centere,right,bold,format text,col n, s[kip][n]鍜宼ab n.   on 閫夐」琛ㄧず鍚?敤璁剧疆锛沷ff琛ㄧず鍙栨秷璁剧疆銆?   杩樺彲甯︽湁鐢ㄦ埛鍙橀噺(鍐呭?鏈夌郴缁熺淮鎶?锛屽?sql.pno椤靛彿锛宻ql.lno琛屽彿绛夈??b. 浣跨敤bti[tle] [printspec[text|variable]..]]|[on|off] 鎸囧畾鍑虹幇鍦ㄦ姤琛ㄤ腑姣忎竴椤甸潰搴曢儴鐨勯〉鑴氥??c. 鏈?鍚庨渶瑕佽?寰椾娇鐢ㄥ懡鍚峵ti/btti off 鍏抽棴銆?	SQL> ttitle left 'Date: ' _date center 'Query data from employees' right 'Page: ' format 999 skip 2 sql.pnd	SQL> btitle center 'Thank you for viewing this report'	SQL> set echo off	SQL> set verify off	SQL> set pagesize 50	SQL> set linesize 5000	SQL> column employee_id heading 'emp_no' format 9999	SQL> column salary heading 'sal' format L99,999.99	SQL> column job_id justify center	SQL> column department_id justify center	SQL> select * from employees;

14. 璁＄畻灏忚?break浠ュ強compute鍛戒护銆?    bre[ak] [on column_name] skip n a.column_name 琛ㄧず瀵瑰摢涓?鍒楁墽琛屾搷浣溿?? b.skip n琛ㄧず鍦ㄦ寚瀹氬垪鐨勫?煎彉鍖栦箣鍓嶆彃鍏?涓?┖琛屻??    comp[ute] function lable lable_text of column on a.琛ㄧず鎵ц?鐨勬搷浣滐紝渚嬪?sum/avg/count绛夈?? b. lable 鎸囧畾鏄剧ず缁撴灉鏄?殑鏂囨湰淇℃伅銆?break on deptno  琛ㄧず鏍规嵁deptno杩涜?鍒嗙粍銆?compute sum of salary on deptno 琛ㄧず鏍规嵁鍛樺伐鎵?鍦ㄩ儴闂ㄥ垎缁勶紝鍐嶆眰宸ヨ祫涔嬪拰銆?SQL> set pagesize 100;SQL> set linesize 5000;SQL> break on department_id;SQL> compute sum of salary on department_id;SQL> select * from employees;

  鍒版?涓烘?锛屼负鏈熶袱澶╃殑plsql*plus鐨勭浉鍏虫搷浣滀互鍙婂?瀹岋紝甯屾湜鑷?繁缁忓父浣跨敤涓嶈嚦浜庨仐蹇樸??灏忔搷浣滃皬鎶?宸ц繕鏄?渶瑕佸疄璺典腑鑾风煡銆?