oracle ´æ´¢¹ý³Ì¼òÊö
´æ´¢¹ý³Ì¾ÍÊÇ×÷Ϊ¿ÉÖ´ÐжÔÏó´æ·ÅÔÚÊý¾Ý¿âÖеÄÒ»¸ö»ò¶à¸öSQLÃüÁî¡£
¶¨Òå×ÜÊǺܳéÏó¡£´æ´¢¹ý³ÌÆäʵ¾ÍÊÇÄÜÍê³ÉÒ»¶¨²Ù×÷µÄÒ»×éSQLÓï¾ä£¬Ö»²»¹ýÕâ×éÓï¾äÊÇ·ÅÔÚÊý¾Ý¿âÖеÄ(ÕâÀïÎÒÃÇ̸ֻSQL Server)¡£Èç¹ûÎÒÃÇͨ¹ý´´
½¨´æ´¢¹ý³ÌÒÔ¼°ÔÚASPÖе÷Óô洢¹ý³Ì£¬¾Í¿ÉÒÔ±ÜÃ⽫SQLÓï¾äͬASP´úÂë»ìÔÓÔÚÒ»Æð¡£ÕâÑù×öµÄºÃ´¦ÖÁÉÙÓÐÈý¸ö£º Íø¹ÜÍøbitsCN.com
* µÚÒ»¡¢´ó´óÌá¸ßЧÂÊ¡£´æ´¢¹ý³Ì±¾ÉíµÄÖ´ÐÐËٶȷdz£¿ì£¬¶øÇÒ£¬µ÷Óô洢¹ý³Ì¿ÉÒÔ´ó´ó¼õÉÙͬÊý¾Ý¿âµÄ½»»¥´ÎÊý¡£
* µÚ¶þ¡¢Ìá¸ß°²È«ÐÔ¡£¼ÙÈ罫SQLÓï¾ä»ìºÏÔÚASP´úÂëÖУ¬Ò»µ©´úÂëʧÃÜ£¬Í¬Ê±Ò²¾ÍÒâζ×Å¿â½á¹¹Ê§ÃÜ¡£
* µÚÈý¡¢ÓÐÀûÓÚSQLÓï¾äµÄÖØÓá£
oracle ´æ´¢¹ý³ÌµÄ»ù±¾Óï·¨
1.»ù±¾½á¹¹
CREATE OR REPLACE PROCEDURE ´æ´¢¹ý³ÌÃû×Ö
(
²ÎÊý1 IN NUMBER,
²ÎÊý2 IN NUMBER
) IS
±äÁ¿1 INTEGER :=0;
±äÁ¿2 DATE;
BEGIN
54com.cn
END ´æ´¢¹ý³ÌÃû×Ö
2.SELECT INTO STATEMENT
½«select²éѯµÄ½á¹û´æÈëµ½±äÁ¿ÖУ¬¿ÉÒÔͬʱ½«¶à¸öÁд洢¶à¸ö±äÁ¿ÖУ¬±ØÐëÓÐÒ»Ìõ
¼Ç¼£¬·ñÔòÅ׳öÒì³£(Èç¹ûûÓмǼÅ׳öNO_DATA_FOUND)
Àý×Ó£º
BEGIN
SELECT col1,col2 into ±äÁ¿1,±äÁ¿2 from typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...
3.IF ÅжÏ
IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;
4.while Ñ»·
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;
5.±äÁ¿¸³Öµ
V_TEST := 123;
6.ÓÃfor in ʹÓÃcursor
...
IS
CURSOR cur IS SELECT * from xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.ÁÐÃû1+cur_result.ÁÐÃû2
END;
END LOOP;
END;
7.´ø²ÎÊýµÄcursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME from USER WHERE TYPEID=C_ID;
OPEN C_USER(±äÁ¿Öµ);
LOOP 54ne.com
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;
8.ÓÃpl/sql developer debug
Á¬½ÓÊý¾Ý¿âºó½¨Á¢Ò»¸öTest WINDOW
ÔÚ´°¿ÚÊäÈëµ÷ÓÃSPµÄ´úÂë,F9¿ªÊ¼debug,CTRL+Nµ¥²½µ÷ÊÔ
¹ØÓÚoracle´æ´¢¹ý³ÌµÄ
Ïà¹ØÎĵµ£º
¡¶oracle,db2,mysqlÀà±È¡·×÷ΪÈýÖÖÊý¾Ý¿âÒ»¸öÀà±È£¬Ä¿µÄÔÚÓÚͨ¹ýÀà±È£¬Á˽âÏÖÔÚÊý¾Ý¿âÏàËÆµãÓëÒìͬµã£¬Í¬Ê±¿ÉÒÔ°ïÖú´ó¼ÒÔÚÁ˽âÒ»ÖÖÊý¾Ý¿â£¬Äܹ»Ñ¸ËÙµØÑ§»áÆäËûÊý¾Ý¿â¡£³õ²½¶¨ÒåΪoracle,db2,mysqlÈýÖÖÊý¾Ý¿â£¬ÒÔºó¿ÉÄÜ»¹ÊÇ×·¼Óinformix,sysbase,sql serverµÈÊý¾Ý¿â¡£
±¾Æª×÷ΪÊׯª£¬Ä¿µÄÊÇÈôó¼Ò¶ÔÕâÈýÖÖÊý¾Ý¿â³£ÓõÄÖªÊ ......
ORACLEµ¼Èëµ¼³ö
¹¦ÄÜ£ºOracleÊý¾Ýµ¼Èëµ¼³öimp/exp¾ÍÏ൱ÓëoracleÊý¾Ý»¹ÔÓ뱸·Ý¡£´ó¶àÇé¿ö¶¼¿ÉÒÔÓÃOracleÊý¾Ýµ¼Èëµ¼³öÍê³ÉÊý¾ÝµÄ±¸·ÝºÍ»¹Ô£¨²»»áÔì³ÉÊý¾ÝµÄ¶ªÊ§£©¡£OracleÓиöºÃ´¦£¬ËäÈ»ÄãµÄµçÄÔ²»ÊÇ·þÎñÆ÷£¬µ«ÊÇÄã×°ÁËoracle¿Í»§¶Ë£¬²¢½¨Á¢ÁËÁ¬½Ó£¨Í¨¹ýnet8 assistantÖб¾µØ-->·þÎñÃüÃû Ìí¼ÓÕýÈ·µÄ·þÎñÃüÃû ÆäʵÄã¿É ......
ת£ºOracle All And Any
ÕâÊÇÔÚOracleÖбȽÏÈÝÒ×±»ºöÊÓµÄÁ½¸öÌõ¼þº¯Êý£¬µ«ÕâÁ½¸öº¯ÊýÆäʵ¶Ô¼ò»¯SqlÓï¾äÊǷdz£ÖØÒªµÄ×÷Óõġ£
¡¡¡¡ÀýÈç
scott@eddev> select ename, sal from emp where sal > any (1600, 2999);
ENAME &nb ......
Oracle lpad º¯Êý£º
°æÈ¨ÉùÃ÷£º×ªÔØÊ±ÇëÒÔ³¬Á´½ÓÐÎʽ±êÃ÷ÎÄÕÂÔʼ³ö´¦ºÍ×÷ÕßÐÅÏ¢¼°±¾ÉùÃ÷
http://soysky.blogbus.com/logs/43623642.html
Oracle lpadº¯Êý½«×ó±ßµÄ×Ö·û´®Ìî³äÒ»Ð©ÌØ¶¨µÄ×Ö·û£¬ÆäÓï·¨¸ñʽÈçÏ£º
lpad( string1, padded_length [, pad_string ] )
ÆäÖУº
string1ÊÇÐèÒªÕ³Ìù×Ö·ûµÄ×Ö·û´®£»
padde ......
´´½¨OracleÊý¾Ý¿â£¨ÒÔOracle10gΪÀý£©
ÓÐÁ½ÖÖ´´½¨Êý¾Ý¿âµÄ·½Ê½£¬Ò»ÖÖÊÇÒÔÃüÁîÐнű¾·½Ê½£¬¼´ÊÖ¶¯·½Ê½´´½¨£»ÁíÒ»ÖÖÊÇÀûÓÃOracleÌṩµÄÊý¾Ý¿âÅäÖÃÏòµ¼À´´´½¨¡£±¾ÆªÖ÷Òª½éÉÜÔÚUnixºÍWindowsÏÂÒÔÃüÁîÐнű¾·½Ê½´´½¨OracleÊý¾Ý¿â¡£
Ò»¸öÍêÕûµÄÊý¾Ý¿âϵͳ£¬Ó¦°üÀ¨Ò»¸öÎïÀí½á¹¹¡¢Ò»¸öÂß¼½á¹¹¡¢Ò»¸öÄÚ´ ......