Jincang database kingbasees SQL language reference manual (3.10. database object reference method)

3.10.  Database object reference method

KingbaseES Database support in SQL Reference... In the context of the statement Schema object . KingbaseES The current version of the database supports the following reference types ：

• quote Schema object

• Reference partitioned tables and indexes

• Reference object type properties and methods

The general syntax of the quotation is as follows ： [ schema. ] object [ .part ]

among ：

object Refers to the name of the object .

schema Is containing objects Schema. This qualifier allows the user to refer to other... After being granted permission Schema The object of . If omitted , The default is user default Schema Objects in the .

schema Only applicable to Schema object , Such as table （Table）、 View （View）、 Indexes （Index）、 Partition （Partition）、 Sequence （Sequence）、 A synonym for （Synonyms）、 trigger （Trigger）、 Custom function 、 stored procedure 、 Packages and triggers, etc .

Not Schema Object cannot be used , For example, users （User）、 role （Role）、 Table space （Tablespace） And contents （Directory）. Except for public synonyms , It can choose to use “ PUBLIC ” To limit , Must be quoted .

part It's part of the object . This identifier allows users to reference Schema Part of the object , For example, columns or partitions of tables . But it does not apply to all types of objects .

When in SQL When an object is referenced in a statement ,KingbaseES The database will consider SQL Statement and find the object in the namespace , Then perform the specified operation on the object . If the named object is not found in the namespace , Returns an error .

3.10.1.  quote Schema object

When in SQL When an object is referenced in a statement ,KingbaseES The database will consider SQL Statement and find the object in the namespace , Then perform the specified operation on the object . If the named object is not found in the namespace , Returns an error .

The following example illustrates KingbaseES How to parse SQL References to objects in statements . Following statements , Add a row of data to departments In the table ：

INSERT INTO departments VALUES (280, 'ENTERTAINMENT_CLERK', 206, 1700);

according to SQL The context of the statement ,departments In the database, it may be the following ：

• One in default Schema In the table

• One is by default Schema View in

• A private synonym for a table or view

• A public synonym

KingbaseES The database will first try in the default Schema Resolve the referenced object in the namespace in , Then consider this Schema Namespaces other than .

In this example ,KingbaseES The parsing process of the database is as follows ：

1. KingbaseES The database first tries in the default Schema To locate objects in the namespace of . If the object is a private synonym , be KingbaseES The database will find the object represented by the synonym .

This object can be in the default Schema in , It can also be used in other Schema Or in another database . This object can also be another synonym , under these circumstances ,KingbaseES The database will find the object represented by the synonym .

1. If the object is found in the namespace , be KingbaseES The database attempts to execute the SQL sentence .

In this example ,KingbaseES The database tries to add a row of data to departments in . If this object is not of this type SQL The type required by the statement , be KingbaseES The database returned an error .

departments Must be a table 、 Views can also be private synonyms that can be resolved to tables or views . If departments It's a sequence , be KingbaseES The database returned an error .

1. If the object has not been searched in any namespace so far , be KingbaseES The database will search for namespaces containing public synonyms .

If the object is in a namespace that contains public synonyms , be KingbaseES The database tries to execute SQL sentence . If this object is not of this type SQL The type required by the statement , be KingbaseES The database returned an error .

In this example , If departments Is a public synonym that represents a sequence , that KingbaseES The database will return an error .

If the public synonym contains any dependent tables or user-defined types , Cannot be in the same as the dependent object Schema Create an object with the same name as the synonym .

conversely , If the synonym does not have any dependent tables or user-defined types , Can be in the same as the dependent object Schema Create an object with the same name in .

KingbaseES The database will invalidate all dependent objects , And re verify them the next time you visit them .

3.10.2.  Quote others Schema Objects in the

If the object to be referenced belongs to another Schema, You need to add the corresponding Schema name .

The grammar is as follows ： schema.object

The following example shows the example named hr Of Schema Delete table employees：

DROP TABLE hr.employees;

3.10.3.  References to objects in remote databases

To reference objects in a database other than the local database , Please add a database link to the database after the object name . Database link （database link） Is a schema object , It can make KingbaseES Connect to a remote database to access objects there .

Example ：

select * from [email protected]

Specifically database link establish , Please see the "CREATE DATABASE LINK".

3.10.4.  Reference partitioned tables and indexes

Tables and indexes can be partitioned . In zoning , These schema objects are called partitions Part of , All these parts have the same logical attributes .

for example , All partitions in the table share the same column and constraint definitions , All partitions in the index share the same index column .

Partition extensions and sub partition extensions allow users to perform some partition level and sub partition level operations on only one partition or sub partition , For example, delete all rows from a partition or sub partition .

Partition extension names allow users to use partitions like tables .

Users can use the following SQL Syntax specifies partition extension table name and sub partition extension table name .

The syntax of partition extension table name is as follows ： PARTITION partition

The syntax of the sub partition extension table name is as follows ： SUBPARTITION subpartition

DML sentence INSERT、 UPDATE、 DELETE and ANALYZE Statement requires parentheses around the name of a partition or sub partition .

• Extended name restrictions

There are the following restrictions when using partition extension table names and sub partition extension table names ：

There are no synonyms in the name . You must use the base table to specify the partition or sub partition extension , Synonyms cannot be used 、 View or any other object .

stay PARTITION and SUBPARTITION clause , Cannot specify binding variables for partition or sub partition names .

Example ：

Partition table sales With partition sales_q1_2021 , And create a view for it , Then delete some rows from the partition .

/ For the district sales_q1_2000 Create view Q1_2021_sales/

CREATE VIEW Q1_2021_sales ASSELECT * FROM sales PARTITION (SALES_Q1_2021);

/ Delete view Q1_2021_sales Meet the conditions in amount_sold < 0 Value /

DELETE FROM Q1_2021_sales WHERE amount_sold < 0;

3.10.5.  Properties and methods of reference object types

stay SQL Statement refers to an object type attribute or method , You must fully qualify references with table aliases .

The following example ：

Contains a type cust_address_typ And a table customers, Among them cust_address The column type is cust_address_typ：

CREATE TYPE cust_addr_typ AS OBJECT (

street_address VARCHAR2(40), postal_code VARCHAR2(10), city VARCHAR2(30), state_province VARCHAR2(10), country_id CHAR(2)

);

CREATE TABLE customers

( customer_id NUMBER(6), cust_first_name VARCHAR2(20) CONSTRAINT cust_fname_nn NOT NULL, cust_last_name VARCHAR2(20) CONSTRAINT cust_lname_nn NOT NULL, cust_address cust_address_typ,

...

stay SQL In the sentence , Yes postal_code References to attributes must be fully qualified with table aliases , As shown below ：

SELECT c.cust_address.postal_code FROM customers c;

UPDATE customers c SET c.cust_address.postal_code = '610000' WHERE c.cust_address.city = 'chengdu' AND c.cust_address.state_province = 'SICHUAN';

To reference a member method that does not accept parameters , Empty parentheses must be provided .

for example , One is based on catalog_typ Object table for category_tab, This table contains member functions getCatalogName. In order to be in SQL Statement , Empty parentheses must be provided .

As shown below ：

SELECT TREAT(VALUE(c) AS catalog_typ).getCatalogName() "Catalog Type" FROM categories_tab c WHERE category_id = 10;