Oracle存储过程

-- 建表
create table KECHENG
(
    ID     NUMBER,
    NAME   VARCHAR2(20),
    COURSE VARCHAR2(20),
    SCORE  NUMBER
);

INSERT INTO SCOTT.KECHENG (ID, NAME, COURSE, SCORE) VALUES (1, '张三', '语文', 90);
INSERT INTO SCOTT.KECHENG (ID, NAME, COURSE, SCORE) VALUES (2, '张三', '数学', 80);
INSERT INTO SCOTT.KECHENG (ID, NAME, COURSE, SCORE) VALUES (3, '张三', '英语', 70);
INSERT INTO SCOTT.KECHENG (ID, NAME, COURSE, SCORE) VALUES (4, '张三', '历史', 60);
INSERT INTO SCOTT.KECHENG (ID, NAME, COURSE, SCORE) VALUES (5, '张三', '生物', 58);
INSERT INTO SCOTT.KECHENG (ID, NAME, COURSE, SCORE) VALUES (6, '张三', '物理', 50);
INSERT INTO SCOTT.KECHENG (ID, NAME, COURSE, SCORE) VALUES (7, '李四', '语文', 96);
INSERT INTO SCOTT.KECHENG (ID, NAME, COURSE, SCORE) VALUES (8, '李四', '数学', 93);
INSERT INTO SCOTT.KECHENG (ID, NAME, COURSE, SCORE) VALUES (9, '李四', '英语', 85);
INSERT INTO SCOTT.KECHENG (ID, NAME, COURSE, SCORE) VALUES (10, '李四', '历史', 43);
INSERT INTO SCOTT.KECHENG (ID, NAME, COURSE, SCORE) VALUES (11, '李四', '生物', 29);
INSERT INTO SCOTT.KECHENG (ID, NAME, COURSE, SCORE) VALUES (12, '李四', '物理', 69);

-- 案例
BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO WORLD');
END;

DECLARE
    V_NAME VARCHAR2(220):= '张三';
    V_SAL NUMBER;
    V_ADDR VARCHAR2(220);
BEGIN
    V_SAL := 1000;
    SELECT '上海' INTO V_ADDR FROM DUAL;
    DBMS_OUTPUT.PUT_LINE('姓名:'||V_NAME||',薪水:'||V_SAL||',地址:'||V_ADDR);
END;

DECLARE
    V_ID NUMBER;
    V_NAME VARCHAR2(20);
    V_COURSE VARCHAR2(20);
    V_SCORE VARCHAR2(20);
BEGIN
    SELECT ID,NAME,COURSE,SCORE INTO V_ID ,V_NAME,V_COURSE,V_SCORE FROM KECHENG WHERE ID=1 AND COURSE='语文';
    DBMS_OUTPUT.PUT_LINE(V_ID||V_NAME||V_COURSE||V_SCORE);
end;

DECLARE
    V_NAME KECHENG.NAME%TYPE;
    V_COURSE KECHENG.COURSE%TYPE;
    V_SCORE KECHENG.SCORE%TYPE;
BEGIN
    SELECT NAME,COURSE,SCORE INTO V_NAME,V_COURSE,V_SCORE FROM KECHENG WHERE ID=1 AND COURSE='语文';
    DBMS_OUTPUT.PUT_LINE('姓名:'||V_NAME||',科目:'||V_COURSE||',成绩:'||V_SCORE);
end;

DECLARE
    V_KECHENG KECHENG%ROWTYPE;
BEGIN
    SELECT * INTO V_KECHENG FROM KECHENG WHERE ID=1 AND COURSE='语文';
    DBMS_OUTPUT.PUT_LINE('姓名:'||V_KECHENG.NAME||',科目:'||V_KECHENG.COURSE||',成绩:'||V_KECHENG.SCORE);
END;

-- 判断KECHENG表中记录是否超过20条，10-20之间，或者10以下
DECLARE
    V_COUNT NUMBER;
BEGIN
    SELECT COUNT(1) INTO V_COUNT FROM KECHENG;
    IF V_COUNT>20 THEN
        DBMS_OUTPUT.PUT_LINE('KECHENG表中记录数超过20条为：'||V_COUNT);
        ELSIF V_COUNT<=20 AND V_COUNT>=10 THEN
         DBMS_OUTPUT.PUT_LINE('KECHENG表中记录数10-20之间为：'||V_COUNT);
        ELSE
        DBMS_OUTPUT.PUT_LINE('KECHENG表中记录数10条以下为：'||V_COUNT);
    end if;
end;

--循环
DECLARE
    V_NUM NUMBER := 1;
BEGIN
    LOOP
        EXIT WHEN V_NUM>10;
        DBMS_OUTPUT.PUT_LINE(V_NUM);
        V_NUM := V_NUM+1;
    END LOOP;
END;

--使用游标查询KECHENG表中所有人的姓名，科目，成绩，并将其打印出来。
DECLARE
    --生声明游标
    CURSOR C_KECHENG IS SELECT ID, NAME, COURSE, SCORE FROM KECHENG;
    V_ID KECHENG.ID%TYPE;
    V_NAME KECHENG.NAME%TYPE;
    V_COURSE KECHENG.COURSE%TYPE;
    V_SCORE KECHENG.SCORE%TYPE;
