OracleµÄNVL()º¯ÊýºÍMySQLÖеÄIfNull()º¯Êý
MS SQL ÖеÄIsNull()º¯Êý£º
IsNull ( check_expression , replacement_expression )
check_expression: ¿ÉÒÔÊÇÈκÎÀàÐÍ,½«Òª¼ì²éµÄ±í´ïʽ ²»Îª¿Õ£¬·µ»ØËü
replacement_expression: ÀàÐͱØÐëºÍcheck_expressionÏàͬ£¬check_expressionΪnull£¬·µ»ØËü
Õâ¸öº¯ÊýµÄ×÷ÓþÍÊÇ£ºÅжÏcheck_expressionÊÇ·ñΪ¿Õ£¬Îª¿Õ¾Í·µ»Øreplacement_expression£¬²»Îª¿ÕÔò·µ»Øcheck_expression
eg£º
SELECT IsNull(null,1) ·µ»Ø1
SELECT IsNull(null,’ÄÐ') ·µ»ØÄÐ
SELECT IsNull(‘’,'ÄÐ') ×¢Òâ·µ»ØµÄ²»ÊÇÄÐŶ
MS SqlÖÐµÄ IsNull( )º¯ÊýÏ൱ÓÚOracleÖÐµÄ NVL( )º¯Êý£º
NVL£¨EXP1,EXP2£©£¬º¯Êý·µ»Øexp1ºÍexp2 ÖеÚÒ»¸ö²»ÎªnullµÄÖµ¡£
Èç¹ûexp1Ϊ¿Õ¾Í·µ»Øexp2£¬·ñÔò·µ»Øexp1¡£
Ï൱ÓÚMysqlÖеÄIfNull()º¯Êý£º
Ó¦Óó¡¾°£º±ÈÈçÒ»ÕÅ±í£ºProducts £¬¸Ã±íÈ¡×Ô http://www.w3schools.com/SQL/sql_isnull.asp
P_Id
ProductName
UnitPrice
UnitsInStock
UnitsOnOrder
1
Jarlsberg
10.45
16
15
2
Mascarpone
32.56
23
3
Gorgonzola
15.67
9
20
¼ÙÉèUnitsOnOrderÕâÒ»ÁÐÊÇ¿ÉÒÔΪ¿ÕµÄ£¬¿ÉÄܺ¬ÓпÕÖµ£¬ÄÇôִÐÐ
SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder) from Products ÓпÉÄÜ»á³ö´í
ÒòΪUnitsOnOrderÕâÒ»ÁÐΪ¿ÕµÄʱºò£¬ÔËËãµÄ½á¹û¾ÍÊÇ¿Õ
mssqlµÄisnull()º¯Êý¿ÉÒÔÖ¸¶¨´¦Àí¿ÕÖµµÄ·½Ê½£º
¸ÄΪÏÂÃæµÃ¾Í¿ÉÒÔÁËSELECT ProductName,UnitPrice*(UnitsInStock+isnull(UnitsOnOrder,0)) from Products
isnull(unitsOnOrder,0) unitsOnOrderΪ¿ÕµÄʱºò·µ»Ø0
COALESCE() function
COALESCE ( expression [ ,...n ] )
Ï൱ÓÚ:
COALESCE(expression1,...n) µÄ¹¦ÄÜÓëÒÔÏ CASE ±í´ïʽÏàͬ£º
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
...
ELSE expressionN
END
Ïà¹ØÎĵµ£º
ʲôÊǺϲ¢¶àÐÐ×Ö·û´®£¨Á¬½Ó×Ö·û´®£©ÄØ£¬ÀýÈ磺
SQL> desc test;
Name Type Nullable Default Comments
------- ------------ -------- ------- --------
COUNTRY VARCHAR2(20) Y &nb ......
1¡¢´´½¨´æ´¢¹ý³Ì
create or replace procedure test(var_name_1 in type,var_name_2 out type) as
--ÉùÃ÷±äÁ¿(±äÁ¿Ãû ±äÁ¿ÀàÐÍ)
begin
--´æ´¢¹ý³ÌµÄÖ´ÐÐÌå
end test;
´òÓ¡³öÊäÈëµÄʱ¼äÐÅÏ¢
E.g:
create or replace procedure test(workDate in Date) is
begin
dbms_output.putline('The input date is: ......
ÒÔÇ°¾³£Ê¹ÓÃoracleµÄ¿Í»§¶Ë¹¤¾ßtoadÀ´½øÐÐÊý¾Ý¿âµÄµ¼ÈëºÍµ¼³ö£¬Æäʵ¸öÈ˵¹²»Ï²»¶ÓÃÕâ¸ö¿Í»§¶Ë¹¤¾ß£¬¸Ð¾õ¹¦ÄܺÜÇ¿´óµÄÒ»¸ö¹¤¾ß£¬dbaÊÊÓ᣿ª·¢»¹ÊÇÓÃpl/sql±È½ÏºÃ£¬¼ò½àµÄ½çÃæ¡£
C:\Users\Administrator>imp nwgis/pass file=e:/nwgis20100529.DMP log=dible_db full=y ignore=y
Æô¶¯ÃüÁîÐУ¬ÐÞ¸Ä ......
ÔÚORACLEÀïÈç¹ûÓöµ½Ìرð´óµÄ±í£¬¿ÉÒÔʹÓ÷ÖÇøµÄ±íÀ´¸Ä±äÆäÓ¦ÓóÌÐòµÄÐÔÄÜ¡£
¡¡¡¡ÒÔsystemÉí·ÝµÇ½Êý¾Ý¿â£¬²é¿´ v$optionÊÓͼ£¬Èç¹ûÆäÖÐPartitionΪTRUE£¬ÔòÖ§³Ö·ÖÇø¹¦ÄÜ£»·ñÔò²»Ö§³Ö¡£PartitionÓлùÓÚ·¶Î§¡¢¹þÏ£¡¢×ÛºÍÈýÖÖÀàÐÍ¡£ÎÒÃÇÓõıȽ϶àµÄÊÇ°´·¶Î§·ÖÇøµÄ±í¡£
¡¡¡¡ÎÒÃÇÒÔÒ»¸ö2001Ä꿪ʼʹÓõÄÁôÑÔ°æ×öÀý×Ó½²Êö·ÖÇ ......
oracleÖÐpro*cµÄѧϰ
¡¡Ò» Pro*C ³ÌÐò¸ÅÊö£º
1£®Ê²Ã´ÊÇPro*C³ÌÐò
ÔÚORACLEÊý¾Ý¿â¹ÜÀíºÍϵͳÖУ¬ ÓÐÈýÖÖ·ÃÎÊÊý¾Ý¿âµÄ·½·¨£»
£¨1£© ÓÃSQL*Plus£¬ ËüÓÐSQLÃüÁîÒÔ½»»¥µÄÓ¦ÓóÌÐò·ÃÎÊÊý¾Ý¿â£»
£¨2£© ÓõÚËÄ´úÓïÑÔÓ¦Óÿª·¢¹¤¾ß¿ª·¢µÄÓ¦ÓóÌÐò·ÃÎÊÊý¾Ý¿â£¬ÕâЩ¹¤¾ßÓÐSQL*froms£¬QL*Reportwriter,SQL*MenuµÈ£»
£¨3£© ÀûÓÃÔÚ ......