Re: How to convert this PL/SQL procedure to MySQL?
Laurent HENNION wrote:
> 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
begin
DROP TABLE if exists bf;
CREATE TABLE bf (
tuple_id int auto_increment primary key
, a int references node(id)
)';
DELETE FROM matchnode;
DELETE FROM tmpnode;
DELETE FROM tmpedge;
-- Compute the match graph in one big query
INSERT INTO bf (a)
SELECT 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
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 m.node_id
from matchnode m
left join tmpnode t
on m.node_id = t.node_id
WHERE t.node_id is null
and m.node_name = 'a'
;
-- Finished
COMMIT;
end;
Just a few observations concerning your original proc
1) Consider your declarations section:
TYPE cur_typ IS REF CURSOR;
cRef cur_typ;
p_id paths.path_id%type;
These are never used, i chucked them.
2)
-- Compute the match graph in one big query
EXECUTE IMMEDIATE 'INSERT INTO
and also:
-- Restructure the match graph to make it easier accessible
EXECUTE IMMEDIATE 'INSERT INTO
etc.
Why use dynamic sql? YWhy not do a straight INSERT?
3)
-- 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;
Typically, you'd use a NOT EXISTS in oracle.
Subject
Views
Written By
Posted
8177
March 30, 2006 04:01AM
Re: How to convert this PL/SQL procedure to MySQL?
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.