È«Ãæ½âÎöOracle developerµÄÒì³£´¦Àí»úÖÆ
Oracle developerÒÔÆä¿ìËÙµÄÊý¾Ý´¦Àí¿ª·¢¶øÎÅÃû£¬ÆäÒì³£´¦Àí»úÖÆÒ²ÊDZȽÏÍêÉÆ£¬²»¿ÉСêï¡£
1¡¢ Òì³£µÄÓŵã
Èç¹ûûÓÐÒì³££¬ÔÚ³ÌÐòÖУ¬Ó¦µ±¼ì²éÿ¸öÃüÁîµÄ³É¹¦»¹ÊÇʧ°Ü£¬Èç
BEGIN
SELECT ...
-- check for ’no data found’ error
SELECT ...
-- check for ’no data found’ error
SELECT ...
-- check for ’no data found’ error
ÕâÖÖʵÏֵķ½·¨È±µãÔÚÓÚ´íÎó´¦ÀíûÓÐÓëÕý³£´¦Àí·Ö¿ª£¬¿É¶ÁÐԲʹÓÃÒì³££¬¿ÉÒÔ·½±ã´¦Àí´íÎ󣬶øÇÒÒì³£´¦Àí³ÌÐòÓëÕý³£µÄÊÂÎñÂß¼·Ö¿ª£¬Ìá¸ßÁ˿ɶÁÐÔ£¬Èç
BEGIN
SELECT ...
SELECT ...
SELECT ...
...
EXCEPTION
WHEN NO_DATA_FOUND THEN -- catches all ’no data found’ errors
2¡¢ Òì³£µÄ·ÖÀà
ÓÐÁ½ÖÖÀàÐ͵ÄÒì³££¬Ò»ÖÖΪÄÚ²¿Òì³££¬Ò»ÖÖΪÓû§×Ô¶¨ÒåÒì³££¬ÄÚ²¿Òì³£ÊÇÖ´ÐÐÆڼ䷵»Øµ½PL/SQL¿éµÄORACLE´íÎó»òÓÉPL/SQL´úÂëµÄij²Ù×÷ÒýÆðµÄ´íÎó£¬Èç³ýÊýΪÁã»òÄÚ´æÒç³öµÄÇé¿ö¡£Óû§×Ô¶¨ÒåÒì³£ÓÉ¿ª·¢ÕßÏÔʾ¶¨Ò壬ÔÚPL/SQL¿éÖд«µÝÐÅÏ¢ÒÔ¿ØÖƶÔÓÚÓ¦ÓõĴíÎó´¦Àí¡£
ÿµ±PL/SQLÎ¥±³ÁËORACLEÔÔò»ò³¬Ô½ÁËϵͳÒÀÀµµÄÔÔò¾Í»áÒþʽµÄ²úÉúÄÚ²¿Òì³£¡£ÒòΪÿ¸öORACLE´íÎó¶¼ÓÐÒ»¸öºÅÂë²¢ÇÒÔÚPL/SQLÖÐÒ쳣ͨ¹ýÃû×Ö´¦Àí£¬ORACLEÌṩÁËÔ¤¶¨ÒåµÄÄÚ²¿Òì³£¡£ÈçSELECT INTO Óï¾ä²»·µ»ØÐÐʱ²úÉúµÄORACLEÒì³£NO_DATA_FOUND¡£¶ÔÓÚÔ¤¶¨ÒåÒì³££¬ÏÖ½«×î³£ÓõÄÒì³£ÁоÙÈçÏ£º
exception oracle error sqlcode value condition
no_data_found ora-01403 +100 select into Óï¾äûÓзûºÏÌõ¼þµÄ¼Ç¼·µ»Ø
too_mang_rows ora-01422 -1422 select into Óï¾ä·ûºÏÌõ¼þµÄ¼Ç¼ÓжàÌõ·µ»Ø
dup_val_on_index ora-00001 -1 ¶ÔÓÚÊý¾Ý¿â±íÖеÄijһÁУ¬¸ÃÁÐÒѾ±»ÏÞÖÆΪΨһË÷Òý£¬³ÌÐòÊÔͼ´æ´¢Á½¸öÖظ´µÄÖµ
value_error ora-06502 -6502 ÔÚת»»×Ö·ûÀàÐÍ£¬½ØÈ¡»ò³¤¶ÈÊÜÏÞʱ£¬»á·¢Éú¸ÃÒì³££¬ÈçÒ»¸ö×Ö·û·ÖÅä¸øÒ»¸ö±äÁ¿£¬¶ø¸Ã±äÁ¿ÉùÃ÷µÄ³¤¶È±È¸Ã×Ö·û¶Ì£¬¾Í»áÒý·¢¸ÃÒì³£
storage_error ora-06500 -6500 ÄÚ´æÒç³ö
zero_divide ora-01476 -1476 ³ýÊýΪÁã
case_not_found ora-06592 -6530 ¶ÔÓÚÑ¡ÔñcaseÓï¾ä£¬Ã»ÓÐÓëÖ®ÏàÆ¥ÅäµÄÌõ¼þ£¬Í¬Ê±£¬Ò²Ã»ÓÐelseÓï¾ä²¶»ñÆäËûµÄÌõ¼þ
cursor_already_open ora-06511 -6511 ³ÌÐòÊÔͼ´ò¿ªÒ»¸öÒѾ´ò¿ªµÄÓαê
timeout_on_resource ora-00051 -51 ϵͳÔڵȴýijһ×ÊÔ´£¬Ê±¼ä³¬Ê±
Èç¹ûÒª´¦ÀíδÃüÃûµÄÄÚ²¿Òì³££¬±ØÐëʹÓÃOTHERSÒì³£´¦ÀíÆ÷»òPRA
Ïà¹ØÎĵµ£º
select myFunc(²ÎÊý1,²ÎÊý2..) to dual; --¿ÉÒÔÖ´ÐÐһЩҵÎñÂß¼
Ò»:OracleÖеĺ¯ÊýÓë´æ´¢¹ý³ÌµÄÇø±ð:
A:º¯Êý±ØÐëÓзµ»ØÖµ,¶ø¹ý³ÌûÓÐ.
B:º¯Êý¿ÉÒÔµ¥¶ÀÖ´ÐÐ.¶ø¹ý³Ì±ØÐëͨ¹ýexecuteÖ´ÐÐ.
C:º¯Êý¿ÉÒÔǶÈëµ½SQLÓï¾äÖÐÖ´ÐÐ.¶ø¹ý³Ì²»ÐÐ.
ÆäʵÎÒÃÇ¿ÉÒÔ½«±È½Ï¸´ÔӵIJéѯд³Éº¯Êý.È»ºóµ½´æ´¢¹ý³ÌÖÐÈ¥µ÷ÓÃÕâЩº¯Êý.
¶þ:È ......
Õ⼸Ìì×öÏîÄ¿Óöµ½ÁËǧÍò¼¶±íµÄ´¦Àí£¬Ïà¹ØÓÅ»¯µÄÐĵÃÌؼǼÏÂÀ´£¬ÒÔÇ°ÈÕºó²é¿´¡£
ÊÕ¼¯Í³¼Æ±íÐÅÏ¢ÓÐ2ÖÖ·½·¨£º
1: ANALYZE TABLE employees COMPUTE STATISTICS;
2: exec dbms_stats.gather_table_stats(ownname => 'owner_name',tabname => 'table_name' ,estimate_percent => null ,method_o ......
connect by Êǽṹ»¯²éѯÖÐÓõ½µÄ£¬Æä»ù±¾Óï·¨ÊÇ£º
select ... from tablename start with Ìõ¼þ1
connect by Ìõ¼þ2
where Ìõ¼þ3;
Àý£º
select * from table
start with org_id = 'HBHqfWGWPy'
connect by prior org_id = parent_id;
¼òµ¥ËµÀ´Êǽ«Ò»¸öÊ÷×´½á¹¹´æ´¢ÔÚÒ»ÕűíÀ±ÈÈçÒ»¸ö±í ......
ÏÈ¿´Oracle ¹Ù·½½âÊÍ
Oracle managed file (OMF)
A file that is created automatically by the Oracle database server when it is needed and automatically deleted when it is no longer needed.
ÈçºÎÅжÏÄãµÄÊý¾Ý¿âÊÇ·ñΪ֧³ÖOMF
SQL> show parameter db_create_file_dest;
NAME & ......
Ò»Ö±Ìýµ½µÄ¶¼ÊÇ˵¾¡Á¿ÓÃexists²»ÒªÓÃin£¬ÒòΪexistsÖ»ÅжϴæÔÚ¶øinÐèÒª¶Ô±ÈÖµ£¬ËùÒÔexists±È½Ï¿ì£¬µ«¿´ÁË¿´ÍøÉϵÄһЩ¶«Î÷²Å·¢ÏÖ¸ù±¾²»ÊÇÕâô»ØÊ¡£
ÏÂÃæÕâ¶ÎÊdzµÄ
Select * from T1 where x in ( select y from T2 )
Ö´ÐеĹý³ÌÏ൱ÓÚ:
select *
......