存储过程:
vim test_prc2.sql
create procedure sp_fi(in iv_dt string,out rlt string)
i_dt string;
begin
i_dt = iv_dt;
insert into table test.test_1 select customerid||current_date cust_id,i_dt from xx.info limit 10;
rlt = '1';
end;
调用存储过程
vim call_2.sql
include test_prc2.sql --引用存储过程
declare
str string;
iv_dt string;
begin
iv_dt = current_date;
call sp_fi(iv_dt,str);
print str;
print iv_dt;
end;
/opt/hplsql-0.3.31/hplsql -f /job/hsql/hplsql.sql -d DATA_DATE=`date -d yesterday +%G/%m/%d`
declare 支持substr(),replace
procedure 支持regexp_replace;date_add(current_date,-30),add_months(current_date,-1)
eg2:
CREATE PROCEDURE SP_PRC1(IN DATA_DATE string,out RETURN_MSG string)
LV_NUM_DATA_DATE string;
LV_STR_PRO_NAME string;
LV_STR_DEPT_NAME string;
LV_STR_TABLE_NAME string;
LV_DT_BEGIN_DT string;
LV_DT_END_DT string;
LV_NUM_LOG_STEP INT;
LV_LOG_POT string;
BEGIN
MAP OBJECT log TO big_data.log AT mysqlconn;
LV_NUM_DATA_DATE=DATA_DATE;
print LV_NUM_DATA_DATE
LV_STR_PRO_NAME='SP_PRC1';
LV_STR_DEPT_NAME='TEST';
LV_STR_TABLE_NAME='TEST_1';
LV_DT_BEGIN_DT=CURRENT_TIMESTAMP;
LV_DT_END_DT=CURRENT_TIMESTAMP;
LV_NUM_LOG_STEP=0;
LV_LOG_POT='Begin....';
print LV_DT_END_DT;
pirnt 'LV_NUM_DATA_DATE='||LV_NUM_DATA_DATE;
--开始执行
insert into log( dept_name, pro_name, table_name, log_step, log_pot, begin_time, end_time, error_msg, load_dt, data_date) values
(LV_STR_DEPT_NAME,LV_STR_PRO_NAME,LV_STR_TABLE_NAME,LV_NUM_LOG_STEP,LV_LOG_POT,LV_DT_BEGIN_DT,LV_DT_END_DT,'0',LV_DT_BEGIN_DT,LV_NUM_DATA_DATE);
commit;
print '33333333333333333333333333333';
insert into table test.TEST_1
select SERIALNO as cust_id,
LV_NUM_DATA_DATE as etl_dt
from xx.st_fo where inputdate = LV_NUM_DATA_DATE;
LV_DT_END_DT=CURRENT_TIMESTAMP;
LV_NUM_LOG_STEP=LV_NUM_LOG_STEP+1;
LV_LOG_POT='END';
RETURN_MSG=99;
--结束
insert into log( dept_name, pro_name, table_name, log_step, log_pot, begin_time, end_time, error_msg, load_dt, data_date) values
(LV_STR_DEPT_NAME,LV_STR_PRO_NAME,LV_STR_TABLE_NAME,LV_NUM_LOG_STEP,LV_LOG_POT,LV_DT_BEGIN_DT,LV_DT_END_DT,RETURN_MSG,LV_DT_END_DT,LV_NUM_DATA_DATE);
commit;
EXCEPTION WHEN OTHERS THEN
RETURN_MSG='ERROR';
--错误登记
insert into log( dept_name, pro_name, table_name, log_step, log_pot, begin_time, end_time, error_msg, load_dt, data_date) values
(LV_STR_DEPT_NAME,LV_STR_PRO_NAME,LV_STR_TABLE_NAME,LV_NUM_LOG_STEP,LV_LOG_POT,LV_DT_BEGIN_DT,LV_DT_END_DT,RETURN_MSG,LV_DT_END_DT,LV_NUM_DATA_DATE);
commit;
END;
declare
LV_DATA_DATE string;
RETURN_MSG string;
begin
LV_DATA_DATE=DATA_DATE
call sp_prc1(LV_DATA_DATE,RETURN_MSG);
print LV_DATA_DATE;
pring RETURN_MSG;
end;