Bob update

[TheFaQQer]

So what would happen if you write many of your procedures this complex and there is requirement to move to different database provider. This will be the result to rewrite all the stored procedures.

If this logic stays in this BL layer you are protected and future proofed.

Think like a Bob - much rewriteness, plenty invoiceness

[eek]

So what would happen if you write many of your procedures this complex and there is requirement to move to different database provider. This will be the result to rewrite all the stored procedures.

If this logic stays in this BL layer you are protected and future proofed.

Why would you do this for any internal application? I business starts buying oracle or sql server and will stay there for evermore.

The only reason for supporting multiple databases would be if its was a product you intend to sell to multiple clients.

[DimPrawn]

I am quite interested in this debate. I do not regard myself as a SQL expert, it's not how I make my living, however I have developed a couple of small web-sites with SQL Server back-ends. I did not use cursors and can't think of a situation where they would be useful.

The development standard I adopted was a simple two layer approach, a web page that in almost all cases did exactly one call to a SQL procedure. (There was one exception which I won't go into.) The web page mostly just handled user interface and simple validation, most logic was at the SQL level.

The idea behind one-page = one-proc is that once the user "presses enter", everything that is going to happen is predetermined, therefore there should be no need to call the database more than once. Many of my procs did return multiple results sets, to satisfy all the requirements of the particular transaction.

My take on cursors is that as soon as you use them, you are taking a procedural approach to computing your result sets, and this is not a good thing if you are using a tool that is designed and optimised for set processing. In other words, despite my lack of qualification, I'm claiming that using cursors is actually bad practice in SQL.

Can you give me an example (simple and made-up is fine) where using a cursor would be clearly superior to a non-cursor alternative? (Assumed constraint is that the mission is to take some parameters and compute one or more result sets from data contained in a single database. I have used cursors in contexts where I was interacting with something other than just a database, for example executing a sequence of operating system commands.)

There's a big difference between the architecture of SQL Server and Oracle and T-SQL and PL/SQL. Every CRUD operation in Oracle uses a cursor, whether you declare one or not. Infact just returning data from Oracle to middleware requires a REF Cursor.

[Mich the Tester]

You've obviously never worked with an Oracle DBA team have you?

You:
I'm writing a middle-ware layer in C/C++/C#/Java...

Them:
Why are you doing that, the Oracle database can do all that in PL/SQL....

You:
I'm writing a utility to load, transform and write data to the database....

Them:
Why are you doing that, the Oracle database can do all that in PL/SQL....

You:
I'm using a webserver to convert data into XHTML...

Them:
Why are you doing that, the Oracle database can do all that in PL/SQL....

You:
I'm creating a front end user interface in .NET...

Them:
Why are you doing that, the Oracle database can do all that in PL/SQL....

You:
Right, I'm going for a tulip!

Them:
Why are you doing that, the Oracle database can do all that in PL/SQL....

DBA's right, except for having a tulip. You could make a dump of the Oracle database though.

[russell]

Why would you do this for any internal application? I business starts buying oracle or sql server and will stay there for evermore.

Must be up there for stupid post ever, hope you're not in IT.

[TheFaQQer]

I am quite interested in this debate. I do not regard myself as a SQL expert, it's not how I make my living, however I have developed a couple of small web-sites with SQL Server back-ends. I did not use cursors and can't think of a situation where they would be useful.

The development standard I adopted was a simple two layer approach, a web page that in almost all cases did exactly one call to a SQL procedure. (There was one exception which I won't go into.) The web page mostly just handled user interface and simple validation, most logic was at the SQL level.

The idea behind one-page = one-proc is that once the user "presses enter", everything that is going to happen is predetermined, therefore there should be no need to call the database more than once. Many of my procs did return multiple results sets, to satisfy all the requirements of the particular transaction.

My take on cursors is that as soon as you use them, you are taking a procedural approach to computing your result sets, and this is not a good thing if you are using a tool that is designed and optimised for set processing. In other words, despite my lack of qualification, I'm claiming that using cursors is actually bad practice in SQL.

Can you give me an example (simple and made-up is fine) where using a cursor would be clearly superior to a non-cursor alternative? (Assumed constraint is that the mission is to take some parameters and compute one or more result sets from data contained in a single database. I have used cursors in contexts where I was interacting with something other than just a database, for example executing a sequence of operating system commands.)

If you are selecting any data in Oracle, then you are using cursors - they may be explicit or implicit, but you are always using a cursor.

Performance-wise, if possible then you should ideally use an implicit cursor (which isn't quite what Oracle teaches!). However, using an implicit cursor means that your code needs to catch exceptions which might be thrown.

For example,

declare
  cursor c is select * from dual where 1=2;
  r c%rowtype;
begin
  open c;
  fetch c into r;
  close c;
end;
/

will execute fine with no errors being thrown. However

declare
  x   dual.dummy%type;
begin
  select *
  into   x
  from dual where 1=2;
end;
/

ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

you need to code additional logic to cater for the no data found error and two many rows error which might be returned.

If you have confidence in the data model, then not catching the error and allowing the code to fall over might be a good thing, since the data is not in a state which should be expected. If you don't, then there is a reasonable case to be had for handling the errors gracefully using a cursor.

Performance-wise, as I said, there is a marginal gain from using

FOR i IN ( SELECT * FROM DUAL) LOOP
..
END LOOP;

over

FOR i IN c LOOP
..
END LOOP;

[Mich the Tester]

If you have confidence in the data model,

Yes, but with a database built on the subcontinent, this will be a very big 'if'.

[TheFaQQer]

Yes, but with a database built on the subcontinent, this will be a very big 'if'.

Yep

What I've found with some off-shore developers is that they use an implicit cursor, and their error handling just then suppresses everything that might go wrong with it and continue blindly onwards....

[Hill Station Murthy]

It is this kind of mindless useless thinking that has resulted in countless projects going over the budget and never getting finished. How many times do you get companies changing the back end database on their enterprise level apps ?

Are you SuitYou in disguise or something ?

On sevral occassions has found me moving backend from Access to sql, from informix to oracle just to name 2 differnet occassions in my career.

I can tell you now that this happens more frequently than you suspect.

I am very much a designer of n-tier applications and this is always been taught to me as best practice.

[suityou01]

There is absolutley no need to be using cursors for any application in the present day.

All business logics should be achieved in the appropriate layer of your application. You should then have a data layer that performs simple reads/inserts/deletes/updates to your Db.

If you are in the situation of using this cursors I have to be quite honest with you and say that I am under no surprise that your team is querying your design.

HTH

Joshi.

What a load of bollox.

HTH