Oracle Note

1. Left join and right join
--left join example
SELECT a.department_name,
 b.first_name,
 a.department_id dep_a,
 b.department_id dep_b
FROM departments a,
 employees b
WHERE a.department_id =b.department_id(+) AND
b.department_id(+)=10;

2. record type
Can not define schema-level record type and cannot reference %type when creating object type.
Object type is "known and visible" to Oracle SQL engine AND PL/SQL engine.
Record Type is "known and visible" to PL/SQL engine only.

3. Oracle Optimization
The difference between the two optimizers is relatively clear: The CBO chooses the best path for your queries, based on what it knows about your data and
by leveraging Oracle database features such as bitmap indexes, function-based indexes, hash joins, index-organized tables, and partitioning, whereas the RBO
just follows established rules (heuristics). With the release of Oracle Database 10g, the RBO's obsolescence is official and the CBO has been significantly improved yet again.

4. For update
You can lock the rows with the FOR UPDATE clause in the cursor query. NO WAIT means not to wait if requested rows have been locked by another user.
declare
    cursor emp_cursor is
    select employee_id,last_name from employees where department_id = 80 FOR UPDATE OF salary NOWAIT;
WHERE CURRENT OF clause is used in conjunction with the FOR UPDATE clause to refer to the current row in an explicit cursor.

5. raise_application_error
raise_application_error(error_number,message[,{TRUE|FALSE}]);
error_number: is a user specified number for the exception between -20000 and -20999
TRUE|FALSE: optional. default is false, the error replaces all previous errors.
   
6. bulk binding
forall i in id.first .. id.last
    update employees set salary = salary*1.1 where manager_id = id(i) returning salary bulk collect into new_sals;    
  
7. Collection
Functions: exists,count,limit,first,next,prior
Procedures: extended,trim,delete
a. Use forall and indices of to skip null example:
declare
type id_table_type is table of number(6);
id_table id_table_type;
begin   
id_table := id_table_type(1,null,3,null,5);
forall i in indices of id_table
    delete from demo where id=id_table(i);
end;
/
b. use forall and values of example
declare
type id_table_type is table of demo.id%type;
type name_table_type is table of demo.name%type;
id_table id_table_type;
name_table name_table_type;
type index_pointer_type is table of pls_integer;
index_pointer index_pointer_type;
begin
select * bulk collect into id_table,name_table from demo;
index_pointer := index_pointer_type(6,8,10);
forall i in values of index_pointer
    insert into new_demo values(id_table(i),name_table(i));
end;
/
1) Indexed TABLE
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE
    INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
BEGIN
SELECT ename INTO ename_table(-1) FROM emp WHERE empno = 7788;
DBMS_OUTPUT.PUT_LINE('The name is:'||ename_table(-1));
END;
/   
2) Nested table
declare
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;
begin
ename_table:=ename_table_type('MARY','MARY','MARY');
select ename into ename_table(2) from emp where empno = &no;
dbms_output.put_line('The employee name: '||ename_table(2));
end;
/
3) VARRAY
CREATE TYPE article_type AS OBJECT (
title VARCHAR2(30), pubdate DATE
);
CREATE TYPE article_array IS VARRAY(20) OF article_type;
CREATE TABLE author(
id NUMBER(6),name VARCHAR2(10),article article_array
);

8.
UNION, INTERSECT and MINUS are ordered by the first column while UNION ALL is not ordered.

9. With statement
SQL>with summary as (select dname,sum(sal) as dept_total from emp,dept where emp.deptno = dept.deptno group by dname)
   select dname,dept_total from summary where dept_total>( select sum(dept_total)*1/3 from summary);
      
10. Use rowtype variable
declare
dept_record dept%ROWTYPE;
begin
dept_record.deptno := 50;
dept_record.dname := 'ADMINISTRATOR';
dept_record.loc := 'BEIJING';
INSERT INTO dept VALUES dept_record;
end;
/
declare
dept_record dept%ROWTYPE;
begin
dept_record.deptno := 50;
dept_record.dname := 'SALES';
dept_record.loc := 'SHANGHAI';
UPDATE dept SET ROW=dept_record where deptno = 50;
end;
/      

11. Exception
a. Use self defined exception
declare
e_integrity exception;
pragma exception_init(e_integrity,-2291);--Hold ORA-02291:?????????
begin
update emp set deptno = &dno where empno = &eno;
exception
when e_integrity then
    dbms_output.put_line('The department does not exist!');
end;
/
b. Raise self defined exception
declare
e_integrity exception;
pragma exception_init(e_integrity,-2291);
e_no_employee exception;
begin
update emp set deptno=&dno where empno = &eno;--10 1111
if sql%notfound then
    raise e_no_employee;
end if;
exception
when e_integrity then
    dbms_output.put_line('The department does not exist!');
when e_no_employee then
    dbms_output.put_line('The employee does not exist!');
end;
/
c. Raise application error
create or replace procedure raise_comm(eno number,commission number)
is
v_comm emp.comm%type;
begin
select comm into v_comm from emp where empno = eno;
if v_comm is null then
    raise_application_error(-20001,'The employee has no complementary!');
end if;
exception
when no_data_found then
    dbms_output.put_line('The employee does not exist!');
end;
/

12. Top N
select the N element order asc:
select * from (select t.*,dense_rank() over (PARTITION BY deptno order by sal) rank from employee t) where rank = N;