rdbms file by surinderkumar0008

VIEWS: 37 PAGES: 24

More Info
									                                           PRACTICAL NO:11

                                                  CURSORS

IMPLICIT CURSORS:
SQL> set linesize 1500;
SQL> select * from emp;

EMPNO ENAME      JOB                    MGR HIREDATE         SAL       COMM      DEPTNO
--------- ----------       --------- ---------- --------- ---------- ---------- ----------
     7369 SMITH            CLERK           7902 17-DEC-80         800                   20
     7499 ALLEN            SALESMAN        7698 20-FEB-81        1600        300        30
     7521 WARD             SALESMAN        7698 22-FEB-81        1250        500        30
     7566 JONES            MANAGER         7839 02-APR-81        2975                   20
     7654 MARTIN           SALESMAN        7698 28-SEP-81        1250       1400        30
     7698 BLAKE            MANAGER         7839 01-MAY-81        2850                   30
     7782 CLARK            MANAGER         7839 09-JUN-81        2450                   10
     7788 SCOTT            ANALYST         7566 19-APR-87        3000                   20
     7839 KING             PRESIDENT            17-NOV-81        5000                   10
     7844 TURNER           SALESMAN        7698 08-SEP-81        1500          0        30
     7876 ADAMS            CLERK           7788 23-MAY-87        1100                   20

    EMPNO    ENAME         JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------    ----------    --------- ---------- --------- ---------- ---------- ----------
     7900    JAMES         CLERK           7698 03-DEC-81        950                    30
     7902    FORD          ANALYST         7566 03-DEC-81       3000                    20
     7934    MILLER        CLERK           7782 23-JAN-82       1300                    10


ATTRIBUTES OF IMPLICIT CURSORS:
SQL> select * from emp where empno=7499;

    EMPNO ENAME                JOB             MGR HIREDATE                  SAL       COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7499 ALLEN           SALESMAN               7698 20-FEB-81            1600       300  30

   1. %Found
SQL> set serveroutput on
SQL> declare
 2 begin
 3 update emp set sal=sal*0.5 where empno=&empno;
 4 if sql%found then
 5 dbms_output.put_line('emp record modify successfully');
 6 else
 7 dbms_output.put_line('emp record doesnot exit');
 8 end if;
 9 end;
10 /
Enter value for empno: 7499
old 3: update emp set sal=sal*0.5 where empno=&empno;
new 3: update emp set sal=sal*0.5 where empno=7499;
emp record modify successfully

PL/SQL procedure successfully completed.

SQL> select * from emp where empno=7499;

    EMPNO ENAME                JOB             MGR HIREDATE                  SAL       COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7499 ALLEN           SALESMAN               7698 20-FEB-81             800       300  30

   2. %Notfound

SQL> set serveroutput on
SQL> declare
  2 begin
  3 update emp set sal=sal*0.5 where empno=&empno;
  4 if sql%notfound then
  5 dbms_output.put_line('emp record doesnot exit');
  6 else
  7 dbms_output.put_line('emp record modify successfully');
  8 end if;
  9 end;
 10 /
Enter value for empno: 1546
old 3: update emp set sal=sal*0.5 where empno=&empno;
new 3: update emp set sal=sal*0.5 where empno=1546;
emp record doesnot exit

PL/SQL procedure successfully completed.

SQL> /
Enter value for empno: 7499
old 3: update emp set sal=sal*0.5 where empno=&empno;
new 3: update emp set sal=sal*0.5 where empno=7499;
emp record modify successfully

PL/SQL procedure successfully completed.

SQL> select * from emp where empno=7499;

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
 7499 ALLEN      SALESMAN        7698 20-FEB-81        400        300         30
   3. %Rowcount

SQL> declare
  2 row_affected char(4);
  3 begin
  4 update emp set sal=sal*0.5 where job='MANAGER';
  5 row_affected:=to_char(sql%rowcount);
  6 if sql%rowcount>0 then
  7 dbms_output.put_line(row_affected||'emp records modify successfully');
  8 else
  9 dbms_output.put_line('there are no employees working as managers');
 10 end if;
 11 end;
 12 /
3 emp records modify successfully

PL/SQL procedure successfully completed.

