本人只学过oracle,现在做练习,里面有一段server语句不知道怎么理解,请高手帮忙把这段代码改成oracle的代码,谢谢了create procedure GetScheduleGivenDay @GivenDay datetime
asselect
task.title,
task.detail,
task.begintime,
task.notifytime,
task.neednotify,
task.finished,
tasktype.typetitle,
priority.prioritytitle
from tasktype
INNER JOIN task ON Task.Type.TaskType_ID=task.type
INNER JOIN priority ON task.priority=priority.priority_ID
where
(DAY(@GivenDay)=DAY(task.BeginTime))
AND
(MONTH(@GivenDay)=MONTH(task.BeginTime))
AND
(YEAR(@GivenDay)=YEAR(task.BeginTime))
RETURN
GO
asselect
task.title,
task.detail,
task.begintime,
task.notifytime,
task.neednotify,
task.finished,
tasktype.typetitle,
priority.prioritytitle
from tasktype
INNER JOIN task ON Task.Type.TaskType_ID=task.type
INNER JOIN priority ON task.priority=priority.priority_ID
where
(DAY(@GivenDay)=DAY(task.BeginTime))
AND
(MONTH(@GivenDay)=MONTH(task.BeginTime))
AND
(YEAR(@GivenDay)=YEAR(task.BeginTime))
RETURN
GO
还有你的说明你这段代码是干啥的,别人才好帮你。
as
begin
select
task.title,
task.detail,
task.begintime,
task.notifytime,
task.neednotify,
task.finished,
tasktype.typetitle,
priority.prioritytitle
from tasktype
INNER JOIN task ON Task.Type.TaskType_ID=task.type
INNER JOIN priority ON task.priority=priority.priority_ID
where
to_char(GivenDay,'yyyy-MM-dd')=to_char(task.BeginTime,'yyyy-MM-dd');
end GetScheduleGivenDay;
/
as
begin
select
task.title,
task.detail,
task.begintime,
task.notifytime,
task.neednotify,
task.finished,
tasktype.typetitle,
priority.prioritytitle
from tasktype
JOIN task ON Task.Type.TaskType_ID=task.type
JOIN priority ON task.priority=priority.priority_ID
where
to_char(GivenDay,'yyyy-MM-dd')=to_char(task.BeginTime,'yyyy-MM-dd');
end GetScheduleGivenDay;
/
as
g_task_title task.title%type;
g_task_detail task.detail%type;
g_task_begintime task.begintime%type;
g_task_neednotify task.neednotify%type;
g_task_finished task.finished%type;
g_tasktype_typetitle tasktype.typetitle%type;
g_priority_title priority.prioritytitle%type;
begin
select
task.title,
task.detail,
task.begintime,
task.notifytime,
task.neednotify,
task.finished,
tasktype.typetitle,
priority.prioritytitle
into
g_task_title,
g_task_detail,
g_task_begintime,
g_task_neednotify,
g_task_finished,
g_tasktype_typetitle,
g_priority_title
from tasktype
INNER JOIN task ON TaskType.TaskType_ID=task.tasktype_id
INNER JOIN priority ON task.priority_id=priority.priority_ID
where
to_char(GivenDay,'yyyy-MM-dd')=to_char(task.BeginTime,'yyyy-MM-dd');
end GetScheduleGivenDay;
最后报错是:28/20 PL/SQL: ORA-00947: not enough values
12/65530 PL/SQL: SQL Statement ignored
task.title,
task.detail,
task.begintime,
task.notifytime,
task.neednotify,
task.finished,
tasktype.typetitle,
priority.prioritytitle
into
g_task_title,
g_task_detail,
g_task_begintime,
g_task_neednotify,
g_task_finished,
g_tasktype_typetitle,
g_priority_title
-- 如:2009-04-25,表示查询BgeinTime属于2009年04月25日那一天的数据记录
CREATE OR REPLACE PACKAGE pkg_GetScheduleGivenDay
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE GetScheduleGivenDay_proc(v_GivenDay varchar2, p_rc OUT myrctype);
END pkg_GetScheduleGivenDay;
/CREATE OR REPLACE PACKAGE BODY pkg_GetScheduleGivenDay
AS
PROCEDURE GetScheduleGivenDay_proc(v_GivenDay varchar2, p_rc OUT myrctype)
IS
sqlstr VARCHAR2(4000);
BEGIN
sqlstr := 'select task.title, task.detail, task.begintime, task.notifytime, task.neednotify, task.finished,
tasktype.typetitle, priority.prioritytitle
from tasktype
where task.BeginTime >= to_date(:v_GivenDay,''YYYY-MM-DD'')
and task.BeginTime < to_date(:v_GivenDay,''YYYY-MM-DD'')+1';
OPEN p_rc FOR sqlstr USING v_GivenDay, v_GivenDay;
END GetScheduleGivenDay_proc;
END pkg_GetScheduleGivenDay;
/
------------ 在SQL*Plus 中进行测试,测试代码如下: ------------
SQL> var v_rc refcursor
SQL> exec pkg_GetScheduleGivenDay.GetScheduleGivenDay_proc('2010-02-25',:v_rc);
SQL> print v_rc;