在对象关系数据库中使用Method时如何编写SQL语句 [英] How to write SQL statement when use Method in object relational database

问题描述

我正在尝试使用oracle 11g中的方法创建一个由银行雇员组成的小型数据库，因此最终我可以获取那些有资格在年底获得奖励的雇员的价值:

I am trying to create a small database of bank employees using method in oracle 11g, so at the end I can fetch values of those employees who are entitle for the awards at the end of the year:

在银行工作超过12年并监督6名以上员工的员工的金牌；在银行工作超过8年并监督3名以上员工的员工可获得银牌；曾在银行工作的员工获得铜牌 超过4年，显示他们的姓名和获得的奖章(仅限 显示被授予的人.

Gold medals for employees who have been working at the bank for more than 12 years and supervised more than 6 staff; silver medals for employees who have been working at the bank for more than 8 years and supervised more than 3 staff; bronze medals for employees who have been working at the bank for more than 4 years, displaying their names and Medal awarded (only displaying those who have been awarded).

这就是我在做什么

create type EmployeeName as object(
title varchar2(10),
firstName varchar2(20),
surname varchar2(20))
not final
/
create or replace type employeeaward as object(
empID integer,
eName EmployeeName,
number_staff_supervised int,
working_years int,
MEMBER FUNCTION award_given RETURN STRING,
MEMBER FUNCTION number_fraction (N real) RETURN real
);
/
CREATE OR REPLACE TYPE BODY employeeaward AS
MEMBER FUNCTION award_given RETURN STRING IS
BEGIN
IF self.working_years > 12 THEN
RETURN 'gold medal';
ELSIF self.working_years > 8 THEN
RETURN 'silver medal';
ELSIF self.working_years > 4 THEN
RETURN 'bronze medals';
END IF;
END award_given;
MEMBER FUNCTION number_fraction(N real) RETURN real IS
num real;
BEGIN
num :=(self.number_staff_supervised);
return num;
END number_fraction;
END;

，然后像这样创建一个表employeeaward的employeeawardtable:

create table employeeawardtable of employeeaward;
/

然后在表格中插入一些值

insert into employeeawardtable values('2001',EmployeeName('Mr','Rohit','Sharma'),'12','18');
/
insert into employeeawardtable values('2002',EmployeeName('Mr','Andrew','Darson'),'9','7');
/
insert into employeeawardtable values('2003',EmployeeName('Mrs','Sarah','Barlow'),'5','4');
/
insert into employeeawardtable values('2004',EmployeeName('Mr','Ram','Gopal'),'11','9');
/

**这是SQL查询，我正在努力获取数据. **

**This is the SQL query, I am struggling to fetch data. **

select e.ename.firstname, e.award_given(),e.number_fraction(15)
from employeeawardtable e
where e.number_fraction() > 8; 

谢谢.

推荐答案

以下是您的代码存在的问题.

Following are the issues with your code.

1. 在谓词中不带参数的情况下调用number_fraction成员函数.这将引发错误"ORA-06553: PLS-306: wrong number or types of arguments in call to 'NUMBER_FRACTION'"

下面是您需要的SQL.

Below is SQL for your requirement.

select e.ename.firstname, e.award_given()
from employeeawardtable e
where e.award_given() = 'gold medal' and e.number_staff_supervised > 6
or e.award_given() = 'silver medal' and e.number_staff_supervised > 3
or e.award_given() = 'bronze medal';

1. member_function award_given没有返回默认值.这将引发错误. "ORA-06503: PL/SQL: Function returned without value".如下固定字体.

创建或替换类型的身体员工奖AS 成员功能award_given返回字符串为 开始 如果self.working_years> 12 THEN 返回金牌"； ELSIF self.working_years> 8 THEN 返回银牌"； ELSIF self.working_years> 4 THEN 返回铜牌"； ELSE-新添加 返回无勋章"；-新添加 万一; END award_given; 成员函数number_fraction(N实数)返回实数IS num real; 开始 num:=(self.number_staff_supervised); 返回num; END number_fraction; END;

CREATE OR REPLACE TYPE BODY employeeaward AS MEMBER FUNCTION award_given RETURN STRING IS BEGIN IF self.working_years > 12 THEN RETURN 'gold medal'; ELSIF self.working_years > 8 THEN RETURN 'silver medal'; ELSIF self.working_years > 4 THEN RETURN 'bronze medals'; ELSE--newly added RETURN 'no medal';--newly added END IF; END award_given; MEMBER FUNCTION number_fraction(N real) RETURN real IS num real; BEGIN num :=(self.number_staff_supervised); return num; END number_fraction; END;

现在发出SQL. 输出:

Now issue the SQL. Output:

ENAME.FIRSTNAME E.AWARD_GIVEN()
Rohit           gold medal
Ram             silver medal

这篇关于在对象关系数据库中使用Method时如何编写SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！