Saturday, 20 January 2018  
 
Dolvin Blog
Dolvin Blog » Our DB
Oracle Troublesome
by. Dolvin ≈ November 9, 2010
Oracle is cool.. and have a bigger size than SQL I think.. *correct me if I'm wrong*

but some feature is makes it complicated I think..
like LIMIT..
in SQL we can use limit to do pagination
but in Oracle we have to count it first and use rownum for know the data position.

Rownum Example :
in this example I show 5 data per page.. and this is to show page 2

SELECT *
  FROM (SELECT ROWNUM AS rn, x.*
          FROM (SELECT   client_id, client_name
                    FROM tbl_client
                ORDER BY client_id) x
         WHERE ROWNUM <= 10)
 WHERE rn BETWEEN ((2 - 1) * 5) + 1 AND (2) * 5


The others is autonumber..
in SQL we can use auto increment for it
but in Oracle.. we have to update from application or use trigger to count it

Example of create auto number in Oracle:

   1. CREATE TABLE tbl (
   2. tbl_id INTEGER PRIMARY KEY,
   3. );
   4.
   5. CREATE SEQUENCE eq_seq;
   6. CREATE TRIGGER eq_seq_trigger
   7. BEFORE INSERT ON tbl FOR EACH ROW
   8.
   9. BEGIN
  10. IF (:new.tbl_id IS NULL) THEN
  11. SELECT eq_seq.nextval INTO :new.tbl_id
  12. FROM DUAL;
  13. END IF;
  14. END;
  15. /


so irritating..
humm.. why I miss this word? hihihih...
 
<< back to Dolvin's Blog




:: January '18 ::
 
 
S
M
T
W
T
F
S
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
   

:: My Blog List ::
 
   
   

:: Blog Category ::
 
 
» Books
» Christian Soul
» Fashion
» Film
» Foods & Drinks
» Games
» General Info
» Health
» Jokes
» Love
» Music
» My Life
» Programming
» Technology
 
   

:: Site Links ::
 
 
» Angel Sulistyo
» Anton Ongsono
» Darwin
» Fellys
» Inge
» Kado Imut
» Koleksi Unik
» Lie Fung
» Lintaka
» Lobak
» Rosrita
 
   
 
:: Dolvin Website | Dolvin Blog | Our DB ::
Copyright © Lavinia - 2006 - 2018