ORACLE存储过程

[qq_26654275]

 BEGIN
         DELETE FROM tt_advice_detail;
         --循环获取当前病种当前阶段的所有非并发症医嘱明细
         INSERT INTO tt_advice_detail
           SELECT * ,
                  '1' || aaa.MARK + aaa.ADVICE_NAME advice_name_group  
             FROM ( SELECT (CASE advicecount
                                            WHEN '1' THEN ' '
                           ELSE CASE serial
                                           WHEN 1 THEN '1'
                                           WHEN advicecount THEN '2'
                           ELSE '3'
                              END
                              END) MARK  ,
                           MAIN_ID ,
                           stepnum ,
                           ADVICE_ID ,
                           ADVICE_NAME ,
                           aa.ORDER_MAIN_ID ,
                           aa.SERIALNUM ,
                           aa.NODE_ID ,
                           aa.TYPE ,
                           aa.ISOPTIONAL 
                    FROM ( SELECT COUNT(D.stepnum) OVER ( PARTITION BY ORDER_MAIN_ID ) advicecount  ,
                                  ROW_NUMBER() OVER ( PARTITION BY ORDER_MAIN_ID ORDER BY ORDER_MAIN_ID  ) serial  ,
                                  MAIN_ID ,
                                  D.stepnum ,
                                  ADVICE_ID ,
                                  ADVICE_NAME ,
                                  ORDER_MAIN_ID ,
                                  D.SERIALNUM ,
                                  D.NODE_ID ,
                                  D.TYPE ,
                                  D.ISOPTIONAL 
                           FROM LIB_ZYCP.DISEASE_DOCTOR a
                                  JOIN LIB_ZYCP.DISEASE_DOCTOR_MAIN D   ON a.MAIN_ID = D.ID
                                  LEFT JOIN LIB_ZYCP.DISEASE_NODE b   ON D.NODE_ID = b.ID
                                  AND D.SERIALNUM = b.SERIALNUM
                            WHERE  a.FLAG_INVALID = 0
                                     AND b.SERIALNUM = v_serialnum
                                     AND b.DISEASE_CODE = v_icdcode
                                     AND D.FLAG_INVALID = 0
                                     AND b.ISCOMPLICATION = 0
                                     AND b.FLAG_INVALID = 0 ) aa ) aaa;

这里显示未找到要求的from关键字

  BEGIN
               WITH nrow ( rn ) AS ( SELECT 1  FROM DBO_CONFIG 
               UNION ALL 
               SELECT rn + 1 
                 FROM nrow 
                WHERE  rn + 1 <= v_maxrn ) 
                  INSERT INTO tt_comalladvice_2
                    ( rn, jobtype, serialnum1, orderNum1, jobType1, adviceType1, adviceName1, isComplete1, remark1, BEGINCURSERIALTIME1, serialnum2, orderNum2, jobType2, adviceType2, adviceName2, isComplete2, remark2, BEGINCURSERIALTIME2 )
               ( SELECT v_serialnum2 ,
                             (CASE 
                                   WHEN a.jobTypesm IS NULL THEN b.jobTypesm
                             ELSE a.jobTypesm
                                END) ,
                             a.serialnum ,
                             a.orderNum ,
                             a.jobType ,
                             a.adviceType ,
                             a.adviceName ,
                             a.isComplete ,
                             a.remark ,
                             a.BEGINCURSERIALTIME ,
                             b.serialnum ,
                             b.orderNum ,
                             b.jobType ,
                             b.adviceType ,
                             b.adviceName ,
                             b.isComplete ,
                             b.remark ,
                             b.BEGINCURSERIALTIME 
                      FROM nrow M
                             LEFT JOIN ( SELECT rn ,
                                                 jobTypesm  ,
                                                serialnum ,
                                                orderNum ,
                                                jobType ,
                                                adviceType ,
                                                adviceName ,
                                                isComplete ,
                                                remark ,
                                                BEGINCURSERIALTIME 
                                         FROM tt_mm_2 
                                          WHERE  serialnum = v_serialnum2
                                                   AND jobType = v_jobType
                                                   AND adviceType = v_adviceType ) a   ON M.rn = a.rn
                             LEFT JOIN ( SELECT rn ,
                                                 jobTypesm  ,
                                                serialnum ,
                                                orderNum ,
                                                jobType ,
                                                adviceType ,
                                                adviceName ,
                                                isComplete ,
                                                remark ,
                                                BEGINCURSERIALTIME 
                                         FROM tt_mm_2 
                                          WHERE  serialnum = v_serialnum2 + 1
                                                   AND jobType = v_jobType
                                                   AND adviceType = v_adviceType ) b   ON M.rn = b.rn );

这里显示缺失select关键字

[qq_26654275]

引用 1 楼 wmxcn2000 的回复:

create table t1(id int, name varchar(30));
create table t2(id int, name varchar(30), rn int);

begin
end;
/
insert into t2
-- 这里要写上表的前辍，不能直接写 *
select t1.*, row_number() over(order by id) rn
from t1;

drop table t1 purge;
drop table t2 purge;

还真是这个原因，起别名前面的*忘记加上别名了，谢谢指导，后面的缺失SELECT关键字能帮忙看下什么问题嘛

[卖水果的net]

with 块后面，只能用 select 不能用 insert ，你调整理一下。

[qq_26654275]

引用 3 楼 wmxcn2000 的回复:

with 块后面，只能用 select 不能用 insert ，你调整理一下。

大神，能详细说下嘛？刚接触存储过程这块，不是很懂

[卖水果的net]

create table test(id int, name varchar(10));
-- 改成这样的。
insert into test
with m(id, name) as (
    select 1 , 'xxx' from dual
    union all
    select 2, 'yyy' from dual
)
select id, name from m;

select * from test;

drop table test purge;

[卖水果的net]

create table t1(id int, name varchar(30));
create table t2(id int, name varchar(30), rn int);

begin
end;
/
insert into t2
-- 这里要写上表的前辍，不能直接写 *
select t1.*, row_number() over(order by id) rn
from t1;

drop table t1 purge;
drop table t2 purge;

[verejava]

​PLSQL 存储过程 详解 http://www.verejava.com/?id=17173794602255

[qq_26654275]

引用 5 楼 wmxcn2000 的回复:

create table test(id int, name varchar(10));
-- 改成这样的。
insert into test
with m(id, name) as (
    select 1 , 'xxx' from dual
    union all
    select 2, 'yyy' from dual
)
select id, name from m;

select * from test;

drop table test purge;

谢谢指导，最近又看了看，了解了很多

[qq_26654275]

引用 6 楼 verejava 的回复:

​PLSQL 存储过程 详解 http://www.verejava.com/?id=17173794602255

谢谢分享的内容