Oracle Object

Mata Kuliah : Aplikasi Basis Data

Dosen            : M. Dedi Suryadi

What is a Database? 

       The definition of a database is a structured collection of records or data that is stored in a computer system. In order for a database to be truly functional, it must not only store large amounts of records well, but be accessed easily.

What is a Table? 

       A single store of related information. A table consists of records, and each record is made up of a number of fields.

What is Primary Key?

       A field that uniquely identifies a record in a table

What is SQL?
SQL (Structured Query Language) is the most common standardized language used to access databases. SQL is a nonprocedural language. Oracle produces a procedural version of SQL which it calls PL/SQL. SQL is often pronounced "sequel"

 

  User/Schema

Setiap orang yang akan mengakses ke suatu database Oracle harus memiliki database user account atau biasa dikenal dengan user name.  Schema pada dasarnya akan terbentuk secara otomatis pada saat  sebuah user dibuat. Dalam Oracle, account yang dapat membuat user adalah SYS dan SYSTEM

Create User

CREATE USER username

IDENTIFIED BY password

Modify User

ALTER USER username
IDENTIFIED BY newpassword

Drop User

drop user username cascade;

Exercise:

Create user ilham identified by ilham;

Grant connect, resource to ilham;

 

ORACLE Built-in Data Types (Mostly Use)

•  VARCHAR2(size[BYTE | CHAR])

Variable-length character string having maximum length size bytes or characters. Maximum size is            4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify size for           VARCHAR2.

BYTE indicates that the column will have byte length semantics; CHAR indicates that the column will          have character semantics.

 

• NUMBER(p,s) 
  
  
  Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.  

•  DATE

         Valid date range from January 1, 4712 BC to December 31, 9999 AD 

 

• ROWID

        Base 64 string representing the unique address of a row in its table. This data type is 

        primarily for values returned by the ROWID pseudocolumn.

 

• CHAR(size [BYTE | CHAR])

         Fixed-length character data of length size bytes. Maximum size is 2000 bytes or 

       characters. Default and minimum size is 1 byte.

       

       BYTE and CHAR have the same semantics as for VARCHAR2.

 

•  CLOB

         A character large object containing single-byte or multibyte characters. Both fixed-

         width and variable-width character sets are supported, both using the database 

         character set. Maximum size is (4 gigabytes - 1) * (database block size).

•  BLOB

         A binary large object. Maximum size is (4 gigabytes - 1) * (database block size).  

Common tips: How to make good tables:

1.Provide column for store information about when and who the data was created and or  updated, make both not null. Such as: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY

2.Provide some column for unpredicted future changes, such as: ATTRIBUTE1 up to 5

3.Use valid from and valid until date for flagging the status of record

4.Do not forget to give comment for every column

5.Ensure the data type was fixed to the certain column properly

6.Give the column name for primary key and foreign key with the same name

7.Same prefix for the tables in one schema

 

Create Table

  

create table table_name(

AC_ID_PK number primary key,
AC_STATUS number not null,
AC_COUNTRY_ID number default 1,
AC_CREATED date default sysdate,
AC_ACCOUNT varchar2(50)
……

……. ) ;

Note:

-Primary Key: Indicates the primary key of the table

-Not null: Indicates that the column not allowed null data

-Default: Indicates the default value of the column, when insert record and this column set to null, then automatically  will be stored the default value

 
Alter Table

 

The ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table.

Renaming a table

The basic syntax for renaming a table is:

ALTER TABLE table_name
 RENAME TO new_table_name;

Adding column(s) to a table
For example:

ALTER TABLE supplier ADD (supplier_name varchar2(50), city varchar2(45) );

Modifying column(s) in a table
To modify a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 MODIFY column_name column_type;

For example:

ALTER TABLE supplier
 MODIFY supplier_name   varchar2(100)     not null

 

Rename Column(s) in a table To rename a column in an existing table, the ALTER TABLE syntax is : ALTER TABLE TABLE_NAME     RENAME COLUMN column_name TO new_column_name; For example : ALTER TABLE supplier  RENAME COLUMN supplier_name TO nama_suplier; Drop column(s) in a table To drop a column in an existing table, the ALTER TABLE syntax is: ALTER TABLE table_name  DROP COLUMN column_name; For example: ALTER TABLE supplier  DROP COLUMN supplier_name;

USING ALTER TABLE STATEMENT ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n); Alter Table – Foreign Key CREATE TABLE table_name ( column1 datatype null/not null, column2 datatype null/not null, ... CONSTRAINT fk_column FOREIGN KEY (column1, column2, ... column_n) REFERENCES parent_table (column1, column2, ... column_n) ); Alter Table – Add Foreign Key  ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column1, column2, ... column_n) REFERENCES parent_table (column1, column2, ... column_n); Oracle Object - Sequence   Sequence (Auto number) In Oracle, you can create an auto number field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key. CREATE SEQUENCE supplier_seq     START WITH 1     INCREMENT BY 1; Now that you've created a sequence object to simulate an auto number field, we'll cover how to retrieve a value from this sequence object. To retrieve the next value in the sequence order, you need to use nextval. For example: supplier_seq.nextval This would retrieve the next value from supplier_seq. The nextval statement needs to be used in an SQL statement. For example: INSERT INTO suppliers (supplier_id, supplier_name) VALUES (supplier_seq.nextval, 'Kraft Foods'); Oracle Object - Index Index An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes. Create an Index The syntax for creating a index is: CREATE [UNIQUE] INDEX index_name   ON table_name (column1, column2, . column_n); UNIQUE indicates that the combination of values in the indexed columns must be unique. COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a "plan of execution" when SQL statements are executed. For example: CREATE INDEX supplier_idx    ON supplier (supplier_name); In this example, we've created an index on the supplier table called supplier_idx. It consists of only one field - the supplier_name field. We could also create an index with more than one field as in the example below: CREATE INDEX supplier_idx    ON supplier (supplier_name, city); Rename an Index The syntax for renaming an index is: ALTER INDEX index_name   RENAME TO new_index_name; For example: ALTER INDEX supplier_idx   RENAME TO supplier_index_name; In this example, we're renaming the index called supplier_idx to supplier_index_name. Drop an Index The syntax for dropping an index is: DROP INDEX index_name; For example: DROP INDEX supplier_idx; In this example, we're dropping an index called supplier_idx.