V_DN IN VARCHAR2,
V_START_DATE IN VARCHAR2,
V_END_DATE IN VARCHAR2,
V_SQL := 'selct * FROM XXX WHERE ';IF LENGTH(V_DN) <> 0 THEN
V_SQL := V_SQL || ' DN = ' || V_DN;
END IF;
IF LENGTH(V_START_DATE ) <> 0 AND LENGTH(V_END_DATE) <> 0THEN
V_SQL := V_SQL || ' AND DAY BETWWEN TO_DATE(' || V_START_DATE ||',''YYYY-MM-DD'') AND TO_DATE(' || V_END_DATE;||',''YYYY-MM-DD'')';第二次连接时候就报错了
这个连接符只能连接一次么,那这里应该怎么使用
V_START_DATE IN VARCHAR2,
V_END_DATE IN VARCHAR2,
V_SQL := 'selct * FROM XXX WHERE ';IF LENGTH(V_DN) <> 0 THEN
V_SQL := V_SQL || ' DN = ' || V_DN;
END IF;
IF LENGTH(V_START_DATE ) <> 0 AND LENGTH(V_END_DATE) <> 0THEN
V_SQL := V_SQL || ' AND DAY BETWWEN TO_DATE(' || V_START_DATE ||',''YYYY-MM-DD'') AND TO_DATE(' || V_END_DATE;||',''YYYY-MM-DD'')';第二次连接时候就报错了
这个连接符只能连接一次么,那这里应该怎么使用
解决方案 »
- 求个思路 关于分组函数的 sql 在线等!
- linux 安装oracle 参数提示PORT的值NULL无效
- 怎样将execl数据导入oracle中
- 求一个使用Oracle数据库的Web项目的实施思路
- 求oracle 9i高级复制技术文档
- 简单sql,送分交友型
- oracle中有没有类似于SQL Server中的if和exists命令
- 菜鸟哭求一个SQL语句!!!对于高手来说是很容易.....
- oracle 9i 在2000服务器上装不了,是不是要补订?
- 请问一个简单问题。我执行了一个select的动态sql后,在sqlplus中exec这个过程,为什么没有记录输出呢?
- utl_file怎么指定导出excel的那个Sheet1的单元格格式全为文本型的?
- ora-14452 试图创建,更改或删除正在使用的临时表中的索引
V_END_DATE後面的分號是錯誤所在,去除它吧
V_SQL := V_SQL || ' AND DAY BETWWEN TO_DATE(' || V_START_DATE ||',''YYYY-MM-DD'') AND TO_DATE(' || V_END_DATE ||',''YYYY-MM-DD'')';
V_SQL := V_SQL || ' AND DAY BETWWEN TO_DATE('' V_START_DATE '',''YYYY-MM-DD'') AND TO_DATE('' V_END_DATE'',''YYYY-MM-DD'')'
V_DN IN VARCHAR2,
V_SCHOOL_NAME IN VARCHAR2,
V_START_DAY IN VARCHAR2,
V_END_DAY IN VARCHAR2,
V_QUERY_ID_SQL VARCHAR2(300);
V_QUERY_ID_SQL := 'SELECT TCPT.TEMP_ID FROM TM_PARENT TCPT, TO_SCHOOL_ORG TSO, TU_STUDENT TS, TO_SCHOOL TSCH, TO_AGENT_AREA TAA WHERE TCPT.STUDENT_ID = TS.STUDENT_ID AND TSCH.ORG_ID = TS.SCHOOL_ORG AND TSO.ORG_ID = TS.ORG_ID AND TSCH.AGENT_AREA_BOSS = TAA.ORG_ID AND TCPT.AUDIT_STATE=2 ';
IF length(V_SCHOOL_NAME) <> 0 THEN
V_QUERY_ID_SQL := V_QUERY_ID_SQL || ' AND TSCH.Name_Chi LIKE %' || V_SCHOOL_NAME || '%';
END IF;
IF length(V_START_DAY) <> 0 and length(V_END_DAY) <> 0 THEN
V_QUERY_ID_SQL := V_QUERY_ID_SQL || 'AND TCPT.UPLOAD_DATE BETWEEN TO_DATE(' || V_START_DAY || ',''YYYY-MM-DD'') AND TO_DATE(' || V_END_DAY || ',''YYYY-MM-DD'') + 1';
END IF;
IF LENGTH(V_DN) <> 0 THEN
V_QUERY_ID_SQL := V_QUERY_ID_SQL || ' AND TCPT.DN LIKE %' || V_DN || '%';
END IF;前面是这一段,走到日期那一段就跳到exception了
V_DN IN VARCHAR2,
V_SCHOOL_NAME IN VARCHAR2,
V_START_DAY IN VARCHAR2,
V_END_DAY IN VARCHAR2,
V_QUERY_ID_SQL VARCHAR2(300);
V_QUERY_ID_SQL := 'SELECT TCPT.TEMP_ID FROM TM_PARENT TCPT, TO_SCHOOL_ORG TSO, TU_STUDENT TS, TO_SCHOOL TSCH, TO_AGENT_AREA TAA WHERE TCPT.STUDENT_ID = TS.STUDENT_ID AND TSCH.ORG_ID = TS.SCHOOL_ORG AND TSO.ORG_ID = TS.ORG_ID AND TSCH.AGENT_AREA_BOSS = TAA.ORG_ID AND TCPT.AUDIT_STATE=2 ';
IF length(V_SCHOOL_NAME) <> 0 THEN
V_QUERY_ID_SQL := V_QUERY_ID_SQL || ' AND TSCH.Name_Chi LIKE ''%' || V_SCHOOL_NAME || '%''';
END IF;
IF length(V_START_DAY) <> 0 and length(V_END_DAY) <> 0 THEN
V_QUERY_ID_SQL := V_QUERY_ID_SQL || 'AND TCPT.UPLOAD_DATE BETWEEN TO_DATE(' || V_START_DAY || ',''YYYY-MM-DD'') AND TO_DATE(' || V_END_DAY || ',''YYYY-MM-DD'') + 1';
END IF;
IF LENGTH(V_DN) <> 0 THEN
V_QUERY_ID_SQL := V_QUERY_ID_SQL || ' AND TCPT.DN LIKE ''%' || V_DN || '%''';
END IF;
pageSize in number,
currentPage in number,
rowCount out number,
pageCount out number,
resultCursor out sys_refcursor) is
v_sql varchar2(1000);
v_begin number := pageSize * (currentPage - 1);
v_end number := pageSize * currentPage;
begin
v_sql := 'select * from (select t.*,rownum rn from ' || tableName ||
' t where rownum<=' || v_end || ') where rn>' || v_begin;
open resultCursor for v_sql;
v_sql := 'select count(*) from ' || tableName;
execute immediate v_sql
into rowCount;
pageCount := ceil(rowCount / pageSize);
end;是不是你存储过程格式不对.给你个例子.
create or replace procedure P_PARENT_ONE2N_BATCH_AUDIT(V_DN IN VARCHAR2,
V_SCHOOL_NAME IN VARCHAR2,
V_START_DAY IN VARCHAR2,
V_END_DAY IN VARCHAR2,
V_RETTEXT OUT VARCHAR2) is V_QUERY_ID_SQL VARCHAR2(300);
V_DNS VARCHAR2(20);
V_USER_NAME VARCHAR2(100);
V_STUDENT_ID NUMBER;
V_RELATION VARCHAR2(20);
V_LINK_DN VARCHAR2(20);
V_ADDR VARCHAR2(100);
V_REMARK VARCHAR2(100);
V_PARENT_ID NUMBER;
V_RETURN NUMBER;
BEGIN
V_QUERY_ID_SQL := 'SELECT TCPT.TEMP_ID FROM TM_PARENT TCPT, TO_SCHOOL_ORG TSO, TU_STUDENT TS, TO_SCHOOL TSCH, TO_AGENT_AREA TAA WHERE TCPT.STUDENT_ID = TS.STUDENT_ID AND TSCH.ORG_ID = TS.SCHOOL_ORG AND TSO.ORG_ID = TS.ORG_ID AND TSCH.AGENT_AREA_BOSS = TAA.ORG_ID AND TCPT.AUDIT_STATE=2 ';
IF length(V_SCHOOL_NAME) <> 0 THEN
V_QUERY_ID_SQL := V_QUERY_ID_SQL || ' AND TSCH.Name_Chi LIKE %' || V_SCHOOL_NAME || '%';
END IF;
IF length(V_START_DAY) <> 0 and length(V_END_DAY) <> 0 THEN
V_QUERY_ID_SQL := V_QUERY_ID_SQL || ' AND TCPT.UPLOAD_DATE BETWEEN TO_DATE(' || V_START_DAY || ',''YYYY-MM-DD'') AND TO_DATE(' || V_END_DAY || ',''YYYY-MM-DD'') + 1';
END IF;
IF LENGTH(V_DN) <> 0 THEN
V_QUERY_ID_SQL := V_QUERY_ID_SQL || ' AND TCPT.DN LIKE ''%' || V_DN || '%''';
END IF;
EXCEPTION
WHEN OTHERS THEN
V_RETTEXT:= '1';
END P_PARENT_ONE2N_BATCH_AUDIT;
我把多余的删掉了,请大大帮助把这一段调试一下