Oracle SQLµÄÓ²½âÎöºÍÈí½âÎö
˵µ½Èí½âÎö£¨soft prase£©ºÍÓ²½âÎö£¨hard prase£©£¬¾Í²»Äܲ»ËµÒ»ÏÂOracle¶ÔsqlµÄ´¦Àí¹ý³Ì¡£µ±Äã·¢³öÒ»ÌõsqlÓï¾ä½»¸¶Oracle£¬ÔÚÖ´ÐкͻñÈ¡½á¹ûǰ£¬Oracle¶Ô´Ësql½«½øÐм¸¸ö²½ÖèµÄ´¦Àí¹ý³Ì£º
1¡¢Óï·¨¼ì²é£¨syntax check£©
¼ì²é´ËsqlµÄƴдÊÇ·ñÓï·¨¡£
2¡¢ÓïÒå¼ì²é£¨semantic check£©
ÖîÈç¼ì²ésqlÓï¾äÖеķÃÎʶÔÏóÊÇ·ñ´æÔÚ¼°¸ÃÓû§ÊÇ·ñ¾ß±¸ÏàÓ¦µÄȨÏÞ¡£
3¡¢¶ÔsqlÓï¾ä½øÐнâÎö£¨prase£©
ÀûÓÃÄÚ²¿Ëã·¨¶Ôsql½øÐнâÎö£¬Éú³É½âÎöÊ÷£¨parse tree£©¼°Ö´Ðмƻ®£¨execution plan£©¡£
4¡¢Ö´ÐÐsql£¬·µ»Ø½á¹û£¨execute and return£©
ÆäÖУ¬Èí¡¢Ó²½âÎö¾Í·¢ÉúÔÚµÚÈý¸ö¹ý³ÌÀï¡£
OracleÀûÓÃÄÚ²¿µÄhashËã·¨À´È¡µÃ¸ÃsqlµÄhashÖµ£¬È»ºóÔÚlibrary cacheÀï²éÕÒÊÇ·ñ´æÔÚ¸ÃhashÖµ£»
¼ÙÉè´æÔÚ£¬Ôò½«´ËsqlÓëcacheÖеĽøÐбȽϣ»
¼ÙÉè“Ïàͬ”£¬¾Í½«ÀûÓÃÒÑÓеĽâÎöÊ÷ÓëÖ´Ðмƻ®£¬¶øÊ¡ÂÔÁËÓÅ»¯Æ÷µÄÏà¹Ø¹¤×÷¡£ÕâÒ²¾ÍÊÇÈí½âÎöµÄ¹ý³Ì¡£
³ÏÈ»£¬Èç¹ûÉÏÃæµÄ2¸ö¼ÙÉèÖÐÈÎÓÐÒ»¸ö²»³ÉÁ¢£¬ÄÇôÓÅ»¯Æ÷¶¼½«½øÐд´½¨½âÎöÊ÷¡¢Éú³ÉÖ´Ðмƻ®µÄ¶¯×÷¡£Õâ¸ö¹ý³Ì¾Í½ÐÓ²½âÎö¡£
´´½¨½âÎöÊ÷¡¢Éú³ÉÖ´Ðмƻ®¶ÔÓÚsqlµÄÖ´ÐÐÀ´ËµÊÇ¿ªÏú°º¹óµÄ¶¯×÷£¬ËùÒÔ£¬Ó¦µ±¼«Á¦±ÜÃâÓ²½âÎö£¬¾¡Á¿Ê¹ÓÃÈí½âÎö¡£
Õâ¾ÍÊÇÔںܶàÏîÄ¿ÖУ¬³«µ¼¿ª·¢Éè¼ÆÈËÔ±¶Ô¹¦ÄÜÏàͬµÄ´úÂëҪŬÁ¦±£³Ö´úÂëµÄÒ»ÖÂÐÔ£¬ÒÔ¼°ÒªÔÚ³ÌÐòÖжàʹÓð󶨱äÁ¿µÄÔÒò¡£
/****************************************************/
´ó¼Ò¶¼ÔÚ˵ÔÚSqlÖÐʹÓÃÁËBind Var£¨°ó¶¨±äÁ¿£©»áÌá¸ß²»ÉÙÐÔÄÜ£¬ÄÇËûµ½µ×ÊÇÈçºÎÌá¸ßÐÔÄܵÄÄØ£¿
ʹÓÃÁËBind VarÄÜÌá¸ßÐÔÄÜÖ÷ÒªÊÇÒòΪÕâÑù×ö¿ÉÒÔ¾¡Á¿±ÜÃâ²»±ØÒªµÄÓ²·ÖÎö£¨Hard Parse£©¶ø½ÚÔ¼ÁËʱ¼ä£¬Í¬Ê±½ÚÔ¼ÁË´óÁ¿µÄCPU×ÊÔ´¡£
µ±Ò»¸öClientÌá½»Ò»ÌõSql¸øOracleºó£¬Oracle Ê×ÏÈ»á¶ÔÆä½øÐнâÎö£¨Parse£©£¬È»ºó½«½âÎö½á¹ûÌá½»¸øÓÅ»¯Æ÷£¨Optimiser£©À´½øÐÐÓÅ»¯¶øÈ¡µÃOracleÈÏΪµÄ×î
Ïà¹ØÎĵµ£º
1.1.1 OracleÎﻯÊÓͼ¼ò½é
1. ÎﻯÊÓͼ˵Ã÷
ÎﻯÊÓͼ (Materialized View)£¬ÔÚÒÔǰµÄOracle°æ±¾ÖгÆÎª¿ìÕÕ(Snapshot)¡£Oracle µÄÎﻯÊÓͼÌṩÁËÇ¿´óµÄ¹¦ÄÜ£¬¿ÉÒÔÓÃÓÚÔ¤ÏȼÆËã²¢±£´æ±íÁ¬½Ó»ò¾Û¼¯µÈºÄʱ½Ï¶àµÄ²Ù×÷µÄ½á¹û ......
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[pr_xls_to_tb]
@path varchar(200),--EXCEL·¾¶Ãû
@tbName varchar(30),--±íÃû
@stName varchar(30) --excelÖÐÒª¶ÁµÄSHEETÃû
as
declare @sql varchar(500),--×îºóÒªÖ´ÐеÄSQL
@stName_Real varchar(35),--ÕæÕýµÄSHEETÃû
......
SQL×¢Èë¹¥»÷·À·¶¼¼ÇÉ
Ò»°ãµÄSQL×¢Èë¹¥»÷¶¼ÊÇͨ¹ý¹¹½¨Ò»Ìõ¸´ÔÓµÄsqlÓï¾ä£¬
ͨ¹ýÍøÒ³Â©¶´À´Ö´ÐÐsqlÓï¾ä£¬´ïµ½¹¥»÷Êý¾Ý¿âµÄÄ¿µÄ¡£
Èçͨ¹ýÎÄÕÂIDÀ´²éѯijһƪÎÄÕµÄÍøÒ³£¬
ͨ³£²ÉÓõÄsqlÓï¾äΪ£º
sql="select top 1 * from articles where articId="&request("id")
ÄÇô¿ÉÒÔ¼òµ ......
Ò»¡¢PL/SQL³öÏÖµÄÄ¿µÄ
¡¡¡¡½á¹¹»¯²éѯÓïÑÔ(Structured Query
Language£¬¼ò³ÆSQL)ÊÇÓÃÀ´·ÃÎʹØÏµÐÍÊý¾Ý¿âÒ»ÖÖͨÓÃÓïÑÔ£¬ËüÊôÓÚµÚËÄ´úÓïÑÔ£¨4GL£©£¬ÆäÖ´ÐÐÌØµãÊǷǹý³Ì»¯£¬¼´²»ÓÃÖ¸Ã÷Ö´ÐеľßÌå·½·¨ºÍ;
¾¶£¬¶øÊǼòµ¥µÄµ÷ÓÃÏàÓ¦Óï¾äÀ´Ö±½ÓÈ¡µÃ½á¹û¼´¿É¡£ÏÔÈ»£¬ÕâÖÖ²»¹Ø×¢ÈκÎʵÏÖϸ½ÚµÄÓïÑÔ¶ÔÓÚ¿ª·¢ÕßÀ´ËµÓÐ׿«´óµÄ ......
/*
--×÷ÓãºSQL SERVER²Ù×÷ACCESSµÄ´æ´¢¹ý³Ì
----------------------Ó÷¨-------------------------------
--´´½¨±ítc
exec sp_operate_access 'create table tc(id int)','c:\db1.mdb'
go
--ÔÚtc±í²åÈëÊý¾Ý
exec sp_operate_access 'insert into tc(id) values(1)','c:\db1.mdb'
go
--ɾ³ýtc±íµÄÊý¾Ý
exec sp_op ......