CREATE OR REPLACE PACKAGE pkg_mytest
IS
TYPE my_cursor IS REF CURSOR;
procedure sql_GetNoFillTimeSheetByStaff(StaffID IN CHAR, StartDate IN DATE, EndDate IN DATE, p_cursor out my_cursor);
END pkg_mytest; CREATE OR REPLACE PACKAGE BODY pkg_mytest //报错
IS
CREATE OR REPLACE procedure sql_GetNoFillTimeSheetByStaff
(StaffID IN CHAR, StartDate IN DATE, EndDate IN DATE, p_cursor out my_cursor) iS
BEGIN
OPEN p_cursor for INSERT INTO TEMPSTAFFTICKETDATE(DateValue)
(SELECT DATEVALUE FROM CDATE d WHERE d.DATEVALUE BETWEEN StartDate AND EndDate);
Commit; DELETE FROM TEMPSTAFFTICKETDATE WHERE EXISTS
(SELECT 1 FROM V_TIMESHEET vt WHERE DATEVALUE = TEMPSTAFFTICKETDATE.DATEVALUE
AND (vt.STATUS = 1 OR vt.STATUS = 2) AND vt.STAFFID = StaffID
AND vt.DATEVALUE BETWEEN StartDate AND EndDate
GROUP BY DATEVALUE, STAFFID HAVING SUM(HOURS) > = 8);
commit; SELECT DATEVALUE into dataTable FROM TEMPSTAFFTICKETDATE;
commit; End sql_GetNoFillTimeSheetByStaff;
END pkg_mytest; 小弟刚学存储过程 问一下
是不是包头和包体不能写在一起呀
错误就出现在定义包体时,报PLS-00103:出现符号 "CREATE " 这是怎么回事 另外 我是想用这个程序返回一个DATASET 我使用了游标,如何使用它返回一个DATASET便于.NET的绑定呢? 谢谢了
IS
TYPE my_cursor IS REF CURSOR;
procedure sql_GetNoFillTimeSheetByStaff(StaffID IN CHAR, StartDate IN DATE, EndDate IN DATE, p_cursor out my_cursor);
END pkg_mytest; CREATE OR REPLACE PACKAGE BODY pkg_mytest //报错
IS
CREATE OR REPLACE procedure sql_GetNoFillTimeSheetByStaff
(StaffID IN CHAR, StartDate IN DATE, EndDate IN DATE, p_cursor out my_cursor) iS
BEGIN
OPEN p_cursor for INSERT INTO TEMPSTAFFTICKETDATE(DateValue)
(SELECT DATEVALUE FROM CDATE d WHERE d.DATEVALUE BETWEEN StartDate AND EndDate);
Commit; DELETE FROM TEMPSTAFFTICKETDATE WHERE EXISTS
(SELECT 1 FROM V_TIMESHEET vt WHERE DATEVALUE = TEMPSTAFFTICKETDATE.DATEVALUE
AND (vt.STATUS = 1 OR vt.STATUS = 2) AND vt.STAFFID = StaffID
AND vt.DATEVALUE BETWEEN StartDate AND EndDate
GROUP BY DATEVALUE, STAFFID HAVING SUM(HOURS) > = 8);
commit; SELECT DATEVALUE into dataTable FROM TEMPSTAFFTICKETDATE;
commit; End sql_GetNoFillTimeSheetByStaff;
END pkg_mytest; 小弟刚学存储过程 问一下
是不是包头和包体不能写在一起呀
错误就出现在定义包体时,报PLS-00103:出现符号 "CREATE " 这是怎么回事 另外 我是想用这个程序返回一个DATASET 我使用了游标,如何使用它返回一个DATASET便于.NET的绑定呢? 谢谢了
直接procedure就可以了。
AS
procedure sql_GetNoFillTimeSheetByStaff
(StaffID CHAR, StartDate DATE, EndDate DATE, p_cursor out my_cursor)
IS
sqlstr VARCHAR2(500);
BEGIN
sqlstr :=
'INSERT INTO TEMPSTAFFTICKETDATE(DateValue)
(SELECT DATEVALUE FROM CDATE d WHERE d.DATEVALUE BETWEEN StartDate AND EndDate);
Commit;DELETE FROM TEMPSTAFFTICKETDATE WHERE EXISTS
(SELECT 1 FROM V_TIMESHEET vt WHERE DATEVALUE = TEMPSTAFFTICKETDATE.DATEVALUE
AND (vt.STATUS = 1 OR vt.STATUS = 2) AND vt.STAFFID = StaffID
AND vt.DATEVALUE BETWEEN StartDate AND EndDate
GROUP BY DATEVALUE, STAFFID HAVING SUM(HOURS) >= 8);
commit;SELECT DATEVALUE into dataTable FROM TEMPSTAFFTICKETDATE;
commit';
OPEN p_cursor FOR sqlstr USING StaffID,StartDate,EndDate;
End sql_GetNoFillTimeSheetByStaff;
END pkg_mytest;
IS
TYPE my_cursor IS REF CURSOR;
procedure sql_GetNoFillTimeSheetByStaff
(StaffID IN CHAR, StartDate IN DATE, EndDate IN DATE, p_cursor out my_cursor);
END pkg_mytest;CREATE OR REPLACE PACKAGE BODY pkg_mytest PLS-00103:出现符号 "CREATE"
AS
procedure sql_GetNoFillTimeSheetByStaff
(StaffID CHAR, StartDate DATE, EndDate DATE, p_cursor out my_cursor)
IS
sqlstr VARCHAR2(500);
BEGIN
sqlstr :=
'INSERT INTO TEMPSTAFFTICKETDATE(DateValue)
(SELECT DATEVALUE FROM CDATE d WHERE d.DATEVALUE BETWEEN StartDate AND EndDate);
Commit;DELETE FROM TEMPSTAFFTICKETDATE WHERE EXISTS
(SELECT 1 FROM V_TIMESHEET vt WHERE DATEVALUE = TEMPSTAFFTICKETDATE.DATEVALUE
AND (vt.STATUS = 1 OR vt.STATUS = 2) AND vt.STAFFID = StaffID
AND vt.DATEVALUE BETWEEN StartDate AND EndDate
GROUP BY DATEVALUE, STAFFID HAVING SUM(HOURS) >= 8);
commit;SELECT DATEVALUE into dataTable FROM TEMPSTAFFTICKETDATE;
commit';
OPEN p_cursor FOR sqlstr USING StaffID,StartDate,EndDate;
End sql_GetNoFillTimeSheetByStaff;
END pkg_mytest;