oracle学习记录之十九

例外 

declare

 v_ename emp.ename%type;
  begin
  select ename into v_ename from emp where empno=&no;
  dbms_output.put_line(v_ename);
 exception
 when no_data_found then
 dbms_output.put_line('编号不存在');
 end;

 /

create or replace  procedure mypro13(no number) is

v_sal emp.sal%type;

begin 

select sal into v_sal from emp where empno=no;

case

when v_sal<1000 then

null;

when v_sal<2000 then

null;

end case;

exception

when case_not_found then

dbms_output.put_line('case_not_found);

end;

declare
v_ename varchar2(10);
begin
select ename into v_ename from emp;
exception
when too_many_rows then
dbms_output.put_line('too_many_rows');
end;
/

--自定义例外

create or replace procedure expro1(no number) is

begin

update emp set sal=sal+100 where empno=no;

end;

create or replace procedure expro1(no number)  is

myex exception;

begin

update emp set sal=sal+100 where empno=no;

--sql%notfound 这是表示没有update

--raise myex；触发myex

if sql%notfound then

raise myex;

end if;

exception

when myex then

dbms_output.put_line('没有更新任何用户');

end;

--视图

create view myview as
select * from emp where sal<1000;

create view myview1 as

select empno, ename, dname from emp, dept where emp.deptno=dept.deptno;

create or replace view 视图名 as

select语句

 [with read only]

drop view 视图名