Oracle
query to create month of a Year
select to_char(TO_DATE(level,'MM'),'FMMON') MONTH from DUAL
connect by level <=12;
MONTH
|
JAN
|
FEB
|
MAR
|
APR
|
MAY
|
JUN
|
JUL
|
AUG
|
SEP
|
OCT
|
NOV
|
DEC
|
Oracle
query to create auto numbers
select level from DUAL connect by level <=12;
Num
|
1
|
2
|
3
|
4
|
5
|
6
|
7
|
8
|
9
|
10
|
11
|
12
|
Oracle
query to create next 12 years from current year
SELECT EXTRACT(YEAR FROM SYSDATE) + 1 - LEVEL AS YEARS
FROM DUAL
connect by level <= 12
ORDER BY YEARS;
YEARS
|
2004
|
2005
|
2006
|
2007
|
2008
|
2009
|
2010
|
2011
|
2012
|
2013
|
2014
|
2015
|
Oracle
query to create all combination of dates for 30 Years starting from 1st
Jan 2000(for Date Dimension)
SELECT to_number(TO_CHAR(CurrDate, 'YYYYMMDD')) AS DATE_ID,
CurrDate AS
DATE_VALUE,
to_number(TO_CHAR(CurrDate,'D')) AS DAY_OF_WEEK_NUM,
to_number(TO_CHAR(CurrDate,'DD')) AS DAY_OF_MONTH_NUM,
to_number(TO_CHAR(CurrDate,'DDD')) AS DAY_OF_YEAR_NUM,
TO_CHAR(CurrDate,'Day') AS DAY_NAME, -- Also check options DY
to_number(TO_CHAR(CurrDate,'WW')) AS WEEK_OF_YEAR_NUM, -- Also check
options W, WW,IW
TO_CHAR(CurrDate,'Month') AS MONTH_NAME,
to_number(TO_CHAR(CurrDate,'MM')) AS MONTH_NUM,
to_number((TO_CHAR(CurrDate,'Q'))) AS QUARTER_NUM,
to_number(TO_CHAR(CurrDate,'YYYY')) AS YEAR_NUM
FROM
(SELECT level n,
TO_DATE('31/12/1999','DD/MM/YYYY')
+ NUMTODSINTERVAL(level,'day') CurrDate
FROM dual
CONNECT BY level
<= (30*365)
) --generate for
next 30 years. i.e for 2000-2030
ORDER BY 1;
ORACLE/PLSQL: SELECT
FOR UPDATE STATEMENT
This Oracle tutorial explains how to use the Oracle/PLSQL SELECT FOR UPDATE statement with syntax and examples.
DESCRIPTION
The SELECT
FOR UPDATE statement
allows you to lock the records in the cursor result set. You are not required
to make changes to the records in order to use this statement. The record locks
are released when the next commit or rollback statement is issued.
SYNTAX
The syntax for the SELECT
FOR UPDATE statement
in Oracle/PLSQL is:
CURSOR cursor_name
IS
select_statement
FOR UPDATE [OF column_list] [NOWAIT];
Parameters or Arguments
cursor_name is the name of the cursor.
select_statement is a SELECT statement that will populate your cursor result set.
column_list are the columns in the cursor result set that you wish to
update.
NOWAIT is optional. The cursor does not wait for resources.
EXAMPLE
For example, you could use the SELECT FOR UPDATE statement as
follows:
CURSOR c1
IS
SELECT course_number, instructor
FROM courses_tbl
FOR UPDATE OF instructor;
If you plan on updating or deleting records that have been
referenced by a SELECT FOR UPDATE statement, you can use theWHERE CURRENT OF statement.
ORACLE/PLSQL: WHERE
CURRENT OF STATEMENT
This Oracle tutorial explains how to
use the Oracle/PLSQL WHERE CURRENT OF statement with syntax
and examples.
DESCRIPTION
If you plan on updating or deleting
records that have been referenced by a SELECT FOR UPDATE statement, you can use theWHERE
CURRENT OF statement.
SYNTAX
The syntax for the WHERE
CURRENT OF statement in Oracle/PLSQL is either:
UPDATE table_name
SET set_clause
WHERE CURRENT OF cursor_name;
OR
DELETE FROM table_name
WHERE CURRENT OF cursor_name;
NOTE
- The
WHERE CURRENT OF statement allows you to update or delete the record that
was last fetched by the cursor.
EXAMPLE
Updating using the WHERE CURRENT OF Statement
Here is an example where we are
updating records using the WHERE CURRENT OF Statement:
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course_number
FROM courses_tbl
WHERE course_name = name_in
FOR UPDATE of instructor;
BEGIN
OPEN c1;
FETCH c1 INTO cnumber;
if c1%notfound then
cnumber := 9999;
else
UPDATE courses_tbl
SET instructor = 'SMITH'
WHERE CURRENT OF c1;
COMMIT;
end if;
CLOSE c1;
RETURN cnumber;
END;
Deleting using the WHERE CURRENT OF Statement
Here is an example where we are
deleting records using the WHERE CURRENT OF Statement:
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in
FOR UPDATE of instructor;
BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
else
DELETE FROM courses_tbl
WHERE CURRENT OF c1;
COMMIT;
end if;
close c1;
RETURN cnumber;
END;
No comments:
Post a Comment