Nothing special. This function is too easy to be forgeten, for so many times. Just record here:
This function returns either -1, 0 or 1.
http://www.techonthenet.com/oracle/functions/sign.php
Showing posts with label PL/SQL. Show all posts
Showing posts with label PL/SQL. Show all posts
Sequential and Modular Programming
Modular Programming came out as a newer technique. In terms sequence to execute the programing blocks, or called modules, modular programming also means the run time automatically searching where the modules are. Unfortunately, different language sets it differently, some are better and some are not so good. Object-oriented languages are all modular programming and they are the best in this regarding.
PL/SQL
PL/SQL is sequential language and its modules searching function is very bad. It means you would have to put functions/procedures in right order within the package to enable the sequential process. Of course, for packages itself it has no problem since packages are individual files.
Java
Java is a fully object-oriented language, though within the class, it is still the sequential. However, methods within the class are also fully modularized. It means the position of methods plays no role, and can be found by run time automatically.
JavaScript
Although JavaScript is classified as object-oriented language, only functions in root level in the file are modularized. Within the function, while it is sequential, nested functions are not fully modularized. It means within the function, your nested functions need to be placed in right order before it can be called. Even in root level, variablized functions still need to be treated carefully with the order. In particular, some built-in functions are very sensitive to the sequence, such as setTimeout() and setInterval().
http://en.wikipedia.org/wiki/Modular_programming
http://en.wikipedia.org/wiki/Javascript
http://koncordpartners.blogspot.com/2010/04/function-declaration-in-javascript.html
PL/SQL
PL/SQL is sequential language and its modules searching function is very bad. It means you would have to put functions/procedures in right order within the package to enable the sequential process. Of course, for packages itself it has no problem since packages are individual files.
Java
Java is a fully object-oriented language, though within the class, it is still the sequential. However, methods within the class are also fully modularized. It means the position of methods plays no role, and can be found by run time automatically.
JavaScript
Although JavaScript is classified as object-oriented language, only functions in root level in the file are modularized. Within the function, while it is sequential, nested functions are not fully modularized. It means within the function, your nested functions need to be placed in right order before it can be called. Even in root level, variablized functions still need to be treated carefully with the order. In particular, some built-in functions are very sensitive to the sequence, such as setTimeout() and setInterval().
http://en.wikipedia.org/wiki/Modular_programming
http://en.wikipedia.org/wiki/Javascript
http://koncordpartners.blogspot.com/2010/04/function-declaration-in-javascript.html
Copy Only the Structure of A Table
This old little trick had been asked for so many times. So, it might be a good itea to post it on the blog.
In Oracle:
CREATE TABLE NEW_TAB AS
SELECT *
FROM OLD_TAB
WHERE 0 = -1
In SQL Server:
SELECT *
INTO NEW_TAB
FROM OLD_TAB
WHERE 0 = -1
The key point is WHERE condition. Because this condition is never to be met, so there is no records actually being selected. Someone might like WHERE 1 = 2, however, 1 or 2 can be referred to column one and column two. If by chance the value in column one just equals to column two, haha, you will get some data.
In Oracle:
CREATE TABLE NEW_TAB AS
SELECT *
FROM OLD_TAB
WHERE 0 = -1
In SQL Server:
SELECT *
INTO NEW_TAB
FROM OLD_TAB
WHERE 0 = -1
The key point is WHERE condition. Because this condition is never to be met, so there is no records actually being selected. Someone might like WHERE 1 = 2, however, 1 or 2 can be referred to column one and column two. If by chance the value in column one just equals to column two, haha, you will get some data.
Quick Sorting Method in SQL Query for Disorderly Sorting Criterion
ORDER BY can be used for orderly sorting criterion only. For disorderly sorting criterion, such as Car, Ship, Airplane, ORDER BY could not accomplish the task. Here is a quick method to use ORDER BY as usually.
Step One: Add order number in front of sorting field by using concatenation function.
Step Two: Using ORDER BY as usually.
Step Three: Using substring function to cut the first character.
Example in T-SQL:
SELECT SUBSTRING(newSort, 2, 8)
, Other_Fields
FROM
(
SELECT CASE WHEN Sort_Field = ‘Car’ THEN ‘1Car’
WHEN Sort_Field = ‘Ship’ THEN ‘2Ship’
WHEN Sort_Field = ‘Airplane’ THEN ‘3Airplane’ END AS newSort
, Other_Fields
FROM Tab
) AS innerQuery
ORDER BY newSort
This method can be used in T-SQL, PL/SQL, etc. For T-SQL, please pay special notice to this: ORDER BY cannot be used for any inner query, it must be placed at outer most query. The solution to overcome this weakness is to either abandon this method or save inner query result to temporary table.
Step One: Add order number in front of sorting field by using concatenation function.
Step Two: Using ORDER BY as usually.
Step Three: Using substring function to cut the first character.
Example in T-SQL:
SELECT SUBSTRING(newSort, 2, 8)
, Other_Fields
FROM
(
SELECT CASE WHEN Sort_Field = ‘Car’ THEN ‘1Car’
WHEN Sort_Field = ‘Ship’ THEN ‘2Ship’
WHEN Sort_Field = ‘Airplane’ THEN ‘3Airplane’ END AS newSort
, Other_Fields
FROM Tab
) AS innerQuery
ORDER BY newSort
This method can be used in T-SQL, PL/SQL, etc. For T-SQL, please pay special notice to this: ORDER BY cannot be used for any inner query, it must be placed at outer most query. The solution to overcome this weakness is to either abandon this method or save inner query result to temporary table.
PIVOT – Turning Rows into Columns
Here we exam various PIVOT functions/methods in MS Access, Oracle and SQL Server.
MS Access:
TRANSFORM does not work properly because it would generate many rows with a lot of zeros. Let us call it seggragation. However, covring it by a SUM() GROUP BY statement will do.
SELECT with IIF() does work perfect. Example: http://koncordpartners.blogspot.com/2009/08/practical-case-of-transpose-query.html
Oracle:
SELECT PIVOT does not work properly because of seggragation. However, covring it by a SUM() GROUP BY statement will do.
SELECT with CASE does work perfect. Example: http://koncordpartners.blogspot.com/2009/08/practical-case-of-transpose-query.html
OVER PARTITION BY does work fine, but not recommended because lack of flexibility. Example: http://baurdotnet.wordpress.com/2009/08/26/select/
SQL Server:
PIVOT does work fine, but strongly not recommended for two reasons: 1, Aggragate function whtin PIVOT can not be recalculated according to your will. 2, T-SQL does not allow ORDER BY working within the inner query, which does cause a lot of difficulties for layout arrangement.
MS Access:
TRANSFORM does not work properly because it would generate many rows with a lot of zeros. Let us call it seggragation. However, covring it by a SUM() GROUP BY statement will do.
SELECT with IIF() does work perfect. Example: http://koncordpartners.blogspot.com/2009/08/practical-case-of-transpose-query.html
Oracle:
SELECT PIVOT does not work properly because of seggragation. However, covring it by a SUM() GROUP BY statement will do.
SELECT with CASE does work perfect. Example: http://koncordpartners.blogspot.com/2009/08/practical-case-of-transpose-query.html
OVER PARTITION BY does work fine, but not recommended because lack of flexibility. Example: http://baurdotnet.wordpress.com/2009/08/26/select/
SQL Server:
PIVOT does work fine, but strongly not recommended for two reasons: 1, Aggragate function whtin PIVOT can not be recalculated according to your will. 2, T-SQL does not allow ORDER BY working within the inner query, which does cause a lot of difficulties for layout arrangement.
Temporarily Disable and Re-enable the Constraints in Oracle
SQL command script files to disable and enable all constraints:
Disable:
set feedback off
set verify off
set echo off
prompt Finding constraints to disable...
set termout off
set pages 80
set heading off
set linesize 120
spool tmp_disable.sql
select 'spool igen_disable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' DISABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@tmp_disable.sql;
exit
/
Enable:
set feedback off
set verify off
set wrap off
set echo off
prompt Finding constraints to enable...
set termout off
set lines 120
set heading off
spool tmp_enable.sql
select 'spool igen_enable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' ENABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
/
select 'exit;' from dual;
set termout on
prompt Enabling constraints now...
set termout off
@tmp_enable;
!rm -i tmp_enable.sql;
exit
/
Scripts in PL/SQL to disable and enable all constraints:
Disable:
BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE c.status = 'ENABLED'
ORDER BY c.constraint_type DESC
)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || i.owner || '.' || i.table_name || ' disable constraint ' || i.constraint_name);
END LOOP;
END;
/
Enable:
BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE c.status = 'DISABLED'
ORDER BY c.constraint_type
)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
END LOOP;
END;
/
Disable:
set feedback off
set verify off
set echo off
prompt Finding constraints to disable...
set termout off
set pages 80
set heading off
set linesize 120
spool tmp_disable.sql
select 'spool igen_disable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' DISABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@tmp_disable.sql;
exit
/
Enable:
set feedback off
set verify off
set wrap off
set echo off
prompt Finding constraints to enable...
set termout off
set lines 120
set heading off
spool tmp_enable.sql
select 'spool igen_enable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' ENABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
/
select 'exit;' from dual;
set termout on
prompt Enabling constraints now...
set termout off
@tmp_enable;
!rm -i tmp_enable.sql;
exit
/
Scripts in PL/SQL to disable and enable all constraints:
Disable:
BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE c.status = 'ENABLED'
ORDER BY c.constraint_type DESC
)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || i.owner || '.' || i.table_name || ' disable constraint ' || i.constraint_name);
END LOOP;
END;
/
Enable:
BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE c.status = 'DISABLED'
ORDER BY c.constraint_type
)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
END LOOP;
END;
/
A Practical Case of Transpose Query
Let us say a pension fund wants to generate a summary consists of total contributions had been made so far for each member, by their employers. The fund does not care about exact contribution made by each employers for each member. However, the fund wants to know the three employers paid the contributions most for each member. As the result, the summary table should have 5 columns: Member_Id (unique), Total_Contributions, Employer_1, Employer_2, Employer_3. If a member has less than three employers, leave it blank. Each member should have occupied one row only.
Simple SQL query of SELECT GROUP BY won't be able to accomplish this task, since it is a transpose requirement, namely, turning the row into column. However, CASE or DECODE in PL/SQL will do. The equivalent of CASE in Microsoft Office Access is IIF. Alternatively, TRANSFORM in Access and SELECT PIVOT in PL/SQL can be used. By use CASE or DECODE, it is possible to avoid the complex programming. There are five steps:
1. To get dataset first which should consist of all necessary information. Let us use Dataset_All to describe it:
SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contribution
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id
ORDER BY is important since it would make sure the ROWNUM is in align with the Member_Id.
2. Get a dataset which consists of the Member_Id and their last ROWNUM in Dataset_All. Let us name it Dataset_Uni:
SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id
Using GROUP BY to get largest ROWNUM if same Member_Id having multiple rows.
3. To flag each employer according their rank, start from 1. Let us name it Dataset_Rank:
SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id
4. Transpose process. We also use the numerical feather of Employer_Id which was generated from sequence:
SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM Dataset_Rank
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id
Put everything together:
WITH Dataset_All AS
( SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contributio
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id
)
SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM
( SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM
( SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id
) Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id
)
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id
Done.
Simple SQL query of SELECT GROUP BY won't be able to accomplish this task, since it is a transpose requirement, namely, turning the row into column. However, CASE or DECODE in PL/SQL will do. The equivalent of CASE in Microsoft Office Access is IIF. Alternatively, TRANSFORM in Access and SELECT PIVOT in PL/SQL can be used. By use CASE or DECODE, it is possible to avoid the complex programming. There are five steps:
1. To get dataset first which should consist of all necessary information. Let us use Dataset_All to describe it:
SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contribution
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id
ORDER BY is important since it would make sure the ROWNUM is in align with the Member_Id.
2. Get a dataset which consists of the Member_Id and their last ROWNUM in Dataset_All. Let us name it Dataset_Uni:
SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id
Using GROUP BY to get largest ROWNUM if same Member_Id having multiple rows.
3. To flag each employer according their rank, start from 1. Let us name it Dataset_Rank:
SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id
4. Transpose process. We also use the numerical feather of Employer_Id which was generated from sequence:
SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM Dataset_Rank
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id
Put everything together:
WITH Dataset_All AS
( SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contributio
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id
)
SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM
( SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM
( SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id
) Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id
)
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id
Done.
Detect a Leap Year with PL/SQL
We have seen some very complicated ways to detect leap year. Follow functions are probably the most elegant method we can find:
function DATE_LEAP_YEAR_IS
--------------------------------------------------------------------------------
-- This function is to return if a given year is leap year or not.
-- This is 1 of 2 overloaded functions.
-- Input: Parameter 1: The given year in number.
-- Output: Return: The result in single varchar2, 'Y' is leap year, 'N' is not.
-- Authers: Ken Stevens, Lee Howe, Jane Stone & Howard Stone
--------------------------------------------------------------------------------
( nYr in number
) return varchar2
as v_day varchar2(2);
begin
v_day := to_char(last_day(to_date(to_char(nYr)||'/02/01', 'YYYY/MM/DD')), 'DD');
if v_day = '29' -- if v_day = 29 then it must be a leap year, return TRUE.
then return 'Y';
else return 'N';
end if;
end DATE_LEAP_YEAR_IS;
function DATE_LEAP_YEAR_IS
--------------------------------------------------------------------------------
-- This function is to return if a given year is leap year or not.
-- This is 1 of 2 overloaded functions.
-- Input: Parameter 1: The given year in date.
-- Output: Return: The result in single varchar2, 'Y' is leap year, 'N' is not.
-- Authers: Ken Stevens, Lee Howe, Jane Stone & Howard Stone
--------------------------------------------------------------------------------
( dDate in date
) return varchar2
as v_day varchar2(2);
begin
v_day := to_char(last_day(to_date(to_char(extract(year from dDate))||'/02/01', 'YYYY/MM/DD')), 'DD');
if v_day = '29' -- if v_day = 29 then it must be a leap year, return TRUE.
then return 'Y';
else return 'N';
end if;
end DATE_LEAP_YEAR_IS;
Following is Don Burleson’s scripts:
create or replace function IS_LEAP_YEAR (nYr in number) return boolean is
v_day varchar2(2);
begin
select to_char(last_day(to_date( '01-FEB-'|| to_char(nYr), 'DD-MON-YYYY')), 'DD') into v_day from dual;
if v_day = '29' then -- if v_day = 29 then it must be a leap year, return TRUE
return TRUE;
else
return FALSE; -- otherwise year is not a leap year, return false
end if;
end;
http://www.dba-oracle.com/t_detect_leap_year_function.htm
http://www.dba-oracle.com/oracle_news/2005_8_23_function_detect_leap_year.htm
function DATE_LEAP_YEAR_IS
--------------------------------------------------------------------------------
-- This function is to return if a given year is leap year or not.
-- This is 1 of 2 overloaded functions.
-- Input: Parameter 1: The given year in number.
-- Output: Return: The result in single varchar2, 'Y' is leap year, 'N' is not.
-- Authers: Ken Stevens, Lee Howe, Jane Stone & Howard Stone
--------------------------------------------------------------------------------
( nYr in number
) return varchar2
as v_day varchar2(2);
begin
v_day := to_char(last_day(to_date(to_char(nYr)||'/02/01', 'YYYY/MM/DD')), 'DD');
if v_day = '29' -- if v_day = 29 then it must be a leap year, return TRUE.
then return 'Y';
else return 'N';
end if;
end DATE_LEAP_YEAR_IS;
function DATE_LEAP_YEAR_IS
--------------------------------------------------------------------------------
-- This function is to return if a given year is leap year or not.
-- This is 1 of 2 overloaded functions.
-- Input: Parameter 1: The given year in date.
-- Output: Return: The result in single varchar2, 'Y' is leap year, 'N' is not.
-- Authers: Ken Stevens, Lee Howe, Jane Stone & Howard Stone
--------------------------------------------------------------------------------
( dDate in date
) return varchar2
as v_day varchar2(2);
begin
v_day := to_char(last_day(to_date(to_char(extract(year from dDate))||'/02/01', 'YYYY/MM/DD')), 'DD');
if v_day = '29' -- if v_day = 29 then it must be a leap year, return TRUE.
then return 'Y';
else return 'N';
end if;
end DATE_LEAP_YEAR_IS;
Following is Don Burleson’s scripts:
create or replace function IS_LEAP_YEAR (nYr in number) return boolean is
v_day varchar2(2);
begin
select to_char(last_day(to_date( '01-FEB-'|| to_char(nYr), 'DD-MON-YYYY')), 'DD') into v_day from dual;
if v_day = '29' then -- if v_day = 29 then it must be a leap year, return TRUE
return TRUE;
else
return FALSE; -- otherwise year is not a leap year, return false
end if;
end;
http://www.dba-oracle.com/t_detect_leap_year_function.htm
http://www.dba-oracle.com/oracle_news/2005_8_23_function_detect_leap_year.htm
Subscribe to:
Posts (Atom)
Posts
