Different types Functions in Oracle with examples

Hi All,

In this post we will discuss about several functions used in oracle

select INSTR('MAHESH', 'H',-1,1) from dual;-- Output  :6

select RTRIM('MAHESMMM', 'M') from dual;--MAHES

select replace('mahesh', 'h','e') from dual;--maeese

select translate('Led','Le','R') from dual;--Translates letter by letter. In this example, L is replaced by R, but e is replaced but with nothing. Output : Rd

select chr(68), ASCII('D'), ASCII('MAHESH'), ASCII('M') from dual;--Output : D 68 77 77

select round(123.123456789, -2) from dual;--Output : 100

select 19.637, trunc(19.637, -1), round(19.637, -1) from dual;--Output : 19.637 10 20

select floor(19.999) from dual;--Output : 19

select mod(100, 32) from dual;--Output : 4

select power(-5,3) from dual;--Output : -125

select sqrt(49) from dual;--Output : 7

select abs(-200) from dual;--Output : 200

select sign(-1) from dual;--Output : 0 -1

select SYSDATE, NEXT_DAY(SYSDATE, 'MONDAY'), LAST_DAY(SYSDATE) from dual;--Output : 28-FEB-14 03-MAR-14 28-FEB-14

select ROUND(SYSDATE, 'DAY'), TRUNC(SYSDATE, 'DAY'), 
       ROUND(SYSDATE, 'MONTH'), TRUNC(SYSDATE, 'MONTH'), 
       ROUND(SYSDATE, 'YEAR'), TRUNC(SYSDATE, 'YEAR') 
FROM DUAL;--Output : 02-MAR-14 23-FEB-14 01-MAR-14 01-FEB-14 01-JAN-14 01-JAN-14

-- TO_CHAR Function

select 1234, TO_CHAR(1234, '9999D99') from dual;--1234 1234.00  Convert into a character of a decimal format

select TO_CHAR(5634, '9.9EEEE') from dual;--  5.6E+03  Convert a number into a character of scientific notation

select TO_CHAR(1234567, '99G999G999') from dual;--  1,234,567  Grouping of numbers

select TO_CHAR(1234, 'L9999'), TO_CHAR(123, '9999L') from dual;--  $1234  123$  Setting locale to a number

select TO_CHAR(-1234, '9999MI'), TO_CHAR(1234, '9999MI') from dual;--1234- 1234  Trailing Minus if then umber is negative number

select TO_CHAR(-1234, 'L99G999D99MI') from DUAL;-- $1,234.00-  Trailing Minus with format if then umber is negative number

select TO_CHAR(-1234, 'L99G999D99PR') from DUAL;--  <$1,234.00> Negative Number Indicator

select 12, TO_CHAR(12, 'RN'), TO_CHAR(12, 'rn') from dual; -- 12 XII xii Returns Roman number RN for capital Roman and rn for small Roman Number

select 1000, TO_CHAR(1000, 'S9999'), TO_CHAR(-1000, 'S99999'), TO_CHAR(-1000, '99999S') from dual;-- Used for Signs to be placed ar front or rear

select 1000, TO_CHAR(1000.112, 'XXXX') from dual;-- 1000, 3E8 Converts number to a hexadecimal value. Can't accept -ve numbers. If no is not an integer, converts to a closest integer

select TO_CHAR(1234, '99,999.99') from dual;-- 1,234.00 Converts to a comma specified value

select TO_CHAR(1234, 'L99,999.99') from dual;-- $1,234.00 Converts to a locale specified decimal with comma separated values

select TO_CHAR(13453.23, '$99999.99') from dual;-- $13453.23 returns value with a leading dollar sign

select TO_CHAR(12345, '0999990') from dual;-- 012345 Zero indicator, Returns leading or trailing zeros

select 10000, TO_CHAR(1000, '0999999'), TO_CHAR(1000, '09999990') from dual;--10000 0001000 00001000

select 1000, 600, TO_CHAR(1000-600, '99999'), TO_CHAR(600-1000, '99999') from dual;-- Digit Place marker, provides space when positive and minus when negative

select 1000, TO_CHAR(1000, 'C9999999.99') from dual; -- 1000 USD1000.00 Specifies ISO Currency Symbol at the specified location

-- Date Format Functions:

