Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

ORACLE TRUNC()º¯Êý

 ÒÔÉÏΪתÔØ,¶àÊý²¢Î´ÑéÖ¤.
ÆäÖÐ,select trunc(sysdate,'dd') from dual,µÃµ½µÄÊÇ: 2009-3-23 ÉÏÎç12:00:00 ,ÓëÏÂÎIJ¢²»Ò»Ñù!
oracle trunc()º¯ÊýµÄÓ÷¨
1.TRUNC(for dates)
TRUNCº¯ÊýΪָ¶¨ÔªËضø½ØÈ¥µÄÈÕÆÚÖµ¡£
Æä¾ßÌåµÄÓï·¨¸ñʽÈçÏ£º
TRUNC£¨date[,fmt]£©
ÆäÖУº
date Ò»¸öÈÕÆÚÖµ
fmt ÈÕÆÚ¸ñʽ£¬¸ÃÈÕÆÚ½«ÓÉÖ¸¶¨µÄÔªËظñʽËù½ØÈ¥¡£ºöÂÔËüÔòÓÉ×î½üµÄÈÕÆÚ½ØÈ¥
ÏÂÃæÊǸú¯ÊýµÄʹÓÃÇé¿ö£º
TRUNC£¨TO_DATE(’24-Nov-1999 08:00 pm’,’dd-mon-yyyy hh:mi am’)£©
=’24-Nov-1999 12:00:00 am’
TRUNC£¨TO_DATE(’24-Nov-1999 08:37 pm’,’dd-mon-yyyy hh:mi am’,’hh’)£© =’24-Nov-1999 08:00:00 am’
round (date,''format'')δָ¶¨formatʱ£¬Èç¹ûÈÕÆÚÖеÄʱ¼äÔÚÖÐÎç֮ǰ£¬Ôò½«ÈÕÆÚÖеÄʱ¼ä½Ø¶ÏΪ12 A.M.(ÎçÒ¹,Ò»ÌìµÄ¿ªÊ¼),·ñÔò½øµ½µÚ¶þÌì¡£
TRUNC(date,''format'')δָ¶¨formatʱ£¬½«ÈÕÆÚ½ØΪ12 A.M.£¬²»¿¼ÂÇÊÇ·ñÔÚÖÐÎç֮ǰµÄÌõ¼þ¡£
2.TRUNC(for number)
TRUNCº¯Êý·µ»Ø´¦ÀíºóµÄÊýÖµ£¬Æ乤×÷»úÖÆÓëROUNDº¯Êý¼«ÎªÀàËÆ£¬Ö»ÊǸú¯Êý²»¶ÔÖ¸¶¨Ð¡ÊýÇ°»òºóµÄ²¿·Ö×öÏàÓ¦ÉáÈëÑ¡Ôñ´¦Àí£¬¶øͳͳ½ØÈ¥¡£
Æä¾ßÌåµÄÓï·¨¸ñʽÈçÏÂ
TRUNC£¨number[,decimals]£©
ÆäÖУº
number ´ý×ö½ØÈ¡´¦ÀíµÄÊýÖµ
decimals Ö¸Ã÷Ðè±£ÁôСÊýµãºóÃæµÄλÊý¡£¿ÉÑ¡ÏºöÂÔËüÔò½ØÈ¥ËùÓеÄСÊý²¿·Ö
ÏÂÃæÊǸú¯ÊýµÄʹÓÃÇé¿ö£º
TRUNC£¨89.985£¬2£©=89.98
TRUNC£¨89.985£©=89
TRUNC£¨89.985£¬-1£©=80
×¢Ò⣺µÚ¶þ¸ö²ÎÊý¿ÉÒÔΪ¸ºÊý£¬±íʾΪСÊýµã×ó±ßÖ¸¶¨Î»ÊýºóÃæµÄ²¿·Ö½ØÈ¥£¬¼´¾ùÒÔ0¼Ç¡£
{
formatΪdayʱ,Ö»¾«È·µ½Ìì,¶ø²»¹Ü¼¸Ä꼸ÔÂÖ»ÒªÊÇ·ûºÏµÄday¾Í¿ÉÒÔÁË,ÒªÏëÈ·¶¨Ò»ÄêÖеÄijÔµÄijһÌì¾ÍÒªÓÃtrunc(date,''dd'').
ͨË×µÄ˵°É,formatΪÄêʱ,¾«È·µ½-----Äê
ΪÔÂʱ,¾«È·µ½------Äê,ÔÂ(²»¹ÜÄÄÄê,Ö»ÒªÊÇÏàͬµÄÔºÍÄÄÌì)
ΪÈÕʱ,¾«È·µ½------Äê,ÔÂ,ÈÕ(²»¹ÜÄÄÄêµÄÄÄÔÂ,Ö»¹ØÐÄÊÇÄÄÌì)
}
 ///////////////////////////////////////////////////////////////////////////////