SQL> select * from emp where job='MANAGER';

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
------- ---------- --------- ---------- --------- ---------- ---------- ----------
   7566 JONES      MANAGER         7839 02-APR-81     1487.5                     20
   7698 BLAKE      MANAGER         7839 01-MAY-81       1425                     30
   7782 CLARK      MANAGER         7839 09-JUN-81       1225                     10
EXPLICIT CURSORS:

ATTRIBUTES:

   1. %ISOPEN

Set serveroutput on
SQL> declare
 2 cursor c_emp is select empno,sal from emp where deptno=20;
 3 str_empno emp.empno%type;
 4 num_sal emp.sal%type;
 5 begin
 6 open c_emp;
 7 if c_emp%isopen then
 8 loop
 9 fetch c_emp into str_empno, num_sal;
10 exit when c_emp%notfound;
11 update emp set sal=num_sal+(num_sal*0.5) where empno= str_empno;
12 insert into emp_raise values(str_empno,sysdate,num_sal*0.5);
13 end loop;
14 commit;
15 close c_emp;
16 else
17 dbms_output.put_line('unable to open cursor');
18 end if;
19 end;
20
21 /

PL/SQL procedure successfully completed.

SQL> select * from emp_raise;

   EMPNO INC_DATE INC_SAL
  ---------- --------- ----------
    7369 28-MAR-11       400
    7566 28-MAR-11 1487.5
    7788 28-MAR-11      1500
    7876 28-MAR-11       550
    7902 28-MAR-11      1500
SQL> select * from emp;

EMPNO ENAME       JOB                 MGR HIREDATE         SAL       COMM     DEPTNO
---------- ----------     --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH          CLERK           7902 17-DEC-80       1200                    20
      7499 ALLEN          SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD           SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES          MANAGER         7839 02-APR-81     4462.5                    20
      7654 MARTIN         SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE          MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK          MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT          ANALYST         7566 19-APR-87       4500                    20
      7839 KING           PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER         SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS          CLERK           7788 23-MAY-87       1650                    20

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES       CLERK          7698 03-DEC-81        950                     30
      7902 FORD        ANALYST        7566 03-DEC-81       4500                     20
      7934 LER       CLERK          7782 23-JAN-82       1300                    10
   14 Rows selected.


   2. %FOUND
SQL> declare
 2 cursor c_emp is select empno,sal from emp where deptno=20;
 3 str_empno emp.empno%type;
 4 num_sal emp.sal%type;
 5 begin
 6 open c_emp;
 7 loop
 8 fetch c_emp into str_empno, num_sal;
 9 if c_emp%found then
10 update emp set sal=num_sal+(num_sal*0.5) where empno= str_empno;
11 insert into emp_raise values(str_empno,sysdate,num_sal*0.5);
12 else
13 exit;
14 end if;
15 end loop;
16 commit;
17 close c_emp;
18 end;
19 /

PL/SQL procedure successfully completed.
   3. %Notfound
SQL> set serveroutput on
SQL> declare
 2 cursor c_emp is select empno,sal from emp where deptno=21;
 3 str_empno emp.empno%type;
 4 num_sal emp.sal%type;
 5 begin
 6 open c_emp;
 7 loop
 8 fetch c_emp into str_empno, num_sal;
 9 if c_emp%notfound then exit;
10 else
11 update emp set sal=num_sal+(num_sal*0.5) where empno= str_empno;
12 insert into emp_raise values(str_empno,sysdate,num_sal*0.5);
13 end if;
14 end loop;
15 commit;
16 close c_emp;
17 end;
18 /

PL/SQL procedure successfully completed.

   4. %Rowcount

SQL> declare
 2 cursor c_emp is select empno,sal,deptno from emp order by sal desc;
 3 str_empno emp.empno%type;
 4   num_sal emp.sal%type;
 5 num_deptno emp.deptno%type;
 6 begin
 7   open c_emp;
 8   loop
 9 fetch c_emp into str_empno, num_sal,num_deptno;
10 exit when c_emp%rowcount=10or c_emp%notfound;
11 dbms_output.put_line(str_empno||' '||num_sal||' '||num_deptno);
12 end loop;
13 commit;
14 close c_emp;
15 end;
16 /
7788 6750 20
7902 6750 20
7566 6693.75 20
7839 5000 10
7698 2850 30
7876 2475 20
7782 2450 10
7369 1800 20
7499 1600 30

PL/SQL procedure successfully completed.
                                     PRACTICAL NO:12

