Oracle function 等的实际用法与书中的差异

前面的废话

本人是一名学生目前在学习万难的数据库

使用的课本是那本黑黑的数据库系统概念第七版

看到了书中一堆堆sql代码

想要实践一下书中的代码部分

我使用的是一个较为老版本的Oracle

到目前为止发现了不少书中内容与实际有出入的部分

毕竟书上教的是通用的sql语言

其中让我最头疼的莫过于这位function的用法

正文开始

先看一下书中的代码

create function dept_count(dept_name varchar(20))
    returns integer
    begin
    declare d_count integer;
        select count(*) into d_count
        from instructor
        where instructor.dept_name = dept_name
    return d_count;
    end

我向来是一个乖乖的孩子

于是我乖乖的把书上的代码敲了进去

很好

编译器也很乖

错误(1,38): PLS-00103: 出现符号 "("在需要下列之一时：  := . ) , 
@ % default    character 符号 ":=" 被替换为 "(" 后继续。 
错误(2,5): PLS-00103: 出现符号 "RETURNS"在需要下列之一时：  return 

弹了两个错误

后来去追查了半天

Oracle中的函数大致是不可以这样的

其中函数的基础规范格式

大概是

create function function_name (parameter_1 mode type_1, ...)--mode为in/out,不写默认为in
    return return_type
    is/as 
        --在这里定义需要的参数,记得每定义一个加";"
    begin
    --进行操作
    return ans;
    end;

所以照理我应该写成

--写为create or replace如果重名重参数可以更新相同的函数
create or replace function dept_count(dept_name varchar(20))
    return integer
    is
        d_count integer;
    begin
        select count(*) into d_count
        from instructor
        where instructor.dept_name = dept_name;
    return d_count;
    end;

很好

编译器说

错误(1,38): PLS-00103: 出现符号 "("在需要下列之一时：  := . ) , 
@ % default    character 符号 ":=" 被替换为 "(" 后继续。 

还是留下了一个问题

在尝试多次后发现

将第一行改为

create or replace function dept_count(dept_name varchar)

则问题解决

在网上翻来翻去找了半天也没有结果

我现在只能被迫认为

Oracle大概不支持在这里限定varchar的长度

如果有哪位好心人可以告诉我细则我感激不尽

所以现在代码变为了

create or replace 
function dept_count(dept_name varchar)
    return integer
    is
        d_count integer;
    begin
        select count(*) into d_count
        from instructor
        where instructor.dept_name = dept_count.dept_name;
    return d_count;
    end;

很好运行成功

现在测试一下这个函数的功能

构建简单的测试用例

declare
  Ans integer;
  Get Varchar(20);
begin
  Get := 'Pol. Sci.';
  Ans := Dept_count(Get);
  dbms_output.put_line(Ans);
end;

输出结果为

匿名块已完成

我的答案呢？？？

又是几经周折

方才知道

要用

set serveroutput on;

来打开输出显示

于是改为

set serveroutput on;
declare
  Ans integer;
  Get Varchar(20);
begin
  Get := 'Pol. Sci.';
  Ans := Dept_count(Get);
  dbms_output.put_line(Ans);
end;

成功获取想要的结果

其实这个字段只要使用一次后面就可以一直有用

如果想要关闭这个功能

可以使用

set serveroutput off;

折腾了半天终于结束了

麻了

本来到这里就要结束了

手残的又去尝试了表函数

书上原代码为

create or replace function instructor_of(dept_name varchar)
return table(                        --此处已经把returns改为return
              ID  varchar(5),
              name varchar(20),
              dept_name varchar(20),
              salary numeric (8,2)
              ) 
return table(
              select ID, name, dept_name, salary
              from instructor
              where instructor.dept_name = instructor_of.dept_name
              );

编译器诚实的给了我一个

错误(2,8): PLS-00103: 出现符号 "TABLE"在需要下列之一时：  <an identifier>    
<a double-quoted delimited-identifier> self long double ref   
 char time timestamp interval date binary national character    nchar 

说真的完全看不懂

经过一番查找

发现有大佬说：在Oracle中想要返回表对象，必须自定义一个表类型

如果只想要一列

可以用

create or replace type type_name is table of type;

如果想要多列

可以用

create or replace type type_name as object
 (
    name_1, type1,
    name_2, type2,
    ...
 );

