看看存储过程建立有错

shahand 2003-04-15 05:01:17

SQL> ed
Wrote file afiedt.buf

1 insert into yk_against_promise_stat (register_no,the_days)
2 select register_no,
3 (select count(*) from dual)
4 from
5 yk_job_info
6* where 1 = 2
SQL> /

0 rows created.

SQL> ed
Wrote file afiedt.buf

1 create or replace function f_test(p_char char)
2 return number
3 is
4 begin
5 insert into yk_against_promise_stat (register_no,the_days)
6 select register_no,
7 (select count(*) from dual)
8 from
9 yk_job_info
10 where 1 = 2
11 ;
12 return 1;
13* end;
SQL> /

Warning: Function created with compilation errors.

SQL> show err
Errors for FUNCTION F_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/7 PLS-00103: Encountered the symbol "SELECT" when expecting one of
the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute cast trim forall
<a string literal with character set specification>
<a number> <a single-quoted SQL string>

8/3 PLS-00103: Encountered the symbol "FROM" when expecting one of
the following:
<an identifier> <a double-quoted delimited-identifier> set

12/1 PLS-00103: Encountered the symbol "RETURN"

...全文

38 13 打赏收藏转发到动态举报

写回复

用AI写文章

13 条回复

切换为时间正序

请发表友善的回复…

发表回复

shahand 2003-04-16

打赏
举报

多谢，空杯，我试一试，如果成功另有分
多谢

Lastdrop 2003-04-16

打赏
举报

insert into B (register_no , the_days)
( select A.register_no, count(*)
from
(select register_no,pass_time from yk_app_pass_info where tache_code = '105' ) A,
(select register_no,pass_time from yk_app_pass_info where tache_code = '059' ) B,
yk_work_day C
where A.register_no = B.register_no AND c.the_date between b.pass_time and a.pass_time
group by a.register_no
);

这样应该可以满足你的要求。

shahand 2003-04-15

打赏
举报

不是的，简化问题以后，有三个表
yk_work_day （work_day）（工作日表，其实就是把每一天date插入，
然后删除掉那些周末和假期）
B表登记号(pk),天数(number) （统计表）
yk_app_pass_info （登记号、环节号、传递时间）

现在向统计表插入（登记号，天数）这个天数是每个登记号两个特定环节之间的工作日数

beckhambobo 2003-04-15

打赏
举报

是否想要这种效果:
insert into B (register_no , the_days)
select register_no,(select count(1) from yk_work_day where register_no=a.register_no) from yk_work_day a where tache_code between '059' and '105';

shahand 2003-04-15

打赏
举报

jiezhi(西域浪子):
我是在存储过程中间出错的啊
你是让我用cast转换一下select count(*)...吗？
我试了，as number(3)错误没有任何变化！

jiezhi 2003-04-15

打赏
举报

select * from test

DATE SL CONTENT2 CONTENT3
---------- ----------- -------------------- --------------------
2002-01-01 50 - -
2002-01-02 60 - -
2002-01-05 70 - -

insert into test(content2,content3)
select cast(sl as char(10)),
cast ((select count(*) from test) as char(10))
from test

DATE SL CONTENT2 CONTENT3
---------- ----------- -------------------- --------------------
2002-01-01 50 - -
2002-01-02 60 - -
2002-01-05 70 - -
- - 50 3
- - 60 3
- - 70 3

shahand 2003-04-15

打赏
举报

因为这个子查询要以A表为条件取数据，所以我才这样写的
语句原型是下面这样的，我一句一句调，才发现上面那个错误的
（功能：向b表中插入每个登记号两个环节'105''059'传递时间之间的工作日(yk_work_day)天数）
insert into B (register_no , the_days)
select A.register_no,
(select count(*) from yk_work_day where the_date between b.pass_time and a.pass_time )
from
(select register_no,pass_time from yk_app_pass_info where tache_code = '105' ) A,
(select register_no,pass_time from yk_app_pass_info where tache_code = '059' ) B
where A.register_no = B.register_no
;

Lastdrop 2003-04-15

打赏
举报

不知道，这样的用法也是在比较高的版本的Oracle才支持的，在PL/SQL中有可能不支持，不过，你也不用很急的，因为这样的SQL往往都能通过子查询实现！

insert into yk_against_promise_stat (register_no,the_days)
( select register_no,a.count1
from yk_job_info,(select count(*) count1 from dual) a
where 1 = 2
);