AntDB-Oracle Compatibility Developer's Manual P3–9
Published: 2023-08-31 09:11:36

**Description**

The CREATE PROCEDURE command defines a new procedure. The CREATE OR REPLACE PROCEDURE command either creates a new procedure or replaces an existing procedure definition with a newly created procedure definition. If the name of the schema is included, then the procedure is created in the specified schema, otherwise it is created in the current schema. The new procedure name should not be the same as the name of any procedure in the same schema. Unless the intent is to update the definition of an existing procedure using the CREATE OR REPLACE PROCEDURE command.

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

See Section 3 for more on stored procedures.

**argument**

name

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

argname

The name of the argument. Arguments are referenced by their names inside the procedure body.

IN \| IN OUT \| OUT

Argument mode. IN declares the parameter as an input parameter. This is the default case. IN OUT allows the parameter to either receive a value or return a value. OUT specifies that the parameter is used as output only.

argtype

The data type of the procedure arguments. The argument type can be a base data type, a copy of a pre-existing column using %TYPE as the data type, or a user-defined type, such as a nested table or an object type. For any base data type, the length should not be specified-for example, specify VARCHAR2 instead of VARCHAR2 (10).

By using tablename.columnname%TYPE, we can refer to a column type. Using this approach allows us to not have to modify the body of the procedure when the definition of the table changes.

DEFAULT value

When a procedure is called without a value provided for the parameter, it can provide a default value for the input parameter. The parameter DEFAULT is not specified for parameters whose mode is IN OUT or OUT.

DEFINER \| CURRENT\_USER

Used to specify whether access to database objects referenced in a procedure can be granted using the privileges of the procedure owner (DEFINER) or the user currently executing the procedure (CURRENT\_USER). In the case of the procedure owner (DEFINER), the procedure owner's search path is used to resolve references to unqualified database objects. Also, in the case of the user currently executing the procedure (CURRENT\_USER), the search path of the user currently executing the procedure is used to resolve references to unqualified database objects. DEFINER is the default option.

declaration

Variables, types or declarations of REF CURSOR.

statement

SPL program statements. Note that a DECLARE-BEGIN-END block of code is itself considered an SPL statement. 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.

**Example**

The following procedure lists the employees in the emp table.

```
\set PLSQL_MODE ON
CREATE OR REPLACE PROCEDURE list_emp()
IS
v_empno	NUMBER(4);
v_ename	VARCHAR2(10);
CURSOR emp_cur IS
SELECT empno, ename FROM emp ORDER BY empno;
BEGIN
OPEN emp_cur;
DBMS_OUTPUT.PUT_LINE('EMPNO	ENAME');
DBMS_OUTPUT.PUT_LINE('-----	-------');
LOOP
FETCH emp_cur INTO v_empno, v_ename;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || '	' || v_ename);
END LOOP;
CLOSE emp_cur;
END;
/
\set PLSQL_MODE OFF
select list_emp();

NOTICE:  7369   SMITH
NOTICE:  7499   ALLEN
NOTICE:  7521   WARD
NOTICE:  7566   JONES
NOTICE:  7654   MARTIN
NOTICE:  7698   BLAKE
NOTICE:  7782   CLARK
NOTICE:  7788   SCOTT
NOTICE:  7839   KING
NOTICE:  7844   TURNER
NOTICE:  7876   ADAMS
NOTICE:  7900   JAMES
NOTICE:  7902   FORD
NOTICE:  7934   MILLER
 LIST_EMP 
----------
 
(1 row)
```

The following stored procedure first queries the employee number, employee name and job type based on the employee number, if no results are found then queries using the employee name and finally returns the results using the IN OUT parameter and the OUT parameter. This procedure is called from within an anonymous block of code.

Note that in the following example, the last parameter of emp\_job is the output. In oracle, the result can be assigned directly to the v\_job variable by emp\_job(v\_empno,v\_ename, v\_job). But in AntDB, when calling the procedure, the result variable can't be put in the parameter, it can only be called in this way: re= emp\_job(v\_empno, v\_ename); then extract the result from re.

```
\set PLSQL_MODE ON
CREATE OR REPLACE PROCEDURE emp_job (
p_empno	IN OUT numeric,
p_ename	IN OUT varchar2,
p_job	OUT	varchar2
)
IS
v_empno	emp.empno%TYPE; --Function arguments do not support definition in this format
v_ename	emp.ename%TYPE;
v_job	emp.job%TYPE;
BEGIN
SELECT ename, job INTO v_ename, v_job FROM emp WHERE empno = p_empno;
p_ename := v_ename;
p_job	:= v_job;
DBMS_OUTPUT.PUT_LINE('Found employee # ' || p_empno);
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
SELECT empno, job INTO v_empno, v_job FROM emp
WHERE ename = p_ename;
p_empno := v_empno;
p_job	:= v_job;
DBMS_OUTPUT.PUT_LINE('Found employee ' || p_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Could not find an employee with ' ||
'number, ' || p_empno || ' nor name, '	|| p_ename);
p_empno := NULL;
p_ename := NULL;
p_job	:= NULL;
END;
END;
/

DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_job   emp.job%TYPE;
re record;
BEGIN
v_empno := 7782;
v_ename := 'CLARK';
re := emp_job(v_empno, v_ename);
DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno);
DBMS_OUTPUT.PUT_LINE('Name  : ' || v_ename);
DBMS_OUTPUT.PUT_LINE('Job   : ' || re.p_job);
END;
/
\set PLSQL_MODE OFF

Found employee CLARK
Employee No: 7782
Name	: CLARK
Job	: MANAGER
```

#### CREATE PUBLIC SYNONYM

**Name**

CREATE PUBLIC - define a new public synonym

CREATE SYNONYM -- define a new private synonym

**Summary**

CREATE \[ OR REPLACE ] \[PUBLIC] SYNONYM name FOR object

**Description**

Use the CREATE PUBLIC SYNONYM command to create a public synonym for a particular type of database object. A synonym is another name that refers to a database object. A public synonym is a synonym that is valid in the database and can be referenced by any user of the database cluster. Without the public field, the CREATE SYNONYM command creates a private synonym, which can only be referenced by the current user.

The CREATE OR REPLACE PUBLIC SYNONYM command has a similar function, except that if a public synonym with the same name already exists, the newly created synonym replaces the one that already exists.

Synonyms are very useful in cases where a schema name is required to fully qualify a database object in order to properly reference it in a SQL statement.

**argument**

name

The name of the synonym to be created.

object

The name of the database object to which the synonym refers (which can be referenced in a schema-qualified manner). This database object can be a table, view, function, stored procedure or other synonym.

**Note**

In any SQL statement, each user can refer to a public synonym, however, the statement can only be executed successfully if the user has the appropriate permissions on the object specified by the synonym.

In AntDB, a public synonym is also a member of a schema.

We can create public synonyms for objects that do not exist.

Only the privileges of the current user using the public synonym can determine whether access to the database objects referenced by the public synonym is allowed. Therefore the public synonym user must have the appropriate permissions on the particular database object.

**Example**

Create a public synonym for the table emp in schema antdb.

```
CREATE PUBLIC SYNONYM personnel FOR antdb.emp;
```

Create a private synonym for the table emp in schema antdb.

```
CREATE SYNONYM personnel2 FOR antdb.emp;
```