select sysdate, TO_CHAR(SYSDATE, 'AD'), TO_CHAR(SYSDATE, 'B.C.') from dual; -- Indicates if specified date is AD or BC

select TO_CHAR(sysdate, 'A.M.') from dual;-- Indicates if it AM or PM based on the current date and time

select TO_CHAR(sysdate, 'CC-AD') from dual;-- Indicates which century it is

select TO_CHAR(sysdate, 'D') from dual;-- Indicates which number of day of a week it is

select TO_CHAR(SYSDATE, 'DAY') from dual;-- Indicates the spelled out day of a week in words

select TO_CHAR(SYSDATE, 'DD') from dual;-- Indicates which month day number of a particular month

select TO_CHAR(SYSDATE, 'DDD') from dual;-- Indicates which year day number of the particular year. This takes into consideration the leap year days too

select TO_CHAR(SYSDATE, 'DY') from dual;-- Indicates the abbreviated week day of a week

select TO_CHAR(SYSDATE, 'D-DY-DAY') from dual;--5-THU-THURSDAY 

select TO_CHAR(SYSDATE, 'IW') from dual;-- ISO Standard Week of the year indicator

select TO_CHAR(SYSDATE, 'IYYY'), TO_CHAR(SYSDATE, 'IYY'),TO_CHAR(SYSDATE, 'IY'),TO_CHAR(SYSDATE, 'I') from dual;-- ISO Standard Year format

select TO_CHAR(SYSDATE, 'YYYY'),TO_CHAR(SYSDATE, 'YYY'),TO_CHAR(SYSDATE, 'YY'),TO_CHAR(SYSDATE, 'Y') from dual;-- Similar to ISO Standard Year format

select TO_CHAR(SYSDATE, 'YEAR') from dual;-- Year spelled out

select TO_CHAR(SYSDATE, 'W') from dual;-- Returns week of the month

select TO_CHAR(SYSDATE, 'WW') from dual;-- Week of the year indicator

select TO_CHAR(SYSDATE, 'Q') from dual; -- Quarter of the year indicator

select TO_CHAR(SYSDATE, 'J') from dual;-- Julian Day of a given date

select TO_CHAR(SYSDATE, 'MM') from dual;-- Returns Numeric month of an year

select TO_CHAR(SYSDATE, 'MON') from dual;-- Returns Abbreviated month of an year

select TO_CHAR(SYSDATE, 'MONTH') from dual;-- Returns spelled out month of an year

select TO_CHAR(SYSDATE, 'HH') from dual;-- Returns Hours of the time from date time

select TO_CHAR(SYSDATE, 'MI') from dual;-- Returns minutes of the time from date time

select TO_CHAR(SYSDATE, 'HHAM'), TO_CHAR(SYSDATE, 'HH-AM') from dual;-- Returns Hours of the time from date time with AM or PM indicator

select TO_CHAR(SYSDATE, 'HH12'), TO_CHAR(SYSDATE, 'HH24') from dual;-- Returns Hour from time in 12 and 24 hour format

select TO_CHAR(SYSDATE, 'RM'), TO_CHAR(SYSDATE, 'DD-RM-YYYY') from dual; -- Returns Roman numeral month

select TO_CHAR(SYSDATE, 'SS') from dual;-- Returns Seconds from current date time

--These are the punctuation marks that can be used in date formats

select TO_CHAR(SYSDATE, 'DDth MON YYYY') from dual; -- th is used to suffix the number with st/nd/rd/th characters

select TO_CHAR(SYSDATE, 'DDsp MON YYYY') from dual;-- sp is used to spell out the number.

select TO_CHAR(SYSDATE, 'DD MON YYYYSPTH') from dual;-- We can use SP and TH in conjunction to a number to spell out the first and add a suffix from the listed keys

select TO_CHAR(SYSDATE, 'DDspth MON YYYYSP') from dual;-- Similar example as above  ## Output : TWENTY-SEVENTH FEB TWO THOUSAND FOURTEEN

select TO_CHAR(SYSDATE, 'FMDDspth MON YYYYSP') from dual;-- Fill Mode(FM) is used to suppress the blank padding in the return value of the TO_CHAR

--TO_NUMBER Function : Converts a character value to a number

select '$10,000.00', TO_NUMBER('$10,000.00', 'L99,999.99')+500 from dual;-- Converts a character number to a number format and added 500 to it