ʵÀý:
  ¶ÔÊý×Ö£¬ÈÕÆÚ½øÐеĠ   
  SQL>   select   trunc(sysdate)   from   dual;  
   
  TRUNC(SYSD  
  ----------  
  07-1Ô   -0


Ïà¹ØÎĵµ£º

oracle ±í¿Õ¼ä²Ù×÷

oracle±í¿Õ¼ä²Ù×÷Ïê½â
  1
  2
  3×÷Õߣº   À´Ô´£º    ¸üÐÂÈÕÆÚ£º2006-01-04 
  5
  6 
  7½¨Á¢±í¿Õ¼ä
  8
  9CREATE TABLESPACE data01
 10DATAFILE '/ora ......

ORACLE PL/SQL¿ª·¢

¸Õ¸ÕÔÚinthirtiesÀÏ´óµÄ²©¿ÍÀï¿´µ½ÕâƪÎÄÕ£¬Ð´µÄ²»´í£¬ÕýºÃ×Ô¼º×î½üÔÚѧϰPL/SQL£¬×ª¹ýÀ´Ñ§Ï°Ñ§Ï°¡£
==================================================================================
bulk collectÊÇ¿ÉÒÔ¿´×öÊÇÒ»ÖÖÅú»ñÈ¡µÄ·½Ê½£¬ÔÚÎÒÃǵÄplsqlµÄ´úÂë¶ÎÀï¾­³£×÷ΪintoµÄÀ©Õ¹À´Ê¹Ó᣶ÔÓÚselect id into v from ... ......

Oracle¶¯Ì¬ÐÔÄܱí(1)

±¾ÊÓͼ³ÖÐø¸ú×ÙËùÓÐshared poolÖеĹ²Ïícursor£¬ÔÚshared poolÖеÄÿһÌõSQLÓï¾ä¶¼¶ÔÓ¦Ò»ÁС£±¾ÊÓͼÔÚ·ÖÎöSQLÓï¾ä×ÊԴʹÓ÷½Ãæ·Ç³£ÖØÒª¡£
V$SQLAREAÖеÄÐÅÏ¢ÁÐ
HASH_VALUE£ºSQLÓï¾äµÄHashÖµ¡£
ADDRESS£ºSQLÓï¾äÔÚSGAÖеĵØÖ·¡£
ÕâÁ½Áб»ÓÃÓÚ¼ø±ðSQLÓï¾ä£¬ÓÐʱ£¬Á½Ìõ²»Í¬µÄÓï¾ä¿ÉÄÜhashÖµÏàͬ¡£Õâʱºò£¬±ØÐëÁ¬Í¬ADDRESSÒ ......

Oracle¶¯Ì¬ÐÔÄܱí

±¾ÊÓͼ°üÀ¨Shared poolÖÐSQLÓï¾äµÄÍêÕûÎı¾£¬Ò»ÌõSQLÓï¾ä¿ÉÄֳܷɶà¸ö¿é±»±£´æÓÚ¶à¸ö¼Ç¼ÄÚ¡£
V$SQLTEXTÖеij£ÓÃÁÐ
HASH_VALUE£ºSQLÓï¾äµÄHashÖµ
ADDRESS£ºsqlÓï¾äÔÚSGAÖеĵØÖ·
SQL_TEXT£ºSQLÎı¾¡£
PIECE£ºSQLÓï¾ä¿éµÄÐòºÅ
V$SQLTEXTÖеÄÁ¬½ÓÁÐ
Column      View     ......

oracle ±Ê¼Ç II Ö®DML:Êý¾Ý²Ù×÷ÓïÑÔ

 DML:Data Manipulation Language Êý¾Ý²Ù×÷ÓïÑÔ
°üÀ¨£ºCRUD
1. insertÓï¾ä
(1) ´ÓÆäËü±íÖи´ÖÆÊý¾Ý,ʵÏÖ·½·¨:ÔÚinsert Óï¾äÖмÓÈë²éѯÓï¾ä
insert into sales_reps(id,name,salary,commission_pct)  select employee_id,last_name,salary,commission_pct
from employees where job_id like '%rep';
(2) up ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