oracle ´æ´¢¹ý³ÌµÄ»ù±¾Óï·¨
1.»ù±¾½á¹¹
CREATE OR REPLACE PROCEDURE ´æ´¢¹ý³ÌÃû×Ö
(
²ÎÊý1 IN NUMBER,
²ÎÊý2 IN NUMBER
) IS
±äÁ¿1 INTEGER :=0;
±äÁ¿2 DATE;
BEGIN
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
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µ¥²½µ÷ÊÔ
Ïà¹ØÎĵµ£º
ƽʱ½Ó´¥mysql½Ï¶à£¬ÒòΪmysqlÔÚÒ»°ãµÄСÏîÄ¿Öй»ÓÃÁË ¿ªÔ´¶øÇÒÃâ·Ñ¡£
ºÜ¶à¹«Ë¾ÐèÒªoracleά»¤µÄ£¬¿ª·¢oracleÉõÖÁÊǶþ´Î¿ª·¢ÔÚ¹úÄÚÒ²±È½ÏÉٵģ¬Óм׹ÇÎĹ«Ë¾£¬Ë¸ÒºÍËûÃÇÇÀ·¹Íë°¡ ¹þ¹þ ²»¹ýÈ˼ҵÄÊÕ·ÑÂù¹óµÄ£¬ÄãÈç¹ûÓÐˮƽ£¬³Ôµã²Ð¸þµÄ»ú»áÂù¶à¡£ oracleµÄÅàѵÐÅÏ¢±í´ïÁËÊг¡ÐèÇó¡£
£¨ÎÒÔÚѧУ»·¾³µÄ²ÂÏ룬רҵÈËÊ¿À´ÅÄש ......
ÊÕ²Ø×ÔijЩ¸ßÊÖµÄ×ܽᣬ²»¸Ò¶ÀÏí£¬ÓëÓÐÔµÀ´´ËÕß¹²Ïí¡£
-----------------------------------------------------------------------------------
Oracle×Ö·û¼¯ÎÊÌâ×ܽá
¾³£ÓÐͬÊÂ×ÉѯoracleÊý¾Ý¿â×Ö·û¼¯Ïà¹ØµÄÎÊÌ⣬ÈçÔÚ²»Í¬Êý¾Ý¿â×öÊý¾ÝǨÒÆ¡¢Í¬ÆäËüϵͳ½»»»Êý¾ÝµÈ£¬³£³£ÒòΪ×Ö·û¼¯²»Í¬¶øµ¼ÖÂǨÒÆʧ°Ü»òÊý¾Ý¿âÄÚÊý¾Ý±ä³ ......
Ò»¸öÔµĵÚÒ»Ìì
´úÂë (Ë«»÷´úÂ븴ÖƵ½Õ³Ìù°å)
SELECT to_date(to_char(SYSDATE,'yyyy-mm')||'-01','yyyy-mm-dd')
from dual
sysdate ΪÊý¾Ý¿â·þÎñÆ÷µÄµ±Ç°ÏµÍ³Ê±¼ä¡£
to_char Êǽ«ÈÕÆÚÐÍתΪ×Ö·ûÐ͵ĺ¯Êý¡£
to_date Êǽ«×Ö·ûÐÍתΪÈÕÆÚÐ͵ĺ¯Êý£¬Ò»°ãʹÓà yyyy-mm-dd hh24:mi:ss¸ñʽ£¬µ±Ã»ÓÐÖ¸¶¨Ê±¼ä²¿·Öʱ£¬ÔòÄ ......
Oracle 10gÖеÄEM²ÉÓÃWebµÄÐÎʽÀ´¹ÜÀíÊý¾Ý¿â¼°Ïà¹Ø·þÎñ£¬µ«Ê¹Óùý³ÌÖлáÅöµ½Ò»Ð©¸úEMÓйصÄÎÊÌ⣬¸ù¾ÝͬÐеÄÌáʾ¼°±ÊÕßµÄʵ¼Ê¾Ñ飬½øÐÐÁËһЩ¹éÄÉ£¬Ï£Íû¶ÔÏà¹Ø°®ºÃÕßÓÐËù°ïÖú£º
£¨1£©Êý¾Ý¿â¿ØÖÆ̨Æô¶¯ºó£¬¿´µ½1£©Êý¾Ý¿âʵÀýÎÞÐÅÏ¢2£©¼àÌý³ÌÐòΪ״̬Ϊ²»¿ÉÓÃ3£©µ½ÊµÀýµÄ´úÀíÁ¬½Ó״̬Ϊ²»¿ÉÓá£
ÎÊÌâ·ÖÎö£º
´Ëʱ·þÎñ¸ÕÆô ......