CREATE OR REPLACE FUNCTION get_svc_site1 (
bu1 VARCHAR2,
area1 VARCHAR2,
country1 VARCHAR2,
custno VARCHAR2,
custname VARCHAR2,
shiplocation VARCHAR2,
sppsite OUT VARCHAR2,
fontend OUT VARCHAR2
)
RETURN VARCHAR2
IS
v_rtn VARCHAR2 (200) := 'OK';
BEGIN
sqlstr :=
'select spp_site,font_end into sppsite,fontend from GRMA_SVC_SITE where 1=1'; IF bu1 IS NOT NULL
THEN
sqlstr := sqlstr || ' and bu = ''' || bu1;
END IF; IF area1 IS NOT NULL
THEN
sqlstr := sqlstr || ''' and area=''' || area1;
END IF; IF country1 IS NOT NULL
THEN
sqlstr := sqlstr || ''' and country=''' || country1;
END IF; IF custno IS NOT NULL
THEN
sqlstr := sqlstr || ''' and cust_no=''' || custno;
END IF; IF custname IS NOT NULL
THEN
sqlstr := sqlstr || ''' and cust_name=''' || custname;
END IF; IF shiplocation IS NOT NULL
THEN
sqlstr := sqlstr || ''' and ship_location=''' || shiplocation || '''';
END IF; EXECUTE IMMEDIATE sqlstr; RETURN v_rtn;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN 'NONE';
END get_svc_site1;现在我希望传入6个参数查询数据库得到2个字段返回,那位高手帮我看下这段代码有错误嘛?我是菜鸟了,oracle的确实不会啊
bu1 VARCHAR2,
area1 VARCHAR2,
country1 VARCHAR2,
custno VARCHAR2,
custname VARCHAR2,
shiplocation VARCHAR2,
sppsite OUT VARCHAR2,
fontend OUT VARCHAR2
)
RETURN VARCHAR2
IS
v_rtn VARCHAR2 (200) := 'OK';
BEGIN
sqlstr :=
'select spp_site,font_end into sppsite,fontend from GRMA_SVC_SITE where 1=1'; IF bu1 IS NOT NULL
THEN
sqlstr := sqlstr || ' and bu = ''' || bu1;
END IF; IF area1 IS NOT NULL
THEN
sqlstr := sqlstr || ''' and area=''' || area1;
END IF; IF country1 IS NOT NULL
THEN
sqlstr := sqlstr || ''' and country=''' || country1;
END IF; IF custno IS NOT NULL
THEN
sqlstr := sqlstr || ''' and cust_no=''' || custno;
END IF; IF custname IS NOT NULL
THEN
sqlstr := sqlstr || ''' and cust_name=''' || custname;
END IF; IF shiplocation IS NOT NULL
THEN
sqlstr := sqlstr || ''' and ship_location=''' || shiplocation || '''';
END IF; EXECUTE IMMEDIATE sqlstr; RETURN v_rtn;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN 'NONE';
END get_svc_site1;现在我希望传入6个参数查询数据库得到2个字段返回,那位高手帮我看下这段代码有错误嘛?我是菜鸟了,oracle的确实不会啊
bu1 VARCHAR2,
area1 VARCHAR2,
country1 VARCHAR2,
custno VARCHAR2,
custname VARCHAR2,
shiplocation VARCHAR2,
sppsite OUT VARCHAR2,
fontend OUT VARCHAR2
)
RETURN VARCHAR2
IS
v_rtn VARCHAR2 (200) := 'OK';
sqlstr varchar2(800);BEGIN
sqlstr :=
'select spp_site,font_end from GRMA_SVC_SITE where 1=1'; IF bu1 IS NOT NULL
THEN
sqlstr := sqlstr || ' and bu = ''' || bu1;
END IF; IF area1 IS NOT NULL
THEN
sqlstr := sqlstr || ''' and area=''' || area1;
END IF; IF country1 IS NOT NULL
THEN
sqlstr := sqlstr || ''' and country=''' || country1;
END IF; IF custno IS NOT NULL
THEN
sqlstr := sqlstr || ''' and cust_no=''' || custno;
END IF; IF custname IS NOT NULL
THEN
sqlstr := sqlstr || ''' and cust_name=''' || custname;
END IF; IF shiplocation IS NOT NULL
THEN
sqlstr := sqlstr || ''' and ship_location=''' || shiplocation || '''';
END IF; EXECUTE IMMEDIATE sqlstr
into sppsite,fontend;
RETURN v_rtn;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN 'NONE';
END get_svc_site1;
说明:这个过程中执行的sql只有在返回一个值时,才不会报错,不然会报错的
如果我用out 参数也不能输出多个值嘛?谢谢了
请问下用function能写嘛?我主要是现在我没权限添加,只能修改数据库。