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µ¥²½µ÷ÊÔ
Ïà¹ØÎĵµ£º
ÔÚʵ¼ÊµÄ¹¤×÷»·¾³ÖУ¬Ðí¶àÈËΪÁËÊÊÓ¦¿Í»§µÄ»·¾³£¬×ÜÊÇÐèҪƵ·±¸ü¸ÄIPµØÖ·£¬µ«ÊÇÔÚIPµØÖ·±ä»»ºó£¬Oracle 10g¾Í»á³öÏÖÆô¶¯²»Á˵ÄÏÖÏó¡£ÍøÉÏÏà¹ØÎÊÌâµÄ¼¼ÊõÎÄÕ´ó¶àÊýÊÇÕë¶ÔOracle 9i£¬»òÕßÒÔÇ°µÄOracle 8iµÄ°æ±¾£¬¶ÔÓëOracle 10gµÄ°æ±¾£¬´ó¶àÖ»ÊǽéÉÜÁË´ó¸Å£¬²¢Ã»Óиø³ö¾ßÌåµÄ²½Öè!±¾ÎÄÖУ¬ÎÒÃǽ«Õë¶ÔÕâÒ»ÎÊÌâ¸ø³ö¾ßÌåµÄ½â¾ö² ......
http://blog.csdn.net/mustbelove/archive/2007/04/17/1567836.aspx(ת)
1 year=1*12 months
1 day=24
hours=24*(1*60mins)=24*60*(1*60 seconds)
1 week =7 days
×¢Ò⣺
ºÚÉ«×ÖÌåÊÇ oracle
8i£¬9i¶¼¿ÉÒÔʹÓõĺ¯Êý£¬
À¶É«×ÖÌåÊÇ Oracle
9i
ÐÂ
ÔöµÄ²¿·Ö¡£
Çë´ó¼ÒÔÚʹÓÃʱ£¬×¢Òâ°æ±¾ÏÞÖÆ¡£
add_mon ......
Ö£ÖØ˵Ã÷£º´ËÎÄÀ´×ÔÓÚÍøÂ磬ÈçÓÐÇÖȨÇë¸æÖ®£¡
µ¼¶Á£º
¡¡¡¡Öؽ¨Ë÷ÒýÓжàÖÖ·½Ê½£¬Èçdrop and re-create¡¢rebuild¡¢rebuild onlineµÈ¡£ÏÂÃæ¼òµ¥±È½ÏÕ⼸ÖÖ·½Ê½ÒìͬÒÔ¼°ÓÅȱµã£º
¡¡¡¡Ê×ÏȽ¨Á¢²âÊÔ±í¼°Êý¾Ý£º
¡¡¡¡SQL> CREATE TABLE TEST AS SELECT CITYCODE C1 from CITIZENINFO2;
¡¡¡¡Table created
¡¡¡¡SQL> ALTE ......
oracle·ÖÎöº¯ÊýÊ®·ÖÇ¿´ó£¬ÎÒÃÇÖ»ÒªÕÆÎÕÕâЩ·½·¨£¬¸üÖ±½ÓµÄ˵·¨¾ÍÊÇÖªµÀÕâЩ·ÖÎöº¯ÊýµÄ×÷ÓþÍÄÜÍê³ÉºÜ¶à¹¤×÷¡£
ϱßÌù³öÕâЩº¯Êý£¬¼°¼òµ¥Ó¦Óá£
ÆäÖÐÎÒÏë¶Ôlag£¨£©ºÍlead£¨£©º¯Êý×øÏÂ˵Ã÷£ºlag£¨£©±¾ÉíÊÇÑÓºóµÄÒâ˼Ҳ¾ÍÊÇÑÓºó³öÏÖijÁеÄÊý£¬¶ølead£¨£©ÓÐÒýÁì¡¢ÁìÏȵÄÒâ˼Ҳ¾ÍÊÇÌáÇ°¼¸ÐÐÏÔʾijÁÐÊý¾Ý
RANK()
dense_rank() ......
Oracle´¥·¢Æ÷Óï·¨(Ò»)
Ò» Oracle´¥·¢Æ÷Óï·¨
´¥·¢Æ÷ÊÇÌض¨Ê¼þ³öÏÖµÄʱºò£¬×Ô¶¯Ö´ÐеĴúÂë¿é¡£ÀàËÆÓÚ´æ´¢¹ý³Ì£¬´¥·¢Æ÷Óë´æ´¢¹ý³ÌµÄÇø±ðÔÚÓÚ:´æ´¢¹ý³ÌÊÇÓÉÓû§»òÓ¦ÓóÌÐòÏÔʽµ÷ÓõÄ,¶ø´¥·¢Æ÷ÊDz»Äܱ»Ö±½Óµ÷Óõġ£
¹¦ÄÜ£º
1¡¢ ÔÊÐí/ÏÞÖƶԱíµÄÐÞ¸Ä
2¡¢ ×Ô¶¯Éú³ÉÅÉÉúÁУ¬±ÈÈç×ÔÔö×Ö¶Î
3¡¢ Ç¿ÖÆÊý¾ÝÒ»ÖÂÐÔ
4¡¢ ÌṩÉó¼Æº ......