SQL Óë ORACLE µÄ±È½Ï (ת)
01¡¢SQLÓëORACLEµÄÄÚ´æ·ÖÅä
ORACLEµÄÄÚ´æ·ÖÅä´ó²¿·ÖÊÇÓÉINIT.ORAÀ´¾ö¶¨µÄ£¬Ò»¸öÊý¾Ý¿âʵÀý¿ÉÒÔÓÐNÖÖ·ÖÅä·½°¸£¬²»Í¬µÄÓ¦Óã¨OLTP¡¢OLAP£©ËüµÄÅäÖÃÊÇÓвàÖصġ£ SQL¸ÅÀ¨ÆðÀ´Ëµ£¬Ö»ÓÐÁ½ÖÖÄÚ´æ·ÖÅ䷽ʽ£º¶¯Ì¬ÄÚ´æ·ÖÅäÓ뾲̬ÄÚ´æ·ÖÅ䣬¶¯Ì¬ÄÚ´æ·ÖÅä³äÐíSQL×Ô¼ºµ÷ÕûÐèÒªµÄÄڴ棬¾²Ì¬ÄÚ´æ·ÖÅäÏÞÖÆÁËSQL¶ÔÄÚ´æµÄʹ Óá£
002¡¢SQLÓëORACLEµÄÎïÀí½á¹¹
×ܵý²£¬ËüÃǵÄÎïÀí½á¹¹ºÜÏàËÆ£¬SQLµÄÊý¾Ý¿âÏ൱ÓÚORACLEµÄģʽ£¨·½°¸£©£¬SQLµÄÎļþ×éÏ൱ÓÚORACLEµÄ±í¿Õ¼ä£¬×÷Óö¼ÊǾùºâDISK I/O£¬SQL´´½¨±íʱ£¬¿ÉÒÔÖ¸¶¨±íÔÚ²»Í¬µÄÎļþ×飬ORACLEÔò¿ÉÒÔÖ¸¶¨²»Í¬µÄ±í¿Õ¼ä¡£
CREATE TABLE A001£¨ID DECIMAL£¨8£¬0£©£© ON [Îļþ×é]
--------------------------------------------------------------------------------------------
CREATE TABLE A001£¨ID NUMBER£¨8£¬0£©£© TABLESPACE ±í¿Õ¼ä
×¢£ºÒÔºóËùÓÐʾÀý£¬ÏÈSQL£¬ºóORACLE
003¡¢SQLÓëORACLEµÄÈÕ־ģʽ
SQL¶ÔÈÕÖ¾µÄ¿ØÖÆÓÐÈýÖÖ»Ö¸´Ä£ÐÍ£ºSIMPLE¡¢FULL¡¢BULK-LOGGED£»ORACLE¶ÔÈÕÖ¾µÄ¿ØÖÆÓжþÖÖģʽ£º NOARCHIVELOG¡¢ARCHIVELOG¡£SQLµÄSIMPLEÏ൱ÓÚORACLEµÄNOARCHIVELOG£¬FULLÏ൱ÓÚ ARCHIVELOG£¬BULK-LOGGEDÏ൱ÓÚORACLE´óÅúÁ¿Êý¾Ý×°ÔØʱµÄNOLOGGING¡£¾³£ÓÐÍøÓѱ§Ô¹SQLµÄÈÕÖ¾ÅÓ´óÎÞ±ÈÇÒû·¨´¦ Àí£¬×î¼òµ¥µÄ°ì·¨¾ÍÊÇÏÈÇл»µ½SIMPLEģʽ£¬ÊÕËõÊý¾Ý¿âºóÔÙÇл»µ½FULL£¬¼ÇסÇл»µ½FULLÖ®ºóÒªÂíÉÏ×öÍêÈ«±¸·Ý¡£
004¡¢SQLÓëORACLEµÄ±¸·ÝÀàÐÍ
SQLµÄ±¸·ÝÀàÐͷֵļ«ÔÓ£ºÍêÈ«±¸·Ý¡¢ÔöÁ¿±¸·Ý¡¢ÈÕÖ¾±¸·Ý¡¢Îļþ»òÎļþ×鱸·Ý£»ORACLEµÄ±¸·ÝÀàÐ;ÍÇåäÀ¶àÀ²£ºÎïÀí±¸·Ý¡¢Âß¼±¸ ·Ý£»ORACLEµÄÂß¼±¸·Ý£¨EXP£©Ï൱ÓÚSQLµÄÍêÈ«±¸·ÝÓëÔöÁ¿±¸·Ý£¬ORACLEµÄÎïÀí±¸·ÝÏ൱ÓÚSQLµÄÎļþÓëÎļþ×鱸·Ý¡£SQLµÄ¸÷ÖÖ±¸·Ý¶¼ÃÜ ÇÐÏà¹Ø£¬ÒÔÍêÈ«±¸·ÝΪ»ù´¡£¬ÅäºÏÆäËüµÄ±¸·Ý·½Ê½£¬¾Í¿ÉÒÔÁé»îµØ±¸·ÖÊý¾Ý£»ORACLEµÄÎïÀí±¸·ÝÓëÂß¼±¸·Ý¸÷˾ÆäÖ°¡£SQL¿ÉÒÔÓжà¸öÈÕÖ¾£¬Ï൱ÓÚ ORACLEÈÕÖ¾×飬ORACLEµÄÈÕÖ¾×Ô¶¯Çл»²¢¹éµµ£¬SQLµÄÈÕÖ¾²»Í£µØÅòÕÍ……SQLÓи½¼ÓÊý¾Ý¿â£¬¿ÉÒÔ½«Êý¾Ý¿âºÜ·½±ãµØÒƵ½±ðÒ»¸ö·þÎñÆ÷£¬ ORACLEÓпɴ«Êä±í¿Õ¼ä£¬¿É²Ù×÷ÐԾ͵Ã×¢ÒâÀ²¡£
005¡¢SQLÓëORACLEµÄ»Ö¸´ÀàÐÍ
SQLÓÐÍêÈ«»Ö¸´Óë»ùÓÚʱ¼äµãµÄ²»ÍêÈ«»Ö¸´£»ORACLEÓÐÍêÈ«»Ö¸´Óë²»ÍêÈ«»Ö¸´£¬²»ÍêÈ«»Ö¸´ÓÐÈýÖÖ·½Ê½£º»ùÓÚÈ¡ÏûµÄ¡¢»ùÓÚʱ¼äµÄ¡¢»ùÓÚÐ޸ĵģ¨SCN£©µÄ»Ö¸´¡£²»ÍêÈ«»Ö¸´¿ÉÒÔ»Ö¸´Êý¾Ýµ½Ä³¸öÎȶ¨µÄ״̬µã¡£
006¡¢SQLÓëORACLEµÄÊÂÎñ¸ôÀë
SET TRANSACTION ISOLATION LEVEL
Ïà¹ØÎĵµ£º
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
ËäȻѧϰJavaºÜ¾ÃÁË£¬×Ô¼ºÒ²Á¬½Ó¹ýһЩÊý¾Ý¿â£¬±ÈÈçmysqlÖ®ÀàµÄ£¬Èç½ñÄØ£¬Ò²Ñ§Ï°ÁËÒ»¶Îʱ¼äµÄOracle£¬È»¶øÄØ£¬½ñÌìÊÇÎÒµÚÒ»´ÎÁ¬½ÓOracle£¬ºÙºÙ£¬Ó¦¸Ã»¹²»ËãÌ«³Ù°É¡£
½ñÌìÄØ£¬Óе㱿׾£¬´ó¼ÒĪЦ£¡
ÎÒÕâÊÇÒ»¸ö²éѯÀý×Ó
Ê×ÏÈ£¬Ô ......
ÔÚ×ö±¨±íʱoqlÓï¾äÖÐÓÐʱÐèÒªÓõ½Óû§×Ô¶¨Ò庯Êý£¬µ÷ÓÃÖ®ºó±¨±í±¨´í£º¡° ¶ÔÊý¾Ý¼¯¡°DataQuery¡±Ö´Ðвéѯʧ°Ü¡£ 'fn_GetLevelItemCatName' ²»ÊÇ¿ÉÒÔʶ±ðµÄ º¯ÊýÃû³Æ¡£ ')' ¸½½üÓÐÓï·¨´íÎó¡£ ¡° £¬ÓÚÊÇÎÒ½«oqlÓï¾ä½âÎöºóÄÃsqlÓï¾äµ½sql server 2008ÖÐÈ¥Ö´Ðб¨´í£º 'fn_GetLevelItemCatName' ²»ÊÇ¿ÉÒÔʶ±ðµÄº¯ÊýÃû³Æ¡£ ÕâÖÖÎÊÌ ......
´Ë·½·¨ÊÇ´Óһλǰ±²ÄÇÀïѧÀ´µÄ£¬µ¼Óï¾äºÜ·½±ã£¬Ö»ÐèдÇå³þ±íÃû¾ÍÐС£ÅÂÍüÁË£¬ÔݼÇһϡ££¨sql server 2005ÊÔÑé¹ý£©
µÚÒ»´ÎʹÓõĻ°£¬ÐèÒª½¨Á¢ÈçÏ´洢¹ý³Ì¡£´úÂëºÜ³¤£¬Ã»¹Øϵ£¬Ö±½Ócopy¾ÍÐС£
--------- outputdata ´æ´¢¹ý³Ì
CREATE PROCEDURE dbo.OutputData
@tablename sysname
AS
declare @column va ......
OracleÖÐÈçºÎÓÃÒ»ÌõSQL¿ìËÙÉú³É10ÍòÌõ²âÊÔÊý¾Ý
×öÊý¾Ý¿â¿ª·¢»ò¹ÜÀíµÄÈ˾³£Òª´´½¨´óÁ¿µÄ²âÊÔÊý¾Ý£¬¶¯²»¶¯¾ÍÐèÒªÉÏÍòÌõ£¬Èç¹ûÒ»ÌõÒ»ÌõµÄ¼È룬
ÄÇ»áÀË·Ñ´óÁ¿µÄʱ¼ä£¬±¾ÎĽéÉÜÁËOracleÖÐÈçºÎͨ¹ýÒ»ÌõSQL¿ìËÙÉú³É´óÁ¿µÄ²âÊÔÊý¾ÝµÄ·½·¨¡£
²úÉú²âÊÔÊý¾ÝµÄSQLÈçÏ£º
SQL> select rownum as id,
&nb ......