Insert Record into Anonymous Block

Create a PL/SQL block to insert a new record into the Department table. Fetch the maximum department id from the Department table and add 10 to it. Then, take this value for department id. 'TESTING' is the value for department name and CHN-102 is the value for Location ID.

COLUMN	DATATYPE	CONS.
DEPARTMENT_ID	NUMBER(5)	PK
DEPARTMENT_NAME	VARCHAR2(25)	NOT NULL
LOCATION_ID	VARCHAR2(15)

Sample Output:

 
DEPARTMENT ID   DEPARTMENT NAME   LOCATION_ID
-------------   ---------------   -----------
XXXX            TESTING           CHN-102
            

(Hint: Data is case sensitive. Use '/' to terminate the PLSQL block)

This PL/SQL code snippet accomplishes the following tasks:

Declaration: It starts with declaring a variable maxid of type number, which will be used to store the maximum value of the DEPARTMENT_ID from the Department table.

BEGIN-END Block: The code is enclosed within a BEGIN and END block, indicating the beginning and end of the PL/SQL block.

SELECT Statement: Inside the BEGIN block, there's a SELECT statement that retrieves the maximum value of the DEPARTMENT_ID column from the Department table using the MAX function. The result is stored into the maxid variable using the INTO clause.

Incrementing: After retrieving the maximum ID, it increments maxid by 10. This ensures that the new department ID will be greater than the current maximum ID in the Department table.

INSERT Statement: Following the increment, there's an INSERT statement that inserts a new row into the Department table. The new row contains the incremented maxid as the DEPARTMENT_ID, along with values 'TESTING' for the department name and 'CHN-102' for the location.

Commitment Control: Finally, the / symbol is used to signify the end of the PL/SQL block and to execute the statements.

In summary, this code fetches the maximum department ID from the Department table, increments it by 10, and then inserts a new department with an ID incremented by 10, along with specified values for the department name and location. Users can adapt this code to their specific requirements by modifying table names, column names, and values as needed.