SELECT를 사용할 수 없는 이유...집계 기능이 있는 업데이트의 경우?

SELECT를 사용할 수 없는 이유...집계 기능이 있는 업데이트의 경우?

레코드 집합에 대한 데이터베이스 열의 합계()를 찾고 나중에 다음과 유사한 별도 쿼리에서 이 합계를 사용하는 응용 프로그램이 있습니다(구성된 테이블이지만 아이디어는 동일합니다).

SELECT Sum(cost)
INTO v_cost_total
FROM materials
WHERE material_id >=0
AND material_id <= 10; 

[a little bit of interim work]

SELECT material_id, cost/v_cost_total
INTO v_material_id_collection, v_pct_collection
FROM materials
WHERE material_id >=0
AND material_id <= 10
FOR UPDATE; 

그러나 이론상으로는 두 쿼리 간에 재료 테이블의 비용 열을 업데이트할 수 있으며, 이 경우 계산된 백분율이 비활성화됩니다.

첫 번째 쿼리에 FORUPDATE 절을 사용하는 것이 이상적이지만, 이를 시도하면 다음과 같은 오류가 발생합니다.

ORA-01786: FOR UPDATE of this query expression is not allowed

이제 해결 방법은 문제가 아닙니다. Sum()을 찾기 전에 행을 잠그는 추가 쿼리를 수행하면 됩니다. 하지만 이 쿼리는 테이블을 잠그는 것 외에는 다른 목적이 없습니다.이 예제는 시간이 많이 소요되지는 않지만 추가 쿼리는 특정 상황에서 성능 저하를 일으킬 수 있으며, 이는 깨끗하지 않기 때문에 수행하지 않아도 됩니다.

이것이 허용되지 않는 특별한 이유를 아는 사람이 있습니까?내 머릿속에서, FOR UPDATE 조항은 WHERE 조항과 일치하는 행을 잠가야 합니다. - 저는 우리가 그 행들로 무엇을 하고 있는지가 왜 중요한지 모르겠습니다.

편집: 보기에 SELECT...FOR UPDATE는 아래 David Aldridge가 제안한 것처럼 분석 기능과 함께 사용할 수 있습니다.이것이 작동하는지 증명하기 위해 사용한 테스트 스크립트입니다.

SET serveroutput ON;

CREATE TABLE materials (
    material_id NUMBER(10,0),
    cost        NUMBER(10,2)
);
ALTER TABLE materials ADD PRIMARY KEY (material_id);
INSERT INTO materials VALUES (1,10);
INSERT INTO materials VALUES (2,30);
INSERT INTO materials VALUES (3,90);

<<LOCAL>>
DECLARE
    l_material_id materials.material_id%TYPE;
    l_cost        materials.cost%TYPE;
    l_total_cost  materials.cost%TYPE;

    CURSOR test IS
        SELECT material_id,
            cost,
            Sum(cost) OVER () total_cost
        FROM   materials
        WHERE  material_id BETWEEN 1 AND 3
        FOR UPDATE OF cost;
BEGIN
    OPEN test;
    FETCH test INTO l_material_id, l_cost, l_total_cost;
    Dbms_Output.put_line(l_material_id||' '||l_cost||' '||l_total_cost);
    FETCH test INTO l_material_id, l_cost, l_total_cost;
    Dbms_Output.put_line(l_material_id||' '||l_cost||' '||l_total_cost);
    FETCH test INTO l_material_id, l_cost, l_total_cost;
    Dbms_Output.put_line(l_material_id||' '||l_cost||' '||l_total_cost);
END LOCAL;
/

출력은 다음과 같습니다.

1 10 130
2 30 130
3 90 130

구문select . . . for update업데이트를 준비하기 위해 테이블의 레코드를 잠급니다.집계를 수행하면 결과 집합이 더 이상 원래 행을 참조하지 않습니다.

즉, 데이터베이스에 업데이트할 레코드가 없습니다.단지 일시적인 결과 세트가 있을 뿐입니다.

다음과 같은 방법을 사용할 수 있습니다.

<<LOCAL>>
declare
  material_id materials.material_id%Type;
  cost        materials.cost%Type;
  total_cost  materials.cost%Type;
begin
  select material_id,
         cost,
         sum(cost) over () total_cost
  into   local.material_id,
         local.cost,
         local.total_cost 
  from   materials
  where  material_id between 1 and 3
  for update of cost;

  ...

end local;

첫 번째 행은 총 비용을 제공하지만 모든 행을 선택하고 이론적으로 잠글 수 있습니다.

이것이 허용되는지는 모르겠습니다만, 여러분 -- 그것이 허용되는지에 대해 흥미롭게 들어보세요.

예를 들어, 아래와 같이 와 를 포함하는 표가 있습니다.

product표:

이드	이름.	주식.
1	사과	3
2	오렌지색	5
3	레몬	8

그러면 아래 2개의 쿼리를 모두 실행할 수 있습니다.sum()그리고.SELECT FOR UPDATE함께:

SELECT sum(stock) FROM (SELECT * FROM product FOR UPDATE) AS result;

WITH result AS (SELECT * FROM product FOR UPDATE) SELECT sum(stock) FROM result;

출력:

 sum
-----
  16
(1 row)

이를 위해 다음을 사용할 수 있습니다.WITH지휘권

예:

WITH result AS (
  -- your select
) SELECT * FROM result GROUP BY material_id;

당신의 문제는 "하지만 이론적으로 누군가가 두 쿼리 사이에서 재료 테이블의 비용 열을 업데이트할 수 있으며, 이 경우 계산된 백분율이 비활성화됩니다."라는 것입니까?

이 경우 다음과 같이 내부 쿼리를 사용할 수 있습니다.

SELECT material_id, cost/(SELECT Sum(cost)
  FROM materials
  WHERE material_id >=0
  AND material_id <= 10)
INTO v_material_id_collection, v_pct_collection
FROM materials
WHERE material_id >=0
AND material_id <= 10;

왜 테이블을 잠그려고 합니까?이 시간 동안 다른 응용 프로그램이 해당 테이블을 업데이트하려고 하면 실패할 수 있습니다.

언급URL : https://stackoverflow.com/questions/18455473/why-cant-i-use-select-for-update-with-aggregate-functions