PLSQL rocks..

Most of us know PLSQL is an extension of SQL meant for procedural needs and computations which cannot be done using SQL/"set theory"... It takes a while for programmers coming from procedural programming world (Cobol, Java, C etc) to switch to SQL because thinking in sets needs some time and practise before you can start enjoying the fun.

But fortunately or unfortunately, people who dont find SQL solution easily still somehow manage to solve everything in Oracle just by writing lots of procedural code using PL/SQL and call it within SQL..One classic example (funny too) is illustrated here.

But check out the PLSQL package guide and you will be amazed how many useful APIs there are..actually its been tough to catch up with the speed of new packages getting introduced with every new release.

I just happened to read something really cool and was amazed..Would you believe Oracle's engine has the capability of solving Linear algebra problems? Read on ..to believe..


From PLSQL documentation:

UTL_NAV.LAPACK_GESV Procedures
This procedure computes the solution to a real system of linear equations
a * x = b
where a is an n by n matrix and x and b are n by nrhs matrices.
The LU decomposition with partial pivoting and row interchanges is used to factor A
as
a = P * L * U
where P is a permutation matrix, L is unit lower triangular, and U is upper triangular.
The factored form of a is then used to solve the system of equations
a * x = b

Syntax
UTL_NLA.LAPACK_GESV (
n IN POSITIVEN,
nrhs IN POSITIVEN,
a IN OUT UTL_NLA_ARRAY_DBL,
lda IN POSITIVEN,
ipiv IN OUT UTL_NLA_ARRAY_INT,
b IN OUT UTL_NLA_ARRAY_DBL,
ldb IN POSITIVEN,
info OUT INTEGER,
pack IN flag DEFAULT 'C');

Solving Linear equation with PL/SQL :


I am taking a practical problem from this site :
http://mathforum.org/library/drmath/view/57278.html

Problem:
(1) A has three times as many sweets as B. If he gives B six sweets,
he will then have twice as many as B then has. How many sweets did
they each have to start with?

Solution:

Let x = number of sweets that B has initially; then 3x is the number
that A has. If now A gives 6 sweets to B then A has 3x-6 sweets and B
has x+6 sweets. Now we are told that after this transfer, A has twice
as many sweets as B, so we can write down an equation to represent
this fact, i.e.

3x-6 = 2(x+6)
3x-6 = 2x + 12
3x-2x = 12 + 6
x = 18

So initially B had 18 sweets and A had 3*18 = 54 sweets.

Check: After transfer B has 18+6 = 24, A has 54-6 = 48, and 48 is
twice 24.

Now, lets solve this using PL/SQL

Lets represent the data in Matrix form first..

Lets say 'a' is the number of sweets A has & 'b' is the number of sweets with B

i) Before Transfer..

a-3b=0 (since a exactly is 3 times of b)

ii) After transfer (A gives 6 sweets to B)

a-6 = 2 (b+6) (after transfer A has double the amount of what B has)
a-6 = 2b +12
a-2b = 18


So we have two equations..
a - 3b = 0 -- Equation 1
a - 2b = 18 -- Equation 2

Representing the same by Matrix theory..


| 1 -3 | | a | __ | 0 |
| 1 -2 | | b | -- | 18 |

<---A--> <-X-> -- <-B--> <== Matrix Notation

Now all we have to do is find values of Matrix X

Lets feed the same value into PLSQL and save ourselves some time...



SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 A utl_nla_array_dbl := utl_nla_array_dbl(1,1,-3,-2);
3 B utl_nla_array_dbl := utl_nla_array_dbl( 0,18);
4 X utl_nla_array_int := utl_nla_array_int(0,0);
5 result integer;
6 BEGIN
7 UTL_NLA.LAPACK_GESV (
8 n => 2, /* MATRIX A is 2*2 */
9 nrhs => 1, /* MATRIX B has 1 column */
10 a => A, /* PASS MATRIX A */
11 lda => 2, /* max(1,2)=2 */
12 ipiv => X, /******** THIS IS WHAT WE WANT TO FIND */
13 b => B, /* PASS MATRIX B */
14 ldb => 2, /* max(1,2)=2 */
15 info => result, /* Code execution status */
16 pack => 'C' /* default column-wise */
17 );
18 IF result=0 THEN
19 dbms_output.put_line('PLSQL call was successfully executed');
20 dbms_output.put_line(' Results');
21 dbms_output.put_line(' -------------');
22 FOR i IN 1..B.count LOOP
23 dbms_output.put_line('- '||chr(64+i) ||' = ' || TO_CHAR(B(i),'999'));
24 END LOOP;
25 ELSE
26 dbms_output.put_line('Error during PLSQL call..Troubleshooting reqd');
27 END IF;
28* END;

SQL> /
PLSQL call was successfully executed
Results
-------------
- A = 54
- B = 18

PL/SQL procedure successfully completed.

How cool is that? There are lots of subprograms in the same UTL_NAV package to solve other situations (say if matrix A is not n*n matrix but instead happens to be m*n size)..


Have more fun..by changing the data and see how fast can your PL/SQL program actually respond..I tried this site to try couple of more linear equation problems and its working great

Something **NEW** learnt for the day :)