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

¡°ÓÅÐãʾÀý¡±: Oracle´úÂë¹æ³Ì

 ÔÚPL/SQLÖÐʹÓÃÕóÁд¦ÀíÊÇÒ»¸öºÜºÃµÄ×ö·¨(È磬ʹÓÃbulk collectºÍforall)¡£ÅúÁ¿´¦ÀíÄܹ»´ó´ó¼õÉÙPL/SQLÓï¾äÖ´ÐÐÒýÇæµÄ»·¾³Çл»´ÎÊý£¬´Ó¶øÌá¸ßÆäÐÔÄÜ¡£
¡¡¡¡ÁíÒ»¸öÓÅÐãʾÀýÊǰѴ洢¹ý³ÌÖеÄËùÓдúÂë·ÅÈëËø¶¨µÄÈí¼þ°üÖУ¬ÕâÑù¿ÉÒÔÉú³ÉÄ£¿éµ¥Ôª¡£°Ñ´æ´¢¹ý³Ì·ÅÈëÈí¼þ°üÀï¿ÉÒÔʵÏÖÏà¹Ø³ÌÐòºÍ¹¦ÄܵķÖ×é¡£µ±
µ¥¸ö°ü±»Ê¹Óã¬Õû¸öÈí¼þ°ü¶¼»áÔØÈëÄÚ´æÖÐ(ÄÚ´æ»áÆô¶¯Õû¸öÈí¼þ°ü?)£¬°Ñ´Åµú´æÈ¡Ê±¼ä¼õµ½×îÉÙ¡£Í¨¹ýÕâ¸ö·½·¨ÎÒÃÇͬÑù¿ÉÒÔ°ÑÕû¸öÓ¦ÓóÌÐò°üÔØ½øÄÚ´æÖУ¬·À
Ö¹·¢ÉúÖØÐÂÔØÈëºÍ´úÂë½âÎö£¬´Ó¶ø¼õÉÙÑÏÖØÓ°ÏìÐÔÄܵĴúÂëµÝ¹é¡£
¡¡¡¡PL/SQL(ºÍSQL)µÄÁíÒ»¸öÓÅÐãʾÀýÊÇʹÓÃÊʵ±µÄ±äÁ¿ÀàÐÍ(µ±ÄãÐèÒªNUMBERʱ²»ÒªÊ¹ÓÃVARCHAR2£¬·´Ö®ÒàÈ»)¡£Ê¹Óò»Êʵ±µÄ±äÁ¿(ÓÃcharacter¸únumber½øÐбȽÏ)»áµ¼ÖÂÎÞÓÃË÷Òý¡£±£Ö¤±äÁ¿ÀàÐÍÕýÈ·µÄÒ»ÖÖ·½·¨¾ÍÊÇʹÓÃ%TYPE ºÍ%ROWTYPE¡£
¡¡¡¡»¹ÓоÍÊÇÓÀԶʹÓÃDBMS_PROFILER»òʹÓÃÏñQuest SoftwareµÄQuest Code Tester¹¤¾ßÀ´Ñé֤ѭ»·Âß¼­¡£DBMS_PROFILERÊÇOracleÌṩµÄÒ»¸öÈí¼þ°ü£¬Äܹ»Ê¹ÄãµÄ´úÂëÉú³É¶ÔÿÐÐÖ´ÐÐʱ¼ä¼°ËùÐèʱ¼äµÄ×·×Ù¡£Äã¿ÉÒÔÑé֤ѭ»·Ö´ÐдÎÊýӦΪ×îÉÙ¡£
¡¡¡¡ÄãͬÑùÓ¦¸ÃÑéÖ¤Êʵ±µÄIF-THEN-ELSE½á¹¹¡£ÎÒµÄÒâ˼ÊÇÄãÓ¦¸Ã°Ñ×î³£ÓõÄÑ¡Ïî·ÅÔÚÇ°Ãæ(±ÈÈçexit test)¡£Õâ¸ö·½·¨Í¬ÑùʹÓÃÓÚCASE½á¹¹¡£
¡¡¡¡PL/SQL(ÒÔ¼°Java¡¢C¡¢C++ºÍÆäËûËùÓÐOracleÏà¹ØµÄ3GLs)ÖУ¬ÁíÒ»¸ö¸üÎªÖØÒªµÄÓÅÐãʾÀýÊÇÊ×Ïȵ÷ÓÅSQL¡£¼´Ê¹ÊÇÊÀ½çÉÏÉè¼Æ×ÃܵijÌÐò£¬Èç¹ûÆäÖк¬ÓеÄSQLºÜ²îµÄ»°£¬ÔËÐÐÆðÀ´ÐÔÄÜÒ²»á²»ºÃ¡£QuestµÄSQL Optimizer¡¢Performance Analyzer¡¢TOADºÍSQL Navigator¶¼Äܹ»ÓÅ»¯SQL¡£
¡¡¡¡ÔÚ²âÊÔPL/SQL-SQL´úÂëµÄʱºò£¬Ó¦ÀûÓÃÄäÃûPL/SQL¿éÀ´±£Ö¤´¦Àí»·¾³µÄÏàËÆÐÔ¡£Èç¹ûÄãÔÚÒ»¸ö±ê×¼µÄSQL»·¾³ÀïÓÃÎÄ×Ö´úÌæ°ó¶¨±äÁ¿À´½øÐвâÊÔ£¬ÄãËùµÃµ½µÄÖ´Ðмƻ®»á²»Í¬ÓÚµ±ÄãʹÓÃÄäÃûPL/SQL¿éºÍ°ó¶¨±äÁ¿Ê±µÃµ½µÄÖ´Ðмƻ®£¬Òò´ËÄãµÄµ÷ÕûºÜ¿ÉÄܲ»ÄÜ»ñµÃºÜºÃµÄ½á¹û¡£Ê¹ÓÃQuest Code TesterÄܹ»±£Ö¤ÄãÄÜ»ñµÃÄãÏëÒªµÄ½á¹û¡£
¡¡¡¡»¹ÓÐÒ»¸öÁ¼ºÃµÄ±à³Ì×ö·¨ÊǺܺõØÀûÓÃÁÙʱ±íºÍPL/SQLË÷Òý±í¡£²»ÕýÈ·µØÊ¹ÓÔnormal”±í½øÐÐÁÙʱ´¢´æ»á½µµÍÐÔÄÜ£¬Òò´ËӦʹÓÃÁÙʱ±í¡£¶øµ±ÄÚ´æ¿Õ¼ä×ã¹»ÔËÐÐPL/SQL±íʱʹÓÃÁÙʱ±íÒ²»á´ó´ó½µµÍÐÔÄÜ¡£ÄãÒ²¿ÉÒÔÀûÓÃÊÓͼÀ´´´½¨Öмä½á¹ûµÄ»º´æ±í¡£
¡¡¡¡»¹ÓÐÒ»¸öºÜ¼òµ¥Ò×ÐеÄÓÅÐãʾÀýÊÇÔÚIN OUTºÍOUT±äÁ¿ÉÏÀûÓÃNOCOPY¡£ÔÚÒ»¸öʹÓÃIN OUT»òOUTµÄPL/SQL³ÌÐòÖУ¬Èç¹ûNOCOPY¹Ø¼ü×ÖûÓаüº¬ÔÚÆäÒ³Ê׵ıäÁ¿ÉùÃ÷ÖУ¬ËùÓеıäÁ¿¶¼»áÍ


