Edit and execute SQL statements

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

Background information

The SQL window of ODC provides a GUI for data processing. You can sort, filter, add, edit, and delete data, and execute database objects in the SQL window.

The following example describes how to create a table named employee in the odc_test database in the SQL window.

Prerequisites

You have queried, exported, or modified the database.

Edit SQL statements

1. Click the name of the odc_test project, test_data, to go to the SQL window.

   

2. In the SQL window, enter SQL statements to create a table named employee.

   

   CREATE TABLE test_data.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 is as follows:

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

   The following table describes the parameters.

   Parameter	Description
   CREATE TABLE	The statement that creates a table with a specified name. You must have the privilege 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 each column in the table. Separate the tuples of multiple columns with commas (,).

   • You can click the name of a database in the SQL window to switch to the database.

     

   • For more information about the toolbar in the SQL window, see Common features.

SQL Intelligent Editor

In the SQL editing area, after you select the content to edit, you can perform quick intelligent operations by using the editing buttons on the right or the shortcut menu. These operations include SQL generation, SQL correction, SQL rewriting, and SQL optimization. For more information about these operations, see Intelligent SQL Development.

SQL Auto-completion

The ODC SQL window supports intelligent auto-completion. When you enter an SQL statement, the system automatically suggests possible completion options, including table names, column names, function names, and keywords.

Auto-completion supports the following object types:

Execute SQL statements

1. After you write SQL statements, click Run F8 in the toolbar to run all SQL statements in the current SQL window.

   Feature	Description
   Run F8	Click this option to run all SQL statements in the current SQL window. You can also press F8 to run all SQL statements.
   Run Current Statement (F9)	Click this option to run all selected SQL statements or the statement in the line where the pointer is located. You can also press Ctrl+Enter (Windows) or Command+Enter (MacOS) to run the current statement.
   Abort	Click this option to abort the statement that is being executed.

   

2. On the result tabs, view the status and results of statement execution.

   

View data

1. In the left-side navigation pane of the SQL window, verify that the employee table exists in the icon.

   odc_test > test_data > table > employee
   

   

   • Enter a keyword in the search box, or press the shortcut key Ctrl+J (Windows) / Command+J (MacOS) to open the search drop-down list and enter a keyword. You can search for all objects such as databases, tables, views, and functions in the current project or data source.

     

2. You can group databases by different conditions.

   

3. Click to manually synchronize metadata of tables and columns in the current project or data source.

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

   

5. Right-click the name of the database and select Sync Metadata to manually synchronize metadata of tables and columns in the database.

   

SQL scripts

ODC allows you to save and edit scripts in an SQL window or an anonymous block window. You can also manage the saved scripts on the script tabs.

Save a script

Save the script that creates the employee table in an SQL window.

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

   

2. Specify the script name and click OK.

   

Manage scripts

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

   

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

   You can double-click a script name to directly reference the script content in the SQL window. You can also copy the path to reference the script content in the command line window.

   

   When you download a script, you can select multiple scripts for batch download.

   Note

   You can download up to 200 scripts in a batch.

   1. Click the name of the script that you want to download.

   2. Use the ctrl (Windows) or command (MacOS) key together with left mouse button to select more scripts, or use the shift key together with left mouse button to select scripts in a range.

      

   3. Right-click to select Download in bulk.

Snippets

ODC allows you to create snippets. You can query and view the usage of related statements in the snippets during development.

Create a snippet

Save the snippet that creates the employee table on the snippets page.

1. In the left-side navigation pane of the SQL window, click and click + to create a snippet on the code snippets tab.

   

2. On the Create Code Segment page, specify the snippet information and click OK.

   

   Note

   In ODC V4.2.3 and later, the maximum length of a snippet is 64 KB.

Manage snippets

On the snippets page, you can copy, edit, delete, and refresh saved snippets.

References

• Compile and debug PL programs

• Command-line window

• Edit and export the result set

• Execution analysis

• Generate test data

• Database change management

• Manage partitioning plans

• Synchronize shadow tables

• Online schema changes