--函数的功能是想实现传入一个日期和值然后统计该年到该天的这列的值
create or replace function f_tol(v_date in date,v_value in varchar2,v_type in char)
return number
as
num number;
v_sql varchar2(2000);
begin
if (v_type = 'Y') then
v_sql:='select sum('||v_value||') from t03_business_letter_sum
where open_invoice between trunc('||v_date||',''yyyy'') and '||v_date;
execute immediate v_sql into num; -- 缺失右括号,在line10
end if;
return num;
end;请各位帮忙看看是哪写错啦?谢谢
create or replace function f_tol(v_date in date,v_value in varchar2,v_type in char)
return number
as
num number;
v_sql varchar2(2000);
begin
if (v_type = 'Y') then
v_sql:='select sum('||v_value||') from t03_business_letter_sum
where open_invoice between trunc('||v_date||',''yyyy'') and '||v_date;
execute immediate v_sql into num; -- 缺失右括号,在line10
end if;
return num;
end;请各位帮忙看看是哪写错啦?谢谢
解决方案 »
- oracle查询能将查询的某个字段特别显示数据吗
- oracle xmltype的update问题,急问!
- 高手请进: Oracle的索引能不能在叶子级包含非键值列?
- ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务
- [性能优化] 关于ASH报告中SQL*Net more data from client的问题
- oracle8i数据库系统安装是在哪儿设置字符集?
- 递归返回查询中sql语句问题,谢谢
- 用EXP远程备份怎么用?
- 求救:关于ORA-12545的解决方法
- 一个Oracle中的简单的问题,今天晚上结帐!
- pro *c 项目迁移到另一个服务器后不能够编译
- Mysql内置函数对应Oracle的内置函数
4楼的加上了dbms_output.put_line(v_sql); 但是什么也没输出来,有debug any procedure的权限
scott@TBWORA> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10已选择14行。scott@TBWORA> create or replace function f_tol(v_date in date,v_value in varchar2,v_type in char)
2 return number
3 is
4 v_num number(18,0);
5 v_sql varchar2(2000);
6 begin
7 if (v_type='Y') then
8 v_sql := 'select sum('||v_value||') from emp where hiredate between trunc(:v_date1,''yyyy'') and :v_date2';
9 execute immediate v_sql into v_num using v_date, v_date;
10 end if;
11 return v_num;
12 end;
13 /函数已创建。scott@TBWORA> select f_tol(to_date('1981-12-31','yyyy-mm-dd'),'sal','Y') from dual;F_TOL(TO_DATE('1981-12-31','YYYY-MM-DD'),'SAL','Y')
---------------------------------------------------
22825
--函数的功能是想实现传入一个日期和值然后统计该年到该天的这列的值
create or replace function f_tol(v_date in date,v_value in varchar2,v_type in char)
return number
as
num number(18,0);
v_sql varchar2(2000);
begin
if (v_type = 'Y') then
v_sql:='select sum('||v_value||') from t03_business_letter_sum
where open_invoice between trunc(:v_date1,''yyyy'') and :v_date2';
execute immediate v_sql into num using v_date, v_date; -- 缺失右括号,在line10
end if;
return num;
end;
/