Home > Software design >  difference between creating a type with new keyword or without in plsql
difference between creating a type with new keyword or without in plsql

Time:10-26

In oracle pl/sql you can create an object with or without the new keyword. Is there any difference how oracle threats these requests ?

So for example:

CREATE TYPE emp_object AS OBJECT(
emp_no NUMBER,
emp_name VARCHAR2(50),
salary NUMBER,
manager NUMBER,
CONSTRUCTOR FUNCTION emp_object(p_emp_no NUMBER, p_emp_name VARCHAR2,
p_salary NUMBER) RETURN SELF AS RESULT),
MEMBER PROCEDURE insert_records,
MEMBER PROCEDURE display_records);
/

CREATE OR REPLACE TYPE BODY emp_object AS
CONSTRUCTOR FUNCTION emp_object(p_emp_no NUMBER,p_emp_name VARCHAR2,
p_salary NUMBER)
RETURN SELF AS RESULT
IS
BEGIN
Dbms_output.put_line('Constructor fired..');
SELF.emp_no:=p_emp_no;|
SELF.emp_name:=p_emp_name;
SELF.salary:=p_salary;
SELF.managerial:=1001;
RETURN;
END:
MEMBER PROCEDURE insert_records
IS
BEGIN
INSERT INTO emp VALUES(emp_noemp_name,salary,manager);
END
MEMBER PROCEDURE display_records
IS
BEGIN
Dbms_output.put_line('Employee Name:'||emp_name);
Dbms_output.put_line('Employee Number:'||emp_no);
Dbms_output.put_line('Salary':'||salary);
Dbms_output.put_line('Manager:'||manager);
END:
END:
/

This is a type with spec and body, now you can create the object like this without the new keyword:

DECLARE
guru_emp_det emp_object;
BEGIN
guru_emp_det:=emp_object(1005,'RRR',20000,1000);
guru_emp_det.display_records;
guru_emp_det.insert_records;
COMMIT;
END;

But it is also possible with the keyword:

DECLARE
guru_emp_det emp_object;
BEGIN
guru_emp_det:= new emp_object(1005,'RRR',20000,1000);
guru_emp_det.display_records;
guru_emp_det.insert_records;
COMMIT;
END;

CodePudding user response:

From the Type Constructor Expressions documentation:

Type Constructor Expressions

A type constructor expression specifies a call to a constructor method. The argument to the type constructor is any expression. Type constructors can be invoked anywhere functions are invoked.

type_constructor_expression::= Syntax diagram for a type constructor

The NEW keyword applies to constructors for object types but not for collection types. It instructs Oracle to construct a new object by invoking an appropriate constructor. The use of the NEW keyword is optional, but it is good practice to specify it.

As to your question:

Is there any difference how oracle treats these requests?

No, there is no difference; the NEW keyword is optional.

Although the documentation states that the NEW keyword does not apply for collection types, in practice it does not appear to be invalid syntax and is both allowable and optional for collection types (there may be an edge-case I have not yet found where the NEW keyword is forbidden; however, on some brief tests I cannot find such a case).

fiddle

  • Related