¹ØÓÚÊý¾Ý¿â·½ÃæoracleÖг£Óõ½µÃº¯Êý
º¯ÊýÊÇÒ»ÖÖÓÐÁã¸ö»ò¶à¸ö²ÎÊý²¢ÇÒÓÐÒ»¸ö·µ»ØÖµµÄ³ÌÐò¡£ÔÚSQLÖÐOracleÄÚ½¨ÁËһϵÁк¯Êý£¬ÕâЩº¯Êý¶¼¿É±»³ÆΪSQL»òPL/SQLÓï¾ä£¬º¯ÊýÖ÷Òª·ÖΪÁ½´óÀࣺ
µ¥Ðк¯Êý£¬×麯Êý
SQLÖеĵ¥Ðк¯Êý
SQLºÍPL/SQLÖÐ×Ô´øºÜ¶àÀàÐ͵ĺ¯Êý£¬ÓÐ×Ö·û¡¢Êý×Ö¡¢ÈÕÆÚ¡¢×ª»»¡¢ºÍ»ìºÏÐ͵ȶàÖÖº¯ÊýÓÃÓÚ´¦Àíµ¥ÐÐÊý¾Ý£¬Òò´ËÕâЩ¶¼¿É±»Í³³ÆΪµ¥Ðк¯Êý¡£ÕâЩº¯Êý¾ù¿ÉÓÃÓÚSELECT,WHERE¡¢ORDER BYµÈ×Ó¾äÖУ¬ÀýÈçÏÂÃæµÄÀý×ÓÖоͰüº¬ÁËTO_CHAR,UPPER,SOUNDEXµÈµ¥Ðк¯Êý¡£
SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')from empWhere UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename)
µ¥Ðк¯ÊýÒ²¿ÉÒÔÔÚÆäËûÓï¾äÖÐʹÓã¬ÈçupdateµÄSET×Ӿ䣬INSERTµÄVALUES×Ӿ䣬DELETµÄWHERE×Ó¾ä,ÈÏÖ¤¿¼ÊÔÌرð×¢ÒâÔÚSELECTÓï¾äÖÐʹÓÃÕâЩº¯Êý£¬ËùÒÔÎÒÃǵÄ×¢ÒâÁ¦Ò²¼¯ÖÐÔÚSELECTÓï¾äÖС£
NULLºÍµ¥Ðк¯Êý
ÔÚÈçºÎÀí½âNULLÉÏ¿ªÊ¼ÊǺÜÀ§Äѵģ¬¾ÍËãÊÇÒ»¸öºÜÓоÑéµÄÈËÒÀÈ»¶Ô´Ë¸Ðµ½À§»ó¡£NULLÖµ±íʾһ¸öδ֪Êý¾Ý»òÕßÒ»¸ö¿ÕÖµ£¬ËãÊõ²Ù×÷·ûµÄÈκÎÒ»¸ö²Ù×÷ÊýΪNULLÖµ£¬½á¹û¾ùΪÌá¸öNULLÖµ,Õâ¸ö¹æÔòÒ²ÊʺϺܶຯÊý£¬Ö»ÓÐCONCAT,DECODE,DUMP,NVL,REPLACEÔÚµ÷ÓÃÁËNULL²ÎÊýʱÄܹ»·µ»Ø·ÇNULLÖµ¡£ÔÚÕâЩÖÐNVLº¯Êýʱ×îÖØÒªµÄ£¬ÒòΪËûÄÜÖ±½Ó´¦ÀíNULLÖµ£¬NVLÓÐÁ½¸ö²ÎÊý£ºNVL(x1,x2),x1ºÍx2¶¼Ê½±í´ïʽ£¬µ±x1Ϊnullʱ·µ»ØX2,·ñÔò·µ»Øx1¡£
ÏÂÃæÎÒÃÇ¿´¿´empÊý¾Ý±íËü°üº¬ÁËнˮ¡¢½±½ðÁ½ÏÐèÒª¼ÆËã×ܵIJ¹³¥
column name emp_id salary bonuskey type pk nulls/unique nn,u nnfk table datatype number number numberlength 11.2 11.2
²»ÊǼòµ¥µÄ½«Ð½Ë®ºÍ½±½ð¼ÓÆðÀ´¾Í¿ÉÒÔÁË£¬Èç¹ûijһÐÐÊÇnullÖµÄÇô½á¹û¾Í½«ÊÇnull£¬±ÈÈçÏÂÃæµÄÀý×Ó£º
update empset salary=(salary+bonus)*1.1
Õâ¸öÓï¾äÖУ¬¹ÍÔ±µÄ¹¤×ʺͽ±½ð¶¼½«¸üÐÂΪһ¸öеÄÖµ£¬µ«ÊÇÈç¹ûûÓн±½ð£¬¼´ salary + null,ÄÇô¾Í»áµÃ³ö´íÎóµÄ½áÂÛ£¬Õâ¸öʱºò¾ÍҪʹÓÃnvlº¯ÊýÀ´ÅųýnullÖµµÄÓ°Ïì¡£
ËùÒÔÕýÈ·µÄÓï¾äÊÇ£º
update empset salary=(salary+nvl(bonus,0)*1.1
µ¥ÐÐ×Ö·û´®º¯Êý
µ¥ÐÐ×Ö·û´®º¯ÊýÓÃÓÚ²Ù×÷×Ö·û´®Êý¾Ý£¬ËûÃÇ´ó¶àÊýÓÐÒ»¸ö»ò¶à¸ö²ÎÊý£¬ÆäÖоø´ó¶àÊý·µ»Ø×Ö·û´®
ASCII()
c1ÊÇÒ»×Ö·û´®£¬·µ»Øc1µÚÒ»¸ö×ÖĸµÄASCIIÂ룬ËûµÄÄ溯ÊýÊÇCHR()
SELECT ASCII('A') BIG_A,ASCII('z') BIG_z from empBIG_A BIG_z65 122
CHR(£¼i£¾)[NCHAR_CS]
iÊÇÒ»¸öÊý×Ö£¬º¯Êý·µ»ØÊ®½øÖƱíʾµÄ×Ö·û
select CHR(65),CHR(122),CHR(223) from empCHR65 CHR122
Ïà¹ØÎĵµ£º
extent--×îС¿Õ¼ä·ÖÅ䵥λ --tablespace management
block --×îСi/oµ¥Î» --segment management
create tablespace james
datafile '/export/home/oracle/oradata/james.dbf'
size 100M ¡¡¡¡¡¡¡¡¡¡¡¡--³õʼµÄÎļþ´óС¡¡
autoextend On¡¡¡¡¡¡¡¡ --×Ô¶¯Ôö³¤
next 10M¡ ......
ÔÎĵØÖ·£ºhttp://space.itpub.net/12330444/viewspace-249140
¹ØÓÚtkprofµÄÏêϸʹÓÃÇé¿ö¿ÉÔÚoracle10gÁª»úÎĵµPerformance Tuning GuideÖÐÕÒµ½¡£
TkprofÊÇÒ»¸ö·ÖÎöORACLE¸ú×ÙÎļþ²¢ÇÒ²úÉúÒ»¸ö¸ü¼ÓÈËÐÔ»¯ÇåÎúµÄÊä³ö½á¹ûµÄ¿ÉÖ´Ðй¤¾ß£»¿ÉÓÃÀ´¸ñʽ»¯sql trace²úÉúµÄÎļþ£¬ÈÃÄã¸üÈÝÒ׿´¶®traceµÄÄÚÈÝ¡£
Tkprof´æ·ÅλÖãºC:\ ......
Ò»¸ö³ÆÖ°µÄÊý¾Ý¿âDBA½ö½öÈ¡µÃORACLE³§¼ÒÈÏÖ¤ÊDz»¹»µÄ£¬¹Ø¼üÊÇÕæʵ»·¾³µÄÀúÁ·£¬±ÊÕß´ÓÊÂORACLE DBA¶àÄ꣬¾ÀúORACLE°æ±¾´Ó8iµ½10g(×¢£º¶ÔÓÚÒ»¸ö¹«Ë¾»òµ¥Î»µÄÕæʵ»·¾³£¬¶ÔÓÚ°æ±¾µÄ×·ÇóÊ×ÒªµÄ£¬¹Ø¼üµÄÎȶ¨ÐÔ)£¬²Ù×÷ϵͳ´Ówindows µ¥»ú¡¢Ë«»úµ½Ö÷Á÷IBM¡¢HPµÄUNIX²Ù×÷ϵͳ£¬ÒÔÏÂÊDZÊÕ߶àÄê´ÓÊÂORACLE DBAµÄһЩÐĵã¬Ï£ÍûÄܸø³õÑ ......
³£Óõļ¸¸öÊý¾Ý×ֵ䣺
user_objects : ¼Ç¼ÁËÓû§µÄËùÓжÔÏ󣬰üº¬±í¡¢Ë÷Òý¡¢¹ý³Ì¡¢ÊÓͼµÈÐÅÏ¢£¬ÒÔ¼°´´½¨Ê±¼ä£¬×´Ì¬ÊÇ·ñÓÐЧµÈÐÅÏ¢£¬ÊÇ·ÇDBAÓû§µÄ´ó±¾Óª¡£ÏëÖªµÀ×Ô¼ºÓÐÄÄЩ¶ÔÏó£¬ÍùÕâÀï²é¡£
user_source :°üº¬ÁËϵͳÖжÔÏóµÄÔÂ룬Èç´æ´¢¹ý³Ì£¬FUNCTION¡¢PROCEDURE¡¢PACKAGEµÈÐÅÏ¢
cat»òTab £º°üº¬µ±Ç°Óû§ËùÓеÄÓû§ºÍ ......
¶Ô±í½øÐзÖÎö£¬Í¨³£Çé¿öÏ¿ÉÒÔ¶Ô±í£¬Ë÷Òý£¬ÁнøÐе¥¶À·ÖÎö£¬»òÕß½øÐÐ×éºÏ·ÖÎö£¬µ«ÕâÈýÕßÄÄЩÊÇÏà¶ÔÖØÒªµÄ£¬ÄÄЩ·ÖÎöÏԵò»ÄÇôÖØÒª£¿Í¨¹ý±¾ÆªÎÄÕµÄʵÑéÏàÐÅ´ó¼ÒÒ²»á¶ÔÖ±·½Í¼ÓиüÒ»²½µÄÁ˽â.
1.Ê×ÏÈ´´½¨²âÊÔ±í,²¢²åÈë100000ÌõÊý¾Ý
SQL> create table test(id number,nick varchar2(30));
Table created.
SQL> b ......