PL/SQL Shortcut

Shortcut:
===============================================================================
Edit/undo Ctrl + Z
Edit/redo shift+ctrl+z
Edit/pl/sql beautifier ctrl+w (Custom)

       shift+home  Select the cursor position to the beginning of the line
       shift+ end  Select the cursor position to the end of the line
       ctrl+shift+home Select the cursor position to the beginning of the first line
        ctrl+shift+end Select cursor position to end Line end
object:view     shift+ctrl+v view (custom)
object:describe    shift+ctrl+d structure (custom)
object:properties   shift+ctrl+p Properties (custom)
object:browse    shift+ctrl+b browse (Custom)
Object:edit data   shift+ctrl+e Edit data (custom)
Object:standard query  shift+ctrl+s standard Query (custom)

edit/find replace   ctrl+f
edit/find next    ctrl+l
Edit/find Previous   Shift+ctrl+l
Edit/replace next   ctrl+p
edit/full screen   ctrl+f11
Edit/Go to Line     ctrl+g
Edit/next tab page   ctrl+h
edit/previous tab page  shift+ctrl+h

Session/execute F8
Session/break Shift+esc
Session/commit F10
Session/rollback SHIFT+F10

Debug/toggle Breakpoint Ctrl+b
Debug/start F9
Debug/run Ctrl+r
Debug/step into CTRL + N
Debug/step over Ctrl+o
Debug/step out Ctrl+t

Tools/explain Plan F5
Tools/code Assistant F6

Editor:start of Document Ctrl+pgup OR ctrl+home
Editor:end of Document Ctrl+pgdn OR ctrl+end
Editor:delete Line Ctrl+y
Editor:navigate back Alt+left
Editor:navigate Forward Alt+right
SQL Window:previous SQL Ctrl+up
SQL Window:next SQL Ctrl+down

1. PL/SQL Developer remember login password
When using PL/SQL developer,
For your convenience, PL/SQL developer remember to log in to Oracle's username and password;
Setup method:
PL/SQL Developer->tools->preferences->oracle->logon history,
In the "Definition" of the right-hand interface, the "Store history" is checked by default,
Check the "Store with Password" button again.
If the above method is not useful, use the following methods:
In the "Fixed Users" section of the above-mentioned interface,
Add username/password @oracle_sid that you need to directly select to log in,
Such as:
cbsdb/[email protected]
When you log back in, from the login interface of Oracle logon username ... button,
Select the user you want to log in to.
2. Execute a single SQL statement (the SQL window automatically selects the statement based on the cursor position)
When you use the SQL window of PL developer, press the F8 key, PL/SQL developer defaults to the window that executes the
There are SQL statements that need to be set to the SQL statement where the mouse is located, that is, executing the current SQL statement;
Setup method: PL/SQL Developer->tools->preferences->sql Window->window types,
Tick "AutoSelect Statement".
Notice that you add a semicolon after each statement.

3. Formatting SQL statements
When you use SQL window with PL-SQL developer, sometimes the SQL statements you enter are too long or too messy,
Hope to use a more general format words, this will look good, or analysis;
How to use:
Select the SQL statement that you want to format, and then click the PL/SQL Beautifier button on the toolbar.
4. View the execution plan
When you use SQL window with PL/developer, sometimes the efficiency of the SQL statements that you enter is performed, the following table structure is analyzed,
How you can improve the efficiency of your queries by looking at the execution plan provided by Oracle;
How to use:
Select the SQL statement you want to parse, and then click the Explain Plan button on the toolbar (that is, the execution plan).
or press F5 directly.

5. Debugging Stored Procedures
When using PL/SQL developer to operate Oracle, some stored procedures are sometimes called, or stored procedures are debugged;
Methods for calling stored procedures:
First, select procedures in the browser to the left of PL/SQL developer,
Locate the stored procedure that you want to call, then select the stored procedure for debugging, right-click,
Select Test, in the pop-up test Script window,
For a parameter that is defined as an in type, you need to enter a value for that parameter's value;
Last click on the number of bars button above: Start debugger or press F9;
Last click: Run or Ctrl+r.
(in order to tune a stored procedure, please refer to the operating manual, this is probably the application of the instructions).
6. Oralce the use of thin clients
For PL/SQL to connect to an Oracle database, Oracle clients are required in addition to PL/SQL Developer.
There is a more convenient way to use Oracle thin client, many places can be downloaded, the file is very small, the cost source is also low.
After the installation is complete, modify the \oracle\ora90\network\admin\tnsnames.ora file in the installation directory:
The format is as follows:
database_name =
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521))
)
(Connect_data =
# (service_name = dealer)
(SID = sid_name)
# (SERVER = dedicated)
)
)
7. Automatic Capitalization of keywords:
People who know a little about programming know that coding style is important.
In terms of reading code, maintaining a consistent coding style, easy to read;
In terms of execution efficiency, consistent coding styles are more likely to be placed in the shared SQL area,
This provides the efficiency of execution.

