¸ß¼¶SQL @Oracle
1.¼¯ºÏ²Ù×÷
ѧϰoracle
Öм¯ºÏ²Ù×÷µÄÓйØÓï¾ä£¬
ÕÆÎÕunion,union
all,minus,interestµÄʹÓÃ,Äܹ»ÃèÊö½áºÏÔËË㣬²¢ÇÒÄܹ»½«¶à¸ö²éѯ×éºÏµ½Ò»¸ö²éѯÖÐÈ¥£¬Äܹ»¿ØÖÆÐзµ»ØµÄ˳Ðò¡£
°üº¬¼¯ºÏÔËËãµÄ
²éѯ³ÆÎª¸´ºÏ²éѯ¡£¼û±í¸ñ1-1
±í1-1
Operator Returns content
UNION
ÓÉÿ¸ö²éѯѡÔñµÄËùÓв»Öظ´µÄÐÐ ²¢¼¯²»°üº¬Öظ´Öµ
UNION ALL
ÓÉÿ¸ö²éѯѡÔñµÄËùÓеÄÐУ¬°üÀ¨ËùÓÐÖØ¸´µÄÐÐ ÍêÈ«²¢¼¯°üº¬Öظ´Öµ
INTERSECT
ÓÉÿ¸ö²éѯѡÔñµÄËùÓв»Öظ´µÄÏཻÐÐ ½»¼¯
MINUS
ÔÚµÚÒ»¸ö²éѯÖУ¬²»ÔÚºóÃæ²éѯÖУ¬²¢ÇÒ½á¹ûÐв»Öظ´ ²î¼¯
ËùÓеļ¯ºÏÔËËãÓëµÈºÅµÄÓÅÏȼ¶Ïàͬ£¬Èç¹ûSQL
Óï¾ä°üº¬¶à¸ö¼¯ºÏÔËËã²¢ÇÒûÓÐÔ²À¨ºÅÃ÷È·µØÖ¸¶¨ÁíÒ»¸ö˳
Ðò£¬Oracle·þÎñÆ÷½«ÒÔ´Ó×óµ½ÓÒµÄ˳Ðò¼ÆËã¡£ÄãÓ¦¸ÃʹÓÃÔ²À¨ºÅÀ´Ã÷È·µØÖ¸¶¨´øÁíÍâµÄ¼¯ºÏÔËËãµÄINTERSECT (Ïཻ) ÔËËã²éѯÖеĸ³ÖµË³Ðò¡£
Union
all ЧÂÊÒ»°ã±Èunion¸ß¡£
1.1.unionºÍunion all
UNION(ÁªºÏ)ÔËËã
UNIONÔËËã·µ»ØËùÓÐÓÉÈÎ
Ò»²éѯѡÔñµÄÐС£ÓÃUNIONÔËËã´Ó¶à±í·µ»ØËùÓÐÐУ¬µ«³ýÈ¥ÈκÎÖØ¸´µÄÐС£
ÔÔò :
􀂃±»Ñ¡ÔñµÄÁÐÊý
ºÍÁеÄÊý¾ÝÀàÐͱØÐëÊÇÓëËùÓÐÓÃÔÚ²éѯÖеÄSELECTÓï¾äÒ»Ö¡£ÁеÄÃû×Ö²»±ØÏàͬ¡£
􀂃ÁªºÏÔËËãÔÚËùÓб»Ñ¡ÔñµÄÁÐÉϽø
ÐС£
􀂃ÔÚ×öÖØ¸´¼ì²éµÄʱºò²»ºöÂÔ¿Õ(NULL)Öµ¡£
􀂃INÔËËãÓбÈUNION
ÔËËã¸ßµÄÓÅÏȼ¶¡£
􀂃ÔÚĬÈÏÇé¿öÏ£¬Êä³öÒÔSELECT×Ó¾äµÄµÚÒ»ÁеÄÉýÐòÅÅÐò¡£
È«ÁªºÏ(UNION
ALL)ÔËËã
ÓÃÈ«ÁªºÏÔËËã´Ó¶à¸ö²éѯÖзµ»ØËùÓÐÐС£
ÔÔò
􀂃ºÍÁªºÏ²»Í¬£¬Öظ´µÄÐв»±»¹ý
ÂË£¬²¢ÇÒĬÈÏÇé¿öÏÂÊä³ö²»ÅÅÐò¡£
􀂃²»ÄÜʹÓÃDISTINCT¹Ø¼ü×Ö¡£
ʹÓãº
Select
statement union | union all Select statement;
1.2.intersect½»¼¯²Ù×÷
Ïà
½»ÔËËã
ÓÃÏཻÔËËã·µ»Ø¶à¸ö²éѯÖÐËùÓеĹ«¹²ÐС£ ÎÞÖØ¸´ÐС£
ÔÔò
𙦦
Ïà¹ØÎĵµ£º
֮ǰûÔõôÑо¿¹ýMySQLµÄÈÝÁ¿£¬ÓÉÓÚÏîÄ¿ÐèÒª£¬ÐèÒªÒ»¸öÃâ·ÑµÄ¿ÉÒÔÖ§³ÖǧÍòÌõÊý¾Ý¼¶ÒÔÉϵÄÊý¾Ý¿â£¬²é¿´ÏÂmysqlµÄÎĵµ£¬·¢ÏÂmysql»¹ÊǺÜÇ¿´óµÄ£¬¼Ç¼Ï£¬ºÇºÇ¡£ÔÎÄÈçÏ£º
MySQL 3.22ÏÞÖÆµÄ±í´óСΪ4GB¡£ÓÉÓÚÔÚMySQL 3.23ÖÐʹÓÃÁËMyISAM´æ´¢ÒýÇæ£¬×î´ó±í³ß´çÔö¼Óµ½ÁË65536TB£¨2567 –1×Ö½Ú£©¡£ÓÉÓÚÔÊÐíµÄ±í³ß´ç¸ü´ ......
×î½üÐèÒª²âÊÔÒ»¸öÏîÄ¿, ÐèÒªSQL SERVER 2008, ÔÚ΢ÈíÏÂÔØÆÀ¹À°æºó½øÐа²×°
Öм䷢ÏÖÁËÖÖÖÖ°²×°Ê§°ÜµÄÎÊÌ⣬¶Ô¸Ã¹ý³Ì½øÐÐÁ˼Ǽ£¬Ï£Íû¸øÓöµ½´ËÀàÎÊÌâµÄÅóÓÑÃǹ²Í¬Ì½ÌÖ£¬ÓÐËù°ïÖú£º
1. WINDOWS 2003, WINDOWS 7 ÐÂ×°µÄʱºò¿ÉÐУ¬µ«ÊÇÈç¹ûÓÐɾ³ýºóÖØÐ°²×°ÔòÎÞ·¨Ôٴΰ²×°
2. ¸ù¾ÝÍøÉÏÎÞÊýÏÈÇýµÄʧ°ÜÌáʾ£¬ ½øÐÐÁËÎÞÊýÖØÊÔ£¬ ......
--exec [P_AutoGenerateNumber] 'reception_apply','generate_code','',7
/*
¹ý³Ì˵Ã÷:Éú³É×Ô¶¯±àºÅ
´´½¨Ê±¼ä:2010Äê1ÔÂ12ÈÕ
×÷Õß:feng
debug:ÉÐδ¿¼ÂDZàºÅÒç³öÇé¿ö
*/
ALTER proc [P_AutoGenerateNumber]
(
@table ......
ÔÚÍøÉÏËÑË÷Á˰ëÌ죬Ö÷ÒªÔÒòÊÇoci.dllÊÇ64λµÄ,¶øPL/SQL developerÖ»ÓÐ32λµÄ,ÔÚʹÓÃ64λoci.dllÎļþʱ³ö´í.
ÔÚÍøÉÏÕÒÁ˺ܶà×ÊÁÏ,×î·½±ãµÄ½â¾ö·½°¸ÈçÏÂ:
1.µ½oracle¹ÙÍøÏÂÔØOracle InstantClient,ÎÒϵÄÊÇinstantclient-basiclite-win32-10.2.0.4.zip,°ÑËü½âѹËõµ½
oracle°²×°Ä¿Â¼productÏÂ,ÆäÖл¹ÓÐÒ»¸öĿ¼Ϊ10.2.0. ......
/*
´¥·¢Æ÷»ñÈ¡SQLÓï¾äÔöÁ¿´«Êä
¹¦ÄÜ£º²¶×½Ð޸ıíµÄSQLÓï¾ä
ʹÓÃ˵Ã÷£º 1¡¢ÏÈн¨Ò»±íÊÖ¶¯Ð´ÈëÖ÷¼üÐÅÏ¢»òÕßΨһË÷Òý
Create table prmary_key
(tab_name varchar(255),
key_name varchar(255))
--´Ë±í½öÔÚ½¨Á¢´¥·¢Æ÷ʱʹÓ㬽¨ÍêËùÓд¥·¢Æ÷ºó ¼ÇµÃ ......