PL/SQL

Exception --> error

user defined

system exception

declare

begin

exception

end

 

PL/SQL program 

take the input (any number ) from user, print the input in reverse order

print the input in reverse order

use length function

for i in reverse 1..length(no)

loop

DBMS_OUTPUT.put_line(i);

end loop;

num1:= num1 || substr(no, i, 1)

 

for i in reverse 1..length(no)

substr( no, 1, 1 )

 

eg :

declare

given_number varchar2(5) := '&given_number';

str_length number(2);

reverse_num varchar2(5);

begin

str_length := length(given_number);

for i in reverse 1..str_length

loop

reverse_num := reverse_num || substr(given_number, i, 1);

end loop;

DBMS_OUTPUT.put_line('Given number is : ' || given_number);

DBMS_OUTPUT.put_line('Reverse_number is : || reverse_num);

end;

 

eg :

2nd method 

using MOD() and TRUND() function

 

MOD(1234, 10) --> 4

reverse_num := reverse_num || 

TRUNC(1234/10) --> 123

 

eg : LEVEL or MADAM  or RACECAR(similarity )

Palindrome

write a program to find the given word is palindrome or not.

substr() for loop

 

declare

s varchar2(10) := '&s';

temp varchar2(10);

begin

for i in reverse 1..length(s)

loop

temp := temp || substr(s, i, 1);

end loop;

--compare

if temp = s then

DBMS_OUTPUT.put_line('PALINDROME');

else

DBMS_OUTPUT.put_line('NOT PALINDROME');

end if;

end;

 

WAP(write a program) to calculate the salary

input -->ename, salary

calculate --> net salary = salary + HRA + DA - PF

HRA is --> 10% of salary

DA is --> 30% of salary

PF is --> 8% of salary(if salary < 8000)

PF is --> 10% of salary (if salary > 8000)

output --> net salary

  --> PF

  --> ename

DECLARE

salary varchar2(10) := '&salary';

ename varchar2(10) := 'ename';

net_salary varchar2(10) := 0;

pf varchar2(10) := 0;

hra varchar2(10) := 0;

da varchar2(10) := 0;

BEGIN

hra := 10%*salary;

da := 30%*salary;

if salary > 800 then

pf := 10%*salary;

else

pf := 8%*salary'

end if;

net_salary := salary + hra + da - pf;

DBMS_OUTPUT.put_line('NET SALARY IS : ' || net_salary);

DBMS_OUTPUT.put_line('PF IS : ' || pf);

DBMS_OUTPUT.put_line('ENAME is : ' || ename);

 

END;

 

pre-defined (system defined)

use-defined (user defined)

exception : an identifier in PL/SQL that is used to trap for an abnormal condition. if an error occurs then an exception is raised, the block will terminate and control will pass to the exception handler if present.

 

declare

num1 number(2) := 10;

num2 number(2) ;

begin

num := 1/0;

exception

when error then

end;

when no_data_found

exceptions :

DUP_VAL_ON_INDEX

NO_DATA_FOUND

VALUE_ERROR

INVALID_CURSOR

TOO_MANY_ROWS

INVALID_NUMBER

 

declare

begin

exception

when no_data_found then

...

when value_error then

...

when too_many-rows then

...

when others then

...

end;

SQLCODE : returns the error number associated with the exception.

SQLERRM : return the complete error message for the exception, including the error code.

 

declare

num1 number(3) := 10;

num2 number(3) := 8;

e1 EXCEPTION;

begin

if num1 > num2

--start our exception

RAISE e1;

end if;

exception

when no_data_found then

...

when e1 then

...

end;

 

note : by declaring your exceptions and then explicitly raising that exception with the raise command.

 

example of user defined exception

eg :(using exception block)

declare

e1 exception;

sal1 sal.emp%type;

begin

select sal into sal1 from emp

where ename='&ename';

if sal1 < 5000 then

raise e1;

end if;

exception

when e1 then

DBMS_OUTPUT.put_line('Less Salary');

when no_data_found then

DBMS_OUTPUT.put_line('employee not found');

end;

 

eg : (check exception without exception block)

declare

sal1 emp.sal%type;

begin

select sal into sal1 from emp

where ename='&ename';

if sal < 5000 then

raise_application_error(-20001, 'Less Salary');

end if;

end;

note : whenever you use raise_application_error, you must ensure the error code is bigger than 20001.

 

declare exception variable

raise exception

when e1 then

eg :

create table test1(a1 varchar2(10) primary key)

WAP to check if the same data is present

we cannot insert the same data

pre-defined / user-defined

eg :

create table test1(a1 varchar2(10) primary key)

 

declare

abc varchar2(10);

begin

insert into test1 values('&abc');

exception

when dup_val_on_index then

DBMS_OUTPUT.put_line('SAME DATA PRESENT');

end;

 

value_error

 

转载于:https://www.cnblogs.com/jilili/p/4399674.html