sql/note.sql

   1 NEXT_DAY(CURRENT_DATE, 'WEDNESDAY')
   2 DROP TYPE SYS_REFCURSOR CASCADE;
   3 CREATE TYPE SYS_REFCURSOR AS INT;
   4
   5 SET DATABASE REFERENTIAL INTEGRITY FALSE;
   6 SET DATABASE REFERENTIAL INTEGRITY TRUE;
   7 SELECT * FROM UNNEST(SEQUENCE_ARRAY(CURRENT_DATE, CURRENT_DATE + 6 DAY, 1 DAY)) WITH ORDINALITY AS T(D, I);
   8
   9 CREATE FUNCTION an_hour_before (t TIMESTAMP)
  10   RETURNS TIMESTAMP
  11   RETURN t - 1 HOUR
  12
  13 CREATE PROCEDURE delete_extra_customers(INOUT val_p INT, IN lastname_p VARCHAR(20))
  14   MODIFIES SQL DATA
  15   BEGIN ATOMIC
  16     SET val_p = 0;
  17     for_label: FOR SELECT * FROM customers WHERE lastname = lastname_p DO
  18       IF val_p > 0 THEN
  19       DELETE FROM customers WHERE customers.id = id;
  20       END IF;
  21       SET val_p = val_p + 1;
  22     END FOR for_label;
  23   END
  24
  25 -- DECLARE my_ver INTEGER DEFAULT 2;
  26 -- loop_label: WHILE my_var < 20 DO
  27 --   INSERT INTO CUSTOMERS VALUES (DEFAULT, my_var);
  28 --   SET my_var = my_var + 1;
  29 --   -- LEAVE can be used to break the loop
  30 --   IF my_var = 15 THEN LEAVE loop_label; END IF;
  31 -- END WHILE loop_label;
  32
  33 -- Array of dates
  34 SELECT *
  35 FROM UNNEST(SEQUENCE_ARRAY(DATE '2025-04-07', CURRENT_DATE + 7 DAY, 1 DAY))
  36        WITH ORDINALITY AS T(D, I);
  37 DECLARE LOCAL NAMES TABLE test_retval (x INT);
  38
  39
  40
  41
  42
  43 CREATE TABLE customers(id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, firstname VARCHAR(50), lastname VARCHAR(50), added TIMESTAMP);
  44
  45 CREATE TABLE addresses(id INTEGER GENERATED BY DEFAULT AS IDENTITY, customerid INTEGER, address VARCHAR(50));
  46
  47 ALTER TABLE addresses ADD CONSTRAINT fk_addr FOREIGN KEY(customerid) REFERENCES customers(id);
  48
  49 \.
  50 DROP PROCEDURE new_customer IF EXISTS;
  51 CREATE PROCEDURE new_customer(firstname VARCHAR(50), lastname VARCHAR(50))
  52    MODIFIES SQL DATA DYNAMIC RESULT SETS 1
  53    BEGIN ATOMIC
  54      DECLARE result CURSOR FOR SELECT * FROM CUSTOMERS WHERE ID = IDENTITY();
  55      INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP);
  56      OPEN result;
  57    END
  58 .;
  59
  60 --CREATE VIEW Prix
  61 --CREATE DOMAIN valid_string AS VARCHAR(20) DEFAULT 'NO VALUE' CHECK (VALUE IS NOT NULL AND CHARACTER_LENGTH(VALUE) > 2)
  62 --select "Prix".*, P.*
  63 --  FROM TABLE (produit_maxdate(CURRENT_DATE)) as P
  64 --  JOIN "Prix" ON "Prix".ID = P.ID
  65 --   AND "Prix".date = P.max_date
  66 --;
  67 -- SELECT "PRICES".*
  68 -- FROM ( SELECT "PID", MAX( "D" ) AS "MD"
  69 --         FROM "PRICES" JOIN "PROD" ON "PROD"."ID" = "PRICES"."PID"
  70 --         WHERE "EX" = FALSE AND "D" <= COALESCE ( :pDate, CURRENT_DATE )
  71 --         GROUP BY "PID" ) AS "X"
  72 -- JOIN "PRICES" ON "PRICES"."PID" = "X"."PID"
  73 --  AND "PRICES"."D" = "X"."MD"
  74
  75 --SET retval = SEQUENCE_ARRAY(DATE '2025-04-07', DATE '2025-04-17', 1 DAY);
  76 --select count(*) from information_schema.columns where table_name='LOB_IDS' and table_schema='SYSTEM_LOBS';