Netezza存储过程示例

CREATE OR REPLACE PROCEDURE pro_1(CHARACTER VARYING(10))
 RETURNS VARCHAR(ANY)
 EXECUTE AS OWNER
 LANGUAGE NZPLSQL AS
 BEGIN_PROC
 DECLARE
 jqid_cursor record;
 BEGIN
 -- procedure body
 FOR jqid_cursor for select f_jqid from t_scenicday1 group by f_jqid  LOOP
     RAISE NOTICE '景区编号: % ', jqid_cursor;
     insert into mytest(
     select w.*, f_ctname from
     (select f_jqid,f_ykprovinceid,sum(f_xzyhs) as su from t_scenicday1
     where f_tjsj like '2014-06-01%' and f_jqid=jqid_cursor
     group by f_jqid,f_ykprovinceid) w
     left join t_province pro on w.f_ykprovinceid=pro.f_ctid);
 END LOOP;