DECLARE  
  v_empno employees.EMPLOYEE_ID%TYPE;  
  v_ename employees.FIRST_NAME%TYPE;  
BEGIN  
  UPDATE employees SET FIRST_NAME = 'superdb' WHERE EMPLOYEE_ID = 206 RETURNING EMPLOYEE_ID, FIRST_NAME INTO v_empno, v_ename;  
  DBMS_OUTPUT.PUT_LINE('Updated EMPLOYEE_ID: ' || v_empno || ', FIRST_NAME: ' || v_ename);  
END;
/

Updated EMPLOYEE_ID: 206, FIRST_NAME: superdb

PL/SQL procedure successfully completed.

HR@orcl> select EMPLOYEE_ID, FIRST_NAME,SALARY FROM employees WHERE DEPARTMENT_ID = 110 ;

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        205 Shelley                   12008
        206 William                    8300

DECLARE  
  TYPE emp_tab IS TABLE OF employees.EMPLOYEE_ID%TYPE INDEX BY PLS_INTEGER;  
  v_empnos emp_tab;  
  TYPE name_tab IS TABLE OF employees.FIRST_NAME%TYPE INDEX BY PLS_INTEGER;  
  v_enames name_tab;  
BEGIN  
  -- 正確的多列多行處理示例：  
  UPDATE employees SET FIRST_NAME = 'John Doe' WHERE DEPARTMENT_ID = 110   
  RETURNING EMPLOYEE_ID, FIRST_NAME BULK COLLECT INTO v_empnos, v_enames; 
  
  -- 遍歷并輸出  
  FOR i IN 1 .. v_empnos.COUNT LOOP  
    DBMS_OUTPUT.PUT_LINE('Empno: ' || v_empnos(i) || ', Ename: ' || v_enames(i));  
  END LOOP;  
END;
/
Empno: 205, Ename: John Doe
Empno: 206, Ename: John Doe

PL/SQL procedure successfully completed.

DECLARE  
  TYPE emp_rec IS RECORD (  
    empno employees.EMPLOYEE_ID%TYPE,  
    ename employees.FIRST_NAME%TYPE  
  );  
  TYPE emp_tab IS TABLE OF emp_rec INDEX BY PLS_INTEGER;  
  v_emps emp_tab;  
BEGIN  
  -- 多列多行處理示例
  UPDATE employees SET FIRST_NAME = 'superdb' WHERE DEPARTMENT_ID = 110  
  RETURNING EMPLOYEE_ID, FIRST_NAME BULK COLLECT INTO v_emps;  
  -- 遍歷并輸出    
  FOR i IN 1 .. v_emps.COUNT LOOP  
    DBMS_OUTPUT.PUT_LINE('Empno: ' || v_emps(i).empno || ', Ename: ' || v_emps(i).ename);  
  END LOOP;  
END;
/
Empno: 205, Ename: superdb
Empno: 206, Ename: superdb

PL/SQL procedure successfully completed.

HR@orcl> select EMPLOYEE_ID, FIRST_NAME,SALARY FROM employees WHERE DEPARTMENT_ID = 110 ;

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        205 Shelley                   12008
        206 William                    8300

-- 您可以使用組函數(shù)執(zhí)行另一個(gè)SQL語(yǔ)句來(lái)檢索這些信息。

DECLARE l_total INTEGER; 
BEGIN 
   UPDATE employees 
      SET salary = salary * 2 
    WHERE DEPARTMENT_ID = 110;
   -- 要做SUM運(yùn)算，需要寫(xiě)很多代碼。
   SELECT SUM (salary) 
     INTO l_total 
     FROM employees 
    WHERE DEPARTMENT_ID = 110;
 
   DBMS_OUTPUT.put_line (l_total); 
END;

-- 可以在PL/SQL中執(zhí)行計(jì)算。使用RETURNING可以收回所有修改后的工資。然后對(duì)它們進(jìn)行迭代，一條語(yǔ)句完成總和。

DECLARE 
   l_salaries   DBMS_SQL.number_table; 
   l_total      INTEGER := 0; 
BEGIN 
      UPDATE employees 
         SET salary = salary * 2 
       WHERE DEPARTMENT_ID = 110
   RETURNING salary 
        BULK COLLECT INTO l_salaries; 
 
   FOR indx IN 1 .. l_salaries.COUNT 
   LOOP 
      l_total := l_total + l_salaries (indx); 
   END LOOP; 
 
   DBMS_OUTPUT.put_line (l_total); 
END;
/

HR@orcl> select EMPLOYEE_ID, FIRST_NAME,SALARY FROM employees WHERE DEPARTMENT_ID = 110 ;

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        205 Shelley                   12008
        206 William                    8300

DECLARE l_total INTEGER; 
BEGIN 
  UPDATE employees 
     SET salary = salary * 2 
   WHERE DEPARTMENT_ID = 110
  RETURNING SUM (salary) INTO l_total; 
  DBMS_OUTPUT.put_line (l_total); 
END;
/

DECLARE  
   l_EMPLOYEE_ID   employees.EMPLOYEE_ID%TYPE;  
BEGIN  
   EXECUTE IMMEDIATE 
   q'[UPDATE employees  
         SET FIRST_NAME = FIRST_NAME || '-1' 
       WHERE EMPLOYEE_ID=206
      RETURNING EMPLOYEE_ID INTO :one_para_id]'       
   RETURNING INTO l_EMPLOYEE_ID;  
  
   DBMS_OUTPUT.put_line (l_EMPLOYEE_ID);   
END;
/

DECLARE  
   l_EMPLOYEE_ID   DBMS_SQL.number_table;  
BEGIN  
   EXECUTE IMMEDIATE 
   q'[UPDATE employees  
         SET FIRST_NAME = FIRST_NAME || 'list' 
	  WHERE DEPARTMENT_ID = 110
      RETURNING EMPLOYEE_ID INTO :para_list]'       
   RETURNING BULK COLLECT INTO l_EMPLOYEE_ID;  
  
   FOR indx IN 1 .. l_EMPLOYEE_ID.COUNT  
   LOOP  
      DBMS_OUTPUT.put_line (l_EMPLOYEE_ID (indx));  
   END LOOP;  
END;
/