[Oracle] Chapter 4 Exception Handling

[Oracle] Chapter 4 Exception Handling

Chapter 4 Exception Handling

PL/SQL blocks are the basic units for PL/SQL programs.

Combine logic-related statements and statements

PL/SQL consists of three parts: Declaration, executable, and exception handling.

[DECLAREdeclarations]BEGINexecutable statements[EXCEPTIONhandlers]END;

The following example demonstrates how to use PL/SQL statements:

Declarearea integer; width integer; height integer; currentdate date; cnumber constant integer: = 10; beginarea: = 10; height: = 2; width: = area/height; currentdate: = sysdate; DBMS_OUTPUT.put_line ('width = '| width); DBMS_OUTPUT.put_line ('height =' | height); DBMS_OUTPUT.put_line ('area = '| area ); DBMS_OUTPUT.put_line ('constant value: '| cnumber); DBMS_OUTPUT.put_line ('current time:' | currentdate); exceptionwhen zero_divide limit ('divisor cannot be 0'); end;

Assign values to variables in two forms:

Use the value assignment statement: =

Use the select into statement

Assign values using constants:

Cnumber constant integer: = 10;

 

The following code demonstrates the use of constants and select into statements:

 

declarefirstName varchar2(20);lastName varchar2(20);employeeid number;consNumber constant integer :=1000;beginselect employee_id, first_name,last_name into employeeid, firstName,lastname from employees2 where employee_id =1;DBMS_OUTPUT.put_line('consNumber = '||consNumber);DBMS_OUTPUT.put_line('employeeid='||firstName);DBMS_OUTPUT.put_line('firstName='||firstName);DBMS_OUTPUT.put_line('lastName='||lastname);end;

Use of the CLOB data type.

create table my_book_text(chapter_id number(3),chapter_descr varchar2(40),chapter_text clob);

Add data to the clob field chapter_text:

Insert into my_book_text values (5, 'Chapter 5 PL/SQL introduction', 'pl/SQL can be used to create stored procedures, triggers, and packages for processing business rules, database time or add program logic to SQL command execution. ');

Read CLOB data type:

set serveroutput ondeclareclob_var clob;amount integer;offset integer;output_var varchar2(100);beginselect chapter_content into clob_var from my_book_text where chapterid=1;amount :=20;offset :=5;DBMS_LOB.READ(clob_var,amount,offset,output_var);DBMS_OUTPUT.put_line(output_var);end;/

 

 

 

 

 

PL/SQL supports the following process control structures:

Condition Control

IF statement

CASE statement

Loop Control

LOOP

WHILE Loop

FOR Loop

Sequential Control

GOTO statement

NULL statement

The following code demonstrates Conditional Control (IF-THEN-ELSE statement ):

Declareage number (8); beginage: = & age; if age> 20 and age <30 thendbms_output.put_line ('Age between 20 and 30 '); elsif age <20 thendbms_output.put_line ('Age less than 20'); elsedbms_output.put_line ('Age greater than 30'); end if; end ;/

The following code retrieves records whose employee_id is 3 from the employees2 table. If salary is greater than 15000, It is subtracted from 1000. Otherwise, salary adds 100.

declarefirstName varchar(20);lastName varchar2(20);salarytemp number(10);beginselect first_name,last_name,salary into firstName,lastName,salarytemp from employees2 where employee_id=3;if salarytemp > 15000 thenupdate employees2 set salary = salary-1000 where employee_id = 3;elseupdate employees2 set salary = salary+100 where employee_id=3;end if;dbms_output.put_line('firstName ='||firstName);dbms_output.put_line('lastName='||lastName);dbms_output.put_line('salarytemp = '||salarytemp);end;

Case statement:

The following code demonstrates the selector. The system first calculates the selector value. Then select the WHEN clause.

Set serveroutput onbegincase '& grade 'when' a' then dbms_output.put_line ('excellent'); when 'B' then dbms_output.put_line ('excellent '); when 'C' then dbms_output.put_line ('excellent '); when 'd 'then dbms_output.put_line ('General'); when 'E' then dbms_output.put_line ('poor '); else dbms_output.put_line ('no such score '); end case; end ;/

Loop loop: the following code demonstrates the use of Loop.

Declarex number; begwhen: = 0; loopx: = x + 1; if x> = 3 thenexit; end if; dbms_output.put_line ('cycle body x = '| x ); end loop; dbms_output.put_line ('circulating in vitro x = '| x); end ;/

Another form:

Declarex number; begwhen: = 0; loopx: = x + 1; exit when x> = 3; dbms_output.put_line ('loop body x = '| x); end loop; dbms_output.put_line ('circulating in vitro x = '| x); end;

