Reverse function in Oracle

The following shows how to use the reverse function to reverse a string or number in Oracle.
SQL> select reverse('string') from dual;
REVERS
------
gnirts

However you may not have the function installed and can use the following procedure:
SQL>
SQL> create or replace procedure reverse(x in varchar2) as
2 a char(1);
3 i number;
4 begin
5 for i in 1..length(x) loop
6 select substr(x,length(x)-i+1,1) into c from dual;
7 dbms_output.put(a);
8 end loop;
9 dbms_output.put_line(' ');
10 end;
11 /


Procedure created.

SQL> set serverout on
SQL> exec reverse('Oracle')
elcarO

SQL> exec reverse('string')
gnirts
PL/SQL procedure successfully completed.

Oracle SQL Usage: PARTITION BY

PARTITION BY: divide the groups into subgroups

This example will demonstrate grouping all sales by month, per product.

SQL> -- create your table
SQL> CREATE TABLE all_sales (
2 year INTEGER,
3 month INTEGER,
4 prd_type_id INTEGER,
5 emp_id INTEGER ,
6 amount NUMBER(8, 2)
7 );

Table created.

SQL> -- ad data into your table
SQL>
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,1 ,1 ,21 ,16034.84);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,2 ,1 ,21 ,15644.65);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,3 ,2 ,21 ,20167.83);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,4 ,2 ,21 ,25056.45);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,5 ,2 ,21 ,NULL);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,6 ,1 ,21 ,15564.66);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,7 ,1 ,21 ,15644.65);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,8 ,1 ,21 ,16434.82);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,9 ,1 ,21 ,19654.57);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,10 ,1 ,21 ,21764.19);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,11 ,1 ,21 ,13026.73);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,12 ,2 ,21 ,10034.64);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,22 ,16634.84);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,21 ,26034.84);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,2 ,1 ,21 ,12644.65);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,3 ,1 ,21 ,NULL);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,4 ,1 ,21 ,25026.45);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,5 ,1 ,21 ,17212.66);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,6 ,1 ,21 ,15564.26);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,7 ,2 ,21 ,62654.82);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,8 ,2 ,21 ,26434.82);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,9 ,2 ,21 ,15644.65);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,10 ,2 ,21 ,21264.19);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,11 ,1 ,21 ,13026.73);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,12 ,1 ,21 ,10032.64);

1 row created.



SQL>
SQL> -- retrieve the records, just to check the content
SQL> --of the values inserted above
SQL> select * from all_sales;

YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT
---------- ---------- ----------- ---------- ----------
2006 1 1 21 16034.84
2006 2 1 21 15644.65
2006 3 2 21 20167.83
2006 4 2 21 25056.45
2006 5 2 21
2006 6 1 21 15564.66
2006 7 1 21 15644.65
2006 8 1 21 16434.82
2006 9 1 21 19654.57
2006 10 1 21 21764.19
2006 11 1 21 13026.73
2006 12 2 21 10034.64
2005 1 2 22 16634.84
2005 1 2 21 26034.84
2005 2 1 21 12644.65
2005 3 1 21
2005 4 1 21 25026.45
2005 5 1 21 17212.66
2005 6 1 21 15564.26
2005 7 2 21 62654.82
2005 8 2 21 26434.82
2005 9 2 21 15644.65
2005 10 2 21 21264.19
2005 11 1 21 13026.73
2005 12 1 21 10032.64

25 rows selected.

SQL>
SQL> --PARTITION BY: divide the groups into subgroups.
SQL> --calculate the total sales amount per month
SQL>
SQL>
SQL> SELECT
2 prd_type_id, month, SUM(amount),
3 RANK() OVER (PARTITION BY month ORDER BY SUM(amount) DESC) AS rank
4 FROM all_sales
5 where amount IS NOT NULL
6 GROUP BY prd_type_id, month
7 ORDER BY prd_type_id, month;

PRD_TYPE_ID MONTH SUM(AMOUNT) RANK
----------- ---------- ----------- ----------
1 1 16034.84 2
1 2 28289.3 1
1 4 25026.45 2
1 5 17212.66 1
1 6 31128.92 1
1 7 15644.65 2
1 8 16434.82 2
1 9 19654.57 1
1 10 21764.19 1
1 11 26053.46 1
1 12 10032.64 2
2 1 42669.68 1
2 3 20167.83 1
2 4 25056.45 1
2 7 62654.82 1
2 8 26434.82 1
2 9 15644.65 2
2 10 21264.19 2
2 12 10034.64 1

19 rows selected.

SQL>
SQL> --drop table
SQL> drop table all_sales;

Table dropped.

SQL>
SQL>