OracleÖÐNVL2 ºÍNULLIFµÄÓ÷¨
NULLÖ¸µÄÊÇ¿ÕÖµ£¬»òÕß·Ç·¨Öµ¡£
NVL (expr1, expr2)->expr1ΪNULL£¬·µ»Øexpr2£»²»ÎªNULL£¬·µ»Øexpr1¡£×¢ÒâÁ½ÕßµÄÀàÐÍÒªÒ»ÖÂ
NVL2 (expr1, expr2, expr3) ->expr1²»ÎªNULL£¬·µ»Øexpr2£»ÎªNULL£¬·µ»Øexpr3¡£expr2ºÍexpr3ÀàÐͲ»Í¬µÄ»°£¬expr3»áת»»Îªexpr2µÄÀàÐÍ
NULLIF (expr1, expr2) ->ÏàµÈ·µ»ØNULL£¬²»µÈ·µ»Øexpr1
ÏêϸµÄ::
oracleµÄnvlº¯ÊýµÄÓ÷¨
ͨ¹ý²éѯ»ñµÃij¸ö×ֶεĺϼÆÖµ£¬Èç¹ûÕâ¸öֵλnull½«¸ø³öÒ»¸öÔ¤ÉèµÄĬÈÏÖµ
select nvl(sum(t.dwxhl),1) from tb_jhde t where
zydm=-1ÕâÀï¹ØÐĵÄnvlµÄÓ÷¨£¬nvl£¨arg,value£©´ú±íÈç¹ûÇ°ÃæµÄargµÄֵΪnullÄÇô·µ»ØµÄֵΪºóÃæµÄvalue
Èç: NVL(a,b)¾ÍÊÇÅжÏaÊÇ·ñÊÇNULL,Èç¹û²»ÊÇ·µ»ØaµÄÖµ£¬Èç¹ûÊÇ·µ»ØbµÄÖµ
ͨ¹ý²éѯ»ñµÃij¸ö×ֶεĺϼÆÖµ£¬Èç¹ûÕâ¸öֵλnull½«¸ø³öÒ»¸öÔ¤ÉèµÄĬÈÏÖµ
select nvl(sum(t.dwxhl),1) from tb_jhde t where
zydm=-1ÕâÀï¹ØÐĵÄnvlµÄÓ÷¨£¬nvl£¨arg,value£©´ú±íÈç¹ûÇ°ÃæµÄargµÄֵΪnullÄÇô·µ»ØµÄֵΪºóÃæµÄvalue
ÖйúÍø¹ÜÁªÃË
ÁíÒ»¸öÓйصÄÓÐÓ÷½·¨
declare
i integer
select nvl(sum(t.dwxhl),1) into i from tb_jhde t where
zydm=-1ÕâÑù¾Í¿ÉÒÔ°Ñ»ñµÃµÄºÏ¼ÆÖµ´æ´¢µ½±äÁ¿iÖУ¬Èç¹û²éѯµÄֵΪnull¾Í°ÑËüµÄÖµÉèÖÃΪĬÈϵÄ1
OracleµÃNvlº¯Êý
nvl( ) º¯Êý
´ÓÁ½¸ö±í´ïʽ·µ»ØÒ»¸ö·Ç null Öµ¡£
Óï·¨
NVL(eExpression1, eExpression2)
²ÎÊý
eExpression1, eExpression2
Èç ¹û eExpression1 µÄ¼ÆËã½á¹ûΪ null Öµ£¬Ôò NVL( ) ·µ»Ø eExpression2¡£Èç¹û
eExpression1 µÄ¼ÆËã½á¹û²»ÊÇ null Öµ£¬Ôò·µ»Ø eExpression1¡£eExpression1 ºÍ
eExpression2 ¿ÉÒÔÊÇÈÎÒâÒ»ÖÖÊý¾ÝÀàÐÍ¡£Èç¹û eExpression1 Óë eExpression2 µÄ½á¹û½ÔΪ null
Öµ£¬Ôò NVL( ) ·µ»Ø .NULL.¡£
·µ»ØÖµÀàÐÍ
×Ö·ûÐÍ¡¢ÈÕÆÚÐÍ¡¢ÈÕÆÚʱ¼äÐÍ¡¢ÊýÖµÐÍ¡¢»õ±ÒÐÍ¡¢Âß¼ÐÍ»ò null Öµ
˵Ã÷
ÔÚ²»Ö§³Ö null Öµ»ò null ÖµÎ޹ؽôÒªµÄÇé¿öÏ£¬¿ÉÒÔʹÓà NVL( ) À´ÒÆÈ¥¼ÆËã»ò²Ù×÷ÖÐµÄ null Öµ¡£
select nvl(a.name,'¿ÕµÃ') as name
from student a join school b on a.ID=b.ID
×¢Ò⣺Á½¸ö²ÎÊýµÃÀàÐÍҪƥÅä
ÎÊ£ºÊ²Ã´ÊÇNULL£¿
´ð£ºÔÚÎÒÃDz»ÖªµÀ¾ßÌåÓÐʲôÊý¾ÝµÄʱºò£¬Ò²¼´Î´Öª£¬¿ÉÒÔÓÃNULL£¬
ÎÒÃdzÆËüΪ¿Õ£¬ORACLEÖУ¬º¬ÓпÕÖµµÄ±íÁ㤶ÈΪÁã¡£
ORACLEÔÊÐíÈκÎÒ»ÖÖÊý¾ÝÀàÐ͵Ä×Ö¶ÎΪ¿Õ£¬³ýÁËÒÔÏÂÁ½ÖÖÇé¿ö£º
1¡¢Ö÷¼ü×ֶΣ¨primary key£©£¬
2¡¢¶¨ÒåʱÒѾ¼ÓÁËNOT NULLÏÞÖÆÌõ¼þµÄ×Ö¶Î
Ïà¹ØÎĵµ£º
ÉùÃ÷£º
ÒÔϵÄÀý×Ó²»Ò»¶¨ÕýÈ·£¬Ö»ÊÇΪÁËÑÝʾ´ó¸ÅµÄÁ÷³Ì¡£
Ò»£ºÎÞ·µ»ØÖµµÄ´æ´¢¹ý³Ì
´æ´¢¹ý³ÌΪ£º
CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS
BEGIN
INSERT INTO HYQ.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2);
END TESTA;
È»ºóÄØ£¬ÔÚjavaÀïµ÷ÓÃʱ¾ÍÓÃÏÂÃæµÄ´ ......
¡¾51CTO.com¶À¼ÒÌØ¸å¡¿Ãæ¶Ô¾ª»ÅµÄ¿Í»§ºÍ¹ã·ºÖÊÒɵÄýÌ壬Oracle¹Ù·½×î½üÖÕÓÚ³ö°ñ°²Ãñ£¬ÄDZãÊÇÒ»¸öÒÔ“SUN CUSTOMERS ,Oracle Plans To£º”Ϊ´ó±êÌâµÄ¹ã¸æ¡£Ö±Òë¹ýÀ´±ã¹ýÀ´¿ÉÖªµÀ£¬Oracle½«Òª¼Ó´óSPARCºÍSolarisµÄͶÈëÁË£¬¶ø´óÀϰ尣ÀïÉÒ²ÏòIBMÓ²¼þÏÂÁËÕ½Ê飬Éù³ÆOracle½«×îÖÕÓ®µÃÕⳡ¾ºÕù……
&n ......
Ö»ÊÇsqlserver ÌṩµÄÔ¶³ÌÊý¾Ý·ÃÎʺ¯Êý; ÔÚ±¾µØsqlserver ÖÐÈ¡ÍⲿÊý¾ÝÔ´Êý¾Ýʱºò¿ÉÓÃ;
¶ÔÁ¬½Ó±¾µØ oracle ²Ù×÷Ô¶³Ì oracle ²»ÄÜʹÓÃ; ²âÊÔ: pl/sql ÖÐʹÓÃ:
select * from openrowset(................); ÎÞЧ!!!!!!!!!!!!!!
ÔÚoracle ÖÐÐèÒª·ÃÎÊÔ¶³ÌÊý¾Ý,ÐèÒª½¨Á¢Ò»Á¬½ÓÔ¶³Ìoracle µÄ dblink ;
ÔÙÓÃÈçÏ·½ ......
Oracle 10g statisticÊý¾Ýͳ¼Æ,Oracle»á¸ù¾ÝÕâЩͳ¼ÆÐÅÏ¢À´¾ö¶¨ÊÇ×ßRBO(Rule-BasedOptimization)£¬»¹ÊÇ×ßCBO(Cost-BasedOptimization)£¬»áȥѡÔñÄÄÖÖÖ´Ðмƻ®¸ü»®Ëã,Ó°ÏìÊÇ·ñ×ßÏà¹ØµÄË÷ÒýµÈ.Èç¹ûÊÇCBOµÄ»°£¬ËüÒÀ¿¿×¼È·µÄ£¨»òÕß˵±È½Ï׼ȷµÄ£©Í³¼ÆÐÅÏ¢À´²úÉúÓÅ»¯µÄÖ´Ðз¾¶,Èç¹ûûÓÐ×ö¹ýͳ¼Æ£¬CBOÒ²¾ÍûÓÐ×öcostÆÀ¹ÀµÄÒÀ¾Ý ......