Oracle PL/SQL/PL SQL/IF — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 09:59, 26 мая 2010
Содержание
- 1 A conditional statement
- 2 Adding ELSE to the IF block
- 3 An if-then-elsif-then-else statement where the first two comparisons are true and the third false
- 4 Check number value in if statement
- 5 Compare three variables with if statement
- 6 Create a function and call it in an if statement
- 7 Exit a loop with condition
- 8 If condition meets, throw exception
- 9 If count() is 0, insert data
- 10 IF, ELSIF ELSE and END IF
- 11 IF...ELSIF...ELSE... END IF
- 12 If ladder
- 13 IF THEN and END IF
- 14 If...then...Else
- 15 Nested if statement
- 16 The IF statement contains more than one statement per condition.
- 17 Using a Boolean variable instead of the comparison operation
- 18 Using IF...ELSIF to determine a grade
- 19 Using nested IF statements
A conditional statement
SQL>
SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL>
SQL> DECLARE
2 v_Totallecturer NUMBER;
3 BEGIN
4 SELECT COUNT(*)
5 INTO v_Totallecturer
6 FROM lecturer;
7
8 IF v_Totallecturer = 0 THEN
9 INSERT INTO MyTable (char_col) VALUES ("There are no lecturers registered");
10 ELSIF v_Totallecturer < 5 THEN
11 INSERT INTO MyTable (char_col) VALUES ("There are only a few lecturers registered");
12 ELSIF v_Totallecturer < 10 THEN
13 INSERT INTO MyTable (char_col) VALUES ("There are a little more lecturers registered");
14 ELSE
15 INSERT INTO MyTable (char_col) VALUES ("There are many lecturers registered");
16 END IF;
17 END;
18 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from MyTable;
NUM_COL CHAR_COL
-------- ------------------------------------------------------------
There are many lecturers registered
SQL>
SQL> drop table MyTable;
Table dropped.
SQL> drop table lecturer;
Table dropped.
SQL>
Adding ELSE to the IF block
SQL>
SQL> -- Adding ELSE to the IF block.
SQL> set echo on
SQL> DECLARE
2 v_HoursWorked Number := 50 ;
3 v_OverTime Number ;
4 BEGIN
5 IF v_HoursWorked > 40 THEN
6 v_OverTime := v_HoursWorked - 40;
7 DBMS_OUTPUT.PUT_LINE("Hours overtime worked = " || v_OverTime);
8 ELSE
9 v_OverTime := 0;
10 END IF;
11 END;
12 /
Hours overtime worked = 10
PL/SQL procedure successfully completed.
SQL>
An if-then-elsif-then-else statement where the first two comparisons are true and the third false
SQL>
SQL> DECLARE
2 equal BOOLEAN NOT NULL := TRUE;
3 BEGIN
4 IF 1 = 1 THEN
5 dbms_output.put_line("Condition one met!");
6 ELSIF equal THEN
7 dbms_output.put_line("Condition two met!");
8 ELSIF 1 = 2 THEN
9 dbms_output.put_line("Condition three met!");
10 END IF;
11 END;
12 /
Condition one met!
PL/SQL procedure successfully completed.
Check number value in if statement
SQL>
SQL>
SQL> SET ECHO ON
SQL>
SQL> DECLARE
2 v_MyNumber NUMBER := 0;
3 BEGIN
4 LOOP
5 IF v_MyNumber = 7 THEN
6 EXIT;
7 END IF;
8 v_MyNumber := v_MyNumber + 1;
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> --
Compare three variables with if statement
SQL> CREATE TABLE book (
2 isbn CHAR(10) PRIMARY KEY,
3 category VARCHAR2(20),
4 title VARCHAR2(100),
5 num_pages NUMBER,
6 price NUMBER,
7 copyright NUMBER(4),
8 emp1 NUMBER,
9 emp2 NUMBER,
10 emp3 NUMBER
11 );
Table created.
SQL>
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("1", "Database", "Oracle", 563, 39.99, 1999, 1, 2, 3);
1 row created.
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2)
2 VALUES ("2", "Database", "MySQL", 765, 44.99, 1999, 4, 5);
1 row created.
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("3", "Database", "SQL Server", 404, 39.99, 2001, 6, 7, 8);
1 row created.
SQL>
SQL> DECLARE
2 e_Duplicateemp EXCEPTION;
3
4 v_emp1 book.emp1%TYPE;
5 v_emp2 book.emp2%TYPE;
6 v_emp3 book.emp3%TYPE;
7 BEGIN
8 SELECT emp1, emp2, emp3
9 INTO v_emp1, v_emp2, v_emp3
10 FROM book
11 WHERE title = "XML";
12
13 IF (v_emp1 = v_emp2) OR (v_emp1 = v_emp3) OR(v_emp2 = v_emp3) THEN
14 RAISE e_Duplicateemp;
15 END IF;
16 EXCEPTION
17 WHEN e_Duplicateemp THEN
18 INSERT INTO log_table (info) VALUES ("XML has duplicate emp");
19 END;
20 /
SQL>
SQL> drop table book;
Table dropped.
Create a function and call it in an if statement
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY,
3 fname VARCHAR2(50),
4 lname VARCHAR2(50)
5 );
Table created.
SQL>
SQL> CREATE TABLE books (
2 isbn CHAR(10) PRIMARY KEY,
3 category VARCHAR2(20),
4 title VARCHAR2(100),
5 num_pages NUMBER,
6 price NUMBER,
7 copyright NUMBER(4),
8 emp1 NUMBER,
9 emp2 NUMBER,
10 emp3 NUMBER
11 );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION Threeemp(p_ISBN IN books.isbn%TYPE)
2 RETURN BOOLEAN AS
3
4 v_emp3 books.emp3%TYPE;
5 BEGIN
6 SELECT emp3 INTO v_emp3 FROM books WHERE isbn = p_ISBN;
7
8 IF v_emp3 IS NULL THEN
9 RETURN FALSE;
10 ELSE
11 RETURN TRUE;
12 END IF;
13 END Threeemp;
14 /
Function created.
SQL>
SQL> set serveroutput on
SQL>
SQL> BEGIN
2 FOR v_Rec IN (SELECT ISBN, title FROM books) LOOP
3 IF Threeemp(v_Rec.ISBN) THEN
4 DBMS_OUTPUT.PUT_LINE(""" || v_Rec.title || "" has 3 emp");
5 END IF;
6 END LOOP;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> drop table books;
Table dropped.
SQL> drop table emp;
Table dropped.
Exit a loop with condition
SQL>
SQL> SET ECHO ON
SQL>
SQL> DECLARE
2 v_MyNumber NUMBER := 0;
3 BEGIN
4 LOOP
5 IF v_MyNumber > 7 THEN
6 EXIT;
7 ENd if;
8 v_MyNumber := v_MyNumber + 1;
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> --
If condition meets, throw exception
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
SQL>
SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> create or replace procedure sal_check( p_deptno in number)is
2 avg_sal number;
3 max_sal number;
4 begin
5 select avg(sal), max(sal)
6 into avg_sal, max_sal
7 from emp
8 where deptno = p_deptno;
9 if ( max_sal/2 > avg_sal)then
10 raise_application_error(-20001,"Rule violated");
11 end if;
12 end;
13 /
Procedure created.
SQL>
SQL> drop table emp;
Table dropped.
If count() is 0, insert data
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY,
3 fname VARCHAR2(50),
4 lname VARCHAR2(50)
5 );
Table created.
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z");
1 row created.
SQL>
SQL>
SQL> DECLARE
2
3 v_count PLS_INTEGER := 0;
4
5 BEGIN
6 SELECT COUNT(1) INTO v_count FROM emp WHERE id = 55;
7
8 IF v_count = 0
9 THEN
10 INSERT INTO emp VALUES (54, "R", "W");
11 DBMS_OUTPUT.PUT_LINE("Added emp");
12 ELSE
13 DBMS_OUTPUT.PUT_LINE("emp already exists");
14 END IF;
15
16 COMMIT;
17
18 EXCEPTION
19 WHEN OTHERS
20 THEN
21 DBMS_OUTPUT.PUT_LINE(SQLERRM);
22 END;
23 /
Added emp
PL/SQL procedure successfully completed.
SQL> drop table emp;
Table dropped.
SQL>
IF, ELSIF ELSE and END IF
SQL>
SQL> -- The emptype function.
SQL> CREATE OR REPLACE FUNCTION emptype (paytype CHAR)
2 RETURN VARCHAR2 IS
3 BEGIN
4 IF paytype = "H" THEN
5 RETURN "Hourly";
6 ELSIF paytype = "S" THEN
7 RETURN "Salaried";
8 ELSIF paytype = "E" THEN
9 RETURN "Executive";
10 ELSE
11 RETURN "Invalid Type";
12 END IF;
13 EXCEPTION
14 WHEN OTHERS THEN
15 RETURN "Error Encountered";
16 END emptype;
17 /
Function created.
SQL>
SQL> select emptype("H") from dual;
EMPTYPE("H")
---------------------------------------------------------------------
Hourly
SQL>
SQL> select emptype("S") from dual;
EMPTYPE("S")
---------------------------------------------------------------------
Salaried
SQL>
SQL> select emptype("E") from dual;
EMPTYPE("E")
---------------------------------------------------------------------
Executive
SQL>
IF...ELSIF...ELSE... END IF
SQL>
SQL> CREATE TABLE place (
2 room_id NUMBER(5) PRIMARY KEY,
3 building VARCHAR2(15),
4 room_number NUMBER(4),
5 number_seats NUMBER(4),
6 description VARCHAR2(50)
7 );
Table created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20001, "Building 7", 201, 1000, "Large Lecture Hall");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20002, "Building 6", 101, 500, "Small Lecture Hall");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20003, "Building 6", 150, 50, "Discussion Room A");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20004, "Building 6", 160, 50, "Discussion Room B");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20005, "Building 6", 170, 50, "Discussion Room C");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20006, "Music Building", 100, 10, "Music Practice Room");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20007, "Music Building", 200, 1000, "Concert Room");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20008, "Building 7", 300, 75, "Discussion Room D");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20009, "Building 7", 310, 50, "Discussion Room E");
1 row created.
SQL>
SQL>
SQL> select * from place;
ROOM_ID BUILDING ROOM_NUMBER NUMBER_SEATS DESCRIPTION
-------- --------------- ----------- ------------ --------------------------------------------------
######## Building 7 201.00 1000.00 Large Lecture Hall
######## Building 6 101.00 500.00 Small Lecture Hall
######## Building 6 150.00 50.00 Discussion Room A
######## Building 6 160.00 50.00 Discussion Room B
######## Building 6 170.00 50.00 Discussion Room C
######## Music Building 100.00 10.00 Music Practice Room
######## Music Building 200.00 1000.00 Concert Room
######## Building 7 300.00 75.00 Discussion Room D
######## Building 7 310.00 50.00 Discussion Room E
9 rows selected.
SQL>
SQL> DECLARE
2 v_NumberSeats place.number_seats%TYPE;
3 v_Comment VARCHAR2(35);
4 BEGIN
5 SELECT number_seats
6 INTO v_NumberSeats
7 FROM place
8 WHERE room_id = 20008;
9 IF v_NumberSeats < 50 THEN
10 v_Comment := "Fairly small";
11 ELSIF v_NumberSeats < 100 THEN
12 v_Comment := "A little bigger";
13 ELSE
14 v_Comment := "Lots of room";
15 END IF;
16 END;
17 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from place;
ROOM_ID BUILDING ROOM_NUMBER NUMBER_SEATS DESCRIPTION
-------- --------------- ----------- ------------ --------------------------------------------------
######## Building 7 201.00 1000.00 Large Lecture Hall
######## Building 6 101.00 500.00 Small Lecture Hall
######## Building 6 150.00 50.00 Discussion Room A
######## Building 6 160.00 50.00 Discussion Room B
######## Building 6 170.00 50.00 Discussion Room C
######## Music Building 100.00 10.00 Music Practice Room
######## Music Building 200.00 1000.00 Concert Room
######## Building 7 300.00 75.00 Discussion Room D
######## Building 7 310.00 50.00 Discussion Room E
9 rows selected.
SQL>
SQL>
SQL> drop table place;
Table dropped.
SQL>
If ladder
SQL>
SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL>
SQL> select * from lecturer;
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
-------- -------------------- -------------------- ------------------------------ ---------------
######## Scott Lawson Computer Science 11.00
######## Mar Wells History 4.00
######## Jone Bliss Computer Science 8.00
######## Man Kyte Economics 8.00
######## Pat Poll History 4.00
######## Tim Viper History 4.00
######## Barbara Blues Economics 7.00
######## David Large Music 4.00
######## Chris Elegant Nutrition 8.00
######## Rose Bond Music 7.00
######## Rita Johnson Nutrition 8.00
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
-------- -------------------- -------------------- ------------------------------ ---------------
######## Sharon Clear Computer Science 3.00
12 rows selected.
SQL>
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 v_Major lecturer.major%TYPE;
3 v_CourseName VARCHAR2(10);
4 BEGIN
5 SELECT major
6 INTO v_Major
7 FROM lecturer
8 WHERE ID = 10011;
9
10 IF v_Major = "Computer Science" THEN
11 v_CourseName := "CS 101";
12 ELSIF v_Major = "Economics" THEN
13 v_CourseName := "ECN 203";
14 ELSIF v_Major = "History" THEN
15 v_CourseName := "HIS 101";
16 ELSIF v_Major = "Music" THEN
17 v_CourseName := "MUS 100";
18 ELSIF v_Major = "Nutrition" THEN
19 v_CourseName := "NUT 307";
20 ELSE
21 v_CourseName := "Unknown";
22 END IF;
23 DBMS_OUTPUT.PUT_LINE(v_CourseName);
24 END;
25 /
NUT 307
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
IF THEN and END IF
SQL>
SQL> -- Calculating overtime hours with IF.
SQL> set echo on
SQL> DECLARE
2 v_HoursWorked Number := 50 ;
3 v_OverTime Number := 0 ;
4 BEGIN
5 IF v_HoursWorked > 40 THEN
6 v_OverTime := v_HoursWorked - 40;
7 DBMS_OUTPUT.PUT_LINE("Hours overtime worked = " || v_OverTime);
8 END IF;
9 END;
10 /
Hours overtime worked = 10
PL/SQL procedure successfully completed.
If...then...Else
SQL> -- create demo table
SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 fname VARCHAR2(10 BYTE),
4 lname VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL>
SQL>
SQL> -- prepare data
SQL> insert into emp(ID, fname, lname, Start_Date, End_Date
, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMM
DD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into emp(ID, fname, lname, Start_Date, End_Date
, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMM
DD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL>
SQL>
SQL> declare
2 dateValue date;
3 nameValue VARCHAR2(10 BYTE);
4 s Number(8,2);
5 begin
6 select start_date into dateValue from emp where rownum = 1;
7 select salary into s from emp where rownum = 1;
8 IF dateValue > "11-APR-63" then
9 s := s * 1.15; -- Increase salary by 15%
10 ELSE
11 s := s * 1.05; -- Increase salary by 5%
12 END IF;
13
14 dbms_output.put_line(s);
15 end;
16 /
1419.74
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
Nested if statement
SQL>
SQL> -- create demo table
SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 fname VARCHAR2(10 BYTE),
4 lname VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL>
SQL>
SQL> -- prepare data
SQL> insert into emp(ID, fname, lname, Start_Date, End_Date
, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMM
DD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into emp(ID, fname, lname, Start_Date, End_Date
, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMM
DD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL>
SQL>
SQL> declare
2 dateValue date;
3 nameValue VARCHAR2(10 BYTE);
4 s Number(8,2);
5 begin
6 select start_date into dateValue from emp where rownum = 1;
7 select salary into s from emp where rownum = 1;
8 select lname into nameValue from emp where rownum = 1;
9 IF dateValue > "11-APR-63" then
10 If nameValue = "PAKMAN" then
11 s := s * 1.15;
12 ELSE
13 s := s * 1.10;
14 END IF;
15 ELSE
16 s := s * 1.05;
17 END IF;
18
19 dbms_output.put_line(s);
20 end;
21 /
1358.02
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
The IF statement contains more than one statement per condition.
SQL>
SQL> CREATE TABLE place (
2 room_id NUMBER(5) PRIMARY KEY,
3 building VARCHAR2(15),
4 room_number NUMBER(4),
5 number_seats NUMBER(4),
6 description VARCHAR2(50)
7 );
Table created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20001, "Building 7", 201, 1000, "Large Lecture Hall");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20002, "Building 6", 101, 500, "Small Lecture Hall");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20003, "Building 6", 150, 50, "Discussion Room A");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20004, "Building 6", 160, 50, "Discussion Room B");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20005, "Building 6", 170, 50, "Discussion Room C");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20006, "Music Building", 100, 10, "Music Practice Room");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20007, "Music Building", 200, 1000, "Concert Room");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20008, "Building 7", 300, 75, "Discussion Room D");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20009, "Building 7", 310, 50, "Discussion Room E");
1 row created.
SQL>
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL> select * from place;
ROOM_ID BUILDING ROOM_NUMBER NUMBER_SEATS DESCRIPTION
-------- --------------- ----------- ------------ --------------------------------------------------
######## Building 7 201.00 1000.00 Large Lecture Hall
######## Building 6 101.00 500.00 Small Lecture Hall
######## Building 6 150.00 50.00 Discussion Room A
######## Building 6 160.00 50.00 Discussion Room B
######## Building 6 170.00 50.00 Discussion Room C
######## Music Building 100.00 10.00 Music Practice Room
######## Music Building 200.00 1000.00 Concert Room
######## Building 7 300.00 75.00 Discussion Room D
######## Building 7 310.00 50.00 Discussion Room E
9 rows selected.
SQL>
SQL> DECLARE
2 v_NumberSeats place.number_seats%TYPE;
3 v_Comment VARCHAR2(35);
4 BEGIN
5 SELECT number_seats
6 INTO v_NumberSeats
7 FROM place
8 WHERE room_id = 20008;
9 IF v_NumberSeats < 50 THEN
10 v_Comment := "Fairly small";
11 INSERT INTO MyTable (char_col) VALUES ("Nice and cozy");
12 ELSIF v_NumberSeats < 100 THEN
13 v_Comment := "A little bigger";
14 INSERT INTO MyTable (char_col) VALUES ("Some breathing room");
15 ELSE
16 v_Comment := "Lots of room";
17 END IF;
18 END;
19 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from mytable;
NUM_COL CHAR_COL
-------- ------------------------------------------------------------
Some breathing room
SQL>
SQL> select * from place;
ROOM_ID BUILDING ROOM_NUMBER NUMBER_SEATS DESCRIPTION
-------- --------------- ----------- ------------ --------------------------------------------------
######## Building 7 201.00 1000.00 Large Lecture Hall
######## Building 6 101.00 500.00 Small Lecture Hall
######## Building 6 150.00 50.00 Discussion Room A
######## Building 6 160.00 50.00 Discussion Room B
######## Building 6 170.00 50.00 Discussion Room C
######## Music Building 100.00 10.00 Music Practice Room
######## Music Building 200.00 1000.00 Concert Room
######## Building 7 300.00 75.00 Discussion Room D
######## Building 7 310.00 50.00 Discussion Room E
9 rows selected.
SQL>
SQL> drop table mytable;
Table dropped.
SQL>
SQL> drop table place;
Table dropped.
SQL>
Using a Boolean variable instead of the comparison operation
SQL>
SQL> DECLARE
2 equal BOOLEAN NOT NULL := TRUE;
3 BEGIN
4 IF equal THEN
5 dbms_output.put_line("Condition met!");
6 END IF;
7 END;
8 /
Condition met!
PL/SQL procedure successfully completed.
Using IF...ELSIF to determine a grade
SQL>
SQL> -- Using IF...ELSIF to determine a grade.
SQL> DECLARE
2 variable_Score Number := 85;
3 variable_LetterGrade Char(1);
4 BEGIN
5 IF variable_Score >= 90 THEN
6 variable_LetterGrade := "A";
7 ELSIF variable_Score >= 80 THEN
8 variable_LetterGrade := "B";
9 ELSIF variable_Score >= 70 THEN
10 variable_LetterGrade := "C";
11 ELSIF variable_Score >= 60 THEN
12 variable_LetterGrade := "D";
13 ELSE
14 variable_LetterGrade := "E";
15 END IF;
16 DBMS_OUTPUT.PUT_LINE("Your Letter Grade is: " || variable_LetterGrade);
17 END;
18 /
Your Letter Grade is: B
PL/SQL procedure successfully completed.
SQL>
SQL>
Using nested IF statements
SQL>
SQL>
SQL> -- Using nested IF statements.
SQL> DECLARE
2 v_HoursWorked Number := 80 ;
3 v_OverTime Number := 0 ;
4 v_PayType char(1) := "E";
5
6 BEGIN
7 IF v_HoursWorked > 40 THEN
8 IF v_PayType = "H" THEN
9 v_OverTime := v_HoursWorked - 40;
10 DBMS_OUTPUT.PUT_LINE("Hours overtime worked = " || v_OverTime);
11 ELSE
12 IF v_PayType = "S" THEN
13 DBMS_OUTPUT.PUT_LINE("Employee is Salaried");
14 ELSE
15 DBMS_OUTPUT.PUT_LINE("Employee is Executive Management");
16 END IF;
17 END IF;
18 END IF;
19 END;
20 /
Employee is Executive Management
PL/SQL procedure successfully completed.