CREATE OR REPLACE PROCEDURE "CSFrameworkV6_Normal"."_Test_Product_Search" 
(
	productCode IN VARCHAR(50)=null,
	productName IN VARCHAR(50)=null,
	category IN VARCHAR(50)=null,
	barcode IN VARCHAR(50)=null,
    result1 OUT SYS_REFCURSOR
)
AS
BEGIN
	-- 打开第一个结果集
	OPEN result1 FOR SELECT top 10 * FROM "dt_Product" WHERE 1=1;

	-- 打开第二个结果集
    --	OPEN result2 FOR SELECT top 10 * FROM 表;
END;

CREATE OR REPLACE PROCEDURE "CSFrameworkV6_Normal"."_Test_INV_Search" 
(
	INVNO IN VARCHAR(50)=null,
	CustomerCode IN VARCHAR(50)=null,
	PayType IN VARCHAR(50)=null,
	barcode IN VARCHAR(50)=null,
    result1 OUT SYS_REFCURSOR,
    result2 OUT SYS_REFCURSOR  
)
AS
BEGIN
	-- 打开第一个结果集
	OPEN result1 FOR SELECT top 10 * FROM "tb_IV" WHERE 1=1;

	-- 打开第二个结果集
    OPEN result2 FOR SELECT top 100 * FROM "tb_IVs" WHERE "IVNO" IN (SELECT TOP 10 "IVNO" FROM "tb_IV");
    
END;

CREATE OR REPLACE PROCEDURE "CSFrameworkV6_Normal"."usp_EmployeeQueryReportData" 
(
	InfoID IN VARCHAR(100),
    result1 OUT SYS_REFCURSOR,
    result2 OUT SYS_REFCURSOR  
)
AS
BEGIN
	
	OPEN result1 FOR 
	SELECT e.*,
	    ec."ContractNo",
	    ec."ValidateDateBegin",
	    ec."ValidateDateEnd",
		epa."DeptAfter",--最新部门			
		DATEDIFF(DAY,e."Birthday",SYSDATE) AS Ages, --年龄
		DATEDIFF(DAY,e."EntryFactoryDate",SYSDATE) AS WorkYears --连续工龄
    FROM "dt_Employee" as e
    left join "dt_EmployeeContract" as ec on e."InfoID"=ec."InfoID"
	left join "dt_EmployeePositionAdjustment" as epa on e."InfoID"=epa."InfoID"
	WHERE e."InfoID"=InfoID
	ORDER BY ec."CreationDate",epa."CreationDate" DESC;
		
	OPEN result2 FOR SELECT * FROM "dt_EmployeeContract" WHERE "InfoID"=InfoID;		

END;