Oracle PL/SQL interview note

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.

Left join and right join

Exception
e_integration exception;
pragma exception_init(e_integration,-2291);

raise_application_error(-20001,'The employee has no complementary!');

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

);

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

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;

Most complicated sql query:

B*Tree index is used mostly (one key to one row/range) while Bitmap index is used for low cardinality data (Y,N and null).

dynamic sql