select birthday from your_tab where birthday >= trunc(sysdate) and birthday <=trunc(sysdate+15); 此处的birthday为date型数据,如果不是date型数据,请作相应转化。
SQL> --建表 SQL> create table birth(id number,birthday date);表已创建。SQL> --插入数据 SQL> insert into BIRTH (ID, BIRTHDAY) 2 values (1, to_date('01-01-1983', 'dd-mm-yyyy'));已创建 1 行。SQL> insert into BIRTH (ID, BIRTHDAY) 2 values (2, to_date('25-12-1978', 'dd-mm-yyyy'));已创建 1 行。SQL> insert into BIRTH (ID, BIRTHDAY) 2 values (3, to_date('20-02-1983', 'dd-mm-yyyy'));已创建 1 行。SQL> insert into BIRTH (ID, BIRTHDAY) 2 values (4, to_date('05-08-1973', 'dd-mm-yyyy'));已创建 1 行。SQL> insert into BIRTH (ID, BIRTHDAY) 2 values (5, to_date('16-01-1983', 'dd-mm-yyyy'));已创建 1 行。SQL> commit;提交完成。SQL> --当前日期 SQL> select sysdate from dual;SYSDATE ------------------- 2010-12-24 22:40:42SQL> --查询15天内生日 SQL> SELECT * 2 FROM birth t 3 WHERE to_date(to_char(t.birthday, 'mmdd'), 'mmdd') BETWEEN SYSDATE AND 4 trunc(SYSDATE) + 15 5 OR add_months(to_date(to_char(t.birthday, 'mmdd'), 'mmdd'), 12) BETWEEN 6 SYSDATE AND trunc(SYSDATE) + 15; ID BIRTHDAY ---------- ------------------- 1 1983-01-01 00:00:00 2 1978-12-25 00:00:00SQL>
1 declare 2 qtime date:=sysdate; 3 type refempcur is ref cursor; 4 empcur refempcur; 5 emprow emp%rowtype; 6 begin 7 qtime:=to_date(nvl(to_date('&birthday','yyyy-mm-dd'),sysdate)); 8 if qtime=sysdate then 9 open empcur for select * from emp where hiredate between sysdate and sysdate+15; 10 else 11 open empcur for select * from emp where hiredate between qtime and qtime+15; 12 end if; 13 loop 14 fetch empcur into emprow; 15 exit when empcur%notfound; 16 dbms_output.put_line('happy birthday'||emprow.empno||' '||emprow.ename); 17 end loop; 18 close empcur; 19* end; SQL> / 输入 birthday 的值: 原值 7: qtime:=to_date(nvl(to_date('&birthday','yyyy-mm-dd'),sysdate)); 新值 7: qtime:=to_date(nvl(to_date('','yyyy-mm-dd'),sysdate)); happy birthday7369 SMITHPL/SQL 过程已成功完成。SQL> / 输入 birthday 的值: 2003-2-20 原值 7: qtime:=to_date(nvl(to_date('&birthday','yyyy-mm-dd'),sysdate)); 新值 7: qtime:=to_date(nvl(to_date('2003-2-20','yyyy-mm-dd'),sysdate)); happy birthday7499 ALLEN happy birthday7521 WARDPL/SQL 过程已成功完成。
忘了把表贴出来 select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 2010-04-17 800 20 7499 ALLEN SALESMAN 7698 2003-02-20 1600 900 30 7521 WARD SALESMAN 7698 2003-02-22 1250 500 30 7566 JONES MANAGER 7839 2003-04-02 2975 20 7654 MARTIN SALESMAN 7698 2003-09-28 1250 1400 30 7698 BLAKE MANAGER 7839 2003-05-01 2850 30 7782 CLARK MANAGER 7839 2003-06-09 2450 10 7788 SCOTT ANALYST 7566 2009-04-19 3000 20 7839 KING PRESIDENT 2003-11-17 5000 10 7844 TURNER SALESMAN 7698 2003-09-08 1500 0 30 7876 ADAMS CLERK 7788 2009-05-23 1100 20 7900 JAMES CLERK 7698 2003-12-03 950 30 7902 FORD ANALYST 7566 2003-12-03 3000 20 7934 MILLER CLERK 7782 2004-01-23 1300 10
此处的birthday为date型数据,如果不是date型数据,请作相应转化。
SQL> create table birth(id number,birthday date);表已创建。SQL> --插入数据
SQL> insert into BIRTH (ID, BIRTHDAY)
2 values (1, to_date('01-01-1983', 'dd-mm-yyyy'));已创建 1 行。SQL> insert into BIRTH (ID, BIRTHDAY)
2 values (2, to_date('25-12-1978', 'dd-mm-yyyy'));已创建 1 行。SQL> insert into BIRTH (ID, BIRTHDAY)
2 values (3, to_date('20-02-1983', 'dd-mm-yyyy'));已创建 1 行。SQL> insert into BIRTH (ID, BIRTHDAY)
2 values (4, to_date('05-08-1973', 'dd-mm-yyyy'));已创建 1 行。SQL> insert into BIRTH (ID, BIRTHDAY)
2 values (5, to_date('16-01-1983', 'dd-mm-yyyy'));已创建 1 行。SQL> commit;提交完成。SQL> --当前日期
SQL> select sysdate from dual;SYSDATE
-------------------
2010-12-24 22:40:42SQL> --查询15天内生日
SQL> SELECT *
2 FROM birth t
3 WHERE to_date(to_char(t.birthday, 'mmdd'), 'mmdd') BETWEEN SYSDATE AND
4 trunc(SYSDATE) + 15
5 OR add_months(to_date(to_char(t.birthday, 'mmdd'), 'mmdd'), 12) BETWEEN
6 SYSDATE AND trunc(SYSDATE) + 15; ID BIRTHDAY
---------- -------------------
1 1983-01-01 00:00:00
2 1978-12-25 00:00:00SQL>
2 qtime date:=sysdate;
3 type refempcur is ref cursor;
4 empcur refempcur;
5 emprow emp%rowtype;
6 begin
7 qtime:=to_date(nvl(to_date('&birthday','yyyy-mm-dd'),sysdate));
8 if qtime=sysdate then
9 open empcur for select * from emp where hiredate between sysdate and sysdate+15;
10 else
11 open empcur for select * from emp where hiredate between qtime and qtime+15;
12 end if;
13 loop
14 fetch empcur into emprow;
15 exit when empcur%notfound;
16 dbms_output.put_line('happy birthday'||emprow.empno||' '||emprow.ename);
17 end loop;
18 close empcur;
19* end;
SQL> /
输入 birthday 的值:
原值 7: qtime:=to_date(nvl(to_date('&birthday','yyyy-mm-dd'),sysdate));
新值 7: qtime:=to_date(nvl(to_date('','yyyy-mm-dd'),sysdate));
happy birthday7369 SMITHPL/SQL 过程已成功完成。SQL> /
输入 birthday 的值: 2003-2-20
原值 7: qtime:=to_date(nvl(to_date('&birthday','yyyy-mm-dd'),sysdate));
新值 7: qtime:=to_date(nvl(to_date('2003-2-20','yyyy-mm-dd'),sysdate));
happy birthday7499 ALLEN
happy birthday7521 WARDPL/SQL 过程已成功完成。
select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 2010-04-17 800 20
7499 ALLEN SALESMAN 7698 2003-02-20 1600 900 30
7521 WARD SALESMAN 7698 2003-02-22 1250 500 30
7566 JONES MANAGER 7839 2003-04-02 2975 20
7654 MARTIN SALESMAN 7698 2003-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 2003-05-01 2850 30
7782 CLARK MANAGER 7839 2003-06-09 2450 10
7788 SCOTT ANALYST 7566 2009-04-19 3000 20
7839 KING PRESIDENT 2003-11-17 5000 10
7844 TURNER SALESMAN 7698 2003-09-08 1500 0 30
7876 ADAMS CLERK 7788 2009-05-23 1100 20
7900 JAMES CLERK 7698 2003-12-03 950 30
7902 FORD ANALYST 7566 2003-12-03 3000 20
7934 MILLER CLERK 7782 2004-01-23 1300 10