Edit and execute SQL statements

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

Background information

The ODC 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.

This topic describes how to create a table named employee in the odc_test database in the SQL window.

Prerequisites

You have obtained the query, export, and modify permissions for the database.

Edit SQL statements

1. In the odc_test project, click the test_data database to go to the SQL window.

2. In the SQL window, edit the SQL statement to create the employee table.

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

   Syntax:

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

   Parameter description:

   Parameter	Description
   CREATE TABLE	Creates a table with the specified name. You must have the CREATE permission for the table.
   table_name	The name of the table to be created. The table name must comply with the identifier naming rules.
   column_name column_type	The name and data type of each column (field) in the data table. If you create multiple columns, separate them with commas.

   • Click the database name in the SQL window to switch databases.

   • For more information about the toolbar, see SQL console function keys.

Execute SQL statements

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

   Function	Description
   Run F8	Runs all SQL statements in the current SQL window. You can also press the F8 key to run all SQL statements.
   Run Current Statement F9	Runs all selected SQL statements or the SQL statement on the current cursor line. You can also press the F9 key or Ctrl+Enter (Windows) / Command+Enter (MacOS) to run the current statement.
   Abort	Stops the running statement.

2. On the result tab, view the execution status and results.

View data

1. In the left-side navigation pane of the SQL window, check whether the employee table is created.

   odc_test > test_data > Tables > employee
   

   • You can search for databases, tables, views, functions, and triggers in the current project or data source by entering keywords in the search box or by pressing the Ctrl+J (Windows) / Command+J (MacOS) shortcut key to open the search dialog box and entering keywords.

2. You can group databases based on different conditions.

3. Click to manually synchronize metadata such as columns of tables 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 database name and select Synchronize Metadata to manually synchronize metadata such as columns of tables in the current database.

SQL scripts

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

Save a script

In the SQL window, save the script for creating the employee table.

1. In the SQL window, click the Save Script button 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, and delete saved scripts, copy the path of a saved 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 of a script to reference the script content in the command line window.

   You can select multiple scripts and download them in batches.

   Note

   You can batch download a maximum of 200 scripts at a time. If you need to download more scripts, download them in batches.

   1. Click the name of a script.

   2. You can also select more scripts by holding down the Ctrl key (Windows) or the Command key (MacOS) and clicking the names of the scripts, or by holding down the Shift key and dragging the mouse to select a range of scripts.

   3. Right-click the selected scripts and choose Batch Download.

Code snippets

ODC allows you to create code snippets. During development, you can query the created code snippets to learn how to use the statements.

Create a code snippet

On the code snippet page, save a code snippet for creating the employee table.

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

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

   Note

   Starting from ODC V4.2.3, the maximum length of a code snippet is 64 KB.

Manage code snippets

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

References

• PL compilation and debugging

• Result editing and exporting

• Execution analysis

• Generate test data

• Database change management

• Partition management

• Shadow table synchronization