OracleÖÐNOT INµÄÎÊÌâ
½ñÌìÔÚ¿ª·¢µÄʱºòÐèÒª²éѯÊý¾Ý¿âÖÐÄÄЩѧÉúÓжà¸öѧºÅ£¬Ð´µÄsqlÈçÏ£º
select * from(
select count(t1.xh) as xhs,t2.uuid,t3.xm as xsxm,t3.sfzjh, t1.xjzt as xjzt from hust_xs_jb t1
INNER JOIN hust_xx_identity t2 on t1.sfid= t2.sfid
INNER JOIN hust_xx_profile t3 on t2.uuid=t3.uuid group by t2.uuid ,t3.xm, t3.sfzjh, t1.xjzt order by t3.xm) t
where xhs>=2 and xjzt not in('15','17','19','21','30','31','32','33','34','35','36','37','38','39','40','51','52','53','54')
²é³öµÄÊý¾ÝΪ¿Õ£¬µ«ÊÇÈ¥µôwhereÌõ¼þÖеĵڶþ¸öÌõ¼þ£¨Ò²¾ÍÊÇNOT INÌõ¼þ£©È´Äܲé³ö31ÌõÊý¾Ý£¬ÇÒÆäÖÐÖ»ÓÐÒ»ÌõµÄÊý¾ÝµÄXJZT×Ö¶ÎΪ33£¬ÆäËûµÄ¶¼Îªnull£¬Ó¦¸Ã²é³ö30ÌõÊý¾Ý°¡£¬ÕâÊÇΪʲôÄØ£¿ÄѵÀNOT IN»á½«nullÖµ¹ýÂ˵ô£¿ÓÚÊǽ«sqlÉÔ¼ÓÐ޸ģº
select * from(
select count(t1.xh) as xhs,t2.uuid,t3.xm as xsxm,t3.sfzjh, t1.xjzt as xjzt from hust_xs_jb t1
INNER JOIN hust_xx_identity t2 on t1.sfid= t2.sfid
INNER JOIN hust_xx_profile t3 on t2.uuid=t3.uuid group by t2.uuid ,t3.xm, t3.sfzjh, t1.xjzt order by t3.xm) t
where xhs>=2 and (xjzt not in('15','17','19','21','30','31','32','33','34','35','36','37','38','39','40','51','52','53','54') or xjzt is null)
¾ÍÄܲé³öËùÐèÒªµÄ30ÌõÊý¾ÝÁË¡£ÍøÉϲéÁËһϣ¬ËµOracle not in Èç¹û·µ»ØµÄÓÐnullÖµµÄ»°£¬¾Í²»»áÓмǼ¡£
Ïà¹ØÎĵµ£º
£±£º´´½¨»·¾³±äÁ¿
¡¡¡¡export $ORACLE_HOME $ORACLE_SID£¨×¼±¸½¨Á¢µÄÊý¾Ý¿âµÄSID)
£²£º½¨Á¢³õʼ»¯²ÎÊýÎļþ
¡¡¡¡$ORACLE_HOME/dbs/init$ORACLE_SID.ora (¿ÉÒÔ´ÓÒѾ½¨ºÃµÄ¿âÀïÃæÄÃ,ͨ³£ÔÚpfile ÀïÃ棩,Ò²¿ÉÒÔ×Ô¼º½¨£º
¡¡¡¡control_files = ($ORACLE_HOME/oradata/$ORACLE_SID/control1.ctl,$ORACLE_HOME/oradata/$ORACLE ......
×î½ü¹¤×÷ÉÔ΢±È½Ï¿ÕÏУ¬±ã°ÑʹÓÃDBCA ´´½¨Êý¾Ý¿âʱµÄÊä³öµÄ½Å±¾ÄÃÀ´Ñо¿Ñо¿£¬²¢²ÎÕսű¾Îļþ£¬ÊÖ¶¯ÊµÏÖÒ»¸öÊý¾Ý¿âµÄ´´½¨¹ý³Ì¡£Ê×ÏÈʹÓÃDBCA¹¤¾ß´´½¨Ò»·Ý½¨¿â½Å±¾£¬Õû¸ö½Å±¾Îļþ°üº¬ÈçÏÂÎļþ£¬
¸Ð¾õÓ¦¸ÃÊÇ´ÓBATÎļþ¿ªÊ¼ÈëÊÖ£¬´ò¿ªBATÎļþ²é¿´ÄÚÈÝÈçÏ£º
mkdir f:\oracle\product\10.2.0\admin\test\adump
mkdir f:\oracl ......
ÎÒÃÇÒª×öµ½²»µ«»áдSQL,»¹Òª×öµ½Ð´³öÐÔÄÜÓÅÁ¼µÄSQL,ÒÔÏÂΪ±ÊÕßѧϰ¡¢ÕªÂ¼¡¢²¢»ã×ܲ¿·Ö×ÊÁÏÓë´ó¼Ò·ÖÏí£¡
£¨1£© Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
ORACLEµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí£ ......
ADO ʹÓà ORACLE provider FOR OLE DB Çý¶¯Ê±
µ÷Óô洢¹ý³ÌÐèÒª·µ»ØÓαêʱ£¬ÔÚÁ¬½Ó×Ö·û´®ÖÐÔö¼Ó
PLSQLRSet=1 ²ÎÊý¡£
´æ´¢¹ý³ÌÖеÄÓα겻ÐèÒª¸¶³õʼֵ
Èç:
sp1: TADOStoredProc;
sp1.Close;
sp1.ProcedureName := 'pkg_ScanList_By_JobNo.sp_GetScanList';& ......
Íⲿ±í×÷ÓÃ:¼ÓÔغÍжÔØÊý¾Ý
Íⲿ±í¶¼ÊÇÖ»¶Á±í,²»ÄܽøÐÐupdate,insert,delete²Ù×÷.
Oracle ÌṩÁ½ÖÖ·ÃÎÊÇý¶¯: ORACLE_LOADER, ORACLE_DATAPUMP(oracle 10.2ºóÐÂÔöµÄÇý¶¯)
ORACLE_LOADER: ËüÀûÓÃoracle loader¼¼Êõ´ÓÍⲿ±í¶ÁÈ¡Êý¾Ý. Ëü»¹¾ßÓÐÀàËÆSQL*Loader¹¤¾ß¿ØÖÆÎļþÓï·¨µÄÊý¾ÝÓ³ÉäÄÜÁ¦.
ORACLE_DAT ......