Management of Objects in Oracle Database

The management of objects in Oracle databases is done by using the DDL (Data Definition Language) query command. The objects in Oracle database are user, table, view, package, procedure and function. SQL (Structured Query Language) is a standard DBMS (Database Management System) command in a database to interact with objects inside it. The management of the database object is performed by using one of the following SQL commands:

1. CREATE; used to create a new object.

2. ALTER; used to make changes or modifications to objects that have been created previously.

3. DROP; used to delete an existing object.

DDL on Table

The table is the object of a database consisting of rows and columns. The rows in the table are known as record / row while the columns in the table are known as field / column / attribute. Here is an example of using DDL to create a table with the name of a student that has 3 attributes (nim, name, notelp) in Oracle Database:

1. Login by using user belajar

2. Create a table with the name TMahasiswa that has 3 attributes (nim, name, notelp). The TMahasiswa table has 2 constraints ie NOT NULL and PRIMARY KEY. The NOT NULL constraint is given in the nim attribute, meaning the nim attribute must be loaded a value when the new row data is added. Constraint PRIMARY KEY is given at attribute nim, meaning attribute nim is a distinguishing attribute in table TMahasiswa where there should be no contents of the same data or repeated inputted on attribute nim in table TMahasiswa. Here is the DDL command to create a TMahasiswa table:

CREATE TABLE TMahasiswa (

nim NUMBER NOT NULL,

name of VARCHAR (64),

notelp VARCHAR (16),

CONSTRAINT pk_mhs PRIMARY KEY (nim)

);

if successful will appear Table table message.

3. Check the existence of the TMahasiswa table by typing the command

DESCRIBE TMahasiswa

if it appears table structure means TMahasiswa table has been successfully created. Describe is a command to display the structure of a table that is in the Oracle database (DESCRIBE command can be short with DESC)

4. If the contents of the NIM attribute is a combination of letters and numbers it is necessary to make changes to the NI deta type. Here's how to make changes to the NIM attribute to VARCHAR:

ALTER TABLE TMMODIFY student nim VARCHAR2 (16);

Re-show the table structure of the student:

DDL on Function

Function is an object of a database that serves to perform processing a data input to then generate a value and return it (return). A function can have one or more input parameters, but can also without having an input parameter. Many values ​​can be returned (return) by a function only one.

Here is the use of DDL to create a stored function in the Oracle database:

1. Login by using user learn (if not login)

2. Create a function with the name FHitung with 2 input parameters (p1, p2)

CREATE FUNCTION F Count (p1 IN NUMBER, p2 IN NUMBER) RETURN NUMBER

US

result NUMBER;

BEGIN

result: = p1 + p2;

RETURN results;

END F Count;

/

if successful will appear Message Function created.

3. Check the existence of a stored function by typing the following command:

SELECT object_name FROM user_objects WHERE object_type = 'FUNCTION';

Object_name with the name FHITUNG will be displayed, note the writing of capital hutuf FUNCTION on WHERE clause.

DDL on Procedure

Procedure is an object of a database that serves to perform processing a data input to then generate a value. Like function, procedure can have one or more input parameters, but can also without having input parameters. The difference between a function and a procedure is that the procedure does not return a value (return).

Here is the use of DDL to create a stored procedure in Oracle database:

1. Login by using user learn (if not login)

2. Create procedure with name of PPangkat with 1 input parameter (p1)

CREATE PROCEDURE PPangkat (p1 IN NUMBER)

US

result NUMBER: = 1;

BEGIN

result: = p1 * p1;

DBMS_OUTPUT.PUT_LINE (result);

END PPangkat;

/

if successful will appear Procedure created message.

3. Check the existence of a stored procedure by typing the following command:

SELECT object_name FROM user_objects WHERE object_type = 'PROCEDURE';

Object_name with the name of the DEVICE will be displayed, note the writing of capital hutuf PROCEDURE in the WHERE clause.

Thank You - Bobsis