Mark Barinstein,
Спасибо - попробовал. Пока не получается. Вот моя версия - так что может дело еще в этом:
1.
2.
3.
4.
5.
6.
D:\work>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09057" with
level identifier "06080107".
Informational tokens are "DB2 v9.5.700.579", "s101129", "IP23137", and Fix Pack
"7".
Product is installed at "D:\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".
Выхлоп ниже:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
D:\work>db2 < test.sql
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.5.7
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => DB21034E The command was processed as an SQL statement because it was no
t a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "on@" was found following "set serveroutput ".
Expected tokens may include: "=". SQLSTATE=42601
db2 => db2 => DB21034E The command was processed as an SQL statement because it
was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "BEGIN
ATOMIC ". Expected tokens may include: "END". LINE NUMBER=1.
SQLSTATE=42601
db2 => DB21034E The command was processed as an SQL statement because it was no
t a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "row" was found following "FOR ". Expected
tokens may include: "JOIN <joined_table>". SQLSTATE=42601
db2 =>
IDENTIFIER TYPE
---------------------------------------------------------------- ----
Canada CNTY
United States CNTY
Australia CNTY
3 record(s) selected.
db2 => DB21034E The command was processed as an SQL statement because it was no
t a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "DO".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
db2 => SQL0206N "ROW.IDENTIFIER" is not valid in the context where it is used.
SQLSTATE=42703
db2 => DB21034E The command was processed as an SQL statement because it was no
t a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "FOR" was found following "END ". Expected
tokens may include: "JOIN <joined_table>". SQLSTATE=42601
db2 => DB21034E The command was processed as an SQL statement because it was no
t a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END@".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
db2 =>
Не похоже на то, что ему нравится даже самое начало. Пробовал
1.
2.
3.
4.
5.
6.
7.
8.
9.
set serveroutput on@
BEGIN ATOMIC
FOR row AS
SELECT identifier, type FROM wcsdbuser.geonode where type = 'CNTY'
DO
call dbms_output.put_line(row.identifier);
END FOR;
END@
Запускаю так:
Также пробовал
1.
2.
3.
4.
5.
6.
7.
8.
9.
set serveroutput=on@
BEGIN ATOMIC
FOR row AS
SELECT identifier, type FROM wcsdbuser.geonode where type = 'CNTY'
DO
call dbms_output.put_line(row.identifier);
END FOR;
END@
Теперь ругается на SERVEROUTPUT:
1.
2.
3.
4.
db2 => DB21034E The command was processed as an SQL statement because it was no
t a
valid Command Line Processor command. During SQL processing it returned:
SQL0206N "SERVEROUTPUT" is not valid in the context where it is used.
SQLSTATE=42703
ну и дальше прежние ошибки.
Марк, спасибо - почитал про мою версию SQL PL, вроде все должно работать. Я правильно понимаю, что эта структура называется Inline Compound Statement? Может есть какие-то ограничения по использованию циклов? Но я ведь видел пример, собственно из которого и делал свой блок:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
CREATE TABLE TARGET
(PK INTEGER NOT NULL
PRIMARY KEY, C1 INTEGER);
CREATE TABLE EXCEPT
(PK INTEGER NOT NULL
PRIMARY KEY, C1 INTEGER);
CREATE TABLE SOURCE
(PK INTEGER NOT NULL
PRIMARY KEY, C1 INTEGER);
CREATE FUNCTION DISCRETIZE(RAW INTEGER) RETURNS INTEGER
RETURN CASE
WHEN RAW < 0 THEN CAST(NULL AS INTEGER)
WHEN RAW > 1000 THEN NULL
ELSE ((RAW / 10) * 10) + 5
END;
INSERT INTO SOURCE (PK, C1)
VALUES (1, -5),
(2, NULL),
(3, 1200),
(4, 23),
(5, 10),
(6, 876);
BEGIN ATOMIC
FOR ROW AS
SELECT PK, C1, DISCRETIZE(C1) AS D FROM SOURCE
DO
IF ROW.D IS NULL THEN
INSERT INTO EXCEPT VALUES(ROW.PK, ROW.C1);
ELSE
INSERT INTO TARGET VALUES(ROW.PK, ROW.D);
END IF;
END FOR;
END
Там опять же 9.7, но по идее должно работать и в 9.5? Прошу прощения у профессионалов за такой дилетантский вопрос, просто уже нет мочи переводить простыни кодов ошибок.
