Oracle ÈÕÆÚ²éѯÓï¾äС½á
²éѯÐÇÆÚ¼¸:
select to_char(sysdate,'day') from dual;
²éѯ¼¸ºÅ:
select to_char(sysdate,'dd') from dual;
²éѯСʱÊý:
select to_char(sysdate,'hh24') from dual;
²éѯʱ¼ä:
select to_char(sysdate,'hh24:mi:ss') from dual;
²éѯÈÕÆÚʱ¼ä:
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
²éѯÐÇÆÚ¼¸:
select to_char(to_date('2006-11-21','YYYY-MM-DD'),'DAY') from dual;
select to_char(to_date('2006-11-21','YYYY-MM-DD'),'DAY','NLS_DATE_LANGUAGE = American') from dual;
alter session set nls_date_language = 'simplified chinese';
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
²éѯÁ½¸öÈÕÆÚ¼äµÄÌìÊý:
FLOOR:·µ»ØµÈÓÚ»òСÓÚxµÄ×î´óÕûÊý:
select floor(to_date('20061121','yyyymmdd') - to_date('20061120','yyyymmdd')) from dual;
//»ñȡСʱ
SELECT EXTRACT(HOUR from TIMESTAMP '2005-10-10 10:10:10') from DUAL;
//»ñÈ¡Ô·Ý
SELECT EXTRACT(MONTH from SYSDATE) from DUAL;
//¼ÆËãÒ»Äê¶àÉÙÌì
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual;
//×¢Ò⣺trunc(sysdate,'year')½ØÈ¡Éú³É==>'2006-01-01';
select trunc(sysdate,'year') from dual;
//²éѯһÄêÖеĵڼ¸Ìì
select TO_CHAR(SYSDATE,'DDD'),sysdate from dual;
next_day(sysdate,6)ÊÇ´Óµ±Ç°¿ªÊ¼ÏÂÒ»¸öÐÇÆÚÎå¡£ºóÃæµÄÊý×ÖÊÇ´ÓÐÇÆÚÈÕ¿ªÊ¼ËãÆð¡£
1 2 3 4 5 6 7
ÈÕ Ò» ¶þ Èý ËÄ Îå Áù
select next_day(to_date('2006-11-21','YYYY-MM-DD'),6);
//·µ»ØÒ»¸öÔ·ݵÄ×îºóÒ»Ìì
Select last_day(date'2006-02-01') D from dual£»
Select last_day(to_date('2006-02-01','YYYY-MM-DD')) from dual;
//·µ»ØÒ»¸öÔ·ݵÄ×îºóÒ»ÌìÊÇÐÇÆÚ¼¸
select to_char(last_day(to_date('2006-11-21','YYYY-MM-DD')),'DAY') from dual;
//»ñÈ¡ÉϸöÔÂÄ©Ì죺
select to_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd') LastDay from dual;
==>×¢Òâ:last_day(sysdate)±íʾsysdateÕâ¸öÔµÄ×îºóÒ»Ìì
add_months(last_day(sysdate),-1)±íʾÉϸöÔµÄ×îºóÒ»Ìì
//ÉÏÔ½ñÌì
select to_char(add_months(sysdate,-1),'yyyy-MM-dd') PreToday from dual;
//ÉÏÔÂÊ×Ìì
select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual
Ïà¹ØÎĵµ£º
1¡¢ÔÚ±¾»ú69ÉÏ´´½¨Êý¾Ý¿âorcl £¬global_name=orcl£¬Ê¹ÓÃÓï¾ä
alter database rename global_name to orcl.us.oracle.com ÐÞ¸ÄÊý¾Ý¿âµÄÈ«¾ÖÊý¾Ý¿âÃûΪorcl.us.oracle.com
2¡¢ÔÚÐé»ú188ÉÏ´´½¨Êý¾Ý¿âviotest£¬global_name=viotest£¬Ê¹ÓÃÓï¾ä
alter database rename global_name to viotest.us.oracle.com ÐÞ¸ÄÊý¾Ý¿âµÄÈ«¾ÖÊ ......
Óα꣨CURSOR£©Ò²½Ð¹â±ê£¬ÔÚ¹ØϵÊý¾Ý¿âÖо³£Ê¹Óã¬ÔÚPL/SQL³ÌÐòÖпÉÒÔÓÃCURSORÓëSELECTÒ»Æð¶Ô±í»òÕßÊÓͼ
ÖеÄÊý¾Ý½øÐвéѯ²¢ÖðÐжÁÈ¡¡£
OracleÓαê·ÖΪÏÔʾÓαêºÍÒþʽÓαꡣ
ÏÔʾÓα꣨Explicit Cursor£©:ÔÚPL/SQL ......
Êýѧº¯Êý£º
1.¾ø¶ÔÖµ
S:SELECT abs(-1) value
O:SELECT abs(-1) value from dual
2.È¡Õû(´ó)
S:SELECT ceiling(-1.001) value
O:SELECT ceil(-1.001) value from dual
3.È¡Õû£¨Ð¡£©
S:SELECT floor(-1.001) value
O:SELECT floor(-1.001) value from dual
4.È¡Õû£¨½ØÈ¡£©
S:SELECT cast(-1.002 as int) value
O ......
´æ´¢¹ý³Ì
==========================================================================
==========================================================================
¡¾ÑµÁ·15.1¡¿ ´´½¨Ò»¸öÏÔʾ¹ÍÔ±×ÜÈËÊýµÄ´æ´¢¹ý³Ì¡£
²½Öè1£ºµÇ¼SCOTTÕË»§(»òѧÉú¸öÈËÕË»§)¡£
²½Öè2£º ......
oracle grant
ÊÚȨÓï¾ä--select * from dba_users; ²éѯÊý¾Ý¿âÖеÄËùÓÐÓû§
--alter user TEST_SELECT account lock; ËøסÓû§
--alter user TEST_SELECT account unlock; ¸øÓû§½âËø
--create user xujin identified by xujin; ½¨Á¢Óû§
--grant create tablespace to xujin; ÊÚȨ
--grant select ......