·¢²¼Ê±¼ä : ÐÇÆÚÁù ÎÄÕÂoracle´æ´¢¹ý³Ì³¬ÏêϸʹÓÃÊÖ²á¸üÐÂÍê±Ï¿ªÊ¼ÔĶÁ
¹¦ÄÜ: ·µ»ØstringµÄ×Ö½Úµ¥Î»µÄ³¤¶È.CHARÊýÖµÊÇÌî³ä¿Õ¸ñÀàÐ͵Ä,Èç¹ûstringÓÉÊý¾ÝÀàÐÍCHAR,ËüµÄ½áβµÄ¿Õ¸ñ¶¼±»¼ÆËãµ½×Ö·û´®³¤¶ÈÖмä. Èç¹ûstringÊÇNULL,·µ»Ø½á¹ûÊÇNULL,¶ø²»ÊÇ0. ʹÓÃλÖÃ: ¹ý³ÌÐÔÓï¾äºÍSQLÓï¾ä¡£
20¡¢LENGTHB
Óï·¨: LENGTHB£¨string£©
¹¦ÄÜ: ·µ»ØÒÔ×Ö½ÚΪµ¥Î»µÄstringµÄ³¤¶È.¶ÔÓÚµ¥×Ö½Ú×Ö·û¼¯LENGTHBºÍLENGTHÊÇÒ»ÑùµÄ.
ʹÓÃλÖÃ: ¹ý³ÌÐÔÓï¾äºÍSQLÓï¾ä¡£
21¡¢NLSSORT
Óï·¨: NLSSORT£¨string[,nlsparams]£©
¹¦ÄÜ: µÃµ½ÓÃÓÚÅÅÐòstringµÄ×Ö·û´®×Ö½Ú.ËùÓеÄÊýÖµ¶¼±»×ª»»Îª×Ö½Ú×Ö·û´®,ÕâÑùÔÚ²»Í¬Êý¾Ý¿âÖ®¼ä¾Í±£³ÖÁËÒ»ÖÂÐÔ. NlsparamsµÄ×÷ÓúÍ
NLS_INITCAPÖеÄÏàͬ.Èç¹ûºöÂÔ²ÎÊý,»á»°Ê¹ÓÃȱʡÅÅÐò. ʹÓÃλÖÃ: ¹ý³ÌÐÔÓï¾äºÍSQLÓï¾ä¡£
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 af where af.appid=aid and af.foundationid=fid;-- ûÓÐinto£¬±àÒ뱨´í£¬Ìáʾ£ºCompilation
Error: PLS-00428: an INTO clause is expected in this SELECT statement
3.ÔÚÀûÓÃselect...into...Ó﷨ʱ£¬±ØÐëÏÈÈ·±£Êý¾Ý¿âÖÐÓиÃÌõ¼Ç¼£¬·ñÔò»á±¨³ö\Òì³£¡£