--注意在此之后我们需要修改表对象类型的定义
--语句如下：

create or replace type new_type_name is table of type_name;

--具体为什么这样做希望大佬指教

所以在这里我们先定义一个表类型

create or replace type Get_table as object 
  (
     ID  varchar(5),
     name varchar(20),
     dept_name varchar(20),
     salary numeric (8,2) 
  );

create or replace type Get_table_ is table of Get_table;

然后，在几经波折尝试后

原函数被改写为

create or replace 
function instructor_of(dept_name varchar)
return Get_table_
is
    Ans Get_table_ := Get_table_();
begin
    for i in (
                select * 
                from instructor
                where instructor.dept_name = instructor_of.dept_name
              ) --此处为遍历instructor中满足条件的元组
      loop
      Ans.extend;--扩展
      Ans(Ans.count):= Get_table(i.ID, i.name, i.dept_name,i.salary);--存储内容
      end loop;
    return Ans;
end;

在使用函数查询结果时需要如此这般

select * from table(instructor_of('Pol. Sci.'));

成功获得想要的结果

麻了，真麻了，查了一堆资料

所以我们果断地又试了一下过程

好在没出现前面这么大的差别

书上原代码为

create procedure dept_count_proc(in dept_name varchar, out d_count integer)
begin
    select count(ID) into d_count
    from instructor
    where instructor.dept_name = dept_count_proc.dept_name;
end

改为

create or replace 
procedure dept_count_proc(dept_name in varchar, d_count out integer)
is
begin
    select count(ID) into d_count
    from instructor
    where instructor.dept_name = dept_count_proc.dept_name;
end;

即可

后记

其实这类差异还是不少的

我在这里简单记录我发现的一点点

也算是给自己做个笔记

希望大家可以指教我

情景1：

select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';

--问题：Oracle仿佛不支持在此处使用as,应当直接使用空格
--应为

select distinct T.name
from instructor T, instructor S
where T.salary > S.salary and S.dept_name = 'Biology';

情景2：

(select course_id
 from section
 where semester = 'Fall' and year = 2017)
intersect all
(select course_id
 from section
 where semester = 'Spring' and year = 2018);

--问题：Oracle中好像没有"intersect all"的用法

情景3：

(select course_id
 from section
 where semester = 'Fall' and year = 2017)
except
(select course_id
 from section
 where semester = 'Spring' and year = 2018);

--问题：Oracle中没有except，取而代之的为mins
--应为

(select course_id
 from section
 where semester = 'Fall' and year = 2017)
mins
(select course_id
 from section
 where semester = 'Spring' and year = 2018);

--好像也没有"mins all"的用法

情景4：

select name
from instructor
where salary > 10000 is unknown;

--问题：Oracle仿佛不支持在此处使用"is unknown/true/false"

情景5：

select T.course_id
from course as T   --Oracle中为直接空格如情景1
where unique(select R.course_id
             from section as R  --Oracle中为直接空格如情景1
             where T.course_id = R.course_id and R.year = 2017);

--问题：Oracle不支持此类的"unique"的用法

情景6：

select dept_name, avg_salary
from (select dept_name, avg(salary)
      from instructor
      group by dept_name)
      as dept_avg(dept_name, avg_salary)--
where avg_salary > 42000;

--问题：Oracle中不支持给此处子查询的属性更名
--应为

select dept_name, avg_salary
from (select dept_name, avg(salary) as avg_salary
      from instructor
      group by dept_name)
      as dept_avg
where avg_salary > 42000;

情景7：

--创建新类型
create type Dollars as numeric(12,2)final;
--问题：Oracle中不支持此类的定义新类型的方式
--应为

create type Dollars is table of numeric(12,2);

--此外Oracle还支持一种
create type type_name as object
 (
    name_1, type1,
    name_2, type2,
    ...
 );
--尚待研究

情景8：

--定义域
create domain DDollars as numeric(12,2)not null;
--问题：Oracle不支持域的定义

情景9：

--定义一张与instructor相同类型的表
create table temp_instructor like instructor;
--问题：Oracle不支持此类"like"的用法

情景10：

--收回用户的授权的权限
revoke grant option for select on department from Amit;
--问题：Oracle不支持单收回权限的操作，可以用收回权限本身再用不带"grant option"授权来取代

祝大家生活顺利