使用having count的PL/SQL更新率
使用having count的PL/SQL更新率
提问于 2011-12-15 01:37:26
你好，我是pl/sql的新手。
我需要将少于5名员工的所有项目的rate_per_hour更新到500
这是我写的代码，但它会更新所有员工
set serveroutput on
declare
cursor rate_cur is
select * from project
for update of rate_per_hour;
begin
for rate_rec IN rate_cur
loop
update project
set rate_per_hour=500
where current of rate_cur;
end loop;
end;下面是我的表格：
CREATE TABLE employee(
empid number(5),
empname varchar(20),
address varchar(20),
no_of_dependents number(5),
deptno number(5),
CONSTRAINT EMPLOYEE_PKEY PRIMARY KEY(empid),
CONSTRAINT EMPLOYEE_FKEY FOREIGN KEY(deptno) REFERENCES department(deptno));
CREATE TABLE project(
projectno number(5),
location varchar(20),
incharge number(5),
rate_per_hour number(5),
CONSTRAINT PROJECT_PKEY PRIMARY KEY(projectno),
CONSTRAINT PROJECT_FKEY FOREIGN KEY(incharge) REFERENCES employee(empid));
CREATE TABLE assignment(
empid number(5),
projectid number(5),
hours number(5),
CONSTRAINT ASSIGNMENT_FKEY FOREIGN KEY(empid) REFERENCES employee(empid),
CONSTRAINT ASSIGNEMNT_FKEY2 FOREIGN KEY(projectid) REFERENCES project(projectno));
Please suggest a solution回答 2
Stack Overflow用户
回答已采纳
发布于 2011-12-15 23:37:10
我在另一个论坛上找到了帮助！以下是代码
update project
set rate_per_hour = 500
where projectno IN
(select projectid
from assignment
group by projectid
having count(distinct empid) <5);Stack Overflow用户
发布于 2011-12-15 12:03:14
它会更新所有项目，因为您选择的是无限制的所有项目。您可以将光标更改为类似以下内容：
declare
cursor RATE_CUR is
select P.PROJECTNO, count(*) as EMP_CNT
from PROJECT P
join ASSIGNMENT A
on A.PROJECTID = P.PROJECTNO
group by PROJECTNO
having count(*) < 5;
begin
for RATE_REC in RATE_CUR loop
update PROJECT
set RATE_PER_HOUR = 500
where PROJECTNO = RATE_REC.PROJECTNO;
end loop;
end;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/8509063
复制相关文章
相似问题
腾讯云开发者
