当前位置:Gxlcms > 数据库问题 > oracle-PL/SQL2

oracle-PL/SQL2

时间:2021-07-01 10:21:17 帮助过:19人阅读

第一个案例(创建一个简单的存储过程)

例如:向emp表中插入数据(111,’张三’)

create procedure pro1 is

begin

insert into emp(empno, ename)values(111,‘张三‘);

end;

调用存储过程:

exec pro1

带参数的存储过程:

删除emp表中指定员工编号的员工信息。

create procedure pro2(in_empno number) is

begin

delete from emp where empno = in_empno;

end;

调用:

exec pro2(111);

练习:建立一个存储过程,根据员工编号,显示员工的薪资和所在部门的平均工资

create or replace procedure pro1(v_in_empno in number,

v_out_mysal out number, v_out_avgsal out number)

is

begin

  select sal into v_out_mysal from emp where empno=v_in_empno;

  select avg(sal) into v_out_avgsal from emp

where deptno=(select deptno from emp where empno=v_in_empno);

end;

 

调用执行:

declare

  v_mysal number;

  v_avgsal number;

begin

  pro1(7902,v_mysal,v_avgsal);

  dbms_output.put_line(v_mysal||‘  ‘||v_avgsal);

end;

二 函数

函数一般用于计算和返回一个值,可以经常需要进行的计算写成函数。函数的调用是表达式的一部分,而调用过程是一条PL/SQL语句。

2.1 函数的结构

声明部分:包括类型、变量、游标

执行部分:完成功能而编写的SQL语句或是PL/SQL代码块

异常处理部分:

函数与过程在创建的形式是有些相似,也是编译后放在内存中供用户使用,只不过调用函数时要用表达式,而不像过程只需要用过程名。另外,函数必须有一个返回值,而过程则没有。

2.2 创建函数

函数用于返回特定的数据,当建立函数时,在函数头部必须包含return子句,而在函数体内必须包含return语句返回的数据。

语法:

create  or  replace  function 函数名(参数1,...)

return 数据类型 is

       定义变量;

begin

       执行语句;

end;

/

举例:定义函数,输入工作名,返回负责该工作的员工数

--创建函数

CREATE OR REPLACE Function GETCOUNT(Major IN varchar2 )

--声明返回类型

return number is

f_count number;

BEGIN

--使用INTO 语句将结果赋值给变量

select count(*) into f_count from students where major=’Major’ ;

--使用RETURN 语句返回

return f_count;  --返回结果

END;

 函数调用

方式一:

--调用函数--

DECLARE

--声明变量接收函数的返回值

          v_count number;

BEGIN

           v_count:=GETCOUNT(‘MUSIC‘);

            Dbms_Output.put_line(v_count);

END;

方式二:

var 变量名 变量类型

call 函数名(参数值,...) into :变量名;

print 变量名

方式三:

select 函数名(参数,...) from dual;

2.3 删除函数

语法:

DROP FUNCTION 函数名

案例

请编写一个函数,可以接收用户名并返回该用户的年薪。

create function inName_outSal(v_in_name varchar2)

return number is

        v_annual_sal number;

begin

       select (sal+nvl(comm,0))*13 into v_annual_sal from emp where ename=v_in_name;

       return v_annual_sal;

end;

/

2.4 函数和过程的区别

技术分享图片

 

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

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

3.java中调用oracle函数可以在select语句中直接调用,如:select 自定义的函数名(参数) from 表;过程则是使用CallableStatement完成调用。

 

三 游标

游标:用来处理使用select语句从数据库中检索到的多行记录的工具

分类:

1)    显示游标

返回多条记录时,使用显示游标逐行读取

2)    隐式游标

PL/SQL自动为DML语句创建隐式游标,包含一条返回记录

3.1 游标的属性

属性名称

说  明

%found

用于检验游标是否成功,通常在FETCH语句前使用,当游标按照条件查询出一条记录时,返回true

%isopen

判断游标是否处于打开状态,试图打开一个已经打开或者已经关闭的游标,将会出现错误

%notfound

与%found的作用相反,当按照条件无法查询到记录时,返回true

%rowcount

循环执行游标读取数据时,返回检索出的记录数据的行数

3.2 游标的使用

1)   游标的声明

语法:

