使用一些连接将两个存储过程组合成一个(Combining two stored procedures into a single one using some joins)

我是Oracle的新手; 我写了两个存储过程，它们有两个不同的参数。我想将这两个存储过程中的查询组合到单个存储过程中的单个查询中，并确保它支持任何条件。

这些是存储过程：

procedure usp_testsp1(RC1 OUT RCT1, in_dep_Id IN number, in_Org_id IN number, in_emp_no IN number) is begin select emp.Name as name,select emp.phone as phone,select emp.Race as race from employee emp JOIN Project prj ON emp.ProjectId=prj.ProjectId JOIN Project_Org Prorg ON prj.ProjectId=Prorg.ProjectId JOIN Organization Org1 ON Prorg.OrgId=Org1.OrgId JOIN Organization Org2 ON emp.OrgId=Org2.OrgId AND Org2.OrgType=0 WHERE (upper(emp.emp_No) in (in_emp_No )) AND Prorg.OrgId=in_Org_id AND Org2.OrgId=in_dep_Id end; procedure usp_testsp2(RC1 OUT RCT1, in_dep_Id IN number, in_Org_id IN number, in_vendor_id IN raw, in_vendor_startdate IN date, in_vendor_enddate IN date) is begin OPEN RC1 FOR select emp.Name as name,select emp.phone as phone,select emp.Race as race from from employee emp JOIN Project prj ON emp.ProjectId=prj.ProjectId JOIN Project_Org Prorg ON prj.ProjectId=Prorg.ProjectId JOIN Organization Org1 ON Prorg.OrgId=Org1.OrgId JOIN Organization Org2 ON emp.OrgId=Org2.OrgId AND Org2.OrgType=0 INNER JOIN vendor_Emp ON emp.employeeid=vendor_emp.employeeid INNER JOIN Vendor ON Vendor.VendorId=Vendor_emp.VendorId WHERE Prorg.OrgId=in_Org_id AND Org2.OrgId=in_dep_Id AND Vendor.VendorId=in_vendor_id AND vendor.StartDate=in_vendor_startdate AND Vendor.EndDate=in_vendor_enddate end;

我的目标是将这两个单独的过程中的查询组合成一个单独的查询，这样如果我组合所有参数，我有in_emp_no，in_vendor_id，in_vendor_startdate，in_vendor_enddate除了公共参数之外，任何参数都可以为null我想要确保那些东西不会影响查询的其余部分，即使它不是null因为ex我只有in_emp_no ='xxxx'和常见输入其余的参数如in_vendor_id等为null。我想让我的查询工作，即使使用单参数来过滤结果集注意不使用动态SQL谢谢BJ

I am very new to Oracle; I have written two stored procedures where they have both different parameters. I would like to combine the queries in that two stored procedures into single query in a single stored procedure, and make sure it supports any criteria.

These are the stored procedures:

procedure usp_testsp1(RC1 OUT RCT1, in_dep_Id IN number, in_Org_id IN number, in_emp_no IN number) is begin select emp.Name as name,select emp.phone as phone,select emp.Race as race from employee emp JOIN Project prj ON emp.ProjectId=prj.ProjectId JOIN Project_Org Prorg ON prj.ProjectId=Prorg.ProjectId JOIN Organization Org1 ON Prorg.OrgId=Org1.OrgId JOIN Organization Org2 ON emp.OrgId=Org2.OrgId AND Org2.OrgType=0 WHERE (upper(emp.emp_No) in (in_emp_No )) AND Prorg.OrgId=in_Org_id AND Org2.OrgId=in_dep_Id end; procedure usp_testsp2(RC1 OUT RCT1, in_dep_Id IN number, in_Org_id IN number, in_vendor_id IN raw, in_vendor_startdate IN date, in_vendor_enddate IN date) is begin OPEN RC1 FOR select emp.Name as name,select emp.phone as phone,select emp.Race as race from from employee emp JOIN Project prj ON emp.ProjectId=prj.ProjectId JOIN Project_Org Prorg ON prj.ProjectId=Prorg.ProjectId JOIN Organization Org1 ON Prorg.OrgId=Org1.OrgId JOIN Organization Org2 ON emp.OrgId=Org2.OrgId AND Org2.OrgType=0 INNER JOIN vendor_Emp ON emp.employeeid=vendor_emp.employeeid INNER JOIN Vendor ON Vendor.VendorId=Vendor_emp.VendorId WHERE Prorg.OrgId=in_Org_id AND Org2.OrgId=in_dep_Id AND Vendor.VendorId=in_vendor_id AND vendor.StartDate=in_vendor_startdate AND Vendor.EndDate=in_vendor_enddate end;

My objective is to combine the queries within these two separate procedures into a single query such that if I combine all the parameters, I have in_emp_no, in_vendor_id, in_vendor_startdate, in_vendor_enddate apart from the common parameters any of the parameters can be null and I want to make sure that those things won't affect the rest of the query even though its not null For ex i have only in_emp_no ='xxxx' and common inputs rest of the params like in_vendor_id etc is null. I would like to make my query to work even with single paramter to filter the result set NOTEWithout using Dynamic SQL Thanks BJ

最满意答案

您可以使查询中的列表现为可选，就您的联接而言，您可能需要使用各种组合来确保一切正常。

