Ejemplos de Stored Procedure

[webness]

yo aprendi PL/SQL DE ORACLE AQUI en http://www.devjoker.com/asp/indice_c...as_categoria=2,

si los quieres en postgresql aqui te mando 2 ejemplos reales y algo complejos

Código SQL:

CREATE OR REPLACE FUNCTION p_get_blocks_count(INTEGER, VARCHAR,INTEGER,VARCHAR,VARCHAR) RETURNS SETOF type_block_count AS $BODY$
DECLARE 
    FILTER  ALIAS FOR $1;  
    list    ALIAS FOR $2;
    city    ALIAS FOR $3;
    socioeconomic   ALIAS FOR $4;
    country ALIAS FOR $5;
    
    sql_query VARCHAR;
    query_result type_block_count;
BEGIN
    IF FILTER = 1 THEN
        sql_query := 'SELECT    country_name,region_name,city_name,locale_name,locale_code,socioeconomic_status,city_code,total
                       FROM     conteo_manzanas_localidad_'||country||'
                       WHERE    locale_code IN ('||list||') AND 
                                city_code = '||city||' AND
                                socioeconomic_status IN ('||socioeconomic||')';
        FOR query_result IN EXECUTE sql_query LOOP RETURN NEXT query_result;
        END LOOP;           
    ELSE
        sql_query := 'SELECT    country_name,region_name,city_name,neighborhood_name,neighborhood_code,socioeconomic_status,city_code,total,locale_name,locale_code,
                       FROM     conteo_manzanas_barrio_'||country||'
                       WHERE    neighborhood_code IN ('||list||') AND 
                                city_code = '||city||' AND
                                socioeconomic_status IN ('||socioeconomic||')';
        FOR query_result IN EXECUTE sql_query LOOP RETURN NEXT query_result;
        END LOOP;               
        END IF;
    RETURN;
END $BODY$
LANGUAGE plpgsql;
 
SELECT * FROM p_get_blocks_count(1, $$119 ,120 ,121 ,122$$ ,739,$$'3','4','5'$$,'co')
SELECT * FROM p_get_blocks_count(2, $$'000076','000063','000066','000061'$$ ,76834,$$'3','4','5'$$,'co')
 

OTRO

Código SQL:

CREATE OR REPLACE FUNCTION p_update_locales_in_count() RETURNS INTEGER AS $BODY$
DECLARE 
    query_count         RECORD;
    query_has_locales   RECORD;
    real_data           RECORD;
BEGIN
    
    FOR query_count IN  SELECT  co.neighborhood_code,co.city_code 
                        FROM    conteo_manzanas_barrio_co co,sm_city ci
                        WHERE   co.city_code = ci.pk_city AND has_locales = TRUE
                        LIMIT 1 OFFSET 0 LOOP
        
        SELECT  INTO real_data  sl.pk_locale,sl.name
        FROM    sm_locale sl,sm_neighborhood sn,servcon_barrios sb
        WHERE   sn.pk_neighborhood = query_count.neighborhood_code AND
                sl.fk_pk_city = query_count.city_code AND
                sb.cod_localidad = sl.locale_code AND
                sb.cod_barrio = sn.neighborhood_code AND
                sl.name = sb.nom_localidad;
                
        UPDATE  conteo_manzanas_barrio_co 
        SET     locale_code = real_data.pk_locale, locale_name = real_data.name 
        WHERE   neighborhood_code = query_count.neighborhood_code;
        
    END LOOP;
 
    RETURN 0;   
END $BODY$
LANGUAGE plpgsql;