Re: Pl/SQL

From: Avi Abrami <aabrami_at_intersystemsww.com>
Date: Sun, 25 Feb 2001 07:47:07 +0200
Message-ID: <3A989C5B.2AC80DD3_at_intersystemsww.com>


Paul Kolbohm wrote:

> I need help with the following:
>
> Part1: I am trying to develop a PL/SQL Package stored within a database to
> accept input data for single distance calculations. i need to write a
> procedure that will accept two sets of x and y coordinates as input. The
> procedure will then output the distance between the points in km. Can anyone
> help as I am new to PL/SQL?
> The distance between two points (in two-dimensional world) is
> SQRT(POWER(x2 - x1,2) + POWER(y2 - y1,2)).
>
> Part2: I am trying to develop a PL/SQL Procedure for multiple distance
> calculatons which will take input from a single database table and output
> results to another database table. The input and output tables must be
> specified as part of the procedure call. The procedure must calculate
> distances between all locations specified in the input table and will allow
> the user to specify the calculation of distances in both directions if
> required. So for 10 locations in the input table, this will result in 100
> distance calculations or 50 calculations if only one direction is required.
> The procedure will accept as input a flag to indicate whether or not
> distances are to be calculated in one direction only or in both directions
> from one point to another. The return flag will accept 'Y' or 'N' as its
> values.
> The table names are 'Location' and 'Dist'
> Location Table (Input Table)
> Id_number
> Region
> Name
> Code
> X
> Y
> Done
>
> Dist Table (Output table)
> From_id
> To_id
> Distance
>
> Part3:
> For multiple distance calculations a second procedure must be developed to
> alow the use of 2 input tables. This procedure will calculate distances
> between all the records in table 1 and the records in table 2 i.e. it will
> calculate distances between location 1 in table 1 and all the locations in
> table 2 , then between location 2 in table 1 and all the locations in table
> 2, then between location 3 in table 1 and all the locations in table 2 and
> so. Again this procedure will output results to a database table. The format
> of this procedure call might be: Procedure_call (input_table1, input_table2,
> output_table).
>
> Please reply to--
> paul.kolbohm_at_mallontechnology.com

Hi Paul,
The documentation for PL/SQL is available online from here:

http://technet.oracle.com/docs/index.htm

I can't be more specific since you don't say what version of Oracle you are using, but you should be able to navigate your way from the above URL.

Look for the PL/SQL Users Guide, and the PL/SQL Supplied Packages Reference.

You mention using a PL/SQL package - this feature exists in Oracle versions 7 and up.

You also mention that your procedure should accept input. If you mean procedure parameters, then that's doable in PL/SQL. However, if you mean accepting input from the user via the keyboard, then AFAIK this is not doable in PL/SQL.

Good Luck,
Avi. Received on Sun Feb 25 2001 - 06:47:07 CET

Original text of this message