찾으시는 정보가 있으시다면
주제별reference를 이용하시거나
우측 상단에 있는 검색기능을 이용해주세요

SELECT INTO

Copies data from one table into a new table

The new table will be created with the column-names and types as defined in the old table

• You can create new column names using the AS clause

Syntax
SELECT column_name(s)
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

INSERT INTO SELECT

Copies data from one table and inserts it into another table

• The existing records in the target table are unaffected

Requires that the data types in source and target tables match

Syntax
INSERT INTO table2(column(s))
SELECT column(s)
FROM table1
WHERE condition;

CASE

Goes through conditions and returns a value when the first condition is met

• Like an if-then-else statement

Once a condition is true, it will stop reading and return the result

If no conditions are true, it returns the value in the ELSE clause

• If there is no ELSE part and no conditions are true, it returns NULL

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result
END;

NULL Function

Lets you return an alternative value when an expression is NULL

NVL(column_name, alternative value)

PROCEDURE

A stored procedure is a prepared SQL code that you can save, so the code can be reused

-- Stored procedure
CREATE PROCEDURE procedure_name
    (parameter_name1 IN parameter_type1)
IS
    var_name var_type := value;
BEGIN   
    sql_statement
END;

-- Execute a stored procedure
EXEC procedure_name;

Comments

Are used to explain sections of SQL statements, or to prevent execution of SQL statements

Single line comments

• It start with --

• Any text between – and the end of the line will be ignored

Multi-line comments

• It start with /* and end with */

• Any text between /* and */ will be ignored