Ïà¹ØÎĵµ£º

oracleÁÐתÐз½·¨×ܽá

·½·¨Ò»£º
----------------------------------------------------------------
---Muti-row to line(col2row)
----------------------------------------------------------------
create or replace type str_tab is table of varchar2(20);
/
grant all on str_tab to public;
create public synonym str_tab for ......

8¸öDBA×î³£ÓÃµÄ¼à¿ØOracleÊý¾Ý¿âµÄ³£ÓÃshell½Å±¾

±¾ÎĽéÉÜÁË8¸ö³£ÓÃµÄ¼à¿ØÊý¾Ýshell½Å±¾¡£Ê×ÏȻعËÁËһЩDBA³£ÓõÄUnixÃüÁÒÔ¼°½âÊÍÁËÈçºÎͨ¹ýUnix CronÀ´¶¨Ê±Ö´ÐÐDBA½Å±¾¡£ÍøÉÏÒ²ÓкöàÀàËÆµÄÎÄÕ£¬µ«»ù±¾É϶¼²»ÄÜÕý³£ÔËÐУ¬»¨µãʱ¼äÖØÐÂÕûÀíÁËÏ£¬ÒÔºó¾ÍÄÜÖ±½ÓʹÓÃÁË¡£
Ò»£®Í¬Ê±ÎÄÕ»¹½éÉÜÁË8¸öÖØÒªµÄ½Å±¾À´¼à¿ØOracleÊý¾Ý¿â£º
1.¼ì²éʵÀýµÄ¿ÉÓÃÐÔ
2.¼ì²é¼àÌýÆ÷µ ......

¶ÔOracleѧϰÕßµÄһЩ½¨Òé

ѧϰOracleÊÇÒ»¸öÂþ³¤¼è
ÐÁµÄ¹ý³Ì¡£Èç¹ûûÓÐÐËȤ£¬Ö»ÊDZ»ÆÈѧϰ£¬ÄÇôÊǺÜÄÑѧºÃµÄ¡£Ñ§Ï°µ½Ò»¶¨³Ì¶ÈµÄʱºò£¬ÒªÏë½øÒ»²½Ìá¸ß£¬¾Í²»µÃ²»½Ó´¥ºÜ¶àOracleÖ®ÍâµÄ¶«Î÷£¬Èç
Unix£¬ÈçÍøÂç¡¢´æ´¢µÈ¡£Òò´Ë£¬ÒªÕæµÄ¾öÐÄѧºÃOracle£¬¾ÍÒ»¶¨ÒªÓÐÐËȤ¡£ÓÐÁËÐËȤ£¬¾Í»áÒ»ÇбäµÃ¼òµ¥¿ìÀÖÆðÀ´¡£¼òµ¥×ܽáһϣ¬ÄǾÍÊÇ£ºÐËȤ¡¢Ñ§
ϰ¡¢Êµ¼ù¡£ ......

Oracle ´æ´¢¹ý³Ì

 
create or replace procedure p   //ÓоÍÌæ»»£¬Ã»Óоʹ´½¨
is
     cursor c is
         select * from emp for update;
begin
  for v_emp in c loop
      if (v_emp.deptno =10) then
 &nb ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