±±´óÇàÄñ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
Ïà¹ØÎĵµ£º
oracle°²È«
Óû§¹ÜÀí
DBAÊÚÓ費ͬÓû§²»Í¬È¨Àû£¬Ã¿¸öÓû§¿ÉÒÔÔÚÊÚȨ·¶Î§Äڻ£¬Èκγ¬Ô½È¨ÏÞ·¶Î§µÄ²Ù×÷¶¼ÊÓΪ·Ç·¨¡£
sysÓû§ ÍøÂç¹ÜÀíÔ± ÓµÓÐ×î¸ßȨÏÞ
conn sys/¿ÚÁî as sysdba;
systemÓû§ ±¾µØ¹ÜÀíÔ±
scottÓû§ ʾÀýÊý¾Ý¿â
½¨Á¢Óû§£º£¨±ØÐëÓµÓÐdbaȨÏÞ£©
Create User Óû§Ãû Identified by ¿ÚÁî [E ......
PL/SQL
¿é½á¹¹
DECLARE
ÉùÃ÷²¿·Ö
BEGIN
¿ÉÖ´Ðв¿·Ö
EXCEPTION
Òì³£´¦Àí²¿·Ö
END;
Àý£º
Ê×ÏÈÎÒÃÇ¿´Ò»¸ö¼òµ¥Ö®Àý×Ó,ÏÂÃæÕâ¸öÀý×ÓÊÇͳ¼Æ´Ó1 ......
in/not inÔÚÅжÏNULLʱÓõÄÓë=/<>Ò»ÑùµÄ·½Ê½£¬¼´±ØÐëÓÃis nullÀ´Åжϣ¬·ñÔòʼÖÕΪʧ°Ü¡£
Óï¾ä
select 'true' from dual where (1,2) not in ((2,3),(2,null));
³É¹¦µÄÔÒòÔÚÓÚÅж϶þÔªÖµÊ±Ê ......
oracleÀïµÄextendµÄÒâ˼
À©Õ¹ÒÑÖªµÄÊý×é¿Õ¼ä£¬Àý£º
DECLARE
TYPE CourseList IS TABLE OF VARCHAR2(10);
courses CourseList;
BEGIN
-- ³õʼ»¯Êý×éÔªËØ£¬´óСΪ3
courses := CourseList( 'Biol 4412 ', 'Psyc 3112 ', 'Anth 3001 ');
-- ΪÊý×éÔö¼ÓÒ»¸öÔªËØ£¬Êý×é´óСΪ4£¬Ä©Î²µÄÔªËØÎªNULL
courses.EXTEN ......
¹Ø¼ü×Ö: oracle cast() º¯ÊýÎÊÌâ
SQL> create table t1(a varchar(10));
Table created.
SQL> insert into t1 values ('12.3456');
1 row created.
SQL> select round(a) from t1;
ROUND(A)
----------
12
SQL> select round(a,3) from t1;
ROUN ......