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
Ïà¹ØÎĵµ£º
½¨±í
create table users(
id number(4) primary key,
username varchar2(10),
password varchar2(10)
)
²éѯ±í²¢½âËø±í(¼´¿ÉÒÔµã»÷ÊäÈë¿òÏÂÃæµÄ"Ëø"ͼ±ê¹¤¾ß, ¼´"Edit data")
select * from users for update
ɾ³ý±íÖжàÓàµÄÁÐ
alter table mobilephone drop column mobiletype
תÔØ
------------------- ......
Èý¡¢Ç¶Ì×±íµÄʹÓ÷½·¨
1¡¢½«Ç¶Ì×±í¶¨ÒåΪPL/SQLµÄ³ÌÐò¹¹Ôì¿é
TYPE type_name IS TABLE OF element_type[NOT NULL];
ÈçÏÂÀýËùʾ£º
DECLARE
-- Define a nested table of variable length strings.
TYPE card_table IS TABLE OF VARCHAR2(5 CHAR);
-- Declare and initialize a n ......
ORACLE 10GµÄ¼¯ºÏAPI
¼¯ºÏAPI·½·¨×îÔçÊÇÔÚORACLE 8IÖÐÒýÈëµÄ¡£¼¯ºÏAPIÌṩÁ˼ò»¯·ÃÎʼ¯ºÏµÄ·½·¨¡£ÔÚORACLE 10GÒÔÇ°£¬ÕâЩ·½·¨È·Êµ¼ò»¯Á˶Լ¯ºÏµÄ·ÃÎÊ¡£µ«ÊÇ£¬²¢²»ÊÇ·ÇÒªÇóÕÆÎÕÕâЩ·½·¨²»¿É¡£´ÓORACLE 9IµÄINDEX-BY±íµ½ORACLE 10GµÄÁªºÏÊý×éµÄת»»£¬Ê¹ËüÃdzÉΪ±ØÐëÕÆÎյķ½·¨¡£
ÏÂÃæÊÇÎÒ´ÓÊéÉÏժ¼ÏµÄһР......
±¾ÊÓͼ³ÖÐø¸ú×ÙËùÓÐshared poolÖеĹ²Ïícursor£¬ÔÚshared poolÖеÄÿһÌõSQLÓï¾ä¶¼¶ÔÓ¦Ò»ÁС£±¾ÊÓͼÔÚ·ÖÎöSQLÓï¾ä×ÊԴʹÓ÷½Ãæ·Ç³£ÖØÒª¡£
V$SQLAREAÖеÄÐÅÏ¢ÁÐ
HASH_VALUE£ºSQLÓï¾äµÄHashÖµ¡£
ADDRESS£ºSQLÓï¾äÔÚSGAÖеĵØÖ·¡£
ÕâÁ½Áб»ÓÃÓÚ¼ø±ðSQLÓï¾ä£¬ÓÐʱ£¬Á½Ìõ²»Í¬µÄÓï¾ä¿ÉÄÜhashÖµÏàͬ¡£Õâʱºò£¬±ØÐëÁ¬Í¬ADDRESSÒ ......
°´ÕÕOracleDocumentÖеÄÃèÊö£¬v$sysstat´æ´¢×ÔÊý¾Ý¿âʵÀýÔËÐÐÄÇ¿ÌÆð¾Í¿ªÊ¼ÀÛ¼ÆȫʵÀý(instance-wide)µÄ×ÊԴʹÓÃÇé¿ö¡£
ÀàËÆÓÚv$sesstat£¬¸ÃÊÓͼ´æ´¢ÏÂÁеÄͳ¼ÆÐÅÏ¢£º
1>.ʼþ·¢Éú´ÎÊýµÄͳ¼Æ(È磺user commits)
2>.Êý¾Ý²úÉú£¬´æÈ¡»òÕß²Ù×÷µÄtotalÁÐ(È磺redo size)
3>.Èç¹ûTIMED_STATISTICSֵΪtrue,Ôòͳ¼Æ»¨·Ñ ......