Implementation Of DML Basic Function (Built In Function) On Oracle Database

Function is a special syntax in DML (Data Manipulation Language) queries to perform data processing in accordance with user needs. There are some ready-made function in Oracle Database. Here are some purposes of function in SQL query:

• data manipulation
• menerima argumen dan mengembalikan satu nilai
• accept arguments and return a value
• able to modified column data type
• can perform nested functions

There are three categories of basic functions, which are character function, numeric functions and date function.

For example, there is a table named PRODUK as follows:

PRODID   PRODNAME           UNIT PRICE QTY
---------------------------------------------------------------------------------
P-001 Sabun mandi batang buah 7500 50
P-002 Sabun mandi cair botol 100ml 15000 21
P-003 Sabun cuci sachet 5 sachet 7500 70
P-004 Sabun cuci cair botol 500ml 12000 11
P-005 Sabun mandi kotak kotak 22000 4
P-006 Pelembut pakaian botol 1000ml 15000 7
P-007 Pewangi pakaian botol 1000ml 12000 5
P-008 Minyak goreng botol 1000ml 25000 26

Here are examples of character function implementation:
1. LOWER
Show all product names with a lowercase letter!

• SELECT LOWER(nmproduk) FROM produk;

2. UPPER
Show all product names and units with capital letters!

• SELECT UPPER(nmproduk) FROM produk;

3. INITCAP
Show all product names with a capital letter on the first word!

• SELECT INITCAP(nmproduk) as "Awal" FROM produk;

4. CONCAT
Show product information with codes and product names are combined, for example:
P-001 : Sabun mandi batang

• SELECT CONCAT(CONCAT(kdproduk,' : '),nmproduk) produk FROM produk;
• SELECT kdproduk||' : '||nmproduk produk FROM produk;

5. SUBSTR
Show productid and first five words of the name of the product!

• SELECT kdproduk, SUBSTR(nmproduk,1,5) prod FROM produk;

6. LENGTH
Show the product name and the number of words of each product name!

• SELECT nmproduk, LENGTH(nmproduk) jml FROM produk;

7. INSTR
Show the character position number of the letter c found on each product name!

• SELECT nmproduk, INSTR(nmproduk,'c') FROM produk;

8. LPAD
Show the product name and price of the products, set the total character is 7, for example: xxx7500!

• SELECT nmproduk, LPAD(harga,7,'x') FROM produk;

9. RPAD
Show the product name and price of the products, set the total character is 7, for example: 7500xxx!

• SELECT nmproduk, RPAD(harga,7,'x') FROM produk;

10. REPLACE
Show the productid and product name, replace character c with character g!

• SELECT kdproduk, REPLACE(nmproduk,'c','g') FROM produk;