-- TO_DATE Function

select TO_CHAR(TO_DATE('12-FEB-2014'), 'DDSPTH') from dual;-- Converts a character dated value to a date and then spelled out the date

select first_name||' '||last_name, ADD_MONTHS(TO_DATE('1980-DECEMBER-17','YYYY-MONTH-DD'), 3) from employees;-- Adding 3 months to a date after converting from a character to a date

create table temp(a number, doj date);

insert into temp values(TO_NUMBER('1,23,456','9G99G999'), sysdate);

insert into temp values(TO_NUMBER('1,23,456-','9G99G999MI'), sysdate);

select * from temp;

insert into temp values(23412, TO_DATE('12-JAN-09 11:30:15 AM','DD-MON-YYYY HH:MI:SS AM'));

select TO_TIMESTAMP(doj) from temp;

alter table temp modify doj timestamp;

select TRUNC(TO_DATE('27-OCT-92', 'DD-MON-YYYY'), 'YEAR') from dual;--Truncates the date to the nearest year. Other parameters to pass are day and month

select ROUND(TO_DATE('27-OCT-92', 'DD-MON-YYYY'), 'YEAR') from dual;--01-JAN-93 Rounds the date to the nearest year. Other parameters to pass are day and month

select NEXT_DAY('27-FEB-14', 'WED') from dual;-- Gives the next wednesday. We can also pass the weekday spelled out like wednesday

select TO_CHAR(SYSDATE, 'FMMONTH, DD YYYY') from dual;

select TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR'), 'YYYY') from dual;--This converts the year and based on the parameter specified like RR or RRRR, returns the year from current year or previous year

select user, uid, userenv('SESSIONID'), userenv('LANGUAGE'), userenv('LANG'), userenv('INSTANCE') from dual;

select first_name, VSIZE(first_name), department_id, vsize(department_id) from employees where department_id = 100;

create table customers(ID NUMBER, NAME VARCHAR2(15), AGE NUMBER, ADDRESS VARCHAR2(20), Salary Number(10, 2));

create table ORDERS (OID NUMBER, ORDER_DATE DATE, CUSTOMER_ID NUMBER, AMOUNT Number(10, 2));

insert into CUSTOMERS VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00);
insert into CUSTOMERS VALUES(2, 'Khilan', 25, 'Delhi     ', 1500.00);
insert into CUSTOMERS VALUES(3, 'kaushik  ', 23, 'Kota', 2000.00);
insert into CUSTOMERS VALUES(4, 'Chaitali ', 25, 'Mumbai', 6500.00);
insert into CUSTOMERS VALUES(5, 'Hardik   ', 27, 'Bhopal', 8500.00);
insert into CUSTOMERS VALUES(6, 'Komal    ', 22, 'MP', 4500.00);
insert into CUSTOMERS VALUES(7, 'Muffy    ', 24, 'Indore', 10000.00);

insert into ORDERS VALUES(102, TO_DATE('2009-10-08', 'YYYY-MM-DD'), 3, 3000);
insert into ORDERS VALUES(100, TO_DATE('2009-10-08', 'YYYY-MM-DD'), 3, 1500);
insert into ORDERS VALUES(101, TO_DATE('2009-11-20', 'YYYY-MM-DD'), 2, 1560);
insert into ORDERS VALUES(103, TO_DATE('2008-5-20', 'YYYY-MM-DD'), 4, 2060);
insert into ORDERS VALUES(103, TO_DATE('2008-9-14', 'YYYY-MM-DD'), 8, 1230);

select * from ORDERS;

SELECT ID, NAME, AGE, AMOUNT
        FROM CUSTOMERS, ORDERS
        WHERE  CUSTOMERS.ID = ORDERS.CUSTOMER_ID(+);
        /*Output: 
        3 kaushik   23 3000
        3 kaushik   23 1500
        2 Khilan  25 1560
        4 Chaitali 25 2060
        5 Hardik   27 null
        1 Ramesh  32 null
        6 Komal     22 null
        7 Muffy     24 null
        */
        SELECT ID, NAME, AGE, AMOUNT
        FROM CUSTOMERS, ORDERS
        WHERE  CUSTOMERS.ID(+) = ORDERS.CUSTOMER_ID;
        /*Output : 
        2 Khilan 25 1560
        3 kaushik   23 1500
        3 kaushik   23 3000
        4 Chaitali 25 2060
        null      null       null    1230
        */

