小弟新手 头一次写存储过程 现在假设有一条sql: select ename ,eadd,etel from emp where eno=? and ename=? and esal=? 这三个条件不都是必选项 所以需要动态拼写sql 哪位大哥麻烦帮写个例子 通过存储过程实现这个 动态拼写sql 并且返回结果
--我昨天正好有个例子你可以参考一下 --http://topic.csdn.net/u/20111011/11/4ababd51-412d-49f0-a159-536ef6dc95a1.html create or replace package PKG_Report is TYPE Result_Cur is REF CURSOR; --请假明细表 可按部门号、工号、姓名、假别、状态、请假时间段等来查询 PROCEDURE SP_Rpt_Leave(p_Org_ID varchar2,p_Employee_No varchar2,p_Employee_Name varchar2,p_Type_No VARCHAR2,p_Flow_Status varchar2,p_Date_Begin DATE,p_Date_End DATE,p_Result out Result_Cur);end; create or replace package body PKG_Report IS PROCEDURE SP_Rpt_Leave(p_Org_ID varchar2,p_Employee_No varchar2,p_Employee_Name varchar2,p_Type_No VARCHAR2,p_Flow_Status varchar2,p_Date_Begin DATE,p_Date_End DATE,p_Result out Result_Cur) IS v_SQL VARCHAR2(4000); v_Date_Begin VARCHAR2(20); v_Date_End VARCHAR2(20); BEGIN v_SQL :='SELECT t4.org_id 部门编号,t4.org_name 部门名称,t2.employee_no 工号,t3.employee_name 姓名 ,t5.type_name 假别,t2.time_begin 开始时间,t2.time_end 结束时间,t2.leave_hours 请假时长 ,t2.leave_reason 请假原因,t1.form_no 请假单号,t1.update_user 申请人,t1.update_date 申请时间 ,t1.flow_status 审核状态 FROM oa_att_leave t1 INNER JOIN oa_att_leave_d t2 ON t1.form_id=t2.form_id INNER JOIN oa_hr_machi_employee t3 ON t3.employee_no=t2.employee_no INNER JOIN oa_hr_org t4 ON t3.org_id=t4.org_id INNER JOIN oa_att_leave_type t5 ON t5.type_no=t2.type_no WHERE t3.org_id IN ('||p_Org_ID||')'; IF NVL(p_Employee_No,' ')<>' ' THEN v_SQL := v_SQL || ' AND t2.employee_no like ''%'||nvl(p_Employee_No,'%')||'%'''; END IF; IF NVL(p_Employee_Name,' ')<>' ' THEN v_SQL := v_SQL || ' AND t2.employee_name like ''%'||nvl(p_Employee_Name,'%')||'%'''; END IF; IF p_Type_No IS NOT NULL THEN v_SQL := v_SQL || ' AND t2.type_no IN ('||p_Type_No||')'; END IF; IF p_Flow_Status IS NOT NULL THEN v_SQL := v_SQL || ' AND t1.flow_status IN ('||p_Flow_Status||')'; END IF; IF p_Date_End IS NOT NULL THEN v_Date_End :=to_char(p_Date_End,'yyyy-mm-dd'); v_SQL := v_SQL ||' AND to_char(t2.time_begin,''yyyy-mm-dd'')<='''|| v_Date_End||'''';
IF p_Date_Begin IS NOT NULL THEN --查询该区间内所有请假的人 v_Date_Begin :=to_char(p_Date_Begin,'yyyy-mm-dd'); v_SQL := v_SQL || ' AND to_char(t2.time_begin,''yyyy-mm-dd'')>='''|| v_Date_Begin|| ''''; END IF; ELSE --查询当前时间(即开始时间)所有请假的人 IF p_Date_Begin IS NOT NULL THEN v_Date_Begin :=to_char(p_Date_Begin,'yyyy-mm-dd'); v_SQL := v_SQL || ' AND to_char(t2.time_begin,''yyyy-mm-dd'')<='''|| v_Date_Begin|| '''';
v_Date_Begin :=to_char(p_Date_Begin,'yyyy-mm-dd'); v_SQL := v_SQL || ' AND to_char(t2.time_begin,''yyyy-mm-dd'')>='''|| v_Date_Begin|| ''''; END IF; END IF;
v_SQL := v_SQL||' ORDER BY t4.sort_id'; open p_Result for v_SQL ; END SP_Rpt_Leave; end; --end package
也可以不用动态sql的 create or replace p_test ( i_eno varchar2, i_ename varchar2, i_sal number, ocursor out sys_refcursor ) as begin open ocursor for select * from tabname where (eno = str_eno or i_eno is null) and (ename = str_ename or i_ename is null) and (sal = int_sal or i_sal is null); end p_test;
前阵子写的 create or replace procedure sp_get_busi(vv out varchar2,p_OutCursor out sys_refcursor) is tb_name varchar2(100); v_sql varchar2(1000); has_updateTM number; max_tm varchar(100); start_tm varchar(100); begin tb_name:='srcs.CM_BUSI_'|| to_char(sysdate,'yyyymm') ||'@link_mcm';--当前的月份表 select count(*) into has_updateTM from scm_updatewuzi_tm where update_type=0 ;--是否存在更新时间 0是物资 1是号码 if has_updateTM =0 then start_tm:=to_char(add_months(sysdate,-1),'yyyy-mm-dd hh24:mi:ss'); v_sql:='select so_nbr, busi_code,so_org_id,so_date from '|| tb_name || ' where so_date > to_date('''||start_tm||''',''yyyy-mm-dd hh24:mi:ss'') and so_date <=to_date(to_char(sysdate,''yyyy-mm-dd hh24:mi:ss''),''yyyy-mm-dd hh24:mi:ss'') order by so_date';-- else select to_char(max(tm),'yyyy-mm-dd hh24:mi:ss') into max_tm from scm_updatewuzi_tm where update_type=0 ; v_sql:='select so_nbr, busi_code,so_org_id,so_date from '|| tb_name || ' where so_date >to_date('''||max_tm||''',''yyyy-mm-dd hh24:mi:ss'') and so_date <=to_date(to_char(sysdate,''yyyy-mm-dd hh24:mi:ss''),''yyyy-mm-dd hh24:mi:ss'') order by so_date';--
end if; vv:=v_sql; open p_OutCursor for v_sql; exception when others then rollback; end sp_get_busi;
--http://topic.csdn.net/u/20111011/11/4ababd51-412d-49f0-a159-536ef6dc95a1.html
create or replace package PKG_Report is TYPE Result_Cur is REF CURSOR;
--请假明细表 可按部门号、工号、姓名、假别、状态、请假时间段等来查询
PROCEDURE SP_Rpt_Leave(p_Org_ID varchar2,p_Employee_No varchar2,p_Employee_Name varchar2,p_Type_No VARCHAR2,p_Flow_Status varchar2,p_Date_Begin DATE,p_Date_End DATE,p_Result out Result_Cur);end;
create or replace package body PKG_Report IS
PROCEDURE SP_Rpt_Leave(p_Org_ID varchar2,p_Employee_No varchar2,p_Employee_Name varchar2,p_Type_No VARCHAR2,p_Flow_Status varchar2,p_Date_Begin DATE,p_Date_End DATE,p_Result out Result_Cur) IS
v_SQL VARCHAR2(4000);
v_Date_Begin VARCHAR2(20);
v_Date_End VARCHAR2(20);
BEGIN
v_SQL :='SELECT t4.org_id 部门编号,t4.org_name 部门名称,t2.employee_no 工号,t3.employee_name 姓名
,t5.type_name 假别,t2.time_begin 开始时间,t2.time_end 结束时间,t2.leave_hours 请假时长
,t2.leave_reason 请假原因,t1.form_no 请假单号,t1.update_user 申请人,t1.update_date 申请时间
,t1.flow_status 审核状态
FROM oa_att_leave t1
INNER JOIN oa_att_leave_d t2 ON t1.form_id=t2.form_id
INNER JOIN oa_hr_machi_employee t3 ON t3.employee_no=t2.employee_no
INNER JOIN oa_hr_org t4 ON t3.org_id=t4.org_id
INNER JOIN oa_att_leave_type t5 ON t5.type_no=t2.type_no
WHERE t3.org_id IN ('||p_Org_ID||')';
IF NVL(p_Employee_No,' ')<>' ' THEN
v_SQL := v_SQL || ' AND t2.employee_no like ''%'||nvl(p_Employee_No,'%')||'%''';
END IF;
IF NVL(p_Employee_Name,' ')<>' ' THEN
v_SQL := v_SQL || ' AND t2.employee_name like ''%'||nvl(p_Employee_Name,'%')||'%''';
END IF;
IF p_Type_No IS NOT NULL THEN
v_SQL := v_SQL || ' AND t2.type_no IN ('||p_Type_No||')';
END IF;
IF p_Flow_Status IS NOT NULL THEN
v_SQL := v_SQL || ' AND t1.flow_status IN ('||p_Flow_Status||')';
END IF;
IF p_Date_End IS NOT NULL THEN
v_Date_End :=to_char(p_Date_End,'yyyy-mm-dd');
v_SQL := v_SQL ||' AND to_char(t2.time_begin,''yyyy-mm-dd'')<='''|| v_Date_End||'''';
IF p_Date_Begin IS NOT NULL THEN --查询该区间内所有请假的人
v_Date_Begin :=to_char(p_Date_Begin,'yyyy-mm-dd');
v_SQL := v_SQL || ' AND to_char(t2.time_begin,''yyyy-mm-dd'')>='''|| v_Date_Begin|| '''';
END IF;
ELSE --查询当前时间(即开始时间)所有请假的人
IF p_Date_Begin IS NOT NULL THEN
v_Date_Begin :=to_char(p_Date_Begin,'yyyy-mm-dd');
v_SQL := v_SQL || ' AND to_char(t2.time_begin,''yyyy-mm-dd'')<='''|| v_Date_Begin|| '''';
v_Date_Begin :=to_char(p_Date_Begin,'yyyy-mm-dd');
v_SQL := v_SQL || ' AND to_char(t2.time_begin,''yyyy-mm-dd'')>='''|| v_Date_Begin|| '''';
END IF;
END IF;
v_SQL := v_SQL||' ORDER BY t4.sort_id'; open p_Result for v_SQL ;
END SP_Rpt_Leave;
end; --end package
create or replace p_test
(
i_eno varchar2,
i_ename varchar2,
i_sal number,
ocursor out sys_refcursor
)
as
begin
open ocursor for
select * from tabname
where (eno = str_eno or i_eno is null)
and (ename = str_ename or i_ename is null)
and (sal = int_sal or i_sal is null);
end p_test;
前阵子写的
create or replace procedure sp_get_busi(vv out varchar2,p_OutCursor out sys_refcursor)
is
tb_name varchar2(100);
v_sql varchar2(1000);
has_updateTM number;
max_tm varchar(100);
start_tm varchar(100);
begin
tb_name:='srcs.CM_BUSI_'|| to_char(sysdate,'yyyymm') ||'@link_mcm';--当前的月份表
select count(*) into has_updateTM from scm_updatewuzi_tm where update_type=0 ;--是否存在更新时间 0是物资 1是号码
if has_updateTM =0 then
start_tm:=to_char(add_months(sysdate,-1),'yyyy-mm-dd hh24:mi:ss');
v_sql:='select so_nbr, busi_code,so_org_id,so_date from '||
tb_name || ' where so_date > to_date('''||start_tm||''',''yyyy-mm-dd hh24:mi:ss'') and so_date <=to_date(to_char(sysdate,''yyyy-mm-dd hh24:mi:ss''),''yyyy-mm-dd hh24:mi:ss'') order by so_date';--
else
select to_char(max(tm),'yyyy-mm-dd hh24:mi:ss') into max_tm from scm_updatewuzi_tm where update_type=0 ;
v_sql:='select so_nbr, busi_code,so_org_id,so_date from '||
tb_name || ' where so_date >to_date('''||max_tm||''',''yyyy-mm-dd hh24:mi:ss'') and so_date <=to_date(to_char(sysdate,''yyyy-mm-dd hh24:mi:ss''),''yyyy-mm-dd hh24:mi:ss'') order by so_date';--
end if;
vv:=v_sql;
open p_OutCursor for v_sql;
exception
when others then
rollback;
end sp_get_busi;