AntDB-Oracle Compatibility Developer's Manual P2–1
Published: 2023-08-29 06:08:08

#### Update Operations

We can use the UPDATE command to update the value of a column in an existing record. For example, the following command shows the results before and after each employee with a manager position received a ten percent raise.

```
SELECT ename, sal FROM emp WHERE job = 'MANAGER';

 ENAME | SAL  
-------+------
 JONES | 2975
 BLAKE | 2850
 CLARK | 2450
(3 rows)

UPDATE emp SET sal = sal * 1.1 WHERE job = 'MANAGER';

SELECT ename, sal FROM emp WHERE job = 'MANAGER';

 ENAME |  SAL   
-------+--------
 JONES | 3272.5
 BLAKE |   3135
 CLARK |   2695
(3 rows)
```

#### Delete Operations

The DELETE command is used to delete rows from a table. For example, the following command displays the results before and after deleting all employees with department number 20.

```
SELECT ename, deptno FROM emp;

 ENAME  | DEPTNO 
--------+--------
 SMITH  |     20
 ALLEN  |     30
 WARD   |     30
 MARTIN |     30
 SCOTT  |     20
 KING   |     10
 TURNER |     30
 ADAMS  |     20
 JAMES  |     30
 FORD   |     20
 MILLER |     10
 JONES  |     20
 BLAKE  |     30
 CLARK  |     10
(14 rows)

DELETE FROM emp WHERE deptno = 20;

SELECT ename, deptno FROM emp;

 ENAME  | DEPTNO 
--------+---------------
 ALLEN  |     30
 WARD   |     30
 MARTIN |     30
 KING   |     10
 TURNER |     30
 JAMES  |     30
 MILLER |     10
 BLAKE  |     30
 CLARK  |     10
(9 rows)
```

We should be especially careful when using the DELETE command without the WHERE clause. As follows: 

```
DELETE FROM tablename; 
```

This statement will delete all records from the specified table. Before performing such an operation, the system will not ask for confirmation that such an operation is being performed.










### **Advanced Concepts**

In the previous chapter, we introduced the basic methods of using SQL commands to store and access data in AntDB. This chapter discusses more advanced features of SQL commands that can be used to simplify the management of data and prevent data loss or corruption.

#### View

First, take a look at this SELECT command below.

```
SELECT ename, sal, sal * 24 AS yearly_salary, deptno FROM emp;

 ENAME  | SAL  | YEARLY_SALARY | DEPTNO 
--------+------+---------------+--------
 SMITH  |  800 |         19200 |     20
 ALLEN  | 1600 |         38400 |     30
 WARD   | 1250 |         30000 |     30
 JONES  | 2975 |         71400 |     20
 MARTIN | 1250 |         30000 |     30
 BLAKE  | 2850 |         68400 |     30
 CLARK  | 2450 |         58800 |     10
 SCOTT  | 3000 |         72000 |     20
 KING   | 5000 |        120000 |     10
 TURNER | 1500 |         36000 |     30
 ADAMS  | 1100 |         26400 |     20
 JAMES  |  950 |         22800 |     30
 FORD   | 3000 |         72000 |     20
 MILLER | 1300 |         31200 |     10
(14 rows)

```

If this query is used frequently, then you can use this query to create a view. This allows you to reuse the query in an easy way without having to type the entire SELECT command again and again.

```
CREATE VIEW employee_pay AS SELECT ename, sal, sal * 24 AS yearly_salary, deptno FROM emp; 
```

In executing the query, we can use the view name employee\_pay just like a normal table name.

```
SELECT * FROM employee_pay;

 ENAME  | SAL  | YEARLY_SALARY | DEPTNO 
--------+------+---------------+---------
 SMITH  |  800 |         19200 |     20
 ALLEN  | 1600 |         38400 |     30
 WARD   | 1250 |         30000 |     30
 JONES  | 2975 |         71400 |     20
 MARTIN | 1250 |         30000 |     30
 BLAKE  | 2850 |         68400 |     30
 CLARK  | 2450 |         58800 |     10
 SCOTT  | 3000 |         72000 |     20
 KING   | 5000 |        120000 |     10
 TURNER | 1500 |         36000 |     30
 ADAMS  | 1100 |         26400 |     20
 JAMES  |  950 |         22800 |     30
 FORD   | 3000 |         72000 |     20
 MILLER | 1300 |         31200 |     10
(14 rows)

```

Making full use of views is a key part of a well-designed SQL database. Views provide a consistent interface to encapsulate the detailed structure of a data table, which can be changed when the program is changed.

We can use views where real tables are used. It is not common to create views on top of other views.

#### Foreign Key 

If you want to ensure that all employees belong to a pre-existing department, then this constraint is called maintaining the referential integrity of the data. In the most simplified database system, this is achieved by first checking in the dept table for the existence of matching records and then inserting or rejecting new employee records. This approach has many problems and is very inconvenient. AntDB provides a much easier way for users to implement it.

We added a foreign key to the table emp that appears in section 1.1.3, and the modified table emp looks like this.

```
CREATE TABLE emp (
empno	NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename	VARCHAR2(10),
job	VARCHAR2(9),
mgr	NUMBER(4),
hiredate	DATE,
sal	NUMBER(7,2),
comm	NUMBER(7,2),
deptno	NUMBER(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);
```

If you try to run this INSERT command below in table emp, then the foreign key constraint emp\_ref\_dept\_fk makes sure that the record with department number 50 exists in table dept. And in fact, because this department does not exist, the system refuses to execute this command.

```
INSERT INTO emp VALUES (8000,'JONES','CLERK',7902,'17-AUG-07',1200,NULL,50);

ERROR:	insert or update on table "emp" violates foreign key constraint "emp_ref_dept_fk"
DETAIL:	Key (deptno)=(50) is not present in table "dept".
```

By taking advantage of the system behavior of foreign keys, the program can be better tuned. Proper use of foreign keys will improve the quality of the database programs you design, so we encourage you to learn more about foreign keys.

#### ROWID Column

The rowid is a unique identifier for a row and has global uniqueness.

There are 2 parameter settings related to rowid.

1. default\_with\_rowids： Used to control the rowid switch.
2. default\_with\_rowid\_id : Used to generate the high bit of the rowid value, the default value is an invalid value -1. If the switch is turned on, this value needs to be set to 0-1023, but it is not recommended to set 0 and 1023, this value cannot be changed after the first rowid value is generated.

There are three ways to turn on the rowid switch.

- Set the global switch.

Directly modify the configuration document postgresql.conf and set the following parameters.

```
default_with_rowids = on
```

At this point, all tables created in the database will have a rowid column by default.

- Session level setting.

If the following statement is executed in the same session, only the tables created in the current session will have the rowid column by default.

```
set default_with_rowids = on
```

- Table level setting

The create table statement plus with rowid, as follows: 

```
create table tt(id int, sal int) with rowid;
insert into tt values(1, 1000),(2, 4000),(4, 8000);
select rowid, * from tt;
    ROWID     | ID | SAL  
--------------+----+------
 ZAAAAAAAQAA= |  1 | 1000
 ZQAAAAAAQAA= |  2 | 4000
 ZgAAAAAAQAA= |  4 | 8000
(3 rows)
```

Note: When updating a row of data, the corresponding row rowid will be updated under the cluster version. Under the standalone version, the corresponding rowid will not be updated. Therefore, the standalone version is more compatible with Oracle.