SQL存储过程如下,晚上得为这些加班遇到这个不知如何转成Oracle存储过程set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[Report_Templet_SelectByfilter]
@DepartmentID int,
@Term varchar(50),
@Templet_year nvarchar(4)
AS
DECLARE @strDept nvarchar(100)
DECLARE @strTerm nvarchar(100)
DECLARE @strTemplet_year nvarchar(100)
DECLARE @SQL nvarchar(4000)
IF @DepartmentID = 0
SET @strDept = '1=1';
ELSE
--SET @strDept ='departmentID='+CONVERT(nvarchar(10),@DepartmentID);
SET @strDept ='templetid in (select templetid from report_tempdepart where departmentID= '+CONVERT(nvarchar(10),@DepartmentID) + ')';
IF @Term = ''
SET @strTerm = '1=1';
ELSE
SET @strTerm ='Term='''+@Term+'''';
IF @Templet_year = ''
SET @strTemplet_year = '1=1';
ELSE
SET @strTemplet_year ='Templet_year>='''+@Templet_year+'''';
SET @SQL ='
select Report_Templet.*,DEPARTMENT.NAME as Department_name from Report_Templet
LEFT JOIN DEPARTMENT
ON Report_Templet.departmentID = DEPARTMENT.DEPT_ID
where '+@strDept+' and '+@strTerm+' and '+@strTemplet_year+ ' and Report_Templet.del=0'+
'order by Report_Templet.viewindex,Report_Templet.templetID'
PRINT @SQL;
EXECUTE(@SQL)
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[Report_Templet_SelectByfilter]
@DepartmentID int,
@Term varchar(50),
@Templet_year nvarchar(4)
AS
DECLARE @strDept nvarchar(100)
DECLARE @strTerm nvarchar(100)
DECLARE @strTemplet_year nvarchar(100)
DECLARE @SQL nvarchar(4000)
IF @DepartmentID = 0
SET @strDept = '1=1';
ELSE
--SET @strDept ='departmentID='+CONVERT(nvarchar(10),@DepartmentID);
SET @strDept ='templetid in (select templetid from report_tempdepart where departmentID= '+CONVERT(nvarchar(10),@DepartmentID) + ')';
IF @Term = ''
SET @strTerm = '1=1';
ELSE
SET @strTerm ='Term='''+@Term+'''';
IF @Templet_year = ''
SET @strTemplet_year = '1=1';
ELSE
SET @strTemplet_year ='Templet_year>='''+@Templet_year+'''';
SET @SQL ='
select Report_Templet.*,DEPARTMENT.NAME as Department_name from Report_Templet
LEFT JOIN DEPARTMENT
ON Report_Templet.departmentID = DEPARTMENT.DEPT_ID
where '+@strDept+' and '+@strTerm+' and '+@strTemplet_year+ ' and Report_Templet.del=0'+
'order by Report_Templet.viewindex,Report_Templet.templetID'
PRINT @SQL;
EXECUTE(@SQL)
解决方案 »
- oracle
- ORA-12514 TNS 无法处理服务名 问题 !请帮忙
- 定义的变量值为null
- 帮忙看个procedure为什么错了及如何修改,关于动态sql的
- 关于Oracle的序列问题,请各位大虾们帮忙,高分相送
- 看遍以前的帖子,我的问题依旧没有解决,求助!
- 在ORACLE 8i中进入SQLPLUS登陆后出现ORA-12547:TNS:丢失联系错误;进入DBA后连接数据库时根本没响应。
- 如何存取LONG RAW字段?谢谢
- Get the last position of sub-string ?
- 好奇怪 !
- 编译Oracle自带的occi例子,可以通过,但是一运行就core dump
- c/c++如何访问oracle数据库?
(p_DepartmentID INTEGER,
p_Term VARCHAR2,
p_Templet_year VARCHAR2)
AS
v_strDept VARCHAR2(100);
v_strTerm VARCHAR2(100);
v_strTemplet_year VARCHAR2(100);
v_SQL VARCHAR2(4000);
BEGIN
IF (p_DepartmentID = 0)
THEN
v_strDept := '1=1';
ELSE
v_strDept :='templetid in (select templetid from report_tempdepart where departmentID= '||TO_CHAR(p_DepartmentID) || ')';
END IF;
DBMS_OUTPUT.PUT_LINE(v_strDept);
IF (p_Term IS NULL)
THEN
v_strTerm := '1=1';
ELSE
v_strTerm :='Term='''|| p_Term||'''';
END IF;
DBMS_OUTPUT.PUT_LINE(v_strTerm);
IF (p_Templet_year IS NULL)
THEN
v_strTemplet_year := '1=1';
ELSE
v_strTemplet_year :='Templet_year>='''|| p_Templet_year||'''';
END IF;
DBMS_OUTPUT.PUT_LINE(v_strTemplet_year);
v_SQL :='
SELECT Report_Templet.*,DEPARTMENT.NAME AS Department_name FROM Report_Templet
LEFT JOIN DEPARTMENT
ON Report_Templet.departmentID = DEPARTMENT.DEPT_ID
WHERE '|| v_strDept||' AND '||v_strTerm||' AND '||v_strTemplet_year|| ' AND Report_Templet.del=0 '||
'order by Report_Templet.viewindex,Report_Templet.templetID';
DBMS_OUTPUT.PUT_LINE(SUBSTRB(v_sql,1,255));
DBMS_OUTPUT.PUT_LINE(SUBSTRB(v_sql,256));
--execute immediate
END ll_RTS;--
你要是想返回记录集要用游标的方式
可以把VARCHAR2换成NVARCHAR2