oracleµ¥Ðк¯Êý
µ¥Ðк¯Êý:
º¯ÊýÀà±ð:
µ¥ÐÐ:·µ»Øµ¥¸ö½á¹û:substr,length
¶àÐÐ:·µ»Ø¶à¸ö½á¹û,any,all
µ¥ÐеķÖÀà:
×Ö·ûÀ࣬ÈÕÆÚÀ࣬Êý×ÖÀ࣬ת»»À࣬ͨÓÃÀà
1.×Ö·ûÀà
ת»»´óСд:
lower:ת»»ÎªÐ¡Ð´
Select ENAME,LOWER(ENAME) from EMP
upper:ת»»Îª´óд
Select upper('abcd') from DUAL
initcap :Ê××Öĸ´óд
Select INITCAP(ENAME) from EMP
2.×Ö·û´¦ÀíÀà
concat:Á¬½ÓÁ½¸ö²»Í¬µÄÁÐ,CONCAtÖ»ÄܽÓÊÜÁ½¸ö²ÎÊý
Select CONCAT(EMPNO,ENAME) from EMP
Select CONCAT('A','B') from dual
substr:½ØÈ¡×Ó´®
ϱê´Ó1¿ªÊ¼
Select SUBSTR(ENAME,2) from EMP--´ÓµÚ2¸öλÖýص½Ä©Î²
Select SUBSTR(ENAME,2,3) from EMP--´ÓµÚ2¸öλÖýØÈ¡3¸ö
length:ÌáÈ¡³¤¶È
Select ENAME,length(ename) from emp
INSTR:Ï൱ÓÚStringÀàÖеÄindexOf,ÇóË÷Òý
Select ENAME,instr(ename,'A') from emp
LPADºÍRPADµÄÓ÷¨
±íʾ²¹³äµÄº¬Òå
Select LPAD(SAL,10,'*') from EMP
Select RPAD(SAL,10,'*') from EMP
½«salÏÔʾΪ10λ,²»×ãµÄλÖò¹ÉÏ×Ö·û*
trimÁ½±ßÂË¿Õ
Select Trim(' A B C ') from DUal
ʹÓÃLTrimeºÍRTrim¹ýÂËÒ»±ßµÄ¿Õ¸ñ
Select LTrim(' A B C ') from DUAL
REPLACE:Ìæ»»
Select ename ,Replace(ename,'A','B') from EMP
3.Êý×Öת»»Àà:
ROUND:ËÄÉáÎåÈë
TRUNC:½ØÈ¡
MOD:È¡Óà
Select ROUND(16.336666663,3) from DUAL
Select TRUNC(16.33666666,3) from DUAL
Select Mod(8,3) from DUAL
4.ÈÕÆÚÀຯÊý
sysdate:ÄêÔÂÈÕʱ·ÖÃë
ÈÕÆÚ+-1,¶¼´ú±íÒ»ÌìµÄʱ¼ä,±ÈÈç:
Select TRUNC(Sysdate-365) from DUAL
Select Sysdate-1/24/60 from DUAL
5.ÈÕÆÚÔËË㺯Êý
MONTHS_BETWEEN:±íʾÁ½¸öÈÕÆÚµÄÔ·ÝÖ®²î
Select EMPNO,HIREDATE,MONTHS_BETWEEN(Sysdate,HIREDATE)/12 from EMP
ADD_MONTHS:±íʾ¸øÖ¸¶¨µÄÈÕÆÚ¼ÓÒ»¸öÔÂÊý
Select HIREDATE,ADD_MONTHS(HIREDATE,3) from EMP
NEXT_DAY:±íʾÒÔµ±Ç°Ê±¼äΪ»ù×¼,ÏÂÒ»¸ö"Ä¿±êÈÕ"µÄÈÕÆÚ
Select NEXT_DAY(Sysdate,'ÐÇÆÚ¶þ') from DUAL
LAST_DAY:¼ÆË㵱ǰÈÕÆÚµÄ×îºóÒ»Ìì
Select HIREDATE,LAST_DAY(HIREDATE)-HIREDATE from EMP
ROUND:¶ÔÈÕÆÚ½øÐÐËÄÉáÎåÈë
Select ROUND(Sysdate,'YEAR') from DUAL
TRUNC:±íʾ¶ÔÈÕÆÚ½øÐнØÈ¡
Select TRUNC(Sysdate) from DUAL
Ïà¹ØÎĵµ£º
ËäȻѧϰJavaºÜ¾ÃÁË£¬×Ô¼ºÒ²Á¬½Ó¹ýһЩÊý¾Ý¿â£¬±ÈÈçmysqlÖ®ÀàµÄ£¬Èç½ñÄØ£¬Ò²Ñ§Ï°ÁËÒ»¶Îʱ¼äµÄOracle£¬È»¶øÄØ£¬½ñÌìÊÇÎÒµÚÒ»´ÎÁ¬½ÓOracle£¬ºÙºÙ£¬Ó¦¸Ã»¹²»ËãÌ«³Ù°É¡£
½ñÌìÄØ£¬Óе㱿׾£¬´ó¼ÒĪЦ£¡
ÎÒÕâÊÇÒ»¸ö²éѯÀý×Ó
Ê×ÏÈ£¬Ô ......
Õâ¸öOracleµÄ·ÖÒ³ÏÔʾ¾Ý˵ÊÇ×îÂé·³µÄ£¨MySQL×î¼òµ¥£©£¬µ«ÊÇÕâ¸ö·ÖÒ³ÏÔʾºÜÓÐÓá£ÏÖÔںܶàÍøÕ¾ÉϵÄÌõÄ¿¶¼·Ç³£¶à£¬ÀýÈçÐÂÎÅ£¬¿ÉÄܾ͸ոüеľÍÓÐÉϰÙÌõ£¬ÔÙ¼ÓÉÏÒÔǰµÄ£¬Ò»ÕÅÒ³Ãæ¿Ï¶¨¾ÍÊǷŲ»ÏÂÁË£¬ËùÒÔ·ÖÒ³µÄºÃ´¦¾ÍÊÇ¿ÉÒÔ½â¾öÕâ¸öÎÊÌâ¡£
ÏÂÃæÒ»ÅÅÅŵĽÐÄãÑ¡ÔñµÚ¼¸Ò³£¬Ò»Ñ¡Ôñ£¬Ö±½Ó¾ÍÌøµ½ºóÃæµÄ¼¸Ò³È¥ÁË£¬²»ÓÃÕâÑù´ÓÉÏÒ ......
°²×°Oracleºó£¬¸ü¸ÄWindows¼ÆËã»úÃû£¬µ¼Ö²¿·ÖOracleµÚÈý·½¹¤¾ß£¬Èç:PLSQL
DeveloperÎÞ·¨µÇ½£¬²¢ÌáʾORA-12541:TNS:no listener»òORA-12547:TNS:¶ªÊ§ÁªÏµµÈ´íÎóÐÅÏ¢µÄ½â¾ö·½·¨¡£
´ð£º´ÓOracleµÄ´íÎóÌáʾÀ´¿´,ÊÇ˵TNS:no listener
£¬¹À¼ÆÊÇijÖÖ·þÎñûÓÐÆô¶¯£¬´ò¿ªwindows¹ÜÀí¹¤¾ß->·þÎñ£¬Ò»¿´£¬ÓÐÒ»¸öOracle ......
Ò»¡¢¼àÌýÆ÷(LISTENER)
¡¡¡¡¼àÌýÆ÷ÊÇOracle»ùÓÚ·þÎñÆ÷¶ËµÄÒ»ÖÖÍøÂç·þÎñ£¬Ö÷ÒªÓÃÓÚ¼àÌý¿Í»§¶ËÏòÊý¾Ý¿â·þÎñÆ÷¶ËÌá³öµÄÁ¬½ÓÇëÇó¡£¼ÈÈ»ÊÇ»ùÓÚ·þÎñÆ÷¶ËµÄ·þÎñ£¬ÄÇôËüÒ²Ö»´æÔÚÓÚÊý¾Ý¿â·þÎñÆ÷¶Ë£¬½øÐмàÌýÆ÷µÄÉèÖÃÒ²ÊÇÔÚÊý¾Ý¿â·þÎñÆ÷¶ËÍê³ÉµÄ¡£
¶þ¡¢±¾µØ·þÎñÃû(Tnsname)
¡¡¡¡Oracle¿Í»§¶ËÓë·þÎñÆ÷¶ËµÄÁ¬½ÓÊÇͨ¹ý¿Í»§¶ ......
½ü¶Îʱ¼äºÜ¶àÍøÓÑÌá³ö¼àÌýÅäÖÃÏà¹ØÎÊÌ⣬¿Í»§ÖÕ¶Ë(Client)ÎÞ·¨Á¬½Ó·þÎñÆ÷¶Ë(Server)¡£±¾ÎÄÏÖ¶Ô¼àÌýÅäÖÃ×÷Ò»¼òµ¥½éÉÜ£¬²¢Ìá³öһЩ¿Í»§ÖÕ¶ËÎÞ·¨Á¬½Ó·þÎñÆ÷¶ËµÄ½â¾ö˼·£¬Ô¸¶Ô¹ã´óÍøÓÑÓë¶ÁÕßÓÐһЩ°ïÖú¡£
Ò»¡¢¼àÌýÆ÷(LISTENER)
¡¡¡¡¼àÌýÆ÷ÊÇOracle»ùÓÚ·þÎñÆ÷¶ËµÄÒ»ÖÖÍøÂç·þÎñ£¬Ö÷ÒªÓÃÓÚ¼àÌý¿Í»§¶ËÏòÊý¾Ý¿â·þÎñÆ÷¶ËÌá ......