While loop:

Declarex number; begcursor: = 0; while x <= 3 loopx: = x + 1; dbms_output.put_line ('inloop '| x); end loop; dbms_output.put_line ('out-of-loop '| x); end ;/

The following code demonstrates the use of the while loop. Declare the sales volume monthly_value and daily_value, and initialize it to 0. While executes the loop until the monthly sales volume is greater than or equal to 4000

Set serveroutput ondeclaremonthly_value number: = 0; daily_value number: = 0; beginwhile monthly_value <= metric: = daily_value * 31; daily_value: = daily_value + 10; daily sales: '| daily_value); end loop; dbms_output.put_line ('monthly sales' | monthly_value); end ;/

For Loop statement:

Beginfor I in 1 .. 5 loopdbms_output.put_line ('cycle I value = '| I); end loop; dbms_output.put_line ('end loop'); end;/Reverse (decrease) use beginfor I in reverse 1 .. 5 loopdbms_output.put_line ('cyclic I value = '| I); end loop; dbms_output.put_line ('end loop'); end ;/

The following code shows 25 even numbers.

set serveroutput onbeginfor eve_number in 1..25loopdbms_output.put_line(eve_number*2);end loop;end;/

Handling exceptions in Oracle:

Predefined exceptions:

Multi-row exception returned:

Declarefirstname varchar2 (20); beginselect first_name into firstname from employees2 where division_id = 'sal '; dbms_output.put_line ('first _ name =' | firstname ); predictionwhen too_many_rows thendbms_output.put_line ('multi-row data cannot be returned '); end ;/

Custom exception:

The following code demonstrates the type that the user accepts the input. The IF statement matches the type entered by the user with the specified category. If the specified category does not exist, a typeException is thrown.

DeclaretypeException exception; temptype varchar2 (20); begintemptype: = '& type'; if temptype not in ('java', 'c ++', 'c # ') thenraise typeException; elsedbms_output.put_line ('temptype = '| temptype); end if; predictionwhen typeException then -- dbms_output.put_line ('corresponding type not found'); raise_application_error (-20000, 'No corresponding type found'); end;

Usage of stored procedures:

A process is a subroutine that executes certain operations. It is a module that executes specific tasks. It can be assigned parameters and stored in the database. The following code

1. Create a stored procedure Syntax:

CREATE [OR REPLACE] PROCEDURE<procedure name> [(<parameter list>)]IS|AS<local variable declaration>BEGIN<executable statements>[EXCEPTION<exception handlers>]END;

The following code creates a stored procedure without parameters:

Create or replace procedure pro_empasfirstName varchar2 (20); lastName varchar2 (20); salary number (20); beginselect first_name, last_name, salary into firstName, lastName, salary from employees2 where employee_id = 1; dbms_output.put_line ('firstname = '| firstName); dbms_output.put_line ('lastname =' | lastName ); dbms_output.put_line ('salary = '| salary); predictionwhen no_data_found thendbms_output.put_line ('data not found'); end;

Execute the above stored procedure:

Execute pro_emp;

Process Parameter mode: three IN, OUT, and in out modes are used for parameter transmission.

IN is the default mode of a parameter. The parameter defined IN this mode already has a value when the program is running, and this value will not change IN the program body.

Parameters defined in the OUT mode are assigned only within the process.

The parameter defined in out mode may already have a value when it is run, but it can also be modified IN the process body.

The process with parameters is as follows:

create or replace procedure mypro(employeeid in number,divisionid in out varchar2,jobid out varchar2)astempdivid varchar2(20);tempjobid varchar2(20);beginselect division_id,job_id into tempdivid,tempjobid from employees2 whereemployee_id =employeeid;divisionid :=tempdivid;jobid :=tempjobid;end;

Execute the above process:

declarecdivisionid varchar2(20);cjobid varchar2(20);cempid number(10);begincempid :=1;mypro(cempid,cdivisionid,cjobid);dbms_output.put_line('...... cdivisionid = '||cdivisionid);dbms_output.put_line('...... cjobid = '||cjobid);end;/

Functions in Oracle:

The following code creates a function:

create or replace function myfun(empid number)return varchar2 isfirstName varchar2(20);lastName varchar2(20);beginselect first_name,last_name into firstName,lastName from employees2 whereemployee_id = empid;return 'firstName = '||firstName ||' lastName = '||lastName;end;/

Execute the above functions:

declarefid number(8);info varchar2(100);beginfid :=1;info :=myfunction(1);dbms_output.put_line('info ='||info);end;/