Search Box

Custom Search

Friday, February 21, 2014

Table pada Oracle Database

Posted by chacky glory  
2:12 PM

Dictionary Table

SQL>DESC dictionary

Mencari Infromasi Table di user shcema

SQL>SELECT table_name FROM user_tables; 

•Informasi Objek

SQL>COLUMN object_name FORMAT A15

SQL>COLUMN object_type FORMAT A15

SQL>SELECT obeject_name, object_type FROM user_objects;



Membuat Table

SQL> CREATE TABLE product

2 (kode_product VARCHAR2(5),

3 nama_product VARCHAR2(20),

4 harga_jual NUMBER(10),

5 satuan VARCHAR2(10),

6 stock NUMBER(4)

7  ;
 
Untuk mengisi datanya gunakan perintah INSERT

SQL>INSERT INTO product VALUES('AT001','BUKU',1500,'pcs',100);

SQL>INSERT INTO product VALUES('AT002','PENSIL',1000,'pcs',50);

SQL>INSERT INTO product VALUES('AT003','PENGARIS',2000,'pcs',20); 

Default Option

SQL> CREATE TABLE penjualan (no_order Number(5),

2 tgl_transaksi Date Default sysdate,

3 nama_pembeli Varchar2(15) Default 'Noname',

4 rp_transaksi Number(10) Default 0); 

Menampilkan informasi table lebih detail dari dictionary USER_TAB_COLUMNS

SQL> BREAK ON TABLE_NAME

SQL> COL TABLE_NAME FORMAT A10

SQL> COL COLUMN_NAME FORMAT A15

SQL> COL DATA TYPE FORMAT A15

SQL> COL DATA_TYPE FORMAT A15

SQL> COL DATA_DEFAULT FORMAT A15

SQL> SELECT table_name, column_name, data_type,

2 data_default from user_tab_columns

3 WHERE TABLE_NAME = 'PENJUALAN'; 

Cara memberi nilai default ada dua cara yaitu :
1.Mengabaikan kolom yang didefinisikan dengan nilai default

SQL> INSERT INTO penjualan (no_order) Values(1);

2. Menyebutkan secara eksplisit dengan DEFAULT

SQL> INSERT INTO penjualan VALUES (2,DEFAULT,'Toni',20000);

Membuat Table dengan Subquery

SQL> CREATE TABLE copy_pegawai

2 AS

3 SELECT * FROM pegawai;



SQL> CREATE TABLE copy_peg41

2 AS

3 SELECT * FROM pegawai

4 WHERE kode_bag=41;

Modifikasi Tabel

Menambah kolom

SQL> ALTER TABLE product

2 ADD (pct_diskon NUMBER(5,2) DEFAULT 0,

3 keterangan VARCHAR2(30));

*Cek Struktur Table SQL>DESC product
Modifikasi Kolom
Tambah jumlah karakter pada Kolom

SQL> ALTER TABLE product MODIFY (nama_product varchar2(25));

Ubah nilai default pada kolom

SQL> ALTER TABLE product MODIFY (pct_diskon DEFAULT 10); 

Mengahapus kolom

SQL> ALTER TABLE product DROP COLUMN keterangan; 

Mengubah nama Column

SQL> ALTER TABLE product RENAME COLUMN harga_jual TO harga_jual_product; 

Set Kolom menjadi UNUSED (kolom yang tidak digunakan)

SQL> ALTER TABLE product SET UNUSED COLUMN satuan; 

Informasi jumlah kolom yang sudah di UNUSED

SQL>SELECT * from user_unused_col_tabs; 

Menghapus kolom yang di set UNUSED

SQL> ALTER TABLE product DROP UNUSED COLUMNS;

Membuat Constraint

Primary Key

SQL> ALTER TABLE PRODUCT

2 ADD CONSTRAINT pk_product PRIMARY KEY (kode_product);

Unique

SQL> ALTER TABLE PRODUCT

2 ADD CONSTRAINT uq_product UNIQUE (nama_product); 
 
Check

SQL> ALTER TABLE pegawai

2 ADD CONSTRAINT ck_gaji CHECK (gaji>=1000000);
Not Null

SQL> ALTER TABLE pegawai Modify userid constraint userid_nn NOT NULL; 

Foreign Key

SQL> ALTER TABLE pegawai

2 ADD CONSTRAINT fk_kode_bag FOREIGN KEY (kode_bag)

3 REFERENCES bagian(kode_bag); 

Mengetahui Constraint pada table

SQL> SELECT constraint_name, table_name, constraint_type

2 from user_constraints where table_name=’PRODUCT’;

Disable dan Enable Constraint

•Langkan pertama adalah mengecek dahulu status constraintnya dengan perintah

SQL> SELECT constraint_name, table_name, status

2 FROM user_constraints WHERE

3 table_name IN('EMPLOYEE','DEPARTEMENT');
 
Disable

SQL> ALTER TABLE EMPLOYEE

2 DISABLE CONSTRAINT ck_gaji_emp; 
 
•Enable

SQL> ALTER TABLE EMPLOYEE

2 ENABLE CONSTRAINT ck_gaji_emp; 
 
Menghapus Constraint

SQL> ALTER TABLE EMPLOYEE

2 DROP CONSTRAINT ck_gaji_emp;

Penerapan Constraint Relasi

Pada table dengan relasi, statement delete terkadang gagal

SQL> DELETE departement

2 where kode=10;

DELETE departement

*

ERROR at line 1:

ORA-02292: integrity constraint (SALES.FK_KODE_DEPT) violated - child record found

Hal ini terjadi karena pada saat pendefinisian constraint fk_kode_dept tidak diikuti option ON DELETE CASCADE atau ON DELETE SET NULL.

SQL> Alter table employee

2 ADD CONSTRAINT fk_kode_dept FOREIGN KEY(kode)

3 REFERENCES departement(kode) ON DELETE SET NULL; 
 
Coba Delete kembali

SQL> DELETE departement where kode=10; 
 
ON DELETE CASCADE

SQL> Alter table employee

2 ADD CONSTRAINT fk_kode_dept FOREIGN KEY(kode)

3 REFERENCES departement(kode) ON DELETE CASCADE;

Truncate Tabel

Buatlah Tabel dengan data dummy

SQL> CREATE TABLE COPY_PEG

2 as

3 SELECT * FROM pegawai;

•Table created.

SQL> BEGIN

2 FOR X IN 1..8 LOOP

3 INSERT INTO COPY_PEG

4 SELECT * FROM COPY_PEG;

5 END LOOP;

6 COMMIT;

7 END;

8 /

thumbnail Judul : Table pada Oracle Database

URL : http://filedoom.blogspot.com/2014/02/table-pada-oracle-database.html

Rating : 10 out of 10 based on 696969 ratings. 696969reviews.
Ditulis Oleh chacky glory
Free Download 2:12 PM

0 komentar:

Labels

Tips Dan Trik (38) IT (23) pelajaran tentang IT (23) stmik (22) OS (21) Berita (20) HTML5 (19) materi (18) tutorial (15) Sismul (13) Tizen (12) UI (11) web (11) Download (10) Download lagu (10) Android (9) Fitur (9) Meningkatkan Traffic (9) Samsung (9) TouchWiz (9) streaming (9) Aplikasi (8) Intel (8) Blackberry (7) Fakta (7) database (7) Handphone (6) oracle (6) Fungsi (5) Game (5) Lucu (5) SEO (5) optimal (5) sql (5) widget (5) PHP (4) Programing Languages (4) Smartfren (4) Spesifikasi (4) bbm (4) Andromax (3) Dollar (3) Gadget (3) Kata (3) Obsidian (3) Ads (2) CPM (2) Foto (2) Kata Bijak (2) adsense (2) celebrity (2) sosial (2) Crack (1) Error (1) Gaya Hidup (1) Gokil (1) Harga (1) Harlem Shake (1) PPC (1) Penyakit (1) Video (1) bola (1) cheat (1) facebook (1) reggae (1)

Daftar BackLink

AutoSurf Traffic Exchange: Powerful Results with SEO tips | 10KHits - 10KHits provides quality traffic hits to your personal or business websites. Auto Backlink Gratis : Filedoom Auto Backlink Gratis Indonesia : Top Link Indo
W3 Directory - the World Wide Web Directory

Site Info

SEO Reports for filedoom.blogspot.com Antispam
filedoom.blogspot.com Webutation
LinkXchanger.com
Latest Downloads

Rating for filedoblogspotom..com DMCA.com

My Facebook

Google+ Badge

Google+ Followers

Proudly Powered by Blogger.
back to top