Use of DROP, TRUNCATE, DELETE, FLASHBACK, PURGE

Hi,

In this post, we will discuss about how the update / delete on table works with data. Then we will focus on Drop, Truncate, Purge and Flashback commands. 

When you update or delete the data from a table, the database records the before images of the changes (ie., the image of the data as is) to the rollback segments and all the after image changes (ie., the image of the data after the changes to the data is done) will be recorded to the redo log buffer. 

So, when the commit happens, no changes would be done to the rollback segment, but the redo log buffer is cleared and the actual changed data would be written to the disk. 

Now lets discuss about the other mentioned commands - Drop, Truncate, Purge and Flashback. 

Let's first create a sample table with one column and populate sample data as displayed below. 

select * from b;

When we use TRUNCATE command on table 'b', all the data in table 'b' would be deleted and an internal commit operation is performed as TRUNCATE is a DDL command. But when we use DELETE command on a table, since data is written to the rollback and redo log segments, we would still be able to recover the data deleted using ROLLBACK command but only if you have not used COMMIT command after deleting the data. 

Let's again populate the data as shown in the figure above. 

Now, if we use DROP command on table 'b', the table would be dropped. What actually happens is that this table is not completely dropped. When you query Recyclebin, you would still see the trace of this table. 

You can use either of these statements : 

select * from recyclebin;
or 
select * from user_recyclebin;

So, if an user accidentally dropped a table, they can still recover it. We use FLASHBACK command to achieve this. 

FLASHBACK TABLE b TO BEFORE DROP;

When you do this, the table would be restored to the same state as it was earlier including the data. 

But, if you want to delete the data even from recyclebin once you drop the table, you can use this command:

PURGE recyclebin;

If you want to drop the table explicitly in a way you won't be able to recover, you need to use PURGE command while dropping the table. 

DROP TABLE B PURGE;

Hope you understood the concepts explained in this post. 

Primary Difference between Delete and Truncate : 

Delete is a DML command. Truncate is a DDL command.
In Delete statement we can use where clause But we can’t use where clause in truncate statement.
Delete activates trigger. Truncate does not activate trigger.
We can rollback delete command. We can not rollback truncate command. Delete does not reset identity of table. Truncate resets identity of table.

Components of SQL (Structured Query Language)

In this post, we will discuss about the components of SQL (Structured Query Language)

There are five components in SQL. 

1. Data Retrieval / Query Language

Eg. Select - this command is used to write a statement to retrieve the information from database objects like tables, views etc for read only purpose. 

2. Data Manipulation Language

This is used to insert / modify / delete the data from the database. 

Eg. Insert - This command is used to write a statement to insert new data into database object(s)

     Update - This command is used to write a statement to update any existing data in the database object(s)

     Delete - This command is used to write a statement to delete data from the database object(s)

3. Data Definition Language

This is used to define database objects.

Eg. We can create / modify / delete the definition of a database object(s)

Commands used are : Create, Alter, Drop, Truncate and Rename

4. Data Control Language

This is used to share the information or privileges between users.

Eg., Grant and Revoke

5. Transaction Control Language

This is used to save or cancel the DML operations / transactions. 

Eg., Commit, Rollback and Savepoint

Hope you got the overview of different components of SQL. 

Relations among data

In this post we will see the relationship held between data

1. A relationship can be defined as an association among entities. 
2. Several relationships may exist between the same entity. 

There are three types of relationships among data.

• One to One
• One to Many / Many to One
• Many to Many

Lets go through one by one. 

One to One :

Consider an example of a class of students. 

Each student can only be assigned one student ID. In the same way, each student ID can only be associated to one student. So, from both ways, only one relation can be built. Same example is depicted in the picture. 

One to Many / Many to One:

Consider an example of students enrolling to a course.

One student can enroll for only one particular course at a time, and more than one students can enroll for a course. Same example is depicted in the picture. 

Many to Many:

Consider an example of a seller selling number of items.

One seller can sell a number of items and many sellers can sell the same particular item. So, many sellers can sell many items. Same example is depicted in the picture. 

Now, I hope you understood the relations among data.