Chapter 03 DBMS TYPES OF OPERATORS ARTHEMATIC OPERATORS

Chapter 03 DBMS

TYPES OF OPERATORS �ARTHEMATIC OPERATORS �LOGICAL OPERATORS �COMPARISON OPERATORS

TYPES OF OPERATORS

AND OPERATOR � SQL> SELECT * FROM EMP WHERE COMM = 300 AND DEPTNO = 30; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO � ---------- ---------� 7499 ssssd SALESMAN 7698 20 -FEB-81 1600 30 �

OR OPERATOR � � 1* SELECT * FROM EMP WHERE COMM = 300 OR DEPTNO = 30 SQL> / � EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------7499 ssssd SALESMAN 7698 20 -FEB-81 1600 30 7521 ssssd SALESMAN 7698 22 -FEB-81 1250 500 30 7654 ssssd SALESMAN 7698 28 -SEP-81 1250 1400 30 7698 ssssd MANAGER 7839 01 -MAY-81 2850 30 7844 ssssd SALESMAN 7698 08 -SEP-81 1500 0 30 7900 ssssd CLERK 7698 03 -DEC-81 200 30 � � � �

NOT OPERATOR � 1* SELECT * FROM EMP WHERE NOT COMM = 300 � SQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO � ---------- --------- -------� 7521 ssssd SALESMAN 7698 22 -FEB-81 1250 500 30 � 7654 ssssd SALESMAN 7698 28 -SEP-81 1250 1400 30 � 7844 ssssd SALESMAN 7698 08 -SEP-81 1500 0 30

TYPES OF COMPARISON OPERATORS � SYMBOLIC OPERATORS <, >, <=, >=, =, != WORD COMPARISON OPERATORS � LIKE � NOT LIKE � IN � NOT IN � BETWEEN � NOT BETWEEN � IS NULL � IS NOT NULL

Character functions �SQL> select initcap('school') from dual; �INITCA �-----�School

Character functions �SQL> select translate('school', 'o', 'm')from dual; �TRANSL �-----�schmml

