Home » SQL & PL/SQL » SQL & PL/SQL » please help me with Travelling Salesman Problem (oracle 11g plsql)
| please help me with Travelling Salesman Problem [message #601326] |
Tue, 19 November 2013 12:56  |
 |
liloracle
Messages: 4
Registered: November 2013
|
Junior Member |
|
|
Hello.
Please help me with this task. I'm trying to make it almost a month and does not work.
You must use: pl / sql, with no functions, procedures, and dynamic code.
In the Internet I am find this code, but it's not work on my computer, and it uses the functions and procedures, packages and dynamic sql.
Please help me.
drop table salesmans_scrambled_dist_chart;
create table salesmans_scrambled_dist_chart
(
source varchar2(5),
destination varchar2(5),
distance number(5,0)
);
truncate table salesmans_scrambled_dist_chart;
insert into salesmans_scrambled_dist_chart values ('-AGR-','-DEL-',250);
insert into salesmans_scrambled_dist_chart values ('-AGR-','-PTN-',700);
insert into salesmans_scrambled_dist_chart values ('-AGR-','-GYA-',800);
insert into salesmans_scrambled_dist_chart values ('-AGR-','-ALD-',300);
insert into salesmans_scrambled_dist_chart values ('-GYA-','-ALD-',500);
insert into salesmans_scrambled_dist_chart values ('-ALD-','-PTN-',333);
insert into salesmans_scrambled_dist_chart values ('-ALD-','-DEL-',601);
insert into salesmans_scrambled_dist_chart values ('-GYA-','-PTN-',100);
insert into salesmans_scrambled_dist_chart values ('-PTN-','-DEL-',1050);
insert into salesmans_scrambled_dist_chart values ('-DEL-','-GYA-',1100);
commit;
create or replace view v_distance_chart as
select distinct
source,
destination,
distance
FROM
(
SELECT
source,
destination,
distance
FROM
salesmans_scrambled_dist_chart
UNION ALL
SELECT
destination AS source,
source AS destination,
distance
FROM
salesmans_scrambled_dist_chart);
drop table all_cities;
create table all_cities
(
sno number(2,0),
city varchar2(5)
);
truncate table all_cities;
insert into all_cities(sno, city) select sno, city from v_all_cities;
commit;
create or replace procedure scramble_cities is
v_retval number(2,0);
begin
dbms_output.put_line('uninplemented!!!');
end scramble_cities;
create or replace function get_sql_city_sequence return varchar2 is
v_sql_stmt varchar2(4000);
v_sql_col1 varchar2(1000);
v_sql_col2 varchar2(1000);
v_sql_tbl varchar2(500);
v_sql_where varchar2(1500);
v_no_of_elements number(2,0);
v_index number(2,0);
v_inner_index number(2,0);
v_bool number(1,0);
v_pipe varchar2(4);
v_add varchar2(4);
v_comma varchar2(3);
v_and varchar2(5);
begin
v_bool := 0;
select count(*) into v_no_of_elements from all_cities;
v_pipe := '';
v_add := '';
v_comma := '';
v_and := '';
for v_index in 1..v_no_of_elements
loop
v_sql_col1 := v_sql_col1 || v_pipe || 'nvl(t' || v_index || '.CITY,'')';
if v_index > 1 then
v_sql_col2 := v_sql_col2 || v_add || 'distance_between_cities(t' || v_index || '.city, t' || to_char(v_index - 1) || '.city)';
else
v_sql_col2 := 0;
end if;
v_sql_tbl := v_sql_tbl || v_comma || 'all_cities t' || v_index;
v_inner_index := v_index + 1;
for v_inner_index in v_index + 1 .. v_no_of_elements
loop
if v_bool = 1 then
v_pipe := ' || ';
v_add := ' + ';
v_comma := ' , ';
v_and := ' AND ';
end if;
v_sql_where := v_sql_where || v_and || ' t'|| v_index ||'.SNO != t'|| v_inner_index ||'.SNO ';
v_bool := 1;
end loop;
end loop;
v_sql_stmt := v_sql_stmt ||' select ' || v_sql_col1 ||' as route, ' || v_sql_col2 ||' as distance_to_be_covered FROM ' || v_sql_tbl || ' WHERE ' || v_sql_where || ' order by distance_to_be_covered';
return(v_sql_stmt);
end get_sql_city_sequence;
create or replace function distance_between_cities( v_city1 varchar, v_city2 varchar) return number is
v_retval number(5,0);
begin
select
distance into v_retval
from v_distance_chart
where
source = v_city1
and destination = v_city2;
return v_retval;
end distance_between_cities;
select pkg_salesman_scramble.get_sql_city_sequence() from dual; -- pick the output and execute the dynamic sql, say str_dyn_sql so generated.
execute str_dyn_sql
|
|
|
|
| Re: please help me with Travelling Salesman Problem [message #601327 is a reply to message #601326] |
Tue, 19 November 2013 13:08  |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Welcome to OraFAQ.
Quote:You must use: pl / sql, with no functions, procedures, and dynamic code.
Quote:In the Internet I am find this code, but it's not work on my computer, and it uses the functions and procedures, packages and dynamic sql.
Then why are you trying to work with it at all?
You have not told us what it is that you are trying to do.
You have not told us what the exact problem is.
You have not bothered to read the forum guidelines.
|
|
|
|
| Re: please help me with Travelling Salesman Problem [message #601329 is a reply to message #601327] |
Tue, 19 November 2013 13:26 |
|
liloracle
Messages: 4
Registered: November 2013
|
Junior Member |
|
|
pablolee wrote on Tue, 19 November 2013 13:08Welcome to OraFAQ.
Quote:You must use: pl / sql, with no functions, procedures, and dynamic code.
Quote:In the Internet I am find this code, but it's not work on my computer, and it uses the functions and procedures, packages and dynamic sql.
Then why are you trying to work with it at all?
You have not told us what it is that you are trying to do.
You have not told us what the exact problem is.
You have not bothered to read the forum guidelines.
i'am trying to work with pl/sql because it's the main condition of my task.
i told that i am trying to solve travelling salesman problem. you don't know what is it?
the main problem - i don't know how to do it. i can write it on c#, c++, visual basic byut i can't make it on pl/sql.
it's dufficult for me to read al guidelines because English is not my general language. I'm trying to find this decision on this forum but not found.
|
|
|
|
| Re: please help me with Travelling Salesman Problem [message #601331 is a reply to message #601326] |
Tue, 19 November 2013 13:38 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
liloracle wrote on Wed, 20 November 2013 00:26
drop table salesmans_scrambled_dist_chart;
create table salesmans_scrambled_dist_chart
(
source varchar2(5),
destination varchar2(5),
distance number(5,0)
);
truncate table salesmans_scrambled_dist_chart;
Truncate is unnecessary here, since, the newly created table is obviously having no rows.
Quote:
drop table all_cities;
create table all_cities
(
sno number(2,0),
city varchar2(5)
);
truncate table all_cities;
Once again, Truncate is unnecessary here, since, the newly created table is obviously having no rows.
Quote:create or replace procedure scramble_cities is
v_retval number(2,0);
begin
dbms_output.put_line('uninplemented!!!');
end scramble_cities;
This procedure is useless. What is it doing anyway? What is the whole logic behind this procedure code? Why is the variable "v_retval" even declared?
Please mention clearly your requirements.
|
|
|
|
|
|
| Re: please help me with Travelling Salesman Problem [message #601335 is a reply to message #601334] |
Tue, 19 November 2013 14:10 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
No-one will give you an answer, because that would hinder your learning.
There are several well known algorithms for this problem. Probably the easiest is to test each possible route. If you have ten points, that is only ten factorial possibilities. But if you want maximum marks for this assignment, you'll need a more intelligent approach.
Recursion is probably a technique you will need to consider.
|
|
|
|
| Re: please help me with Travelling Salesman Problem [message #601337 is a reply to message #601335] |
Tue, 19 November 2013 14:16 |
|
liloracle
Messages: 4
Registered: November 2013
|
Junior Member |
|
|
John Watson wrote on Tue, 19 November 2013 14:10No-one will give you an answer, because that would hinder your learning.
There are several well known algorithms for this problem. Probably the easiest is to test each possible route. If you have ten points, that is only ten factorial possibilities. But if you want maximum marks for this assignment, you'll need a more intelligent approach.
Recursion is probably a technique you will need to consider.
mmm... if somebody did not decide that for me I will be expelled.
this task is not for the maximum score...
[Updated on: Tue, 19 November 2013 14:18] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 12:56:08 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
