sql 学习

create or replace function P_Get_SwNet_PeWeight3(Code in varchar2)
  return number is
  calcResult number;
  cursor mycur is
    select case
             when m.variable_val = 3 then
              1
             else
              0
           end
      from Y_S_DATA_ITEM m
     where m.variable_code = Code
       and m.busi_item_class = 'Nso_Pe_Mapping';
begin
  calcResult := 0;
  open mycur;
  fetch mycur
    into calcResult;
  close mycur;
  return calcResult;
end P_Get_SwNet_PeWeight3;

  上面是使用游标的方式，下面是使用select into方式。

create or replace function F_ReadDataItemValue(Code in String) return number is
  Result number;
begin
  Result:=0;
  select nvl(s.variable_val,0) into Result from Y_S_Data_Item s where s.variable_code=Code;
  return Result;
end F_ReadDataItemValue;

 

下面是在函数中有判断条件的：

create or replace function P_Get_SW_DriverNP14_PeWeight(driverCode in varchar2,peCode in varchar2)
	return number is
	rt number;
	dataItemValue number;
	tmpDataItemValue number;
begin
	if(peCode='PE-V001' or peCode='PE-V008') then 
		dataItemValue := F_ReadDataItemValue('NSO-PE-MP-003');
		if(dataItemValue=1) then
			rt := 0;
		else
			rt := 4;
		end if;
	end if;
	if(peCode='PE-V003' or peCode='PE-V023') then
		tmpDataItemValue := F_ReadDataItemValue('NSO-PE-MP-003');
		dataItemValue := F_ReadDataItemValue('NSO-PE-MP-007');
		if(dataItemValue=1) then
			if(tmpDataItemValue=1) then
				rt := 2;
			else
				rt := 4;
			end if;
		else
			f(tmpDataItemValue=1) then
				rt := 2;
			else
				rt := 6;
			end if;
		end if;
	end if;
	return rt;
end P_Get_SW_DriverNP14_PeWeight;