DB2 ´¿SQL´æ´¢¹ý³ÌÓ뺯ÊýµÄһЩÏÞÖÆ
*¸ø¶à¸ö±äÁ¿¸³Öµ
functionÖ»ÄÜʹÓÃ
set (a,b,c)=(select a,b,c from #);
procedureÖ»ÄÜʹÓÃ
select a,b,c into a,b,c from #
*functionÎÞ·¨Ç¶Ì×µ÷ÓôøÓÐinout»òout²ÎÊýµÄprocedure£¬ÏÖÏóΪ£ºÎÞ·¨´´½¨¡£
½ñÌìµÄ½øÕ¹½áÂÛÊÇ£º
function¿ÉÒÔµ÷ÓÃÈκÎprocedure£¬µ«±ØÐëÉùÃ÷Ϊmodifies sql data£¬Í¬Ê±±ØÐë·µ»ØtableÀàÐÍ£¬²»ÄÜʹµ¥Öµ»òrowÀàÐÍ
*procedure¿ÉÒÔǶÌ×µ÷Óã¬ÄÄÅÂÊÇ´øinout²ÎÊýµÄ´æ´¢¹ý³Ì.
*function ¿ÉÒÔǶÌ×µ÷ÓÃ.
*IBM¿ª·¢ÕßÍøÕ¾Ò»ÆªÎÄÕÂ˵£ºfunctionµÄЧÂʱÈprocedure¸ß¡£
ÎҵĴð°¸ÊÇÏà·´¡£
ͬÑùµÄÓï¾ä£¬functionºÍprocedureµÄÊ״ε÷ÓÃʱ¼äÓÐ10±¶×óÓҵIJî¾à¡£
¾¹ý²âÊÔÓÐÈçϽáÂÛ£º
1.Èç¹û²ÎÊýÍêȫһÑù£¬Á½´Îµ÷ÓÃͬÑùµÄfunction£¬Ê״ε÷ÓÃËٶȺÜÂý£¨2Ãë×óÓÒ£©£¬µ«µÚ¶þ´Î¿ªÊ¼¾ÍºÜ¿ì£¨0.2Ãë×óÓÒ£©£¬µ«ºóÀ´ºÜ¿ìµÄµ÷ÓÃÓ¦¸ÃÊDzÉÓÃÁË»º´æ»úÖÆ£¬¶øÕâ¸ö»º´æµÄÉú´æÆÚδ֪¡£
2.ͬÑùµÄº¯Êý£¬Ö»Òª²ÎÊý·¢ÉúÈκα仯£¬ÄÇôÊ״λ¹ÊÇ»áºÜÂý¡£
3.procedureºÜÎȶ¨£¬µÚÒ»´ÎºÍºóÃæµÄµ÷Óòî±ð²»´ó¡£²¢ÇÒ¶¼ÊÇ0.2Ãë¼¶µÄ¡£
¶øÇÒ×îÈÃÈËÄÉÃÆµÄÊÇÄÄÅ´úÂë½öÊÇһЩ±È½ÏÅжϣ¬¸ù±¾²»Éæ¼°Êý¾Ý£¬functionµÄЧÂÊÒ²²»¸Ò¹§Î¬¡£
ËùÒÔÈç¹ûÊÇʹÓÃÆµÂÊÏ൱¸ßµÄ´úÂ룬ÎÞÂÛÈçºÎ£¬Ïë·½Éè·¨¶¼µÃ¸Ä³Éprocedure¡£
*procedureÎÞ·¨½øÐж¯Ì¬out²ÎÊýprocedureµ÷Óá£
ʲôÒâË¼ÄØ£¬¾ÍÊÇÒªµ÷ÓõÄprocedure²»ÊÇÓ²±àÂëµÄ
--ÕâÊÇûÓÐÎÊÌâµÄ
create procedure out_proc
call in_proc;
end
--ÕâҲûÓÐÎÊÌâµÄ
create procedure out_proc
...
statment='call in_proc()';
prepare s1 from statment;
execute s1;
end
--¿ÉÒÔ´´½¨£¬µ«ÔËÐÐʱ»á±¨´í
create procedure out_proc
...
declare out_param int;
statment='call in_proc('||cast(out_param as char(10))||')';
prepare statment;
execute statment;
end
ÕâЩÏÞÖÆÍêȫûÓÐÈκεÄÎĵµ¿ÉÒÔ²éѯµ½£¬ÊÇʵ¼Ê²âÊÔºóµÃ³öµÄ½áÂÛ£¬ÌؼÇÓÚ´Ë£¬ÃâµÃÒÔºóÓÖ×ßÍä·
ĿǰÒѾдºÃÁ˵÷ÓÃprocedureµÄfunction£¬ÐÔÄÜȷʵҪǿ¹ýÖ±½Óµ÷ÓÃfunction¡£µ«Åöµ½ÁËÐÂÎÊÌ⣺
*µ÷ÓÃÁËprocedureµÄfunction²»ÄÜÏóÒ»°ãfunctionÒ»ÑùÔÚselectÓï¾äÖвéѯ£¬±ÈÈç
func1 returns table()
func2 returns table() --ÆäÖе÷ÓÃÁËprocedure
ÄÇô
--Ö±½Ó»ñÈ¡func1,Õý³£
select * from table(func1) as tmp
--Ö±½Ó»ñÈ¡func2£¬Õý³£
select * from t
Ïà¹ØÎĵµ£º
sql º¯Êý×ܽá
³£ÓõÄ×Ö·û´®º¯ÊýÓУº
Ò»¡¢×Ö·ûת»»º¯Êý
1¡¢ASCII()
·µ»Ø×Ö·û±í´ïʽ×î×ó¶Ë×Ö·ûµÄASCII ÂëÖµ¡£ÔÚASCII£¨£©º¯ÊýÖУ¬´¿Êý×ÖµÄ×Ö·û´®¿É²»ÓÑ’À¨ÆðÀ´£¬µ«º¬ÆäËü×Ö·ûµÄ×Ö·û´®±ØÐëÓÑ’À¨ÆðÀ´Ê¹Ó㬷ñÔò»á³ö´í¡£
2¡¢CHAR()
½«ASCII Âëת»»Îª×Ö·û¡£Èç¹ûûÓÐÊäÈë0 ~ 255 Ö®¼äµÄASCII ......
¹«Ë¾×éÖ¯SQLˮƽ¿¼ÊÔ£¬¿´ÁËд½Ì²Ä£¬Ð´Á˵ã×ܽᷢÉÏÀ´¸ú´ó¼Ò·ÖÏí¡£
ÎÒ¹«Ë¾Ê¹ÓõÄÊÇSybase ASE12.5£¬ËùÒÔÏÂÃæµÄÒ»Ð©ÌØÐÔÊÇÕë¶ÔSybase ASEµÄ¡£
Ò»£ºSQL Bisic
1£ºSQL(Structured Quary Language)ÌØÐÔ£º
a£º±ê×¼»¯
b£º·Ç¹ý³Ì»¯µÄ
c£º¿ÉÓÅ»¯µÄ
d£ºÃæÏò¼¯ºÏ²Ù×÷µÄ
2£ºASEÖеÄÊý¾ÝÀàÐÍ
a£ºNumberic
b£ºCharacter
c£ºDat ......
Ò»¡¢×Ö·ûת»»º¯Êý
1¡¢ASCII()
·µ»Ø×Ö·û±í´ïʽ×î×ó¶Ë×Ö·ûµÄASCII ÂëÖµ¡£ÔÚASCII£¨£©º¯ÊýÖУ¬´¿Êý×ÖµÄ×Ö·û´®¿É²»ÓÑ’À¨ÆðÀ´£¬µ«º¬ÆäËü×Ö·ûµÄ×Ö·û´®±ØÐëÓÑ’À¨ÆðÀ´Ê¹Ó㬷ñÔò»á³ö´í¡£
2¡¢CHAR()
½«ASCII Âëת»»Îª×Ö·û¡£Èç¹ûûÓÐÊäÈë0 ~ 255 Ö®¼äµÄASCII ÂëÖµ£¬CHAR£¨£© ·µ»ØNULL ¡£
3¡¢LOWE ......
ÔÎĵØÖ·£ºhttp://www.dingos.cn/index.php?topic=1688.0
OracleÓÐrownumÓÃÓÚ·ÃÎʱíÖÐÐкš£ÄÇôÔÚSQL ServerÖÐÊÇ·ñÓеÈЧµÄÄØ£¿»òÕßÔÚSQL ServerÖÐÈçºÎÊä³öÐкţ¿
-----------------------------------
ÔÚSQL ServerÖÐûÓÐÖ±½ÓµÈЧÓÚOracleµÄrownum
-----------------------------------
Ñϸñ˵À´£¬ÔÚ¹ØÏµÊý¾Ý¿âÖУ¬± ......
ÈçºÎÉú³Éexplain plan?
¡¡¡¡½â´ð:ÔËÐÐutlxplan.sql. ½¨Á¢plan ±í
¡¡¡¡Õë¶ÔÌØ¶¨SQLÓï¾ä£¬Ê¹Óà explain plan set statement_id = 'tst1' into plan_table
¡¡¡¡ÔËÐÐutlxplp.sql »ò utlxpls.sql²ì¿´explain plan
EXPLAIN PLAN ÊÇÒ»¸öºÜºÃµÄ·ÖÎöSQLÓï¾äµÄ¹¤¾ß,ËüÉõÖÁ¿ÉÒÔÔÚ²»Ö´ÐÐSQLµÄÇé¿öÏ·ÖÎöÓï¾ä. ͨ¹ý·ÖÎö,ÎÒÃǾͿÉÒÔÖ ......