Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

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


Ïà¹ØÎĵµ£º

jdbcÁ¬½ÓOracle

      ËäȻѧϰJavaºÜ¾ÃÁË£¬×Ô¼ºÒ²Á¬½Ó¹ýһЩÊý¾Ý¿â£¬±ÈÈçmysqlÖ®ÀàµÄ£¬Èç½ñÄØ£¬Ò²Ñ§Ï°ÁËÒ»¶Îʱ¼äµÄOracle£¬È»¶øÄØ£¬½ñÌìÊÇÎÒµÚÒ»´ÎÁ¬½ÓOracle£¬ºÙºÙ£¬Ó¦¸Ã»¹²»ËãÌ«³Ù°É¡£
    ½ñÌìÄØ£¬Óе㱿׾£¬´ó¼ÒĪЦ£¡
    ÎÒÕâÊÇÒ»¸ö²éѯÀý×Ó
    Ê×ÏÈ£¬Ô ......

ѧϰ¡¶Oracle 9i10g±à³ÌÒÕÊõ¡·µÄ±Ê¼Ç (¾Å) ½ø³Ì

1.Oracle ʵÀýÖ÷ÒªÓÐ3 Àà½ø³Ì
Oracle Öеĸ÷¸ö½ø³ÌÒªÍê³Éij¸öÌØ¶¨µÄÈÎÎñ»òÒ»×éÈÎÎñ£¬Ã¿¸ö½ø³Ì¶¼»á·ÖÅäÄÚ²¿Äڴ棨PGA Äڴ棩À´
Íê³ÉËüµÄÈÎÎñ¡£Oracle ʵÀýÖ÷ÒªÓÐ3 Àà½ø³Ì£º
·þÎñÆ÷½ø³Ì£¨server process£©£ºÕâЩ½ø³Ì¸ù¾Ý¿Í»§µÄÇëÇóÀ´Íê³É¹¤×÷¡£ÎÒÃÇÒѾ­¶ÔרÓ÷þ
ÎñÆ÷ºÍ¹²Ïí·þÎñÆ÷ÓÐÁËÒ»¶¨µÄÁ˽⡣ËüÃǾÍÊÇ·þÎñÆ÷½ø³Ì¡£
º ......

Oracle µÄdrop table if exists¹¦ÄÜ

Oracle´´½¨±íʱ£¬³£Óöµ½ÏÈɾ³ýºó´´½¨µÄÇé¿ö£¬¶øËüÓÖûÓÐdrop table... if existsÓï·¨¡£Îª´Ë¿ÉÒÔʹÓÃuser_objectsÊý¾Ý×ÖµäºÍ¶¯Ì¬sqlÓï¾äʵÏÖÀàËÆµÄ¹¦ÄÜ£¬ÈçÏÂËùʾ£º
create or replace procedure proc_dropifexists(
    p_table in varchar2
) is
    v_count number(10);
begin
&nbs ......

OracleËæ»úº¯ÊýµÄÓ÷¨

 ÈÝÒ×µÃ˵£¬¾­¹ýdbms_random°üµ÷ÓÃËæ»úÊýµÄ²½Öè´óÌåÓÐ4ÖÖ£º
Ò»¡¢dbms_random.normal
Õâ¸öº¯Êý²»´ø²ÎÊý£¬»á»Øµ½normal distributionµÄÒ»¸önumberÃÅÀ֮࣬ËùÒÔ´óµÖËæ»úÊý»áÔÚ-Ò»µ½Ò»Æä¼ä¡£
¼òÂÔ²âÊÔÁËÒ»ÏÂ×Ó£¬·¢×÷100000´Î×î´óÄܵ½ÎåÉÏÏ£º
SQL> declare
¶þ i number:=Áã;
Èý j number:=Áã;
ËÄ begin
Îå for ......

[ת] ½âÎöoracleµÄROWNUM ×÷Õß: chen_liang

¶ÔÓÚrownumÀ´ËµËüÊÇoracleϵͳ˳Ðò·ÖÅäΪ´Ó²éѯ·µ»ØµÄÐеıàºÅ£¬·µ»ØµÄµÚÒ»ÐзÖÅäµÄÊÇ1£¬µÚ¶þÐÐÊÇ2£¬ÒÀ´ËÀàÍÆ£¬Õâ¸öα×ֶοÉÒÔÓÃÓÚÏÞÖÆ²éѯ·µ»ØµÄ×ÜÐÐÊý£¬¶øÇÒrownum²»ÄÜÒÔÈκαíµÄÃû³Æ×÷Ϊǰ׺¡£
 ¾ÙÀý˵Ã÷£º
ÀýÈç±í£ºstudent(ѧÉú)±í£¬±í½á¹¹Îª£º
ID¡¡¡¡¡¡    char(6)¡¡¡¡¡¡¡¡¡¡ --ѧºÅ
name¡¡¡¡ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