Manipulating Data in PL/SQL

To manipulate data in the database use DML statements

      INSERT, UPDATE and DELETE in PL/SQL

INSERT

              

DECLARE

   v_empno    emp.empno%TYPE   := &empno;

   v_ename    emp.ename%TYPE   := '&ename';

   v_salary   emp.sal%TYPE     := &sal;

BEGIN

   INSERT INTO emp

               (empno, ename, sal

               )

        VALUES (v_empno, v_ename, v_salary

               );

   COMMIT;

END;

UPDATE

DECLARE

   v_empno    emp.empno%TYPE;

   v_salary   emp.sal%TYPE     := &sal;

BEGIN

   UPDATE emp

      SET sal = v_salary

    WHERE empno = 1234;

   COMMIT;

END;

DELETE

DECLARE

   v_empno   emp.empno%TYPE   := &eno;

BEGIN

   DELETE      emp

         WHERE empno = v_empno;

   COMMIT;

END;

 Sequence Object

CREATE SEQUENCE MySeq

      INCREMENT BY 1

      START WITH 1

      MAXVALUE   99999

      NOCACHE

      NOCYCLE;

INSERT INTO emp (empno) VALUES(MySeq.NEXTVAL);

Sequence Object: Example

CREATE SEQUENCE seq_emp;

SET SERVEROUTPUT ON

DECLARE

   v_empno   emp.empno%TYPE;

BEGIN

   -- Using a sequence to autogenerate the primary key column values

   INSERT INTO emp

               (empno, ename

               )

        VALUES (seq_emp.NEXTVAL, 'Ajay'

               )

     RETURNING empno

          INTO v_empno;

   /* Displaying the value of the sequence that is inserted in the table */

   DBMS_OUTPUT.put_line (v_empno);

END;