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 :

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Merhaba,

    Manipulating Data in PL/SQL being contrived to exist for many projects simply so it can be run will be the first to hit the wall, but those projects where the functions to make existing transactions cheaper in real world applications will find the elusive real world demand.

    1) This is my question that needs to be answered.
    option or create a simple materialized view.

    select IR.rowid MV_INST_LOBR_ROWID, J.rowid Job_ROWID, J.* FROM JOB J
    LEFT JOIN MV_INST_LOB_R IR ON
    (IR.I1503_CUST_AC_NO_PT1 = J.I3200_CUST_AC_NO_PT1 AND IR.I1503_CUST_AC_NO_PT2 = J.I3200_CUST_AC_NO_PT2 AND IR.I1503_INST_SEQ_NO = J.I3200_INST_SEQ_NO)
    WHERE IR.I1503_CUST_AC_NO_PT1 IS NOT NULL AND IR.I1503_CUST_AC_NO_PT2 IS NOT NULL
    UNION SELECT null, J.rowid Job_ROWID, J.* FROM JOB J WHERE J.I3200_CUST_AC_NO_PT1 IS NULL AND J.I3200_CUST_AC_NO_PT1 IS NULL;
    Basically, the intention is to get all the records joining MV_INST_LOB_R and JOB table
    where MV_INST_LOB_R do not have record for the joining condition J.I3200_CUST_AC_NO_PT1 / J.I3200_CUST_AC_NO_PT2

    I am trying to use this in a materialized view and got below error:

    SQL Error: ORA-12015: cannot create a fast refresh materialized view from a complex query. I have searched for help on the internet and still have not found a solution.
    12015. 00000 - "cannot create a fast refresh materialized view from a complex query"
    *Cause: Neither ROWIDs and nor primary key constraints are supported for
    complex queries.
    *Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE

    Can you help me in converting to simple query, so that I can use simple materialized view.

    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!

    Thank you,
    Preethi

    ReplyDelete
  3. Hello There,

    Fully agree on Manipulating Data in PL/SQL . We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.

    My table column type is clob. Field value contain double quotes

    id,text_clob,date

    1,"\"data1\",0,1,1,1,\"data2\"","2018-03-03 09:08:45"

    can you kindly help loader query. i cant use replace in clob field.
    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!

    Thank you,
    Preethi.

    ReplyDelete