Convert Oracle PL/SQL to BigQuery SQL

-- Procedure: refresh_department_metrics
CREATE OR REPLACE PROCEDURE refresh_metrics AS
  v_count NUMBER := 0;
  CURSOR c_depts IS
    SELECT department_id,
      NVL(department_name, 'Unassigned'),
      DECODE(status, 'A', 'Active',
        'I', 'Inactive', 'Unknown')
    FROM departments
    WHERE create_date >=
      ADD_MONTHS(SYSDATE, -6);
BEGIN
  FOR rec IN c_depts LOOP
    INSERT INTO dept_metrics (
      dept_id, dept_name, status_label,
      headcount, run_date
    )
    SELECT rec.department_id,
      rec.department_name,
      rec.status,
      COUNT(*),
      SYSDATE
    FROM employees e
    WHERE e.dept_id = rec.department_id;

    v_count := v_count + 1;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE(
    'Processed: ' || TO_CHAR(v_count));
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;

-- PL/SQL procedure → BigQuery SQL
CREATE OR REPLACE PROCEDURE
  dataset.refresh_metrics()
BEGIN
  DECLARE v_count INT64 DEFAULT 0;

  INSERT INTO dataset.dept_metrics
    (dept_id, dept_name, status_label,
     headcount, run_date)
  SELECT
    d.department_id,
    COALESCE(d.department_name,
             'Unassigned'),
    CASE d.status
      WHEN 'A' THEN 'Active'
      WHEN 'I' THEN 'Inactive'
      ELSE 'Unknown'
    END AS status_label,
    COUNT(e.employee_id) AS headcount,
    CURRENT_DATE() AS run_date
  FROM dataset.departments d
  LEFT JOIN dataset.employees e
    ON e.dept_id = d.department_id
  WHERE d.create_date >=
    DATE_SUB(CURRENT_DATE(),
             INTERVAL 6 MONTH)
  GROUP BY d.department_id,
    d.department_name, d.status;

  SET v_count = (
    SELECT COUNT(DISTINCT dept_id)
    FROM dataset.dept_metrics
    WHERE run_date = CURRENT_DATE());

EXCEPTION WHEN ERROR THEN
  SELECT @@error.message;
END;