±±´óÇàÄñoracleѧϰ±Ê¼Ç18
Òì³£
Ô¤¶¨ÒåÒì³£
oracleΪ³£¼û´íÎóÔ¤¶¨Òå
ÔÚDBMS_STANDARD³ÌÐò°üÖÐÌṩÁËÕâЩ¶¨Òå
²»ÐèÒªÏÔʾÉùÃ÷
declare
sex student.stu_sex%type;
begin
select stu_sex into sex from student;
dbms_output.put_line('sex:'||sex);
exception
when no_data_found then
dbms_output.put_line('no such student!');
when too_many_rows then
dbms_output.put_line('too many rows selected!');
when others then
dbms_output.put_line('other error!');
end;
Ô¤¶¨ÒåÒì³£Ãû
²úÉúÔÒò
ACCESS_INTO_NULL
䶨Òå¶ÔÏó
CASE_NOT_FOUND
CASEÖÐÈôΪ°üº¬ÏàÓ¦µÄWHEN£¬²¢ÇÒûÓÐÉèÖÃELSEʱ
COLLECTION_IS_NULL
¼¯ºÏÔªËØÎ´³õʼ»¯
CURSER_ALREADY_OPEN
ÓαêÒѾ´ò¿ª
DUP_VAL_ON_INDEX
ΨһË÷Òý¶ÔÓ¦µÄÁÐÉÏÓÐÖØ¸´Öµ
INVALID_CURSOR
ÔÚ²»ºÏ·¨µÄÓαêÉϽøÐвÙ×÷
INVALID_NUMBER
ÄÚǶµÄSQLÓï¾ä²»Äܽ«×Ö·ûת»»ÎªÊý×Ö
NO_DATA_FOUND
ʹÓÃselect into δ·µ»ØÐУ¬»òÓ¦ÓÃË÷Òý±íδ³õʼ»¯ÔªËØÊ±
TOO_MANY_ROWS
Ö´ÐÐselect intoʱ»ú¹ý¼¯³¬¹ýÒ»ÐÐ
ZERO_DIVIDE
³ýÊýΪ0
SUBSCRIPT_BEYOND_COUNT
ÔªËØÏ±곬¹ýǶÌ×±í»òVARRAYµÄ×î´óÖµ
SUBSCRIPT_OUTSIDE_LIMIT
ʹÓÃǶÌ×±í»òVARRAYʱ½«Ï±êÖÆ¶¨Îª¸ºÊý
VALUE_ERROR
¸³ÖµÊ±£¬±äÁ¿³¤¶È²»×ãÒÔÈÝÄÉʵ¼ÊÊý¾Ý
LOGIN_DENIED
PL/SQL Ó¦ÓóÌÐòÁ¬½Óµ½ oracle Êý¾Ý¿âʱ£¬ÌṩÁ˲»ÕýÈ·µÄÓû§Ãû»òÃÜÂë
NOT_LOGGED_ON
PL/SQL Ó¦ÓóÌÐòÔÚûÓÐÁ¬½Ó oralce Êý¾Ý¿âµÄÇé¿öÏ·ÃÎÊÊý¾Ý
PROGRAM_ERROR
PL/SQL ÄÚ²¿ÎÊÌ⣬¿ÉÄÜÐèÒªÖØ×°Êý¾Ý×ֵ䣦 pl./SQL ϵͳ°ü
ROWTYPE_MISMATCH
ËÞÖ÷Óαê±äÁ¿Óë PL/SQL Óαê±äÁ¿µÄ·µ»ØÀàÐͲ»¼æÈÝ
SELF_IS_NULL
ʹÓöÔÏóÀàÐÍʱ£¬ÔÚ null ¶ÔÏóÉϵ÷ÓöÔÏó·½·¨
STORAGE_ERROR
ÔËÐÐ PL/SQL ʱ£¬³¬³öÄÚ´æ¿Õ¼ä
SYS_INVALID_ID
ÎÞЧµÄ ROWID ×Ö·û´®
TIMEOUT_ON_RESOURCE
Oracle Ôڵȴý×ÊԴʱ³¬Ê±
Óû§×Ô¶¨ÒåÒì³£
ÉùÃ÷ÀàÐÍΪExceptionÀàÐÍ
Ö»ÄÜÖ÷¶¯ÓÉraiseÅ׳ö
declare
Dup_Value Exception;
icount int := 0;
begin
select count(*) into icount from student;
if icount > 0 then
Ïà¹ØÎĵµ£º
in/not inÔÚÅжÏNULLʱÓõÄÓë=/<>Ò»ÑùµÄ·½Ê½£¬¼´±ØÐëÓÃis nullÀ´Åжϣ¬·ñÔòʼÖÕΪʧ°Ü¡£
Óï¾ä
select 'true' from dual where (1,2) not in ((2,3),(2,null));
³É¹¦µÄÔÒòÔÚÓÚÅж϶þÔªÖµÊ±Ê ......
oracle Set ÃüÁî½éÉÜ
http://luoke920.javaeye.com/blog/323743
1). ÉèÖõ±Ç°sessionÊÇ·ñ¶ÔÐ޸ĵÄÊý¾Ý½øÐÐ×Ô¶¯Ìá½»
SQL>SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}
2)£®ÔÚÓÃstartÃüÁîÖ´ÐÐÒ»¸ösql½Å±¾Ê±£¬ÊÇ·ñÏÔʾ½Å±¾ÖÐÕýÔÚÖ´ÐеÄSQLÓï¾ä
SQL> SET ECHO {ON|OFF}
3).ÊÇ·ñÏÔʾµ±Ç°sqlÓï¾ä²éѯ»òÐ޸ĵÄÐÐÊý ......
OracleÖ´Ðмƻ®µÄÏà¹Ø¸ÅÄ
rowid£ºÏµÍ³¸øoracleÊý¾ÝµÄÿÐи½¼ÓµÄÒ»¸öαÁУ¬°üº¬Êý¾Ý±íÃû³Æ£¬Êý¾Ý¿âid£¬´æ´¢Êý¾Ý¿âidÒÔ¼°Ò»¸öÁ÷Ë®ºÅµÈÐÅÏ¢£¬rowidÔÚÐеÄÉúÃüÖÜÆÚÄÚΨһ¡£
recursive sql£ºÎªÁËÖ´ÐÐÓû§Óï¾ä£¬ÏµÍ³¸½¼ÓÖ´ÐеĶîÍâ²Ù×÷Óï¾ä£¬Æ©Èç¶ÔÊý¾Ý×ÖµäµÄά»¤µÈ¡£
row source£¨ÐÐÔ´£©£ºoracleÖ´Ðв½Öè¹ý³ÌÖУ¬ÓÉÉÏÒ» ......
oracleÖÐ×Ô¶¨ÒåÊý¾ÝÀàÐÍ
oracleÖÐÓлù±¾µÄÊý¾ÝÀàÐÍ£¬Èçnumber£¬varchar2£¬date£¬numeric£¬float....µ«ÓÐʱºòÎÒÃÇÐèÒªÌØÊâµÄ¸ñʽ£¬È罫name¶¨ÒåΪ
£¨firstname,lastname£©µÄÐÎʽ£¬ÎÒÃÇÏë°ÑÕâ¸ö×÷Ϊһ¸ö±íµÄÒ»Áп´´ý£¬Õâʱºò¾Í²»ÒªÎÒÃÇ×Ô¼º¶¨ÒåÒ»¸öÊý¾ÝÀàÐÍ
create or replace type type_name as object(firstname varchar ......
OracleË÷Òý·ÖÇøÔÓ̸
<> OracleÊý¾Ý¿âÖУ¬ÓÐÁ½ÖÖÀàÐ͵ķÖÇøË÷Òý£¬È«¾ÖË÷ÒýºÍ±¾µØË÷Òý£¬ÆäÖб¾µØË÷ÒýÓÖ¿ÉÒÔ·ÖΪ±¾µØÇ°×ºË÷ÒýºÍ±¾µØ·Çǰ׺Ë÷Òý¡£ÏÂÃæ¾Í·Ö±ð¿´¿´Ã¿ÖÖÀàÐ͵ÄË÷Òý¸÷×ÔµÄÌØµã¡£
È«¾ÖË÷ÒýÒÔÕû¸ö±íµÄÊý¾ÝΪ¶ÔÏó½¨Á¢Ë÷Òý£¬Ë÷Òý·ÖÇøÖеÄË÷ÒýÌõÄ¿¼È¿ÉÄÜÊÇ»ùÓÚÏàͬµÄ¼üÖµµ«ÊÇÀ´×Ô²»Í¬µÄ·ÖÇø£¬Ò²¿ÉÄÜÊǶà¸ö²»Í¬¼üÖ ......