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 af.appid=aid and af.foundationid=fid;-- ÓÐinto£¬ÕýÈ·±àÒë
select af.keynode from APPFOUNDATION
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
ת×Ô£ºhttp://hi.baidu.com/dashuaiwang/blog/item/47cc680ec35055c37acbe1f8.html
´ò¿ªoracle Êý¾Ý¿âʱ³öÏÖÁË´íÎó:
ORA-00313: ÎÞ·¨´ò¿ªÈÕÖ¾×é 1 (Ïß³Ì 1) µÄ³ÉÔ±
ORA-00312: Áª»úÈÕÖ¾ 1 Ïß³Ì 1: 'D:\ORACLE\ORADATA\GOCOM\REDO01.LOG'
SQL> connect / as sysdba;
ÒÑÁ¬½Ó¡£
SQL> shutdown immediate;
ORA- ......
ÎÊÌ⣺Êý¾Ý¿âΪ£ºOracle Database 10g Release 2
¡¡¡¡²Ù×÷ϵͳΪ£ºWindows Vista/Windows XP MCE(Á½¸ö²Ù×÷ϵͳ·Ö±ð°²×°ºó³öÏÖÏàͬÎÊÌâ)
¡¡¡¡Êý¾Ý¿â°²×°ºó(һ·ĬÈÏÖµ)£¬³¢ÊÔInsertÊý¾ÝºóSelect³öÀ´£¬ÔÀ´µÄÖÐÎÄ×Ö¶ÎÖµ±ä³ÉÁË??????ÐÎʽµÄÂÒÂ룬¸ÃÈçºÎ½â¾ö£¿
¡¡¡¡ÔÚÍøÉϲéÁËһЩ×ÊÁÏ£¬ËµÊÇ·þÎñÆ÷Óë¿Í»§¶Ë×Ö·û¼¯²»Æ¥Å䣬µ«Î ......
¿ª´°º¯ÊýµÄµÄÀí½â£º
¿ª´°º¯ÊýÖ¸¶¨ÁË·ÖÎöº¯Êý¹¤×÷µÄÊý¾Ý´°¿Ú´óС£¬Õâ¸öÊý¾Ý´°¿Ú´óС¿ÉÄÜ»áËæ×ÅÐеı仯¶ø±ä»¯£¬¾ÙÀýÈçÏ£º
over£¨order by salary£© °´ÕÕsalaryÅÅÐò½øÐÐÀۼƣ¬order byÊǸöĬÈϵĿª´°º¯Êý
over£¨partition by deptno£©°´ÕÕ²¿ÃÅ·ÖÇø
ove ......