[Oracle] 기본

데이터 검색, 조작

--------------------------------------------------------------------------------

SELECT [column_name1] 

FROM [table1]

[WHERE [column_name1] = [column_data1]]

[AND [column_name2] = [column_data2]]

[GROUP BY [column_name1]]

[ORDER BY [column_name1]];

--------------------------------------------------------------------------------

INSERT INTO [table1]

  ( [column_name1]

  ) 

VALUES

  ( [insert_data]

  );

  

--------------------------------------------------------------------------------

UPDATE [table1]

SET [column_name1] = [update_data1]

WHERE [column_name1]   = [column_data1];

--------------------------------------------------------------------------------

DELETE 

FROM [table1] 

WHERE [column_name1] = [column_data1];

--------------------------------------------------------------------------------

COMMIT;

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

INSERT INTO [table1]

  ( [column_name1],

  [column_name2],

  [column_name3]

  ) 

VALUES

  ( [insert_data_01],

  [insert_data_02],

  [insert_data_03]

  ) ;

  

  

INSERT INTO [table1] 

VALUES

  ( [insert_data_01],

  [insert_data_02],

  [insert_data_03]

  );

  

INSERT INTO [table1]

  ( [column_name1], [column_name2], [column_name3]

  )

SELECT [insert_data_01], [insert_data_02], [insert_data_03] 

FROM dual;

UPDATE (

  SELECT t.[column_name1]

  FROM [table1] t

  WHERE t.[column_name2] IN ([column_data2_01], [column_data2_02], [column_data2_03])

  )

SET [column_name1] = [update_data1];

UPDATE

  (SELECT [column_name1],

    [column_name2]

  FROM [table1]

  WHERE [column_name3] = [column_data2_03]

  AND [column_name4]   = [column_data2_04]

  )

SET [column_name1] = [column_data2_01],

  [column_name2]   = [column_data2_02];

DELETE

FROM [table1] tt

WHERE (tt.[column_name1], tt.[column_name2]) IN

  (SELECT t.[column_name1],

    t.[column_name2]

  FROM [table1] t

  WHERE t.[column_name1] = t.[column_data1]

  AND t.[column_name2] = t.[column_data2]

  );

--------------------------------------------------------------------------------

MERGE INTO [table1] t1 

USING (SELECT [column_name1] FROM [table2]

WHERE [column_name2] = [data]) t2 

ON (t1.[column_name1] = t2.[column_name1])

WHEN MATCHED THEN UPDATE SET t1.[column_name2] = [update_data] 

  DELETE WHERE (t2.[column_name1] > [data])

WHEN NOT MATCHED THEN  INSERT ([column_name1])

  VALUES  ([insert_data1])

  WHERE (t2.[column_name1] <= [data]);

--------------------------------------------------------------------------------

MERGE INTO [table1] t1 USING

(SELECT [column_data1] AS [column_name1] FROM dual

) t2 ON (t1.[column_name1] = t2.[column_name1])

WHEN MATCHED THEN

  UPDATE SET t1.[column_name2] = [update_data] 

WHEN NOT MATCHED THEN

  INSERT

    ([column_name1], [column_name2], [column_name3], [column_name4], [column_name5]

    )

  VALUES

    ([insert_data1], [insert_data2], [insert_data3], [insert_data4], [insert_data5]

    );

--------------------------------------------------------------------------------

WITH T AS

  (SELECT '1', 'a' FROM dual

  UNION ALL

  SELECT '2', 'b' FROM dual

  [UNION ALL 

  ...]

  )

SELECT * FROM T;

--------------------------------------------------------------------------------

-- Oracle SQL

SELECT *

FROM [table1] t1,

[table2] t2,

[table3] t3

WHERE t1.[column1] = t2.[column1]

AND t1.[column1] = t3.[column1](+)

AND [condition2]

AND [condition3]

-- ANSI SQL

SELECT *

FROM [table1] t1

INNER JOIN [table2] t2

ON t1.[column1] = t2.[column1]

AND [condition2]

LEFT OUTER JOIN [table3] t3

ON t1.[column1] = t3.[column1]

AND [condition3]