一、文件格式
- 初始化DDL文件:存放create table,trigger、函数等sql语句
- 初始化DML文件:insert、update、delete等sql语句
- 查询语句:select语句
二、什么时候使用触发器?
例如:
编写程序,实现每一位员工参加完某个课程的考试,能自动更新员工课程成绩,课程平均分,课程最高分的功能。
如果遇到插入、更新、删除等之前或之后,自动更新。。。这时就要使用触发器了
create or replace trigger auto_update
before insert on emp_course
for each row
declare -- 声明变量
p_count number(4);
p_top number(4);
p_avg number(4);
p_credit number(4);
p_pass number(4);
begin
select count(1) into p_count from emp_course ec where ec.course_id = :new.course_id; -- 获得选择该门课程的人数 --
select c.course_top,c.course_avg,c.course_credit,c.course_pass into p_top,p_avg,p_credit,p_pass from courses c where c.course_id = :new.course_id; -- 获取该门课程的最高分、平均分、学分、及格线
update courses c -- 自动更新员工课程成绩,课程平均分,课程最高分
set c.course_top=(case when :new.score > c.course_top then :new.score else c.course_top end),
c.course_avg=(p_count*p_avg + :new.score)/(p_count+1)
where c.course_id = :new.course_id;
if :new.score >= p_pass then -- 如果员工课程成绩通过,则学分增加
update emp e
set e.emp_credit = (e.emp_credit + p_credit)
where e.emp_id = :new.emp_id;
end if;
end auto_update;
三、什么时候使用函数
**函数:**一般用于计算和返回一个值,可以将经常需要进行的计算写成函数。
函数的调用是表达式的一部分,而过程的调用是一条PL/SQL语句。
函数与过程procedure在创建形式上有些相似,也是编译后放在内存中供用户使用,**只不过调用时函数需要表达式,而不像过程只需调用过程名。**另外,函数名必须有一个返回值,而过程则没有。
例如:
编写程序,能够查询某位员工的年度网络学院课程总学分是否达标(如输入员工A,查询到总学分为45,返回达标)
如果需要返回值的时候,考虑使用函数。
**创建函数:**创建函数的语法和创建存储过程的语法也很相似。他们在数据库内采用相同的规则,参数的传递也相同。
create [or replace] function function_name
[parameter1 [in|out|in out] data_type1,
parameter2 [in|out|in out] data_type2,
...]
return data_type
is|as
声明部分
begin
执行部分
exception
异常处理部分
end [function_name];
在语法结构上,定义存储过程procedure和函数的区别主要有两处:
- 一是函数有return数据类型子句,用于指定函数返回值的数据类型。
- 二是在函数体内,用户可以在任何地方使用return语句返回值,其返回值的数据类型必须是函数头中声明的,并且执行return语句后函数将返回,不再执行下面的语句。
create or replace function getCreditInfo(p_name in varchar2)
return varchar2
is
result varchar2
begin
select case when emp_credit >= 45 then '达标' else '不达标' into result
from emp
where emp_name = p_name;
return (result);
end getCreditInfo;
**调用函数:**需要一个变量来保存函数的返回的结果值
select getCreditInfo('员工A') from dual; -- 直接显式
-- 匿名块
begin
dbms_output.put_line(getCreditInfo('员工A'));
end;
四、什么时候使用存储过程(procedure)
子程序是执行特定任务的程序单元/模块。(主要用于执行动作)
子程序可以被称为调用程序的另一个子程序或程序调用。
可以在以下几个地方中创建一个子程序:
- 在模式(schema)级别中
- 一个程序包
- 在PL/SQL块中
4.1 创建存储过程
create [or replace] procedure procedure_name
[(parameter_name [in|out|in out] type[, ...])]
{is|as}
begin
可执行部分
end [procedure_name];
- in:表示将从外部传递的值
- out:表示将用于返回过程外的值的参数
4.2 执行/删除procedure
/*
执行procedure
*/
exec procedure_name;
-- 或者
execute procedure_name;
/*
删除procedure
*/
DROP PROCEDURE procedure-name;