How to convert this PL/SQL procedure to MySQL?
Hi,
I have to convert some stored procedures from Oracle Server 9.2 to MySQL Server 5.0. I don't manage to convert this one:
CREATE OR REPLACE PROCEDURE compiled_query IS
TYPE cur_typ IS REF CURSOR;
cRef cur_typ;
p_id paths.path_id%type;
begin
-- Prepare the intermediate tables
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE bf';
EXCEPTION
WHEN OTHERS THEN
null;
END;
EXECUTE IMMEDIATE 'CREATE TABLE bf (
tuple_id number,
a number references node(id)
)';
DELETE FROM matchnode;
DELETE FROM tmpnode;
DELETE FROM tmpedge;
-- Compute the match graph in one big query
EXECUTE IMMEDIATE 'INSERT INTO bf
SELECT
seq_bindingfunction.nextval,
a.id
FROM
node a
WHERE
( select count(*) from closure e where to_id = a.id) > 10 AND
( select count(*) from closure e where from_id = a.id) > 18';
-- Restructure the match graph to make it easier accessible
EXECUTE IMMEDIATE 'INSERT INTO matchnode
SELECT DISTINCT ''a'', a
FROM bf';
-- Construct the result graph from the match graph
-- Found node select function for node variable a
INSERT INTO tmpnode
SELECT DISTINCT node_id from matchnode
WHERE node_name = 'a'
MINUS
SELECT DISTINCT node_id
FROM tmpnode;
-- Finished
COMMIT;
end compiled_query;
Thanks for your help.
Laurent HENNION
INSERM Unité 522
Subject
Views
Written By
Posted
How to convert this PL/SQL procedure to MySQL?
8175
March 30, 2006 04:01AM
3077
April 01, 2006 06:34AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.