BEGIN
    OPEN C_KECHENG;
    LOOP
        FETCH C_KECHENG INTO V_ID,V_NAME,V_COURSE,V_SCORE;
        EXIT WHEN C_KECHENG%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('编号:'||V_ID||',姓名:'||V_NAME||',科目:'||V_COURSE||',成绩:'||V_SCORE);
    END LOOP;
    CLOSE C_KECHENG;
END;

--使用游标查询KECHENG表中所有人的姓名，科目，成绩，并将其打印出来。
--带参数
DECLARE
    CURSOR C_KECHENG(V_ID KECHENG.ID%TYPE) IS SELECT NAME,COURSE,SCORE FROM KECHENG WHERE ID=V_ID;
    V_NAME KECHENG.NAME%TYPE;
    V_COURSE KECHENG.COURSE%TYPE;
    V_SCORE KECHENG.SCORE%TYPE;
BEGIN
    OPEN C_KECHENG(3);
    LOOP
        FETCH C_KECHENG INTO V_NAME,V_COURSE,V_SCORE;
        EXIT WHEN C_KECHENG%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(V_NAME||V_COURSE||V_SCORE);
     --   DELETE KECHENG WHERE NAME =V_NAME;
    end loop;
    CLOSE C_KECHENG;
--     COMMIT ;
end;

–声明变量

CREATE OR REPLACE PROCEDURE P_HELLO AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('hello world');
end;


--执行存储过程
BEGIN
    P_HELLO();
END;

CREATE OR REPLACE PROCEDURE TESTDURE(INPUT NUMBER) AS
    V_NAME VARCHAR2(20);
BEGIN
    SELECT NAME INTO V_NAME FROM KECHENG WHERE COURSE='语文' AND ID = INPUT;
    DBMS_OUTPUT.PUT_LINE(V_NAME);
end;

DECLARE V_ID NUMBER;
BEGIN
    V_ID := 1;
    TESTDURE(V_ID);
end;

-- 游标和存储过程一起使用
CREATE OR REPLACE PROCEDURE TESTDURE(INPUT NUMBER) AS
begin
    declare
        cursor V_KECHENG is select NAME, COURSE from KECHENG where ID = to_number(input) ;
        V_NAME   VARCHAR2(20);
        V_COURSE VARCHAR2(20);
    BEGIN
        open V_KECHENG;
        LOOP
            fetch V_KECHENG into V_NAME,V_COURSE;
            exit when V_KECHENG%notfound;
            dbms_output.put_line(V_NAME || V_COURSE);
        end loop;
        close V_KECHENG;
    end;
END;

DECLARE
    V_ID NUMBER;
BEGIN
    V_ID := 1;
    TESTDURE(1); 
 end;

实例1：查找业务数据表这看来你刚刚字段是空的值，当接口数据有，就自动更新业务数据表。

DECLARE
  TYPE cur IS REF CURSOR;
  cur_01 cur;
  m1 VARCHAR2(50);
  m2 VARCHAR2(50);
  m3 VARCHAR2(50);

BEGIN
  OPEN cur_01 FOR 
   select tt.vin from vehicle.v_order@vehicle218  tt 
   where tt.vin_code is null 
   and tt.engine_model is null
   and tt.from_ship_loc in ('轻卡国际库','重卡库','同大库')
   and  tt.to_ship_loc='上海港';
  LOOP
    FETCH cur_01 INTO m1;
    EXIT WHEN cur_01%notfound;
    select aa.engin_no,aa.vihicle_vin_no into m2,m3 from intjacprd.wl_syczc@intjac aa where aa.vn=m1;
    if (m2 is not null and m3 is not null) then
    update vehicle.v_order@vehicle218 set engine_model=m2,vin_code=m3 where vin=m1;
    end if;
    COMMIT;
  END LOOP;
  CLOSE cur_01;
  exception when others then
    dbms_output.put_line('erro');
END;

实例2：查找数据并写入到bb表中

delete from bb;
commit;
DECLARE
  TYPE cur IS REF CURSOR;
  cur_01 cur;
  TYPE rec IS RECORD(
  r1 VARCHAR2(100),
  r2 VARCHAR2(100),
  r3 VARCHAR2(100),
  r4 VARCHAR2(100),
  r5 VARCHAR2(100),
  r6 VARCHAR2(100)
  );
  rec_01 rec;
  m number:=0;

BEGIN
  OPEN cur_01 FOR 
   select tt.vin ,tt.link_type,tt.username,tt.link_time,tt.phone,tt.code
   from link_trail  tt 
   where not exists (select 1 from link_trail l
   where l.vin=tt.vin 
   and l.link_type in('收车','回单','签收'))
   and  tt.company_name='商储'
   order by tt.vin ,tt.link_time;
  LOOP
    FETCH cur_01 INTO rec_01;
    EXIT WHEN cur_01%notfound;
    if (rec_01.r2='打卡警告') then
         m:=m+1;
         insert into bb(frequency,vin,link_type,username,time,phone,code)
          values(m,rec_01.r1,rec_01.r2,rec_01.r3,rec_01.r4,rec_01.r5,rec_01.r6);
    else m:=0;
    end if;
    COMMIT;
  END LOOP;
  CLOSE cur_01;
  exception when others then
    dbms_output.put_line('erro');
END;