Oracle——解决一个问题、创建函数、创建procedure

一、文件格式

1. 初始化DDL文件：存放create table，trigger、函数等sql语句
2. 初始化DML文件：insert、update、delete等sql语句
3. 查询语句：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;