oracle vs. SQL ͬÒå´Êsynonym ±ðÃû alias
oracle ͬÒå´Ê synonym ΪÊý¾Ý¿â¶ÔÏó½¨Á¢Í¬Òå´Êºó,µ±dba¶ÔÊý¾Ý¿â¶ÔÏó(table,view,ÐòÁÐ)×öÁËÒ»¸ö¼òµ¥¸Ä¶¯Ö®ºó,ÈçÃû×Ö»òÕ߽ṹ¸Ä±ä,Ôò²»ÐèÒªÖØиü¸Ä²¢±àÒëÓ¦ÓÃ.ÔÚÊý¾Ý¿â×ÖµäÖÐÓж¨Òå,²»ÐèÒª½øÐÐÈκδ洢.
oracle±ðÃûalias,With as¡¡Éú³ÉÒ»¸öÁÙʱ±í£¬¶ÔÓÚ¸´ÔÓµÄÊý¾Ý²éѯ£¬¿ÉÒÔ¼õÉÙ×ÊÔ´ÏûºÄ¡¢Ìá¸ßÐÔÄÜ¡£9iÒýÈë¡£
with alias as (sql_statement) select * from alias
PHP code:
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as scott
SQL>
SQL> WITH emp AS (SELECT * from dept)
2 SELECT * from emp
3 ;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
withºóÃæµÈÓÚ½¨Á¢ÁËÒ»¸öÁÙʱ±í£¬°Ñ¸´ÔӵIJéѯ·ÅÔÚÕâ¶ù£¬Ö»ÔËÐÐÒ»´Î
ºóÃæÓï¾äÖпÉÒÔ¶à´ÎÒýÓã¬Ìá¸ßÁËЧÂÊ£º
µ«ÊÇËüµÄ»úÖƺÍÁÙʱ±íÒ»Ñù£¬ËùÒÔûÓÐindex,¶øÇÒ²»ÊʺϴóÊý¾ÝÁ¿£¬×îºÃÊÇ·µ»ØСÊý¾ÝÁ¿µÄ¼Ç¼Êý£¬µ«ÊDzéѯÓֱȽϸ´ÔӵĶ«Î÷
Óï¾äÈ磺
with temp as ( select ....) --complex query
select * from temp, a
union
select * from temp, b --¶à´ÎÒýÓÃ
....
ORACLE°ÑËü½Ð×ö: subquery_factoring_clause
Oracle9i SQL Reference
http://download-west.oracle.com/ ... ts_103a.htm#2075668
SQLµÄalias,¿ÉÒÔ¶Ô±íºÍÁÐÈ¡±ðÃû,ʹ²éѯ³ÌÐò¸üÒ×ÔĶÁºÍÊéд¡£
¼ÙÉèÎÒÃÇÓÐÁ½¸ö±í·Ö±ðÊÇ£º"Persons" ºÍ "Product_Orders"¡£ÎÒÃÇ·Ö±ðΪËüÃÇÖ¸¶¨±ðÃû "p" ºÍ "po"¡£
ÏÖÔÚ£¬ÎÒÃÇÏ£ÍûÁгö "John Adams" µÄËùÓж¨µ¥¡£
ÎÒÃÇ¿ÉÒÔʹÓÃÏÂÃæµÄ SELECT Óï¾ä£º
SELECT po.OrderID, p.LastName, p.FirstName
from Persons AS p, Product_Orders AS po
WHERE p.LastName='Adams'
WHERE p.FirstN
Ïà¹ØÎĵµ£º
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£¬ºÙºÙ£¬Ó¦¸Ã»¹²»ËãÌ«³Ù°É¡£
½ñÌìÄØ£¬Óе㱿׾£¬´ó¼ÒĪЦ£¡
ÎÒÕâÊÇÒ»¸ö²éѯÀý×Ó
Ê×ÏÈ£¬Ô ......
ʹÓà SQL Server ʼþ̽²éÆ÷µÄ·½°¸
ʹÓà SQL Server ProfilerµÄµÚÒ»²½ÊÇÈ·¶¨¼àÊÓ SQL Server ʵÀýµÄÔÒò¡£±¾Ö÷ÌâÌÖÂÛʹÓà SQL Server ProfilerÊÕ¼¯¸ú×ÙÐÅÏ¢µÄµäÐÍ·½°¸¡£
ʹÓà SQL Server ProfilerµÄµäÐÍ·½°¸°üÀ¨£º
²éÕÒÖ´ÐÐÇé¿ö×î²îµÄ²éѯ¡£
ÀýÈ磬¿ÉÒÔ´´½¨Ò»¸ö²¶»ñÓë TSQL ºÍ Stored Procedure ʼþÀࣨRPC:Completed ......
1.Êý¾Ý¿âµÄË÷Òý
¿ÉÒÔ½«Ë÷Òý¸ÅÄîÓ¦Óõ½Êý¾Ý¿â±íÉÏ¡£µ±Ò»¸ö±íº¬ÓдóÁ¿µÄ¼Ç¼ʱ£¬Oracle²éÕҸñíÖеÄÌØд¼Ç¼Ҫ»¨ºÜ³¤µÄʱ¼ä——¾ÍÏñ»¨ºÜ³¤Ê±¼ä·¿´È«ÊéÀ´²éÕÒij¸öÖ÷ÌâÒ»Ñù¡£OracleÓÐÒ»¸öÒ×ÓÚʹÓõŦÄÜ£¬¼´¿ÉÒÔ½¨Á¢Ò»¸ö´ÎÒþ²Ø±í£¬¸Ã±í°üº¬Ö÷±íÖеÄÒ»¸ö»ò¶à¸öÖØÒªµÄÁУ¬ÒÔ¼°ÔÚÖ÷± ......
1.ÔÚ±íÖ®¼ä´«ÊäÊý¾Ý
1£©ÀûÓÃINSERT´«ÊäÊý¾Ý
insert into test1 (select name2,age2 from test2);
´ÓÉÏÃæµÄ²Ù×÷¿ÉÒÔ¿´³ö£¬¿Éͨ¹ýSELECTÏòÒ»¸ö±íÖгÉÅúµØÌí¼ÓÊý¾Ý£¬µ«Ó¦×¢Ò⣺Êý¾ÝÀàÐÍÒªÒ»Ö£¬ËùÑ¡ÔñµÄÁÐÊýÓ¦Ò»Ö¡£´ËÓï¾äµÄÓï·¨¸ñʽÈçÏ£º
INSERT INTO table_name (
SELECT statement
) ;
2£©»ùÓÚÒÑÓÐµÄ±í½¨Á¢Ð ......