Edit and execute SQL statements

This topic introduces how to edit and execute SQL statements in the SQL window.

Background information

The SQL window provides a user interface for data processing. In the SQL window, you can sort, filter, add, edit, and delete data, and execute database objects.

Edit data

Assume that you want to create a table named employee in the odc_test database for the odc_4.2.0 project in the SQL window.

1. In the odc_4.2.0 project, click Log On to Database to go to the SQL development window.

2. In the SQL window, click Select a database to select the project database.

3. In the Switch Database dialog box, select the target database to which the project belongs.

4. In the SQL window, edit the following SQL statements to create a table named employee.

   CREATE TABLE odc_test.employee (
   emp_no int(120) COMMENT 'Employee ID' NOT NULL,
   birthday date COMMENT 'Birthday' NULL,
   name varchar(120) COMMENT 'Employee name' NULL,
   CONSTRAINT cons_employee_empno PRIMARY KEY (emp_no)) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;
   

   The syntax of this statement is as follows:

   CREATE TABLE table_name (column_name column_type, column_name column_type,.......);
   

   The parameters are described in the following table.

   Parameter	Description
   CREATE TABLE	The key word that indicates the creation of a table with a specified name. You must have the privileges to create tables.
   table_name	The name of the table. The table name must conform to the identifier naming rules.
   column_name column_type	The name and data type of the column in the table. To create multiple columns, separate the tuple with commas (,).

   The SQL window also provides the following function icons in the toolbar.

   Icon	Description
   SQL Check	Click this icon to check the SQL syntax.
   Execution Plan	Click this icon to view the execution plan for the SQL statement that you select or on which the pointer is located. This execution plan is estimated by the system before execution and is the result of the EXPLAIN PLAN operation. Therefore, the execution data displayed may differ from the actual execution data. You can use this feature to evaluate SQL statements. Click Execution Plan. On the Plan Details tab, click View Formatting Info to switch to the formatted view.
   Find and Replace	Click this button and enter text in the search field to find the specific content and enter text in the replacement field to replace the content found.
   Undo	Click this icon to undo the previous operation.
   Redo	Click this icon to reverse an Undo operation.
   Format	Click this button to apply formatting, such as indentation, line break, and keyword highlighting, to the selected SQL statements or all the SQL statements in the current SQL window.
   IN Value Conversion	Click this icon to convert the copied rows or columns into the specified format during queries. After you paste the copied data to the SQL editing section, select the copied data and click IN Value Conversion to convert it into the in('A','B') format. Column values are separated with line breaks. Row values are separated with spaces or tabs.
   Case sensitivity variables	The system supports three configurations: All Caps, All Lowercase, and Capitalize First Letter. Click this icon to convert the selected statements in the script to the corresponding capitalization format.
   Indent	You can add indents to or delete indents from the statements that you selected.
   Comments	You can click Add Comments to convert the statements that you select into comments or click Delete Comment to convert comments to SQL statements.
   Settings	Delimiter: Select the character that you want to use as the delimiter from the drop-down list. Five types of delimiters are supported: semi-colons (;), slashes (/), double slashes (//), dollar signs ($), and double dollar signs ($$). Query Result Limit: allows you to set the maximum number of lines that a query can return. Default value: 1000. Obtain Column Information of Result Set: Before you execute an SQL statement, you can specify whether to obtain column information of the result set. If the table contains a large number of columns, we recommend that you disable this option. After you disable this option, column comments and editable columns are not queried, which shortens the SQL execution time. Session Variable: allows you to edit, refresh, and search for variables.

Execute SQL statements

1. After the SQL statements are edited, click the Press F8 icon in the toolbar to execute all SQL statements in the current SQL window.

   Icon	Note
   Press F8	Executes all SQL statements in the current window.
   Run Current Statement (F9)	Executes all the selected SQL statements or the SQL statement in the line where the pointer is located.
   Abort	Aborts the statement that is being executed.

2. On the Results tab, you can view the execution status and result.

View data

1. In the left-side navigation pane of the SQL development window, click and check whether the employee table is created in the odc_4.2.0 database of the odc_test project.

2. Right-click the employee table or edit the SELECT statement in the SQL window to view the table data.

SQL scripts

ODC allows you to save edited scripts in the SQL window and anonymous block window and manage saved scripts on the Scripts tab.

Save scripts

In the SQL window, save the script that creates the employee table.

1. In the SQL window, click the Save Script icon in the toolbar.

2. Specify the name of the script and click OK.

Script management

1. In the left-side navigation pane of the SQL development window, click to go to the Scripts page.

2. On the Scripts page, you can edit, download, delete, and refresh a saved script, copy the path of a saved script, and upload a script from your local disk.

   Note

   • You can click the script name to reference the script content in the SQL window.
   • You can copy the path to reference the script content in the command-line window.

Code snippets

ODC allows you to create code snippets. You can query created code snippets to learn about the usage of related statements during development.

Create code snippets

On the Snippets page, save the code snippet that creates the table employee.

1. In the left-side navigation pane of the SQL development window, click the icon. On the Snippets tab, click + to create a snippet.

2. In the Create Snippet dialog box, specify the snippet information and click OK.

Manage code snippets

On the Snippets page, you can copy, edit, delete, or refresh a saved code snippet.

References

• Compile and debug PL objects

• Command-line window

• Edit and export results

• Analyze SQL statements

• Generate mock data

• Manage database changes

• Manage partitions

• Synchronize shadow tables

• Change lock-free structures