oracle过程、块、触发器、异常、控…

输出用dbms_output.put_line(‘输出的内容’);

特别说明：在默认情况下，输出的内容是默认不输出，需要设置set serveroutput on;

 

块案例：根据用户输入的雇员编号，显示出该雇员的名字

declare

v_ename varchar2(8);

begin

  select ename into v_ename from emp where empno=& empno;

  dbms_output.put_line('雇员是:'||v_ename);

  end;

  /

                       

      &表示要接收从控制台输入的变量

      ||表示把两个字符串拼接

创建过程案例：

create or replace procedure pro10(in_empno number)is

v_ename varchar2(8);

begin

  select ename into v_ename from emp where empno=in_empno;

  dbms_output.put_line('雇员是:'||v_ename);

  end;

  /                 

调用方式：exec pro10（雇员编号）；

 

处理异常，比如输入的编号不存在：

create or replace procedure pro10(in_empno number)is

v_ename varchar2(8);

begin

  select ename into v_ename from emp where empno=in_empno;

  dbms_output.put_line('雇员是:'||v_ename);

  exception

    when no_data_found then

      dbms_output.put_line('没有这个编号存在');

  end;

  /

 

异常的基本语法：

eception

when 异常的名称 then

对异常进行处理的代码

when 异常的名称2 then

对这种异常进行处理的代码

 

 

异常处理作用：

1.       可以给出捕获异常，并给出明确提示

2.       有时可以以利用异常来处理业务处理

 

 

自定义异常：

set serveroutput on;

declare

exp exception;

a number :=9;

begin

if(a>0) then raise exp;

end if;

exception

when exp then dbms_output.put_line('这是自定义异常！');

end;

 

   oracle的过程，可以指定参数要输入的参数还是输出的参数，基本语法如下：

   create procedure 过程名（变量名 in 变量的类型,变量名 out 变量类型）is

   定义变量

begin

执行的语句

end

                               

当编写过程时，可以输入show error来查看具体错误

 

 

案例：创建修改员工的工资的过程

create or replace procedure pro(in_ename in varchar2,in_sal in number)is

begin

  update emp set sal=in_sal where ename=in_ename;

  end;

/

函数：

基本语法：

crate function 函数名（参数）

return 返回的数据类型 is

定义变量

begin

执行语句

end

创建函数案例：

create or replace function test(in_v_ename varchar2)

return number is

v_sal number;

begin

  select (sal+500)*2 into v_sal from emp where ename=in_v_ename;

  return v_sal;

  end;

/

如何调用函数

1.       在控制台调用：select 函数名（实际参数）from dual

2.       在java程序中调用

 

 

函数和过程的区别：

1.       函数必须有返回值，而过程可以没有

2.       函数和过程在java中调用方式不一样

函数：select 函数名（列）from 表

过程：CallableStatement 去调用

 

 

包：

使用包可以更好的管理自己写的函数、过程

包的基本语法：

create or replace package 报名 is

声明函数

function 函数名（参数1）return 返回类型

声明过程

procedure 过程名（参数）；

end

 

 

 

  包体：

  包体是用于把包中声明的函数或者是过程实现的数据对象。

  基本语法：

  create or replace package body 包名 is

  --实现过程

   create procedure 过程名（参数列表）is

--定义变量

   begin

--执行的语句

end；

--实现的函数

create function 函数名（参数列表）return 数据类型

is

--定义变量

begin

--执行

end；

end；

细节：

1.       包体中要实现的方法或者是过程，应当在包中声明

2.       在调用包中的某个方法的时候，需要这样调用

（1）       控制台：

exec 方案名.报名.过程名（参数值）

call 方案名.包名.函数名（参数值）

（2）       在java中去调用包下的函数或是过程应当这样：

String sql=“{call 方案名.包名.过程名（参数）}”

String sql=“{select 方案名.包名.函数名（参数）} from 表名”

 

 

 

触发器：

触发器是一个隐含执行的过程。他不是有程序员或者dba来显示调用，而是因为某个操作引发执行的。

 

1.       输入一个员工号，输出该员工的姓名、薪金：

create or replace procedure proc2(v_in_empno number)is

v_ename emp.ename%type;

v_sal emp.sal%type;

begin

  select ename,sal into v_ename,v_sal from emp where empno=v_in_empno;

  dbms_output.put_line('员工的姓名是'|| v_ename || '工资是'|| v_sal);

  end;

  /

              

2.     接收一个员工号，输出该员工所在部门的名称：

create or replace procedure proc3(v_in_empno number)is

--定义游标

type test_ref is ref cursor;

--定义游标变量

v_test_ref test_ref;

--定义普通变量

v_dname dept.dname%type;

v_empno emp.empno%type;

begin

  --打开游标

open v_test_ref for select emp.empno,dept.dname into v_empno,v_dname from emp,dept where emp.empno=v_in_empno;

--循环

loop

  --取出当前游标所指向的行

fetch v_test_ref into v_empno,v_dname;

--判断当前游标所指向的行是否为空，为空就退出

exit when v_test_ref%notfound;

  dbms_output.put_line('员工的编号为'|| v_empno || '所在部门的名称是'|| v_dname);

  --结束循环

  end loop;

  end;

  /

 

 

