记录类型

• 复制
  复制成功

CREATE SCHEMA IF NOT EXISTS test_oracle;
set search_path to test_oracle;
create table tbl_city(id int, population int,  nation varchar(32), city varchar(32), gdp int, others text);

insert into tbl_city(id, population,  nation, city, gdp) values(1,500, 'China','Guangzhou', 23000);
insert into tbl_city(id, population,  nation, city, gdp) values(2,1500, 'China', 'Shanghai', 29000);
insert into tbl_city(id, population,  nation, city, gdp) values(3,1500, 'China', 'Beijing', 25000);
insert into tbl_city(id, population,  nation, city, gdp) values(4,1000, 'China', 'Shenzhen', 24000);
insert into tbl_city(id, population,  nation, city, gdp) values(5,1000,'USA','New York', 35000);
insert into tbl_city(id, population,  nation, city, gdp) values(6,500, 'USA', 'Bostom', 15000);
insert into tbl_city(id, population,  nation, city, gdp) values(7,500, 'Japan','Tokyo', 40000);
insert into tbl_city(id, population,  nation, city, gdp) values(8,800, 'China', 'Hongkong', 23500);
insert into tbl_city(id, population,  nation, city, gdp) values(9,800, 'China', 'Hangzhou', 15500);
insert into tbl_city(id, population,  nation, city, gdp) values(10,100, 'USA', 'Los Angele', 15500);


set search_path to test_oracle;
DROP FUNCTION catcity;
CREATE FUNCTION catcity(nation_name text) RETURNS text AS $$
DECLARE
    TYPE mycitytype IS RECORD(city text);
    trow tbl_city%ROWTYPE;
    rlt text;
    lt mycitytype;
BEGIN
    rlt = ':' || nation_name;
    FOR trow IN SELECT * FROM tbl_city WHERE nation = nation_name
    LOOP

        lt.city = trow.city;
        rlt = lt.city ||'+' || rlt;
    END LOOP;
    return rlt;
END;
$$ LANGUAGE plpgsql;

select catcity('China');