System functions
SYS_GUID()
SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms, the generated identifier consists of a host identifier, a process or thread identifier of the process or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread.
Ref: http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/functions153.htm
Ref: http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/functions153.htm
SYSDATE
SYSDATE returns the current date and time set for the operating system on which the database resides.
Ref: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions172.htm
Ref: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions172.htm
Example:
insert into TABLE2 (oid, created) VALUES(sys_guid(), sysdate)
To_Date function
In Oracle/PLSQL, the to_date function converts a string to a date.The syntax for the to_date function is:
to_date( string1, [ format_mask ], [ nls_language ] )
string1 is the string that will be converted to a date.
format_mask is optional. This is the format that will be used to convert string1 to a date.
nls_language is optional. This is the nls language used to convert string1 to a date.
Example:
to_date('1.9.2012','dd.mm.yyyy')
to_date('1.9.2012 23:59:59','dd.mm.yyyy HH24:mi:ss')
Between condition
The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates
SELECT *
FROM orders
WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');
is equal to
WHERE order_date >= to_date('2003/01/01', 'yyyy/mm/dd')
AND order_date <= to_date('2003/12/31','yyyy/mm/dd');
Decode function
In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.The syntax for the decode function is:
decode( expression , search , result [, search , result]... [, default] )
expression is the value to compare.
search is the value that is compared against expression.
result is the value returned, if expression is equal to search.
default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).
For Example:
You could use the decode function in an SQL statement as follows:
SELECT supplier_name,
decode(supplier_id, 10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result
FROM suppliers;
The above decode statement is equivalent to the following IF-THEN-ELSE statement:
IF supplier_id = 10000 THEN
result := 'IBM';
ELSIF supplier_id = 10001 THEN
result := 'Microsoft';
ELSIF supplier_id = 10002 THEN
result := 'Hewlett Packard';
ELSE
result := 'Gateway';
END IF;
The decode function will compare each supplier_id value, one by one.
http://www.techonthenet.com/oracle/functions/decode.php
NVL Function
In Oracle/PLSQL, the NVL function lets you substitute a value when a null value is encountered.
The syntax for the NVL function is:
nvl( string1, replace_with )
string1 is the string to test for a null value.
replace_with is the value returned if string1 is null.
Example:
select nvl(commission, 0)
from sales;
This SQL statement would return 0 if the commission field contained a null value. Otherwise, it would return the commission field.
Pseudocolumns (sequence)
A sequence is a schema object that can generate unique sequential values. These values are often used for primary and unique keys. You can refer to sequence values in SQL statements with these pseudocolumns:
CURRVAL |
returns the current value of a sequence.
|
NEXTVAL |
increments the sequence and returns the next value.
|
Insert into table that has not-null sequence:
INSERT into terminal (terminal_id, terminal_sequence_number)
VALUES('someId', terminal_seq.NEXTVAL);
Get sequence current value:
SELECT empseq.currval FROM DUAL;
Get sequence current value:
SELECT empseq.currval FROM DUAL;
Ref
MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses ROWNUM.
E.g. Return 2 results:
E.g. Return 2 results:
SELECT * FROM table_name WHERE ROWNUM <= 2;
Adding ORDER BY clause will not return proper data!
Solution is to use
SELECT * FROM ( your_query_here ) WHERE ROWNUM <= N.
Adding ORDER BY clause will not return proper data!
Solution is to use
SELECT * FROM ( your_query_here ) WHERE ROWNUM <= N.
Ref
No comments:
Post a Comment