Scribd Logo
Upload

Welcome to Scribd!

  • 30 views

    Table Creation

    Uploaded by

    Vijaya Prabakaran
    The document demonstrates various SQL statements for creating a table, inserting records, updating records, deleting records, and using implicit and explicit cursors. It creates a car table with four columns, inserts initial records, updates a record by changing the customer name, brand and model, deletes a record by ID, and performs selects using implicit and explicit cursors.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd

    Table Creation

    Uploaded by

    Vijaya Prabakaran
    30 views10 pages
    The document demonstrates various SQL statements for creating a table, inserting records, updating records, deleting records, and using implicit and explicit cursors. It creates a car table with four columns, inserts initial records, updates a record by changing the customer name, brand and model, deletes a record by ID, and performs selects using implicit and explicit cursors.

    Original Description:

    dbms sql queries

    Original Title

    Cursor

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document demonstrates various SQL statements for creating a table, inserting records, updating records, deleting records, and using implicit and explicit cursors. It creates a car table with four columns, inserts initial records, updates a record by changing the customer name, brand and model, deletes a record by ID, and performs selects using implicit and explicit cursors.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    30 views10 pages

    Table Creation

    Uploaded by

    Vijaya Prabakaran
    The document demonstrates various SQL statements for creating a table, inserting records, updating records, deleting records, and using implicit and explicit cursors. It creates a car table with four columns, inserts initial records, updates a record by changing the customer name, brand and model, deletes a record by ID, and performs selects using implicit and explicit cursors.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 10

    TABLE CREATION: SQL> create table car(id number,custname varchar2(10),brand varchar2(7),model varchar2(10)); Table created.

    INSERTING RECORDS: SQL> insert into car values(&id,'&custname','&brand','&model'); Enter value for id: 1 Enter value for custname: Naren Enter value for brand: Hyundai Enter value for model: Verna old 1: insert into car values(&id,'&custname','&brand','&model') new 1: insert into car values(1,'Naren','Hyundai','Verna') 1 row created.

    SQL> / Enter value for id: 2 Enter value for custname: Manoj Enter value for brand: Tata Enter value for model: Indica old 1: insert into car values(&id,'&custname','&brand','&model') new 1: insert into car values(2,'Manoj','Tata','Indica') 1 row created.

    SQL> / Enter value for id: 3 Enter value for custname: Prem Enter value for brand: Ford Enter value for model: Fiesta old 1: insert into car values(&id,'&custname','&brand','&model') new 1: insert into car values(3,'Prem','Ford','Fiesta') 1 row created.

    SQL> / Enter value for id: 4 Enter value for custname: Ragu Enter value for brand: Toyota Enter value for model: Innova old 1: insert into car values(&id,'&custname','&brand','&model') new 1: insert into car values(4,'Ragu','Toyota','Innova') 1 row created.

    SQL> select * from car;

    ID CUSTNAME BRAND MODEL ---------- ------------------ ------------- ---------1 Naren Hyundai Verna 2 Manoj Tata Indica 3 Prem Ford Fiesta 4 Ragu Toyota Innova

    IMPLICIT CURSORS Static Insert SQL> DECLARE id number; custname varchar2(30); brand varchar2(20); model varchar2(20); BEGIN id:=5; custname:='Pradheep'; brand:='Toyota'; model:='Fortuner'; insert into car values(id,custname,brand,model); dbms_output.put_line(TO_CHAR(SQL%ROWCOUNT)||' row inserted'); END; / 1 row inserted PL/SQL procedure successfully completed.

    Dynamic insert SQL> DECLARE id number; custname varchar2(30); brand varchar2(20); model varchar2(20); BEGIN id:=&id; custname:='&custname'; brand:='&brand'; model:='&model'; insert into car values(id,custname,brand,model); dbms_output.put_line(TO_CHAR(SQL%ROWCOUNT)||' row inserted'); END; / Enter value for id: 6 old 7: id:=&id; new 7: id:=6; Enter value for custname: Dhiran old 8: custname:='&custname'; new 8: custname:='Dhiran'; Enter value for brand: Maruti old 9: brand:='&brand'; new 9: brand:='Maruti'; Enter value for model: Ertiga old 10: model:='&model'; new 10: model:='Ertiga'; 1 row inserted PL/SQL procedure successfully completed.

    Update SQL> DECLARE id number; custname varchar2(30); brand varchar2(20); model varchar2(20); BEGIN update car set custname='&custname',brand='&brand',model='&model' where id=&id; dbms_output.put_line(TO_CHAR(SQL%ROWCOUNT)||'row Updated'); END; / Enter value for custname: Ragu Enter value for brand: Hyundai Enter value for model: i20 Enter value for id: 4 old 7: update car set custname='&custname',brand='&brand',model='&model' whereid=&id; new 7: update car set custname='Ragu',brand='Hyundai',model='i20' where id=4;

    1 row Updated PL/SQL procedure successfully completed.

    Deletion SQL> DECLARE id number; custname varchar2(30); brand varchar2(20); model varchar2(20); BEGIN delete from car where id=&id; dbms_output.put_line(TO_CHAR(SQL%ROWCOUNT)||' row Deleted'); END; / Enter value for id: 6 old 7: delete from car where id=&id; new 7: delete from car where id=6;

    1 row Deleted PL/SQL procedure successfully completed.

    EXPLICIT CURSORS Select SQL> DECLARE rec car%ROWTYPE; CURSOR cur is select * from car; BEGIN if not cur%ISOPEN then open cur; end if; Loop fetch cur into rec; exit when cur%NOTFOUND; dbms_output.put_line(rec.id||' '||rec.custname||' '||rec.brand||' '||rec.model); end loop; END;

    Update SQL> DECLARE modlrw car.model%TYPE; CURSOR cur is select model from car; BEGIN if not cur%ISOPEN then open cur; end if; Loop fetch cur into modlrw; update car set model='Elantra' where model='Verna'; exit when cur%NOTFOUND; end loop; END; /

    Using For SQL> DECLARE modlrw car.model%TYPE; CURSOR cur is select model from car; BEGIN for modlrw in cur Loop delete from car where id=&id; exit when cur%NOTFOUND; end loop; END;

    You might also like