Best practice to make parametric selects?

user2081233

What is best practice to make parametric selects who's results will be later used in other stored procedures?

(As database evolves then selects can change to output the same data).

For know I use procedure that receive select's parameters and return a cursor with data. (These procedures mostly return cursors with data less than 100 rows.)

I do understand that it is possible to create a parametric views. Still, it requires to change this parameter in code and that can be omitted and lead to errors.

Is there some boundary on return data when you should definitely create a parametric view?

Billy Verreynne

All 3 options results in a cursor being created.

All SQL are parsed and executed as cursors. There is no such concept as using SQL and not using a cursor on the server.

Given this fact, which of these 3 options meets the requirements at hand with minimal moving parts?

Also get the notion of "parameters" (ala procedural style languages) out of your mind when using SQL. SQL is not a procedural language. Do not treat it as one.

Variable data needs to be passed to a SQL cursor using bind variables. Any other method (like using name-values in a context) need sound and logical justification.

As for pipelines - it is intended for doing data transformations. Using it as a procedural wrapper for SQL to pass parameters is just plain idiotic. Makes no sense at all.

kendenny

You can use a pipelined function.

http://www.oracle-base.com/articles/misc/pipelined-table-functions.php

Unknown

What is best practice to make parametric selects who's results will be later used in other stored procedures?
(As database evolves then selects can change to output the same data).

For know I use procedure that receive select's parameters and return a cursor with data. (These procedures mostly return cursors with data less than 100 rows.)

NO! Get that notion out of your head!

CURSORs do NOT contain data. They never have and likely never will.

So any procedure that returns a CURSOR is NOT returning ANY data; period!

Just return a CURSOR. In the procedure OPEN the cursor for whatever query you want/need to use. Just make sure that the projection of the query is the same: same number of columns of compatible datatypes.

I do understand that it is possible to create a parametric views. Still, it requires to change this parameter in code and that can be omitted and lead to errors.

Is there some boundary on return data when you should definitely create a parametric view? 

You don't need any view. REF CURSORs are the standard way to interface with a client that needs data. The client:

1. calls the procedure

2. gets the CURSOR parameter

3. FETCHes data using the cursor

user2081233

Maybe my question was not formulated good enough.

Cursor does not contain data. It contains reference to the data.

The question is more about in which case each solution should be used?

Solution A: Function returns cursor;

Solution B: Create a new view;

Solution C: Create a pipelined table function.

newenrba

If you can create VIEW then create VIEW there is not better way.......

Billy Verreynne

All 3 options results in a cursor being created.

All SQL are parsed and executed as cursors. There is no such concept as using SQL and not using a cursor on the server.

Given this fact, which of these 3 options meets the requirements at hand with minimal moving parts?

Also get the notion of "parameters" (ala procedural style languages) out of your mind when using SQL. SQL is not a procedural language. Do not treat it as one.

Variable data needs to be passed to a SQL cursor using bind variables. Any other method (like using name-values in a context) need sound and logical justification.

As for pipelines - it is intended for doing data transformations. Using it as a procedural wrapper for SQL to pass parameters is just plain idiotic. Makes no sense at all.

user2081233

Thanks.

I mean "parameters" as user "input" for where section.

Billy Verreynne

user2081233 wrote:

I mean "parameters" as user "input" for where section.

Modularisation in SQL is different from most other languages. To create a reusable "module" (SQL statement) cannot be done via formal parameters.

Simple example. We can create cursor interfaces with formal parameters in PL/SQL for example:

create or replace package MyCursors as

  cursor getEmpByID( empID integer ) is
    select * from emp where empno = empID;

end;

How do we do the same in SQL? We cannot and we need not.

To create reusable module for others to use, we create a view. The purpose of a view is not to provide a formal parameter call interface (like the PL/SQL example above), but to apply logic and knowledge in order to expose a new (typically more abstract) data structure for use.

Thus a PL/SQL "getEmpByID" will look as follows when using only SQL:

select * from emp where empno = :bindVariable

SQL cannot be treated the same way ito design and modularisation (and structured programming) as PL/SQL or Java or C# is treated. The language works different. The language's concepts are different. And "parameterising" in SQL (like creating parameterised SQL views) is contrary to these concepts - and should only be used in exceptional circumstances.

user2081233

Can you give some recommended reading about modularisation in SQL or PL/SQL?

In my opinion modular system is easier to maintain and add new features. You create building blocks and then just put them together in different ways.

Billy Verreynne

Agree fully.

But SQL itself does not lend itself to modularisation - with the exception of VIEWs (reusability across SQL statements), or the WITH clause (reusability within a single SQL statement).

Modularisation should be done in the application language (PL/SQL or Java or whatever), and this used to a) abstract the SQL layer, and b) modularise access to the SQL layer.

user2081233

Ok, now I do understand other difference. I use PL/SQL as synonym to SQL as it has added features to SQL.

Then I can rephrase question - what is best practice for modularisation in PL/SQL?