SEO

Hi,

I want to create a stored procedure that will populate an empty table.

I am a java programmer and a novice in databases. Would someone mind
helping me translate my psuedocode into a stored procedure?

create procedure MappingTable{

@array1 = {1,2,3,4}
@array2 = {10,20,30,40}

@i = 0

while ( array1.hasMoreElements(){
if ( array2[i] < array1[i]
-- perform a particualr insert statement
else if ( array2[i] > array1[i]
-- perform another insert statement
else
for (int j = 0; j < array2.length; j++){
if ( array2[i] == array1[i]
-- insert
else
-- an insert statement
}
}


}


Hope this makes some sense. Help is much appreciated.

Thank You.

dufffman@gmail.com schrieb:
> Hi,
>
> I want to create a stored procedure that will populate an empty table.
>
> I am a java programmer and a novice in databases. Would someone mind
> helping me translate my psuedocode into a stored procedure?
>
> create procedure MappingTable{
>
> @array1 = {1,2,3,4}
> @array2 = {10,20,30,40}
>
> @i = 0
>
> while ( array1.hasMoreElements(){
> if ( array2[i] < array1[i]
> -- perform a particualr insert statement
> else if ( array2[i] > array1[i]
> -- perform another insert statement
> else
> for (int j = 0; j < array2.length; j++){
> if ( array2[i] == array1[i]
> -- insert
> else
> -- an insert statement
> }
> }
>
>
> }



create table #tmpArrays (Array1 int, Array2 int)

insert #tmpArrays values( 1, 10)
insert #tmpArrays values( 2, 20)
insert #tmpArrays values( 3, 30)
insert #tmpArrays values( 4, 40)

declare @array1, @array2 int

declare row cursor for
select Array1, Array2
from #tmpArrays

open row

fetch row into @array1, @array2

while (@@sqlstatus = 0)
begin

if (@array2 < @array1)
begin
--- your insert statement
end
else
--- bla bla bla
--- and so on
--- next if statement and so on


--- end while, get next values from array

fetch row into @array1, @array2
end

close row
deallocate cursor row


Obviously, I did not test it. But it could give you an idea how to do it.

Cheers,

Dietmar

dufffman@gmail.com wrote in
news:1126813555.086515.215820@g44g2000cwa.googlegr oups.com:

> I am a java programmer and a novice in databases. Would someone
> mind helping me translate my psuedocode into a stored procedure?
>

Hi,

If you'd like your application to scale, you should know processing
data row at a time is at least 10x slower than in a set.

Think of arrays as tables. Load all the data into a table, then use
set based logic to process the data.

Cheers,
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com

>> I am a java programmer and a novice in databases. <<

SQL is a declarative language. We do not like to write procedural code
at all! We also do not have arrays; the ONLY data structure is a table.
In short, your question pseudo-code makes no sense.

What we would look more like this:

CREATE TABLE Array1
(subscript INTEGER NOT NULL PRIMARY KEY,
value INTEGER NOT NULL);

CREATE TABLE Array2
(subscript INTEGER NOT NULL PRIMARY KEY,
value INTEGER NOT NULL);

BEGIN
INSERT INTO Foobar2 (..)
SELECT ..
FROM FakeArray1 AS A1, FakeArray2 AS A2
WHERE A1.subscript = A2.subscript
AND A1.value > A1.value;

INSERT INTO Foobar3 (..)
SELECT ..
FROM FakeArray1 AS A1, FakeArray2 AS A2
WHERE A1.subscript = A2.subscript
AND A1.value = A1.value;
END;

See? No loops, no if', just pure set-oriented operations. It will take
you about a year of full-time SQL programming to un-learn procedural
coding.

--CELKO-- schrieb:

>>>I am a java programmer and a novice in databases. <<
>
>
> SQL is a declarative language. We do not like to write procedural code
> at all! We also do not have arrays; the ONLY data structure is a table.
> In short, your question pseudo-code makes no sense.

Makes no sense. For what is PL/SQL (Oracle) and Transact SQL in Sybase?
Because procedural code is not necessary? Sure, your code is 100% pure
non procedural.


> What we would look more like this:

> See? No loops, no if', just pure set-oriented operations. It will take
> you about a year of full-time SQL programming to un-learn procedural
> coding.

Sorry, nonsense.

Cheers,

Dietmar.

>> For what is PL/SQL (Oracle) and Transact SQL in Sybase? Because procedural code is not necessary? <<

Those are all proprietary tools from early days of SQL, when systems
were built on top of existing file systems. And the Standard SQL/PSM
also exists. You use them for kludges when you do not know how to
write SQL or have to write code that gets to the engine internals.

>> Sorry, nonsense. <<

No, I based that estimate on 20 years experience teaching SQL to
procedural programmers in college and industry. Oh, I also have written
a few books on the language Will you tell me why you think that
estimate is wrong?

--CELKO-- schrieb:
>>>For what is PL/SQL (Oracle) and Transact SQL in Sybase? Because procedural code is not necessary? <<
>
>
> Those are all proprietary tools from early days of SQL, when systems
> were built on top of existing file systems. And the Standard SQL/PSM
> also exists. You use them for kludges when you do not know how to
> write SQL or have to write code that gets to the engine internals.

>>>Sorry, nonsense. <<
>
> No, I based that estimate on 20 years experience teaching SQL to
> procedural programmers in college and industry. Oh, I also have written
> a few books on the language Will you tell me why you think that
> estimate is wrong?

We are often given internal training to staff or at the client site on
our database model which is from the financial sector and the database
has more than 1000 tables. If we would teach it from the ivory tower
point of view in a academic way, it would take us 1 year to teach
design of some pure SQL procedures that allow us to calculate some Loans
including linear interpolation in a yield curve or applying some more
complex math calc (including built-in functions like exp() and so on.
Not to use some cursors or while statements simply would make the code
too complex or we would need java, C, C++ or VBA which would make it
again more complex. I think mixing procedural code (avoiding it where
possible) with good SQL data retrieval statements is the best approach
to get good productivity and performance.

Dietmar

Hi

Which raises the point of why are you doing those calculations in the
database?

A middle tier would do it a lot more efficiently. T-SQL or PL-SQL are not
the fastest in the world to do number crunching.

A middle tier can scale out, a database hits a finite hardware limit.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"D_Peglow" <news_peg02@snafu.de> wrote in message
news:3p4rbrF8jhh9U1@uni-berlin.de...
> --CELKO-- schrieb:
>>>>For what is PL/SQL (Oracle) and Transact SQL in Sybase? Because
>>>>procedural code is not necessary? <<
>>
>>
>> Those are all proprietary tools from early days of SQL, when systems
>> were built on top of existing file systems. And the Standard SQL/PSM
>> also exists. You use them for kludges when you do not know how to
>> write SQL or have to write code that gets to the engine internals.
>
>>>>Sorry, nonsense. <<
>>
>> No, I based that estimate on 20 years experience teaching SQL to
>> procedural programmers in college and industry. Oh, I also have written
>> a few books on the language Will you tell me why you think that
>> estimate is wrong?
>
> We are often given internal training to staff or at the client site on our
> database model which is from the financial sector and the database has
> more than 1000 tables. If we would teach it from the ivory tower point of
> view in a academic way, it would take us 1 year to teach design of some
> pure SQL procedures that allow us to calculate some Loans including linear
> interpolation in a yield curve or applying some more complex math calc
> (including built-in functions like exp() and so on. Not to use some
> cursors or while statements simply would make the code too complex or we
> would need java, C, C++ or VBA which would make it again more complex. I
> think mixing procedural code (avoiding it where possible) with good SQL
> data retrieval statements is the best approach to get good productivity
> and performance.
>
> Dietmar

>> .. it would take us 1 year to teach design of some pure SQL procedures that allow us to calculate some Loans including linear interpolation in a yield curve or applying some more complex math calc (including built-in functions like exp() and so on. <<

Or you could find a solution for NPV and IRR using auxiliary function
tables, a quick look at the details are in Chapter 22 of SQL FOR
SMARTIES. That is how people did it before calculators and cheap
computers. It also has the advantage of operating in parallel instead
of on a row-by-row basis, and you can avoid linear interpolation in
favor of a second delta interpolation so the results are more accurate.


The auxiliary table is loaded once and the programming is a simple
join. I doubt that it would take a year to do this

LISP and APL programmers understand SQL almost immediately. It is
procedural programmers that fall back to cursors and procedures when
they are firt learning. I have written five cursors in my career and I
know that if we had the CASE expression 15-20 years ago, I could have
avoided at least three of them.

Mike Epprecht (SQL MVP) schrieb:
> Hi
>
> Which raises the point of why are you doing those calculations in the
> database?

Complex calculations like monte carlo, black scholes and stuff like that
are done outside the database (in C++) of course. Some simpler reports
including NPV calcs can be done inside the database in some cases where
C++/java development would be shooting at a small bird using nuclear
weapons.

D.