SQL editing and execution

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

Background information

The SQL window of OceanBase Developer Center (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 example in this topic describes how to create a table named employee in the odc_test database in the SQL window.

Edit SQL statements

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

   

2. In the SQL window, edit an SQL statement 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 '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 fields in the syntax.

   Field	Description
   CREATE TABLE	The keyword that indicates the creation of a table with a specified name. You must have the permission 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 (,).

   • In the SQL window shown in the preceding figure, click to switch to another database.

Execute SQL statements

1. After you complete writing SQL statements, click Press F8 in the toolbar to run all SQL statements in the current SQL window. The following table describes the icons related to SQL statement execution.

   Icon	Description
   Press F8	Click this icon to run all SQL statements in the current SQL window. You can also press F8 to run all SQL statements in the current SQL window.
   Run Current Statement (F9)	Click this icon to run all selected SQL statements or the statement in the line where the pointer is hovering. You can also press F9, or press Ctrl+Enter in Windows or Command+Enter in macOS to run the current statement.
   Abort	Click this icon to abort the running statements.

   

2. On the result tabs, view the execution status and result.

   

View data

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

   

   You can use the shortcut keys Ctrl+J in Windows or Command+J in macOS to search for objects such as databases, tables, views, functions, and triggers in the current project or data source in ODC.

   

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

   

3. Click to manually synchronize metadata such as table columns in the current project or data source.

4. Right-click the name of a database and select Sync Metadata to manually synchronize metadata such as table columns in the current database.

   

SQL scripts

In ODC, you can save edited scripts in the SQL window or anonymous block window and 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 icon in 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 tab, you can edit, download, delete, and refresh a saved script, copy the path of a script, and upload a script from your local disk.

   

   Note

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

Snippets

ODC allows you to create snippets. You can query created snippets to learn the usage details about relevant statements during development.

Create a snippet

On the Code Snippet tab, you can save the snippet for creating the employee table.

1. In the left-side navigation pane of the SQL window, click . On the Code Snippet tab, click the plus sign (+) to create a snippet.

   

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

   

   Note

   In ODC V4.2.3 and later, the maximum snippet length allowed is changed to 64 KB.

Manage snippets

On the Code Snippet tab, you can copy, edit, delete, and refresh a saved snippet.

References

• Perform PL compilation and debugging

• Use the command-line window

• Edit and export the result set of an SQL statement

• Analyze SQL statements

• Generate test data

• Database change management

• Manage partitioning plans

• Synchronize shadow tables

• Lock-free schema changes