OracleÊý¾Ý¿â×Ô¶¨ÒåÒì³£µÄʹÓ÷½·¨
À´Ô´£º²»Ïê ×÷ÕߣºØýÃû ʱ¼ä£º2009-9-6 17:14:04 Tags£º Orac ʹÓ÷½·¨
ÐèÇó£ºÒµÎñÂß¼ÔÚ´¦ÀíÊý¾Ýʱ£¬ÐèÒª·µ»ØMessage²¢×èÖ¹³ÌÐòµÄ¼ÌÐøÔËÐС£µ«ÊÇ£¬ÔÚ´æ´¢¹ý³ÌʹÓÃOracleÊý¾Ý¿âµÄRaise¸ù±¾ÎÞ·¨Âú×ãÏÖÔÚµÄÒªÇó¡£
½â¾ö·½·¨£º
ʹÓÃRAISE_APPLICATION_ERROR
RAISE_APPLICATION_ERROR ( error_number_in IN NUMBER, error_msg_in IN VARCHAR2);
error_number: ×Ô¶¨ÒåµÄ´íÎó±àºÅ¡£
error_msg£º×Ô¶¨ÒåµÄ´íÎóÄÚÈÝ¡£
ÔÚʹÓõĹý³ÌÖУ¬Äã¿ÉÄÜ»áÓöµ½ÁËÏÂÃæµÄÎÊÌâ¡£
ORA-21000: error number argument to raise_application_error of [xxxx] is out of range
³öÏÖ´Ë´íÎóµÄÔÒòÈçÏ£º
ÔÚ´æ´¢¹ý³ÌÖж¨ÒåµÄ´íÎó´úÂëNumberÆäʵ²¢²»ÔÚOracleÊý¾Ý¿âËùÔÊÐíµÄ·¶Î§Ö®ÄÚ¡£ÒòΪOracleÊý¾Ý¿âÔÊÐí×Ô¶¨ÒåµÄ´íÎó´úÂëµÄ·¶Î§ÊÇ-20000 -- -20999
ÕâÑù£¬¾ÍÔÚJava¶Ë¿ÉÒÔͨ¹ýSQLExceptionÀ´²¶»ñÒì³£¡£
ËäȻͨ¹ýSQLException.getMessage()¿ÉÒÔ²¶»ñµ½Òì³£µÄÄÚÈÝ£¬µ«ÊÇÕâЩÄÚÈݶÔÓÚ¿ª·¢±È½ÏÓÐÓ᣶ÔÓÚ¿Í»§À´Ëµ£¬²¢²»ÓѺã¬Ò²Ã»Óжà´óÒâÒå¡£
ͨ¹ýSQLException.getErrorCode()¿ÉÒÔ²¶»ñµ½×Ô¶¨ÒåµÄÒì³£´íÎó±àºÅ¡£È»ºóÎÒÃǾͿÉÒÔͨ¹ýÕâ¸ö´íÎó±àºÅ£¬×Ô¶¨ÒåÏàÓ¦µÄMessageÄÚÈÝ£¬·µ»Ø¸ø¿Í»§¡£
ÏÂÃæÎÒÃÇÀ´½éÉÜÁíÒ»ÖÖ½â¾ö·½·¨£º
ͨ¹ý´æ´¢¹ý³Ì·µ»ØÖµ£¬Äã¿ÉÒÔÖ±½Ó°Ñ´íÎóMessage¶¨ÒåÔÚ·µ»ØÖµÖм䡣ÔÚJava¶Ë½âÎöºó£¬¿ÉÒÔÖ±½ÓÈ¡µÃMessage´úÂë¡£
ʾÀýÈçÏ£º
If condition then
p_result := 'ERR:MSG2061';
end if;
if substr(p_result, 1, 3) = 'ERR' then
ROLLBACK;
else
p_result = 'OK';
end if;
Java£ºÂÔÈ¥ÖмäµÄµ÷Óô洢¹ý³ÌµÄ²½Öè
if(result != null && result.startsWith("ERR:"))
// ´ÓresultÖнâÎö³öMessage_id£¬ÏÔʾMessageµ½Ç°Ì¨
Ïà¹ØÎĵµ£º
select myFunc(²ÎÊý1,²ÎÊý2..) to dual; --¿ÉÒÔÖ´ÐÐһЩҵÎñÂß¼
Ò»:OracleÖеĺ¯ÊýÓë´æ´¢¹ý³ÌµÄÇø±ð:
A:º¯Êý±ØÐëÓзµ»ØÖµ,¶ø¹ý³ÌûÓÐ.
B:º¯Êý¿ÉÒÔµ¥¶ÀÖ´ÐÐ.¶ø¹ý³Ì±ØÐëͨ¹ýexecuteÖ´ÐÐ.
C:º¯Êý¿ÉÒÔǶÈëµ½SQLÓï¾äÖÐÖ´ÐÐ.¶ø¹ý³Ì²»ÐÐ.
ÆäʵÎÒÃÇ¿ÉÒÔ½«±È½Ï¸´ÔӵIJéѯд³Éº¯Êý.È»ºóµ½´æ´¢¹ý³ÌÖÐÈ¥µ÷ÓÃÕâЩº¯Êý.
¶þ:È ......
CREATE OR REPLACE VIEW ADMIN.TYPETREEVIEW
(ID, ITEMNO, ITEMNOSUB, GROUPID, MEMO,
TREE)
AS
/* 2007/01/23 16:51 XieShaoHua µÝ¹é²éѯ */
SELECT typetree.ID, typetree.itemno, typetree.itemnosub, typetree.groupid,
&n ......
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 & ......
ÏÈÀ´¿´¿´¹Ù·½ÎĵµÖжÔÕâ¸ö²ÎÊýµÄ½âÊÍ
CURSOR_SHARING
PropertyDescription
Parameter type
String
Syntax
CURSOR_SHARING = { SIMILAR | EXACT | FORCE }
Default value
EXACT
Modifiable
ALTER SESSION, ALTER SYSTEM
Basic
No
CURSOR_SHARING determines what kind of SQL statements can share the same cu ......