CURSOR cursor_name [ ( parameter [ , parameter]……)]

[ RETURN return_type ] IS selectsql

说明:

CURSOR:用于声明一个游标

parameter:可选参数,用于指定参数类型、模式等

return:可选,指定游标的返回类型

selectsql:需要处理的select语句,不能含INTO子句

2)打开游标

语法: open test_cursor  使用OPEN语句开启一个游标

3)提取游标

语法:

FETCH cursor_name INTO variable_list

说明:

使用FETCH语句实现对游标内容的读取

variable_list必须与从游标提取的结果集类型相同

4)关闭游标

语法:

close cursor_name

说明:

使用CLOSE语句关闭一个游标

关闭游标后,所有资源都将被释放,且不能再次被打开

示例:游标的使用

1)读取一条数据

例如:查询员工标号为:7369的员工姓名和薪水;

DECLARE

     v_ename varchar2(20);

     v_sal emp.sal%type;

     --声明一个游标

     CURSOR c_emp is select ename, sal from emp where empno=7369 ;

BEGIN

     --打开游标

    OPEN c_emp;

    - - 判断游标是否返回记录

    if c_emp %NOTFOUND THEN

           dbms_output.put_line(‘没有找到相应的记录‘);

    else

    - -从游标中读取记录

          fetch c_emp into v_ename, v_sal;

          dbms_output.put_line(v_ename ||‘薪水为‘|| v_sal);

    end if;

   CLOSE c_emp;

END;

2)读取多条记录:

使用FETCH语句只能读取一行记录,那么如何读取多行记录呢?

读取多行记录时,可以采用循环的方式:LOOP循环、while循环和FOR循环

例如: 查询薪水小于3000的所有人的姓名和薪水?

适用for 循环,自动打开游标,而无需使用open语句

DECLARE

        v_ename varchar2(20);

        v_sal emp.sal%type;

        CURSOR c_emps is select ename, sal from emp where sal<=3000 ;

BEGIN

       FOR c_emp IN c_emps LOOP

         V_ename:=c_emp.ename;

         V_sal:=c_emp.sal;

         dbms_output.put_line(‘员工姓名:‘||v_ename||‘薪水为‘||v_sal);

       END LOOP;

END;

说明:

1.使用for循环时,自动打开游标,而无需使用OPEN语句

2.PL/SQL会自动对变量进行隐式声明

3.当循环结束后,游标会总动关闭

3.3 练习

l  需求说明:

l  编写PL/SQL语句,实现使用游标从表中读取出‘SMITH’的相关信息(姓名,编号,部门名称)

l  提示:

l  使用OPEN打开游标

l  对游标内容进行判断

l  使用FETCH进行读取

l  关闭游标

declare

    v_ename emp.ename%type;

    v_deptno   emp.deptno%type;

    v_dname  dept.dname%type;

--声明一个游标

    cursor c_emp is select ename,emp.deptno,dname

from emp,dept

where ename=‘SMITH‘ and emp.deptno=dept.deptno;

 begin

--打开游标

   open c_emp;

--判断游标是否返回记录

   if c_emp%NOTFOUND then

     dbms_output.put_line(‘没有找到相应的记录!‘);

     else

       --从游标中读取记录

       fetch c_emp into v_ename,v_deptno,v_dname ;

       dbms_output.put_line(v_ename||‘部门编号:‘||v_deptno||‘部门名称:‘||v_dname);

       end if;

--关闭游标

   close c_emp;

   end;

 

l  需求说明:

l  在上一练习基础上,实现读取‘smith’所在部门员工信息并显示

l  提示:使用FOR循环实现游标的循环读取

create or replace procedure p3 is

   v_ename varchar2(20);

   v_deptno number(4);

   v_dname varchar2(20);

  

   cursor c_emps is select ename,dept.deptno,dname

                    from emp ,dept

                    where emp.deptno=dept.deptno

                          and emp.deptno = (select deptno

                                            from emp

                                            where ename=‘SMITH‘);

   begin

     for c_emp in c_emps loop

       v_ename:= c_emp.ename;

       v_deptno:=c_emp.deptno;

       v_dname:= c_emp.dname;

       dbms_output.put_line(v_ename||‘部门编号‘||v_deptno||‘部门名称:‘||v_dname);

       end loop;

    end;

 

