Oracle学习整理(二)

一:存储过程

存储过程: (Stored Procedure) 在大型数据库系统中，存储过程是一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

创建存储过程的语法:

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)] AS
begin
PLSQL 子程序体；
End;
或者
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)] is
begin
PLSQL 子程序体；
End    过程名;

范例:创建一个名为helloOracle存储过程

create procedure helloOracle is
begin
       dbms_output.put_line('helloOracle');
end helloOracle;

调用存储过程

begin
helloOracle;
end;

out类型

创建out类型的存储过程

--计算年薪(sal*12+comm)
create procedure my_yearsal(eno  emp.empno%type,yearsal out number)
is  
      s emp.sal%type;
      c emp.comm%type;
begin
     select sal,nvl(comm,0) into s,c from emp where empno=eno;
     yearsal := s*12+c;
end my_yearsal;
drop procedure my_yearsal

调用并输出年薪

declare 
   n number;
begin
   my_yearsal(7369,n);
   dbms_output.put_line(n);
end;

二.存储函数

1. 注意事项

存储过程和存储函数的参数(形参)不能带长度比如number(10)这种
存储函数的返回值类型不能带长度
自定义的变量(非形参)可以带上长度
存储过程没有返回值,存储函数可以用return 返回值

2.语法

create [or replace] function 函数名(Name in type, Name in type, ...) 
return 数据类型 is
  结果变量 数据类型; 

begin
return(结果变量); 
end 函数名;

3.例子:使用存储函数计算年薪

create function empcome(eno in emp.empno%type) return number is 
psal emp.sal%type;
begin
    select t.sal into psal from emp t where t.empno = eno;
return psal * 12;
 end;
 
 drop function empcome;

调用存储函数

declare
  income number;  
begin 
    income:=empcome(7369);
    dbms_output.put_line(income);
end;

三.触发器

1.触发器的含义

当条件满足时会自动触发触发器,从而执行触发器里面所定义的语句。触发器不用人为的调用,也不能调用

2.触发器的分类

1.语句级触发器 :在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行

2.行级触发器　 :触发语句作用的每一条记录都被触发。在行级触发器中使用old 和new 伪记录变量, 识别值的状态

3.定义触发器的语法

CREATE    [or REPLACE] TRIGGER    触发器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表 名
[FOR EACH ROW [WHEN(条件) ] ]
begin
PLSQL 块
End 触发器名

创建一个触发器

--当插入员工时触发
create trigger empInsert
after insert on emp 
declare 
begin 
    dbms_output.put_line('一个员工被插入'); 
end empInsert;

当往emp表中新天加一条记录的时候,控制台会打印

就代表触发了触发器

在触发器中触发语句与伪记录变量的值

触发语句	:old	:new
Insert	所有字段都是空(null)	将要插入的数据
Update	更新以前该行的值	更新后的值
delete	删除以前该行的值	所有字段都是空(null)