FUNCTIONS AND PROCEDURES
SQL> create table item_master(item_id number(3),description varchar(20),bal_stock number(4));

Table created.

SQL> insert into item_master values(&item_id,'&description',&bal_stock);
Enter value for item_id: 101
Enter value for description: mouse
Enter value for bal_stock: 100
old 1: insert into item_master values(&item_id,'&description',&bal_stock)
new 1: insert into item_master values(101,'mouse',100)

1 row created.

SQL> /
Enter value for item_id: 102
Enter value for description: key board
Enter value for bal_stock: 80
old 1: insert into item_master values(&item_id,'&description',&bal_stock)
new 1: insert into item_master values(102,'key board',80)

1 row created.

SQL> /
Enter value for item_id: 103
Enter value for description: cpu
Enter value for bal_stock: 50
old 1: insert into item_master values(&item_id,'&description',&bal_stock)
new 1: insert into item_master values(103,'cpu',50)

1 row created.

SQL> /
Enter value for item_id: 104
Enter value for description: microphone
Enter value for bal_stock: 75
old 1: insert into item_master values(&item_id,'&description',&bal_stock)
new 1: insert into item_master values(104,'microphone',75)

1 row created.
SQL> select * from item_master;

   ITEN_ID   DESCRIPTION           BAL_STOCK
----------   -------------------- ----------
       101   mouse                       100
       102   key board                    80
       103   cpu                          50
       104   microphone                   75

SQL> create table item_transaction(item_id number(3),description varchar(20),quantity number(4));

Table created.

SQL> insert into item_transaction values(&item_id,'&description',&quantity);
Enter value for item_id: 101
Enter value for description: mouse
Enter value for quantity: 20
old 1: insert into item_transaction values(&item_id,'&description',&quantity)
new 1: insert into item_transaction values(101,'mouse',20)

1 row created.

SQL> /
Enter value for item_id: 102
Enter value for description: key board
Enter value for quantity: 50
old 1: insert into item_transaction values(&item_id,'&description',&quantity)
new 1: insert into item_transaction values(102,'key board',50)

1 row created.

SQL> /
Enter value for item_id: 105
Enter value for description: processor
Enter value for quantity: 60
old 1: insert into item_transaction values(&item_id,'&description',&quantity)
new 1: insert into item_transaction values(105,'processor',60)

1 row created.
SQL> select * from item_transaction;



ITEM_ID DESCRIPTION            QUANTITY
-------- -------------------- ----------
     101 mouse                        20
     102 key board                    50
     105 processor                    60

SQL> create or replace function f_itemidchk(vitemno in number)
 2 return number is
 3 dummyitem number(4);
 4 begin
 5 select item_id into dummyitem from item_master where item_id=vitemno;
 6 return 1;
 7 exception
 8 when no_data_found then
 9 return 0;
10 end;
11 /

Function created.

SQL> set serveroutput on
SQL> declare
 2 cursor scantable is select item_id,quantity,description from item_transaction;
 3 vitemidno item_transaction.item_id%type;
 4 vdescrip item_transaction.description%type;
 5 vquantity item_transaction.quantity%type;
 6   valexists number(1);
 7 begin
 8 open scantable;
 9 loop
10 fetch scantable into vitemidno,vquantity,vdescrip;
11 exit when scantable%notfound;
12 valexists:=f_itemidchk(vitemidno);
13 if valexists=0 then
14 insert into item_master(item_id,description,bal_stock)values(vitemidno,vdescrip,vquantity);
15 elsif valexists=1 then
16 update item_master set bal_stock=bal_stock+vquantity where item_id=vitemidno;
17 end if;
18 end loop;
19 close scantable;
20 commit;
21 end;
22 /
PL/SQL procedure successfully completed.




SQL> select * from item_master;
ITEM_ID DESCRIPTION           BAL_STOCK
------- -------------------- ----------
    101 mouse                       120
    102 key board                   130
    103 cpu                          50
    104 microphone                   75
    105 processor                    60

SQL> create or replace function student_type(stud_code in varchar2)
 2 return varchar2
 3 is
 4 begin
 5 if stud_code='t' then
 6 return 'temp';
 7 elsif stud_code='p' then
 8 return 'permanent';
 9 end if;
10 end;
11 /

Function created.

