Manipulate data with DML statements in PL/SQL

DML statements


  • Insert
  • Update
  • Delete
  • Merge

Insert Statement in PL/SQL


BEGIN
  INSERT
  INTO employees
    (employee_id, first_name, last_name,email,hire_date,job_id,salary )
    VALUES
    (301,'Bhavesh','Lakhani', 'Bhaveshlakhani5@gmail.com', sysdate,'IT', 10000 );
END;

update statement in PL/SQL


DECLARE
sal_increase employees.salary%TYPE := 5000;
BEGIN
UPDATE employees
SET salary = salary + sal_increase
WHERE job_id = 'IT_PROG';
END;

Delete Statement in PL/SQL


DECLARE
  emp_id employees.employee_id%TYPE := 301;
BEGIN
  DELETE FROM employees
  WHERE employee_id = emp_id;
END;

Merge Statement in PL/SQL


BEGIN
  MERGE INTO copy_emp c
  USING employees e
  ON (e.employee_id = c.empno)
  WHEN MATCHED THEN
  UPDATE SET
  c.first_name = e.first_name,
  c.last_name = e.last_name,
  c.email = e.email
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name,e.department_id);
END;

Leave a Reply

Your email address will not be published. Required fields are marked *