CREATE TYPE AS VARARRAY

Örnek

Şöyle yaparız.

create or replace type integer_varray as varray (4000) of int;
create table plan_capacities
(
  id       int generated by default as identity not null
    constraint plan_capacities_pkey primary key,
  line_id  int references lines (id) on delete cascade,
  model_id int references models (id) on delete cascade,
  plan_id  int references plans (id) on delete cascade,
  capacity integer_varray
);

ROWNUM

Giriş

Açıklaması şöyle

ROWNUM is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N , where N is the number of rows in the set ROWNUM is used with. A ROWNUM value is not assigned permanently to a row (this is a common misconception). A row in a table does not have a number; you cannot ask for row 5 from a table—there is no such thing.

Also confusing to many people is when a ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned,...

Örnek

Sort işleminden sonra rownum verildiğini görmek için şöyle yaparız

SELECT ROWNUM, customers.*
FROM customers
WHERE customer_id > 4500;

ROWNUM  CUSTOMER_ID  LAST_NAME  FIRST_NAME  FAVORITE_WEBSITE
------  -----------  ---------  ----------  ---------------------
     1         5000  Smith      Jane        www.digminecraft.com
     2         6000  Ferguson   Samantha    www.bigactivities.com
     3         7000  Reynolds   Allen       www.checkyourmath.com
     4         8000  Anderson   Paige
     5         9000  Johnson    Derek       www.techonthenet.com

SELECT ROWNUM, customers.*
FROM customers
WHERE customer_id > 4500
ORDER BY last_name;

ROWNUM   CUSTOMER_ID   LAST_NAME   FIRST_NAME   FAVORITE_WEBSITE
------   -----------   ---------   ----------   ---------------------
     4          8000   Anderson    Paige
     2          6000   Ferguson    Samantha     www.bigactivities.com
     5          9000   Johnson     Derek        www.techonthenet.com
     3          7000   Reynolds    Allen        www.checkyourmath.com
     1          5000   Smith       Jane         www.digminecraft.com

SYSTIMESTAMP Function

Giriş

TIMESTAMP WITH TIME ZONE cinsindendir.

Örnek

Şöyle yaparız

SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
------------------------------------------------------------------
28-MAR-00 12.38.55.538741 PM -08:00

Örnek

Son 1 saat içindeki tüm satırları almak için şöyle yaparız. Burada ts sütunu SYSTIMESTAMP değerindendir

SELECT * FROM MyTable WHERE ts > SYSTIMESTAMP -(1/24) ORDER BY ts DESC;