null; end; 子程序(subprogram)
有头的块
create or replace procedure a as begin null; end; 触发器(trigger)
有头且声明是以declare开头的 create or replace trigger a before insert on emp for each row declare v_num int; begin
select m.nextval into v_num from dual; :new.empno:=v_num; end; 语法:
1、PL/SQL程序可以使用的字符 算术运算符
+ - * / > < = ** 字母
A-Z, a-z 数字 0-9
符号
~ ! @ # $ % * ( ) _ - + = | : ; \" ' < > , . ? / ^ 间空符号
制表符 空格 回车符号
2、如何使用这些字符,声明程序标识符,即命名规则 最长30字符
开始字符以字母开头
可以包含字母、数字和$、_、#符号 不可以包含任何空白字符
不区分大小写
声明变量(„)每行只能定义一个 默认不可以使用保留字
3、如何为声明的标识符对应的变量、常量等提供值 值的提供形式就是字面值 数字:
直接使用 字符文本: 单引号引起 字符串:
单引号引起,若包含单引号,则在其后再添加一个单引号 日期:
单引号引起 布尔型:
直接使用,不能用单引号:true、false 布尔值不可以打印 declare
a number(2); b char(1); c varchar2(20); d date; e boolean; begin
a:=12;
b:='a';
c:='hello';
d:='12-2月-09'; e:=true;
dbms_output.put_line(a); dbms_output.put_line(b); dbms_output.put_line(c); dbms_output.put_line(d); --dbms_output.put_line(e); if e=true then
dbms_output.put_line('true'); end if; end;
4、注释和间空:
-- :单行注释 /*...*/ :段注释
间空 :提高程序的可读性
5、数据类型:基本和Oracle中表的数据类型相同 只是在Oracle中没有布尔型,PL/SQL中有布尔型 6、使用变量和常量
语法
Var_name [CONSTANT] TYPE [NOT NULL] [:=VALUE]; CONSTANT:常量
TYPE:数据类型
NOT NULL:非空约束 :=VALUE:设置初始值 TYPE:
已知数据类型(number\\char\\date\\boolean) 锚定类型
%type :数据类型依赖于锚定表达式 declare
v_empno number(4); --已知数据类型 v_deptno emp.deptno%type;
--锚定类型,变量的数据类型和emp表的deptno列的类型一样 7、表达式:(略) 8、常见PL/SQL操作 赋值:
:= :直接变量、字面值或表达式赋值
SELECT ... INTO „ FROM :通过查询赋值
在PL/SQL语言中select是一个赋值语句, 不是查询语句,所以必须和INTO联合使用
select ename into v_ename from emp where empno=7788;
把select ename from emp where empno=7788,这个查询返回的 值,赋给v_ename变量。 串联:
|| :类似于SQL Server 2000的”+” 9、流程控制
1、顺序
2、选择(分支) 3、循环 选择结构 1、if语句
1、if 条件表达式 then ... end if; 2、if 条件表达式 then ... else ... end if;
3、if 条件表达式 then ... elsif 条件表达式 then ... end if; 范例:向emp表插入新的数据, 诸如以下信息 :编号1234,姓名tom,部门20 declare
v_empno emp.empno%type; v_ename emp.ename%type; v_deptno emp.deptno%type; begin
v_empno:=&empno;
v_ename:=&ename; v_deptno:=&deptno;
insert into emp(empno,ename,deptno)
values(v_empno,v_ename,v_deptno); commit; end;
完善程序:判断是否有相同的员工编号 declare
v_empno emp.empno%type; v_ename emp.ename%type; v_deptno emp.deptno%type; v_count int; begin
v_empno:=&empno; v_ename:=&ename;
v_deptno:=&deptno;
select count(rowid) into v_count from emp where empno=v_empno; if v_count=0 then
insert into emp(empno,ename,deptno) values(v_empno,v_ename,v_deptno); commit; end if; end;
完善程序对用户的友好性 declare
v_empno emp.empno%type; v_ename emp.ename%type; v_deptno emp.deptno%type; v_count int; begin
v_empno:=&empno; v_ename:=&ename;
v_deptno:=&deptno;
select count(rowid) into v_count from emp where empno=v_empno; if v_count=0 then
insert into emp(empno,ename,deptno) values(v_empno,v_ename,v_deptno); commit;
dbms_output.put_line('新数据添加成功'); else
dbms_output.put_line('存在相同的员工编号'); end if;
end;
范例:根据以下规则修改员工的工资: 部门号 增加幅度
10 20%
20 15% 30 18%
通过输入用户的编号,自动修改 declare
v_increment number(6,2); v_deptno emp.deptno%type; v_empno emp.empno%type; v_sal emp.sal%type; v_count int; begin
v_empno:=&empno;
select count(*) into v_count from emp where empno=v_empno; if v_count <>0 then
select deptno,sal into v_deptno,v_sal from emp where empno=v_empno; if v_deptno=10 then
v_increment:=v_sal*0.2; elsif v_deptno=20 then
v_increment:=v_sal*0.15; elsif v_deptno=30 then
v_increment:=v_sal*0.18;
else
v_increment:=0; end if;
update emp set sal=sal+v_increment where empno=v_empno; commit;
dbms_output.put_line('数据操作成功'); else
dbms_output.put_line('员工编号不存在'); end if; end;
2、case语句
1、
case 条件表达式 when 结果1 then 操作1; ...
when 结果n then 操作n; esle
操作n+1; end case; 2、 case
when 条件表达式1 then 操作1; ...
when 条件表达式1 then 操作n; else
操作n+1;
end case;
范例:根据以下规则修改员工的工资: 部门号 增加幅度 10 20% 20 15%
30 18%
通过输入用户的编号,自动修改 declare
v_increment number(6,2); v_deptno emp.deptno%type; v_empno emp.empno%type; v_sal emp.sal%type; v_count int; begin
v_empno:=&empno;
select count(*) into v_count from emp where empno=v_empno; if v_count <>0 then
select deptno,sal into v_deptno,v_sal from emp where empno=v_empno; case v_deptno when 10 then
v_increment:=v_sal*0.2; when 20 then
v_increment:=v_sal*0.15; when 30 then
v_increment:=v_sal*0.18; else
v_increment:=0;
end case;
update emp set sal=sal+v_increment where empno=v_empno; commit;
dbms_output.put_line('数据操作成功'); else
dbms_output.put_line('员工编号不存在'); end if; end;
--CASE的第二种形式 declare
v_increment number(6,2); v_deptno emp.deptno%type; v_empno emp.empno%type; v_sal emp.sal%type; v_count int; begin
v_empno:=&empno;
select count(*) into v_count from emp where empno=v_empno; if v_count <>0 then
select deptno,sal into v_deptno,v_sal from emp where empno=v_empno; case
when v_deptno=10 then v_increment:=v_sal*0.2; when v_deptno=20 then v_increment:=v_sal*0.15; when v_deptno=30 then v_increment:=v_sal*0.18; else
v_increment:=0;
end case;
update emp set sal=sal+v_increment where empno=v_empno; commit;
dbms_output.put_line('数据操作成功'); else
dbms_output.put_line('员工编号不存在'); end if; end;
循环结构:
1、loop ... end loop 1、 loop 操作...
exit when 条件表达式 操作... end loop; 2、 loop
操作...
if 条件表达式 then exit; end if; 操作...
end loop;
2、for ... loop
for 索引变量 in 起始值 .. 结束值 loop 操作
end loop;
3、while ... loop
while 条件表达式 loop 操作
end loop; 计算1到100的累积 declare
v_result number(4):=0; v_increment number(3):=0; begin loop
v_result:=v_result+v_increment; v_increment:=v_increment+1; exit when v_increment>100; end loop;
dbms_output.put_line(v_result); end;
10、复合数据类型
1、record类型:多个成员变量,每一个成员变量的类型可以不同,类似于C中结构体 2、table类型:多个成员变量,类型相同,C中数组 3、游标类型及引用游标变量
record类型: 1、声明类型
type type_name is record(...) 2、声明变量
var_name type_name; declare
type mytype is record (v_empno emp.empno%type, v_ename emp.ename%type, v_deptno emp.deptno%type); emp_record mytype;
begin
select empno,ename,deptno into emp_record from emp where empno=7788;
dbms_output.put_line(emp_record.v_empno); dbms_output.put_line(emp_record.v_ename); dbms_output.put_line(emp_record.v_deptno); end;
RECORD特例:%rowtype declare
emp_row emp%rowtype;
begin
select * into emp_row from emp where empno=7788; dbms_output.put_line(emp_row.empno); dbms_output.put_line(emp_row.ename);
dbms_output.put_line(emp_row.deptno); end; Table类型:
declare
type mytype is table of varchar2(20) index by binary_integer; ename_tab mytype; begin
--ename_tab(1):='scott'
for i in 1 .. 14 loop
select ename into ename_tab(i) from
(select rownum id,emp.* from emp) t where t.id=i; end loop;
for i in 1 .. 14 loop
dbms_output.put_line(ename_tab(i)); end loop; end;
游标:
游标是一个指针,指向游标定义的SQL语句的结果集的内存上下文 游标本身不存储数据,只是定义一个指向内存区域的指针
游标数据默认是静态的,游标打开后,不能反映新的数据变化
游标对应的指针是一个自动处理的指针,对游标执行fetch后,游标会自动移动 游标分为:显式游标和隐式游标
显式游标:由用户自己声明和使用的游标,所有的操作必须用户参与 1、显式游标的基本操作: 1、声明游标
CURSOR 游标名 [(参数列表)] [RETURN 类型] IS
SQL语句
2、打开游标:open 游标名;
3、提取游标:fetch 游标名 into 变量列表或record类型; 4、关闭游标:close 游标名
范例:显示emp表的所有的员工名、工资和部门号 DECLARE
--计划需要的数据 CURSOR cur_1
IS
SELECT ename,sal,deptno FROM emp; v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
v_deptno emp.deptno%TYPE; BEGIN
--得到这些数据
OPEN cur_1;--如果游标已经打开,再次打开就会出错 --提取游标指针指向的当前行 FOR i IN 1 .. 14 LOOP
FETCH cur_1 INTO v_ename,v_sal,v_deptno;
dbms_output.put_line('-------------------------'); dbms_output.put_line(v_ename); dbms_output.put_line(v_sal); dbms_output.put_line(v_deptno); END LOOP; --关闭游标 CLOSE cur_1; END;
范例:根据输入的部门号,显示部门中的员工姓名和工资 --范例:根据输入的部门号,显示部门中的员工姓名和工资 DECLARE
CURSOR cur(v_deptno emp.deptno%TYPE) IS
SELECT * FROM emp WHERE deptno=v_deptno; emp_row emp%ROWTYPE; v_dno INT; BEGIN
v_dno:=&deptno; OPEN cur(v_dno);
FOR i IN 1 .. 14 LOOP
FETCH cur INTO emp_row; dbms_output.put_line('---------------');
dbms_output.put_line('姓名:'||emp_row.ename); dbms_output.put_line('工资:'||emp_row.sal);
END LOOP; CLOSE cur;
END; 5、游标属性: 对象.属性名
游标名%属性名
%isopen:如果游标被打开了,取值true
%found:如果最近一次fetch返回了新的数据,取值true
%notfound:如果最近一次fetch返回的数据没有变化(没有提取到数据),取值true
%rowcount:返回最近一次提取的行的编号。游标每一行自动添加编号,编号从1开始
DECLARE
--计划需要的数据 CURSOR cur_1 IS SELECT ename,sal,deptno FROM emp;
v_ename emp.ename%TYPE; v_sal emp.sal%TYPE;
v_deptno emp.deptno%TYPE; BEGIN
--得到这些数据
if not cur_1%isopen then OPEN cur_1;
end if;
--提取游标指针指向的当前行
loop
FETCH cur_1 INTO v_ename,v_sal,v_deptno; exit when cur_1%notfound;
dbms_output.put_line('------------'||cur_1%rowcount||'-------------'); dbms_output.put_line(v_ename); dbms_output.put_line(v_sal); dbms_output.put_line(v_deptno); END LOOP; --关闭游标 CLOSE cur_1; END; 2、隐式游标:
Oracle中select、insert、delete,update语句,在执行时Oracle会为之自动产生一个游标
游标名为SQL,此游标是由Oralce自动声明、打开、提取和关闭,用户不需要任何操作。 SQL%isopen:永远都是false,因为自动操作此属性无意义。 SQL%found=true SQL%notfound=false select:有返回的数据行 insert:是插入成功的 delete:删除成功的 update:更新成功的 SQL%rowcount=true
select:返回的数据行数 insert:是插入数据行数 delete:删除的数据行数 update:更新的数据行数
隐式游标广泛的应用于PL/SQL编程中,用于检查DML操作的状态 --通过输入岗位名称,工资的增量,修改相应岗位的员工的工资 declare
v_job emp.job%type; v_increment emp.sal%type; begin
v_job:=&job;
v_increment:=&increment;
update emp set sal=sal+v_increment where job=upper(v_job); if SQL%found then
dbms_output.put_line('更新成功,共更新了'||SQL%rowcount||'行'); commit; else
dbms_output.put_line('没有成功的更新任何数据'); end if; end;
3、引用游标变量(ref cursor):类似于record和table类型。 1、声明类型
TYPE type_name is ref cursor; 2、声明变量
var_name type_name;
3、Oracle内部已经内置了一个类型:sys_refcursor 是ref cursor类型实例 var_name sys_refcursor; == 步骤1+步骤2
引用游标变量:
引用游标变量,在声明时,并没有指定SQL语句,必须在打开游标时,定义SQL语句,
再后面的操作和普通游标一致
范例:显示emp表的所有的员工名、工资和部门号 declare
mycur sys_refcursor; --没有定义SQL语句的 v_ename emp.ename%TYPE; v_sal emp.sal%TYPE; v_deptno emp.deptno%TYPE; begin
open mycur for select ename,sal,deptno from emp; --利用相应的SQL语句确定了打开的结果集
loop
fetch mycur into v_ename,v_sal,v_deptno; exit when mycur%notfound;
dbms_output.put_line('------------'||mycur%rowcount||'-------------'); dbms_output.put_line(v_ename); dbms_output.put_line(v_sal); dbms_output.put_line(v_deptno); END LOOP; close mycur; end;
引用游标变量:主要应用于PL/SQL的子程序向调用环境提供多行多列的结果集。 应用程序和Oracle之间接口。
create or replace procedure get_inf_by_dno (v_deptno emp.deptno%type, v_res out sys_refcursor) as begin
open v_res for select * from emp where deptno=v_deptno;
--向调用环境提供多行多列的结果集时,需要游标有效,所以只需要打开即可 --不能提取和关闭 end;
11、异常处理: 程序的错误类型:
1、编译类型错误:
用户误输入,或者其他与用户编程能力相关的语法错误。 此类错误可以避免,提高编程能力、借助开发工具。 2、运行时错误:
程序本事在语法上没有错误,很多时候都是能够正常运行的
有些时候因为用户提供的数据的问题或者网络、硬件等问题,而使程序不能够正常运行的错误
这种错误依赖于用户的输入和环境配置,因此很难完全避免
在程序中可以根据各种类型的错误,做相应的处理——异常处理 语法: Exception
When 异常名称1 [or 异常名称2„] then 语句段1
When 异常名称3 [or 异常名称4„] then 语句段2
When others then --除了1、2、3、4这4个异常以外的所有异常
语句段3
End;
Others:定义在异常处理部分没有处理的任何一个已知异常
异常名称:是一个名字,当此异常被引发,以异常名作为的表达式取true 异常的引发方式:
1、显式引发:raise 异常名; 2、错误引发:
这种形式,无需用户干预,只要是这个异常对应的错误发生了,此异常就自动引发
异常类型:
1、预定义异常==错误引发(由Oracle内置错误引发内置异常) 2、用户自定义异常: 1、显示引发 2、错误引发
1、由Oracle内置错误引发
2、由用户自定义错误引发
1、显式引发:
定义一个与错误无关的异常(显式引发) 1、声明:e_name exception; 2、引发:raise e_name; declare
v_deptno emp.deptno%type; e_dno exception; v_sal number(6,2); begin
v_deptno:=&deptno; if v_deptno=20 then raise e_dno; else
select avg(sal) into v_sal from emp where deptno=v_deptno; dbms_output.put_line(v_sal); end if;
exception
when e_dno then
dbms_output.put_line('20号部门的工资不能查阅'); end;
--以上程序,异常处理没有什么实际的意义,主要是程序员出于调试目的而添加的 --或者是理解异常处理的流程原理
2、Oracle(预定义)内置异常:
随Oracle产品发布且已经定义好的异常,可以无需定义直接使用 dup_val_on_index == -1 :捕获违反了主键约束的错误
too_many_rows :捕获试图向一个标量类型的变量赋予多个值 no_data_found :捕获select into语句没有返回任何数据 1、dup_val_on_index == -1 :捕获违反了主键约束的错误 begin
insert into emp(empno,ename,deptno) values(&empno,&ename,&deptno); commit; exception
when dup_val_on_index then
dbms_output.put_line('员工编号不能重复'); end;
2、too_many_rows —— -1422 :捕获试图向一个标量类型的变量赋予多个值 insert into emp(empno,ename,job,sal,deptno) values(1445,'abc','HY',2300,20); declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where job='&job'; exception
when too_many_rows then
dbms_output.put_line('此岗位有多名员工,无法获得其全部工资'); end;
3、no_data_found —— -1403:捕获select into语句没有返回任何数据 declare
v_sal emp.sal%type; begin
select sal into v_sal from emp where empno=&empno; exception
when no_data_found then
dbms_output.put_line('没有输入的员工编号'); end;
3、用户自定义异常:
1、自定义异常<—引发—内置错误关联 2、自定义异常<—引发—自定义错误关联
1、自定义异常<—引发—内置错误关联 语法:
1、声明
exception_name exception; 2、绑定
pragma exception_init(exception_name,error_code); 3、捕获
when ... then
declare
e_notnull exception;
pragma exception_init(e_notnull,-1400); e_fk exception;
pragma exception_init(e_fk,-2291); begin
insert into emp(empno,ename,deptno) values(&empno,&ename,&deptno); commit; exception
when dup_val_on_index then
dbms_output.put_line('员工编号不能重复'); when e_notnull then
dbms_output.put_line('员工编号不能为空');
when e_fk then
dbms_output.put_line('提供了一个不存在的部门号'); end;
2、自定义异常<—引发—自定义错误关联
利用自定义错误和异常可以实现很多复杂的业务规则 1、定义错误
raise_application_error(error_code,error_message);
error_code:-20000 —— -20999,错误编码,Oracle预留的范围
error_message:错误消息,为用户提供与错误相关的说明(2048字节) 2、声明异常
exception_name exception; 3、绑定错误和异常
pragma exception_init(exception_name,error_code); 4、捕获异常
when exception_name then ...
范例:不允许操作20号部门的信息(DML:触发器) declare
v_deptno emp.deptno%type; v_increment emp.sal%type; --e_dno exception;
--pragma exception_init(e_dno,-20001); begin
v_deptno:=&deptno;
v_increment:=&increment; if v_deptno=20 then
raise_application_error(-20001,'20号部门工资不能修改'); else
update emp set sal=sal+v_increment where deptno=v_deptno;
if SQL%found then
dbms_output.put_line('更新成功,共更新了'||SQL%rowcount||'行'); commit;
else
dbms_output.put_line('没有成功的更新任何数据'); end if; end if;
--exception
--when e_dno then
--dbms_output.put_line('20号部门工资不能修改'); end;
范例:对以上程序,加入,如果不是周六和周日,就报错 declare
v_deptno emp.deptno%type;
v_increment emp.sal%type; e_dno exception;
pragma exception_init(e_dno,-20001);
e_time exception;
pragma exception_init(e_time,-20002); begin
v_deptno:=&deptno;
v_increment:=&increment;
if to_char(sysdate,'day') not in ('星期六','星期日') then raise_application_error(-20002,'非法日期,操作失败'); else
if v_deptno=20 then
raise_application_error(-20001,'20号部门工资不能修改'); raise_application_error(-20001,'20号部门工资不能修改'); else
update emp set sal=sal+v_increment where deptno=v_deptno; if SQL%found then
dbms_output.put_line('更新成功,共更新了'||SQL%rowcount||'行');
commit; else
dbms_output.put_line('没有成功的更新任何数据'); end if; end if;
end if; /* exception
when e_dno then
dbms_output.put_line(SQLCODE); dbms_output.put_line(SQLERRM); when e_time then
dbms_output.put_line(SQLCODE); dbms_output.put_line(SQLERRM); */
exception
when others then
dbms_output.put_line(SQLCODE); dbms_output.put_line(SQLERRM); end; 获取错误栈信息:
1、错误代码:
SQLCODE:函数,返回错误的代码 2、错误消息:
SQLERRM:函数,返回错误消息文本 总结:
异常分类:
1、显式引发:
raise exception_name
显式引发的一般与错误无关,主要用于调试 2、错误引发
1、预定义异常 2、用户定义异常
1、与Oracle内置错误关联 2、与用户自定义错误关联 异常的传播规律:
在一个程序中可能发生错误的地方: 1、执行部分
如果在执行部分发生错误或者显式引发了异常,首先确定当前块是否有相应的 异常处理程序,如果有则异常处理程序处理此异常,如果没有,就把这个异常 传播到此块的父块,如果没有父块,则传播到调用环境,一旦传播到调用环境 如果调用环境没有处理,则导致程序非正常的终止运行。
2、声明部分
如果在声明部分发生错误或者显式引发了异常,则立即将此异常传播到父块, 即使本块有相应的处理程序,也不起作用。如果没有父块,则传播到调用环境 一旦传播到调用环境,如果调用环境没有处理,则导致程序非正常的终止运行。 3、异常处理
如果在异常处理部分发生错误或者显式引发了异常,则立即传播到块外,且一 次只能引发一次异常
===================子程序==================== Oracle子程序:
1、存储过程:一般性、经常性的数据库操纵(DML) 2、存储函数:经常性的运算、数据分析 3、程序包
1、为何要在Oracle中编写子程序:
1、子程序在应用程序开发中不是必须的。 2、优点1:
匿名块和SQL语句不能永久存储在数据库中,每一次执行,都需要重新编写或调用 源代码文本,不方便。子程序是数据库方案对象,可以存储在数据库中,且在数据 库中既存储子程序的源代码,又存储子程序的编译代码,调用、修改方便,执行效 率高。
3、优点2:
在高级预言中,通过嵌入SQL语句也能够完成子程序的任务,但是,很多任务,是 需要多条SQL语句,多个步骤完成的,所以,在高级语言中很多时候需要,多次向 数据库发送多条不同SQL语句。每一条都要通过网络以单独的TCP包发送,会占用 网络带宽,影响服务器性能。子程序可以把多个任务在一个程序中实现,且不需要 将SQL代码在网络上传输,节省网络资源。
4、缺点:过多的在数据库中实现子程序,会让应用程序过分依赖数据库类型,导致应 用程序迁移数据库平台变的困难。 2、如何编写子程序:
1、确定主要任务所对应的SQL语句。
2、根据确定的SQL语句,确定需要用户输入的数据和用户需要得到的数据 即确定子程序的形参(输入参数和输出参数(返回值)) 3、完善程序的相关内容,验证、检查、异常等 3、如何利用已有的匿名块改写为子程序:
1、添加头部信息中的形式参数名、类型和模式
2、没有包含在形参中的变量,作为私有变量在as...begin之间声明 3、(可选)针对调用环境修改输出方式 create or replace procedure mod_sal (
v_deptno IN emp.deptno%type, v_increment IN emp.sal%type )
as
e_dno exception;
pragma exception_init(e_dno,-20001); e_time exception;
pragma exception_init(e_time,-20002); begin
if to_char(sysdate,'day') not in ('星期六','星期日') then raise_application_error(-20002,'非法日期,操作失败'); else
if v_deptno=20 then
raise_application_error(-20001,'20号部门工资不能修改'); raise_application_error(-20001,'20号部门工资不能修改'); else
update emp set sal=sal+v_increment where deptno=v_deptno; if SQL%found then
dbms_output.put_line('更新成功,共更新了'||SQL%rowcount||'行');
commit;
else
dbms_output.put_line('没有成功的更新任何数据'); end if; end if; end if; exception
when others then
dbms_output.put_line(SQLCODE); dbms_output.put_line(SQLERRM); end;
4、如何调用子程序:
1、call subprogram_name;(JAVA)
2、execute subprogram_name;(SQL*PLUS) 3、begin
subprogram_name;
end; 5、参数模式
1、IN:输入模式 :在程序中按常量处理 2、OUT:输出模式 :在程序中按变量处理 3、IN OUT :输入/输出模式 :在程序中按变量处理
create or replace procedure frm_name (fname IN OUT varchar2, lname IN OUT varchar2, frm IN varchar2 default 'FL', fullname OUT varchar2) as begin
fname:=initcap(fname); lname:=initcap(lname);
if frm='FL' then
fullname:=fname||' '||lname; else
fullname:=lname||' '||fname; end if;
end;
6、如何给子程序传递参数
IN:实参可以是变量、常量、字面值;OUT和IN OUT :实参必须是变量 1、按位置传参
1、var fname varchar2(20) exec :fname:='HY'; 2、var lname varchar2(20) exec :lname:='GJ';
3、var full varchar2(40)
4、exec frm_name(:fname,:lname,'FL',:full);--按照子程序的形式参数的位置,依次传递
等价写法:
begin
frm_name(:fname,:lname,'FL',:full); end; 5、print full; 2、按名称传参
exec frm_name(lname=>:lname,frm=>'FL',fname=>:fname,fullname=>:full); 形式参数=>绑定变量,与位置无关
3、混合传参
exec frm_name(:fname,:lname,fullname=>:full,frm=>'LF');
7、子程序的形参数据类型不能设置精度。
以下程序的头中,形参的数据类型只能是varchar2,不能定义精度 如果希望输入的数据对精度有要求,一般使用锚定类型,由数据库底层
create or replace procedure frm_name (fname IN OUT varchar2,
lname IN OUT emp.ename%type, frm IN varchar2 default 'FL', fullname OUT varchar2) 。。。。 8、函数的调用:
函数是一个具有返回值的特殊过程,调用时需要考虑返回值的问题 create or replace function fm_name (fname IN OUT varchar2, lname IN OUT varchar2, frm IN varchar2 default 'FL') return varchar2 as
fullname varchar2(40); begin
fname:=initcap(fname); lname:=initcap(lname);
if frm='FL' then
fullname:=fname||' '||lname; else
fullname:=lname||' '||fname; end if;
return fullname;
end; 调用方法:
exec :full:=fm_name(:fname,:lname,'FL');
=============PL/SQL存储过程============= 存储过程:
Create [or replace] procedure proc_name ([arg1 in | out | in out]] arg_type1,„) Is | as
声明部分 Begin 执行部分 Exception 异常处理部分
End [proc_name]; 存储函数
Create [or replace] function fun_name
([arg1 [ in | out | in out ]] arg_type1,„) Return return_type Is | as
声明部分 Begin 执行部分 Exception 异常处理部分 End [fun_name];
范例:向emp表添加数据(empno、ename、deptno) --外部程序可以访问的是形参列表中的变量 create or replace procedure add_emp (v_empno emp.empno%type, v_ename emp.ename%type, v_deptno emp.deptno%type) as
begin
insert into emp(empno,ename,deptno) values(v_empno,v_ename,v_deptno); commit; end;
--使用内部局部变量实现,子程序内部的逻辑 create or replace procedure add_emp (v_empno emp.empno%type, v_ename emp.ename%type, v_deptno emp.deptno%type) as
v_count int; --子程序内部的局部变量,外部调用环境不能访问 begin
select count(*) into v_count from dept where deptno=v_deptno; if v_count<>0 then
insert into emp(empno,ename,deptno) values(v_empno,v_ename,v_deptno); commit;
dbms_output.put_line('数据添加成功'); else
dbms_output.put_line('非法的部门号'); end if; end;
--如何处理程序对调用环境的反馈,针对高级语言的调用,一般采用操作的状态代码表示 create or replace procedure add_emp (v_empno emp.empno%type, v_ename emp.ename%type, v_deptno emp.deptno%type,
v_code out int)
as
v_count int; --子程序内部的局部变量,外部调用环境不能访问 begin
select count(*) into v_count from dept where deptno=v_deptno; if v_count<>0 then
insert into emp(empno,ename,deptno) values(v_empno,v_ename,v_deptno); commit;
v_code:=1;--1代表操作成功 else
v_code:=0;--0代表操作失败 end if; end;
--增强程序的健壮性,增加异常处理
create or replace procedure add_emp (v_empno emp.empno%type, v_ename emp.ename%type, v_deptno emp.deptno%type, v_code out int,
v_resultset out sys_refcursor)
as
e_notnull exception;
pragma exception_init(e_notnull,-1400); e_fk exception;
pragma exception_init(e_fk,-2291); begin
insert into emp(empno,ename,deptno) values(v_empno,v_ename,v_deptno); if SQL%found then v_code:=1; commit;
open v_resultset for select * from emp where empno=v_empno; end if;
exception
when dup_val_on_index then
v_code:=-1;--不推荐直接使用数字 when e_fk then v_code:=-2; when e_notnull then v_code:=-3; when others then
v_code:=SQLCODE; end;
存储函数:
在语法上讲,可以和过程相互替代,但是,函数更多的是用于计算机或返回 特定的数据
create or replace function insert_emp (v_empno emp.empno%type, v_ename emp.ename%type, v_deptno emp.deptno%type,
v_resultset out sys_refcursor) return int as
v_code int;
e_notnull exception;
pragma exception_init(e_notnull,-1400); e_fk exception;
pragma exception_init(e_fk,-2291); begin
insert into emp(empno,ename,deptno) values(v_empno,v_ename,v_deptno); if SQL%found then v_code:=1;
commit;
open v_resultset for select * from emp where empno=v_empno; end if;
return v_code;
exception
when dup_val_on_index then
v_code:=-1;--不推荐直接使用数字 return v_code; when e_fk then v_code:=-2; return v_code; when e_notnull then v_code:=-3; return v_code; when others then v_code:=SQLCODE; return v_code; end;
函数一般作为单行函数使用是非常灵活的,所以以上的例子是很少见的 具有一个输入参数和一个返回值的函数(没有输出参数)
范例:通过用户的编号,获得用户年收入(月工资和补助求和乘以12) create or replace function get_salary (v_empno emp.empno%type)
return number
as
v_sal number(10,2); begin
select (sal+nvl(comm,0))*12 into v_sal from emp where empno=v_empno; return v_sal; end;
单行函数的调用和过程不同: 1、在PL/SQL块中调用 begin
:sal:=get_salary(7788); end;
2、表达式调用(SQL语句) declare
v_sal number(10,2); begin
v_sal:=get_salary(7788);
dbms_output.put_line(v_sal); end;
select get_salary(empno) from emp(只有单行函数才可以使用的方式) 什么是单行函数:
不能在函数中修改数据库表
不可以包含事务控制、会话控制、系统控制和DDL语句 不能是局部函数
只能带有IN参数不可以包含OUT和IN OUT参数 只能返回标量数据类型
添加适当的异常处理,提高程序的健壮性 create or replace function get_salary (v_empno emp.empno%type) return number as
v_sal number(10,2); begin
select (sal+nvl(comm,0))*12 into v_sal from emp where empno=v_empno; return v_sal; end;
提高程序的通用性:
add_emp过程只能向emp表添加数据,一般添加数据一个表对应一个子程序。 如果我们想修改emp表的特定列
的数据,是不是需要针对一个列,写一个子程序呢?
动态SQL语句
Oralce不推荐使用动态SQL语句,因为性能问题 create or replace procedure mod_inf (v_tablename varchar2, v_colname varchar2, v_newvalues varchar2,
v_condition varchar2,
v_resultset out sys_refcursor, v_code out int) as
SQLStr varchar2(2048); begin
SQLStr:='UPDATE '||v_tablename||' SET '||v_colname||' = '||v_newvalues||' WHERE '||v_condition;
--dbms_output.put_line(SQLStr); EXECUTE IMMEDIATE SQLStr; IF SQL%FOUND THEN
v_code:=1; COMMIT;
SQLStr:='SELECT * FROM '||v_tablename||' WHERE '||v_condition; open v_resultset for SQLStr; END IF;
end;
什么是局部子程序
在块的声明部分实现的子程序
可见范围、作用域是本块
在声明部分的尾部进行定义(前面定义类型,变量、常量等) 出现交叉引用子程序时,需要前向声明 局部子程序支持重载
create or replace function get_salary (v_empno emp.empno%type) return number as
v_sal number(10,2);
function chk_empno
(v_empno emp.empno%type) return boolean as
v_num int;
begin
select count(*) into v_num from emp where empno=v_empno; if v_num=0 then return false; else
return true; end if; end; begin
if chk_empno(v_empno) then
select (sal+nvl(comm,0))*12 into v_sal from emp where empno=v_empno; return v_sal; else
return 0; end if; end;
===============程序包===============
由逻辑上相关的类型、变量、常量、子程序等集成在一起的命名的PL/SQL块 程序包的使用可以有效的隐藏信息,实现集成化的模块化的程序设计
作为完整的单元存储在数据库,以名称标识,具有面向对象的特性
把PL/SQL编程元素逻辑的组织在一起,实现代码重用、封装,保存,面向对象 结构(构成)
1、包标准:定义公共组件
在包标准部分定义的任何程序元素对外部调用环境都是可以使用的 2、包体:定义私有组件,实现公共组件
包体定义但在包标准中不存在的组件都是私有的,外部调用环境不能使用。 在包标准中定义了但没有实现的元素(子程序),需要在包体中实现其功能
建立一个包标准:
create or replace package test as
--公共变量
v_code int;
v_msg varchar2(2000); --公共常量
C_SUCCESS constant int:=1; C_EX_PK constant int:=-1; C_EX_FK constant int:=-2291; c_EX_NOTNULL constant int:=-1400; --公共异常
e_notnull exception;
pragma exception_init(e_notnull,-1400); e_fk exception;
pragma exception_init(e_fk,-2291);
--以上的元素无需实现,在包体中可以使用,外部程序也可以使用 --子程序只提供声明,具体的实现代码需要在包体中完成
--公共函数
function get_salary
(v_empno emp.empno%type) return number; --公共过程
procedure add_emp
(v_empno emp.empno%type, v_ename emp.ename%type, v_deptno emp.deptno%type, v_resultset out sys_refcursor); end;
--包体的实现不是必须的,只有在包标准定义了子程序,而需要实现这些子程序时才需要包体
--包标准不依赖于包体,即使有定义的子程序。 --包体依赖于包标准
注意事项1:
在包体中定义和实现的子程序必须和包标准中的子程序同名,同参数列表 注意事项2:
充分考虑代码的复用:
1、常量、异常、变量的重用 2、私有子程序的重用
create or replace package body test as
--实现包标准中声明的子程序和包体中声明的私有子程序 --这是一个私有函数,用于验证员工的存在性,因为此函数
--没有在包标准中声明,必然是私有函数,此函数只能在包内部使用,外部程序不可见 function chk_emp
(v_empno emp.empno%type) return boolean as
v_num int; begin
select count(*) into v_num from emp where empno=v_empno; if v_num=0 then return false; else
return true; end if; end;
--重载chk_emp函数,因为v_ename和v_empno两个形参的类型不同,可以实现重载 function chk_emp
(v_ename emp.ename%type)
return boolean
as
v_num int; begin
select count(*) into v_num from emp where upper(ename)=upper(v_ename); if v_num=0 then return false; else
return true; end if; end;
--下面的子程序是公共的,因为这些子程序在包标准部分已经声明了 function get_salary
(v_empno emp.empno%type) return number as
v_sal number(10,2); begin
if chk_emp(v_empno) then
select (sal+nvl(comm,0))*12 into v_sal from emp where empno=v_empno; return v_sal; else
return 0; end if; end;
procedure add_emp
(v_empno emp.empno%type, v_ename emp.ename%type, v_deptno emp.deptno%type, v_resultset out sys_refcursor) as
--把程序中声明的局部异常,放到程序包标准部分,这些异常就可以被Oracle中所有程序使用
begin
insert into emp(empno,ename,deptno) values(v_empno,v_ename,v_deptno); if SQL%found then
v_code:=C_SUCCESS;
commit;
open v_resultset for select * from emp where empno=v_empno; end if; exception
when others then
v_code:=SQLCODE; v_msg:=SQLERRM; end; begin
--初始化
v_code:=0;
v_msg:='这是变量的初始化'; end;
--测试程序
--公共变量、常量、异常等的使用方法 declare
v_resultset sys_refcursor; v_row emp%rowtype; begin
test.add_emp(&empno,&ename,&deptno,v_resultset); if test.v_code=1 then
loop
fetch v_resultset into v_row; exit when v_resultset%notfound;
dbms_output.put_line(v_row.ename); end loop;
else
dbms_output.put_line(test.v_msg); end if; end;
==============数据库分页=============== 单表分页:
create or replace procedure sep_page (curr_page int,
page_size int,
v_result out sys_refcursor, v_code out int) as
v_start int; v_end int; v_count int; v_pageCount int; begin
select count(*) into v_count from emp; v_pageCount:=ceil(v_count/page_size); v_start:=(curr_page-1)*page_size+1; v_end:=curr_page*page_size; open v_result for
select * from (select rownum id,emp.* from emp) t where t.id between v_start and v_end;
if curr_page<=v_pageCount then v_code:=1; else
v_code:=0; end if; end;
通用分页:可以对任何查询分页 --创建公共变量,接受查询语句的行数 create or replace package test as
v_code int; end;
--实现分页过程
create or replace procedure sep_page (curr_page int,--要求得到的当前页码 page_size int,--设置每一页显示多少行
v_sql varchar2,--设置对那些数据分页
v_result out sys_refcursor,--返回指定页的结果集 v_code out int)--返回是否操作成功 as
v_start int; v_end int; v_count int;
v_pageCount int; SQLStr varchar2(2000); s1 varchar2(2000); begin
--构建一个匿名块的SQL字符串,通过公共变量test.v_code赋值,获得查询的总行数 s1:='begin select count(*) into test.v_code from ('||v_sql||'); end;'; execute immediate s1; v_count:=test.v_code;
--计算机可以分多少页
v_pageCount:=ceil(v_count/page_size); --计算机页的起始行号
v_start:=(curr_page-1)*page_size+1; --计算页的结束行号
v_end:=curr_page*page_size;
SQLStr:='select * from (select rownum id,tmp.* from ('||v_sql||')tmp) t where t.id between '||v_start||' and '||v_end; open v_result for SQLStr;
if curr_page<=v_pageCount then v_code:=1; else
v_code:=0; end if; end;
========================触发器======================== 特殊的子程序:
一般子程序是通过PL/SQL块调用(execute、begin...end、call等),才能够执行。 而触发器是不能够调用的,只能在满足了相应的条件后自动执行。 触发器的执行需要哪些条件?
触发事件:
什么样的动作,才有可能导致触发器触发
Insert、Delete、Update、Create、Alter、Drop、Grant ...... 触发时机:
触发器是在某个触发事件前还是后执行 触发对象:
并不是触发事件中的语句对任何数据库对象操作,触发器就触发。 只有对特定的对象操作,触发器才有可能触发 触发类型:
语句级还是行级
语句级:触发事件执行一次就触发一次(只关心触发事件的类型)
行级:触发事件在具体执行时,每操作一行就触发一次(不但关系类型,还关系对行的影响)
触发条件:
触发器在满足以上所有条件以外,还要满足的额外条件 条件谓词:
触发器中的语法元素
Inserting、Updating、Deleting等
代表正在执行的操作是什么,比如:用户正在执行Insert操作,则Inserting返还true
触发器的语法规则:
--语句级触发器:
CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER}
{insert | update | delete}
[OR {insert | update | delete]}„]
--触发器名
--触发时机
--触发事件
ON {table_name|view_name} --触发对象 PL/SQL_block | call procedure_name; --触发动作
--行级触发器:
CREATE [OR REPLACE] TRIGGER trigger_name --触发器名 {BEFORE | AFTER} --触发时机
{insert | update | delete [of column1[,column2,„]]}
[Or insert | update | delete [of column3[,column4,„]]}„] --触发事件 ON {table_name|view_name} --触发对象 FOR EACH ROW --行级触发(如果没有for each row就是语句级)
[WHEN condition] --触发条件 PL/SQL_block | call procedure_name; --触发动作
语句级触发器的使用:
语句级触发器只关心语句本事是否是被执行了,不对此语句所影响的数据进行考察, 所以语句级触发器只能完成一些比较简单的功能。
===============语句级触发器范例:================= 1、安全性控制
是特指应用程序级的安全性控制,与Oracle的权限、用户和角色等安全性无关。 例题:
用户在非工作时间不能修改EMP表 1、如何阻止用户修改表
通过自定义错误,并触发这个错误 2、如何描述时间是合法还是非法 9-17 周一——周五 3、如何确定触发器的各个设置: 前触发
Insert、update、delete emp
当用户对emp表,执行Insert或者Update或者Delete操作之前, 检查现在的时间是不是在9-17 或者 周一到周五,如果不是,则 通过raise_application_error产生一个错误。
create or replace trigger chk_emp before insert or update or delete on emp
begin
if to_char(sysdate,'HH24') not between 9 and 17 or to_char(sysdate,'day') in ('星期六','星期日') then raise_application_error(-20001,'error time'); end if; end;
2、安全性审核
要求对emp表的任何操作,都记录在Inf表中,记录的信息包含: 操作者的用户名、操作的时间、操作类型。
当用户在emp表上执行Insert或者Update或者Delete操作之后,
记录用户名、时间和操作类型
create or replace trigger audit_emp after insert or update or delete on emp
declare --和子程序不同,此处用declare,不能用is或as v_type varchar2(10); begin case
when updating then v_type:='UPDATE'; when Deleting then v_type:='DELETE'; else
v_type:='INSERT'; end case;
insert into inf values(USER,SYSDATE,v_type);
--commit;此处COMMIT语句不能添加。因为触发器中不能出现任何事务控制语句 end;
触发器所执行的DML语句和触发这个触发器的DML语句是在一个事务中的。 触发器中不能出现任何事务控制语句
===============行级级触发器范例:================= 触发器中包含For each row则说明此触发器是行级的。 行级触发器就可以访问DML语句所操作的数据
触发语句
insert into emp(empno,ename) values(1200,'aaa'); 导致以下语句的执行
insert into inf values(USER,SYSDATE,v_type); 如果希望访问触发语句中的值,则使用限定词访问
:old :可以访问触发语句中修改前的值(即将删除的数据) :new :可以访问触发语句中修改后的值(即将插入的数据) 例如:想访问1200,则可以通过表达式:new.empno访问 update emp set sal=3000 where sal=2000; 访问2000:——:old.sal 访问3000:——:new.sal Insert语句:只有:new
Delete语句:只有:old
Update语句::OLD和:NEW都可以访问
范例1:自动编号
1、创建序列发生器:
create sequence a; 2、建立一个测试表 create table test( id int primary key, name varchar2(20));
3、创建触发器,实现ID列的自动唯一编号 create or replace trigger seq_id before insert on test for each row declare v_num int;
begin
select a.nextval into v_num from dual; :new.id:=v_num; end;
练习题:
1、创建序列发生器b: create sequence b; 2、建立一个测试表 create table test1(
id varchar2(10) primary key, name varchar2(20));
3、创建触发器实现自动生成主键,主键的格式是HYGJ0001
范例2:检测时间的合法性:
要求用户对hiredate列的数据修改时,此日期不能大于当前系统时间
--利用Check约束实现:
create table emps as select * from emp;
alter table emps add constraint chk_hiredate check(hiredate<=sysdate); 因为Oracle中check表达式不能包含系统函数,以上语句是非法的 所以约束不能完成要求,下面就用触发器解决: create or replace trigger chk_hiredate before update of hiredate or insert on emps
for each row begin
if :new.hiredate>sysdate then
raise_application_error(-20001,'非法的时间,时间必须小于或等于当前时间');
end if;
end;
--如果update后不加of hiredate,则任何针对emps表的Update操作都会触发此
触发器
--加上update of hiredate,则只有在更新hiredate列时,触发器才会被触发
范例3:数据备份
此处的数据备份不同于Oracle的备份,是指应用程序中逻辑备份。
在很多网站或系统中,需要跟踪用户的爱好、兴趣或者用户的操作历史等信息,信息量很大
且系统一般分析用的数据集中在近期,对于较晚的信息,可能临时不需要。但是,有些时候
这些较晚的数据也是偶尔使用的,因此,这些数据不能物理删除,只能逻辑删除。 逻辑删除:数据是被放在一个备份表中,对于日常一般应用,此表的数据是不会被使用的,
只有在特定情况下才使用此表,那么,把数据从日常表中备份到备份表中就是逻辑删除,
因为在日常操作中,哪些备份的数据好像不存在一样,用户认为这些数据被“删除”了
题目:
用户删除emp表的记录时,将这些删除的行,自动备份到bak表 create table bak as select * from emp; delete from bak;
写触发器,实现自动备份
思路:当用户删除表emp中的数据时,在删除之前,把即将删除的数据插入到BAK表中。
1、为何是行级触发器?
关键要看,是否在触发器中使用触发语句所操作的数据,如何使用则是行级,不使用就是语句级
触发语句是:delete from emp ...
触发器中使用:insert into bak ...,Insert语句使用的数据就是被删除的数据
2、触发时机是Before还是After?
很多时候Before还是After,对触发器不重要。
当前例子中,使用Before或After,是没有关系的,都可以。 但是有些时候,Before和After就不能相互替代:自动编号 create or replace trigger bak_emp_del before [after] delete on emp for each row begin
insert into bak
values(:old.EMPNO,:old.ENAME,:old.JOB,:old.MGR, :old.HIREDATE,:old.SAL,:old.COMM,:old.DEPTNO); end;
范例4:级联删除和级联更新
如果主表中的数据被删除,则从表中与之相关的数据一起被删除:级联删除 如果主表中的数据被修改,则从表中与之相关的数据一起被修改:级联更新 如果把DEPT表中20号部门删除,此操作能不能成功呢?
此操作不能完成,因为如果20部门不存在了,则emp表原20部门的记录就违反了 外键约束(所属20部门,在DEPT中不存在了)
为了能够删除dept表中的行,则创建触发器:
1、思路:当用户删除dept表中的行,则在删除前,将EMP表中与之相关的行的部门号 设置为空或者将对应行删除。 --1:级联更新,设置为空
create or replace trigger cascade_delete before delete on dept for each row begin
update emp set deptno=null where deptno=:old.deptno; end;
--2:级联删除相关行
create or replace trigger cascade_delete before delete
on dept
for each row begin
delete from emp where deptno=:old.deptno; end;
用户修改了部门号,则从表EMP中相对应的部门号也随之修改 update dept set deptno=90 where deptno=20 --3:数据级联的更新
create or replace trigger cascade_update before update of deptno on dept
for each row
begin
update emp set deptno=:new.deptno where deptno=:old.deptno; end;
问题:如何将多个触发器合并成一个触发器
对2和3的对比,触发对象、触发时机和类型都是相同的,则两个触发器可以合并成一个
create or replace trigger cascade_update before update of deptno or delete on dept
for each row
begin case
when updating('deptno') then
update emp set deptno=:new.deptno where deptno=:old.deptno; else
delete from emp where deptno=:old.deptno; end case; end;
范例5:生成流水号(参考范例1的练习题)
生成连续流水号:要求编号不能在删除后,不再使用
(一个序号在删除后可以在下一次增加新数据时再次使用)
例如:编号19,删除后19就不能出现在表中,现在需要19再次被使用
思路:在删除相应编号的行时,自动把删除的行对应的编号存储在一个表中,当用户再次
添加新数据时,首先检查表中是否有被删除的编号,如果有就优先使用,如果没有则通过
序列发生器生成新的。
1、创建存储删除的编号的表TMP create table tmp(id int); 2、创建生成编号的序列 create sequence c; 3、创建测试数据表test create table test
(id int primary key, name varchar2(20));
4、通过触发器实现:连续编号 1、当添加新行则生成编号 2、当删除行则备份编号
create or replace trigger seq_id before insert or delete on test for each row declare
v_num int; v_count int; begin
case
when Inserting then
--判断TMP表中是否有已删除的编号
select count(*) into v_count from tmp; if v_count=0 then
--如果没有已删除的编号则从序列生成
select c.nextval into v_num from dual; else
--如果有则取最小值
select min(id) into v_num from tmp; --取出最小值,并把最小值删除 delete from tmp where id=v_num; end if; :new.id:=v_num; else
insert into tmp values(:old.id); end case; end;
练习:生成流水号(连续的)
--当向test表插入数据时,先检查表中有没有已经删除的行,如果有 就优先使用已经删除的编号,且从小到大依次使用,如果没有,就利用 序列发生器生成新的值。
要求流水号格式:HYGJ2009010120000006 前缀:固定的,HYGJ 20090121:入职日期
20:入职时的部门号,如果没有部门则是00 000006:编号 创建测试表:
create table test
(id varchar2(20), name varchar2(20),
deptno int references dept(deptno)); 创建序列
create sequence b; 创建备份表
create table del_id (id int); 创建触发器
5、替代触发器:
--替代触发器只应用于视图
--针对默认不能执行特定的DML语句的视图,创建的触发器, 目的:使不能更新的视图可以执行相应的DML语句 替代触发器的触发时机只有:instead of 替代触发器一定是“行级触发器”
用于替代特定的DML语句
create or replace trigger trigger_name instead of insert | update | delete | insert or update | insert or delete | update or delete | insert or update or delete | [update of column_name,...] on view_name for each row PL/SQL Block
--创建视图(连接视图) create or replace view emps as
select empno,ename,deptno,dname from emp join dept using(deptno)
insert into emps values(1234,'aaa',90,'HR'); 连接视图不能执行Insert操作
通过替代触发器,把不能执行的Insert语句替代成可以执行的insert
思路:当用户向emps视图插入数据时,把这条Insert语句替代成分别向emp和dept插入数据
create or replace trigger instead_insert_emps instead of insert on emps
for each row declare
v_count int;
begin
--检查部门号是否存在
select count(rowid) into v_count from dept where deptno=:new.deptno; --如果不存在,则添加新的部门信息,如果存在则什么都不做 if v_count=0 then
insert into dept(deptno,dname) values(:new.deptno,:new.dname); end if;
--向员工表插入数据
insert into emp(empno,ename) values(:new.empno,:new.ename); end;
Oracle数据库技术总结: 1、数据查询和基本操作:
1、Select语句(连接操作、子查询、Oracle函数) 2、Insert、Update、Delete操作 2、配置Oracle客户端
1、本地网络服务名(主机、端口、协议、SID) 2、SQL*Plus或其他客户端的配置及连接 3、实现数据库:
1、数据存储:Tablespace 2、Oracle用户
3、权限和角色(系统、对象权限,默认Oracle角色)
4、实现方案对象(表、视图、约束、序列、同义词、索引) 5、编写PL/SQL子程序 4、PL/SQL编程:
1、实现存储过程和函数 2、实现程序包 3、实现触发器
在工程实践中,如何实现应用程序的数据库(Oracle) 1、应用项目的需求分析
2、绘制数据库模型图(软件技术中需要UML图) 3、实现基本的表及视图(由模型图生成) 4、创建表空间(可选)
5、创建Oracle用户,且授予适当的权限
6、执行由模型图生成的SQL脚本(start)
7、由项目需要编写数据库端的应用程序(子程序和触发器)
1、创建序列
2、创建触发器实现自动编号
3、根据相对应的业务需求,编写触发器(为了降低高级语言的编程难度) 4、根据业务需求,编写过程、函数、包
在工程实践中,如何实现应用程序的数据库(MS SQL Server) 1、应用项目的需求分析
2、绘制数据库模型图(软件技术中需要UML图) 3、实现基本的表及视图(由模型图生成) 4、创建数据库
5、创建SQL Server用户,且授予适当的权限(可选) 6、执行由模型图生成的SQL脚本
7、由项目需要编写数据库端的应用程序(子程序和触发器)
1、根据相对应的业务需求,编写触发器(为了降低高级语言的编程难度) 2、根据业务需求,编写过程、函数
因篇幅问题不能全部显示,请点此查看更多更全内容
Copyright © 2019- igat.cn 版权所有 赣ICP备2024042791号-1
违法及侵权请联系:TEL:199 1889 7713 E-MAIL:2724546146@qq.com
本站由北京市万商天勤律师事务所王兴未律师提供法律服务