SQL> declare
 2 str_code varchar2(5):=&str_code;
 3 result varchar2(10);
 4 begin result:=student_type(str_code);
 5 dbms_output.put_line('the student is'||result);
 6 end;
 7 /
Enter value for str_code: 't'
old 2: str_code varchar2(5):=&str_code;
new 2: str_code varchar2(5):='t';
the student istemp

PL/SQL procedure successfully completed.
===============================================================
SQL> create or replace procedure calc_bonus(emp_id in number,bonus out number)is
 2 begin
 3 select sal*0.10 into bonus from emp where empno=emp_id;
 4 end calc_bonus;
 5 /Procedure created
SQL> select * from emp;
DEPTNO EMPNO ENAME           JOB              MGR HIREDATE       SAL    COMM
---------- ----------      --------- ---------- --------- ---------- -------
      7369 SMITH           CLERK           7902 17-DEC-80   10402.09                   20
      7499 ALLEN           SALESMAN        7698 20-FEB-81       2600         300       30
      7521 WARD            SALESMAN        7698 22-FEB-81       2250         500       30
      7566 JONES           MANAGER         7839 02-APR-81    4173.75                   20
      7654 MARTIN          SALESMAN        7698 28-SEP-81       2250       1400        30
      7698 BLAKE           MANAGER         7839 01-MAY-81       3850                   30
      7839 KING            PRESIDENT            17-NOV-81       6300                   20
      7844 TURNER          SALESMAN        7698 08-SEP-81       2500           0       30
      7876 ADAMS           CLERK           7788 23-MAY-87   63514.58                   20
      7900 JAMES           CLERK           7698 03-DEC-81    1617.21                   30
      7902 FORD            ANALYST         7566 03-DEC-81       6300                   20

     EMPNO   ENAME         JOB              MGR HIREDATE         SAL       COMM     DEPTNO
----------   ----------    --------- ---------- --------- ---------- ---------- ----------
      7934   MILLER        CLERK           7782 23-JAN-82    12637.8                    10
      7903   abc           ANALYST         7876                 7500        200

SQL> declare
 2 bon number;
 3 ecode number;
 4 begin
 5 ecode:=&ecode;
 6 calc_bonus(ecode,bon);
 7 dbms_output.put_line('the bonus for the employee ' ||ecode||'is'||bon);
 8 end;
 9 /
Enter value for ecode: 7900
old 5: ecode:=&ecode;
new 5: ecode:=7900;
the bonus for the employee 7900is161.721

PL/SQL procedure successfully completed.
                                       PRACTICAL NO:13
JOINS
SQL> create table customer(c_id number(10) primary key,cname varchar(10),c_address varchar(20));

Table created.

SQL> insert into customer values(&c_id,'&cname','&c_address');
Enter value for c_id: 1
Enter value for cname: nitin
Enter value for c_address: mohali
old 1: insert into customer values(&c_id,'&cname','&c_address')
new 1: insert into customer values(1,'nitin','mohali')

1 row created.

SQL> /
Enter value for c_id: 2
Enter value for cname: ram
Enter value for c_address: chd
old 1: insert into customer values(&c_id,'&cname','&c_address')
new 1: insert into customer values(2,'ram','chd')

1 row created.

SQL> /
Enter value for c_id: 3
Enter value for cname: sham
Enter value for c_address: pck
old 1: insert into customer values(&c_id,'&cname','&c_address')
new 1: insert into customer values(3,'sham','pck')

1 row created.

SQL> 4
SP2-0226: Invalid line number
SQL> /
Enter value for c_id: 4
Enter value for cname: john
Enter value for c_address: delhi
old 1: insert into customer values(&c_id,'&cname','&c_address')
new 1: insert into customer values(4,'john','delhi')

1 row created.
SQL> create table orders(c_id number(10),order_detail varchar(20),o_id number(20) primary key);


Table created.

SQL> insert into orders values(&c_id,'&order_detail',&o_id);
Enter value for c_id: 1
Enter value for order_detail: pc
Enter value for o_id: 1
old 1: insert into orders values(&c_id,'&order_detail',&o_id)
new 1: insert into orders values(1,'pc',1)

1 row created.

SQL> /
Enter value for c_id: 1
Enter value for order_detail: laptop
Enter value for o_id: 2
old 1: insert into orders values(&c_id,'&order_detail',&o_id)
new 1: insert into orders values(1,'laptop',2)

