Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the skills used by PL/SQL DEVELOPER, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

1. My Objects is automatically selected by default after login. By default, All objects is selected in Brower after PLSQL Developer login. If the user you are logged in is dba, it normally takes Wait seconds to expand the tables directory, while the response rate after selecting My Objects is calculated in milliseconds. Tools menu-> Object Brower Filters, will open the Brower Folders order window, set "My Objects" to the default. Tools menu-> Object Brower Folders, move several directories you often click on (such as Tables Views Seq Functions Procedures) a little higher, and add color distinction, so that your average time to find the table will be greatly shortened, give it a try. / * setting method: Tools menu-Brower Folders, which will open the order window of Brower Folders and move "My Objects" to the top. Similarly, you can move several directories you often click on (such as: tables Views Seq Functions Procedures) a little higher, and add color distinction, so that your average time to look up the table will be greatly shortened, give it a try. , /

2. Remembering passwords is a controversial function, because remembering passwords can cause data security problems. But if it is the development of the library, the password can even be the same as the user name, each time enter the password is really meaningless, you can consider letting PLSQL Developer remember the password. Location: Tools menu-Preferences--Oracle--Logon HIstory--Store with password

3, double-click to display table data PLSQL Developer when the mouse double-click the table or view of the default response really let me down, because I am most concerned about the table structure and data, but after double-click these two things did not happen, maybe the default response is what the experts need, but for me to view the data and table structure is the most important, the other I do not care about. But the good thing is that this can be set, you can give the mouse double-click and drag-and-drop binding required events, such as: double-click edit data, drag-and-drop display table structure, Yeah! Location: Preferences--User Interface, on the right, double-click and drag-and-drop operations for different Object type bindings.

The core of the information system is the database. When there is something wrong with the system, the first thing to check is the SQL statement. How to quickly find the SQL statement in the vast log is a relatively painful thing. All uppercase SQL statements don't completely solve this problem, but it's relatively easy to find a line of all uppercase characters in the middle of a pile of code, and your eyes will thank you. The setting location is in Editor. At the same time, I think equal width characters can reduce the error rate of the program, so all my tools use Courier New, if an IDE does not support this font, I will basically choose to give up. Ha, I am a very picky guy.

5. The SQL statement written by a special Copy in SQL Window usually needs to be put in Java or other languages, and it needs to be converted into a string and added with the corresponding hyphen. There is no need to repeat this. Right-click on the written SQL and use the special Copy that is OK!

6. There are many keys reserved in the custom shortcut PLSQL Developer for users to customize, which is a very Hight thing. Unlike the overbearing Word, almost all keys have predefined functions, which makes it a headache to modify. Usually, after opening PLSQL Developer, the most common thing to do is to open SQL Window and Command Window, and define the shortcut keys, ALT+S and ALT+ C, for these two operations, so that you only need to press the button with three mouse clicks.

7, other quick emptying tables, TRUNCATE TABLE, can be found in the right button. Do not understand the two functions: Analyze, VALID 7, SQL Window automatically select the statement setting method according to the cursor position: Preferences-> Window Types-- > SQL Window, you can select AutoSelect statement. Note that each statement is followed by a semicolon.

Add:

8. Two steps are needed to start PLSQL Developer,window list menu automatic call-up. First, save the desktop settings, and then check the Window list option. The specific operations are as follows: a, select Option in the Preference option under Tools of the menu item, and check the previous check box in the Autosave desktop on the right. B. Check the Window list option under Tools of the menu item.

9. Format SQL (format) when you get a long SQL statement, you want to quickly see the logic in it. You can put it in this tool and format it, and the logic of the statement will be clear at a glance.

10. The database automatically detects the connection because every time the database is not operated, it will be disconnected automatically, and then you need to connect manually, because of network reasons, it will always be stuck there. The tool provides the ability to detect connections and can connect automatically. You can check the specific settings in front of Tools-Preferences-Check connection,Check connection.

11. Commonly used keyboard shortcuts are actually mentioned in item 6. Here are my habit settings:

Ctrl+tab toggles windows window (or alt+left/right)

Alt + n create a new sql window

Alt + c closes the current window

Alt + f format sql

Once again, add:

1. Right-click menu

In every text editing window in PL/SQL Developer (hereinafter referred to as PLD), such as SQL Window,Command Window and Porgram Window, right-clicking an object name will pop up a menu containing Operand commands, which we call the right-click menu here.

Object types can be tables, views, synonyms, stored procedures, functions, and so on. Pop-up menus vary depending on the type of object. Tables and views have functions such as View, Edit, Rename, Drop, Query data and Edit data. View and Edit view and modify the structural information of the table, such as fields, primary keys, indexes and constraints, respectively. 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 function of Test, which can be debugged when selected. Sometimes due to PLD recognition error, right-click on the object can not come up with the correct menu, you can put a semicolon in front of the DDL or DML statement where the object is located, so that PLD can correctly determine the type of object

2 、 Select for Update

