Основы языка PL/SQL. Гаршина В.В - 45 стр.

UptoLike

Составители: 

45
FOR EACH ROW
DECLARE
rowcnt NUMBER;
BEGIN
SELECT COUNT(*) INTO rowcnt
FROM Emp_tab
WHERE empno = :n.empno;
IF rowcnt = 0 THEN
INSERT INTO Emp_tab (empno,ename)
VALUES (:n.empno, :n.ename);
ELSE
UPDATE Emp_tab
SET Emp_tab.ename = :n.ename
WHERE Emp_tab.empno = :n.empno;
END IF;
SELECT COUNT(*) INTO rowcnt
FROM Dept_tab
WHERE deptno = :n.deptno;
IF rowcnt = 0 THEN
INSERT
INTO Dept_tab (deptno, dept_type)
VALUES(:n.deptno, :n.dept_type);
ELSE
UPDATE Dept_tab
SET Dept_tab.dept_type = :n.dept_type
WHERE Dept_tab.deptno = :n.deptno;
END IF;
END;
FOR EACH ROW
DECLARE
  rowcnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO rowcnt
    FROM Emp_tab
    WHERE empno = :n.empno;
  IF rowcnt = 0 THEN
    INSERT INTO Emp_tab (empno,ename)
       VALUES (:n.empno, :n.ename);
  ELSE
    UPDATE Emp_tab
       SET Emp_tab.ename = :n.ename
    WHERE Emp_tab.empno = :n.empno;
  END IF;

  SELECT COUNT(*) INTO rowcnt
    FROM Dept_tab
    WHERE deptno = :n.deptno;

  IF rowcnt = 0 THEN
    INSERT
       INTO Dept_tab (deptno, dept_type)
       VALUES(:n.deptno, :n.dept_type);
  ELSE
    UPDATE Dept_tab
    SET Dept_tab.dept_type = :n.dept_type
    WHERE Dept_tab.deptno = :n.deptno;
  END IF;

END;




                       45