--1 try to execute one block without exception
declare
v_first_name employees.first_name%type;
begin
select first_name into v_first_name
from
employees
where employee_id=1; -- there is no emp_id=1
end;
-------------------------------------------------
--2 the solution is to make exception
declare
v_first_name employees.first_name%type;
begin
select first_name into v_first_name
from
employees
where employee_id=1; -- there is not emp_id=1
dbms_output.put_line(v_first_name);
exception
when no_data_found then
dbms_output.put_line('The query doesnt retrieve any record');
end;
---------------------------------------------------
--3 many exceptions
declare
v_emp_id employees.employee_id%type;
begin
select employee_id into v_emp_id
from
employees
where first_name=&name; --try 'xyx' then try 'John' then try 1
exception
when no_data_found then
dbms_output.put_line('The query doesnt retrieve any record');
when too_many_rows then
dbms_output.put_line('The query retrieve more than one record');
when others then
dbms_output.put_line('Other ERROR');
end;
----------------------
--this code is wrong
declare
v_first_name employees.first_name%type;
begin
for i in 99..102
loop
select first_name into v_first_name
from
employees
where employee_id=i;
dbms_output.put_line(i||' ' ||v_first_name);
end loop;
exception
when no_data_found then
null;
end;
-------
declare
v_first_name employees.first_name%type;
begin
for i in 99..102
loop
begin
select first_name into v_first_name
from
employees
where employee_id=i;
dbms_output.put_line(i||' ' ||v_first_name);
exception
when no_data_found then
null;
end;
end loop;
end;
---------------------------------------------------------------------------------------------------------
desc departments;
insert into departments(DEPARTMENT_ID ,DEPARTMENT_NAME)
values (1,null );
--SQL Error: ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_NAME")
begin
insert into departments(DEPARTMENT_ID ,DEPARTMENT_NAME)
values (1,null );
end;
----------------------------------------------
declare
e_insert exception;
pragma exception_init(e_insert,-01400);
begin
insert into departments(DEPARTMENT_ID ,DEPARTMENT_NAME)
values (1,null );
exception
when e_insert then
dbms_output.put_line('insert failed');
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
when others then
null;
end;
------------------------------------------------
--now the way for creating this code is wrong
-- the update will not executed when there is exp in the first insert
declare
e_insert exception;
pragma exception_init(e_insert,-01400);
begin
insert into departments(DEPARTMENT_ID ,DEPARTMENT_NAME)
values (1,null );
update employees
set employee_id='ss'
where employee_id=100;
exception
when e_insert then
dbms_output.put_line('insert failed');
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;
------------------------------------------------------------
--this is the correct code
declare
e_insert exception;
pragma exception_init(e_insert,-01400);
begin
begin
insert into departments(DEPARTMENT_ID ,DEPARTMENT_NAME)
values (1,null );
exception
when e_insert then
dbms_output.put_line('insert failed');
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;
begin
update employees
set employee_id='ss'
where employee_id=100;
exception
when others then
dbms_output.put_line('update failed');
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;
end;
---------------------------------------------------------------------------------------------------------------
select * from employees
where employee_id=1;
----------------------
declare
v_employee_id number:=1;
begin
update employees
set salary=20000
where employee_id=v_employee_id;
dbms_output.put_line(sql%rowcount);
end;
--------------------------------------
declare
v_employee_id number:=1;
e_invalid_no exception;
begin
update employees
set salary=20000
where employee_id=v_employee_id;
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
if sql%notfound then
raise e_invalid_no;
end if;
commit;
exception
when e_invalid_no then
dbms_output.put_line('invalid emp ID');
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;
------------------------
declare
v_employee_id number:=1;
---e_invalid_no exception;
begin
update employees
set salary=20000
where employee_id=v_employee_id;
if sql%notfound then
---raise e_invalid_no;
raise_application_error(-20000, 'invalid emp ID');
end if;
commit;
end;
----------------------------------------------------------------------------------------
select * from EMPLOYEES
where EMPLOYEE_ID=15154;
select sum(salary) from employees
where DEPARTMENT_ID=888;
Declare
v_sum_sal number;
begin
select sum(salary) into v_sum_sal
from employees
where DEPARTMENT_ID=&dno;
DBMS_OUTPUT.PUT_LINE('the sum is '||v_sum_sal);
DBMS_OUTPUT.PUT_LINE(sql%rowcount);
EXCEPTION
when no_data_found then
DBMS_OUTPUT.PUT_LINE('no data found');
end;
--------------------------
Declare
v_sum_sal number;
v_er exception;
begin
select sum(salary) into v_sum_sal
from employees
where DEPARTMENT_ID=&dno;
if v_sum_sal is not null then
DBMS_OUTPUT.PUT_LINE('the sum is '||v_sum_sal);
DBMS_OUTPUT.PUT_LINE(sql%rowcount);
else
raise v_er;
end if;
EXCEPTION
when v_er then
DBMS_OUTPUT.PUT_LINE('no data found');
end;
------------------
Declare
v_sum_sal number(2);
v_er exception;
begin
begin
select sum(salary) into v_sum_sal
from employees
where DEPARTMENT_ID=&dno;
if v_sum_sal is not null then
DBMS_OUTPUT.PUT_LINE('the sum is '||v_sum_sal);
DBMS_OUTPUT.PUT_LINE(sql%rowcount);
else
raise v_er;
end if;
EXCEPTION
when v_er then
DBMS_OUTPUT.PUT_LINE('no data found');
end;
EXCEPTION
when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;
No comments:
Post a Comment