How to quickly find the vast log of the SQL statement is a more painful thing.
SQL statement capitalization does not solve the problem completely,
but finding a line of all uppercase characters in the middle of a heap of code is relatively easy, and your eyes will thank you. The
setting is also simple:
  tools->preferences->editor, select uppercase keyword case.
I usually capitalize keywords, other things like table names, field names, etc. are lowercase.
Everyone should develop their own coding habits and keep them going.

8. Right-click menu
Each text editing window in PL/SQL Developer (hereinafter referred to as PLD),
such as SQL Window,command window and Porgram window,
Right-clicking on an object name will bring up a menu containing the Action Object command, which we call the context menu.
Object types can be tables, views, synonyms, stored procedures, functions, and so on.
The menu that pops up differs depending on the type of object.
Tables and views have features such as view, edit, Rename, Drop, Query data, and edit data.
View and edit are the structure information for viewing and modifying tables, such as fields, primary keys, indexes, constraints, and so on.
Query data is equivalent to opening a new window and executing the SELECT * from table.
Edit data is equivalent to opening a new window and executing the SELECT * from table for update.
Stored procedures and functions have the test function and can be checked to enter the debug state.
Sometimes due to PLD recognition error, right-click on the object does not come out the correct menu,
You can precede the DDL or DML statement where the object resides, and the semicolon is used so that PLD can correctly determine the type of the object

9. Select for Update
Sometimes we need to import some data into the database, if you use the UE to spell INSERT statement, it will be more troublesome, and not strong operability.
PLD SQL window can query, add, modify and delete the contents of the table.
Query from needless to say, and add, delete and modify, just add a for update after the SELECT statement,
Row-level locking of the table, then click on the window's lock icon, you can enter the editing state.
Here's how to extract text from Excel into a database.
There are three columns in our Excel file that create temporary tables in the database:
CREATE TABLE T1 (
Cino VARCHAR2 (100),
Contno varchar2 (100),
Loanno VARCHAR2 (100)
)
Then enter the select T1 for update in the SQL window and click on the lock mouse to enter the edit state,
Click on the first line of the input window with the mouse, then PLD will deadlock for a few seconds,
You can then see the cursor flashing in the input box on the first line,
Use the mouse to put Cino, Contno, Loanno selected, into Excel, the need to insert the contents of the database selected,
Then switch to PLD, press CTRL + V, click Yes, and then click the Commit button,
The data is submitted to the table T1, and the content is seen by executing select * from t1.
PL/SQL Beautifier (PL/SQL beautification)
PLD 6 or later has the ability to format DML code.
Select a portion of the code in the SQL window or program window (if you don't select the entire window's code action),
In the menu, choose Edit-PL/SQL Beautifier to get the formatted code.
For an illegal DML statement or DDL statement, PLD will prompt in the status bar below:
PL/SQL Beautifier could not parse text.
In the default state, PLD will arrange each field of the DML statement in a separate line, so it is not easy to see.
Select Editàpl/sql beautifier options in the menu, enter the Preferences window,
Choose Edit, go to the Profile editing screen, select DML in the tab bar,
In the SELECT, insert, and update groups box in the middle of the window, select Fit and click Save.
Save the configuration file to the installation directory of PLD and click Close.
In the rules file, enter the location of the configuration file, click OK to complete the profile switch.
Then you can format the code so that each field is as close as possible on one line.
One. TNS Names
Menu Help->support info->tns Names, you can view the Tnsnames.ora of Oracle.

12. Copy to Excel
Executes the SELECT statement in SQL window, after the result, right-click on the following data area,
Select Copy to Excel, you can copy the data area record to Excel.
But there are two points to note:
(1) field cannot start with = or Excel will be mistaken for a function;
(2) The number should not exceed 17 bits, otherwise the number of digits will be set to 0,
  But you can make Excel think that field is text by adding ' to the number ',
  also for fields of type Numbe in the database, preferably with to_char output, Otherwise, it may not appear normal;

13. Keep the last open SQL script
When you re-enter PL/SQL developer, the Window list can open the document that was last exited:
(1) The menu Tools->window list option is ticked;
(2) to the right of Tools->perferences->user interface->options,
Tick "Autosave desktop".
(3) Exit PL/SQL Developer re-enter.

14. Quickly find tables or other objects with known table names:
In the Tools menu, tick the object Browser to open the objects browser,
Double-click the folder where an object in the Object Browser is located,
For example, the table is in the Tables folder,
Then enter the table name as quickly as possible to find the object that starts with a few letters you enter.

15. Quickly close the document window that opens in Windows list:
Hold down the SHIFT key and left-click the document window you want to close.

