Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

¶¯Ì¬SQLÓ뾲̬SQL

    Ëùν¾²Ì¬SQLÖ¸ÔÚPL/SQL¿éÖÐʹÓõÄSQLÓï¾äÔÚ±àÒëʱÊÇÃ÷È·µÄ£¬Ö´ÐеÄÊÇÈ·¶¨¶ÔÏó¡£¶ø¶¯Ì¬SQLÊÇÖ¸ÔÚPL/SQL¿é±àÒëʱSQLÓï¾äÊDz»È·¶¨µÄ£¬Èç¸ù¾ÝÓû§ÊäÈëµÄ²ÎÊýµÄ²»Í¬¶øÖ´Ðв»Í¬µÄ²Ù×÷¡£±àÒë³ÌÐò¶Ô¶¯Ì¬Óï¾ä²¿·Ö²»½øÐд¦Àí£¬Ö»ÊÇÔÚ³ÌÐòÔËÐÐʱ¶¯Ì¬µØ´´½¨Óï¾ä¡¢¶ÔÓï¾ä½øÐÐÓï·¨·ÖÎö²¢Ö´ÐиÃÓï¾ä¡£
OracleÖж¯Ì¬SQL¿ÉÒÔͨ¹ý±¾µØ¶¯Ì¬SQLÀ´Ö´ÐУ¬Ò²¿ÉÒÔͨ¹ýDBMS_SQL°üÀ´Ö´ÐС£ÏÂÃæ¾ÍÕâÁ½ÖÖÇé¿ö·Ö±ð½øÐÐ˵Ã÷£º
¡¡¡¡Ò»¡¢±¾µØ¶¯Ì¬SQL
¡¡¡¡±¾µØ¶¯Ì¬SQLÊÇʹÓÃEXECUTE IMMEDIATEÓï¾äÀ´ÊµÏֵġ£
¡¡¡¡1¡¢±¾µØ¶¯Ì¬SQLÖ´ÐÐDDLÓï¾ä£º
¡¡¡¡ÐèÇ󣺸ù¾ÝÓû§ÊäÈëµÄ±íÃû¼°×Ö¶ÎÃûµÈ²ÎÊý¶¯Ì¬½¨±í¡£
create or replace procedure proc_test
(
table_name in varchar2, --±íÃû
field1 in varchar2, --×Ö¶ÎÃû
datatype1 in varchar2, --×Ö¶ÎÀàÐÍ
field2 in varchar2, --×Ö¶ÎÃû
datatype2 in varchar2 --×Ö¶ÎÀàÐÍ
) as
str_sql varchar2(500);
begin
str_sql:=’create table ’||table_name||’(’||field1||’ ’||datatype1||’,’||field2||’ ’||datatype2||’)’;
execute immediate str_sql; --¶¯Ì¬Ö´ÐÐDDLÓï¾ä
exception
when others then
null;
end ;
¡¡¡¡ÒÔÉÏÊDZàÒëͨ¹ýµÄ´æ´¢¹ý³Ì´úÂë¡£ÏÂÃæÖ´Ðд洢¹ý³Ì¶¯Ì¬½¨±í¡£
SQL> execute proc_test(’dinya_test’,’id’,’number(8) not null’,’name’,’varchar2(100)’); http://www.mscto.com
PL/SQL procedure successfully completed
SQL> desc dinya_test;
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
ID NUMBER(8)
NAME VARCHAR2(100) Y
SQL>
¡¡¡¡µ½ÕâÀ¾ÍʵÏÖÁËÎÒÃǵÄÐèÇó£¬Ê¹Óñ¾µØ¶¯Ì¬SQL¸ù¾ÝÓû§ÊäÈëµÄ±íÃû¼°×Ö¶ÎÃû¡¢×Ö¶ÎÀàÐ͵ȲÎÊýÀ´ÊµÏÖ¶¯Ì¬Ö´ÐÐDDLÓï¾ä¡£
¡¡¡¡2¡¢±¾µØ¶¯Ì¬SQLÖ´ÐÐDMLÓï¾ä¡£
¡¡¡¡ÐèÇ󣺽«Óû§ÊäÈëµÄÖµ²åÈëµ½ÉÏÀýÖн¨ºÃµÄdinya_test±íÖС£
create or replace procedure proc_insert
(
id in number, --ÊäÈëÐòºÅ
name in varchar2 --ÊäÈëÐÕÃû
) as
str_sql varchar2(500);
begin
str_sql:=’insert into dinya_test values(:1,:2)’;
execute immediate str_sql using id,name; --¶¯Ì¬Ö´ÐвåÈë²Ù×÷
exception
when others then
null;
end ;
¡¡¡¡Ö´Ðдæ