样本表

create table sqltest ( empid number, name varchar2(10), orgid number, depid number, vendorid number); insert into sqltest values(1,'BoB',1,1,21); insert into sqltest values(2,'Chuck',1,1,21); insert into sqltest values(3,'Mary',1,2,21); insert into sqltest values(4,'Jane',1,2,22); insert into sqltest values(5,'Rick',2,1,22); insert into sqltest values(6,'Samir',2,6,23); insert into sqltest values(7,'Kirk',3,6,23); insert into sqltest values(8,'Alex',3,9,23); commit;

样品过程

create or replace procedure calltest ( rc1 out SYS_REFCURSOR, pempid number, porgid number, pdepid number, pvendorid number) is begin open rc1 for select * from sqltest where (pempid IS NULL OR empid = pempid) and (porgid IS NULL OR orgid = porgid) and (pdepid IS NULL OR depid = pdepid) and (pvendorid IS NULL OR vendorid = pvendorid); end;

样品通话

set autoprint on; var vcur refcursor; declare begin calltest(rc1=>:vcur,pempid=>NULL,porgid=>1,pdepid=>NULL,pvendorid=>NULL); --calltest(rc1=>:vcur,pempid=>NULL,porgid=>1,pdepid=>2,pvendorid=>NULL); --calltest(rc1=>:vcur,pempid=>NULL,porgid=>1,pdepid=>2,pvendorid=>22); --calltest(rc1=>:vcur,pempid=>NULL,porgid=>NULL,pdepid=>NULL,pvendorid=>23); end;

样本输出1

VCUR ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ EMPID NAME ORGID DEPID VENDORID --------------------------------------- ---------- --------------------------------------- --------------------------------------- --------------------------------------- 1 BoB 1 1 21 2 Chuck 1 1 21 3 Mary 1 2 21 4 Jane 1 2 22

样本输出2

VCUR ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- EMPID NAME ORGID DEPID VENDORID --------------------------------------- ---------- --------------------------------------- --------------------------------------- --------------------------------------- 3 Mary 1 2 21 4 Jane 1 2 22

样本输出3

VCUR ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ EMPID NAME ORGID DEPID VENDORID --------------------------------------- ---------- --------------------------------------- --------------------------------------- --------------------------------------- 4 Jane 1 2 22

样本输出4

VCUR -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- EMPID NAME ORGID DEPID VENDORID --------------------------------------- ---------- --------------------------------------- --------------------------------------- --------------------------------------- 6 Samir 2 6 23 7 Kirk 3 6 23 8 Alex 3 9 23

You can make columns in the query behave as optional, as far as your join's go, you may need to play with various combinations to make sure everything works.

Sample Table

create table sqltest ( empid number, name varchar2(10), orgid number, depid number, vendorid number); insert into sqltest values(1,'BoB',1,1,21); insert into sqltest values(2,'Chuck',1,1,21); insert into sqltest values(3,'Mary',1,2,21); insert into sqltest values(4,'Jane',1,2,22); insert into sqltest values(5,'Rick',2,1,22); insert into sqltest values(6,'Samir',2,6,23); insert into sqltest values(7,'Kirk',3,6,23); insert into sqltest values(8,'Alex',3,9,23); commit;

Sample Proc

create or replace procedure calltest ( rc1 out SYS_REFCURSOR, pempid number, porgid number, pdepid number, pvendorid number) is begin open rc1 for select * from sqltest where (pempid IS NULL OR empid = pempid) and (porgid IS NULL OR orgid = porgid) and (pdepid IS NULL OR depid = pdepid) and (pvendorid IS NULL OR vendorid = pvendorid); end;

Sample Call

set autoprint on; var vcur refcursor; declare begin calltest(rc1=>:vcur,pempid=>NULL,porgid=>1,pdepid=>NULL,pvendorid=>NULL); --calltest(rc1=>:vcur,pempid=>NULL,porgid=>1,pdepid=>2,pvendorid=>NULL); --calltest(rc1=>:vcur,pempid=>NULL,porgid=>1,pdepid=>2,pvendorid=>22); --calltest(rc1=>:vcur,pempid=>NULL,porgid=>NULL,pdepid=>NULL,pvendorid=>23); end;

Sample Output 1

VCUR ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ EMPID NAME ORGID DEPID VENDORID --------------------------------------- ---------- --------------------------------------- --------------------------------------- --------------------------------------- 1 BoB 1 1 21 2 Chuck 1 1 21 3 Mary 1 2 21 4 Jane 1 2 22

Sample Output 2

VCUR ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- EMPID NAME ORGID DEPID VENDORID --------------------------------------- ---------- --------------------------------------- --------------------------------------- --------------------------------------- 3 Mary 1 2 21 4 Jane 1 2 22

Sample Output 3

VCUR ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ EMPID NAME ORGID DEPID VENDORID --------------------------------------- ---------- --------------------------------------- --------------------------------------- --------------------------------------- 4 Jane 1 2 22

Sample Output 4

VCUR -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- EMPID NAME ORGID DEPID VENDORID --------------------------------------- ---------- --------------------------------------- --------------------------------------- --------------------------------------- 6 Samir 2 6 23 7 Kirk 3 6 23 8 Alex 3 9 23

更多推荐