Code brut sans commentaire, l'article de démo est à venir !
CREATE TABLE cs_mouv (
x number,
y number,
x_dest number,
y_dest number,
n_mouv number,
piece char(1),
col char(1),
move_str varchar2(10),
fic char(1) default 'N'
);
/
CREATE OR REPLACE VIEW pieces AS
SELECT x_dest x, y_dest y, max(piece) keep(dense_rank last order by n_mouv) piece
, max(col) keep(dense_rank last order by n_mouv) col
FROM (
SELECT x_dest, y_dest, n_mouv
, piece
, col
FROM cs_mouv
WHERE x_dest IS NOT NULL
UNION ALL
SELECT x, y, n_mouv
, null
, null
FROM cs_mouv
)
GROUP BY x_dest, y_dest
/
CREATE OR REPLACE PACKAGE pkg_csboard
AS
PROCEDURE init;
PROCEDURE display(p_col IN CHAR DEFAULT 'B');
PROCEDURE display_list;
PROCEDURE csmove(p_move IN VARCHAR2);
PROCEDURE cancel_move(p_cnt IN NUMBER);
END pkg_csboard;
/
CREATE OR REPLACE PACKAGE BODY pkg_csboard
AS
move_format EXCEPTION;
PROCEDURE init
IS
BEGIN
DELETE FROM cs_mouv;
INSERT INTO cs_mouv (x_dest, y_dest, n_mouv, piece, col, fic)
SELECT mod(level-1,8)+1, case trunc((level - 1) / 8) when 0 then 2 else 7 end
, -16 + level, 'P', case trunc((level - 1) / 8) when 0 then 'B' else 'N' end, 'Y'
FROM dual
CONNECT BY level <= 16
UNION ALL
SELECT mod(level-1,2) * 7 + 1, trunc((level-1) / 2) * 7 + 1, -32 + level
, 'T', CASE trunc((level-1) / 2) when 0 then 'B' else 'N' end, 'Y'
FROM dual
CONNECT BY level <= 4
UNION ALL
SELECT 7 - mod(level-1,2) * 5, trunc((level-1) / 2) * 7 + 1, -28 + level
, 'C', CASE trunc((level-1) / 2) when 0 then 'B' else 'N' end, 'Y'
FROM dual
CONNECT BY level <= 4
UNION ALL
SELECT 6 - mod(level-1,2) * 3, trunc((level-1) / 2) * 7 + 1, -24 + level
, 'F', CASE trunc((level-1) / 2) when 0 then 'B' else 'N' end, 'Y'
FROM dual
CONNECT BY level <= 4
UNION ALL
SELECT 4, 1, -19, 'D', 'B', 'Y' FROM dual UNION ALL
SELECT 5, 1, -18, 'R', 'B', 'Y' FROM dual UNION ALL
SELECT 4, 8, -17, 'D', 'N', 'Y' FROM dual UNION ALL
SELECT 5, 8, -16, 'R', 'N', 'Y' FROM dual;
COMMIT;
display;
END init;
PROCEDURE display(p_col CHAR default 'B')
IS
BEGIN
dbms_output.enable;
FOR c IN (WITH cboard AS (
SELECT mod(level-1, 8) + 1 x, trunc((level - 1) / 8) + 1 y
FROM dual
CONNECT BY level <= 64)
, unord AS (
SELECT CASE p_col WHEN 'N' THEN c.y ELSE 9 - c.y END line_nr, to_char(c.y) || '|' || LISTAGG(COALESCE(p.piece || lower(p.col), ' ') || '|') WITHIN GROUP(ORDER BY c.x) disp_line
FROM cboard c
LEFT OUTER JOIN pieces p ON c.x = p.x AND c.y = p.y
GROUP BY c.y
UNION ALL
SELECT -0.5 + level, rpad(' ', 26, '_')
FROM DUAL
CONNECT BY level <= 9
UNION ALL
SELECT 9, ' ' || LISTAGG(' ' || chr(ASCII('a') + level - 1) || ' ') WITHIN GROUP (ORDER BY level)
FROM dual
CONNECT BY level <= 8
)
SELECT disp_line
FROM unord
ORDER BY line_nr)
LOOP
dbms_output.put_line(c.disp_line);
END LOOP;
END display;
PROCEDURE csmove(p_move IN VARCHAR2)
IS
input_x NUMBER;
input_y NUMBER;
input_x_dest NUMBER;
input_y_dest NUMBER;
x2 NUMBER;
y2 NUMBER;
x_dest2 NUMBER;
y_dest2 NUMBER;
type_move NUMBER;
turn CHAR(1);
FUNCTION parse_move(p_move IN VARCHAR2, p_turn IN CHAR) RETURN NUMBER
IS
ret NUMBER; -- -1 erreur, 1 normal, 2 prise en passant, 3 promotion, 4 petit roque, 5 grand roque
BEGIN
IF REGEXP_LIKE(p_move, '([a-h][1-8]){2}(C|F|D|T)?') THEN
input_x := ascii(substr(p_move, 1, 1)) - ascii('a') + 1;
input_y := to_number(substr(p_move, 2, 1));
input_x_dest := ascii(substr(p_move, 3, 1)) - ascii('a') + 1;
input_y_dest := to_number(substr(p_move, 4, 1));
ret := 1;
-- IF length(p_move) = 4 AND abs(input_x_dest - input_x) = 1 abs(input_y_dest - input_y) = 1 THEN
--
-- ELSIF
--
-- END IF
ELSE
IF (p_move = 'oo') THEN
input_x := 5;
input_y := CASE p_turn WHEN 'B' THEN 1 ELSE 8 END;
input_x_dest := 7;
input_y_dest := input_y;
x2 := 8;
y2 := CASE p_turn WHEN 'B' THEN 1 ELSE 8 END;
x_dest2 := 6;
y_dest2 := input_y;
ret := 4;
ELSE IF (p_move = 'ooo') THEN
input_x := 5;
input_y := CASE p_turn WHEN 'B' THEN 1 ELSE 8 END;
input_x_dest := 3;
input_y_dest := input_y;
x2 := 1;
y2 := CASE p_turn WHEN 'B' THEN 1 ELSE 8 END;
x_dest2 := 4;
y_dest2 := input_y;
ret := 5;
ELSE
ret := -1;
END IF;
END IF;
END IF;
RETURN ret;
END parse_move;
FUNCTION get_turn RETURN CHAR
IS
ret CHAR(1);
BEGIN
SELECT CASE COALESCE(max(col) keep(dense_rank last order by n_mouv), 'N') WHEN 'B' THEN 'N' ELSE 'B' END
INTO ret
FROM cs_mouv
WHERE fic = 'N';
RETURN ret;
END get_turn;
PROCEDURE exec_move(p_move IN VARCHAR2, p_x IN NUMBER, p_y IN NUMBER, p_x_dest IN NUMBER, p_y_dest IN NUMBER, p_fic IN CHAR default 'N')
IS
BEGIN
INSERT INTO cs_mouv(x, y, x_dest, y_dest, n_mouv, piece, col, move_str, fic)
SELECT p_x, p_y, p_x_dest, p_y_dest
, max((select max(n_mouv) + 1 from cs_mouv))
, max(piece) keep(dense_rank last order by n_mouv)
, max(col) keep(dense_rank last order by n_mouv)
, p_move
, p_fic
FROM cs_mouv
WHERE x_dest = p_x
AND y_dest = p_y;
END exec_move;
BEGIN
turn := get_turn;
type_move := parse_move(p_move, turn);
IF type_move < 0 THEN
RAISE move_format;
ELSE
exec_move(p_move, input_x, input_y, input_x_dest, input_y_dest);
IF type_move IN (4, 5) THEN
exec_move(p_move, x2, y2, x_dest2, y_dest2, 'Y');
END IF;
END IF;
COMMIT;
display;
dbms_output.put_line('OK !');
EXCEPTION
WHEN move_format THEN
dbms_output.put_line('Wrong move format');
ROLLBACK;
END csmove;
PROCEDURE display_list
IS
BEGIN
FOR c IN (WITH cs_mouv_cont AS (
SELECT x, y, x_dest, y_dest, piece, col
, row_number() over(order by n_mouv) rk
, move_str
FROM cs_mouv
WHERE fic = 'N')
SELECT lpad(round(a.rk / 2), 3) || '. ' || a.move_str || ' ' || b.move_str disp_line
FROM cs_mouv_cont a
LEFT OUTER JOIN cs_mouv_cont b ON b.rk = a.rk + 1
WHERE mod(a.rk, 2) = 1
ORDER BY a.rk)
LOOP
dbms_output.put_line(c.disp_line);
END LOOP;
END display_list;
PROCEDURE cancel_move(p_cnt IN NUMBER)
IS
BEGIN
DELETE FROM cs_mouv
WHERE n_mouv >= (SELECT min(n_mouv)
FROM (SELECT n_mouv, row_number() over(order by n_mouv desc) rk
FROM cs_mouv
WHERE fic = 'N')
WHERE rk <= p_cnt);
COMMIT;
display;
dbms_output.put_line('Cancelled ' || to_char(p_cnt) || ' moves');
END cancel_move;
END pkg_csboard;
/
show errors
/
