Home » RDBMS Server » Server Administration » two-dimensional pl/sql table
two-dimensional pl/sql table [message #370315] Sun, 22 August 1999 13:54 Go to next message
Alex
Messages: 190
Registered: August 1999
Senior Member
How can i create a two-dimensional pl/sql table for retrieving, say, student id and courses for that student.

Any help would be greatly appreciated
Alex
Re: two-dimensional pl/sql table [message #370319 is a reply to message #370315] Mon, 23 August 1999 10:54 Go to previous messageGo to next message
MikeG
Messages: 14
Registered: August 1999
Junior Member
Create a record structure and then create a table of them
Re: two-dimensional pl/sql table [message #370328 is a reply to message #370315] Fri, 27 August 1999 06:02 Go to previous messageGo to next message
Chris Hunt
Messages: 27
Registered: March 1999
Junior Member
Unfortunately PL/SQL tables cannot be built with more than one dimension. However, a good thing about them is that they are designed to be "sparse", i.e. a table with two rows numbered 1 and 1000 takes up no more room than one numbered 1 and 2. We can use this fact to simulate a multi-dimensional table.

Say you're confident that no student will do more than 99 courses (or 9 or 999 or whatever), you can generate a single-dimensional index thus:

<CENTER>(student_number * 100) + course_number</CENTER>

so your notional my_table(10,1) would become actual my_table(1001).

Whenever I've done this myself I've written a couple of local procedures/functions to manipulate the table:

<CENTER>FUNCTION get_record(index1,index2,...)

PROCEDURE put_record(index1,index2,...,record)
</CENTER>

That way I keep the code for implementing a multi-dimensional table separate from the main code of the package. It's less confusing for subsequent maintenance and means I can change it easily if someone takes more than 99 courses or Oracle implement multi-dimensional arrays in a future version of PL/SQL.

Let me know if you need any more help in this area, I should be able to dig out some example code.

Re: two-dimensional pl/sql table [message #370335 is a reply to message #370328] Sat, 28 August 1999 18:58 Go to previous message
Alex
Messages: 190
Registered: August 1999
Senior Member
Thanks, Chris, you clarified it a little bit for me. Can you show me some sample code though to illustrate that?
Alex Gorbounov
Previous Topic: Using a view in a join statement
Next Topic: Select
Goto Forum:
  


Current Time: Fri Mar 29 09:41:29 CDT 2024