oracle------存储过程（解决自增问题）

作为一个移动端人员，突然有一天让你用到了oracle的存储过程。那是相当的酸爽。下面记录下自己遇到的问题：

先叙述下要做的事情：

我们需要在课程表（T_UP_SCIENCE_SUBJECT20191015）中为满足下面条件的专业插入数学这门课程.

1.业务类型（ywlx）='zz'。

2.状态为打开（status='1'）

3.科目id自增（kmid）

3.没有"数学"这个课程的专业（KMDM='0008'）

于是就有了下面的正确代码：

create or replace procedure mydemo08 is
    i varchar2(100);
    new_index number;
    CURSOR  cursor is select SSKB from T_UP_SCIENCE_SUBJECT
                      where YWLX = 'zz'
                        and STATUS = '1'
                        and SSKB not in(select SSKB from T_UP_SCIENCE_SUBJECT
                                        where KMDM = '0008' and YWLX = 'zz' and STATUS = 
                                                                                     '1')
                      group by SSKB;
begin
    i:= 'select to_number(max(to_number(KMID))) from T_UP_SCIENCE_SUBJECT';
    execute immediate i into new_index;
    for stu in cursor
        loop
            new_index:=new_index+1;
            INSERT INTO "ZZ"."T_UP_SCIENCE_SUBJECT" ("KMID","KMDM", "KMMC", "SSKB", 
                        "SFZKM", "SJKM", "KHFS", "KMLX", "CREATE_USER", "CREATE_DATE", 
                        "LASTMODIFIED_USER", "LASTMODIFIED_DATE", "STATUS", "KMXZFS", 
                        "YWLX", "KMDJ")
            values  (new_index,'0008', '数学', stu.SSKB, '1', null, '2', '2', 
                    'zzsd', TO_DATE('2019-10-15 10:16:09', 'YYYY-MM-DD HH24:MI:SS'), 
                     null, null, '1', '1', 'zz', 0.00);
            commit;
        end loop;

end;


begin
    mydemo08();
end;

 

下面说下遇到的问题：

问题一：触发器没法解决自增

最开始我先到的是写一个触发器，每次insert 前触发加1.于是又了下面的代码:

CREATE OR REPLACE TRIGGER tg_test
    before INSERT ON T_UP_SCIENCE_SUBJECT20191015  FOR EACH ROW
declare
    pragma autonomous_transaction;  
begin
    select max(cast(KMID as int))+1 INTO :new.kmid from T_UP_SCIENCE_SUBJECT20191015;
end;


INSERT INTO "ZZ"."T_UP_SCIENCE_SUBJECT20191015" ("KMDM", "KMMC", "SSKB", "SFZKM", "SJKM", "KHFS", "KMLX", "CREATE_USER", "CREATE_DATE", "LASTMODIFIED_USER", "LASTMODIFIED_DATE", "STATUS", "KMXZFS", "YWLX", "KMDJ")
select  '0008', '数学', SSKB, '1', null, '2', '2', 'zzsd', TO_DATE('2019-10-15 10:16:09', 'YYYY-MM-DD HH24:MI:SS'), null, null, '1', '1', 'zz', 0.00
from T_UP_SCIENCE_SUBJECT20191015
where YWLX = 'zz'
    and STATUS = '1'
    and SSKB not in(select SSKB from T_UP_SCIENCE_SUBJECT20191015
    where KMDM = '0008' and YWLX = 'zz' and STATUS = '1')
group by SSKB;

drop trigger  tg_test;

发现如果是一条一条的插入这样是KMID是自增的，但如果像上面代码一样插入一个结果集，发现只会自增一次。因为触发器没有循环，所以选择了存储过程来解决这个问题。但是不是很顺利

下面是存储过程的问题

问题二：

i:=(select to_number(max(to_number(KMID))) from T_UP_SCIENCE_SUBJECT20191015);

最开始我是这么给i赋值的导致问题

<一个带有字符集说明的可带引号的字符串文字>
<一个可带引号的 
4:81:PLS-00103: Encountered the symbol ")" when expecting one of the following:

提示了第4行的遇到了一个可带引号的符号 ”）“问题然后去查阅发现自己写错了正确的

 i:= 'select to_number(max(to_number(KMID))) from T_UP_SCIENCE_SUBJECT20191015';

但是执行时，报错：

[65000][6502] ORA-06502: PL/SQL: 数字或值错误 : character to number conversion error ORA-06512: 在 "ZZ.MYDEMO09", line 4 ORA-06512: 在 line 2 Position: 0

上面语句的i 不是number型的于是又加了

    i varchar2(100);
    new_index number;
    begin
    i:= 'select to_number(max(to_number(KMID))) from T_UP_SCIENCE_SUBJECT20191015';  
    execute immediate i into new_index;