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 into kn from APPFOUNDATION af where&nb
Ïà¹ØÎĵµ£º
1.Oracle ʵÀýÖ÷ÒªÓÐ3 Àà½ø³Ì
Oracle Öеĸ÷¸ö½ø³ÌÒªÍê³Éij¸öÌØ¶¨µÄÈÎÎñ»òÒ»×éÈÎÎñ£¬Ã¿¸ö½ø³Ì¶¼»á·ÖÅäÄÚ²¿Äڴ棨PGA Äڴ棩À´
Íê³ÉËüµÄÈÎÎñ¡£Oracle ʵÀýÖ÷ÒªÓÐ3 Àà½ø³Ì£º
·þÎñÆ÷½ø³Ì£¨server process£©£ºÕâЩ½ø³Ì¸ù¾Ý¿Í»§µÄÇëÇóÀ´Íê³É¹¤×÷¡£ÎÒÃÇÒѾ¶ÔרÓ÷þ
ÎñÆ÷ºÍ¹²Ïí·þÎñÆ÷ÓÐÁËÒ»¶¨µÄÁ˽⡣ËüÃǾÍÊÇ·þÎñÆ÷½ø³Ì¡£
º ......
http://www.inthirties.com/thread-757-1-1.html
ºÜÈÙÐÒ£¬±»ÑûÇëΪCSDNѧϰ´ó±¾ÓªÀïµÄOracleÀÏʦ¡£ÓиöÍøÓÑ·¢ÏûÏ¢¹ýÀ´£¬Ò»Æð̽ÌÖÈçºÎѧϰOracle£¬Ò»ÏÂÊǻظ´£¬ºÍ´ó¼ÒÒ»Æð̽ÌÖ¡£
Ê×ÏÈ£¬ÒªÃ÷È·ÄãµÄ·½ÏòºÍÄ¿±ê¡£
¶ÔÓÚOracleÀ´Ëµ£¬Õâ¸öÌåϵÊDZȽÏÅÓ´óµÄ£¬ËùÒÔÃ÷È·Ò»¸öÄ¿±êºÍÄãµÄ·½ÏòÊÇÔÚѧϰǰÐèҪ˼¿¼µÄÎÊÌâ¡£ ¹ÜÀí£¬ ......
1.ÔÚOracle ÖУ¬Äã»áÁ˽⵽£º
ÊÂÎñÊÇÿ¸öÊý¾Ý¿âµÄºËÐÄ£¬ËüÃÇÊÇ“ºÃ¶«Î÷”¡£
Ó¦¸ÃÑÓ³Ùµ½Êʵ±µÄʱ¿Ì²ÅÌá½»¡£²»ÒªÌ«¿ìÌá½»£¬ÒÔ±ÜÃâ¶Ôϵͳ´øÀ´Ñ¹Á¦¡£ÕâÊÇÒòΪ£¬Èç¹û
ÊÂÎñºÜ³¤»òºÜ´ó£¬Ò»°ã²»»á¶ÔϵͳÓÐѹÁ¦¡£ÏàÓ¦µÄÔÔòÊÇ£ºÔÚ±ØÒªÊ±²ÅÌá½»£¬µ«ÊÇ´Ëǰ²»ÒªÌá
½»¡£ÊÂÎñµÄ´óСֻӦ¸Ã¸ù¾ÝÒµÎñÂß¼À´¶¨¡£
Ö»ÒªÐèÒ ......
1£ºÖØÐÂÔÚdbcaÖд´½¨Êý¾Ý¿â²¢Ñ¡ÔñÕýÈ·µÄ×Ö·û¼¯
2£º
²éѯµ±Ç°×Ö·û¼¯£º
select userenv('language') from dual;
select * from V$NLS_PARAMETERS;
ÔÚWindowsÏÂsqlplusÍêÈ«Õý³££¬¿ÉÊǵ½LinuxÏ£¬sqlplusÖÐÎÄÏÔʾ¾Í³öÎÊÌâÁË£¬×ÜÊÇÏÔʾ“??”£¬Õâ¸öÎÊÌâÓÖÔõô½â¾öÄØ£¿
¾¹ýÔÚÍøÂçÉϲé×ÊÁÏ£¬ÒÔ¼°³¢ÊÔ£¬µÃµ½ ......
Ëæ×żÆËã»úµÄÆÕ¼°ÒÔ¼°ÍøÂçµÄ·¢Õ¹£¬Êý¾Ý¿âÒѾ²»ÔÙ½ö½öÊÇÄÇЩ³ÌÐòÔ±ËùרÓеϰÌâ¡£¶øOracleÊý¾Ý¿â¸üÊÇÆ¾½èÆäÐÔÄÜ׿Խ£¬²Ù×÷·½±ãÁé»îµÄÌØµã£¬ÔÚÊý¾Ý¿âµÄÊг¡ÖÐÒѾռ¾ÝÁËһϯ֮µØ¡£µ«ÊÇͬÑùËæ×ÅÍøÂç¼¼ÊõµÄ²»¶Ï½ø²½£¬Êý¾ÝÐÅÏ¢µÄ²»¶ÏÔö¼Ó£¬Êý¾Ý°²È«ÒѾ²»ÔÙÊÇÒÔǰµÄ“ÀÏÉú³¤Ì¸”£¬Ò²¸ü²»ÊÇÒÔǰÊé±¾ÉÏÄÇЩ“¿ÉÍû² ......