SQL PL newbie question 
Author Message
 SQL PL newbie question
Is it possible to run procedural code from the db2 prompt (like the
PL/SQL statements on SQL Plus in Oracle)?

I want to do something similar to the following:

begin
  declare some variable
  use cursors to fetch values
  update the database using sql statments

end

Actually, I need to port an Oracle database application and for that I
have to port several PL/SQL code snippets to work with DB27.2. Any
help to do this would be highly appreciated.

Thomas.



Wed, 11 Aug 2004 09:50:55 GMT
 SQL PL newbie question

   Sure.  Look in the "sqllib\samples\sqlproc" directory for examples.

--
Larry Menard
IBM Workstation Database (DB2) Performance Team
Defender of Geese and of all things Natural

Quote:
> Is it possible to run procedural code from the db2 prompt (like the
> PL/SQL statements on SQL Plus in Oracle)?

> I want to do something similar to the following:

> begin
>   declare some variable
>   use cursors to fetch values
>   update the database using sql statments

> end

> Actually, I need to port an Oracle database application and for that I
> have to port several PL/SQL code snippets to work with DB27.2. Any
> help to do this would be highly appreciated.

> Thomas.



Wed, 11 Aug 2004 22:33:28 GMT
 SQL PL newbie question
db2 -td%
BEGIN ATOMIC
 DECLARE a INT;
 FOR cur AS SELECT c1 FROM T WHERE c2 = a DO
   SET a = a + cur.c1;
 END FOR;
 INSERT INTO T VALUES(5000, a);
END
%

OK I have no clue what the use of this piece of logic would be, but it
will work on V7.2 :-)

Cheers
Serge
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada



Thu, 12 Aug 2004 22:51:34 GMT
 SQL PL newbie question
Thanks a ton to both Serge and Larry. This is the exact info I wanted.
Also I received an Oracle-to-DB2 porting document from IBM support.
Quote:

> db2 -td%
> BEGIN ATOMIC
>  DECLARE a INT;
>  FOR cur AS SELECT c1 FROM T WHERE c2 = a DO
>    SET a = a + cur.c1;
>  END FOR;
>  INSERT INTO T VALUES(5000, a);
> END
> %

> OK I have no clue what the use of this piece of logic would be, but it
> will work on V7.2 :-)

> Cheers
> Serge



Sat, 14 Aug 2004 01:59:28 GMT
 SQL PL newbie question
Hi Serge,

Is it required to use a cursor, even to query db and populate one
variable? Is there a better way to implement the following logic?

db2 (cont.) => for cur as select count(*) as cnt from employee do
db2 (cont.) => set x = cur.cnt+30000;
db2 (cont.) => end for;
db2 (cont.) => update employee set salary=x;

I was trying to do the equivalent of following Oracle PL/SQL code
snippet:

select count(*) into x from employee;
x := x+30000;
update employee set salary=x;

Quote:

> db2 -td%
> BEGIN ATOMIC
>  DECLARE a INT;
>  FOR cur AS SELECT c1 FROM T WHERE c2 = a DO
>    SET a = a + cur.c1;
>  END FOR;
>  INSERT INTO T VALUES(5000, a);
> END
> %

> OK I have no clue what the use of this piece of logic would be, but it
> will work on V7.2 :-)

> Cheers
> Serge



Sat, 14 Aug 2004 09:19:42 GMT
 SQL PL newbie question
Hi Thomas,

Finally someone who likes efficient SQL :-)

SELECT INTO is an equivalent to SET. However SELECT INTO is not enabled in the dynamic
compound.
The direct translation of what you presented:

BEGIN ATOMIC
DECLARE x INT;
SET x = (select count(*) into x from employee);
SET x = x+30000;
update employee set salary=x;
END
%

Or
BEGIN ATOMIC
DECLARE x INT;
SET x = (select count(*) into x from employee) + 30000;
update employee set salary=x;
END
%

Or in pure SQL.
UPDATE employee set salary=(select count(*) into x from employee) + 30000;

Note that the original (silly) FOR loop does NOT open a cursor in a dynamic compound
statement.
The technology used is "inline" SQL PL. What you write is one big honking "query". Variable
references are correlations (no bindin, bindout, context switches, etc...)

Cheers
Serge

--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada



Sat, 14 Aug 2004 22:30:02 GMT
 SQL PL newbie question
Thanks Serge. It seems, the "into x" part is not needed in the SQL
statements. Without them, your code works as expected. But gives error
otherwise. So I assume that it is a typo.
Quote:

> Hi Thomas,

> Finally someone who likes efficient SQL :-)

> SELECT INTO is an equivalent to SET. However SELECT INTO is not enabled in the dynamic
> compound.
> The direct translation of what you presented:

> BEGIN ATOMIC
> DECLARE x INT;
> SET x = (select count(*) into x from employee);
> SET x = x+30000;
> update employee set salary=x;
> END
> %

> Or
> BEGIN ATOMIC
> DECLARE x INT;
> SET x = (select count(*) into x from employee) + 30000;
> update employee set salary=x;
> END
> %

> Or in pure SQL.
> UPDATE employee set salary=(select count(*) into x from employee) + 30000;

> Note that the original (silly) FOR loop does NOT open a cursor in a dynamic compound
> statement.
> The technology used is "inline" SQL PL. What you write is one big honking "query". Variable
> references are correlations (no bindin, bindout, context switches, etc...)

> Cheers
> Serge



Sun, 15 Aug 2004 02:01:11 GMT
 SQL PL newbie question
Thanks Serge. It seems, the "into x" part is not needed in the SQL
statements. Without them, your code works as expected. But gives error
otherwise. So I assume that it is a typo.
Quote:

> Hi Thomas,

> Finally someone who likes efficient SQL :-)

> SELECT INTO is an equivalent to SET. However SELECT INTO is not enabled in the dynamic
> compound.
> The direct translation of what you presented:

> BEGIN ATOMIC
> DECLARE x INT;
> SET x = (select count(*) into x from employee);
> SET x = x+30000;
> update employee set salary=x;
> END
> %

> Or
> BEGIN ATOMIC
> DECLARE x INT;
> SET x = (select count(*) into x from employee) + 30000;
> update employee set salary=x;
> END
> %

> Or in pure SQL.
> UPDATE employee set salary=(select count(*) into x from employee) + 30000;

> Note that the original (silly) FOR loop does NOT open a cursor in a dynamic compound
> statement.
> The technology used is "inline" SQL PL. What you write is one big honking "query". Variable
> references are correlations (no bindin, bindout, context switches, etc...)

> Cheers
> Serge



Sun, 15 Aug 2004 02:01:12 GMT
 SQL PL newbie question
Oops.. indeed.. The famous copy-pase bug
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada


Sun, 15 Aug 2004 03:57:12 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. Newbie PL/SQL, SQL*Loader and Forms questions

2. Newbie-Question PL-SQL(Oracle) --- T_SQL(SQLSERVER2000)

3. Pl/sql newbie question....

4. Variable in PL/SQL query - newbie question

5. Newbie PL/SQL question

6. Newbie PL/SQL question

7. Newbie PL/SQL question

8. Newbie pl/sql question

9. Newbie PL/SQL question

10. PL/SQL Newbie Question

11. Question newbie on PL/SQL: Create function with cursor

12. newbie PL/SQL (and design) question


 
Powered by phpBB® Forum Software