1 row created.

SQL> /
Enter value for c_id: 2
Enter value for order_detail: cable
Enter value for o_id: 3
old 1: insert into orders values(&c_id,'&order_detail',&o_id)
new 1: insert into orders values(2,'cable',3)

1 row created.

SQL> /
Enter value for c_id: 3
Enter value for order_detail: keyboard
Enter value for o_id: 4
old 1: insert into orders values(&c_id,'&order_detail',&o_id)
new 1: insert into orders values(3,'keyboard',4)

1 row created.

SQL> /
Enter value for c_id: 4
Enter value for order_detail: cpu
Enter value for o_id: 5
old 1: insert into orders values(&c_id,'&order_detail',&o_id)
new 1: insert into orders values(4,'cpu',5)

1 row created.

SQL> select * from customer;

     C_ID CNAME             C_ADDRESS
---------- ---------- --------------------
       1nitin       mohali
       2 ram       chd
       3 sham       pck
       4 john      delhi

SQL> select * from orders;

     C_ID ORDER_DETAIL                       O_ID
---------- -------------------- ----------
       1 pc                      1
       1 laptop                    2
       2 cable                     3
       3 keyboard                    4
       4 cpu                      5

SQL> select * from customer left join orders on customer.c_id=orders.c_id;

     C_ID CNAME             C_ADDRESS                      C_ID ORDER_DETAIL                O_ID
---------- ---------- -------------------- ---------- -------------------- ----------
      1 nitin       mohali                                            1 pc              1
      1 nitin       mohali                                            1 laptop          2
      2 ram          chd                                               2 cable          3
      3 sham         pck                                               3 keyboard       4
     4 john          delhi                                             4 cpu            5

SQL> insert into customer values(&c_id,'&cname','&c_address');
Enter value for c_id: 5
Enter value for cname: aman
Enter value for c_address: kharar
old 1: insert into customer values(&c_id,'&cname','&c_address')
new 1: insert into customer values(5,'aman','kharar')

1 row created.

SQL> select * from customer left join orders on customer.c_id=orders.c_id;
C_ID CNAME             C_ADDRESS                      C_ID ORDER_DETAIL                 O_ID
---------- ---------- -------------------- ---------- -------------------- ----------
      1 nitin       mohali                                            1 pc               1
      1 nitin       mohali                                            1 laptop           2
      2 ram          chd                                               2 cable           3
      3 sham         pck                                               3 keyboard        4
     4 john          delhi                                             4 cpu             5
     5 aman          kharar

6 rows selected.

SQL> insert into orders values(&c_id,'&order_detail',&o_id);
Enter value for c_id: 6
Enter value for order_detail: laptop
Enter value for o_id: 6
old 1: insert into orders values(&c_id,'&order_detail',&o_id)
new 1: insert into orders values(6,'laptop',6)

1 row created.

SQL> select * from customer left join orders on customer.c_id=orders.c_id;

     C_ID CNAME             C_ADDRESS                      C_ID ORDER_DETAIL                 O_ID
---------- ---------- -------------------- ---------- -------------------- ----------
      1 nitin       mohali                                            1 pc               1
      1 nitin       mohali                                            1 laptop           2
      2 ram          chd                                               2 cable           3
      3 sham         pck                                               3 keyboard        4
     4 john          delhi                                             4 cpu             5
     5 aman          kharar

6 rows selected.

SQL> select * from customer right join orders on customer.c_id=orders.c_id;

     C_ID CNAME             C_ADDRESS                      C_ID ORDER_DETAIL                 O_ID
---------- ---------- -------------------- ---------- -------------------- ----------
      1 nitin       mohali                                            1 pc               1
      1 nitin       mohali                                            1 laptop           2
      2 ram          chd                                               2 cable           3
      3 sham         pck                                               3 keyboard        4
     4 john          delhi                                             4 cpu             5
                                                     6 laptop                     6

6 rows selected.
SQL> select * from customer full join orders on customer.c_id=orders.c_id;

     C_ID CNAME             C_ADDRESS                      C_ID ORDER_DETAIL                O_ID
---------- ---------- -------------------- ---------- -------------------- ----------
      1 nitin       mohali                                            1 pc              1
      1 nitin       mohali                                            1 laptop          2
      2 ram          chd                                               2 cable          3
      3 sham         pck                                               3 keyboard       4
     4 john          delhi                                             4 cpu            5
     5 aman          kharar
                                                 6 laptop                    6


