Code refactoring…
Steven Feuerstein wrote an entry on his Toadworld blog where he checks the refactoring capabilities of SQL-Developer.
I am trying the same things in PL/SQL Developer to see if this does do the trick.
First of all, a different couple of options are presented to you when you wrong click (or right click) a piece of highlighted code, depending on what you selected.
Let’s take the following code as our starting point:
<span style="#0000ff">create</span> <span style="#0000ff">or</span> replace <span style="#0000ff">procedure</span> use_cursor (
security_level_in <span style="#0000ff">in</span> pls_integer
, cursor_out <span style="#0000ff">in</span> <span style="#0000ff">out</span> number
)
authid definer
<span style="#0000ff">is</span>
<span style="#0000ff">begin</span>
cursor_out := dbms_sql.open_cursor(security_level_in);
dbms_sql.parse ( cursor_out
, <span style="#006080">'select count(*) from all_source'</span>
, dbms_sql.native
);
<span style="#0000ff">end</span>;
Now select the first line of the executable section of our code and choose ‘Extract procedure…’ from the popup menu:
We are now presented with the following dialog to name the new procedure:
After pressing the OK button, our code is changed to:
<span style="#0000ff">create</span> <span style="#0000ff">or</span> replace <span style="#0000ff">procedure</span> use_cursor (
security_level_in <span style="#0000ff">in</span> pls_integer
, cursor_out <span style="#0000ff">in</span> <span style="#0000ff">out</span> number
)
authid definer
<span style="#0000ff">is</span>
<span style="#008000">-- Refactored procedure open_cursor_with_sl </span>
<span style="#0000ff">procedure</span> open_cursor_with_sl(security_level_in <span style="#0000ff">in</span> pls_integer, cursor_out <span style="#0000ff">in</span> <span style="#0000ff">out</span> number) <span style="#0000ff">is</span>
<span style="#0000ff">begin</span>
cursor_out := dbms_sql.open_cursor(security_level_in);
<span style="#0000ff">end</span> open_cursor_with_sl;
<span style="#0000ff">begin</span>
open_cursor_with_sl(security_level_in, cursor_out);
dbms_sql.parse ( cursor_out
, <span style="#006080">'select count(*) from all_source'</span>
, dbms_sql.native
);
<span style="#0000ff">end</span>;
It seems to do it’s work like it’s supposed to. It even compiles, which makes it tested 
Now, let’s refactor some variable names. PL/SQL Developer wants you to highlight the first occurrence of the variable, for it to enable the ‘Rename item…’ option.
Using this code as a starting point:
<span style="#0000ff">create</span> <span style="#0000ff">or</span> replace <span style="#0000ff">procedure</span> use_cursor (
security_level_in <span style="#0000ff">in</span> pls_integer
)
authid definer
<span style="#0000ff">is</span>
l_cursor number;
<span style="#0000ff">begin</span>
l_cursor := dbms_sql.open_cursor(security_level_in);
dbms_sql.parse ( l_cursor
, <span style="#006080">'select count(*) from all_source'</span>
, dbms_sql.native
);
dbms_sql.close_cursor(l_cursor);
<span style="#0000ff">end</span>;
after selecting the first occurrence of l_cursor and selecting ‘Rename item…’ from the popup menu we are presented with the following dialog:
After typing in the new name of the variable, the code looks like this:
<span style="#0000ff">create</span> <span style="#0000ff">or</span> replace <span style="#0000ff">procedure</span> use_cursor (
security_level_in <span style="#0000ff">in</span> pls_integer
)
authid definer
<span style="#0000ff">is</span>
l_dyn_cursor number;
<span style="#0000ff">begin</span>
l_dyn_cursor := dbms_sql.open_cursor(security_level_in);
dbms_sql.parse ( l_dyn_cursor
, <span style="#006080">'select count(*) from all_source'</span>
, dbms_sql.native
);
dbms_sql.close_cursor(l_dyn_cursor);
<span style="#0000ff">end</span>;
Now, let’s add a reference to a package variable with the same name as we just changed the variable to:
<span style="#0000ff">create</span> <span style="#0000ff">or</span> replace <span style="#0000ff">procedure</span> use_cursor (
security_level_in <span style="#0000ff">in</span> pls_integer
)
authid definer
<span style="#0000ff">is</span>
l_dyn_cursor number;
<span style="#0000ff">begin</span>
l_dyn_cursor := dbms_sql.open_cursor(security_level_in);
dbms_sql.parse ( l_dyn_cursor
, <span style="#006080">'select count(*) from all_source'</span>
, dbms_sql.native
);
dbms_sql.close_cursor(l_dyn_cursor);
my_package.l_dyn_cursor := 10;
<span style="#0000ff">end</span>;
When the ‘Rename item…’ option is selected, the same dialog is displayed as before (makes sense, we are doing pretty much the same thing):
After the refactoring the code looks like this:
<span style="#0000ff">create</span> <span style="#0000ff">or</span> replace <span style="#0000ff">procedure</span> use_cursor (
security_level_in <span style="#0000ff">in</span> pls_integer
)
authid definer
<span style="#0000ff">is</span>
l_dyn_cursor2 number;
<span style="#0000ff">begin</span>
l_dyn_cursor2 := dbms_sql.open_cursor(security_level_in);
dbms_sql.parse ( l_dyn_cursor2
, <span style="#006080">'select count(*) from all_source'</span>
, dbms_sql.native
);
dbms_sql.close_cursor(l_dyn_cursor2);
my_package.l_dyn_cursor := 10;
<span style="#0000ff">end</span>;
As you can see, it didn’t touch the variable from the package, even though it has the same name as the variable being refactored. There is no option to change the case of the selection, but I have to agree with Steven on this one, that’s no real refactoring, more a layout issue.
(This entry is cross-posted at http://blog.bar-solutions.com)
Recent Comments