PL/SQL developer tips

The following tips only apply to PL/SQL developer 6 and later versions. Version 5 only has some features.
1. Right-click the menu
Right-click an object name in each text editing window in PL/SQL developer (PLD), such as SQL window, command window, And porgram window, A menu containing the operation object command is displayed, which is called the Right-click menu. For example:
Objects can be tables, views, synonyms, stored procedures, and functions. The pop-up menu varies depending on the object type. Tables and views include view, edit, rename, drop, query data, and edit data. View and edit are used to view and modify the structure information of a table, such as fields, primary keys, indexes, and constraints. 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 select * from table for update. Stored Procedures and functions have the test function. After selection, you can enter the debugging status.
Sometimes, due to PLD recognition errors, right-clicking an object does not produce a correct menu. You can add a semicolon before the DDL or DML statement of the object, in this way, PLD can correctly determine the object type.
2, select for update
Sometimes we need to import some data into the database. If we use the UE spelling insert statement, it will be troublesome and the operability is not strong. The SQL window of PLD allows you to query, add, modify, and delete table content. You can add, delete, and modify a query. You only need to add for update after the SELECT statement, lock the table row, and click the lock icon in the window to enter the editing status.
The following describes how to extract text from Excel and insert it into the database.
Our Excel file contains three columns:
Create a temporary table in the database:
Create Table T1 (cino varchar2 (100), contno varchar2 (100), loanno varchar2 (100 ))
Then, in the SQL window, enter select T1 for update and click the lock mouse to enter the editing status:
Click the input window of the first line. Then, the PLD will be deadlocked for several seconds. Then, you can see the cursor flashing in the input box of the first line. Click cino, contno, loanno and select:
In Excel, select the content to be inserted into the database, switch to PLD, and press Ctrl + V:
Click √, and then click the commit button. Then, the data is submitted to table T1. Run select * from T1 to see the following content:
3, PL/SQL beautifier
PLD 6 and later versions have the DML code Formatting Function. Select some code in SQL window or program window (if not selected, operate the code in the entire window), and select Edit à PL/SQL beautifier in the menu to get the formatted code. For invalid DML statements or DDL statements, PLD will prompt PL/SQL beautifier cocould not parse text in the following status bar.
By default, PLD will sort every field in the DML statement in a separate row, which is inconvenient to view. Select Edit à PL/SQL beautifier options from the menu to go to the preferences window. select Edit to go to the configuration file editing page:
Select DML in the label column, select fit in the select, insert, and update group boxes in the middle of the window, click save, save the configuration file to the PLD installation directory, and click Close. In rules file, enter the location of the configuration file and click OK to switch the configuration file. In this case, you can format the code so that each field is on a row as much as possible.
4. TNS names
Menu help à support info à TNS names, you can view the tnsnames. ora of oracle.
5. Copy to excel
Run the SELECT statement in the SQL window. After the result is displayed, right-click the data area below and select copy to excel to copy the records in the data area to the Excel file. But there are two points to note: first, the field cannot start with =; otherwise, Excel will mistakenly think of it as a function; second, the number should not exceed 17 digits; otherwise, the subsequent digits will be set to 0, however, you can add 'to the front of the number to make Excel think that the field is text. At the same time, it is best to use to_char to output numbe fields in the database. Otherwise, it may be abnormal.

TIPS:

1. PL/SQL developer remember the Login Password

When PL/SQL developer is used, PL/SQL developer needs to remember the username and
Password;

Setup method: PL/SQL developer 7.1.2-> Tools-> preferences-> Oracle-> logon history, "Sto
Re history is selected by default. Just click store with password. Log On again and enter the password.
Stopped.

2. Execute a single SQL statement

When PL/SQL developer's SQL window is used, press F8. By default, PL/SQL developer executes the window
If you have an SQL statement, you must set it to the SQL statement where the mouse is located, that is, execute the current SQL statement;

Setup method: PL/SQL developer 7.1.2 --> Tools-> preferences --> Window Types, check "autosel
ECT statement.

3. Format SQL statements

When PL/SQL developer's SQL window is used, sometimes the entered SQL statement is too long or too messy, and it is expected to be comparable.
It looks better and better to analyze it;

Usage: select the SQL statement to be formatted, and click PL/SQL beautifier In the toolbar.

4. view the execution plan

When PL/SQL developer's SQL window is used, sometimes the efficiency of the input SQL statement execution is analyzed to analyze the table structure,
To improve query efficiency, you can view the execution plan provided by Oracle;

Usage: select the SQL statement to be analyzed, click the explain Plan button on the toolbar (that is, the execution plan), or
Press F5 directly.

5. debug the Stored Procedure

When PL/SQL developer is used to operate Oracle, some stored procedures are sometimes called or debugged;

Method for calling the stored procedure: first, select procedures in the browser on the left of PL/SQL developer to find
Then, select the Stored Procedure for debugging, right-click and select test. In the displayed test SCR release PT window
For a parameter defined as in, you need to enter a value for the parameter value. Finally, click the above number button: Start
Debugger or press F9. Click RUN or Ctrl + R.

(For details about how to adjust a stored procedure, refer to the operation manual, which describes the application ).

6. Use oralce to streamline the client

To connect PL/SQL to the Oracle database, in addition to PL/SQL developer, you also need the Oracle client.
The solution is to use the simplified Oracle client, which can be downloaded in many places. The files are small and consume less resources. Repair after installation
Change 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 uppercase of keywords:

Anyone familiar with programming knows that the encoding style is very important. In terms of code reading, it is easier to read with consistent encoding styles. In terms of execution efficiency, consistent encoding styles are more likely to be placed in the shared SQL zone, in this way, the execution efficiency is provided.
The setting is also simple: Tools-> preferences-> Editor, select uppercase for keyword case.
Generally, I use uppercase keywords. Others, such as table names and field names, are lowercase. Everyone should develop a coding habit and keep it up.