¶¯Ì¬SQL(Dynamic SQL)
----start
¶¯Ì¬SQLÊÇÔÚ³ÌÐòÔËÐÐʱ¹¹ÔìµÄ£¬ÒªÖ´Ðе¥ÌõSQL£¬Ê¹ÓÃEXECUTE IMMEDATE Óï¾ä£»µ±ÅúÁ¿Ö´ÐÐSQLʱ£¬ÏÈʹÓÃPREPARE Óï¾ä¹¹ÔìSQL£¬È»ºóʹÓÃEXECUTE Óï¾äÖ´ÐС£
Ò»£ºPrepareÓï¾ä£ºÓÃÀ´¹¹ÔìÅúÁ¿SQL
Óï·¨£º
PREPARE <sql-statement> [OUTPUT] INTO <result> [INPUT INTO] <input> from <variable>
¶þ£ºDESCRIBEÓï¾ä£º»ñÈ¡±í¡¢SQLµÈÊý¾Ý¿â¶ÔÏóµÄÃèÊöÐÅÏ¢
Óï·¨£º
>>-DESCRIBE----------------------------------------------------->
.-OUTPUT-.
>--+-+--------+--+-select-statement---------+--------------------------------+-><
| +-call-statement-----------+ |
| '-XQUERY--XQuery-statement-' |
'-+-TABLE----------------------------------+--table-name--+-------------+-'
+-+-----------------+--INDEXES FOR TABLE-+ '-SHOW DETAIL-'
| +-RELATIONAL DATA-+ |
| +-XML DATA--------+ |
| '-TEXT SEARCH-----' |
'-DATA PARTITIONS FOR TABLE--------------'
Èý£ºExecuteÓï¾ä£ºÓÃÀ´Ö´ÐÐÅúÁ¿SQL£¬²»ÄÜÖ´ÐÐselectÓï¾ä
Óï·¨£º
EXECUTE <statement-name> [INTO <result-variable>] [USING <input-variable> [,<input-variable>,...] ]
ËÄ£ºExecute ImmediateÓï¾ä£ºÓÃÀ´Ö´Ðе¥ÌõSQLÓï¾ä£¬²»ÄÜÖ´ÐÐselectÓï¾ä
Óï·¨£º
EXECUTE IMMEDIATE <sql-statement>
Î壺ʾÀý
--Á¬½ÓÊý¾Ý¿â
CONNECT TO SAMPLE!
--´´½¨±í
CREATE TABLE TESTTB(A INT, B INT)!
--ɾ³ý´æ´¢¹ý³Ì
DROP PROCEDURE TEST!
--´´½¨´æ´¢¹ý³Ì
CREATE PROCEDURE TEST (IN V1 INT, IN V2 INT)
BEGIN
DECLARE STMT VARCHAR(50);
DECLARE ST STATEMENT;
--¾²Ì¬SQL
INSERT INTO TESTTB VALUES (1,1);
--¶¯Ì¬SQL(µ¥ÌõÓï¾ä)
SET STMT='INSERT INTO TESTTB VALUES (' ||
Ïà¹ØÎĵµ£º
OracleÊý¾Ý¿â
SELECT
*
from
(
SELECT
ROWNUM AS NO_ROW
,row_.*
from
(
SELECT
&nb ......
1ÓÃÓÚÅÅÐòµÄº¯Êý
row_number()
rank()
dense_rank()
ntile(group_number)
ÏÂÃæÁоÙÕâ¸öº¯ÊýµÄÓ÷¨£º
row_number()º¯ÊýÒ»°ãÓÃÓÚ×éÄÚÅÅÐò£¬¶øÆäËûÈý¸öº¯ÊýÊǶԽá¹û¼¯ÅÅÐò
Àý×Ó£º·ÖÒ³ÅÅÐò
<!--×¢ÒâÈ«¾Ö±äÁ¿Ò²ÔÚÕâÀïÉùÃ÷£¬²¢ÓöººÅ¸ô¿ª-->
create proc MyDividePageSort @iRowCount int ,@iPageNo int
AS
< ......
¹ØÓÚÁ½±í¹ØÁªµÄupdate£¬µ«Óï¾äÔõôд¶¼²»ÕýÈ·£¬ÀÏÊDZ¨´í£¬ÓÚÊÇÐľªÈâÌø£¨¾ÍŲ»Äܼ°Ê±Íê³É²Ù×÷£©È¥²éÁËһϣ¬NND£¬ÔÀ´°ÑSQLд³ÉÁËÔÚSQL ServerÏÂÃæµÄÌØÓÐÐÎʽ£¬ÕâÖÖÓï·¨ÔÚOracleÏÂÃæÊÇÐв»Í¨µÄ£¬¼±Ã¦¸Ä»ØÀ´£¬¼°Ê±Íê³ÉÁËÈÎÎñ¡£Ë³±ãÒ²°Ñ²éµ½µÄSQLÌû³öÀ´£¬ÄÄÌìÔÙÍü¼ÇÁË£¬Ò²ºÃÔÚÕâÀïÕÒ»ØÀ´£º
update customers a ......
Sqlʱ¼äº¯Êý
Ò»¡¢sql serverÈÕÆÚʱ¼äº¯Êý
Sql ServerÖеÄÈÕÆÚÓëʱ¼äº¯Êý
1. µ±Ç°ÏµÍ³ÈÕÆÚ¡¢Ê±¼ä
select getdate()
2. dateadd ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼äµÄ»ù´¡ÉÏ£¬·µ»ØÐ嵀 datetime Öµ
......
1¡¢²éѯ±íÖÐÖظ´Êý¾Ý¡£select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2¡¢É¾³ý±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ ......