Oracle PL/SQL/PL SQL/IF
Содержание
- 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
<source lang="sql">
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>
</source>
Adding ELSE to the IF block
<source lang="sql">
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>
</source>
An if-then-elsif-then-else statement where the first two comparisons are true and the third false
<source lang="sql">
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.
</source>
Check number value in if statement
<source lang="sql">
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> --
</source>
Compare three variables with if statement
<source lang="sql">
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.
</source>
Create a function and call it in an if statement
<source lang="sql">
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.
</source>
Exit a loop with condition
<source lang="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> SQL> SQL> --
</source>
If condition meets, throw exception
<source lang="sql">
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.
</source>
If count() is 0, insert data
<source lang="sql">
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>
</source>
IF, ELSIF ELSE and END IF
<source lang="sql">
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>
</source>
IF...ELSIF...ELSE... END IF
<source lang="sql">
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>
</source>
If ladder
<source lang="sql">
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>
</source>
IF THEN and END IF
<source lang="sql">
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.
</source>
If...then...Else
<source lang="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 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.
</source>
Nested if statement
<source lang="sql">
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.
</source>
The IF statement contains more than one statement per condition.
<source lang="sql">
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>
</source>
Using a Boolean variable instead of the comparison operation
<source lang="sql">
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.
</source>
Using IF...ELSIF to determine a grade
<source lang="sql">
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>
</source>
Using nested IF statements
<source lang="sql">
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.
</source>