I'd like to see records added to the stored procedure language of SQA. One of the biggest complaints from my developers when moving code from Oracle to SQLA is the lack of records. With Oracle you can create records in two ways:

  • By Type

    TYPE rec_t IS RECORD
    (
    memvar1 VARCHAR2(30),
    memvar2 VARCHAR2(30)
    );

    rec1 rec_t;
    rec2 rec_t;

  • By Table Row Type

    rec1 TABLE%ROWTYPE;
    rec2 TABLE%ROWTYPE;

  • By Cursor Row Type

    CURSUR curSample IS
    SELECT col1,
    coll2
    FROM TABLE;

    rec1 curSample%ROWTYPE;
    rec2 curSample%ROWTYPE;

All three of the above will declare 2 records, one based upon a type that was created, the next based upon the table structure, and the third based upon a cursor result set. Access to the variables is based upon dot notation.

rec1.memvar1 := 1;

One of the nicest features is that you can fetch into a record. This reduces the number of variables to type up and also allows you to add columns to cursors without needing to find and update all the fetches.

An additional nicety is that if you change your tables to increase the width of columns the definition within your code is automatically increased as well. Although this can cause other side effects so still needs to be watched carefully.

The number of parameters you need to send to procedures can be reduced dramatically as well with records, which may or may not help with performance.

asked 16 Nov '09, 16:48

Jon%20Greisz's gravatar image

Jon Greisz
571979
accept rate: 0%

edited 15 Mar '13, 18:54

Mark%20Culp's gravatar image

Mark Culp
22.7k9129266


This is surely no trivial feature (both implementing and using). But when I think of the long lists of variables declared in nested procedure levels a record would come in handy.

Of course there has to be some calling convention to pass records as references, avoiding the need to copy all members to and fro. There seems to be something like that already silently working for long varchars.

permanent link

answered 19 Nov '09, 16:53

Reimer%20Pods's gravatar image

Reimer Pods
4.2k334481
accept rate: 12%

FWIW, that request has been realized with SA 16 - confine the ROW (and ARRAY) data type constructors.

I'm not aware of the ORACLE implementation - so I guess the exact usage and syntax will differ in SA.

permanent link

answered 03 Jun '13, 07:38

Volker%20Barth's gravatar image

Volker Barth
29.9k294446654
accept rate: 32%

With respect to cursors with lots of fetch variables, SQL Anywhere's ANSI-compliant FOR statement might be used to reduce much of the discussed overhead with long variable lists. It also omits the need for explicit fetch statements and as such, can be schema-agnostic as long as columns are only added but not re-named or deleted (unless they aren't used in the loop).

For example:

:::SQL
begin
  for forCrs as crs cursor for
     select * from systable order by 1
     for read only
  do
    -- do something with the current row.
    -- Note: All variables are named after the according columns
    message 'table_name = ''' || table_name || '''' to client;
  end for;
end;

When you prefer particular variable names (e.g. to distinguish them from column names), you can use according alias names in the select list, such as

select table_id as @table_id, table_name as @table_name from systable order by 1

Of course, the FOR statement doesn't help to package long variable lists that are to be passed to different procedures so this is only a partial solution.

permanent link

answered 31 Mar '11, 09:15

Volker%20Barth's gravatar image

Volker Barth
29.9k294446654
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×107
×104
×23
×19
×3

question asked: 16 Nov '09, 16:48

question was seen: 1,486 times

last updated: 03 Jun '13, 07:38