Posted via ActualForum NNTP Server 1.5

CREATE GLOBAL TEMPORARY TABLE rowset
(
    rowset_id BIGINT NOT NULL,
    id BIGINT NOT NULL PRIMARY KEY,
    name VARCHAR(200) NOT NULL UNIQUE CHARACTER SET UTF8,
  CONSTRAINT rowset__uq UNIQUE (rowset_id, id)
) ON COMMIT PRESERVE ROWS;

CREATE OR ALTER PROCEDURE get
(
  rowset_id BIGINT NOT NULL
)
RETURNS
(
  id BIGINT,
  name VARCHAR(200) NOT NULL UNIQUE CHARACTER SET UTF8
)
AS
BEGIN
  FOR
      SELECT id, name
        FROM rowset
        WHERE rowset_id = :rowset_id
        INTO: id, name
    DO
      SUSPEND;
END;

CREATE PACKAGE BODY
AS
  DECLARE LOCAL TEMPORARY TABLE t1(
     id int not null,
     name varchar(100),
     constraint pk1 primary key(id)
  );

  PROCEDURE FILL
  AS 
  BEGIN
     INSERT INTO t1(id, name) VALUE(1, 'Привет');
  END

  PROCEDURE GET_T1 RETURNS (id int, name varchar(100))
  AS
  BEGIN
     EXECUTE PRCOEDURE FILL;
     FOR SELECT id, name
            FROM T1
     INTO id, name
     DO SUSPEND;
  END
END

CREATE GLOBAL TEMPORARY TABLE rowset
(
    rowset_id BIGINT NOT NULL,
    id BIGINT NOT NULL,
    name VARCHAR(200) NOT NULL CHARACTER SET UTF8,
  CONSTRAINT rowset__pk PRIMARY KEY (rowset_id, id),
  CONSTRAINT rowset__uq UNIQUE (rowset_id, name)
) ON COMMIT PRESERVE ROWS;

CREATE OR ALTER PROCEDURE get
(
  rowset_id BIGINT NOT NULL
)
RETURNS
(
  id BIGINT,
  name VARCHAR(200) NOT NULL UNIQUE CHARACTER SET UTF8
)
AS
BEGIN
  FOR
      SELECT id, name
        FROM rowset
        WHERE rowset_id = :rowset_id
        INTO: id, name
    DO
      SUSPEND;
END;

Posted via ActualForum NNTP Server 1.5