7 rows selected.
                                                   PRACTICAL NO:14

                                                             VIEWS

SQL> desc emp;
Name                                Null? Type
----------------------------------------- -------- ----------------------------
EMPNO                                 NOT NULL NUMBER(4)
ENAME                                        VARCHAR2(10)
JOB                                       VARCHAR2(9)
MGR                                        NUMBER(4)
HIREDATE                                        DATE
SAL                                       NUMBER(7,2)
COMM                                         NUMBER(7,2)
DEPTNO                                        NUMBER(2)

SQL> set linesize 1500;
SQL> select * from emp;

EMPNO ENAME               JOB      MGR        HIREDATE SAL                        COMM DEPTNO
---------- ----------   --------- ----------     --------- ---------                 ----------- ----------
 7499       ALLEN         CLERK      7902 17-DEC-80            800                              20
 7369       SMITH        SALESMAN 7698 20-FEB-81             1600                  300         30
 7521       WARD          SALESMAN 7698 22-FEB-81             1250                 500         30
 7566       JONES        MANAGER 7839 02-APR-81               2975                             20
 7654      MARTIN         SALESMAN 7698 28-SEP-81            1250                 1400         30
 7698      BLAKE         MANAGER 7839 01-MAY-81                2850                            30
  7782 CLARK             MANAGER 7839 09-JUN-81              2450                             10
  7788 SCOTT             ANALYST      7566 19-APR-87         3000                             20
  7839      KING        PRESIDENT            17-NOV-81        5000                           10
  7844 TURNER           SALESMAN 7698 08-SEP-81              1500                   0        30
  7876 ADAMS              CLERK     7788 23-MAY-87            1100                            20

EMPNO ENAME              JOB              MGR HIREDATE                  SAL        COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7900 JAMES         CLERK              7698 03-DEC-81             950             30
     7902 FORD          ANALYST              7566 03-DEC-81            3000             20
     7934 MILLER CLERK                      7782 23-JAN-82            1300             10

  14 rows selected.

  CREATING UPDATABLE VIEW:
  SQL> create view vw_emp as select empno,ename,job from emp;
View created.

SQL> select * from vw_emp;

    EMPNO ENAME                JOB
---------- ---------- ---------
     7369 SMITH CLERK
     7499 ALLEN           SALESMAN
     7521 WARD            SALESMAN
     7566 JONES          MANAGER
     7654 MARTIN SALESMAN
     7698 BLAKE MANAGER
     7782 CLARK MANAGER
     7788 SCOTT ANALYST
     7839 KING           PRESIDENT
     7844 TURNER SALESMAN
     7876 ADAMS            CLERK

    EMPNO ENAME                JOB
---------- ---------- ---------
     7900 JAMES           CLERK
     7902 FORD           ANALYST
     7934 MILLER CLERK

14 rows selected.


SQL> select * from vw_emp where job='MANAGER';

    EMPNO ENAME                JOB
---------- ---------- ---------
     7566 JONES          MANAGER
     7698 BLAKE MANAGER
     7782 CLARK MANAGER

CREATING NON-UPDATABLE VIEW:
SQL> create view vw_emp1 as select ename,sal,deptno from emp;

View created.

SQL> select * from vw_emp1;

ENAME                SAL DEPTNO
---------- ---------- ----------
SMITH              800         20
ALLEN              1600         30
WARD               1250         30
JONES             2975         20
MARTIN              1250         30
BLAKE              2850         30
CLARK              2450         10
SCOTT             3000         20
KING             5000         10
TURNER              1500         30
ADAMS               1100         20

ENAME                SAL DEPTNO
---------- ---------- ----------
JAMES               950        30
FORD              3000         20
MILLER              1300         10

14 rows selected.

UPDATING IN NON-UPDATABLE VIEW:
SQL> update vw_emp1 set sal=1000 where ename='SMITH';

