Nested Table Example 01

/**
   Demo the nested table. Use it in the schema. 
*/
-- Create a nested table type 
create or replace type scores_type as table of number;
/

-- Create a table with a nested table
-- You need to specify the table name for the nested table using
-- "nested table store as" clause.
create table student_scores (s_id number, scores scores_type)
nested table scores store as scores_tab;
/
-- 
select * from student_scores;

-- Insert values 
-- Use the type name as the constructor to create scores_type instances and store them
-- in the scores_tab table
insert into student_scores values (10, scores_type(100, 100));

-- Update
update student_scores
set scores = scores_type(90,100);

commit;

VARRAY Example 01

/*
  Demo the varray.
  
*/

-- create a varray with the maximum size 6.
create or replace type options_type as varray(6) of varchar2(30);
/

-- create a table with varray
-- You don't have to create another table to store the varray. 
create table questions(q_id number, options options_type);

-- insert values
insert into questions values(1, options_type('Option A', 'Option B', 'Option C',
'Option D'));
commit;

select * from questions;

-- Use PL/SQL codes to insert values to questions table.
set serveroutput on
declare
 options questions.options%type;
begin
  options := options_type('A', 'B', 'C', 'D', 'E', 'F');
  -- Insert a new question.
  insert into questions values (2, options);
  
  -- Print out the rows in the Question table
  for question in (select * from questions) loop
    dbms_output.put(question.q_id || ' ');
 -- Print out the options in a question.
    for i in 1..question.options.count loop
        dbms_output.put(question.options(i) || ' ');
    end loop;
    dbms_output.put_line(' ');
  end loop;
end;
/
rollback;

PL/SQL Reference

Handling Exceptions

Oracle 11g Database error messages
ORA-00984 : 若在 SQL Statement 中使用了 SQLCODE 及 SQLERRM 這兩個 functions, 會產生編譯錯誤，之後再產生 OAR-00984 例外。

Handling PL/SQL Errors from Oracle PL/SQL Language Reference


Date functions
Trunc(Date): 對日期的不同單位, 如年、月、日, 進行捨去與進位. 預設為捨入到最近的一天。

Nested Blocks in PL/SQL

PL/SQL 中提供 Nested Blocks 的功能, 其目的為:
* Use to handle exception locally (Codes from Tony Andrews):

begin
   for emp_rec in (select * from emp) loop
      begin
         my_proc (emp_rec);
      exception
         when some_exception then
            log_error('Failed to process employee '||emp_rec.empno);
      end;
   end loop;
end;

* Declare local variables that have limited scopes (Codes from Tony Andrews);

declare
    l_var1 integer;
    -- lots of variables
begin
   -- lots of lines of code
   ...
   for emp_rec in (select * from emp) loop
      declare
         l_localvar integer := 0;
      begin
         -- Use l_localvar
         ...
      end
   end loop;

end;

Reference:
When should I nest PL/SQL BEGIN…END blocks? (http://stackoverflow.com/questions/2334659/when-should-i-nest-pl-sql-begin-end-blocks)

PL/SQL interview question 001

Uri Lukach 提供了以下的 PL/SQL interview question[1]:

答案是 C.

使用 case-when expression 時有兩種方式, 一為 simple-case-expression, 另一為 searched-case-expression. 使用 searched-case-express 時, 若在 condition 中沒有使用 relational operator 便會產生 ORA-00920 invalid relational operator 的例外[2]。

但是, 我們不能將問題中的 condition 改成 v_input = TRUE, 因為此 case-when expression 是在 SELECT SQL statement 中使用，SQL engine 中沒有 boolean data type (只有在 pl/sql 中才有 boolean data type)。

以下正確執行的版本供參考:

 set serveroutput on  
 DECLARE  
 v_input boolean :=true;  
 v_result varchar2(10);  
 BEGIN  
 v_result := case   
    when v_input=true then 'TRUE'  
    when (v_input <>true) then 'FALSE'  
    else null  
    end;  
 DBMS_OUTPUT.PUT_LINE(v_result);  
 END;  
 /