PL/SQL Developer Practical Skills

PL/SQL Developer Practical Skills

1. PL/SQL Developer remember the Login Password

When PL/SQL Developer is used, PL/SQL Developer needs to remember the username and password for logging on to Oracle for ease of work;

Setting Method: PL/SQL Developer 7.1.2-> tools-> Preferences-> Oracle-> Logon History. "Store history" is selected by default. Check "Store with password, remember to enter the password once you log on again.

2. Enable Automatic capital of keywords

Setting Method:> tools-> Preferences --> user interface --> editor --> keyword case is selected as uppercase

3. copy the query result 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.

4. Automatic completion

Setting Method:> tools-> Preferences --> user interface --> editor. The AutoReplace and Edit buttons on the right interface can be written as follows:

S = SELECT * FROM

Save it.

5. Execute a single SQL statement

When you use the SQL Window of PL/SQL Developer, press F8. By default, PL/SQL Developer executes all SQL statements in the Window and must be set to the SQL statement where the mouse is located, execute the current SQL statement;

Setting Method: PL/SQL Developer 7.1.2 --> tools-> Preferences --> Window types. Just check "AutoSelect Statement.

6. Format SQL statements

When PL/SQL Developer's SQL Window is used, sometimes the entered SQL statements are too long or too messy. If you want to use a common format, it looks better and better for analysis;

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

7. 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. You can view the execution plan provided by Oracle to improve the query efficiency;

Usage: select the SQL statement to be analyzed, click the Explain plan button on the toolbar, or press F5.

8. debug the Stored Procedure

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

Method for calling a stored procedure: first, select Procedures in the Browser on the left of PL/SQL Developer to find the stored procedure to be called. Then, select the Stored Procedure for debugging and right-click, select Test. in the displayed Test scr limit pt window, you need to enter a Value for the parameter defined as in. Finally, click the number button above: start debugger or press F9. Click RUN or Ctrl + R.

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)