Sometimes we need to import some data into the database, if we use UE to spell Insert statements, it will be more troublesome, and the operation is not strong. PLD's SQL Window can query, add, modify and delete the contents of the table. Query needless to say, but add, delete and modify, just add for update after the select statement, lock the table at the row level, and then click the lock icon of the window to enter the editing state. Here's how to extract text from Excel and insert it into our Excel file. There are three columns: create a temporary table in the database: create table T1 (cino varchar2, contno varchar2, loanno varchar2), then enter select T1 for update in SQL Window, and click the lock mouse to enter the editing state: click on the input window on the first line with the mouse, and PLD will deadlock for a few seconds Then you can see the cursor flashing in the input box on the first line. Use the mouse to select CINO, CONTNO, LOANNO: enter Excel, select the content that needs to be inserted into the database, then switch to PLD, press Ctrl + V: click √, and then click the Commit button, then the data is submitted to table T1, and execute select * from T1 to see the content:

3. PL/SQL Beautifier (PL/SQL beautifier)

PLD 6 and above have the ability to format DML code. Select part of the code in SQL Window or Program Window (if not, the code for the entire window), select Edit-> PL/SQL Beautifier from the menu to get the formatted code. For illegal DML or DDL statements, PLD will prompt PL/SQL Beautifier could not parse text in the status bar below. By default, PLD arranges each field of the DML statement on a separate line, which is not easy to view. Select Edit à PL/SQL Beautifier Options from the menu, enter the Preferences window, select Edit, and enter the profile editing interface: select DML in the tab bar, select Fit in the Select, Insert and Update group boxes in the middle of the window, then click Save, save the configuration file to the PLD installation directory, and click Close to close. Enter the location of the configuration file in Rules file and click OK to complete the profile switch. At this point, you can format the code so that each field is on one line as much as possible.

4 、 TNS Names

Menu Help à Support Info à TNS Names, you can view Oracle's tnsnames.ora.

5 、 Copy to Excel

Execute the Select statement in SQL Window. After the result comes out, right-click on the data area below and select Copy to Excel to copy the records of the data area to Excel as is. But there are two points to note: first, the field cannot start with =, otherwise Excel will be mistaken for a function; second, the number should not exceed 17 digits, otherwise the subsequent digits will be set to 0, but you can make Excel think that the field is text by adding 'before the number. At the same time, for Numbe fields in the database, it is best to use to_char output, otherwise the display may not be normal

6. PL/SQL Developer remember the login password

When using PL/SQL Developer, I want PL/SQL Developer to remember the user name and password of logging in to Oracle for the convenience of work.

Setting method: PL/SQL Developer 7.1.2-> tools- > Preferences- > Oracle- > Logon History, "Store history" is checked by default, check "Store with password", re-login to enter the password will be remembered. (mine doesn't seem to work.)

7. Execute a single SQL statement

When using PL/SQL Developer's SQL Window, press the F8 key. By default, PL/SQL Developer executes all the SQL statements of the window, which needs to be set to the SQL statement where the mouse is located, that is, to execute the current SQL statement.

Setting method: PL/SQL Developer 7.1.2-> tools- > Preferences-- > Window types, and tick "AutoSelect Statement".

7. Format SQL statements

When using PL/SQL Developer's SQL Window, sometimes the input SQL statement is too long or too messy. I hope we can write it in a more general format, so that it will look better and easy to analyze.

How to use: select the SQL statement that needs to be formatted, and then click the PL/SQL beautifier button on the toolbar.

8. View the execution plan

When using the SQL Window of PL/SQL Developer, sometimes the execution efficiency of the input SQL statement is analyzed. To analyze the structure of the following table and how to improve the efficiency of the query, you can check the execution plan provided by Oracle.

How to use it: select the SQL statement to be analyzed, and then click the Explain plan button on the toolbar (that is, execute the plan), or press F5 directly.

9. Debug stored procedures

When using PL/SQL Developer to operate Oracle, some stored procedures are sometimes called or debugged.

The method of calling the stored procedure: first, select Procedures in the Browser on the left side of the PL/SQL Developer to find the stored procedure that needs to be called; then, select the debugged stored procedure, right-click, and select Test. In the pop-up Test Value window, for the parameter defined as in, you need to input the value to the parameter Value; finally, click the number of buttons above: Start debugger or press F9; finally, click: RUN or Ctrl+R.

To adjust a stored procedure, please refer to the operation manual, which gives a general description of the application.

10. The use of oralce thin client

If you want PL/SQL to connect to the oracle database, you need an Oracle client in addition to PL/SQL Developer. A more convenient way is to use Oracle to simplify the client, which can be downloaded in many places, with small files and low resource consumption. After the installation, 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))

11. Keywords are automatically capitalized

Anyone who knows a little bit about programming knows that coding style is important. In terms of reading code, maintaining a consistent coding style is easier to read; in terms of execution efficiency, maintaining a consistent coding style is more likely to be placed in a shared SQL area, thus providing execution efficiency. The setup is also simple: Tools- > Preferences- > Editor, select Uppercase for Keyword case. I usually capitalize keywords, and others, such as table names, field names, etc., are lowercase.

After reading the above, do you have any further understanding of the techniques used by PL/SQL DEVELOPER? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.