1.ϵͳ±äÁ¿º¯Êý
£¨1£©SYSDATE
¸Ãº¯Êý·µ»Øµ±Ç°µÄÈÕÆÚºÍʱ¼ä¡£·µ»ØµÄÊÇOracle·þÎñÆ÷µÄµ±Ç°ÈÕÆÚºÍʱ¼ä¡£
select sysdate from dual;
insert into purchase values
(‘Small Widget’,’SH’,sysdate, 10);
insert into purchase values
(‘Meduem Wodget’,’SH’,sysdate-15, 15);
²é¿´×î½ü30ÌìµÄËùÓÐÏúÊۼǼ£¬Ê¹ÓÃÈçÏÂÃüÁ
select * from purchase
where purchase_date between (sysdate-30) and
sysdate;
£¨2£©USER
²é¿´Óû§Ãû¡£
select user from
dual;
£¨3£©USERENV
²é¿´Óû§»·¾³µÄ¸÷ÖÖ×ÊÁÏ¡£
select userenv(‘TERMINAL’) from
dual;
2.ÊýÖµº¯Êý
£¨1£©ROUND ËÄÉáÎåÈ뺯Êý
ROUND(ÊýÖµ£¬±£ÁôλÊý£©
select round(3.1415,3) from deul;
select product_name,round(product_price,0) price
from
product;
£¨2£©TRUNC ´ÓÊýÖнØȥСÊý²¿·Ö
TRUNC(ÊýÖµ£¬½Ø¶ÏСÊýµãnλºóµÄÊý£©
select trunc(3.145159,3) from dual;
select trunc(123456.45,-1) from dual;
select trunc(123456.45) from dual;
select product_name,trunc(product_price) price
from
product;
3.Îı¾º¯Êý
£¨1£©UPPER¡¢LOWERºÍINITCAP
ÕâÈý¸öº¯Êý¸ü¸ÄÌṩ¸øËüÃǵÄÎÄÌåµÄ´óСд¡£
select upper(product_name) from product;
select lower(product_name) from product;
select initcap(product_name) from
product;
º¯ÊýINITCAPÄܹ»ÕûÀíÔÓÂÒµÄÎı¾£¬ÈçÏ£º
select initcap(‘this TEXT hAd UNpredictABLE caSE’) from
dual;
£¨2£©LENGTH
ÇóÊý¾Ý¿âÁÐÖеÄÊý¾ÝËùÕ¼µÄ³¤¶È¡£
select product_name,length(product_name) name_length
from product
order by
product_name;
£¨3£©SUBSTR
È¡×Ó´®£¬¸ñʽΪ£º
SUBSTR£¨Ô´×Ö·û´®£¬ÆðʼλÖã¬×Ó´®³¤¶È£©£»
create table item_test(item_id char(20),item_desc char(25));
insert into item_test values(‘LA-101’,’Can, Small’);
insert into item_test values(‘LA-102’,’Bottle, Small’);
insert into item_test values
(‘LA-103’,’Bottle, Large’);
È¡±àºÅ£º
select substr(item_id,4,3) item_num,item_desc
from
item_test;
£¨4£©INSTR
È·¶¨×Ó´®ÔÚ×Ö·û´®ÖеÄλÖ㬸ñʽ
ѧϰOracle DBAÒ²°ë¸ö¶àѧÆÚÁË£¬½ñÌìÃÍÈ»²Å·¢ÏÖ£¬ÔÀ´ÎÒµÄÊ黹ÊǺÜеģ¬ÉϿβÙ×÷ʱºòÒ²Ö»ÊÇÖªµÀ´ó¸ÅÔõô×ö£¬µ«ÊÇÒªÕæµÄÈ«²¿×Ô¼º×ö£¬¶ø²»È¥·Ê黹ÊÇÓÐÒ»¶¨µÄÄѶȵģ¬ËùÒÔÄØ£¬½ñÌ쿪ʼ½«DBA´ÓÍ·¸´Ï°Ò»±é£¬Í¬Ê±ÔÙ²Ù×÷Ò»±é¡£
µÚÒ»Õ£¬Ñ§µÄÊÇOracleµÄÌåϵ½á¹¹£ ......