Posted By

geekzspot on 12/07/10


Tagged

database Oracle PLSQL DDL


Versions (?)

boolean column for oracle database tables


 / Published in: SQL
 

Boolean values (usually represented as Checkboxes in a User Interface)

This is efficient because is does not suffer the clumsy and inefficient case problems of WHERE UPPER(column) = UPPER(variable);

  1. CREATE TABLE booleans
  2. (
  3. isBoolean char(1) DEFAULT 1 NOT NULL CHECK ( isBoolean (1,0) )
  4. );
  5.  
  6. comment ON COLUMN booleans.isBoolean IS 'boolean: 1 for True, 0 for False';
  7.  
  8. CREATE bitmap INDEX isBoolean_index_name ON booleans(isBoolean);
  9.  
  10.  
  11.  
  12. ALTER TABLE
  13. table_name
  14. ADD
  15. (
  16. is_boolean integer DEFAULT 1 NOT NULL CHECK ( is_boolean IN (1,0) )
  17. );
  18.  
  19. comment ON COLUMN table_name.is_boolean IS 'boolean: 1 for True, 0 for False';
  20.  
  21. CREATE bitmap INDEX is_boolean_index_name ON table_name(is_boolean); -- Faster sorting and searching for low-cardinality data
  22.  
  23.  
  24. SET serveroutput ON
  25. begin
  26. dbms_output.enable;
  27. IF package.boolean_function then
  28. dbms_output.put_line('True');
  29. else
  30. dbms_output.put_line('False');
  31. end IF;
  32. end;
  33. /

Report this snippet  

You need to login to post a comment.