oracle在存储过程中给变量赋值

oracle变量赋值有两种一种是直接:=，两个字符串的连接不可以用 '+' ,要用 '||'

 

还有就是select into 

 

 

下面是我写的例子：

 

 

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;