select * from TIMESHEET_AnnualHoliday where SID=5407 and YearMonthNo='200908' 我通过上面的查询能得到下面的一条数据(唯一的一条)(sid=5407,BASEPERSONSID =2173 YearMonthNo=200908,name=张三)
字段名称: SID BASEPERSONSID YEARMONTHNO NAME
查询果: 5407 2173 200908 张三但是我写到过程里面 就得不到这条记录CREATE OR REPLACE PROCEDURE UpdateAnnualAndLeave1(
SID TIMESHEET_AnnualHoliday.SID%type
CurYM TIMESHEET_AnnualHoliday.YearMonthNo%type
)
is
BASEPERSONSID TIMESHEET_AnnualHoliday.BASEPERSONSID%type
version TIMESHEET_AnnualHoliday.version%type;
begin
Select BASEPERSONSID,version into BASEPERSONSID,version
From TIMESHEET_AnnualHoliday where SID=SID and YearMonthNo=CurYM;--当前月的年假和倒休
DBMS_OUTPUT.put_line('BASEPERSONSID='||BASEPERSONSID);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee Not Found!');
end;
调用这个过程得到我需要的数据也就是说我的BASEPERSONSID不是2173,我写的有什么问题吗?
字段名称: SID BASEPERSONSID YEARMONTHNO NAME
查询果: 5407 2173 200908 张三但是我写到过程里面 就得不到这条记录CREATE OR REPLACE PROCEDURE UpdateAnnualAndLeave1(
SID TIMESHEET_AnnualHoliday.SID%type
CurYM TIMESHEET_AnnualHoliday.YearMonthNo%type
)
is
BASEPERSONSID TIMESHEET_AnnualHoliday.BASEPERSONSID%type
version TIMESHEET_AnnualHoliday.version%type;
begin
Select BASEPERSONSID,version into BASEPERSONSID,version
From TIMESHEET_AnnualHoliday where SID=SID and YearMonthNo=CurYM;--当前月的年假和倒休
DBMS_OUTPUT.put_line('BASEPERSONSID='||BASEPERSONSID);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee Not Found!');
end;
调用这个过程得到我需要的数据也就是说我的BASEPERSONSID不是2173,我写的有什么问题吗?
SID TIMESHEET_AnnualHoliday.SID%type,
CurYM TIMESHEET_AnnualHoliday.YearMonthNo%type
)
is
BASEPERSONSID TIMESHEET_AnnualHoliday.BASEPERSONSID%type;
version TIMESHEET_AnnualHoliday.version%type;
begin
Select BASEPERSONSID,version into BASEPERSONSID,version
From TIMESHEET_AnnualHoliday where SID=SID and YearMonthNo=CurYM;--当前月的年假和倒休
DBMS_OUTPUT.put_line('BASEPERSONSID='||BASEPERSONSID);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee Not Found!');
end;
也就是说我想得到BASEPERSONSID这个值,现在得不到
From TIMESHEET_AnnualHoliday where SID=SID and YearMonthNo=CurYM;--当前月的年假和倒休 select into 返回多条记录,要保证select into正确,则结果有且仅有一条记录。
From TIMESHEET_AnnualHoliday where SID=SID and YearMonthNo=CurYM;--当前月的年假和倒休 select into 返回多条记录,要保证select into正确,则结果有且仅有一条记录。我是返回一条啊select * from TIMESHEET_AnnualHoliday where SID=5407 and YearMonthNo='200908'
这个我在数据库执行的语句啊
SID TIMESHEET_AnnualHoliday.SID%type 这里加逗号
CurYM TIMESHEET_AnnualHoliday.YearMonthNo%type
)
is
BASEPERSONSID TIMESHEET_AnnualHoliday.BASEPERSONSID%type 这里加分号。
version TIMESHEET_AnnualHoliday.version%type;
begin
CREATE OR REPLACE PROCEDURE UpdateAnnualAndLeave1(
SID TIMESHEET_AnnualHoliday.SID%type,
CurYM TIMESHEET_AnnualHoliday.YearMonthNo%type
)
is
BASEPERSONSID TIMESHEET_AnnualHoliday.BASEPERSONSID%type;
version TIMESHEET_AnnualHoliday.version%type;
begin
Select BASEPERSONSID,version into BASEPERSONSID,version
From TIMESHEET_AnnualHoliday where SID=SID and YearMonthNo=CurYM;--当前月的年假和倒休
DBMS_OUTPUT.put_line('BASEPERSONSID='||BASEPERSONSID);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee Not Found!');
end; 这个我是在外面执行的语句
select * from TIMESHEET_AnnualHoliday where SID=5407 and YearMonthNo='200908' 我通过上面的查询能得到下面的一条数据(唯一的一条)(sid=5407,BASEPERSONSID =2173 YearMonthNo=200908,name=张三)
字段名称: SID BASEPERSONSID YEARMONTHNO NAME
查询果: 5407 2173 200908 张三
但是我把
SID=5407 and YearMonthNo='200908' 传进去就是查询不出唯一的上面的结果
where SID=SID
入参最好都改为 i_ 打头以避免不必要的麻烦。
BASEPERSONSID 改为 v_BASEPERSONSID
version 改为 v_version
执行之前先
set serveroutput on;
执行调用
exec UpdateAnnualAndLeave1(5407,'200908');在sqlplus执行。
From TIMESHEET_AnnualHoliday where SID=SID and YearMonthNo=CurYM and rownum=1;--当前月的年假和倒休
看看
insert into TIMESHEET_AnnualHoliday values (5407 ,2173 , '200908' , '张三');
commit;经测试,似乎是你的表里面并没有version这个字段,我加了这个字段后,可以运行也可以出结果
alter table TIMESHEET_AnnualHoliday add version varchar2(10);
CREATE OR REPLACE PROCEDURE UpdateAnnualAndLeave1(
i_SID TIMESHEET_AnnualHoliday.SID%type,
i_CurYM TIMESHEET_AnnualHoliday.YearMonthNo%type
)
is
v_BASEPERSONSID TIMESHEET_AnnualHoliday.BASEPERSONSID%type;
v_version TIMESHEET_AnnualHoliday.version%type;
begin
Select BASEPERSONSID,version into v_BASEPERSONSID,v_version
From TIMESHEET_AnnualHoliday where SID=i_SID and YearMonthNo=i_CurYM;--当前月的年假和倒休
DBMS_OUTPUT.put_line('BASEPERSONSID='||v_BASEPERSONSID);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee Not Found!');
end;
/set serveroutput on;
exec UpdateAnnualAndLeave1(5407,'200908');
2 i_SID TIMESHEET_AnnualHoliday.SID%type,
3 i_CurYM TIMESHEET_AnnualHoliday.YearMonthNo%type
4 )
5 is
6 v_BASEPERSONSID TIMESHEET_AnnualHoliday.BASEPERSONSID%type;
7 v_version TIMESHEET_AnnualHoliday.version%type;
8 begin
9 Select BASEPERSONSID,version into v_BASEPERSONSID,v_version
10 From TIMESHEET_AnnualHoliday where SID=i_SID and YearMonthNo=i_CurYM;--当前月的年假和倒休
11 DBMS_OUTPUT.put_line('BASEPERSONSID='||v_BASEPERSONSID);
12 EXCEPTION
13 WHEN NO_DATA_FOUND THEN
14 DBMS_OUTPUT.PUT_LINE('Employee Not Found!');
15 end;
16 /过程已创建。SQL> set serveroutput on;
SQL> exec UpdateAnnualAndLeave1(5407,'200908');
BASEPERSONSID=2173PL/SQL 过程已成功完成。SQL> select * from TIMESHEET_AnnualHoliday; SID BASEPERSONSID
---------- -------------
YEARMONTHNO
--------------------------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
VERSION
--------------------
5407 2173
200908
张三SQL>