oracle ´æ´¢¹ý³ÌµÄ»ù±¾Óï·¨
oracle ´æ´¢¹ý³ÌµÄ»ù±¾Óï·¨ ¼°×¢ÒâÊÂÏî
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µ¥²½µ÷ÊÔ
¹ØÓÚoracle´æ´¢¹ý³ÌµÄÈô¸ÉÎÊÌⱸÍü
1.ÔÚoracleÖУ¬Êý¾Ý±í±ðÃû²»ÄܼÓas£¬È磺
select a.appname from appinfo a;-- ÕýÈ·
select a.appname from appinfo as a;-- ´íÎó
Ò²Ðí£¬ÊÇźÍoracleÖеĴ洢¹ý³ÌÖеĹؼü×Öas³åÍ»µÄÎÊÌâ°É
2.ÔÚ´æ´¢¹ý³ÌÖУ¬selectijһ×Ö¶Îʱ£¬ºóÃæ±ØÐë½ô¸úinto£¬Èç¹ûselectÕû¸ö¼Ç¼£¬ÀûÓÃÓαêµÄ»°¾ÍÁíµ±±ðÂÛÁË¡£
select af.keynode&nb
Ïà¹ØÎĵµ£º
http://www.diybl.com/course/3_program/java/javajs/2008429/111906.html
Èç¹ûҪʹÓóصĻ°,¾Í±ØÐëʹÓÃOracleConnectionCacheImpl,¹²ÓÐÈýÖֳصIJßÂÔ:
DYNAMIC_SCHEME(ÊǶ¯Ì¬Ôö¼Ó.ÓÃÍêÈç¹û³¬¹ýÉÏÏÞÔò¹Øµô)
The cache will create connections above the specified maximum limit
when necessary but ......
http://www.cnblogs.com/sleepywang/archive/2009/10/13/1582654.html
1. ÏÂÔØoracleµÄ¿Í»§¶Ë³ÌÐò°ü£¨30M£©
Ö»ÐèÒªÔÚ
Oracle
ÏÂÔØÒ»¸ö½Ð
Instant Client Package
µÄÈí¼þ¾Í¿ÉÒÔÁË£¬Õâ¸öÈí¼þ²»ÐèÒª°²×°£¬Ö»Òª½âѹ¾Í¿ÉÒÔÓÃÁË£¬ºÜ·½±ã£¬¾ÍËãÖØ×°ÁËϵͳ»¹ÊÇ¿ÉÒÔÓõġ£
  ......
oracleÊý¾Ý¿âͬ²½¼¼Êõ
¸ß¼¶¸´ÖÆ
ʲôÊǸ´ÖÆ£¿¼òµ¥µØ˵¸´ÖƾÍÊÇÔÚÓÉÁ½¸ö»òÕ߶à¸öÊý¾Ý¿âϵͳ¹¹³ÉµÄÒ»¸ö·Ö²¼Ê½Êý¾Ý¿â»·¾³Öп½±´Êý¾ÝµÄ¹ý³Ì¡£
¸ß¼¶¸´ÖÆ£¬ÊÇÔÚ×é³É·Ö²¼Ê½Êý¾Ý¿âϵͳµÄ¶à¸öÊý¾Ý¿âÖи´ÖƺÍά»¤Êý¾Ý¿â¶ÔÏóµÄ¹ý³Ì¡£ Oracle ¸ß¼¶¸´ÖÆÔÊÐíÓ¦ÓóÌÐò¸üÐÂÊý¾Ý¿âµÄÈκθ±±¾ ......
ÒªÏÔʾ1µ½2ÐÐÔò¿ÉÒÔͨ¹ý
select * from dangan where rownum between 1 and 2
ÔÚOracle
ÖУ¬Òª°´Ìض¨Ìõ¼þ²éѯǰNÌõ¼Ç¼£¬Óøörownum
¾Í¸ã¶¨ÁË¡£
select *
from emp where rownum
<= 5
¶øÇÒÊéÉÏÒ²¸æ½ë£¬²»ÄܶÔrownum
ÓÃ">"£¬ÕâÒ²¾ÍÒâζ×Å£¬Èç¹ûÄãÏëÓÃ
select * from emp
where row ......
֮ǰ¶ÔORACLEÖеıäÁ¿Ò»Ö±Ã»¸öÌ«Çå³þµÄÈÏʶ£¬±ÈÈç˵ʹÓ㺡¢&¡¢&&¡¢DEIFINE¡¢VARIABLE……µÈµÈ¡£½ñÌìÕýºÃÏÐÏÂÀ´£¬ÉÏÍøËÑÁËËÑÏà¹ØµÄÎÄÕ£¬»ã×ÜÁËһϣ¬ÌùÔÚÕâÀ·½±ãѧϰ¡£
==================================================================================
ÔÚoracle ÖУ¬¶ÔÓÚÒ»¸öÌá½ ......