存储过程与函数
语法
数据类型
变量
/*定义变量*/
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); --结束时间
控制结构
循环
游标遍历数据
双重循环
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;