pl/sql的控制语句

1.       条件语句：

分为三种：if—then/if—then—else/if—then---elsif---else    

（1）       if—then

基本语法：

if 条件表达式  then

--执行语句

--执行语句

end if；

（2）       if—then—else

二重分支：

基本语法：

if 条件表达式then

--执行语句

else

--执行语句

end if；

        

 

(1)     多重分支

基本语法：

if  表达式  then

--执行语句

elsif 条件表达式 then

--执行语句

这里可以有多个elsif

else【else可以没有】

--执行语句

end if

 

 

                                              

 

          循环控制语句

           基本语法：

           loop

            --执行语句

           exit when 条件表达式

        end loop；

 

     案例：

declare

age number(3);

begin

age:=1;

loop

age:=age+1;

dbms_output.put_line('age='||age);

exit when(age>5);

end loop;

end; 

 

 

 

 

    

使用while循环

基本语法：

while 条件表达式 loop

--执行语句

end loop

 

案例：

declare

age number(3);

begin

age:=1;

while(age<68)loop

age:=age+1;

dbms_output.put_line('age='||age);

end loop;

end;

 

                  

pl/sql注意：不能在is到begin之间不能对变量赋值，除非在定义变量时赋值否则就需要在begin和end之间赋值

不能对输入参数的值进行赋值

视图：

视图是oracle又一种数据对象，视图的主要用处是简化操作，提高安全，满足不同用户的查询需求，视图不是一个真正存在的物理表，它是根据根据别的表动态生成的

 

创建视图的基本语法：

create view 视图名 as select语句【with read only】

创建或修改视图：

crate or replace view 视图名 as select语句【with read only】

删除视图：

drop view 视图名

 

创建视图案例：SQL> create view viewemp as select ename,job,sal from emp;

注意：如果后面加了with read only则表示该视图只能读，而不能进行其他操作；

触发器的分类：

1.       dml（insert  delete  update）触发器

2.       ddl（create table；create view..）触发器

3.       系统触发器(与系统相关的触发器，比如用户登陆，退出，启动数据库，关闭数据库)

 

 

     创建触发器的基本语法：

create or replace trigger 触发器的名字

before（在之前触发）/after(在之后触发) 操作（insert/update/select等）on

方案名.表名

begin

 执行语句

end

 

                案例：当添加一条数据后提示‘添加数据成功’：

         SQL> create or replace trigger rep1

  2  after insert on

  3  scott.test2

  4  begin

  5    dbms_output.put_line('数据添加成功');

  6  end;

  7  /

 

 

案例：当执行对emp表的删除、修改、添加的时候触发这个触发器，把操作写入到另外一张表中

--创建记录表

create table log_option(who varchar2(20),times date,options varchar2(20));

--创建触发器

create or replace trigger trigger1 before insert or update or delete on scott.emp

declare

v_log varchar2(20);

begin

  if inserting then v_log:='insert';

  elsif updating then v_log:='update';

  elsif deleting then v_log:='delete';

  end if;

  insert into log_option values(user,sysdate,v_log);

end;

 

行级触发器：

create or replace trigger 触发器的名字

before（在之前触发）/after(在之后触发) 操作（insert/update/select等）on

方案名.表名

for each row—表示这是一个行级触发器

begin

 执行语句

end

 

 

行级触发器案例2：

create or replace trigger trigger3 before insert or update or delete of t_id on t_trigger for each row

begin

select seq_users.nextval into :new.t_id from dual;

end;

 

dml触发器

案例：为了禁止工作人员在休息日改变员工信息，开发人员可以建立before语句触发器，从而实现数据的安全：

         create or replace trigger tri2

  before insert or update or delete on

    scott.test2

   begin

    case

      when inserting then

        RAISE_APPLICATION_ERROR(-20001,'工作日不能添加员工');

      when updating then

         RAISE_APPLICATION_ERROR(-20001,'工作日不能修改员工');

         when deleting then

            RAISE_APPLICATION_ERROR(-20001,'工作日不能删除员工');

            end case;

end;

/

特别说明：RAISE_APPLICATION_ERROR这个过程是oracle提供的。可以传入两个参数，第一个是自定的错误号-20000~-20999之间，第二个参数是提示信息

 

如何确保修改时新工资不能低于原有工资：

:new.sal<:old.sal

 

编写一个触发器，控制员工的新工资不能低于原来的工资，同时也不能高出额原来工资的20%：

create or replace trigger tri3

before update on

scott.emp

for each row

begin

  case

     when  :new.sal>:old.sal then

          raise_application_error(-20003,'新工资不能高于旧的工资的20%');

    when  :new.sal<:old.sal*0.02 then

    raise_application_error(-20002,'新工资不能低于原来的工资');

    end case;

  end;

/

ddl触发器：

ddl触发器主要用于记录在oracle中发生的ddl操作

基本语法：

create or replace trigger 触发器名

after ddl on 方案名.schema

begin

执行语句

end

 

在oracle中dml语句需要手动提交（如果没有手动提交，当你退出控制台时候，会自动提交），ddl是自动提交

oracle的例外处理：

基本语法：

begin

--执行语句

exception

--捕获异常

when 例外名称 then

--执行语句

when 例外名称 then

--执行语句

when others then

--执行语句