Ïà¹ØÎĵµ£º

sqlµÄ INNER JOIN, left join,right joinÓï·¨

inner join(µÈÖµÁ¬½Ó) Ö»·µ»ØÁ½¸ö±íÖÐÁª½á×Ö¶ÎÏàµÈµÄÐÐ
left join(×óÁª½Ó) ·µ»Ø°üÀ¨×ó±íÖеÄËùÓмǼºÍÓÒ±íÖÐÁª½á×Ö¶ÎÏàµÈµÄ¼Ç¼
right join(ÓÒÁª½Ó) ·µ»Ø°üÀ¨ÓÒ±íÖеÄËùÓмǼºÍ×ó±íÖÐÁª½á×Ö¶ÎÏàµÈµÄ¼Ç¼
INNER JOIN Óï·¨£º
INNER JOIN Á¬½ÓÁ½¸öÊý¾Ý±íµÄÓ÷¨£º
SELECT * from ±í1 INNER JOIN ±í2 ON ±í1.×ֶκÅ=±í2 ......

SQLÖÐJOINºÍUNIONÇø±ð¡¢Ó÷¨¼°Ê¾Àý


1.JOINºÍUNIONÇø±ð
join ÊÇÁ½Õűí×ö½»Á¬ºóÀïÃæÌõ¼þÏàͬµÄ²¿·Ö¼Ç¼²úÉúÒ»¸ö¼Ç¼¼¯£¬
unionÊDzúÉúµÄÁ½¸ö¼Ç¼¼¯(×Ö¶ÎÒªÒ»ÑùµÄ)²¢ÔÚÒ»Æð£¬³ÉΪһ¸öеļǼ¼¯ ¡£
JOINÓÃÓÚ°´ÕÕONÌõ¼þÁª½ÓÁ½¸ö±í£¬Ö÷ÒªÓÐËÄÖÖ£º
INNER JOIN£ºÄÚ²¿Áª½ÓÁ½¸ö±íÖеļǼ£¬½öµ±ÖÁÉÙÓÐÒ»¸öͬÊôÓÚÁ½±íµÄÐзûºÏÁª½ÓÌõ¼þʱ£¬ÄÚÁª½Ó²Å·µ»ØÐС£ÎÒÀí½âµÄ ......

¾­µäSQLÓï¾ä´óÈ«

¾­µäSQLÓï¾ä´óÈ«
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
¡¡¡¡SQL·ÖÀࣺ
¡¡¡¡DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
¡¡¡¡DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
¡¡¡¡DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
¡¡¡¡Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
¡¡¡¡1¡¢Ë ......

case when then...else end sql Óï¾äÓ÷¨

select f.AGENTID as 'ID',
          f.AGENTNAME as 'ÐÕÃû',
          f.COMNAME as '¹«Ë¾¼ò³Æ',
          c.REGISTDATE as '×¢²áʱ¼ä',
     ......

ʹÓà SQL Server ʱÐèÒª¾­³£Óõ½µÄ¼¸¸öÉèÖÃÑ¡Ïî


1. SET DEADLOCK_PRIORITY
˵Ã÷£º¿ØÖÆÔÚ·¢ÉúËÀËøÇé¿öʱ»á»°µÄ·´Ó¦·½Ê½¡£Èç¹ûÁ½¸ö½ø³Ì¶¼Ëø¶¨Êý¾Ý£¬²¢ÇÒÖ±µ½ÆäËü½ø³ÌÊÍ·Å×Ô¼ºµÄËøÊ±£¬Ã¿¸ö½ø³Ì²ÅÄÜÊÍ·Å×Ô¼ºµÄËø£¬¼´·¢ÉúËÀËøÇé¿ö¡£
Óï·¨£ºSET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var }
²ÎÊý£ºLOW  Ö¸¶¨µ±Ç°»á»°ÎªÊ×Ñ¡ËÀËøÎþÉüÆ·¡£Microsoft® S ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