PL / SQL Programming - Learning Notes 4 - Exception

2011-08-25  来源:本站原创  分类:Database  人气:156 

Fifth, there are three types of exception error handling exception error: Predefined (Predefined) error, non-predefined (Predefined) error, user-defined (User_define) error.
Example 1: pre-defined exception handling
declare
v_empno employees.employee_id% type: = & v_empno;
v_sal employees.salary% type;
begin
select salary into v_sal from employees where employee_id = v_empno for update;
if v_sal <3000 then
update employees set salary = salary + 3000 where employee_id = v_empno;
dbms_output.put_line ('encoded' | | v_empno | | 'wages have been updated');
else
dbms_output.put_line ('encoded' | | v_empno | | 'wages do not need to update');
end if;
exception
when no_data_found then
dbms_output.put_line ('database is not encoded as' | | v_empno | | 'employees');
when too_many_rows then
dbms_output.put_line ('run error, use the cursor');
when others then
dbms_output.put_line ('Other Error');
end;
Example 2: Non-predefined exception handling
declare
v_depno dept.deptno% type: = & v_depno;
deptno_remaining exception;
---2292 Is a violation of consistency constraints of the error code
pragma exception_init (deptno_remaining, -2292);
begin
delete from dept where deptn = v_depno;
exception
when deptno_remaining then
dbms_output.put_line ('Data Integrity constraint violation');
when others then
dbms_output.put_line (sqlcode ||'--'|| sqlerrm);
end;
Example 3: user-defined exception handling
declare
v_empid employees.employee_id% type: = & v_empid;
no_result exception;
begin
update employees set salary = salary + 100 where employee_id = v_empid;
if sql% notfound then
raise no_result;
end if;
exception
when no_result then
dbms_output.put_line ('data update failed');
when others then
dbms_output.put_line ('other abnormality');
end;
SQLCODE returns the error code number
SQLERRM returns the error message such as: sqlcode =- 100 -> sqlerrm = 'no_data_found'
Example 4: the ORACLE error code and message into the error code table
create table errors (errnum number (4), errmsg varchar2 (100));

declare
err_msg varchar2 (100);
begin
/ * Get all the ORACLE error message * /
for err_num in -100 .. 0 loop
err_msg: = sqlerrm (err_num);
insert into errors values ​​(err_num, err_msg);
end loop;
end;

drop table errors;

