Convert Oracle PL/SQL to Snowflake SQL and Snowpark

-- Procedure: generate_account_summary
CREATE OR REPLACE PROCEDURE generate_summary AS
  v_run_date DATE := SYSDATE;
BEGIN
  INSERT INTO account_summary
  SELECT
    a.account_id,
    NVL(a.account_name, 'Unnamed'),
    DECODE(a.account_type,
      'SAV', 'Savings',
      'CHK', 'Checking',
      'LON', 'Loan',
      'Other') AS type_label,
    NVL2(a.close_date,
      'Closed', 'Active') AS status,
    TO_CHAR(a.open_date, 'YYYY-MM-DD'),
    v_run_date
  FROM accounts a
  WHERE a.open_date >=
    ADD_MONTHS(SYSDATE, -12)
    AND ROWNUM <= 100000;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    ROLLBACK;
END;

-- PL/SQL procedure → Snowflake Scripting
CREATE OR REPLACE PROCEDURE generate_summary()
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
  v_run_date DATE := CURRENT_DATE();
BEGIN
  INSERT INTO account_summary
  SELECT
    a.account_id,
    COALESCE(a.account_name, 'Unnamed'),
    IFF(a.account_type = 'SAV', 'Savings',
    IFF(a.account_type = 'CHK', 'Checking',
    IFF(a.account_type = 'LON', 'Loan',
        'Other'))) AS type_label,
    IFF(a.close_date IS NOT NULL,
      'Closed', 'Active') AS status,
    TO_CHAR(a.open_date, 'YYYY-MM-DD'),
    :v_run_date
  FROM accounts a
  WHERE a.open_date >=
    DATEADD('MONTH', -12, CURRENT_DATE())
  LIMIT 100000;

  RETURN 'Success';
EXCEPTION
  WHEN OTHER THEN
    ROLLBACK;
    RETURN SQLERRM;
END;
$$;