MySQL Forums
Forum List  »  Oracle

Re: How to convert this PL/SQL procedure to MySQL?
Posted by: Roland Bouman
Date: April 01, 2006 06:34AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
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.