需要写一个存储过程,返回只有一个字段的结果集,存储过程可接收N个参数,并且不要求每个参数都有值,需要判断有值的话就根据这个值当where条件查询,如数据库里有三张表,一张用户的基本信息表,字段有u_id,u_name,u_email,还有一张表是扩展表,字段有u_id,sex,age,vocation,provice等等,我这个存储过程要接收程序传过来的比如sex,age,vocation等等参数,允许其中的参数为空,不为空的当作where条件,返回一个u_email的结果集给同一个数据库实例的不同用户的程序使用,怎样写啊?我对这个存储过程实在是不能精通,多谢各位帮忙了,一个菜鸟敬上!
-- Created : 2006-5-16 9:59:33
-- Purpose :
-- Public type declarations
type search_result is REF CURSOR;
PROCEDURE sp_search_email(vEmail_in IN VARCHAR2 ,cur_result_out OUT serarch_result) ;
end PK_FORNESLETTER_EMAIL;
--通过输入的条件获得用户email结果集的存储过程
create or replace procedure sp_search_email(vAccountName_in In Varchar2(20) Default Null,
vProvice_in In varchar2(30) Default null,
vSalary_in In Varchar2(4) Default null,
vVocation_in In Varchar2(4) Default Null,
vSex_in In Varchar2(4) Default Null,
vBeginRegisterTime_in In Varchar2 Default Null,
vEndRegisterTime_in In Varchar2 Default Null,
cur_result_out Out search_result)
Is
str_accountName Varchar2(50);
str_provice Varchar2(50);
str_salary Varchar2(50);
str_vocation Varchar2(50);
str_sex Varchar2(50);
str_registerTime Varchar2(70);
str_sql Varchar2(200);
Begin If vAccountName_in Not Null Then
str_accountName := 'and account_name=' || vAccountName_in;
Else If vProvice_in Not Null Then
str_provice := 'and b.provice=' || vProvice_in;
Else If vSalary_in Not Null Then
str_salary := 'and b.salary=' || vSalary_in;
Else If vVocation_in Not Null Then
str_vocation := 'and b.vocation=' || vVocation_in;
Else If vSex_in Not Null Then
str_sex := 'and b.sex=' || vSex_in;
Else If vBeginRegisterTime_in Not Null And vEndRegisterTime_in Not Null Then
str_registerTime := 'and a.reg_time>=' || vBeginRegisterTime_in || 'and a.reg_time<=' || vEndRegisterTime_in;
str_sql := 'select a.email from user_info a,single_user_info b where 1=1'|| str_accountName || str_provice || str_salary || str_vocation || str_sex || str_registerTime;
Open cur_result_out For str_sql;
end sp_search_email;
写了一个大概,问题肯定还有不少吧,请高手指点一下。
P_sex in varchar2,
P_age in varchar2,
P_vocation in varchar2,
P_query out search_result
) as
begin
open P_query for
select a.email from 基本表 a,扩展表 bwhere a.u_id =b.u_id
and (b.sex=P_sex or p_sex is null)
and (b.age=P_age or P_age is null)
and (b.sex=P_vocation or P_vocation is null) )
end test;
/个人认为输入的参数有可能为空或不为空可以在查询时就判断
“sex=female and age=18 and vocation=...”那存储过程一个字符串参数就可以:)