相关文章
  • PL / SQL Programming - Learning Notes 4 - Exception 2011-08-25

    Fifth, there are three types of exception error handling exception error: Predefined (Predefined) error, non-predefined (Predefined) error, user-defined (User_define) error. Example 1: pre-defined exception handling declare v_empno employees.employee

  • PL / SQL Programming - Learning Notes 6 - Pack 2011-08-28

    Seven, including the creation and application: Package is a group of related procedures, functions, variables, constants, and cursors such as PL / SQL program a combination of design elements, it has object-oriented programming language features, is

  • PL / SQL Programming - Learning Notes 5 - Stored functions and procedures 2011-08-26

    Sixth, stored functions and procedures Can it provide ORACLE PL / SQL procedures stored in the database, and can be anywhere to run it. This is called a stored procedure or function. The only difference between process and function is a function of t

  • PL / SQL Programming - Learning Notes 7 - Pack 2011-08-28

    Example 4: Using a cursor variable to create the package curvarpack. As the cursor is a pointer variable refers to its status is uncertain, he was not stored in the database along with the package, not longer PL / SQL packages declared cursor variabl

  • PL / SQL Programming - Learning Notes 8 - Triggers 2011-08-28

    Eight trigger triggers in the database as a separate object store, it is different with the stored procedures, stored procedures to start other programs run by or directly up and running, and the trigger is an event to start the run. That trigger whe

  • PL / SQL Programming - Study Notes 1 - Introduction and Composition 2011-08-25

    A, PL / SQL Programming Introduction to advanced database programming language, access to the ORACLE database, PL / SQL is the ORACLE system's core language. PL / SQL Procedure Language & Structured Query Language PL / SQL stored procedures, SQL lang

  • PL / SQL Programming - Learning Note 2 - Process Control Statements 2011-08-25

    Three, PL / SQL flow control statements introduced PL / SQL flow control statements, including the following three categories: Control statements: IF statements loop: LOOP statements, EXIT statement sequence statements: GOTO statement, NULL statement

  • PL / SQL Programming - Study Notes 3 - Cursor 2011-08-25

    Fourth, the use of cursors in PL / SQL procedures for handling multi-line record of transactions often use a cursor to achieve. Cursor is a pointer to the context handle (handle) or a pointer. By the cursor, PL / SQL can control the context statement

  • PL / SQL programming based learning summary 2010-05-27

    PL / SQL Programming 1. How to write and compile PL / SQL Because PL / SQL is embedded in the Oracle server and Oracle development tool, so Oracle client tools and server can write PL / SQL procedure SQL> edit c: \ plsqlblock1.sql; - to build a docum

  • oracle PL / SQL Programming Notes 2010-08-07

    1. The first paradigm: all the attributes are inseparable entities, and that the table to meet 1NF; The second paradigm: allow more than one property as the main code with the third paradigm: there is no dependence of the transfer function, namely, t

  • Oracle PL / SQL Programming Guide Specification 2010-05-11

    1, PL / SQL programming specification of the case As in SQL, as, PL / SQL is not case sensitive. The general guidelines are as follows: Keyword (BEGIN, EXCEPTION, END, IF THEN ELSE, LOOP, END LOOP), the data type (VARCHAR2, NUMBER), the internal func

  • PL / SQL Programming 2011-04-10

    "PL / SQL Programming" / * Procedural language / sql * / - 1, procedures, functions, triggers a pl / sql written - 2, procedures, functions, triggers in oracle in - 3, pl / sql is very powerful database procedure language - 4, procedure, functio

  • oracle sum of ten ---PL/SQL Programming 2011-04-18

    PL / SQL Programming PL / SQL is the oracle of the special language, its standard SQL language extension. SQL statements can be nested in a PL / SQL language, and combined treatment statement. PL / SQL program using the block structure of the structu

  • PL / SQL programming skills 2011-08-26

    Usually in PL / SQL programming in some of the problems encountered here in the form of questions and answers for them to conclude, for all to share. 1, when you need to load a table or a large number of data streams need to handle large amounts of d

  • Book: Oracle 11g PL / SQL Programming 2011-09-18

    Oracle 11g PL / SQL Programming

  • oracle notes (i) PL / SQL Programming 2011-04-13

    PL / SQL is the oracle of the special language, its standard SQL language extension. SQL statements can be nested in a PL / SQL language, and combined treatment statement. PL / SQL Program Structure Use the block structure of the organization code. T

  • PL / SQL Programming Introduction 2010-06-27

    Oracle in the database into a process programming language, called PL / SQL (Procedural Language / SQL) .PL / SQL and SQL build upon, can be used to write programs containing SQL statements. Which includes such standard programming language is struct

  • PL / SQL Programming Introduction (change) 2010-08-12

    Oracle introduced in the database, a process programming language, called PL / SQL (Procedural Language / SQL) .PL / SQL and SQL build upon, can be used to write programs containing SQL statements. Which includes such standard programming language is

  • Oracle Database 11g PL / SQL Programming 10.1 Introduction trigger 2010-09-08

    http://sns.linuxpk.com/space-52196-do-blog-id-16060.html Database triggers are special stored procedures. Usually do not call them directly, instead of triggering the events from the database. Them in the implementation of command and the implementat

  • Oracle Database 11g PL / SQL Programming Overview 10.1 Triggers 2010-09-08

    http://sns.linuxpk.com/space-52196-do-blog-id-16060.html Database triggers are special stored procedures. Usually do not call them directly, but to the events triggered by the database. They execute the command and the implementation of the action be