PL/SQL Developer usage skills and shortcut keys

Alibaba Cloud
3 min readJan 21, 2022

--

Oracle case: PL/SQL Developer usage skills, shortcut keys

  1. SQL PLUS-like window: File->New->Command Window, this is similar to oracle’s client tool SQL plus, but it is much easier to use.

2. Set the keyword to automatically capitalize: Tools->Preferences->Editor, select the Keyword case as Uppercase. In this way, when you enter a SQL statement in the window, the keywords will be automatically capitalized, while others are lowercase. In this way, it is easier to read the code and maintain a good coding style. Similarly, in Tools->Preferences->Code Assistant (assistant), you can set the code prompt delay time, prompt when entering a few characters, and uppercase and lowercase database objects. capitalization, etc.;

3. View the execution plan: Select the SQL statement to be analyzed, and then click the Explain plan button (that is, the execution plan) on the toolbar, or press F5 directly; this is mainly used to analyze the execution efficiency of the SQL statement and the structure of the table, which is convenient for SQL Provide an intuitive basis for tuning;

4. Automatic replacement: Quickly enter SQL statements, such as input s, press space, and automatically replace with SELECT; for another example, enter sf, press space, and automatically replace with SELECT * FROM, which is very convenient and saves a lot of time to write Duplicate SQL statements.

Setting method: menu Tools–>Preferences–>Editor–>AutoReplace (automatic replacement)–>Edit

1), create a text file shortcuts.txt, and write the following content:
s=SELECT
Copy the code and save it to the ~/PlugIns directory under the installation path of PL/SQL Developer
2) Tools–>Preferences–>User Interface–>Editor–>AutoReplace, select the Enable check box, then browse the file to select the shortcuts.txt created earlier, and click Apply.
3) Restart PL/SQL Developer, enter s+space in the sql window, and sc+space to test.
Note: shortcuts.txt cannot be deleted, otherwise the shortcut keys cannot be used

Some rules are defined below for reference
i=INSERT
u=UPDATE
s=SELECT
f=FROM
w=WHERE
o=ORDER BY
d=DELETE
df=DELETE FROM
sf=SELECT * FROM
sc=SELECT COUNT(*) FROM
sfu=SELECT * FROM FOR UPDATE
cor=CREATE OR REPLACE
p=PROCEDURE
fn=FUNCTION
t=TIGGER
v=VIEW
sso=SET serveroutput ON;

Set shortcut keys (setting method: menu Tools->Preferences->User Interface->Key Configuration)
New sql window: ctrl+shift+s
New command window: ctrl+shift+c
New test window: ctrl+shift+t
PL/SQL Developer beautifier: ctrl+shift+f
Redo: ctrl+shift+z
Undo: ctrl+z
Clear: ctrl+d (use with caution, no recovery, I am disabled O(∩_∩)O~)
Select all: ctrl+a
Indent: tab
Unindent: shift+tab
Uppercase: ctrl+shift+x
Lowercase: ctrl+shift+y
Comment: ctrl+h
Uncomment: ctrl+m
Find: ctrl+f
Show table structure: ctrl+mouse over the table name
Template list: shift+alt+r
Window list: ctrl+w

5. Execute a single SQL statement: press the F8 key

6. TNS Names: menu Help->Support Info (support information)->TNS Names, you can view Oracle’s tnsnames.ora;

7. Debug the stored procedure
When using PL/SQL Developer to operate Oracle, sometimes some stored procedures are called, or stored procedures are debugged;
Method to call stored procedure:
1) First, select Procedures in the Browser on the left of PL/SQL Developer to find the stored procedure that needs to be called;
2) Then, select the debugged stored procedure, right-click, and select Test. In the pop-up Test script window, for the parameter defined as in the type, you need to enter a value for the value of the parameter; finally, click the number button above: Start debugger or press F9;
3), the last click: RUN or Ctrl+R.

Debug shortcuts
Toggle breakpoint: ctrl+b
Start: f9
Run: ctrl+r
Step into: ctrl+n
Step over: ctrl+o
Step out: ctrl+t
Run to exception: ctrl+y

8. Template shortcut keys

9. My Objects is automatically selected by default after login

By default, after PLSQL Developer logs in, all objects will be selected in Brower. If the user you log in is dba, to expand the tables directory, it normally takes a few seconds to wait, but after selecting My Objects, the response rate is calculated in milliseconds.

Setting method:
Tools menu -> Brower Filters, the order window of Brower Folders will be opened, and “My Objects” can be set as the default.
In the Tools menu -> Brower Folders, move the directories you often click (for example Tables Views Seq Functions Procedures) a little higher, and add color distinctions so that your average time to find tables will be greatly shortened, try it out.

priority, from left to right
Tables–>Tablespaces–>Procedures–>Users–>Roles

Original Source:

--

--

Alibaba Cloud
Alibaba Cloud

Written by Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com