存储过程实例

create or replace procedure DAILYSTATIC is
MAX_TMP   int;
AVG_TMP   int;
HIGH_TIME date;
TELEPHONE varchar2(20);
CONTENT   varchar2(150);
SMS_TMP   varchar2(40);
SMS_TIME  varchar2(40);
SMS_AVG   varchar2(40);
SMS_CONTENT            varchar(200);
begin
  --获取要发送的数据
  select max( TEMP ) into MAX_TMP from NEP_NUT_TEMP_LOG where trunc( time, 'dd' ) = trunc( sysdate, 'dd' );
  select TIME into HIGH_TIME from NEP_NUT_TEMP_LOG where TEMP = MAX_TMP  and trunc( time, 'dd' ) = trunc( sysdate,'dd' );
  select avg( TEMP ) into AVG_TMP from NEP_NUT_TEMP_LOG where trunc( time, 'dd' ) = trunc( sysdate, 'dd' );
 
  --获取联系人相关信息
  select tel into TELEPHONE from NEP_SYS_ALARM2LINKMAN where alarm_level = 3;
 
 
  SMS_TMP := '今天的最高温度为' || MAX_TMP;
  SMS_TIME := '出现时间为' || HIGH_TIME;
  SMS_AVG := '平均温度为' || AVG_TMP;
  SMS_CONTENT := SMS_TMP || ',' || SMS_TIME || ',' || SMS_AVG;
 
  --放进NEP_SYS_SENDSMS表中
  insert into NEP_SYS_SENDSMS( MOBILE, CONTENT, SMS_WRITE_TIME ) 
  values( TELEPHONE,SMS_CONTENT, sysdate );
end DAILYSTATIC;