PLSQL创建函数，从列中添加所有值并将总和插入新列

Question

CREATE TABLE WORK(
WorkerNo    NUMBER(10)  NOT NULL,
TotalSalary  NUMBER(10),
CONSTRAINT WORK_PKEY PRIMARY KEY (WorkerNo),
INSERT INTO WORK( 1, NULL );
INSERT INTO WORK( 2, NULL );
CREATE TABLE WorkSalary(
City VARCHAR2(30),
Postal VARCHAR2(30),
Salary NUMBER(9)    NOT NULL,
CONSTRAINT WorkSalary_PKEY PRIMARY KEY(City,Postal),
INSERT INTO WorkSalary('New York City', '123456', 5000);
INSERT INTO WorkSalary('Washington DC', '154876', 4500);
INSERT INTO WorkSalary('New Jersey', '458741', 3500);
INSERT INTO WorkSalary('Maryland', '487451', 2000);
CREATE TABLE WORKDONE(
WorkerNo NUMBER(10)     NOT NULL,
JobNo NUMBER(2)     NOT NULL,
City VARCHAR2(30)   NOT NULL,
Postal VARCHAR2(30) NOT NULL,
CONSTRAINT WORKDONE_PKEY PRIMARY KEY (WorkerNo, JobNo),
CONSTRAINT WORKDONE_FKEY1 FOREIGN KEY (WorkerNo) 
    REFERENCES WORK(WorkerNo),
CONSTRAINT WORKDONE_FKEY2 FOREIGN KEY (City)
    REFERENCES WorkSalary(City),
CONSTRAINT WORKDONE_FKEY3 FOREIGN KEY (Postal)
    REFERENCES WorkSalary(Postal) );
INSERT INTO WORKDONE VALUES( 1, 1, 'New York City', '123456');
INSERT INTO WORKDONE VALUES( 1, 2, 'Maryland', '487451');
INSERT INTO WORKDONE VALUES( 1, 3, 'New Jersey', '458741');
INSERT INTO WORKDONE VALUES( 2, 1, 'New Jersey', '458741');
INSERT INTO WORKDONE VALUES( 2, 2, 'New York City', '123456');
INSERT INTO WORKDONE VALUES( 2, 3, 'Washington DC', '154876');

我需要创建一个函数来获取工人在表workdone上完成的所有工资的总和。例如，工人1已经完成了3个工作，这显示在工作表上。我需要得到他所做的每一项工作的工资，根据他的工作地点参照表工资。然后，我将把他所有的工资加在一起，并将该工人的总工资插入到包含totalsalary列的表work中。

CREATE OR REPLACE FUNCTION SALARYCOUNT ( worker_no IN NUMBER ) RETURN NUMBER IS
wcity WORKDONE.City%TYPE;
wpostal WORKDONE.Postal%TYPE;
total NUMBER:
i INTEGER: = 1;
total_salary NUMBER;
wsalary NUMBER;

BEGIN 
SELECT COUNT(*)
INTO total
FROM WORKDONE
WHERE WorkerNo = worker_no;
WHILE i < total
LOOP
SELECT City,Postal INTO wcity,wpostal FROM WORKDONE WHERE WorkerNo = worker_no;
SELECT Salary INTO wsalary FROM WorkSalary WHERE wcity = City AND wpostal = Postal;
total_salary: =total_salary+wsalary ;
UPDATE WORK SET TotalSalary: = total_salary
WHERE WorkerNo = worker_no;
i: = i+1;
END LOOP;
RETURN(total_salary);
END SALARYCOUNT ;
/

创建

函数时出现编译错误。我知道，如果我能够创建这个函数没有错误，我创建这个函数的逻辑也可能是不正确的。请帮助我的错误和代码的逻辑。我是新手

Answer 1

首先，正如我在评论中所说的，不要使用函数来执行DML语句(UPDATE/INSERT等)。使用程序。函数应该在SQL语句中调用，在选择时不能使用DML语句。

其次，WORKDONE上的外键不正确，您应该在相同的外键中添加CITY和POSTAL:

CONSTRAINT WORKDONE_FKEY2 FOREIGN KEY (City, Postal)
    REFERENCES WorkSalary(City, Postal) 

第三点，也是为将来做准备的，你的环境设置不正确;如果你在发布问题之前测试一下，这会很有帮助。

最后，绝对没有必要使用函数来完成此操作。这一切都可以在单个MERGE语句中完成。使用单个SQL语句几乎总是比使用PL/SQL更有效。

 merge into work m
 using ( select wd.workerno, sum(ws.salary) as salary
           from workdone wd
           join worksalary ws
             on wd.city = ws.city
            and wd.postal = ws.postal
          group by wd.workerno ) u
    on (m.workerno = u.workerno)
  when matched then
update
   set m.totalsalary = u.salary;

下面是一个工作的SQL Fiddle来演示