ÈçºÎÖ´ÐÐÓÃ×Ö·û´®×é³ÉµÄSQLÓï¾ä
ÔÚ´æ´¢¹ý³ÌÖо³£ÐèÒªÖ´ÐгÌÐò×é³ÉµÄSQLÓï¾ä£¬¿ÉÒÔʹÓÃexec(@sql),
Ò²¿ÉÒÔʹÓÃexec sp_executesql @sql¡£µ«Ê¹ÓÃsp_executesqlÒªÓÅÓÚexec£¬½¨ÒéʹÓà sp_executesql ¶ø²»ÒªÊ¹Óà EXECUTE Óï¾äÖ´ÐÐ×Ö·û´®¡£Ö§³Ö²ÎÊýÌæ»»²»½öʹ sp_executesql ±È EXECUTE ¸üͨÓ㬶øÇÒ »¹Ê¹ sp_executesql ¸üÓÐЧ£¬ÒòΪËüÉú³ÉµÄÖ´Ðмƻ®¸üÓпÉÄܱ» SQL Server ÖØÐÂʹÓá£
µ«Èç¹ûʹÓÃsp_executesql £¬È«¾Ö±äÁ¿±ÈÈç@@rowcount½«Ã»ÓÐÕýÈ·Öµ£¬¶øexecÓУ¬Èç¹ûÏë·µ»Ø¸ÃSQLÖÐijһ×ֶεÄÖµ¿ÉʹÓÃËûµÄ²ÎÊý£¬ÀýÈ磺
@sql='select @id=id from table'
exec sp_executesql @sql N'@id int out ',@id out
print @id
Ïà¹ØÎĵµ£º
SQL> setsqlprompt"_user _privilege> " ͨ¹ýÒÔÉÏÉèÖã¬ÄãµÄsqlplusÌáʾ±êʶ¾Í³ÉÁËÒÔϸñʽÁË£º SYS AS SYSDBA> Õâ¾Í±íÃ÷µ±Ç°µÄµÇ¼ÕʺÅÊÇSYS£¬Éí·ÝÊÇSYSDBA¡£Çë×¢ÒâÔÚÉÏÃæÓï¾äÖÐʹÓÃÁËÁ½¸öÌØÊâµÄÔ¤¶¨ÒåµÀµÂ±äÁ¿£º_userºÍ_privilege£¬·Ö±ð¶¨ÒåÁ˵±Ç°Óû§ºÍÕâ¸öÓû§µÄµÇ¼Éí·Ý£¨È¨ÏÞ£©¡£ÏÖÔÚÎÒÃÇÔÙÔö¼ ......
Student(S#,Sname,Sage,Ssex) ѧÉú±í
Course(C#,Cname,T#) ¿Î³Ì±í
SC(S#,C#,score) ³É¼¨±í
Teacher(T#,Tname) ½Ìʦ±í
ÎÊÌ⣺
1¡¢²éѯ“001”¿Î³Ì±È“002”¿Î³Ì³É¼¨¸ßµÄËùÓÐѧÉúµÄѧºÅ£»
select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
fr ......
1 --´ÓTable ±íÖÐÈ¡³öµÚ m Ìõµ½µÚ n ÌõµÄ¼Ç¼£º(Not In °æ±¾)
2
3 SELECT TOP n-m+1 *
4 from Table
5 WHERE (id NOT IN (SELECT TOP m-1 id from Table ))
6
7
8 --´ÓTABLE±íÖÐÈ¡³öµÚmµ½nÌõ¼Ç¼ (Exists°æ±¾)
9
10 SELECT TOP n-m+1 * from ......
SELECT --´ÓÊý¾Ý¿â±íÖмìË÷Êý¾ÝÐкÍÁÐ
INSERT --ÏòÊý¾Ý¿â±íÌí¼ÓÐÂÊý¾ÝÐÐ
DELETE --´ÓÊý¾Ý¿â±íÖÐɾ³ýÊý¾ÝÐÐ
UPDATE --¸üÐÂÊý¾Ý¿â±íÖеÄÊý¾Ý
--SQLÊý¾Ý¶¨Òå
CREATE TABLE --´´½¨Ò»¸öÊý¾Ý¿â±í
DROP TABLE --´ÓÊý¾Ý¿âÖÐɾ³ý±í
ALTER TABLE --ÐÞ¸ÄÊý¾Ý¿â±í½á¹¹
CREATE VIEW --´´½¨Ò»¸öÊÓͼ
DROP VIEW --´ÓÊý¾Ý¿âÖÐɾ³ýÊ ......
--ÐÐÁÐת»» ÐÐתÁÐ
DROP TABLE t_change_lc;
CREATE TABLE t_change_lc (card_code VARCHAR2(3), q NUMBER, bal NUMBER);
INSERT INTO t_change_lc
SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal from dual CONNECT BY ROWNUM <= 4
UNION
SELECT '002' card_code, ROWNUM q, trunc(d ......