#眉標=Oracle 10g
#副標=Oracle 10g開發專題（4）
#大標= PL/SQL預存程序的開發與除錯
#作者=文／何致億
-----box-----
#程式1
 
BEGIN
  SELECT count(*)  
  INTO   v_matched   
  FROM   employees
  WHERE  department_id = v_department_id;

  DBMS_OUTPUT.PUT_LINE
   ('Dept. ID'||CHR(9)||'Emp. ID'
      ||CHR(9)||'Name'||CHR(9)||'Salary');
  DBMS_OUTPUT.PUT_LINE('
    ---------------------------------------');

  OPEN emp_cur;
  FOR i IN 1 .. v_matched
  LOOP 
    FETCH emp_cur INTO emp_rec;
    DBMS_OUTPUT.PUT_LINE(
       emp_rec.department_id||CHR(9)||
       emp_rec.employee_id||CHR(9)||
       emp_rec.first_name||CHR(9)||
       emp_rec.salary);
  END LOOP;
  CLOSE emp_cur;
END;
-----end-----

-----box-----
Successful compilation: 0 errors, o warnings 
-----end-----

-----box-----
#程式2
DECLARE
  V_DEPARTMENT_ID NUMBER;
BEGIN
  V_DEPARTMENT_ID := NULL;

  HR.FIND_EMP_BY_DEPT(
    V_DEPARTMENT_ID => V_DEPARTMENT_ID
  );
END;
-----end-----

-----box-----
#程式3
PROCEDURE FIND_EMP_BY_RANK
(v_department_id IN NUMBER, v_rank IN NUMBER)
IS
   CURSOR emp_cur IS
      SELECT   * 
      FROM     employees 
      WHERE    department_id = v_department_id
      ORDER BY salary DESC;

   emp_rec  emp_cur%ROWTYPE;
   v_matched   NUMBER; 
   v_rowcount  NUMBER; 
   v_notEnough BOOLEAN := false;
    
BEGIN
  SELECT count(*)  
  INTO   v_matched   
  FROM   employees
  WHERE  department_id = v_department_id;

  IF v_rank < v_matched THEN
     v_notEnough := true;
     v_rowcount  := v_matched;
  ELSE
     v_rowcount  := v_rank;
  END IF;

  DBMS_OUTPUT.PUT_LINE(
    'DeptID'||CHR(9)||'EmpID'||CHR(9)||
    'Salary'||CHR(9)||'Name');
  DBMS_OUTPUT.PUT_LINE('
     --------------------------------------');

  OPEN emp_cur;
  FOR i IN 1 .. v_rowcount
  LOOP 
    FETCH emp_cur INTO emp_rec;
    DBMS_OUTPUT.PUT_LINE(
       emp_rec.department_id||CHR(9)||
       emp_rec.employee_id||CHR(9)||
       emp_rec.salary||CHR(9)||
       emp_rec.first_name);
  END LOOP;
  CLOSE emp_cur;
  
  IF v_notEnough = true THEN
    dbms_output.put_line (CHR(10)||
    'P.S: Query results matched only have '||
      v_rowcount||' rows');
  END IF;
END;
-----end-----


-----box-----
GRANT DEBUG CONNECT SESSION, DEBUG ANY PROCEDURE
TO    hr;
-----end-----

-----box-----
#程式3
  IF v_rank > v_matched THEN
     v_notEnough := true;
     v_rowcount  := v_matched;
  ELSE
     v_rowcount  := v_rank;
  END IF;
-----end-----