SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 13 11:39:26 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> -- DECODE Altered WHERE Clause
SQL> CREATE TABLE test (
  2  pubdate  DATE,
  3  compdate DATE,
  4  valuecol NUMBER(5));

Table created.

SQL>
SQL> INSERT INTO test VALUES (TRUNC(SYSDATE), TRUNC(SYSDATE+300), 1);

1 row created.

SQL> INSERT INTO test VALUES (TRUNC(SYSDATE-300), TRUNC(SYSDATE), 9);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT * FROM test;

PUBDATE   COMPDATE    VALUECOL
--------- --------- ----------
13-SEP-07 09-JUL-08          1
17-NOV-06 13-SEP-07          9

SQL>
SQL> CREATE OR REPLACE PROCEDURE testproc (
  2  StartDate DATE, EndDate DATE, DateType IN VARCHAR2) IS
  3
  4   i PLS_INTEGER;
  5  BEGIN
  6    SELECT valuecol
  7    INTO i
  8    FROM test
  9    WHERE DECODE(DateType, 'AA',compdate, 'BB', pubdate, compdate) <= EndDate
 10    AND DECODE(DateType, 'AA', compdate, 'BB', pubdate, compdate) >= StartDate;
 11
 12    dbms_output.put_line(TO_CHAR(i));
 13  END testproc;
 14  /

Procedure created.

SQL>
SQL> set serveroutput on
SQL>
SQL> exec testproc(TRUNC(SYSDATE), TRUNC(SYSDATE), 'BB');
1

PL/SQL procedure successfully completed.

SQL>

Download Code