PL/SQL Usage tips

PL/SQL usage tipsEdit/undo Ctrl+zedit/redo shift+ctrl+zedit/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 the cursor position to the end of the line Object:view shift+ctrl+v view (custom)
Object:describe shift+ctrl+d structure (custom)
Object:properties Shift+ctrl+p Property (Custom)
Object:browse shift+ctrl+b browse (Custom)
Object:edit Data shift+ctrl+e Editing (custom)
Object:standard Query Shift+ctrl+s standard queries (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:de Lete line   ctrl+y
editor:navigate back  alt+left
editor:navigate Forward alt+right
SQL Windo w:previous SQL Ctrl+up
SQL Window:next sql  ctrl+down

1. PL/SQL Developer remember the login password
when using PL/SQL developer,
for the convenience of working with PL/SQL developer remember to log in to Oracle user name and password;
Setting method:
& nbsp PL/SQL Developer->tools->preferences->oracle->logon history,
  "Definition" in the right-hand interface, "Store History "is checked by default,
  then tick" Store with password ".
If the above methods are not working, use the following method:
in the above interface, in the" Fixed Users "
Add username/password @oracle_sid,
  such as:
Cbsdb/[email protected]
  re-login, from the login interface of Oracle logon after username ... button,
  Select the user you want to log in to.
2. Execute a single SQL statement (automatically select a statement from the cursor position in SQL window)
When using SQL window with PL/developer, press F8, PL/SQL developer defaults to 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;
Set method: PL/SQL Developer->tools->preferences->sql window->window types,
tick "AutoSelect Statement".
Note that a semicolon is appended to 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.

In addition, the core of the information system is the database, the first thing to check when the system problem is the SQL statement,
How to quickly find that SQL statement in the vast log is a painful thing.
The full capitalization of the SQL statement does not solve this problem completely.
But it's easier to find a line of all uppercase characters in the middle of a bunch of code, and your eyes will thank you.
The settings are also simple:
Tools->preferences->editor, select the keyword case uppercase.
I usually have the keyword capitalized, other such as table names, field names, etc. are lowercase.
Everyone should develop a coding habit of their own and keep it 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.

Copy to Excel
Execute the SELECT statement in SQL window, and after the result, right-click on the data area below,
By selecting Copy to Excel, you can copy the data area's records to Excel.
But there are two points to note:
(1) field can not start with =, or Excel will be mistaken for a function;
(2) do not exceed 17 digits, otherwise the number of digits will be set to 0.
But you can make Excel think that field is text by adding ' in front of the number '.
At the same time for the Numbe type of field in the database, it is best to use 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

For example, if you want to use this statement in Borland Delphi, you might need a format like this:
SQL: = ' select Deptno, sum (SAL) mgr_sal from emp ' + #13 # # +
' WHERE job = ' MANAGER ' + #13 # # +
' GROUP by Deptno ' + #13 # # +
' ORDER by mgr_sal Desc ';

For this purpose, in PL/SQL developer, right-click on the already-written statement, and
Locate special Copy 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& Gt For first line
 ;<line_*> to all and lines
 ;<line_n> for last line
 ;
&N Bsp SQL: = ' <line_1> ' + #13 # # +
  ' <line_*> ' + #13 # # +
  ' <line_n> ';

The first line requires a preface to the variable that is assigned to SQL, followed by a cr/lf pair.
The last line does not need to have cr/lf, 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 code-changing 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"

The order of the custom object in Object brower and the default automatically check my Objects after login
By default, after PL/SQL developer is logged in, Brower selects all objects.
If the user you are logged in to is a DBA, to expand the tables directory, wait a few seconds for the normal situation,
The response rate after selecting my objects is measured in milliseconds.
Setup method:
The Tools menu, Object Brower Filters, opens the Define Browser Filters interface,
Select "My Objects" and tick "default" to set it to the defaults.
The Tools menu, Object Brower Folders, opens the Define Browser Folders interface,
Here can be used in a number of directories (such as: Tables views Seq Functions Procedures)
Move up a little bit and color-coded so that your average time to find the table will be much shorter and 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:
Menu tools, Preferences, User interface->editor in the right side of the interface "Syntax highlighting", remove the "Comment" to the right of the "Italic" check.

PL/SQL Usage tips