NEXT_DAY(CURRENT_DATE, 'WEDNESDAY')
DROP TYPE SYS_REFCURSOR CASCADE;
CREATE TYPE SYS_REFCURSOR AS INT;

SET DATABASE REFERENTIAL INTEGRITY FALSE;
SET DATABASE REFERENTIAL INTEGRITY TRUE;
SELECT * FROM UNNEST(SEQUENCE_ARRAY(CURRENT_DATE, CURRENT_DATE + 6 DAY, 1 DAY)) WITH ORDINALITY AS T(D, I);

CREATE FUNCTION an_hour_before (t TIMESTAMP)
  RETURNS TIMESTAMP
  RETURN t - 1 HOUR

CREATE PROCEDURE delete_extra_customers(INOUT val_p INT, IN lastname_p VARCHAR(20))
  MODIFIES SQL DATA
  BEGIN ATOMIC
    SET val_p = 0;
    for_label: FOR SELECT * FROM customers WHERE lastname = lastname_p DO
      IF val_p > 0 THEN
      DELETE FROM customers WHERE customers.id = id;
      END IF;
      SET val_p = val_p + 1;
    END FOR for_label;
  END

-- DECLARE my_ver INTEGER DEFAULT 2;
-- loop_label: WHILE my_var < 20 DO
--   INSERT INTO CUSTOMERS VALUES (DEFAULT, my_var);
--   SET my_var = my_var + 1;
--   -- LEAVE can be used to break the loop
--   IF my_var = 15 THEN LEAVE loop_label; END IF;
-- END WHILE loop_label;

-- Array of dates
SELECT *
FROM UNNEST(SEQUENCE_ARRAY(DATE '2025-04-07', CURRENT_DATE + 7 DAY, 1 DAY))
       WITH ORDINALITY AS T(D, I);
DECLARE LOCAL NAMES TABLE test_retval (x INT);





CREATE TABLE customers(id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, firstname VARCHAR(50), lastname VARCHAR(50), added TIMESTAMP);

CREATE TABLE addresses(id INTEGER GENERATED BY DEFAULT AS IDENTITY, customerid INTEGER, address VARCHAR(50));

ALTER TABLE addresses ADD CONSTRAINT fk_addr FOREIGN KEY(customerid) REFERENCES customers(id);

\.
DROP PROCEDURE new_customer IF EXISTS;
CREATE PROCEDURE new_customer(firstname VARCHAR(50), lastname VARCHAR(50))
   MODIFIES SQL DATA DYNAMIC RESULT SETS 1
   BEGIN ATOMIC
     DECLARE result CURSOR FOR SELECT * FROM CUSTOMERS WHERE ID = IDENTITY();
     INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP);
     OPEN result;
   END
.;

--CREATE VIEW Prix
--CREATE DOMAIN valid_string AS VARCHAR(20) DEFAULT 'NO VALUE' CHECK (VALUE IS NOT NULL AND CHARACTER_LENGTH(VALUE) > 2)
--select "Prix".*, P.*
--  FROM TABLE (produit_maxdate(CURRENT_DATE)) as P
--  JOIN "Prix" ON "Prix".ID = P.ID
--   AND "Prix".date = P.max_date
--;
-- SELECT "PRICES".*
-- FROM ( SELECT "PID", MAX( "D" ) AS "MD"
--         FROM "PRICES" JOIN "PROD" ON "PROD"."ID" = "PRICES"."PID"
--         WHERE "EX" = FALSE AND "D" <= COALESCE ( :pDate, CURRENT_DATE )
--         GROUP BY "PID" ) AS "X"
-- JOIN "PRICES" ON "PRICES"."PID" = "X"."PID"
--  AND "PRICES"."D" = "X"."MD"

--SET retval = SEQUENCE_ARRAY(DATE '2025-04-07', DATE '2025-04-17', 1 DAY);
--select count(*) from information_schema.columns where table_name='LOB_IDS' and table_schema='SYSTEM_LOBS';