¡°ÓÅÐãʾÀý¡±: 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±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
½ñÌìÖØÐÂÕûÀíµçÄÔ£¬ÕÒµ½Ò»¸öÎļþ£¬¿´ÁËÏÂÈÕÆÚ£¨2006.11.3£© £¬Å²»Ð¡ÐÄÔÙ¶ªÁË£¬´æµ½ÍøÉÏÀ´É¹É¹¡£
SQL ÓïÑÔ·ÖÀࣺ
1 DDL£¨Êý¾Ý¶¨Òå £©ÓÃÓÚ´´½¨ºÍ¶¨ÒåÊý¾Ý¿â¶ÔÏ󣬲¢ÇÒ½«¶ÔÕâЩ¶ÔÏóµÄ¶¨Òå±£´æÔÚÊý¾Ý×ÖµäÖС£
creat table ´´½¨±í
alter table ÐÞ¸ ......
OracleÊý¾Ý¿âÊÇÒ»ÖÖ´óÐ͹ØÏµÐ͵ÄÊý¾Ý¿â£¬ÎÒÃÇÖªµÀµ±Ê¹ÓÃÒ»¸öÊý¾Ý¿âʱ£¬½ö½öÄܹ»¿ØÖÆÄÄЩÈË¿ÉÒÔ·ÃÎÊÊý¾Ý¿â£¬ÄÄЩÈ˲»ÄÜ·ÃÎÊÊý¾Ý¿âÊÇÎÞ·¨Âú×ãÊý¾Ý¿â·ÃÎÊ¿ØÖƵġ£DBAÐèҪͨ¹ýÒ»ÖÖ»úÖÆÀ´ÏÞÖÆÓû§¿ÉÒÔ×öʲô£¬²»ÄÜ×öʲô£¬ÕâÔÚOracleÖпÉÒÔͨ¹ýΪÓû§ÉèÖÃȨÏÞÀ´ÊµÏÖ¡£È¨ÏÞ¾ÍÊÇÓû§¿ÉÒÔÖ´ÐÐijÖÖ²Ù×÷µÄȨÀû¡£¶ø½ÇÉ«ÊÇΪÁË·½±ãDBA¹Ü ......
create table emp2_log(
uname varchar2(20),
action varchar(10),
atime date
);
create or replace trigger trig
after insert or delete or update on emp2 for each row //°óÔÚÒ»ÕűíÉÏ,before after ¶¼¿É
ÒÔ,beforeÊÇ ²åÊý¾Ý֮ǰ£¬afterÊÇÖ®ºó
begin
&nb ......
·ÖÎöº¯ÊýÊÇoracle816ÒýÈëµÄÒ»¸öȫеĸÅÄî,ΪÎÒÃÇ·ÖÎöÊý¾ÝÌṩÁËÒ»ÖÖ¼òµ¥¸ßЧµÄ´¦Àí·½Ê½.ÔÚ·ÖÎöº¯Êý³öÏÖÒÔǰ,ÎÒÃDZØÐëʹÓÃ×ÔÁª²éѯ,×Ó²éѯ»òÕßÄÚÁªÊÓͼ,ÉõÖÁ¸´ÔӵĴ洢¹ý³ÌʵÏÖµÄÓï¾ä,ÏÖÔÚÖ»ÒªÒ»Ìõ¼òµ¥µÄsqlÓï¾ä¾Í¿ÉÒÔʵÏÖÁË,¶øÇÒÔÚÖ´ÐÐЧÂÊ·½ÃæÒ²ÓÐÏ൱´óµÄÌá¸ß.
ÏÂÃæÖ÷Òª½éÉÜÒ»ÏÂÒÔϼ¸¸öº¯ÊýµÄʹÓà ......