PL/SQL Datatypes

PLS_INTEGER

SIMPLE_INTEGER

BINARY_INTEGER

SIMPLE_INTEGER : It has NOT NULL constraint

BINARY_INTEGER : Stored in binary format, which takes less space

 

Sequences in PL/SQL expressions

Prior to Oracle 11g Release1, in order to access sequence values from within PL/SQL, it was necessary to execute a SQL statement (i.e., SELECT sequence.NEXTVAL from dual; ).  In 11g, you can assign the CURRVAL and NEXTVAL pseudocolumns in PL/SQL expression directly.

The CURRVAL and NEXTVAL make the PL/SQL source text easier and clear. They also improve runtime performance.

Two Single quotes or Alternative Quote Operator

select 'I am a character literal string' from dual;

What about the character literals that contain quotation marks ?

select 'Plural''s have one quote too many' from dual;

Using two single quotes to handle each naturally occurring single quote in a character literal can become messy .
Consider alternative quote(q) operator. The q operator enables you to choose from a set of possible pairs of wrapping symbols for character literals as alternatives to the single quote symbols.
SELECT q'<Plural's can also be specified with alternate quote operators>'  "q<>" FROM DUAL;

SELECT q'[Even square brackets' [] can be used for Plural's]' "q[]" FROM DUAL;

SELECT q'XWhat about UPPER CASE X for Plural's X' "qX" FROM DUAL;

The syntax of alternative quote operator is as follows:
q 'delimiter character literal which may include single quotes delimiter'
where delimiter can be any character or bracket.

Constraint State

ENABLE VALIDATE (This is the default state )
DISABLE NOVALIDATE
ENABLE NOVALIDATE
DISABLE VALIDATE

ON DELETE SET NULL and ON DELETE CASCADE

ON DELETE SET NULL:

Incase if a row in the parent table is removed, Oracle will search the child table for all the matching rows and set foreign key column to null. This means that the rows will be orphaned but will still exist. If the column in the child table also have a not-null constraint, then the deletion from the parent table will fail.

ON DELETE CASCADE:

Incase if a row in the parent table is removed, Oracle will search the child table for all the matching rows and delete them too.

Note: It is not possible to drop or truncate the parent table in a foreign-key relationship, even there are now rows in the child table. This still applies if the ON DELETE SET NULL and ON DELETE CASCADE are used.

MERGE Statement

MERGE statement is used to select rows from one or more sources for update or insert into a table or view. You can specify conditions to determine whether to update or insert into target table or view.

 MERGE statement was introduced in Oracle 9i

Examples:

MERGE INTO bonuses b
USING (
  SELECT employee_id, salary, dept_no
  FROM employee
  WHERE dept_no =20) e
ON (b.employee_id = e.employee_id)
WHEN MATCHED THEN
  UPDATE SET b.bonus = e.salary * 0.1
  DELETE WHERE (e.salary < 40000)
WHEN NOT MATCHED THEN
  INSERT (b.employee_id, b.bonus)
  VALUES (e.employee_id, e.salary * 0.05)
  WHERE (e.salary > 40000);

MERGE INTO employees e
    USING hr_records h
    ON (e.id = h.emp_id)
  WHEN MATCHED THEN
    UPDATE SET e.address = h.address
  WHEN NOT MATCHED THEN
    INSERT (id, address)
    VALUES (h.emp_id, h.address);