1 row updated.
     SQL> select * from emp;

    EMPNO ENAME                JOB             MGR HIREDATE                  SAL       COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7369 SMITH CLERK                       7902 17-DEC-80             1000               20
     7499 ALLEN           SALESMAN               7698 20-FEB-81            1600       300       30
     7521 WARD            SALESMAN               7698 22-FEB-81            1250       500       30
     7566 JONES          MANAGER                7839 02-APR-81            2975               20
     7654 MARTIN SALESMAN                         7698 28-SEP-81            1250      1400       30
     7698 BLAKE MANAGER                          7839 01-MAY-81             2850               30
     7782 CLARK MANAGER                          7839 09-JUN-81            2450               10
     7788 SCOTT ANALYST                        7566 19-APR-87            3000               20
     7839 KING           PRESIDENT                17-NOV-81           5000                10
     7844 TURNER SALESMAN                         7698 08-SEP-81            1500        0      30
     7876 ADAMS            CLERK              7788 23-MAY-87             1100               20

    EMPNO ENAME                JOB             MGR HIREDATE                  SAL        COMM    DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7900 JAMES           CLERK             7698 03-DEC-81              950              30
     7902 FORD           ANALYST              7566 03-DEC-81             3000              20
     7934 MILLER CLERK                       7782 23-JAN-82            1300               10

14 rows selected.
INSERTING INTO UPDATABLE VIEW:
SQL> insert into vw_emp values(&empno,'&ename','&job');
Enter value for empno: 7935
Enter value for ename: laura
Enter value for job: manager
old 1: insert into vw_emp values(&empno,'&ename','&job')
new 1: insert into vw_emp values(7935,'laura','manager')

1 row created.

SQL> select * from emp;

    EMPNO ENAME                JOB             MGR HIREDATE                  SAL       COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7369 SMITH CLERK                       7902 17-DEC-80             1000               20
     7499 ALLEN           SALESMAN               7698 20-FEB-81            1600       300       30
     7521 WARD            SALESMAN               7698 22-FEB-81            1250       500       30
     7566 JONES          MANAGER                7839 02-APR-81            2975               20
     7654 MARTIN SALESMAN                         7698 28-SEP-81            1250      1400       30
     7698 BLAKE MANAGER                          7839 01-MAY-81             2850               30
     7782 CLARK MANAGER                          7839 09-JUN-81            2450               10
     7788 SCOTT ANALYST                        7566 19-APR-87            3000               20
     7839 KING           PRESIDENT                17-NOV-81           5000                10
     7844 TURNER SALESMAN                         7698 08-SEP-81            1500        0      30
     7876 ADAMS            CLERK              7788 23-MAY-87             1100               20

    EMPNO ENAME                JOB             MGR HIREDATE                  SAL        COMM    DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7900 JAMES           CLERK             7698 03-DEC-81              950              30
     7902 FORD           ANALYST              7566 03-DEC-81             3000              20
     7934 MILLER CLERK                       7782 23-JAN-82            1300               10
     7935 laura manager

15 rows selected.


SQL> select * from vw_emp;

    EMPNO ENAME                JOB
---------- ---------- ---------
     7369 SMITH CLERK
     7499 ALLEN           SALESMAN
     7521 WARD            SALESMAN
     7566 JONES          MANAGER
     7654 MARTIN SALESMAN
    7698 BLAKE MANAGER
    7782 CLARK MANAGER
    7788 SCOTT ANALYST
    7839 KING  PRESIDENT
    7844 TURNER SALESMAN
    7876 ADAMS  CLERK

    EMPNO ENAME                JOB
---------- ---------- ---------
     7900 JAMES           CLERK
     7902 FORD           ANALYST
     7934 MILLER CLERK
     7935 laura manager

15 rows selected.




UPDATING IN UPDATABLE VIEW:
SQL> update vw_emp set JOB='ANALYST' where EMPNO=7566;

1 row updated.

