关于Oracle数据库存储过程的问题

发布网友 发布时间：2022-04-22 12:54

我来回答

共2个回答

懂视网 时间：2022-05-02 20:59

测试表 CREATE TABLE TEST20181207 ( ID INTEGER PRIMARY KEY, FUND NUMBER,--上日资金 BALANCE NUMBER,--本日资金 CDATE VARCHAR2(10) );

添加测试数据:

--添加测试数据
INSERT INTO TEST20181207 VALUES(1,100,200,‘2018-10-31‘);
INSERT INTO TEST20181207 VALUES(2,100,200,‘2018-11-01‘);
INSERT INTO TEST20181207 VALUES(3,200,0,‘2018-11-03‘);
INSERT INTO TEST20181207 VALUES(4,0,100,‘2018-11-10‘);
INSERT INTO TEST20181207 VALUES(5,100,0,‘2018-11-20‘);
INSERT INTO TEST20181207 VALUES(6,10,100,‘2018-11-10‘);
INSERT INTO TEST20181207 VALUES(7,100,0,‘2018-11-20‘);
COMMIT;

创建存储过程:

CREATE OR REPLACE PROCEDURE TESTSELECT20181207
(I_START_DATE VARCHAR2,
I_END_DATE VARCHAR2)
IS
 T_ID1 INTEGER;
 T_FUND1 NUMBER;
 T_BALANCE1 NUMBER;
 T_CDATE1 VARCHAR2(10);
 T_ID2 INTEGER;
 T_FUND2 NUMBER;
 T_BALANCE2 NUMBER;
 T_CDATE2 VARCHAR2(10);
 CURSOR CURSOR1 IS
 SELECT ID,FUND,BALANCE,CDATE FROM TEST20181207
 WHERE CDATE BETWEEN I_START_DATE AND I_END_DATE;
 CURSOR CURSOR2(T_CDATE VARCHAR2) IS
 SELECT ID,FUND,BALANCE,CDATE FROM TEST20181207
 WHERE CDATE = (
 SELECT MIN(CDATE) FROM TEST20181207
 WHERE CDATE > T_CDATE
 )
 AND CDATE BETWEEN I_START_DATE AND I_END_DATE;
BEGIN
 OPEN CURSOR1;
 LOOP
 FETCH CURSOR1 INTO T_ID1,T_FUND1,T_BALANCE1,T_CDATE1;
 EXIT WHEN CURSOR1%NOTFOUND;
 OPEN CURSOR2(T_CDATE1);
 LOOP
 FETCH CURSOR2 INTO T_ID2,T_FUND2,T_BALANCE2,T_CDATE2; 
 EXIT WHEN CURSOR2%NOTFOUND;
 IF T_FUND2 <> T_BALANCE1 THEN
  DBMS_OUTPUT.PUT_LINE(‘编号1:‘||T_ID1||‘,本日资金1:‘||T_BALANCE1||‘,日期1:‘||T_CDATE1);
  DBMS_OUTPUT.PUT_LINE(‘编号2:‘||T_ID2||‘,上日资金2:‘||T_FUND2||‘,日期2:‘||T_CDATE2);
 END IF;
 END LOOP;
 CLOSE CURSOR2; 
 END LOOP;
 CLOSE CURSOR1;
END TESTSELECT20181207;
/

最后一个/在同时执行创建多个存储过程是必须的,/代表一个存储过程代码的结尾(结束).

调用存储过程:

CALL TESTSELECT20181207(‘2018-10-31‘,‘2018-11-30‘);

运行结果:

好了,就这样了.

Oracle数据库存储过程练习20181212

标签：存储   执行   open   exit   where   end   oracl   .com   not   

热心网友 时间：2022-05-02 18:07

create or replace procere proc_a
as
begin
    for c1 in 1 .. 200 loop
        insert into table_A select trunc(dbms_random.value(0,1000)) from al;
    end loop;
    commit;
    exception
        when others then
        rollback;
end;

create or replace procere proc_calla
as
begin
    proc_a;
    for c1 in (select * from table_A) loop
        dbms_output.put_line(c1.字段名);
    end loop;
    exception
        when others then
         dbms_output.put_line('出错啦！');
end;
create or replace procere proc_callerror
as
app_exp exception;
begin
    if 1>2 then
        rsise app_exp;
    end if;
    exception
        when app_exp then
        rollback;
end;