Oracle PL\SQL ²Ù×÷£¨Èý£©Oracleº¯Êý
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µÄÌåϵ½á¹¹£ ......
Ê×ÏÈÎÒÏëлл԰×ÓµÄÅóÓÑÃÇ£¬ÊÇÄãÃÇÌáÐÑÎÒдÄÚÈÝÓдíÎ󣬼ǵÃÔø¾µç×ÓÉÌÎñÖ®Êý¾Ý´æ´¢Á÷³Ì£¨Î壩ÀïÃæËµµ½“Ñ¡´æ´¢¹ý³Ì+´«µÝ²ÎÊýÓÃSqlParameterÊÇÒòΪ£¬³ý·ÇÊÇADO.NETÓЩ¶´£¬ÄÇô¾Í¾ø¶Ô²»»á·¢ÉúSQL×¢È딡£Keep Walking´ó¸çÒ²ÔÚ¹ØÓÚ·ÀÖ¹sql×¢ÈëµÄ¼¸ÖÖÊֶΣ¨¶þ£©ÖоٳöÀ´Ò»¸öÀý×Ó˵Ã÷ÎÒ˵µÄÉϾ仰ÊÇ´íÎóµÄ¡£ËµÊµ»°µ± ......
declare @tb3 table (ÉÌÆ·±àºÅ nvarchar(10),Åú´ÎºÅ nvarchar(10),¿â´æÊýÁ¿ int,³ö¿âÊýÁ¿ int)
declare @tb1 table (ÉÌÆ·±àºÅ nvarchar(10),Åú´ÎºÅ nvarchar(10),¿â´æÊýÁ¿ int)
insert into @tb1 select '0001','090801',200
union all select '0001','090501',50
&n ......
01¡¢SQLÓëORACLEµÄÄÚ´æ·ÖÅä
ORACLEµÄÄÚ´æ·ÖÅä´ó²¿·ÖÊÇÓÉINIT.ORAÀ´¾ö¶¨µÄ£¬Ò»¸öÊý¾Ý¿âʵÀý¿ÉÒÔÓÐNÖÖ·ÖÅä·½°¸£¬²»Í¬µÄÓ¦Óã¨OLTP¡¢OLAP£©ËüµÄÅäÖÃÊÇÓвàÖØµÄ¡£ SQL¸ÅÀ¨ÆðÀ´Ëµ£¬Ö»ÓÐÁ½ÖÖÄÚ´æ·ÖÅ䷽ʽ£º¶¯Ì¬ÄÚ´æ·ÖÅäÓ뾲̬ÄÚ´æ·ÖÅ䣬¶¯Ì¬ÄÚ´æ·ÖÅä³äÐíSQL×Ô¼ºµ÷ÕûÐèÒªµÄÄڴ棬¾²Ì¬ÄÚ´æ·ÖÅäÏÞÖÆÁËSQL¶ÔÄÚ´æµÄʹ Óá£
002¡¢SQ ......
¡¡¡¡20ÊÀ¼Í£¸£°Äê´ú³õ£¬ANSI£¨American¡¡National¡¡Standard¡¡Institute£©¡¡Êý¾Ý¿â±ê׼ίԱ»á¿ªÊ¼Öƶ©Ïà¹Ø¹ØÏµÓïÑԵıê×¼£¬µ«Ö±µ½£±£¹£¸£¶Ä꣬Êý¾Ý¿â±ê׼ίԱ»á²ÅÍÆ³öµÚÒ»¸öSQLÓïÑÔ±ê×¼SQL-86¡£Ëæ×ÅÊý¾Ý¿â¼¼ÊõµÄ·¢Õ¹£¬SQL±ê×¼Ò²ÔÚ²»¶Ï½øÐÐÀ©Õ¹ºÍÐÞÕý£¬²¢ÇÒÊý¾Ý¿â±ê׼ίԱ»áÏȺóÓÖÍÆ³öSQL-89£¬SQL-92ÒÔ¼°SQL-99±ê×¼¡££±£¹£·£ ......