Oracle 存储过程语法与样例

存储过程与函数

oracle存储过程与函数的区别

语法

数据类型

FREE教程 | PL/SQL数据类型

变量

/*定义变量*/
STATION_NAME1 varchar2(255);
BAY_NAME1 varchar2(255);
AVG_TIME FLOAT;
MAX_TIME FLOAT;
MAX_TIME_DATE varchar2(255);
MIN_TIME FLOAT;
MIN_TIME_DATE varchar2(255);
startTime varchar2(255); --开始时间
endTime  varchar2(255); --结束时间

控制结构

FREE教程 | PL/SQL控制结构

循环

游标遍历数据

FREE教程 | Oracle游标

双重循环

create  or  replace  procedure  test_procedure  is
   --a表游标定义
   cursor  a_cursor  is
     select  id  from  a;
   --b表游标定义
   cursor  b_cursor(aid number)  is
     select  id  from  b  where  b.id = aid;
begin
   for  a_cur  in  a_cursor loop
     for  b_cur  in  b_cursor(a_cur.id) loop
       --这里是你要执行的操作，比如insert到c
       insert  into  c  values  (b_cur.id);
       commit ;
     end  loop;
   end  loop;

调试

利用PL/SQL Developer 调试 Oracle 存储过程

样例程序

版本：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

/* 检修建议模块 响应变化时间 分析数据存入表中 */
create or replace procedure PeriodInspect_responseTime_YC
is
  /*创建游标 如果是在存储过程外使用显示游标，需要使用DECLARE关键字*/
  /*选择厂站 间隔*/
  CURSOR CUR_FIRST_INDEX IS
            SELECT TRIM(T1.STATION_NAME) STATION_NAME,TRIM(T2.BAYNAME) BAY_NAME
            FROM TABLE_STATION T1,TABLE_BAY T2
            WHERE T1.STATION_NO = T2.STATION_NO;
            
  /*定义游标变量，该变量的类型为基于游标CUR_FIRST_INDEX的行记录*/
  ROW_CUR_FIRST_INDEX CUR_FIRST_INDEX%ROWTYPE;
  
/*定义变量*/
STATION_NAME1 varchar2(255);
BAY_NAME1 varchar2(255);
AVG_TIME FLOAT;
MAX_TIME FLOAT;
MAX_TIME_DATE varchar2(255);
MIN_TIME FLOAT;
MIN_TIME_DATE varchar2(255);
startTime varchar2(255); --开始时间
endTime  varchar2(255); --结束时间

BEGIN
   /*3年*/
  FOR yearNum in 1..3 LOOP
  	-- 变量赋值
    startTime:=to_char(sysdate,'YYYYMMDD');
    endTime:=to_char(add_months(sysdate,-12*yearNum),'YYYYMMDD');
        /*遍历游标*/
    --FOR 循环
    FOR ROW_CUR_FIRST_INDEX IN CUR_FIRST_INDEX LOOP
      --循环体
              -- 获取数据
              -- 获取平均响应时间
              select  t1.STATION_NAME   ,t1.BAY_NAME ,ROUND(AVG(t1.TIME),2) INTO STATION_NAME1,BAY_NAME1, AVG_TIME
              from (
                  SELECT   (substr(bayname, 0, instr(bayname, '.', 1) - 1)) STATION_NAME,
                  (substr(bayname,instr(bayname, '.', 1) + 1,LENGTH(BAYNAME))) BAY_NAME ,TIME,STATTIME STAT_TIME
                  FROM TABLE_BAY
                  
              ) t1 
              WHERE STAT_TIME >= startTime and STAT_TIME <= endTime
                      AND t1.STATION_NAME=ROW_CUR_FIRST_INDEX.STATION_NAME AND t1.BAY_NAME=ROW_CUR_FIRST_INDEX.BAY_NAME
              GROUP BY t1.STATION_NAME,t1.BAY_NAME;
              
              -- 获取最大响应时间及对应日期
              select  T1.TIME  ,T1.STAT_TIME INTO MAX_TIME,MAX_TIME_DATE
              from 
              (
                   SELECT * FROM      (
                    SELECT   (substr(bayname, 0, instr(bayname, '.', 1) - 1)) STATION_NAME,
                    (substr(bayname,instr(bayname, '.', 1) + 1,LENGTH(BAYNAME))) BAY_NAME ,TIME,STATTIME STAT_TIME
                    FROM TABLE_BAY
                    
                ) t1 
                WHERE STAT_TIME >= startTime and STAT_TIME <= endTime
                        AND t1.STATION_NAME=ROW_CUR_FIRST_INDEX.STATION_NAME AND t1.BAY_NAME=ROW_CUR_FIRST_INDEX.BAY_NAME
                 ORDER BY T1.TIME DESC
              ) T1
              WHERE ROWNUM =1;
              
              
               -- 获取最小响应时间及对应日期
              select  T1.TIME  ,T1.STAT_TIME INTO MIN_TIME, MIN_TIME_DATE
              from 
              (
                   SELECT * FROM      (
                    SELECT   (substr(bayname, 0, instr(bayname, '.', 1) - 1)) STATION_NAME,
                    (substr(bayname,instr(bayname, '.', 1) + 1,LENGTH(BAYNAME))) BAY_NAME ,TIME,STATTIME STAT_TIME
                    FROM TABLE_BAY
                    
                ) t1 
                WHERE STAT_TIME >= startTime and STAT_TIME <= endTime
                        AND t1.STATION_NAME=ROW_CUR_FIRST_INDEX.STATION_NAME AND t1.BAY_NAME=ROW_CUR_FIRST_INDEX.BAY_NAME
                 ORDER BY T1.TIME ASC
              ) T1
              WHERE ROWNUM =1;

               
              
              -- 插入数据
              INSERT INTO PD_RESPONSE_TIME(STATION_NAME,BAY_NAME,AVG_TIME,YEAR_NUM,MAX_TIME,MAX_TIME_DATE,MIN_TIME,MIN_TIME_DATE,CREATE_TIME) 
                          VALUES(STATION_NAME1,BAY_NAME1,AVG_TIME,yearNum,MAX_TIME,MAX_TIME_DATE,MIN_TIME,MIN_TIME_DATE,sysdate);
              
              
      
    END LOOP;
    
  END LOOP;

END;

参考链接

Oracle # 创建存储过程+Job 详细步骤
FREE教程 | PL/SQL教程
oracle 定义双重循环例子