Developers Arena: Oracle Functions

Showing posts with label Oracle Functions. Show all posts

Oracle PL/SQL FIRST and LAST Function

The FIRST and LAST functions can be used to return the first or last value from an ordered sequence. The FIRST and LAST functions eliminate the need for self joins or views and enable better performance.

Syntax aggregate_function KEEP (DENSE_RANK FIRST ORDER BY Order_by_clause) [OVER (query_partitioning_clause)] aggregate_function KEEP (DENSE_RANK LAST ORDER BY Order_by_clause) [OVER (query_partitioning_clause)]

Example :


SELECT student_no,

       deptno,

       total_marks,

       MIN(total_marks) KEEP (DENSE_RANK FIRST ORDER BY total_marks) OVER (PARTITION BY deptno) "Lowest_Mark",

       MAX(total_marks) KEEP (DENSE_RANK LAST ORDER BY total_marks) OVER (PARTITION BY deptno) "Highest_Mark"

FROM   students_table

ORDER BY deptno, total_marks;



STUDENT_NO   DEPTNO  TOTAL_MARKS     LOWEST_MARK    HIGHEST_MARK

---------- ----------    ----------    --------- ----------

      1001         100       1400       1350       1600

      1008         100       1350       1350       1600

      1009         100       1600       1350       1600

      1005         200        850        850       3000

      1006         200       1100        850       3500

      1002         200       2975        850       3500

      1003         200       3500        850       3500

      1004         200       3000        850       3500

      1007         200        950        850       3500

Oracle/PLSQL: To_Char Function

In Oracle/PLSQL, the to_char function converts a number or date to a string.

The syntax for the to_char function is:

to_char( value, [ format_mask ], [ nls_language ] )

value can either be a number or date that will be converted to a string.

format_mask is optional. This is the format that will be used to convert value to a string.

nls_language is optional. This is the nls language used to convert value to a string.

Applies To:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

Examples - Numbers

The following are number examples for the to_char function.

to_char(1210.73, '9999.9') would return '1210.7'
to_char(1210.73, '9,999.99') would return '1,210.73'
to_char(1210.73, '$9,999.00') would return '$1,210.73'
to_char(21, '000099') would return '000021'

Oracle CEIL or CEILING Function

In Oracle/PLSQL, the ceil function returns the smallest integer value that is greater than or equal to a number.
The CEIL and CEILING functions round the specified number up, and return the smallest number that is greater than or equal to the specified number

The specified number must be a DOUBLE PRECISION number.
If the specified number is NULL, the result of these functions is NULL.
If the specified number is equal to a mathematical integer, the result of these functions is the same as the specified number.
If the specified number is zero (0), the result of these functions is zero.
If the specified number is less than zero but greater than -1.0, then the result of these functions is zero.

Syntax:
CEIL ( number )
CEILING ( number )
number is the value used to find the smallest integer value.

Example :
SELECT CEIL(12345.67) FROM dual; Output: 1236
SELECT CEIL(-45.44) FROM dual; Output: -45

Difference between Union and Minus in Oracle

Let's consider the difference between Minus and Union using
following examples.

1.create TABLE A AND B With similar structure
2.insert records in Table A and B.
3.Keep some records identical(here 2 rows).
4.find out the difference betwn 2 looking into the output.

CREATE TABLE A(NAME VARCHAR2(30));
INSERT INTO A VALUES('A');
   INSERT INTO A VALUES('B');
   INSERT INTO A VALUES('C');
   INSERT INTO A VALUES('D');
COMMIT;

CREATE TABLE B(NAME VARCHAR2(30));
INSERT INTO b VALUES('A')
INSERT INTO b VALUES('B')
INSERT INTO b VALUES('Y')
INSERT INTO b VALUES('X')
COMMIT;

1) SELECT * FROM A
MINUS
SELECT * FROM B

NAME
------------------------------
C
D
2 rows selected

2)SELECT * FROM A
UNION
SELECT * FROM B

NAME
------------------------------
A
B
C
D
Y
x
6 rows selected

Oracle/PLSQL: To_Char Function | Oracle Functions Tutorial

Sleep function in Oralce PL / SQL

The following small function attempts to obtain a system resource, eg if an index should be created on a "busy" table. You wait a while and tried until the counter has reached the maximum.
CREATE OR REPLACE PROCEDURE Sleep_function IS
GotIt BOOLEAN := FALSE;
Count NUMBER := 0;
BEGIN
WHILE (NOT GotIt AND NOT (Count > 10)) LOOP
    BEGIN
      -- Try to get free slot, if OK, set GotIt = TRUE
      -- else EXCEPTION will automatically fire.
      (Insert Code here)
      GotIt := TRUE;
    EXCEPTION
      WHEN OTHERS THEN
        GotIt := FALSE;
        DBMS_LOCK.SLEEP(10);
        Count := Count + 1;
    END;
END LOOP;
END;
/

Difference between a PROCEDURE & FUNCTION in oracle

Functions
----------
1) can be used with Select statement
2) Not returning output parameter but returns Table variables
3) You can join UDF
4) Cannot be used to change server configuration
5) Cannot be used with XML FOR clause
6) Cannot have transaction within function

Stored Procedure
-----------------
1) have to use EXEC or EXECUTE
2) return output parameter
3) can create table but won?t return Table Variables
4) you can not join SP
5) can be used to change server configuration
6) can be used with XML FOR Clause
7) can have transaction within SP

1. Functions must return a value(scalar, inline table or multi statement table) whereas stored proc may or may not return a value.
2. Functions can return a table whereas stored procs can create a table but can't return table.
3. Stored procs can be called independently using exec keyword whereas function are called using select statements.
4. Stored procs can be used to change server configuration(in terms of security-i.e. setting granular permissions of user rights) whereas function can't be used for this
5. XML and output parameters can't be passed to functions whereas it can be with sp's.
6. Transaction related statement can be handled in sp whereas it can't be in function.
7. Stored procedures can call a function or another stored proc similarly a function can call another function and a stored proc. The catch with function is that no user defined stored proc can be called. Only extended/system defined procs can be called.

Developers Arena