private replication in PL/SQL Developer (Special copy)
If you are using PL/SQL Developer to write the
Then you have to use the code in other tools, such as a programming language like 3GL,
Then you may need to convert the code to a slightly different format.
Let's assume that you have written and tested such an SQL statement in PL/SQL Developer:
Select Deptno, sum (SAL) mgr_sal
From EMP
where job = ' MANAGER '
GROUP BY Deptno
ORDER BY mgr_sal Desc

  SQL: = ' select Deptno, sum (SAL) mgr_sal from emp ' + #13 # # +
&nb sp;   ' WHERE job = ' MANAGER ' + #13 # # +
    ' GROUP by Deptno ' + #13 # # +
  &nb Sp ' ORDER by mgr_sal Desc ';

Special Copy is found in the popup menu. This feature has a submenu that shows all the private copy formats that are defined. After the
format is selected, the converted code is stored on the Clipboard,
so you can paste it into the editor of the appropriate tool. The
Private copy format is defined in the specialcopy subdirectory of the PL/SQL Developer installation directory.
You can change the pre-determined copy format or add a new copy format.
Simply add a text file with a. copy extension,
It contains a variable
(<line_1>) for the first row of the PL/SQL code, and a variable for the last line of the PL/SQL code (<line_n >)
and a variable (<line_*>) for all other rows. Here is an example of a Borland Delphi:
 ;P l/sql Developer specialcopy definition for Borland Delphi
  <LINE_1&G T For first line
 ;<line_*> for all and lines
 ;<line_n> for last line
 ;
  SQL: = ' <line_1> ' + #13 # # +
  ' <line_*> ' + #13 # # +
  ' <line_n> ';

last line does not need to have a cr/lf pair, but it needs to be terminated with a semicolon. All other rows need only cr/lf to be followed.
If <line_1> and <line_n> are the same as <line_*>, you can ignore them.
In some languages, you need to use a code-changing sequence for specific characters.
For example, in C + +, you would use \ t for the tab character (ASCII code 9).
to define these swap sequences, use #define keywords:
  #define CHAR (9) = \ t
  #define \ = \
  String ("<line_1>\n ") +
  string (" <line_*>\n ") +
  string (" <line_n> ");
You can also use "#define compress" to indicate that you want to remove all the extra empty characters (spaces, tabs, and line breaks) from the results.
Note that the name of the. Copy file will be included in the menu, so you should use a descriptive file name.

17. Easy way to copy Row Records in PL/SQL developer
(1) Click the black triangle to the left of the row record you want to copy, and the row is selected and right-copied.
(2) Paste into Notepad, then copy the content you just pasted. (This step is somehow not missing)
(3) Click the small black triangle on the left side of the new record and paste right.

18. Location of shortcut key definition:
Tools->preferences->user Interface->key Configuration
Select the item you want to define, and then press a shortcut key combination to
If the shortcut key is already defined, there will be a prompt, cancel at this time, and select the shortcut key combination;
Typically, when you open Plsql developer, the most common thing to do is open the SQL window and Command window,
The two operations are defined by the shortcut keys, alt+s and ALT + C, so take the mouse point three things only need to press the key.
Setup method:
Menu tools, Preferences, Key Configuration

Attention:
If you set a shortcut key that doesn't work,
Back to Key Configuration interface tools, Preferences,
Click the "..." button to the right of "Default Administrator" at the top,
In the "Preference Set" screen that pops up, the "Personal Preferences"
and the following "Definition", "Description" to set up.

19. Display the full path of the file in the window title bar
Tools->preferences->user interface->options
Tick "Show Complete file path in Windows titles"

20. The order of the custom object in Object brower and the default automatically check my objects   after login,
by default, PL/SQL developer log in, Brower selects all Objects,
If you are logged in as a DBA, you will need to wait a few seconds for the tables directory to expand,
and the response rate after selecting my objects is measured in milliseconds.
Set method:
  Tools menu, Object Brower Filters, opens define Browser Filters interface,
  check My Objects, and tick " Default is set to defaults.
  Tools menu, Object Brower Folders, will open define Browser Folders interface,
  Here you can put a few of the frequently used directories (such as: Tables views Seq Functions procedures)
  Move up a bit, and color-coded, so your average time to find the table will be much shorter, try it.

21. Double-click to display the table data
The default response when the mouse double-clicks a table or view is really disappointing, because I'm most concerned with the table structure and the data,
But after double-clicking these two things did not happen, perhaps the default response is the master needs,
But for me to look at the data and table structure is the most important, the other I do not care.
But the good thing is that it can be set, you can give the mouse double-click and drag and drop the required events,
For example: Double-click Edit Data, drag and drop display table structure, yeah!
Setup method:
Menu tools, Preferences, Object Browser,
On the right, double-click and drag-and-drop operations are bound for different object types.

22. Remove the italic style from the comment:
User interface->editor, menu tools, Preferences
In the right side of the interface "Syntax highlighting", remove the "Comment" to the right of the "Italic" check.

PL/SQL shortcut