Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Wednesday, 6 April 2016

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, 'Daya'
               )
     RETURNING empno
          INTO v_empno;

   /* Displaying the value of the sequence that is inserted in the table */
   DBMS_OUTPUT.put_line(v_empno);

END;
Share this article :

0 comments:

Post a Comment