时间：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语句。
声明部分：包括类型、变量、游标
执行部分：完成功能而编写的SQL语句或是PL/SQL代码块
异常处理部分：
函数与过程在创建的形式是有些相似，也是编译后放在内存中供用户使用，只不过调用函数时要用表达式，而不像过程只需要用过程名。另外，函数必须有一个返回值，而过程则没有。
函数用于返回特定的数据，当建立函数时，在函数头部必须包含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;
语法：
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;
/
1．函数必须有返回值，而过程可以没有；
2．函数和过程在java中调用的方式不一样；
3．java中调用oracle函数可以在select语句中直接调用，如：select 自定义的函数名(参数) from 表;过程则是使用CallableStatement完成调用。
游标：用来处理使用select语句从数据库中检索到的多行记录的工具
分类：
1） 显示游标
返回多条记录时，使用显示游标逐行读取
2） 隐式游标
PL/SQL自动为DML语句创建隐式游标，包含一条返回记录
|
属性名称 |
说 明 |
|
%found |
用于检验游标是否成功，通常在FETCH语句前使用，当游标按照条件查询出一条记录时，返回true |
|
%isopen |
判断游标是否处于打开状态，试图打开一个已经打开或者已经关闭的游标，将会出现错误 |
|
%notfound |
与%found的作用相反，当按照条件无法查询到记录时，返回true |
|
%rowcount |
循环执行游标读取数据时，返回检索出的记录数据的行数 |
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.当循环结束后，游标会总动关闭
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操作建立触发器）。
l DML触发器（insert 、delete 、update触发器）
l DDL触发器（create table、create view 、drop table……）
l 系统触发器（与系统相关的触发器，比如用户登录、退出、启动数据库、关闭数据库）
语法：
Create [or replace] trigger trigger_name –触发器名字
{before| after} —在操作前还是后
{insert | delete | update} –进行增删改查的哪一种操作
On [schema.] table_name --在哪个用户的哪张表中
[for each row] --每一行
Begin
Trigger_body;
End;
after—是在记录操纵之后触发，是先完成数据的增删改，再触发，触发的语句晚于监视的增删改操作;
before—是在记录操纵之前触发，是先完成触发，再增删改，触发的语句先于监视的增删改。
案例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;
系统触发器是指基于Oracle事件（例如用户登录logon、logoff和数据库的启动和关闭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;
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语句时，如果没有手动提交，在退出控制台的时候也会自动提交。
禁止触发器
指让触发器临时失效。
Alter trigger 触发器名 disable;
激活触发器
Alter trigger 触发器名 enable;
删除触发器
Drop trigger 触发器名;
oracle-PL/SQL2
标签：修改 tin 模式 creat 记录 alter info 异常处理 number