Character functions � 1* select replace('school', 'o', 'mtf')from dual �SQL> / �REPLACE('S �-----�schmtfmtfl

Character functions �SQL> select substr('school', 2, 3) from dual; �SUB �--�cho

Character functions � � 1* select empno || ' is from ' || deptno from emp SQL> / � � � � � EMPNO||'ISFROM'||DEPTNO --------------------------------------7369 is from 20 7499 is from 30 7521 is from 30 7566 is from 20 7654 is from 30 7698 is from 30 7782 is from 10 � 14 rows selected.

Numeric functions �SQL> select ceil(89. 77), floor(89. 77) from dual; �CEIL(89. 77) FLOOR(89. 77) �------� 90 89

Numeric functions �SQL> select round(89. 66, 1), trunc(89. 66, 1) from dual; �ROUND(89. 66, 1) TRUNC(89. 66, 1) �-------� 89. 7 89. 6

Numeric functions �SQL> select power(2, 3) from dual; �POWER(2, 3) �-----� 8

If then else � � � � � SQL> declare 2 a number : = 10; 3 b number : = 20; 4 c number : = 30; 5 begin 6 if a < b then 7 dbms_output. put_line('a is lesser'); 8 else if a < c then 9 dbms_output. put_line('a is less'); 10 else if b<c then 11 dbms_output. put_line('b is lesser than c'); 12 end if; 13 end if; 14 end if; 15 end; 16 / a is lesser � PL/SQL procedure successfully completed.

Syntax Simple Loop �Loop <Sequence of Statements > End Loop;

Simple Loop � � � Dec lare v_a number : = 0; begin Loop v_a : = v_a+2; dbms_output. put_line(' The value for v_a is'|| v_a); exit when v_a >= 10; � � � � / End Loop; dbms_output. put_line('End of Loop'); End;

Simple Loop Demo � � SQL> @loopdemo Input truncated to 1 characters � PL/SQL procedure successfully completed. � � � � � SQL> set serveroutput on; SQL> @loopdemo Input truncated to 1 characters The value for v_a is 2 The value for v_a is 4 The value for v_a is 6 The value for v_a is 8 The value for v_a is 10 End of Loop � PL/SQL procedure successfully completed.

Syntax While loop �While<condition> Loop <Sequence of Statements > End Loop;

While loop demo � declare � v_a number : = 0; � begin � while(v_a <= 10) � � � Loop dbms_output. put_line(' The value for v_a is'|| v_a); v_a : = v_a+2; � � � End Loop; dbms_output. put_line('End of Loop'); End; �/

While loop �SQL> @loopdemo �Input truncated to 1 characters �The value for v_a is 0 �The value for v_a is 2 �The value for v_a is 4 �The value for v_a is 6 �The value for v_a is 8 �The value for v_a is 10 �End of Loop �PL/SQL procedure successfully completed.

For Loop �declare � v_a number ; �begin � for v_a in 1. . 10 � Loop � dbms_output. put_line(' The value for v_a is'|| v_a); � End Loop; � dbms_output. put_line('End of Loop'); � End; �/

For Loop � � � � SQL> @loopdemo; Input truncated to 1 characters The value for v_a is 1 The value for v_a is 2 The value for v_a is 3 The value for v_a is 4 The value for v_a is 5 The value for v_a is 6 The value for v_a is 7 The value for v_a is 8 The value for v_a is 9 The value for v_a is 10 End of Loop � PL/SQL procedure successfully completed.

Explicit Cursor

Cursor � � � � � DECLARE emp_rec emp%rowtype; CURSOR emp_cur IS SELECT * FROM emp WHERE sal > 1000; BEGIN IF NOT emp_cur%ISOPEN THEN OPEN emp_cur; End if; � � � FETCH emp_cur INTO emp_rec; dbms_output. put_line (emp_rec. ename || ' ' || emp_rec. sal); CLOSE emp_cur; END; /

Output �SQL> @curdemo �ALLEN 1600

� � � � � DECLARE emp_rec emp%rowtype; CURSOR emp_cur IS SELECT * FROM emp WHERE sal > 1000; BEGIN IF NOT emp_cur%ISOPEN THEN OPEN emp_cur; End if; Loop FETCH emp_cur INTO emp_rec; EXIT WHEN emp_cur%NOTFOUND; dbms_output. put_line (emp_rec. ename || ' ' || emp_rec. sal); End Loop; CLOSE emp_cur; END;

Output � � � � SQL> @curdemo; ALLEN 1600 WARD 1250 JONES 2975 MARTIN 1250 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 ADAMS 1100 FORD 3000 MILLER 1300 � PL/SQL procedure successfully completed.

EXCEPTIONS �SYNTAX (GENERAL FORM) Declare =declarations Begin =executable statements Exception = Exception handlers.

Exception �Exception when <Exception name> then <user defined action to be carried on> End;

Example �SQL> select * from account; � ACC_NO CUST_NAME �---------� 1 misha 1000 � 2 minu 8000 � 3 sheeba 4000 BALANCE

Exception � � � � � declare current_balance account. balance%type; Account_no account. acc_no%type; less_bal exception; v_acc number : = &v_acc; v_withdrawal number : = & v_withdrawal; begin Select balance, Acc_no into current_balance, Account_no from account where acc_no = v_acc; � � � � if (current_balance - v_withdrawal <= 500) then raise less_bal; else update account set balance = balance-v_withdrawal where acc_no = v_acc; dbms_output. put_line('withdraw success'); end if; Exception when less_bal then dbms_output. put_line('sorry'); End; /

Exception �SQL> @errdemo �Enter value for v_acc: 1 �old 5: v_acc number : = &v_acc; �new 5: v_acc number : = 1; �Enter value for v_withdrawal: 500 �old 6: v_withdrawal number : = & v_withdrawal; �new 6: v_withdrawal number : = 500; �sorry �PL/SQL procedure successfully completed.

Exception � � � � SQL> / Enter value for v_acc: 2 old 5: v_acc number : = &v_acc; new 5: v_acc number : = 2; Enter value for v_withdrawal: 500 old 6: v_withdrawal number : = & v_withdrawal; new 6: v_withdrawal number : = 500; withdraw success � PL/SQL procedure successfully completed. � � SQL> select * from account 2 ; � � � ACC_NO CUST_NAME BALANCE ----------1 misha 1000 2 minu 7500 3 sheeba 4000

System defined Exception � SQL> set serveroutput on SQL> DECLARE 2 Num_a NUMBER : = 6; 3 Num_b NUMBER; 4 BEGIN 5 Num_b : = 0; 6 Num_a : = Num_a / Num_b; 7 Num_b : = 7; 8 dbms_output. put_line(' Value of Num_b ' || Num_b); 9 EXCEPTION 10 WHEN ZERO_DIVIDE 11 THEN 12 dbms_output. put_line('Trying to divide by zero'); 13 dbms_output. put_line(' Value of Num_a ' || Num_a); 14 dbms_output. put_line(' Value of Num_b ' || Num_b); 15 END; 16 / Trying to divide by zero Value of Num_a 6 Value of Num_b 0 PL/SQL procedure successfully completed.

Views 1 create view_v 1 2 as 3* select empno, sal from emp SQL> / View created. SQL> select * from view_v 1; EMPNO SAL -----7369 200 7499 1600 7521 1250 7566 2975 7654 1250

sequence � � � 1 create sequence s 1 2 increment by 1 3 start with 1 4 maxvalue 10 5 minvalue 1 6 cycle 7* cache 9 SQL> / Sequence created. SQL> insert into stud values(s 1. nextval, 'meena', 90); 1 row created. � SQL> select * from stud; � � � ROLLNO NAME MARKS ----------1 meena 90

indexes � SQL> select rowid from emp; � ROWID � ---------� AAAAe. FAACAAAAEb. AAA � AAAAe. FAACAAAAEb. AAB � AAAAe. FAACAAAAEb. AAC � AAAAe. FAACAAAAEb. AAD � AAAAe. FAACAAAAEb. AAE � AAAAe. FAACAAAAEb. AAF � AAAAe. FAACAAAAEb. AAG � AAAAe. FAACAAAAEb. AAH � AAAAe. FAACAAAAEb. AAI

synonym � SQL> create synonym emp 1 for emp; � Synonym created. � SQL> select * from emp 1; � EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------7369 ssssd CLERK 7902 17 -DEC-80 20 7499 ssssd SALESMAN 7698 20 -FEB-81 1600 30 7521 ssssd SALESMAN 7698 22 -FEB-81 1250 500 30 7566 ssssd MANAGER 7839 02 -APR-81 2975 20 7654 ssssd SALESMAN 7698 28 -SEP-81 1250 1400 30 7698 ssssd MANAGER 7839 01 -MAY-81 2850 30 � � � �