In this tutorial, you will learn how to create the procedure for DML statements in Toad for Oracle. Toad has an option to generate Oracle stored procedure for Insert, Update and Delete DML statements. You can create these procedures in a package or can build as standalone. This method is very similar to Oracle SQL Developer's Create Table API option. The following are the steps to create DML procedure in Toad:
Steps to Create Procedure for DML in Toad for Oracle
- In Toad for Oracle, click on the menu Database > Schema Browser.
- Then in the Schema Browser's Table tab, select your table for which you want to create the procedure and do the right click on it.
- From the shortcut menu choose Create DML Procedures.
- Then the Create DML Procedures window will open. Select INSERT, UPDATE and DELETE checkboxes to create the procedure for these three statements.
- Then click on the Other Options tab and click on the Use Packages checkbox to generate these procedures in a package. It is better to create a database package rather than creating three procedures separately because all these DML belongs to one table.
- Now click on the Output tab and choose the destination for your package. You can select to create this package in a file or create directly in the database or to the clipboard.
- Then click on the OK button, and you will get the Package Specification and the Package Body for DML procedures based on the Countries table as shown in the below example.
You can create the same DML package online check this link: Create DML Package Online
CREATE OR REPLACE PACKAGE DML_COUNTRIES
IS
PROCEDURE INS_COUNTRIES (
in_COUNTRY_ID IN COUNTRIES.COUNTRY_ID%TYPE,
in_COUNTRY_ISO_CODE IN COUNTRIES.COUNTRY_ISO_CODE%TYPE,
in_COUNTRY_NAME IN COUNTRIES.COUNTRY_NAME%TYPE,
in_COUNTRY_SUBREGION IN COUNTRIES.COUNTRY_SUBREGION%TYPE,
in_COUNTRY_SUBREGION_ID IN COUNTRIES.COUNTRY_SUBREGION_ID%TYPE,
in_COUNTRY_REGION IN COUNTRIES.COUNTRY_REGION%TYPE,
in_COUNTRY_REGION_ID IN COUNTRIES.COUNTRY_REGION_ID%TYPE,
in_COUNTRY_TOTAL IN COUNTRIES.COUNTRY_TOTAL%TYPE,
in_COUNTRY_TOTAL_ID IN COUNTRIES.COUNTRY_TOTAL_ID%TYPE,
in_COUNTRY_NAME_HIST IN COUNTRIES.COUNTRY_NAME_HIST%TYPE);
PROCEDURE UPD_COUNTRIES (
in_COUNTRY_ID IN COUNTRIES.COUNTRY_ID%TYPE,
in_COUNTRY_ISO_CODE IN COUNTRIES.COUNTRY_ISO_CODE%TYPE,
in_COUNTRY_NAME IN COUNTRIES.COUNTRY_NAME%TYPE,
in_COUNTRY_SUBREGION IN COUNTRIES.COUNTRY_SUBREGION%TYPE,
in_COUNTRY_SUBREGION_ID IN COUNTRIES.COUNTRY_SUBREGION_ID%TYPE,
in_COUNTRY_REGION IN COUNTRIES.COUNTRY_REGION%TYPE,
in_COUNTRY_REGION_ID IN COUNTRIES.COUNTRY_REGION_ID%TYPE,
in_COUNTRY_TOTAL IN COUNTRIES.COUNTRY_TOTAL%TYPE,
in_COUNTRY_TOTAL_ID IN COUNTRIES.COUNTRY_TOTAL_ID%TYPE,
in_COUNTRY_NAME_HIST IN COUNTRIES.COUNTRY_NAME_HIST%TYPE);
PROCEDURE DEL_COUNTRIES (in_COUNTRY_ID IN COUNTRIES.COUNTRY_ID%TYPE);
END DML_COUNTRIES;
/
CREATE OR REPLACE PACKAGE BODY DML_COUNTRIES
IS
PROCEDURE INS_COUNTRIES (
in_COUNTRY_ID IN COUNTRIES.COUNTRY_ID%TYPE,
in_COUNTRY_ISO_CODE IN COUNTRIES.COUNTRY_ISO_CODE%TYPE,
in_COUNTRY_NAME IN COUNTRIES.COUNTRY_NAME%TYPE,
in_COUNTRY_SUBREGION IN COUNTRIES.COUNTRY_SUBREGION%TYPE,
in_COUNTRY_SUBREGION_ID IN COUNTRIES.COUNTRY_SUBREGION_ID%TYPE,
in_COUNTRY_REGION IN COUNTRIES.COUNTRY_REGION%TYPE,
in_COUNTRY_REGION_ID IN COUNTRIES.COUNTRY_REGION_ID%TYPE,
in_COUNTRY_TOTAL IN COUNTRIES.COUNTRY_TOTAL%TYPE,
in_COUNTRY_TOTAL_ID IN COUNTRIES.COUNTRY_TOTAL_ID%TYPE,
in_COUNTRY_NAME_HIST IN COUNTRIES.COUNTRY_NAME_HIST%TYPE)
IS
BEGIN
INSERT INTO COUNTRIES (COUNTRY_ID,
COUNTRY_ISO_CODE,
COUNTRY_NAME,
COUNTRY_SUBREGION,
COUNTRY_SUBREGION_ID,
COUNTRY_REGION,
COUNTRY_REGION_ID,
COUNTRY_TOTAL,
COUNTRY_TOTAL_ID,
COUNTRY_NAME_HIST)
VALUES (in_COUNTRY_ID,
in_COUNTRY_ISO_CODE,
in_COUNTRY_NAME,
in_COUNTRY_SUBREGION,
in_COUNTRY_SUBREGION_ID,
in_COUNTRY_REGION,
in_COUNTRY_REGION_ID,
in_COUNTRY_TOTAL,
in_COUNTRY_TOTAL_ID,
in_COUNTRY_NAME_HIST);
END INS_COUNTRIES;
PROCEDURE UPD_COUNTRIES (
in_COUNTRY_ID IN COUNTRIES.COUNTRY_ID%TYPE,
in_COUNTRY_ISO_CODE IN COUNTRIES.COUNTRY_ISO_CODE%TYPE,
in_COUNTRY_NAME IN COUNTRIES.COUNTRY_NAME%TYPE,
in_COUNTRY_SUBREGION IN COUNTRIES.COUNTRY_SUBREGION%TYPE,
in_COUNTRY_SUBREGION_ID IN COUNTRIES.COUNTRY_SUBREGION_ID%TYPE,
in_COUNTRY_REGION IN COUNTRIES.COUNTRY_REGION%TYPE,
in_COUNTRY_REGION_ID IN COUNTRIES.COUNTRY_REGION_ID%TYPE,
in_COUNTRY_TOTAL IN COUNTRIES.COUNTRY_TOTAL%TYPE,
in_COUNTRY_TOTAL_ID IN COUNTRIES.COUNTRY_TOTAL_ID%TYPE,
in_COUNTRY_NAME_HIST IN COUNTRIES.COUNTRY_NAME_HIST%TYPE)
IS
BEGIN
UPDATE COUNTRIES
SET COUNTRY_ISO_CODE = in_COUNTRY_ISO_CODE,
COUNTRY_NAME = in_COUNTRY_NAME,
COUNTRY_SUBREGION = in_COUNTRY_SUBREGION,
COUNTRY_SUBREGION_ID = in_COUNTRY_SUBREGION_ID,
COUNTRY_REGION = in_COUNTRY_REGION,
COUNTRY_REGION_ID = in_COUNTRY_REGION_ID,
COUNTRY_TOTAL = in_COUNTRY_TOTAL,
COUNTRY_TOTAL_ID = in_COUNTRY_TOTAL_ID,
COUNTRY_NAME_HIST = in_COUNTRY_NAME_HIST
WHERE COUNTRY_ID = in_COUNTRY_ID;
END UPD_COUNTRIES;
PROCEDURE DEL_COUNTRIES (in_COUNTRY_ID IN COUNTRIES.COUNTRY_ID%TYPE)
IS
BEGIN
DELETE FROM COUNTRIES
WHERE COUNTRY_ID = in_COUNTRY_ID;
END DEL_COUNTRIES;
END DML_COUNTRIES;
/How to Use This DML Package?
Suppose you want to insert a row in the Countries table, then call the DML_COUNTRIES packaged procedure INS_COUNTRIES as following:
DECLARE
IN_COUNTRY_ID NUMBER;
IN_COUNTRY_ISO_CODE CHAR (2);
IN_COUNTRY_NAME VARCHAR2 (40);
IN_COUNTRY_SUBREGION VARCHAR2 (30);
IN_COUNTRY_SUBREGION_ID NUMBER;
IN_COUNTRY_REGION VARCHAR2 (20);
IN_COUNTRY_REGION_ID NUMBER;
IN_COUNTRY_TOTAL VARCHAR2 (11);
IN_COUNTRY_TOTAL_ID NUMBER;
IN_COUNTRY_NAME_HIST VARCHAR2 (40);
BEGIN
IN_COUNTRY_ID := 1001;
IN_COUNTRY_ISO_CODE := 'US';
IN_COUNTRY_NAME := 'United States of America';
IN_COUNTRY_SUBREGION := 'Northern America';
IN_COUNTRY_SUBREGION_ID := 52797;
IN_COUNTRY_REGION := 'Americas';
IN_COUNTRY_REGION_ID := 52801;
IN_COUNTRY_TOTAL := NULL;
IN_COUNTRY_TOTAL_ID := NULL;
IN_COUNTRY_NAME_HIST := NULL;
DML_COUNTRIES.INS_COUNTRIES (IN_COUNTRY_ID,
IN_COUNTRY_ISO_CODE,
IN_COUNTRY_NAME,
IN_COUNTRY_SUBREGION,
IN_COUNTRY_SUBREGION_ID,
IN_COUNTRY_REGION,
IN_COUNTRY_REGION_ID,
IN_COUNTRY_TOTAL,
IN_COUNTRY_TOTAL_ID,
IN_COUNTRY_NAME_HIST);
COMMIT;
END;
/