SQL> select * from emp;

    EMPNO ENAME                JOB             MGR HIREDATE                  SAL       COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7369 SMITH CLERK                       7902 17-DEC-80             1000               20
     7499 ALLEN           SALESMAN               7698 20-FEB-81            1600       300       30
     7521 WARD            SALESMAN               7698 22-FEB-81            1250       500       30
     7566 JONES          ANALYST               7839 02-APR-81            2975               20
     7654 MARTIN SALESMAN                         7698 28-SEP-81            1250      1400       30
     7698 BLAKE MANAGER                          7839 01-MAY-81             2850               30
     7782 CLARK MANAGER                          7839 09-JUN-81            2450              10
     7788 SCOTT ANALYST                        7566 19-APR-87            3000               20
     7839 KING           PRESIDENT                17-NOV-81           5000                10
     7844 TURNER SALESMAN                         7698 08-SEP-81            1500        0      30
     7876 ADAMS            CLERK              7788 23-MAY-87             1100               20

    EMPNO ENAME                JOB             MGR HIREDATE                  SAL        COMM    DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7900 JAMES           CLERK             7698 03-DEC-81              950              30
     7902 FORD           ANALYST              7566 03-DEC-81             3000              20
     7934 MILLER CLERK                       7782 23-JAN-82            1300               10
      7935 laura        manager

  15 rows selected.

  INSERTTING IN NON-UPDATABLE VIEW:
  SQL> insert into vw_emp1 values('&ename',&sal,&deptno);
  Enter value for ename: salman
  Enter value for sal: 500
  Enter value for deptno: 90
  old 1: insert into vw_emp1 values('&ename',&sal,&deptno)
  new 1: insert into vw_emp1 values('salman',500,90)
  insert into vw_emp1 values('salman',500,90)
  *
  ERROR at line 1:
  ORA-01400: cannot insert NULL into ("SCOTT"."EMP"."EMPNO")

DELETING FROM UPDATABLE VIEW:
SQL> delete from vw_emp where empno=7935;

1 row deleted.
SQL> select * from emp;

EMPNO ENAME               JOB         MGR HIREDATE SAL COMM DEPTNO
---------- ----------    ---------   ---------- --------- ---------- ---------- ----------
 7369      SMITH         CLERK        7902 17-DEC-80        1000                 20
 7499 ALLEN             SALESMAN      7698 20-FEB-81        1600      300       30
 7521 WARD              SALESMAN      7698 22-FEB-81        1250      500        30
 7566 JONES             ANALYST       7839 02-APR-81        2975                 20
 7654 MARTIN            SALESMAN     7698 28-SEP-81         1250      1400       30
 7698 BLAKE             MANAGER      7839 01-MAY-81         2850                30
 7782 CLARK             MANAGER      7839 09-JUN-81         2450                 10
 7788 SCOTT             ANALYST      7566 19-APR-87          3000                 20
 7839 KING              PRESIDENT             17-NOV-81     5000                 10
 7844 TURNER            SALESMAN     7698 08-SEP-81         1500        0        30
 7876 ADAMS             CLERK        7788 23-MAY-87          1100                20

 EMPNO ENAME           JOB             MGR HIREDATE          SAL COMM DEPTNO
---------- ---------- ---------      ---------- ---------    ---------- ---------- ----------
  7900 JAMES          CLERK           7698 03-DEC-81         950                      30
  7902 FORD           ANALYST         7566 03-DEC-81         3000                     20
  7934 MILLER CLERK                  7782 23-JAN-82          1300                     10

  14 rows selected.
DELETING FROM NON-UPDATABLE VIEW:
  SQL> delete from vw_emp1 where ename='MILLER';

  1 row deleted.

  SQL> select * from emp;

EMPNO ENAME                 JOB             MGR HIREDATE                  SAL COMM            DEPTNO
  ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
 7369      SMITH          CLERK             7902 17-DEC-80               1000                  20
 7499 ALLEN              SALESMAN 7698 20-FEB-81                         1600         300      30
 7521 WARD                SALESMAN 7698 22-FEB-81                        1250          500     30
 7566 JONES              ANALYST            7839 02-APR-81               2975                  20
 7654 MARTIN SALESMAN 7698 28-SEP-81                                     1250          1400    30
 7698 BLAKE              MANAGER 7839 01-MAY-81 2850                                           30
 7782 CLARK              MANAGER 7839 09-JUN-81                          2450                  10
 7788 SCOTT              ANALYST            7566 19-APR-87                3000                  20
 7839 KING               PRESIDENT                   17-NOV-81           5000                  10
 7844 TURNER SALESMAN 7698 08-SEP-81                                     1500          0       30
 7876 ADAMS CLERK                          7788 23-MAY-87                1100                  20

EMPNO      ENAME          JOB       MGR HIREDATE SAL COMM DEPTNO
-------    ----------    --------- ---------- --------- ---------- ---------- ----------
7900        JAMES        CLERK       7698 03-DEC-81         950                30
7902        FORD         ANALYST 7566 03-DEC-81             3000               20

  13 rows selected.

								
To top