四 触发器

先思考几个问题:

(1)当一个用户登录到Oracle时,在一张表中记录登录到Oracle的用户名和登录时间等信息,怎么办?

(2)禁止用户在星期天对某一张表进行删除操作,怎么办?

(3)当用户在删除一张表的时候,自动把删除的记录备份到另一张表里去……

很多关系型数据库都提供了一种技术,可以在用户进行某种操作的时候,自动地进行另外一种操作,我们把这种技术成为触发器技术。

触发器是指存放在数据库中,被隐含执行的存储过程,可以支持DML触发器,还支持基于系统事件(启动数据库、关闭数据库、登录数据库以及DDL操作建立触发器)。

4.1 触发器的分类

l  DML触发器(insert 、delete 、update触发器)

l  DDL触发器(create table、create view 、drop table……)

l  系统触发器(与系统相关的触发器,比如用户登录、退出、启动数据库、关闭数据库)

4.2 创建触发器

语法:

Create [or replace] trigger trigger_name   –触发器名字

{before| after}                        —在操作前还是后

{insert | delete | update} –进行增删改查的哪一种操作

On [schema.] table_name               --在哪个用户的哪张表中

[for each row]                        --每一行   

Begin

Trigger_body;

End;

 

after—是在记录操纵之后触发,是先完成数据的增删改,再触发,触发的语句晚于监视的增删改操作;

before—是在记录操纵之前触发,是先完成触发,再增删改,触发的语句先于监视的增删改。

4.3 DML触发器

案例1在某张表(u1)添加一条数据的时候,提示“ 添加了一条数据”。

create table u1(id number,name varchar2(20));

create or replace trigger t1

after insert on u1

begin

  dbms_output.put_line(‘你在u1表中插入了一条数据!‘);

  end;

insert into u1 values(1,‘张三‘);

 

案例2在某张表(emp)修改多条数据的时候,提示多次“修改了数据”。

如果这样写,只会输出一句话。(语句级的写法)

create or replace trigger t1

after update on scott.emp

begin

    dbms_output.put_line(‘修改了一条数据‘);

end;

--执行更新操作

update emp set sal = sal +10;

应该这样写,才会输出多条语句:(变为行级写法)

create or replace trigger t1

after update on scott.emp

for each row--代表行级触发器

begin

    dbms_output.put_line(‘修改了一条数据‘);

end;

--执行更新操作

update emp set sal = sal +10;

 

案例3

为了禁止工作人员在休息日删除员工的信息,开发人员可以建立before语句触发器,从而实现数据的安全。

create or replace trigger t1

before delete on scott.emp

begin

if to_char(sysdate,‘day‘)in(‘星期六‘,‘星期日‘) then

     dbms_output.put_line(‘休息日不能删除员工信息!‘);

     raise_application_error(-20001,   ‘对不起,休息日不能删除员工!‘);

end if;

end;

--执行删除操作,抛出错误信息。

delete from emp where empno  =7499;

 

说明:

1)raise_application_error ( error_number number,  error_msg varchar2)系统已经定义好的一个存储过程,让系统报错。

2)Error_number [自定义]从-20000到-20999之间,这样就不会与Oracle的任何错误代码发生冲突。Error_msg_in[自定义]的长度不能超过2K,否则截取2K,是错误的提示信息;

使用条件谓词:

当触发器中同时包含多个触发事件(insert 、update、delete)时,为了在触发器代码中区分具体的触发事件,可以使用三个条件:inserting、updating、deleting。

案例3用户操作表u1时,给出提示,明确提示用户是进行的insert、update还是delete操作。

create or replace trigger t1

before delete or update or insert on scott.u1

begin

case

when inserting then

         dbms_output.put_line(‘您正在进行插入操作!‘);

when updating then

         dbms_output.put_line(‘您正在进行修改操作!‘);

when deleting then

         dbms_output.put_line(‘您正在进行删除操作!‘);

end case;

end;

--执行插入操作

insert into u1(empno, ename)values(1,‘张三‘);

--删除操作

delete from emp where empno =7499;

--更新操作

update emp set ename =‘张三‘where empno =7499;

 

 

4.4 :old和 :new关键字

思考:

