Question

SQL SERVER 存储过程，转换成ORACLE存储过程

CREATE PROCEDURE [dbo].[getMaterialCC]
@sumyear varchar(50),
@wellname varchar(50)
AS
BEGIN
select ''+@sumyear+'' as 年月,c.countname as 分摊方式,d.departmentname as 库存地点,ROUND(SUM(a.sysl*a.dj),1) as 库存费用,ROUND(SUM(a.je),1) as 总费用 from Material_Cyz a left join costcounttype c on a.counttype=c.counttype left join Department d on a.kcdd=d.departmentid where SUBSTRING(a.ny,1,4)=@sumyear group by kcdd,c.countname,d.departmentname
END
这是sql server写的存储过程，执行此存储过程，结果是：

如何把此存储过程，转换为oracle存储过程，让结果同样是这样的？

Answer 1

CREATE OR REPLACE PROCEDURE GETMATERIALCC(sumyear  varchar2,
                                          wellname varchar2,
                                          OUT_CUR  OUT SYS_REFCURSOR) AS
  STRSQL VARCHAR2(4000);
BEGIN
  STRSQL := 'select ''' || sumyear || ''' as 年月,c.countname as 分摊方式,d.departmentname as 库存地点,
  ROUND(SUM(a.sysl*a.dj),1) as 库存费用,ROUND(SUM(a.je),1) as 总费用 from Material_Cyz a  
  left join costcounttype c on  a.counttype=c.counttype 
  left join Department d on a.kcdd=d.departmentid 
  where SUBSTR(a.ny,1,4)=''' || sumyear ||
            ''' group by kcdd,c.countname,d.departmentname';
  OPEN OUT_CUR FOR STRSQL;
END GETMATERIALCC;