-------------------------------------------------------
-- Stored Procedure - No Parameters
-------------------------------------------------------
CREATE OR REPLACE PROCEDURE no_param IS
BEGIN
dbms_output.put_line('No Params');
END no_param;
/
set serveroutput on
exec no_param;
-------------------------------------------------------------
-- Stored Procedure - IN Parameter
-------------------------------------------------------------
CREATE OR REPLACE PROCEDURE in_param (mesg VARCHAR2) IS
BEGIN
dbms_output.put_line(mesg);
END in_param;
/
set serveroutput on
exec in_param('Single IN Parameter');
------------------------------------------------------------------
-- Stored Procedure - OUT Parameter
------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE out_param(mesg OUT VARCHAR2) IS
BEGIN
mesg := 'Single OUT Parameter';
END out_param;
/
set serveroutput on
DECLARE
s VARCHAR2(50);
BEGIN
out_param(s);
dbms_output.put_line(s);
END;
/
HOST VARIABLE TESTING THE ABOVE PROCEDURE
var x VARCHAR2(30)
exec out_param(:x)
print x
---------------------------------------------------------------------
-- Stored Procedure - IN OUT Parameter
---------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE inout_param (mesg IN OUT VARCHAR2) IS
BEGIN
mesg := mesg || ' an IN OUT Parameter';
END inout_param;
/
set serveroutput on
DECLARE
s VARCHAR2(50) := 'This procedure uses';
BEGIN
inout_param(s);
dbms_output.put_line(s);
END;
---------------------------------------------------------------------
-- Stored Procedure - Multiple Parameters
---------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE many_params (
mesg1 IN VARCHAR2,
mesg2 OUT VARCHAR2,
mesg3 IN OUT VARCHAR2) IS
BEGIN
mesg2 := mesg1 || 'Parameter As The OUT';
mesg3 := mesg3 || 'Returned';
END many_params;
/
set serveroutput on
DECLARE
iparm VARCHAR2(50) := 'This is the IN ';
oparm VARCHAR2(50);
ioparm VARCHAR2(50) := 'And This is the IN OUT ';
BEGIN
many_params(iparm, oparm, ioparm);
dbms_output.put_line(oparm || ' ' || ioparm);
END;
/
create or replace procedure testpr (n1 in number, n2 in number, tot out number, mul out number)
is
begin
tot := n1 + n2;
mul := n1 * n2;
end;
---- TO TEST THE ABOVE PROCEDURE
variable t1 number
variable t2 number
execute testpr(5,6, :t1,t2 )
print t
------------------------------------------------------------------------
-- Stored Procedure - Procedure Without Default
------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE no_default(num_rows INTEGER) IS
BEGIN
FOR r IN (SELECT object_name FROM all_objects
WHERE rownum < style="font-weight: bold;">------------------------------------------------------------------------
-- Stored Procedure - Procedure With Default
------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE with_default (
num_rows PLS_INTEGER DEFAULT 20) IS
BEGIN
FOR r IN (SELECT object_name FROM all_objects
WHERE rownum < style="font-weight: bold;">------------------------------------------------------------------------
-- Stored Procedure - Positional Notation
------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE positional (
min_nr PLS_INTEGER DEFAULT 100,
max_nr PLS_INTEGER DEFAULT 1000) IS
BEGIN
FOR r IN (SELECT table_name FROM user_tables
WHERE num_rows BETWEEN min_nr AND max_nr)
LOOP
dbms_output.put_line(r.table_name);
END LOOP;
END positional;
/
set serveroutput on
exec positional;
exec positional(1);
exec positional(1000, 500000);
------------------------------------------------------------------------
-- Stored Procedure - Named Notation
------------------------------------------------------------------------
exec positional;
exec positional(min_nr => 1);
exec positional(max_nr => 500);
exec positional(max_nr => 5000);
exec positional(max_nr => 10000);
exec positional(max_nr => 50000);
exec positional(max_nr => 999999);
------------------------------------------------------------------------
-- Stored Procedure - Mixed Notation
------------------------------------------------------------------------
exec positional(10, max_nr => 1000);
---------------------------------------------------------------------------------------------------------------------------------------------
-- Dynamic SQL Procedure that Accepts Table Name and WHERE Clause - Execute immediate
---------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE employees_temp AS SELECT * FROM employees;
CREATE OR REPLACE PROCEDURE delete_rows (
table_name IN VARCHAR2,
condition IN VARCHAR2 DEFAULT NULL) AS
where_clause VARCHAR2(100) := ' WHERE ' || condition;
v_table VARCHAR2(30);
BEGIN
-- first make sure that the table actually exists; if not, raise an exception
SELECT OBJECT_NAME INTO v_table FROM USER_OBJECTS
WHERE OBJECT_NAME = UPPER(table_name) AND OBJECT_TYPE = 'TABLE';
IF condition IS NULL THEN where_clause := NULL; END IF;
EXECUTE IMMEDIATE 'DELETE FROM ' || v_table || where_clause;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Invalid table: ' || table_name);
END;
/
BEGIN
delete_rows('employees_temp', 'employee_id = 111');
END;
/
-----------------------------------------------------------------------------------------------
--EXECUTE IMMEDIATE
------------------------------------------------------------------------------------------------
When to Use or Omit the Semicolon with Dynamic SQL
When building up a single SQL statement in a string, do not include any semicolon at the end.
When building up a PL/SQL anonymous block, include the semicolon at the end of each PL/SQL statement and at the end of the anonymous block. For example:
BEGIN
EXECUTE IMMEDIATE 'BEGIN DBMS_OUTPUT.PUT_LINE(''semicolons''); END;';
END;
/
CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
END;
/
----------------------------------------------------------------------------------
-- this procedure can drop any kind of database object
----------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2)
AUTHID CURRENT_USER AS
BEGIN
EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;
END;
/
-----------------------------------------------------------------
--- Avoiding Deadlocks with Dynamic SQL
-----------------------------------------------------------------
In a few situations, executing a SQL data definition statement results in a deadlock. For example, the following procedure causes a deadlock because it attempts to drop itself. To avoid deadlocks, never try to ALTER or DROP a subprogram or package while you are still using it.
CREATE OR REPLACE PROCEDURE calc_bonus (emp_id NUMBER) AS
BEGIN
EXECUTE IMMEDIATE 'DROP PROCEDURE calc_bonus'; -- deadlock!
END;
/
Monday, March 30, 2009
Thursday, March 12, 2009
ADVANCE FUNCTION
BFilename Function
First, we need to create a directory object called exampleDir that points to /example/totn on the file server.
CREATE DIRECTORY exampleDir AS 'C:\TEMP\';
Then we can use the exampleDir directory object in the bfilename function as follows:
SELECT bfilename('exampleDir', 'totn_logo.jpg') FROM dual;
CASE - It has the functionality of an IF-THEN-ELSE statement.
select table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;
select table_name,
CASE
WHEN owner='SYS' THEN 'The owner is SYS'
WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;
select supplier_id,
CASE
WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
from suppliers;
select
CASE
WHEN a < style="font-weight: bold;">COALSCE - function returns the first non-null expression in the list. If all expressions evaluate to null, then the coalesce function will return null.
SELECT coalesce( address1, address2, address3 ) result FROM SCMSUPPLIERSITES
DECODE - Function has the functionality of an IF-THEN-ELSE statement.
SELECT supplier_name,
decode(supplier_id, 10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result
FROM suppliers;
LAG function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from a previous row in the table.
select headerid,itemid, requesteddate,
lag (requesteddate,1) over (ORDER BY requesteddate) AS prev_order_date
from POSLINES
where itemid = 38780;
LEAD function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from the next row in the table
select headerid,itemid, requesteddate,
LEAD (requesteddate,1) over (ORDER BY requesteddate) AS NEXT_order_date
from POSLINES
where itemid = 38780;
LNNVL - unction is used in the WHERE clause of an SQL statement to evaluate a condition when one of the operands may contain a NULL value.
select * from STOCKITEMS where LNNVL(qty >= reorder_level);
Condition Evaluates To LNNVL Return Value
TRUE FALSE
FALSE TRUE
UNKNOWN TRUE
NULLIF
SELECT NULLIF(12, 12) FROM DUAL -- would return NULL
SELECT NULLIF(12, 13) FROM DUAL -- would return 12
SELECT NULLIF('apples', 'apples') FROM DUAL -- would return NULL
SELECT NULLIF('apples', 'oranges') FROM DUAL -- would return 'apples'
SELECT NULLIF(NULL, 12) FROM DUAL -- would return an ORA-00932 error because expr1 can not be the literal NULL
NVL Function lets you substitute a value when a null value is encountered.
select HEADERID, NVL(CUSTOMERORDERREFERENCE, 'not found') cust_order_ref
from POSHEADERS
NVL2 function extends the functionality found in the NVL function. It lets you substitutes a value when a null value is encountered as well as when a non-null value is encountered.
select HEADERID, NVL2(CUSTOMERORDERREFERENCE, 'AVAILABLE','not found') cust_order_ref from POSHEADERS
sys_context function can be used to retrieve information about the Oracle environment.
SELECT sys_context('USERENV', 'TERMINAL') FROM DUAL
SELECT sys_context('USERENV', 'OS_USER') FROM DUAL
SELECT sys_context('USERENV', 'SESSION_USER') FROM DUAL
SELECT sys_context('USERENV', 'SESSION_USERID') FROM DUAL
SELECT sys_context('USERENV', 'TERMINAL') FROM DUAL
SELECT sys_context('USERENV', 'LANGUAGE') FROM DUAL
SELECT sys_context('USERENV', 'IP_ADDRESS') FROM DUAL
SELECT sys_context('USERENV', 'HOST') FROM DUAL
SELECT sys_context('USERENV', 'NETWORK_PROTOCOL') FROM DUAL
uid function returns the id number for a user's session (the user who is currently logged in).
user function returns the user_id from the current Oracle session.
select uid,USER from dual;
SELECT userenv('TERMINAL') FROM DUAL -- would return FALSE
SELECT userenv('LANGUAGE') FROM DUAL -- would return 'AMERICAN_AMERICA.WE8DEC'
First, we need to create a directory object called exampleDir that points to /example/totn on the file server.
CREATE DIRECTORY exampleDir AS 'C:\TEMP\';
Then we can use the exampleDir directory object in the bfilename function as follows:
SELECT bfilename('exampleDir', 'totn_logo.jpg') FROM dual;
CASE - It has the functionality of an IF-THEN-ELSE statement.
select table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;
select table_name,
CASE
WHEN owner='SYS' THEN 'The owner is SYS'
WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;
select supplier_id,
CASE
WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
from suppliers;
select
CASE
WHEN a < style="font-weight: bold;">COALSCE - function returns the first non-null expression in the list. If all expressions evaluate to null, then the coalesce function will return null.
SELECT coalesce( address1, address2, address3 ) result FROM SCMSUPPLIERSITES
DECODE - Function has the functionality of an IF-THEN-ELSE statement.
SELECT supplier_name,
decode(supplier_id, 10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result
FROM suppliers;
LAG function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from a previous row in the table.
select headerid,itemid, requesteddate,
lag (requesteddate,1) over (ORDER BY requesteddate) AS prev_order_date
from POSLINES
where itemid = 38780;
LEAD function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from the next row in the table
select headerid,itemid, requesteddate,
LEAD (requesteddate,1) over (ORDER BY requesteddate) AS NEXT_order_date
from POSLINES
where itemid = 38780;
LNNVL - unction is used in the WHERE clause of an SQL statement to evaluate a condition when one of the operands may contain a NULL value.
select * from STOCKITEMS where LNNVL(qty >= reorder_level);
Condition Evaluates To LNNVL Return Value
TRUE FALSE
FALSE TRUE
UNKNOWN TRUE
NULLIF
SELECT NULLIF(12, 12) FROM DUAL -- would return NULL
SELECT NULLIF(12, 13) FROM DUAL -- would return 12
SELECT NULLIF('apples', 'apples') FROM DUAL -- would return NULL
SELECT NULLIF('apples', 'oranges') FROM DUAL -- would return 'apples'
SELECT NULLIF(NULL, 12) FROM DUAL -- would return an ORA-00932 error because expr1 can not be the literal NULL
NVL Function lets you substitute a value when a null value is encountered.
select HEADERID, NVL(CUSTOMERORDERREFERENCE, 'not found') cust_order_ref
from POSHEADERS
NVL2 function extends the functionality found in the NVL function. It lets you substitutes a value when a null value is encountered as well as when a non-null value is encountered.
select HEADERID, NVL2(CUSTOMERORDERREFERENCE, 'AVAILABLE','not found') cust_order_ref from POSHEADERS
sys_context function can be used to retrieve information about the Oracle environment.
SELECT sys_context('USERENV', 'TERMINAL') FROM DUAL
SELECT sys_context('USERENV', 'OS_USER') FROM DUAL
SELECT sys_context('USERENV', 'SESSION_USER') FROM DUAL
SELECT sys_context('USERENV', 'SESSION_USERID') FROM DUAL
SELECT sys_context('USERENV', 'TERMINAL') FROM DUAL
SELECT sys_context('USERENV', 'LANGUAGE') FROM DUAL
SELECT sys_context('USERENV', 'IP_ADDRESS') FROM DUAL
SELECT sys_context('USERENV', 'HOST') FROM DUAL
SELECT sys_context('USERENV', 'NETWORK_PROTOCOL') FROM DUAL
uid function returns the id number for a user's session (the user who is currently logged in).
user function returns the user_id from the current Oracle session.
select uid,USER from dual;
SELECT userenv('TERMINAL') FROM DUAL -- would return FALSE
SELECT userenv('LANGUAGE') FROM DUAL -- would return 'AMERICAN_AMERICA.WE8DEC'
Mathematical Functions
ABS - function returns the absolute value of a number.
SELECT abs(-23) FROM DUAL --would return 23
SELECT abs(-23.6) FROM DUAL --would return 23.6
SELECT abs(-23.65) FROM DUAL --would return 23.65
SELECT abs(23.65) FROM DUAL --would return 23.65
SELECT abs(23.65 * -1) FROM DUAL --would return 23.65
ACOS - function returns the arc cosine of a number.
SELECT acos(1) FROM DUAL -- would return 0
SELECT acos(0) FROM DUAL -- would return 1.5707963267949
SELECT acos(-1) FROM DUAL -- would return 3.14159265358979
asin function returns the arc sine of a number.
SELECT asin(0.2) FROM DUAL -- would return 0.201357920790331
SELECT asin(0.35) FROM DUAL -- would return 0.35757110364551
SELECT asin(-0.15) FROM DUAL -- would return -0.150568272776686
atan function returns the arc tangent of a number.
SELECT atan(0.2) FROM DUAL -- would return 0.197395559849881
SELECT atan(0.35) FROM DUAL -- would return 0.336674819386727
SELECT atan(-0.15) FROM DUAL --would return -0.148889947609497
atan2 function returns the arc tangent of n and m.
SELECT atan2(0.2, 0.3) FROM DUAL -- would return 0.588002603547568
SELECT atan2(0.35, -0.15) FROM DUAL --would return 1.97568811307998
SELECT atan2(-0.3, -0.4) FROM DUAL --would return -2.49809154479651
Avg function returns the average value of an expression.
SELECT Avg(sal) as "Avg Salary" FROM emp WHERE sal > 25000;
SELECT Avg(DISTINCT sal) as "Avg Salary" FROM emp WHERE sal > 25000;
BIT_TO_NUM - function converts a bit vector to a number.
SELECT bin_to_num(1) FROM DUAL -- would return 1
SELECT bin_to_num(1,0) FROM DUAL --would return 2
SELECT bin_to_num(1,1) FROM DUAL --would return 3
SELECT bin_to_num(1,1,1,0) FROM DUAL --would return 14
SELECT bin_to_num(1,1,1,1) FROM DUAL --would return 15
CEIL - ceil function returns the smallest integer value that is greater than or equal to a number.
SELECT ceil(32.02) FROM DUAL --would return -33
SELECT ceil(32.65) FROM DUAL --would return 33.
SELECT ceil(32) FROM DUAL --would return 32.
SELECT ceil(-32.65) FROM DUAL --would return -32.
SELECT ceil(-32) FROM DUAL --would return -32
floor function returns the largest integer value that is equal to or less than a number.
SELECT floor(5.9) FROM DUAL -- would return 5
SELECT floor(34.29) FROM DUAL -- would return 34
SELECT floor(-5.9) FROM DUAL --would return -6
cos - function returns the cosine of a number.
SELECT cos(0.2) FROM DUAL -- would return 0.980066577841242
SELECT cos(0.35) FROM DUAL -- would return 0.939372712847379
SELECT cos(-3.15) FROM DUAL -- would return -0.999964658471342
SELECT cos(200) FROM DUAL -- would return 0.487187675007006
cosh -function returns the hyperbolic cosine of a number.
select cosh(0.2) from dual -- would return 1.02006675561908
select cosh(0.35) from dual -- would return 1.06187781915599
select cosh(-3.15) from dual -- would return 11.6894583539049
select cosh(200) from dual -- would return 3.61298688406287E86
cosh - function returns the hyperbolic cosine of a number.
select cosh(0.2) from dual -- would return 1.02006675561908
select cosh(0.35) from dual -- would return 1.06187781915599
select cosh(-3.15) from dual -- would return 11.6894583539049
select cosh(200) from dual -- would return 3.61298688406287E86
COUNT - function will only count those records in which the field in the brackets is NOT NULL
SELECT COUNT(*) as "Number of employees" FROM emp
SELECT COUNT(distinct dept) FROM emp
extract
select extract(YEAR FROM DATE '2003-08-22') from dual -- would return 2003
select extract(MONTH FROM DATE '2003-08-22') from dual -- would return 8
select extract(DAY FROM DATE '2003-08-22') from dual --would return 22
greatest function returns the greatest value in a list of expressions.
SELECT greatest(2, 5, 12, 3) FROM DUAL -- would return 12
SELECT greatest('2', '5', '12', '3') FROM DUAL -- would return '5'
SELECT greatest('apples', 'oranges', 'bananas') FROM DUAL --would return 'oranges'
SELECT greatest('apples', 'applis', 'applas') FROM DUAL --would return 'applis'
least function returns the smallest value in a list of expressions.
SELECT least(2, 5, 12, 3) FROM DUAL -- would return 2
SELECT least('2', '5', '12', '3') FROM DUAL --would return '12'
SELECT least('apples', 'oranges', 'bananas') FROM DUAL -- would return 'apples'
SELECT least('apples', 'applis', 'applas') FROM DUAL --would return 'applas'
SELECT least('apples', 'applis', 'applas', null) FROM DUAL -- would return NULL
LOG function returns the logarithm of n base m.
SELECT log(10, 20) FROM DUAL -- would return 1.30102999566398
SELECT log(2, 15) FROM DUAL -- would return 3.90689059560852
SELECT log(100, 1) FROM DUAL --would return 0
MAX function returns the maximum value of an expression.
SELECT MAX(SAL) FROM EMP
MIN function returns the minimum value of an expression.
SELECT MIN(salary) as "Lowest salary" FROM employees;
median function returns the median of an expression.
select median(salary) from employees where department = 'Marketing';
mod function returns the remainder of m divided by n.
SELECT mod(15, 4) FROM DUAL -- would return 3
SELECT mod(15, 0) FROM DUAL -- would return 15
SELECT mod(11.6, 2) FROM DUAL -- would return 1.6
SELECT mod(11.6, 2.1) FROM DUAL -- would return 1.1
SELECT mod(-15, 4) FROM DUAL -- would return -3
SELECT mod(-15, 0) FROM DUAL -- would return -15
power function returns m raised to the nth power.
select power(3, 2) from dual -- would return 9
select power(5, 3) from dual --would return 125
select power(-5, 3) from dual -- would return -125
select power(6.2, 3) from dual -- would return 238.328
select power(6.2, 3.5) from dual -- would return 593.431934277892
rank function returns the rank of a value in a group of values (view must create )
select customerid, ordertotalamount,
rank() OVER (PARTITION BY saleschannelid ORDER BY ordertotalamount) rank
from posheaders
remainder function returns the remainder of m divided by n.
select remainder(15, 6) from dual -- would return 3
select remainder(15, 5) from dual -- would return 0
select remainder(15, 4) from dual -- would return -1
select remainder(11.6, 2) from dual -- would return -0.4
select remainder(11.6, 2.1) from dual -- would return -1
select remainder(-15, 4) from dual -- would return 1
round function returns a number rounded to a certain number of decimal places.
select round(125.315) from dual -- would return 125
select round(125.315, 0) from dual -- would return 125
select round(125.315, 1) from dual -- would return 125.3
select round(125.315, 2) from dual -- would return 125.32
select round(125.315, 3) from dual -- would return 125.315
select round(-125.315, 2) from dual -- would return -125.32
select round(to_date ('22-AUG-03'),'YEAR') from dual -- would return '01-JAN-04'
select round(to_date ('22-AUG-03'),'Q') from dual --would return '01-OCT-03'
select round(to_date ('22-AUG-03'),'MONTH') from dual -- would return '01-SEP-03'
select round(to_date ('22-AUG-03'),'DDD') from dual -- would return '22-AUG-03'
select round(to_date ('22-AUG-03'),'DAY') from dual -- would return '24-AUG-03'
sin function returns the sine of n.
select sin(3) from dual -- would return 0.141120008059867
select sin(5.2) from dual -- would return -0.883454655720153
select sin(-5.2) from dual -- would return 0.883454655720153
sqrt function returns the square root of n.
select sqrt(9) from dual -- would return 3
select sqrt(37) from dual -- would return 6.08276253029822
select sqrt(5.617) from dual -- would return 2.37002109695251
SUM function returns the summed value of an expression.
SELECT SUM(salary) as "Total Salary" FROM employees WHERE salary > 25000;
tan function returns the tangent of n.
select tan(3) from dual -- would return -0.142546543074278
select tan(5.2) from dual -- would return -1.88564187751976
select tan(-5.2) from dual -- would return 1.88564187751976
trunc function returns a number truncated to a certain number of decimal places.
select trunc(125.815) from dual -- would return 125
select trunc(125.815, 0) from dual -- would return 125
select trunc(125.815, 1) from dual -- would return 125.8
select trunc(125.815, 2) from daul -- would return 125.81
select trunc(125.815, 3) from daul -- would return 125.815
select trunc(-125.815, 2) from daul -- would return -125.81
select trunc(125.815, -1) from dual -- would return 120
select trunc(125.815, -2) from dual -- would return 100
select trunc(125.815, -3) from dual -- would return 0
select trunc(to_date('22-AUG-03'), 'YEAR') from dual -- would return '01-JAN-03'
select trunc(to_date('22-AUG-03'), 'Q') from dual -- would return '01-JUL-03'
select trunc(to_date('22-AUG-03'), 'MONTH') from dual -- would return '01-AUG-03'
select trunc(to_date('22-AUG-03'), 'DDD') from dual -- would return '22-AUG-03'
select trunc(to_date('22-AUG-03'), 'DAY') from dual -- would return '17-AUG-03'
SELECT abs(-23) FROM DUAL --would return 23
SELECT abs(-23.6) FROM DUAL --would return 23.6
SELECT abs(-23.65) FROM DUAL --would return 23.65
SELECT abs(23.65) FROM DUAL --would return 23.65
SELECT abs(23.65 * -1) FROM DUAL --would return 23.65
ACOS - function returns the arc cosine of a number.
SELECT acos(1) FROM DUAL -- would return 0
SELECT acos(0) FROM DUAL -- would return 1.5707963267949
SELECT acos(-1) FROM DUAL -- would return 3.14159265358979
asin function returns the arc sine of a number.
SELECT asin(0.2) FROM DUAL -- would return 0.201357920790331
SELECT asin(0.35) FROM DUAL -- would return 0.35757110364551
SELECT asin(-0.15) FROM DUAL -- would return -0.150568272776686
atan function returns the arc tangent of a number.
SELECT atan(0.2) FROM DUAL -- would return 0.197395559849881
SELECT atan(0.35) FROM DUAL -- would return 0.336674819386727
SELECT atan(-0.15) FROM DUAL --would return -0.148889947609497
atan2 function returns the arc tangent of n and m.
SELECT atan2(0.2, 0.3) FROM DUAL -- would return 0.588002603547568
SELECT atan2(0.35, -0.15) FROM DUAL --would return 1.97568811307998
SELECT atan2(-0.3, -0.4) FROM DUAL --would return -2.49809154479651
Avg function returns the average value of an expression.
SELECT Avg(sal) as "Avg Salary" FROM emp WHERE sal > 25000;
SELECT Avg(DISTINCT sal) as "Avg Salary" FROM emp WHERE sal > 25000;
BIT_TO_NUM - function converts a bit vector to a number.
SELECT bin_to_num(1) FROM DUAL -- would return 1
SELECT bin_to_num(1,0) FROM DUAL --would return 2
SELECT bin_to_num(1,1) FROM DUAL --would return 3
SELECT bin_to_num(1,1,1,0) FROM DUAL --would return 14
SELECT bin_to_num(1,1,1,1) FROM DUAL --would return 15
CEIL - ceil function returns the smallest integer value that is greater than or equal to a number.
SELECT ceil(32.02) FROM DUAL --would return -33
SELECT ceil(32.65) FROM DUAL --would return 33.
SELECT ceil(32) FROM DUAL --would return 32.
SELECT ceil(-32.65) FROM DUAL --would return -32.
SELECT ceil(-32) FROM DUAL --would return -32
floor function returns the largest integer value that is equal to or less than a number.
SELECT floor(5.9) FROM DUAL -- would return 5
SELECT floor(34.29) FROM DUAL -- would return 34
SELECT floor(-5.9) FROM DUAL --would return -6
cos - function returns the cosine of a number.
SELECT cos(0.2) FROM DUAL -- would return 0.980066577841242
SELECT cos(0.35) FROM DUAL -- would return 0.939372712847379
SELECT cos(-3.15) FROM DUAL -- would return -0.999964658471342
SELECT cos(200) FROM DUAL -- would return 0.487187675007006
cosh -function returns the hyperbolic cosine of a number.
select cosh(0.2) from dual -- would return 1.02006675561908
select cosh(0.35) from dual -- would return 1.06187781915599
select cosh(-3.15) from dual -- would return 11.6894583539049
select cosh(200) from dual -- would return 3.61298688406287E86
cosh - function returns the hyperbolic cosine of a number.
select cosh(0.2) from dual -- would return 1.02006675561908
select cosh(0.35) from dual -- would return 1.06187781915599
select cosh(-3.15) from dual -- would return 11.6894583539049
select cosh(200) from dual -- would return 3.61298688406287E86
COUNT - function will only count those records in which the field in the brackets is NOT NULL
SELECT COUNT(*) as "Number of employees" FROM emp
SELECT COUNT(distinct dept) FROM emp
extract
select extract(YEAR FROM DATE '2003-08-22') from dual -- would return 2003
select extract(MONTH FROM DATE '2003-08-22') from dual -- would return 8
select extract(DAY FROM DATE '2003-08-22') from dual --would return 22
greatest function returns the greatest value in a list of expressions.
SELECT greatest(2, 5, 12, 3) FROM DUAL -- would return 12
SELECT greatest('2', '5', '12', '3') FROM DUAL -- would return '5'
SELECT greatest('apples', 'oranges', 'bananas') FROM DUAL --would return 'oranges'
SELECT greatest('apples', 'applis', 'applas') FROM DUAL --would return 'applis'
least function returns the smallest value in a list of expressions.
SELECT least(2, 5, 12, 3) FROM DUAL -- would return 2
SELECT least('2', '5', '12', '3') FROM DUAL --would return '12'
SELECT least('apples', 'oranges', 'bananas') FROM DUAL -- would return 'apples'
SELECT least('apples', 'applis', 'applas') FROM DUAL --would return 'applas'
SELECT least('apples', 'applis', 'applas', null) FROM DUAL -- would return NULL
LOG function returns the logarithm of n base m.
SELECT log(10, 20) FROM DUAL -- would return 1.30102999566398
SELECT log(2, 15) FROM DUAL -- would return 3.90689059560852
SELECT log(100, 1) FROM DUAL --would return 0
MAX function returns the maximum value of an expression.
SELECT MAX(SAL) FROM EMP
MIN function returns the minimum value of an expression.
SELECT MIN(salary) as "Lowest salary" FROM employees;
median function returns the median of an expression.
select median(salary) from employees where department = 'Marketing';
mod function returns the remainder of m divided by n.
SELECT mod(15, 4) FROM DUAL -- would return 3
SELECT mod(15, 0) FROM DUAL -- would return 15
SELECT mod(11.6, 2) FROM DUAL -- would return 1.6
SELECT mod(11.6, 2.1) FROM DUAL -- would return 1.1
SELECT mod(-15, 4) FROM DUAL -- would return -3
SELECT mod(-15, 0) FROM DUAL -- would return -15
power function returns m raised to the nth power.
select power(3, 2) from dual -- would return 9
select power(5, 3) from dual --would return 125
select power(-5, 3) from dual -- would return -125
select power(6.2, 3) from dual -- would return 238.328
select power(6.2, 3.5) from dual -- would return 593.431934277892
rank function returns the rank of a value in a group of values (view must create )
select customerid, ordertotalamount,
rank() OVER (PARTITION BY saleschannelid ORDER BY ordertotalamount) rank
from posheaders
remainder function returns the remainder of m divided by n.
select remainder(15, 6) from dual -- would return 3
select remainder(15, 5) from dual -- would return 0
select remainder(15, 4) from dual -- would return -1
select remainder(11.6, 2) from dual -- would return -0.4
select remainder(11.6, 2.1) from dual -- would return -1
select remainder(-15, 4) from dual -- would return 1
round function returns a number rounded to a certain number of decimal places.
select round(125.315) from dual -- would return 125
select round(125.315, 0) from dual -- would return 125
select round(125.315, 1) from dual -- would return 125.3
select round(125.315, 2) from dual -- would return 125.32
select round(125.315, 3) from dual -- would return 125.315
select round(-125.315, 2) from dual -- would return -125.32
select round(to_date ('22-AUG-03'),'YEAR') from dual -- would return '01-JAN-04'
select round(to_date ('22-AUG-03'),'Q') from dual --would return '01-OCT-03'
select round(to_date ('22-AUG-03'),'MONTH') from dual -- would return '01-SEP-03'
select round(to_date ('22-AUG-03'),'DDD') from dual -- would return '22-AUG-03'
select round(to_date ('22-AUG-03'),'DAY') from dual -- would return '24-AUG-03'
sin function returns the sine of n.
select sin(3) from dual -- would return 0.141120008059867
select sin(5.2) from dual -- would return -0.883454655720153
select sin(-5.2) from dual -- would return 0.883454655720153
sqrt function returns the square root of n.
select sqrt(9) from dual -- would return 3
select sqrt(37) from dual -- would return 6.08276253029822
select sqrt(5.617) from dual -- would return 2.37002109695251
SUM function returns the summed value of an expression.
SELECT SUM(salary) as "Total Salary" FROM employees WHERE salary > 25000;
tan function returns the tangent of n.
select tan(3) from dual -- would return -0.142546543074278
select tan(5.2) from dual -- would return -1.88564187751976
select tan(-5.2) from dual -- would return 1.88564187751976
trunc function returns a number truncated to a certain number of decimal places.
select trunc(125.815) from dual -- would return 125
select trunc(125.815, 0) from dual -- would return 125
select trunc(125.815, 1) from dual -- would return 125.8
select trunc(125.815, 2) from daul -- would return 125.81
select trunc(125.815, 3) from daul -- would return 125.815
select trunc(-125.815, 2) from daul -- would return -125.81
select trunc(125.815, -1) from dual -- would return 120
select trunc(125.815, -2) from dual -- would return 100
select trunc(125.815, -3) from dual -- would return 0
select trunc(to_date('22-AUG-03'), 'YEAR') from dual -- would return '01-JAN-03'
select trunc(to_date('22-AUG-03'), 'Q') from dual -- would return '01-JUL-03'
select trunc(to_date('22-AUG-03'), 'MONTH') from dual -- would return '01-AUG-03'
select trunc(to_date('22-AUG-03'), 'DDD') from dual -- would return '22-AUG-03'
select trunc(to_date('22-AUG-03'), 'DAY') from dual -- would return '17-AUG-03'
DATE FUNCTION
ADD_MONTH function returns a date plus n months.
SELECT add_months('01-Aug-03', 3) FROM DUAL -- would return '01-Nov-03'
SELECT add_months('01-Aug-03', -3) FROM DUAL -- would return '01-May-03'
SELECT add_months('21-Aug-03', -3) FROM DUAL -- would return '21-May-03'
SELECT add_months('31-Jan-03', 1) FROM DUAL -- would return '28-Feb-03'
current_date
function returns the current date in the time zone of the current SQL session as set by the ALTER SESSION command.
select current_date from dual;
ALTER SESSION SET TIME_ZONE = '-7:0';
select current_date from dual;
ALTER SESSION SET TIME_ZONE = '5:30';
CURRENT_TIMESTAMP
select current_timestamp from dual;
from_tz function converts a TIMESTAMP value (given a TIME ZONE) to a TIMESTAMP WITH TIME ZONE value.
select from_tz(TIMESTAMP '2005-09-11 01:50:42', '5:00') from dual;
select from_tz(TIMESTAMP '2005-09-11 01:50:42', '-7:00') from dual;
LAST_DAY
SELECT last_day(to_date('2003/03/15', 'yyyy/mm/dd')) FROM DUAL -- would return Mar 31, 2003
SELECT last_day(to_date('2003/02/03', 'yyyy/mm/dd')) FROM DUAL -- would return Feb 28, 2003
SELECT last_day(to_date('2004/02/03', 'yyyy/mm/dd')) FROM DUAL -- would return Feb 29, 2004
select sessiontimezone from dual;
select localtimestamp from dual;
months_between
SELECT months_between (to_date ('2003/01/01', 'yyyy/mm/dd'), to_date ('2003/03/14', 'yyyy/mm/dd') ) FROM DUAL
-- would return -2.41935483870968
SELECT months_between (to_date ('2003/07/01', 'yyyy/mm/dd'), to_date ('2003/03/14', 'yyyy/mm/dd') ) FROM DUAL
-- would return 3.58064516129032
SELECT months_between (to_date ('2003/07/02', 'yyyy/mm/dd'), to_date ('2003/07/02', 'yyyy/mm/dd') ) FROM DUAL
-- would return 0
SELECT months_between (to_date ('2003/08/02', 'yyyy/mm/dd'), to_date ('2003/06/02', 'yyyy/mm/dd') ) FROM DUAL
-- would return 2
new_time function returns a date in time zone1 to a date in time zone2.
SELECT new_time (to_date ('2003/11/01 01:45', 'yyyy/mm/dd HH24:MI'), 'AST', 'MST') FROM DUAL
NEXT_DAY function returns the first weekday that is greater than a date.
SELECT next_day('01-Aug-03', 'TUESDAY') FROM DUAL -- would return '05-Aug-03'
SELECT next_day('06-Aug-03', 'WEDNESDAY') FROM DUAL --would return '13-Aug-03'
SELECT next_day('06-Aug-03', 'SUNDAY') FROM DUAL --would return '10-Aug-03'
ROUND
SELECT round(to_date ('22-AUG-03'),'YEAR') FROM DUAL -- would return '01-JAN-04'
SELECT round(to_date ('22-AUG-03'),'Q') FROM DUAL --would return '01-OCT-03'
SELECT round(to_date ('22-AUG-03'),'MONTH') FROM DUAL --would return '01-SEP-03'
SELECT round(to_date ('22-AUG-03'),'DDD') FROM DUAL --would return '22-AUG-03'
SELECT round(to_date ('22-AUG-03'),'DAY') FROM DUAL --would return '24-AUG-03'
SELECT SYSDATE FROM DUAL
select systimestamp from dual;
to_char
SELECT to_char(sysdate, 'yyyy/mm/dd') FROM DUAL -- would return '2003/07/09'
SELECT to_char(sysdate, 'Month DD, YYYY') FROM DUAL -- would return 'July 09, 2003'
SELECT to_char(sysdate, 'FMMonth DD, YYYY') FROM DUAL -- would return 'July 9, 2003'
SELECT to_char(sysdate, 'MON DDth, YYYY') FROM DUAL -- would return 'JUL 09TH, 2003'
SELECT to_char(sysdate, 'FMMON DDth, YYYY') FROM DUAL -- would return 'JUL 9TH, 2003'
SELECT to_char(sysdate, 'FMMon ddth, YYYY') FROM DUAL -- would return 'Jul 9th, 2003'
to_date
SELECT to_date('2003/07/09', 'yyyy/mm/dd') FROM DUAL -- would return a date value of July 9, 2003.
SELECT to_date('070903', 'MMDDYY') FROM DUAL -- would return a date value of July 9, 2003.
SELECT to_date('20020315', 'yyyymmdd') FROM DUAL -- would return a date value of Mar 15, 2002.
trunc
SELECT trunc(to_date('22-AUG-03'), 'YEAR') FROM DUAL -- would return '01-JAN-03'
SELECT trunc(to_date('22-AUG-03'), 'Q') FROM DUAL -- would return '01-JUL-03'
SELECT trunc(to_date('22-AUG-03'), 'MONTH') FROM DUAL -- would return '01-AUG-03'
SELECT trunc(to_date('22-AUG-03'), 'DDD') FROM DUAL -- would return '22-AUG-03'
SELECT trunc(to_date('22-AUG-03'), 'DAY') FROM DUAL -- would return '17-AUG-03'
SELECT add_months('01-Aug-03', 3) FROM DUAL -- would return '01-Nov-03'
SELECT add_months('01-Aug-03', -3) FROM DUAL -- would return '01-May-03'
SELECT add_months('21-Aug-03', -3) FROM DUAL -- would return '21-May-03'
SELECT add_months('31-Jan-03', 1) FROM DUAL -- would return '28-Feb-03'
current_date
function returns the current date in the time zone of the current SQL session as set by the ALTER SESSION command.
select current_date from dual;
ALTER SESSION SET TIME_ZONE = '-7:0';
select current_date from dual;
ALTER SESSION SET TIME_ZONE = '5:30';
CURRENT_TIMESTAMP
select current_timestamp from dual;
from_tz function converts a TIMESTAMP value (given a TIME ZONE) to a TIMESTAMP WITH TIME ZONE value.
select from_tz(TIMESTAMP '2005-09-11 01:50:42', '5:00') from dual;
select from_tz(TIMESTAMP '2005-09-11 01:50:42', '-7:00') from dual;
LAST_DAY
SELECT last_day(to_date('2003/03/15', 'yyyy/mm/dd')) FROM DUAL -- would return Mar 31, 2003
SELECT last_day(to_date('2003/02/03', 'yyyy/mm/dd')) FROM DUAL -- would return Feb 28, 2003
SELECT last_day(to_date('2004/02/03', 'yyyy/mm/dd')) FROM DUAL -- would return Feb 29, 2004
select sessiontimezone from dual;
select localtimestamp from dual;
months_between
SELECT months_between (to_date ('2003/01/01', 'yyyy/mm/dd'), to_date ('2003/03/14', 'yyyy/mm/dd') ) FROM DUAL
-- would return -2.41935483870968
SELECT months_between (to_date ('2003/07/01', 'yyyy/mm/dd'), to_date ('2003/03/14', 'yyyy/mm/dd') ) FROM DUAL
-- would return 3.58064516129032
SELECT months_between (to_date ('2003/07/02', 'yyyy/mm/dd'), to_date ('2003/07/02', 'yyyy/mm/dd') ) FROM DUAL
-- would return 0
SELECT months_between (to_date ('2003/08/02', 'yyyy/mm/dd'), to_date ('2003/06/02', 'yyyy/mm/dd') ) FROM DUAL
-- would return 2
new_time function returns a date in time zone1 to a date in time zone2.
SELECT new_time (to_date ('2003/11/01 01:45', 'yyyy/mm/dd HH24:MI'), 'AST', 'MST') FROM DUAL
NEXT_DAY function returns the first weekday that is greater than a date.
SELECT next_day('01-Aug-03', 'TUESDAY') FROM DUAL -- would return '05-Aug-03'
SELECT next_day('06-Aug-03', 'WEDNESDAY') FROM DUAL --would return '13-Aug-03'
SELECT next_day('06-Aug-03', 'SUNDAY') FROM DUAL --would return '10-Aug-03'
ROUND
SELECT round(to_date ('22-AUG-03'),'YEAR') FROM DUAL -- would return '01-JAN-04'
SELECT round(to_date ('22-AUG-03'),'Q') FROM DUAL --would return '01-OCT-03'
SELECT round(to_date ('22-AUG-03'),'MONTH') FROM DUAL --would return '01-SEP-03'
SELECT round(to_date ('22-AUG-03'),'DDD') FROM DUAL --would return '22-AUG-03'
SELECT round(to_date ('22-AUG-03'),'DAY') FROM DUAL --would return '24-AUG-03'
SELECT SYSDATE FROM DUAL
select systimestamp from dual;
to_char
SELECT to_char(sysdate, 'yyyy/mm/dd') FROM DUAL -- would return '2003/07/09'
SELECT to_char(sysdate, 'Month DD, YYYY') FROM DUAL -- would return 'July 09, 2003'
SELECT to_char(sysdate, 'FMMonth DD, YYYY') FROM DUAL -- would return 'July 9, 2003'
SELECT to_char(sysdate, 'MON DDth, YYYY') FROM DUAL -- would return 'JUL 09TH, 2003'
SELECT to_char(sysdate, 'FMMON DDth, YYYY') FROM DUAL -- would return 'JUL 9TH, 2003'
SELECT to_char(sysdate, 'FMMon ddth, YYYY') FROM DUAL -- would return 'Jul 9th, 2003'
to_date
SELECT to_date('2003/07/09', 'yyyy/mm/dd') FROM DUAL -- would return a date value of July 9, 2003.
SELECT to_date('070903', 'MMDDYY') FROM DUAL -- would return a date value of July 9, 2003.
SELECT to_date('20020315', 'yyyymmdd') FROM DUAL -- would return a date value of Mar 15, 2002.
trunc
SELECT trunc(to_date('22-AUG-03'), 'YEAR') FROM DUAL -- would return '01-JAN-03'
SELECT trunc(to_date('22-AUG-03'), 'Q') FROM DUAL -- would return '01-JUL-03'
SELECT trunc(to_date('22-AUG-03'), 'MONTH') FROM DUAL -- would return '01-AUG-03'
SELECT trunc(to_date('22-AUG-03'), 'DDD') FROM DUAL -- would return '22-AUG-03'
SELECT trunc(to_date('22-AUG-03'), 'DAY') FROM DUAL -- would return '17-AUG-03'
CHARACTER & STRING FUNCTIONS
ascii
SELECT ascii('t') FROM DUAL -- would return 116.
SELECT ascii('T') FROM DUAL -- would return 84.
SELECT ascii('T2') FROM DUAL -- would also return 84.
chr
select chr(116) from dual -- would return 't'.
select chr(84) from dual -- would return 'T'.
UNISTR
Convert String To The National Character Set (either UTF8 or UTF16)
Unistring Value Resulting character
unistr('\0300') grave accent ( ` )
unistr('\0301') acute accent ( ´ )
unistr('\0302') circumflex ( ^ )
unistr('\0303') tilde ( ~ )
unistr('\0308') umlaut ( ¨ )
compose function returns a Unicode string.
select compose('o' || unistr('\0308') ) from dual -- would return ö
select compose('a' || unistr('\0302') ) from dual -- would return â
select compose('e' || unistr('\0301') ) from dual -- would return é
concat
select concat('Tech on', ' the Net') from dual -- would return 'Tech on the Net'.
select concat('a', 'b') from dual -- would return 'ab'.
|| Operator
select 'Tech on' || ' the Net' from dual -- would return 'Tech on the Net'.
select 'a' || 'b' || 'c' || 'd' from dual -- would return 'abcd'.
dump function returns a varchar2 value that includes the datatype code, the length in bytes, and the internal representation of the expression.
select dump('Tech') from dual -- would return 'Typ=96 Len=4: 84,101,99,104'
select dump('Tech', 10) from dual -- would return 'Typ=96 Len=4: 84,101,99,104'
select dump('Tech', 16) from dual -- would return 'Typ=96 Len=4: 54,65,63,68'
select dump('Tech', 1016) from dual -- would return 'Typ=96 Len=4 CharacterSet=US7ASCII: 54,65,63,68'
select dump('Tech', 1017) from dual -- would return 'Typ=96 Len=4 CharacterSet=US7ASCII: T,e,c,h'
Value Explanation
8 octal notation
10 decimal notation
16 hexadecimal notation
17 single characters
1008 octal notation with the character set name
1010 decimal notation with the character set name
1016 hexadecimal notation with the character set name
1017 single characters with the character set name
initcap
select initcap('tech on the net') from dual -- would return 'Tech On The Net'
select initcap('GEORGE BURNS') from dual -- would return 'George Burns'
upper
select upper('Tech on the Net') from dual -- would return 'TECH ON THE NET'
select upper('george burns 123 ') from dual -- would return 'GEORGE BURNS 123
lower
select lower('Tech on the Net') from dual -- would return 'tech on the net'
select lower('GEORGE BURNS 123 ') from dual -- would return 'george burns 123 '
instr
select instr('Tech on the net', 'e') from dual -- would return 2; the first occurrence of 'e'
select instr('Tech on the net', 'e', 1, 1) from dual -- would return 2; the first occurrence of 'e'
select instr('Tech on the net', 'e', 1, 2) from dual -- would return 11; the second occurrence of 'e'
select instr('Tech on the net', 'e', 1, 3) from dual -- would return 14; the third occurrence of 'e'
length
select length(NULL) from dual -- would return NULL.
select length('') from dual -- would return NULL.
select length('Tech on the Net') from dual -- would return 15.
select length('Tech on the Net ') from dual -- would return 16.
lower
select lower('Tech on the Net') from dual -- would return 'tech on the net'
select lower('GEORGE BURNS 123 ') from dual -- would return 'george burns 123
lpad function pads the left-side of a string with a specific set of characters.
select lpad('tech', 7) from dual -- would return ' tech'
select lpad('tech', 2) from dual -- would return 'te'
select lpad('tech', 8, '0') from dual-- would return '0000tech'
select lpad('tech on the net', 15, 'z') from dual --would return 'tech on the net'
select lpad('tech on the net', 16, 'z') from dual -- would return 'ztech on the net'
rpad function pads the right-side of a string with a specific set of characters.
select rpad('tech', 7) from dual -- would return 'tech '
select rpad('tech', 2) from dual -- would return 'te'
select rpad('tech', 8, '0') from dual -- would return 'tech0000'
select rpad('tech on the net', 15, 'z') from dual -- would return 'tech on the net'
select rpad('tech on the net', 16, 'z') from dual -- would return 'tech on the netz'
trim
select trim(' tech ') from dual -- would return 'tech'
select trim(' ' from ' tech ') from dual -- would return 'tech'
select trim(leading '0' from '000123') from dual -- would return '123'
select trim(trailing '1' from 'Tech1') from dual -- would return 'Tech'
select trim(both '1' from '123Tech111') from dual -- would return '23Tech'
ltrim
select ltrim(' tech') from dual -- would return 'tech'
select ltrim(' tech', ' ') from dual -- would return 'tech'
select ltrim('000123', '0') from dual -- would return '123'
select ltrim('123123Tech', '123') from dual -- would return 'Tech'
select ltrim('123123Tech123', '123') from dual -- would return 'Tech123'
select ltrim('xyxzyyyTech', 'xyz') from dual -- would return 'Tech'
select ltrim('6372Tech', '0123456789') from dual -- would return 'Tech'
rtrim
select rtrim('tech ') from dual -- would return 'tech'
select rtrim('tech ', ' ') from dual -- would return 'tech'
select rtrim('123000', '0') from dual -- would return '123'
select rtrim('Tech123123', '123') from dual -- would return 'Tech'
select rtrim('123Tech123', '123') from dual -- would return '123Tech'
select rtrim('Techxyxzyyy', 'xyz') from dual -- would return 'Tech'
select rtrim('Tech6372', '0123456789') from dual -- would return 'Tech'
replace function replaces a sequence of characters in a string with another set of characters.
select replace('123123tech', '123') from dual -- would return 'tech'
select replace('123tech123', '123') from dual -- would return 'tech'
select replace('222tech', '2', '3') from dual -- would return '333tech'
select replace('0000123', '0') from dual -- would return '123'
select replace('0000123', '0', ' ') from dual -- would return ' 123'
translate function replaces a sequence of characters in a string with another set of characters. . However, it replaces a single character at a time
select translate('1tech23', '123', '456') from dual -- would return '4tech56'
select translate('222tech', '2ec', '3it') from dual -- would return '333tith'
substr
select substr('This is a test', 6, 2) from dual -- would return 'is'
select substr('This is a test', 6) from dual -- would return 'is a test'
select substr('TechOnTheNet', 1, 4) from dual -- would return 'Tech'
select substr('TechOnTheNet', -3, 3) from dual -- would return 'Net'
select substr('TechOnTheNet', -6, 3) from dual -- would return 'The'
select substr('TechOnTheNet', -8, 2) from dual -- would return 'On'
CASE
select table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;
select table_name,
CASE
WHEN owner='SYS' THEN 'The owner is SYS'
WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;
TO_DSINTERVAL
SELECT SYSDATE, SYSDATE+ TO_DSINTERVAL('10 00:00:00') FROM DUAL
SELECT ascii('t') FROM DUAL -- would return 116.
SELECT ascii('T') FROM DUAL -- would return 84.
SELECT ascii('T2') FROM DUAL -- would also return 84.
chr
select chr(116) from dual -- would return 't'.
select chr(84) from dual -- would return 'T'.
UNISTR
Convert String To The National Character Set (either UTF8 or UTF16)
Unistring Value Resulting character
unistr('\0300') grave accent ( ` )
unistr('\0301') acute accent ( ´ )
unistr('\0302') circumflex ( ^ )
unistr('\0303') tilde ( ~ )
unistr('\0308') umlaut ( ¨ )
compose function returns a Unicode string.
select compose('o' || unistr('\0308') ) from dual -- would return ö
select compose('a' || unistr('\0302') ) from dual -- would return â
select compose('e' || unistr('\0301') ) from dual -- would return é
concat
select concat('Tech on', ' the Net') from dual -- would return 'Tech on the Net'.
select concat('a', 'b') from dual -- would return 'ab'.
|| Operator
select 'Tech on' || ' the Net' from dual -- would return 'Tech on the Net'.
select 'a' || 'b' || 'c' || 'd' from dual -- would return 'abcd'.
dump function returns a varchar2 value that includes the datatype code, the length in bytes, and the internal representation of the expression.
select dump('Tech') from dual -- would return 'Typ=96 Len=4: 84,101,99,104'
select dump('Tech', 10) from dual -- would return 'Typ=96 Len=4: 84,101,99,104'
select dump('Tech', 16) from dual -- would return 'Typ=96 Len=4: 54,65,63,68'
select dump('Tech', 1016) from dual -- would return 'Typ=96 Len=4 CharacterSet=US7ASCII: 54,65,63,68'
select dump('Tech', 1017) from dual -- would return 'Typ=96 Len=4 CharacterSet=US7ASCII: T,e,c,h'
Value Explanation
8 octal notation
10 decimal notation
16 hexadecimal notation
17 single characters
1008 octal notation with the character set name
1010 decimal notation with the character set name
1016 hexadecimal notation with the character set name
1017 single characters with the character set name
initcap
select initcap('tech on the net') from dual -- would return 'Tech On The Net'
select initcap('GEORGE BURNS') from dual -- would return 'George Burns'
upper
select upper('Tech on the Net') from dual -- would return 'TECH ON THE NET'
select upper('george burns 123 ') from dual -- would return 'GEORGE BURNS 123
lower
select lower('Tech on the Net') from dual -- would return 'tech on the net'
select lower('GEORGE BURNS 123 ') from dual -- would return 'george burns 123 '
instr
select instr('Tech on the net', 'e') from dual -- would return 2; the first occurrence of 'e'
select instr('Tech on the net', 'e', 1, 1) from dual -- would return 2; the first occurrence of 'e'
select instr('Tech on the net', 'e', 1, 2) from dual -- would return 11; the second occurrence of 'e'
select instr('Tech on the net', 'e', 1, 3) from dual -- would return 14; the third occurrence of 'e'
length
select length(NULL) from dual -- would return NULL.
select length('') from dual -- would return NULL.
select length('Tech on the Net') from dual -- would return 15.
select length('Tech on the Net ') from dual -- would return 16.
lower
select lower('Tech on the Net') from dual -- would return 'tech on the net'
select lower('GEORGE BURNS 123 ') from dual -- would return 'george burns 123
lpad function pads the left-side of a string with a specific set of characters.
select lpad('tech', 7) from dual -- would return ' tech'
select lpad('tech', 2) from dual -- would return 'te'
select lpad('tech', 8, '0') from dual-- would return '0000tech'
select lpad('tech on the net', 15, 'z') from dual --would return 'tech on the net'
select lpad('tech on the net', 16, 'z') from dual -- would return 'ztech on the net'
rpad function pads the right-side of a string with a specific set of characters.
select rpad('tech', 7) from dual -- would return 'tech '
select rpad('tech', 2) from dual -- would return 'te'
select rpad('tech', 8, '0') from dual -- would return 'tech0000'
select rpad('tech on the net', 15, 'z') from dual -- would return 'tech on the net'
select rpad('tech on the net', 16, 'z') from dual -- would return 'tech on the netz'
trim
select trim(' tech ') from dual -- would return 'tech'
select trim(' ' from ' tech ') from dual -- would return 'tech'
select trim(leading '0' from '000123') from dual -- would return '123'
select trim(trailing '1' from 'Tech1') from dual -- would return 'Tech'
select trim(both '1' from '123Tech111') from dual -- would return '23Tech'
ltrim
select ltrim(' tech') from dual -- would return 'tech'
select ltrim(' tech', ' ') from dual -- would return 'tech'
select ltrim('000123', '0') from dual -- would return '123'
select ltrim('123123Tech', '123') from dual -- would return 'Tech'
select ltrim('123123Tech123', '123') from dual -- would return 'Tech123'
select ltrim('xyxzyyyTech', 'xyz') from dual -- would return 'Tech'
select ltrim('6372Tech', '0123456789') from dual -- would return 'Tech'
rtrim
select rtrim('tech ') from dual -- would return 'tech'
select rtrim('tech ', ' ') from dual -- would return 'tech'
select rtrim('123000', '0') from dual -- would return '123'
select rtrim('Tech123123', '123') from dual -- would return 'Tech'
select rtrim('123Tech123', '123') from dual -- would return '123Tech'
select rtrim('Techxyxzyyy', 'xyz') from dual -- would return 'Tech'
select rtrim('Tech6372', '0123456789') from dual -- would return 'Tech'
replace function replaces a sequence of characters in a string with another set of characters.
select replace('123123tech', '123') from dual -- would return 'tech'
select replace('123tech123', '123') from dual -- would return 'tech'
select replace('222tech', '2', '3') from dual -- would return '333tech'
select replace('0000123', '0') from dual -- would return '123'
select replace('0000123', '0', ' ') from dual -- would return ' 123'
translate function replaces a sequence of characters in a string with another set of characters. . However, it replaces a single character at a time
select translate('1tech23', '123', '456') from dual -- would return '4tech56'
select translate('222tech', '2ec', '3it') from dual -- would return '333tith'
substr
select substr('This is a test', 6, 2) from dual -- would return 'is'
select substr('This is a test', 6) from dual -- would return 'is a test'
select substr('TechOnTheNet', 1, 4) from dual -- would return 'Tech'
select substr('TechOnTheNet', -3, 3) from dual -- would return 'Net'
select substr('TechOnTheNet', -6, 3) from dual -- would return 'The'
select substr('TechOnTheNet', -8, 2) from dual -- would return 'On'
CASE
select table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;
select table_name,
CASE
WHEN owner='SYS' THEN 'The owner is SYS'
WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;
TO_DSINTERVAL
SELECT SYSDATE, SYSDATE+ TO_DSINTERVAL('10 00:00:00') FROM DUAL
CONVERSION FUNCTIONS
CAST - function converts one datatype to another.
SELECT CAST(SYSDATE AS TIMESTAMP WITH LOCAL TIME ZONE) FROM dual;
SELECT CAST(SYSDATE AS TIMESTAMP WITH LOCAL TIME ZONE) FROM dual;
SELECT 1 + CAST(3.14 * 0.152 AS NUMBER(10,7)) FLOATING FROM dual;
from_tz function converts a TIMESTAMP value (given a TIME ZONE) to a TIMESTAMP WITH TIME ZONE value.
select from_tz(TIMESTAMP '2005-09-11 01:50:42', '5:00') from dual --11-Sep-05 01.50.42.000000 AM +05:00
select from_tz(TIMESTAMP '2005-09-11 01:50:42', '-7:00') from dual --11-Sep-05 01.50.42.000000 AM -07:00
hextoraw function converts a hexadecimal value into a raw value.
select hextoraw('45D') from dual -- would return '045D' as a raw value
select hextoraw('7E') from dual -- would return '7E' as a raw value
select hextoraw('9FA') from dual -- would return '7E' as a raw value
rawtohex
SELECT rawtohex('AB') FROM DUAL -- would return '4142' if run as an SQL function and 'AB' if run as a PLSQL function.
SELECT rawtohex('7E') FROM DUAL -- would return '3745' if run as an SQL function and '7E' as a PLSQL function.
to_char
SELECT to_char(sysdate, 'yyyy/mm/dd') FROM DUAL -- would return '2003/07/09'
SELECT to_char(sysdate, 'Month DD, YYYY') FROM DUAL -- would return 'July 09, 2003'
SELECT to_char(sysdate, 'FMMonth DD, YYYY') FROM DUAL -- would return 'July 9, 2003'
SELECT to_char(sysdate, 'MON DDth, YYYY') FROM DUAL -- would return 'JUL 09TH, 2003'
SELECT to_char(sysdate, 'FMMON DDth, YYYY') FROM DUAL -- would return 'JUL 9TH, 2003'
SELECT to_char(sysdate, 'FMMon ddth, YYYY') FROM DUAL -- would return 'Jul 9th, 2003'
to_date
SELECT to_date('2003/07/09', 'yyyy/mm/dd') FROM DUAL -- would return a date value of July 9, 2003.
SELECT to_date('070903', 'MMDDYY') FROM DUAL -- would return a date value of July 9, 2003.
SELECT to_date('20020315', 'yyyymmdd') FROM DUAL -- would return a date value of Mar 15, 2002.
LOB functions
select to_clob(nclob_column) from suppliers;
select to_nclob(lob_column) from suppliers;
select to_lob(long_column) from suppliers;
to_number function converts a string to a number.
select to_number('1210.76', '99999.99') from dual -- would return the number 1210.73
select to_number('546', '999') from dual -- would return the number 546
select to_number('23', '99') from dual -- would return the number 23
to_timestamp
select to_timestamp('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS') from dual
-- would return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.
select to_timestamp('2003/DEC/13 10:13:18', 'YYYY/MON/DD HH:MI:SS') from dual
-- would also return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.
to_timestamp_tz
select to_timestamp_tz('2003/12/13 10:13:18 -8:00', 'YYYY/MM/DD HH:MI:SS TZH:TZM') from dual
-- would return '13-DEC-03 10.13.18.000000000 AM -08:00' as a timestamp with time zone value.
select to_timestamp_tz('2003/DEC/13 10:13:18 -8:00', 'YYYY/MON/DD HH:MI:SS TZH:TZM') from dual
-- would also return '13-DEC-03 10.13.18.000000000 AM -08:00' as a timestamp with time zone value.
to_yminterval
select sysdate+to_yminterval('03-11') from dual -- would return 3 years 11 months as an INTERVAL YEAR TO MONTH type
select sysdate+to_yminterval('01-05') from dual -- would return 1 year 5 months as an INTERVAL YEAR TO MONTH type
select sysdate+to_yminterval('00-01') from dual -- would return 0 years 1 month as an INTERVAL YEAR TO MONTH type
SELECT CAST(SYSDATE AS TIMESTAMP WITH LOCAL TIME ZONE) FROM dual;
SELECT CAST(SYSDATE AS TIMESTAMP WITH LOCAL TIME ZONE) FROM dual;
SELECT 1 + CAST(3.14 * 0.152 AS NUMBER(10,7)) FLOATING FROM dual;
from_tz function converts a TIMESTAMP value (given a TIME ZONE) to a TIMESTAMP WITH TIME ZONE value.
select from_tz(TIMESTAMP '2005-09-11 01:50:42', '5:00') from dual --11-Sep-05 01.50.42.000000 AM +05:00
select from_tz(TIMESTAMP '2005-09-11 01:50:42', '-7:00') from dual --11-Sep-05 01.50.42.000000 AM -07:00
hextoraw function converts a hexadecimal value into a raw value.
select hextoraw('45D') from dual -- would return '045D' as a raw value
select hextoraw('7E') from dual -- would return '7E' as a raw value
select hextoraw('9FA') from dual -- would return '7E' as a raw value
rawtohex
SELECT rawtohex('AB') FROM DUAL -- would return '4142' if run as an SQL function and 'AB' if run as a PLSQL function.
SELECT rawtohex('7E') FROM DUAL -- would return '3745' if run as an SQL function and '7E' as a PLSQL function.
to_char
SELECT to_char(sysdate, 'yyyy/mm/dd') FROM DUAL -- would return '2003/07/09'
SELECT to_char(sysdate, 'Month DD, YYYY') FROM DUAL -- would return 'July 09, 2003'
SELECT to_char(sysdate, 'FMMonth DD, YYYY') FROM DUAL -- would return 'July 9, 2003'
SELECT to_char(sysdate, 'MON DDth, YYYY') FROM DUAL -- would return 'JUL 09TH, 2003'
SELECT to_char(sysdate, 'FMMON DDth, YYYY') FROM DUAL -- would return 'JUL 9TH, 2003'
SELECT to_char(sysdate, 'FMMon ddth, YYYY') FROM DUAL -- would return 'Jul 9th, 2003'
to_date
SELECT to_date('2003/07/09', 'yyyy/mm/dd') FROM DUAL -- would return a date value of July 9, 2003.
SELECT to_date('070903', 'MMDDYY') FROM DUAL -- would return a date value of July 9, 2003.
SELECT to_date('20020315', 'yyyymmdd') FROM DUAL -- would return a date value of Mar 15, 2002.
LOB functions
select to_clob(nclob_column) from suppliers;
select to_nclob(lob_column) from suppliers;
select to_lob(long_column) from suppliers;
to_number function converts a string to a number.
select to_number('1210.76', '99999.99') from dual -- would return the number 1210.73
select to_number('546', '999') from dual -- would return the number 546
select to_number('23', '99') from dual -- would return the number 23
to_timestamp
select to_timestamp('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS') from dual
-- would return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.
select to_timestamp('2003/DEC/13 10:13:18', 'YYYY/MON/DD HH:MI:SS') from dual
-- would also return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.
to_timestamp_tz
select to_timestamp_tz('2003/12/13 10:13:18 -8:00', 'YYYY/MM/DD HH:MI:SS TZH:TZM') from dual
-- would return '13-DEC-03 10.13.18.000000000 AM -08:00' as a timestamp with time zone value.
select to_timestamp_tz('2003/DEC/13 10:13:18 -8:00', 'YYYY/MON/DD HH:MI:SS TZH:TZM') from dual
-- would also return '13-DEC-03 10.13.18.000000000 AM -08:00' as a timestamp with time zone value.
to_yminterval
select sysdate+to_yminterval('03-11') from dual -- would return 3 years 11 months as an INTERVAL YEAR TO MONTH type
select sysdate+to_yminterval('01-05') from dual -- would return 1 year 5 months as an INTERVAL YEAR TO MONTH type
select sysdate+to_yminterval('00-01') from dual -- would return 0 years 1 month as an INTERVAL YEAR TO MONTH type
Subscribe to:
Posts (Atom)