当触发器被触发时,要使用被插入、删除或修改的记录中的列值,有时要使用操作前、后列的值。

:old修饰符访问操作完成前列的值

:new 修饰符访问操作

 技术分享图片

 

案例4在修改emp表雇员的薪水时,显示雇员工资修改前和修改后的值。如何确保在修改员工工资不能低于原有工资。

create or replace trigger t1

before update on scott.emp

for each row

begin

if :new.sal < :old.sal then

 dbms_output.put_line(‘更新后的工资值小于更新前的值!不能这样操作!‘);

 raise_application_error(-20005,‘更新后的工资值 小于更新前的值!不能这样操作!‘);

else

 dbms_output.put_line(‘更新前工资为:‘||:old.sal ||‘更新后工资为:‘||:new.sal);

endif;

end;

--执行update操作

update emp set sal =2 where empno =7499;

update emp set sal =7000 where empno =7499;

 

案例5编写一个触发器,保证当用户在删除一张表(emp)记录的时候,自动将删除的记录备份到另一张表(u1)中。

create or replace trigger t2

before delete on scott.emp

for each row

begin

insert into u1(id, ename)values(:old.empno,:old.ename);

end;

 

delete from emp where empno =7499;

 

 

4.5 系统触发器

系统触发器是指基于Oracle事件(例如用户登录logonlogoff和数据库的启动关闭startup、shutdown)所建立的触发器,通过使用系统事件触发器,提供了跟踪系统或者是数据库变化的机制;

下面介绍一些常用的系统事件属性函数,和建立各种事件触发器的方法,在建立系统事件触发器时,我们需要使用事件属性函数,常用的时间属性函数如下:

ora_client_ip_address  //返回客户端的ip

ora_database_name   //返回数据库名

ora_login_user       //返回登录用户名

ora_sysevent        //返回触发触发器的系统事件名

ora_des_encrypted_password  //返回用户des(md5)加密后的密码

案例:建立登录或者退出触发器

语法:

create or replace trigger 触发器名字

after [before] logon [logoff] on database

begin

--执行语句……;

End;

 

案例:

完成登录触发器和退出触发器的创建。

为了记录用户的登录和退出事件,我们可以建立登录或者退出触发器。为了记录用户名称、时间、ip地址,我们首先建立一张信息表:

打开命令行窗口:

Sqlplus system/123456@orcl as dba;

--创建一张存储登录或者退出系统信息的表。

Create table log_table( username varchar2(20),

logon_time date,

logoff_time date, address varchar2(20));

--创建登录触发器:

Create or replace trigger mytrigger1

After logon on database

Begin

Insert into log_table(username, logon_time , address)

values(ora_login_user, sysdate, ora_client_ip_address);

End;

--创建退出触发器:

Create or replace trigger

Before logoff on database

Begin

Insert into log_table(username, logoff_time, address)

values(ora_login_user, sysdate , ora_client_ip_address);

End;

 

4.6 DDL触发器

DDL简单说就是我们平常使用的create、alter和drop这些数据定义语句。

语法:

create or replace trigger 触发器的名字

before|After ddl|create|alter|drop  on 用户名.schema—如scott.schema

Begin

--执行语句……;

End;

 

案例:

编写一个触发器,可以记录某个用户进行的DDL操作。

为了记录系统发生的ddl事件,应该建立一张表(my_ddl_record)用来存储相关的信息

注意:

需要使用system用户登录: 

conn system/manager as sysdba;

Create table my_ddl_record (

event varchar2(64),

username varchar2(64),

ddl_time date

);

--创建触发器

Create or replace trigger mytrigger2

After ddl on scott.schema

Begin

       Insert into my_ddl_record values(ora_sysevent, ora_login_user, sysdate);

End;

 

注意:在Oracle中,DML的事务需要手动提交,DDL语句不需要进行手动提交。我们在写DML语句时,如果没有手动提交,在退出控制台的时候也会自动提交。

4.7 触发器管理

禁止触发器

指让触发器临时失效。

Alter trigger 触发器名 disable;

激活触发器

Alter trigger 触发器名 enable;

删除触发器

Drop trigger 触发器名;

oracle-PL/SQL2

标签:修改   tin   模式   creat   记录   alter   info   异常处理   number   

人气教程排行