AntDB-Oracle Compatibility Developer's Manual P3–7
Published: 2023-08-31 08:50:33

#### COMMIT

**Name**

COMMIT--Commit the current transaction

**Summary**

COMMIT \[ WORK ]

**Description**

The COMMIT command is used to commit the current transaction. When this command completes, all update operations generated by the transaction are visible to other transactions. If a system crash occurs, the committed update operation remains permanently valid.

**argument**

WORK

Optional keyword - has no effect.

**Note**

Use the ROLLBACK command to terminate a transaction. Executing the COMMIT command without being inside a transaction does not cause any side effects.

**Example**

Commit the current transaction and make all changes permanent.

```COMMIT;
COMMIT;
```

#### CREATE FUNCTION

**Name**

CREATE FUNCTION -- Define a new function. Before performing this operation, set to turn PLSQL\_MODE on, as follows.

```
\set PLSQL_MODE ON
```

**Summary**

CREATE \[ OR REPLACE ] FUNCTION name

\[ (argname \[ IN \| IN OUT \| OUT ] argtype \[ DEFAULT value ]

\[, ...]) ]

RETURN rettype

{ IS \| AS }

\[ declaration; ] \[, ...]

BEGIN

statement; \[...]

\[ EXCEPTION

{ WHEN exception \[ OR excepti\`on ] \[...] THEN

statement; \[, ...] } \[, ...]

]

END \[ name ]

**Description**

The CREATE FUNCTION command is used to define a new function. The CREATE OR REPLACE FUNCTION command, on the other hand, can either create a new function or replace an existing function definition with a newly created function definition.

If the name of the schema is included in the command, then the function is created in the specified schema. Otherwise it is created in the current mode. The name of the newly created function cannot be the same as the name of any function with the same argument type in the same schema. However, functions with different argument types can use the same function name (we call this approach overloading). (Overloading of functions is an AntDB feature and is not compatible with Oracle).

By using the CREATE OR REPLACE FUNCTION command, we can update the definition of an existing function. But you can't change the name of the function or the parameter type in this way (if you try this, you are actually creating a new function). And the CREATE OR REPLACE FUNCTION command will not change the return value of an existing function unless it is deleted and recreated.

The user who created the function is the owner of the newly created function.

For more information about functions, see Section 2.5.

**argument**

name

The name of the function to be created (which can be referenced in a schema-qualified manner).

argname

The name of a parameter. The argument is referenced by using this name within the function body.

IN \| IN OUT \| OUT

Parameter mode. IN declares the parameter as an input parameter. This is the default case. IN OUT allows the parameter to both receive and return a value. OUT, on the other hand, specifies that the parameter is to be used only for output.

argtype

The data type of the function parameter. The argument type can be a base data type, a type copy of a column with %TYPE, or a user-defined type such as a nested table or an object type. The length should not be specified for any base data type. For example, we should use a form like VARCHAR2 instead of VARCHAR2(10).

A column type can be referenced by using a form like tablename.columnname%TYPE. This way when the table definition changes, we can do so without having to make changes to the specific implementation of the function.

DEFAULT value

When the function is called without providing a value for the parameter, we use this parameter to provide a default value for the input parameter. And for parameters whose mode is IN OUT or OUT, the value of the parameter DEFAULT can be not specified.

rettype

The data type of the return value can be any of the listed argument types. As with argtype, no length should be specified for rettype.

declaration

Variables, types or declarations of REF CURSOR.

statement

SPL program statements. Note that for a DECLARE - BEGIN - END block, it is considered an SPL statement in itself. Therefore, function bodies can contain nested blocks of code inside them.

exception

The name of the exception condition, e.g. NO\_DATA\_FOUND, OTHERS, etc.

**Note**

AntDB allows function overloading; this means that different functions can use the same name as long as they have different parameter types.

If you are using psql commands to create functions, stored procedures and triggers, or to execute anonymous block statements, you need to replace the last semicolon ";" with a backslash "/" in the SQL statement.

**Example**

The emp\_comp function takes 2 values as input parameters and returns a calculated value. We use the SELECT command to demonstrate the use of this function.

```
\set PLSQL_MODE ON
CREATE OR REPLACE FUNCTION emp_comp (
p_sal	NUMBER,
p_comm	NUMBER
) RETURN NUMBER
IS
BEGIN
RETURN (p_sal + NVL(p_comm, 0)) * 24;
END;
/
\set PLSQL_MODE OFF
SELECT ename "Name", sal "Salary", comm "Commission", emp_comp(sal, comm)
"Total Compensation"	FROM emp;

  Name  | Salary | Commission | Total Compensation 
--------------+--------+-------------------+---------------------------------
 SMITH  |    800 |            |              19200
 ALLEN  |   1600 |        300 |              45600
 WARD   |   1250 |        500 |              42000
 JONES  |   2975 |            |              71400
 MARTIN |   1250 |       1400 |              63600
 BLAKE  |   2850 |            |              68400
 CLARK  |   2450 |            |              58800
 SCOTT  |   3000 |            |              72000
 KING   |   5000 |            |             120000
 TURNER |   1500 |          0 |              36000
 ADAMS  |   1100 |            |              26400
 JAMES  |    950 |            |              22800
 FORD   |   3000 |            |              72000
 MILLER |   1300 |            |              31200
(14 rows)
```

The sal\_range function returns the total number of employees within the specified salary range. The following anonymous block calls the function several times, where the default values of the parameters are used in the first two calls.

```
\set PLSQL_MODE ON
CREATE OR REPLACE FUNCTION sal_range (
p_sal_min	NUMBER DEFAULT 0,
p_sal_max	NUMBER DEFAULT 10000
) RETURN INTEGER
IS
v_count	INTEGER;
BEGIN
SELECT COUNT(*) INTO v_count FROM emp
WHERE sal BETWEEN p_sal_min AND p_sal_max;
RETURN v_count;
END;
/
\set PLSQL_MODE OFF

select sal_range();
SAL_RANGE 
--------------------
        14
(1 row)
```