玩转Oracle第6讲

 

 

 

 

 

 

create or replace procedure sp_pro6(spName varchar2) is

--定义 :=表示赋值

v_num number:=11;

begin

while v_num<=20 loop

--执行

     insert into users1 values(v_num,spName);

     v_num:=v_num+1;

end loop;

end;

 

 

 

 

* declare

v_sal emp.sal%type;

v_ename emp.ename%type;

begin

select ename,sal into v_ename,v_sal

from emp where empno=&no;

if v_sal<3000 then

update emp set comm=sal*0.1 where ename=v_ename;

else

null;

end if;

end;

 

 

 

 

 

create or replace package testpackage  AS

 TYPE test_cursor is ref cursor;

end testpackage;

--创建过程

CREATE OR REPLACE PROCEDURE TESTC

(myno in number,p_cursor out testpackage.test_cursor) IS

BEGIN

    OPEN p_cursor FOR SELECT * FROM emp where deptno=myno;

END TESTC;

 

 

 

 

 

 

--cast_not_found案例

create or replace procedure sp_pro6(spno number) is

v_sal emp.sal%type;

begin

select sal into v_sal from emp where empno=spno;

case

when v_sal<1000 then

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

when v_sal<2000 then

update emp set sal=sal+200 where empno=spno;

end case;

exception

when case_not_found then

dbms_output.put_line('case语句没有与'||v_sal||'相匹配的条件');

end;

--no_data_found案例

declare

v_sal emp.sal%type;

begin

select sal into v_sal from emp

where ename='&name';

exception

when no_data_found then

dbms_output.put_line('不存在该员工');

end;

--too_many_rows错误案例

declare

v_ename emp.ename%type;

begin

select ename into v_ename from emp;

exception

when too_many_rows then

dbms_output.put_line('返回了多行');

end;

 

 

 

如果仅仅是希望不出现异常提示，

可以，只写一个

when others then

dbms_output.put_line(‘错误’);