plPL/SQL 返回过程中的记录

Question

我有一个叫做"gps_coord"的过程。它需要两个参数，并且必须返回记录。但我不知道我该怎么做.提前致谢;)

CREATE OR REPLACE PROCEDURE gps_coord(v_x IN FLOAT,
                                      v_y IN FLOAT, v_result out result_type)
IS
TYPE result_type IS RECORD(
     v_km VARCHAR2(50),
     v_objID VARCHAR2(50)
);
v_out result_type;
BEGIN

select P.KM , P.OBJECTID INTO v_out  from SDE.POINTS p where        sde.st_intersects(P.SHAPE,sde.st_buffer(sde.st_transform( sde.st_point(v_y,v_x,4326),32639) ,5000))=1;
 dbms_output.put_line('km: ' || v_out.v_km || 'objid: ' || v_out.v_objID );
END;

Answer 1

试试这个。希望对您有所帮助。

    --Object type creation
CREATE OR REPLACE TYPE result_type
IS
  OBJECT
  (
    v_km    VARCHAR2(50),
    v_objID VARCHAR2(50) );
--Table type
CREATE OR REPLACE type result_tab
IS
  TABLE OF result_type;
  --Procedure creation and out the collection
CREATE OR REPLACE
PROCEDURE gps_coord(
    v_x IN FLOAT,
    v_y IN FLOAT,
    v_result OUT result_tab)
AS
  v_out result_tab;
BEGIN
  SELECT result_type(P.KM , P.OBJECTID) BULK COLLECT
  INTO v_out
  FROM SDE.POINTS p
  WHERE sde.st_intersects(P.SHAPE,sde.st_buffer(sde.st_transform( sde.st_point(v_y,v_x,4326),32639) ,5000))=1;
  FOR i IN v_out.FIRST..v_out.LAST
  LOOP
    dbms_output.put_line(v_out(i).v_km||' '||v_out(i).v_objID);
  END LOOP;
END;

Answer 2

CREATE OR REPLACE TYPE result_type IS OBJECT
(
   v_km NVARCHAR2(50),
   v_objID NVARCHAR2(50)
 )

CREATE OR REPLACE PROCEDURE gps_coord(v_x IN NVARCHAR2, v_y IN NVARCHAR2,    v_result out result_type)
 IS
  BEGIN
   select P.KM , P.OBJECTID INTO v_result.v_km ,  v_result.v_objID from SDE.POINTS p where sde.st_intersects(P.SHAPE,sde.st_buffer(sde.st_transform( sde.st_point(v_y,v_x,4326),32639) ,5000))=1;
  end;