Oracle PL/SQL Tutorial/PL SQL Statements/IF
Содержание
- 1 A Simple Condition Statement
- 2 A Simple Condition Statement with BOOLEAN variable
- 3 Block IF statement
- 4 Comparing with NULL
- 5 Conditional Logic
- 6 Create a function and call it in an if statement
- 7 ELSIF Ladder
- 8 Handling conditions
- 9 If block statement
- 10 IF...ELSE statements
- 11 IF..ELSIF ladder
- 12 IF with ELSE
- 13 JUMP out of a IF statement with goto
- 14 PLW-06002: Unreachable code
- 15 The IF...THEN...ELSE Statement
- 16 The Syntax for IF...ELSIF
- 17 The Syntax for Nested IF Statements
- 18 Three valued comparison
- 19 Use IF THEN ELSE IF
- 20 Use if with "IN"
- 21 Using an ELSIF Statement
A Simple Condition Statement
SQL>
SQL> create or replace function f_isSunday (in_dt DATE)
2 return VARCHAR2
3 is
4 v_out VARCHAR2(10);
5 begin
6 if to_char(in_dt,"d")=1 then
7 v_out:="Y";
8 DBMS_OUTPUT.put_line("IsSunday=Y");
9 end if;
10 return v_out;
11 end;
12 /
Function created.
SQL>
SQL>
A Simple Condition Statement with BOOLEAN variable
SQL>
SQL> create or replace function f_isSunday (in_dt DATE)
2 return VARCHAR2
3 is
4 v_out VARCHAR2(10);
5 v_flag_b BOOLEAN;
6 begin
7 v_flag_b := to_char(in_dt,"d")=1;
8 if v_flag_b then
9 v_out:="Y";
10 DBMS_OUTPUT.put_line("IsSunday=Y");
11 end if;
12 return v_out;
13 end;
14 /
Function created.
Block IF statement
SQL>
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL> DECLARE
2 v_A Number := 50 ;
3 v_B Number := 0 ;
4 BEGIN
5 IF v_A > 40 THEN
6 v_B := v_A - 40;
7 DBMS_OUTPUT.PUT_LINE("V_B = " || v_B);
8 END IF;
9 END;
10 /
V_B = 10
PL/SQL procedure successfully completed.
SQL>
Comparing with NULL
SQL>
SQL>
SQL> declare
2 v NUMBER;
3 begin
4 if v = 1 then
5 DBMS_OUTPUT.put_line("Equal to 1");
6 elsif v!= 1 then
7 DBMS_OUTPUT.put_line("Not equal to 1");
8 elsif v = v then
9 DBMS_OUTPUT.put_line("Equal to itself");
10 else
11 DBMS_OUTPUT.put_line("Undefined result");
12 end if;
13 v:=v+1;
14 DBMS_OUTPUT.put_line("New value: <"||v||">");
15 end;
16 /
Undefined result
New value: <>
PL/SQL procedure successfully completed.
SQL>
SQL>
Conditional Logic
You may use the IF, THEN, ELSE, ELSIF, and END IF keywords in PL/SQL for performing conditional logic.
The following syntax illustrates the use of conditional logic:
IF condition1 THEN
statements1
ELSIF condition2 THEN
statements2
ELSE
statements3
END IF;
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.
ELSIF Ladder
SQL>
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL> DECLARE
2 v_Score Number := 85; --Percentage
3 v_LetterGrade Char(1);
4 BEGIN
5 IF v_Score >= 90 THEN
6 v_LetterGrade := "A";
7 ELSIF v_Score >= 80 THEN
8 v_LetterGrade := "B";
9 ELSIF v_Score >= 70 THEN
10 v_LetterGrade := "C";
11 ELSIF v_Score >= 60 THEN
12 v_LetterGrade := "D";
13 ELSE
14 v_LetterGrade := "E";
15 END IF;
16 DBMS_OUTPUT.PUT_LINE("Your Letter Grade is: " || v_LetterGrade);
17 END;
18 /
Your Letter Grade is: B
PL/SQL procedure successfully completed.
SQL>
SQL>
Handling conditions
Conditions can be connected by using logical operations: AND, OR, and NOT.
The default order of evaluation is standard.
First any parentheses are resolved.
Then operators are executed on the same level in order of precedence: NOT (highest precedence), AND, and OR (lowest precedence).
SQL> declare
2 v_day NUMBER := TO_CHAR(TO_DATE("20060101","YYYYMMDD"),"D");
3 begin
4 if v_day in (1,7) or (v_day not in (1,7) and (v_day between 0 and 6 or v_day between 19 and 23))
5 then
6 DBMS_OUTPUT.put_line(v_day||": Off-peak");
7 else
8 DBMS_OUTPUT.put_line(v_day||": Peak");
9 end if;
10 end;
11 /
1: Off-peak
PL/SQL procedure successfully completed.
SQL>
If block statement
SQL>
SQL>
SQL> create table employee (
2 id number,
3 employee_type_id number,
4 external_id varchar2(30),
5 first_name varchar2(30),
6 middle_name varchar2(30),
7 last_name varchar2(30),
8 name varchar2(100),
9 birth_date date,
10 gender_id number);
Table created.
SQL>
SQL>
SQL>
SQL> create table gender (
2 id number,
3 code varchar2(30),
4 description varchar2(80),
5 active_date date default SYSDATE not null,
6 inactive_date date );
Table created.
SQL>
SQL>
SQL>
SQL> insert into gender ( id, code, description ) values ( 1, "F", "Female" );
1 row created.
SQL> insert into gender ( id, code, description ) values ( 2, "M", "Male" );
1 row created.
SQL> insert into gender ( id, code, description ) values ( 3, "U", "Unknown" );
1 row created.
SQL>
SQL> create table employee_type (
2 id number not null,
3 code varchar2(30) not null,
4 description varchar2(80) not null,
5 active_date date default SYSDATE not null,
6 inactive_date date );
Table created.
SQL>
SQL> insert into employee_type(id,code,description)values(1,"C","Contractor" );
1 row created.
SQL> insert into employee_type(id,code,description)values(2,"E","Employee" );
1 row created.
SQL> insert into employee_type(id,code,description)values(3,"U","Unknown" );
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> set serveroutput on size 1000000;
SQL>
SQL> declare
2 n_id employee.id%TYPE;
3 n_employee_type_id employee.employee_type_id%TYPE;
4 v_external_id employee.external_id%TYPE;
5 v_first_name employee.first_name%TYPE;
6 v_middle_name employee.middle_name%TYPE;
7 v_last_name employee.last_name%TYPE;
8 v_name employee.name%TYPE;
9 d_birth_date employee.birth_date%TYPE;
10 n_gender_id employee.gender_id%TYPE;
11
12 n_inserted number := 0;
13 n_updated number := 0;
14
15 begin
16 v_first_name := "JOHN";
17 v_middle_name := "J.";
18 v_last_name := "DOE";
19 v_name := rtrim(v_last_name||", "||v_first_name||" "||v_middle_name);
20 d_birth_date := to_date("19800101", "YYYYMMDD");
21
22 begin
23 select id into n_employee_type_id from employee_type where code = "C";
24 exception
25 when OTHERS then
26 raise_application_error(-20002, SQLERRM||" on select employee_type");
27 end;
28
29 begin
30 select id into n_gender_id from gender where code = "M";
31 exception
32 when OTHERS then
33 raise_application_error(-20004, SQLERRM||" on select gender");
34 end;
35
36 begin
37 select id into n_id from employee
38 where name = v_name
39 and birth_date = d_birth_date
40 and gender_id = n_gender_id;
41 exception
42 when NO_DATA_FOUND then
43 n_id := NULL;
44 when OTHERS then
45 raise_application_error(-20003, SQLERRM||" on select employee_T");
46 end;
47
48 if n_id is NULL then
49 begin
50 select 12 into n_id from SYS.DUAL;
51 exception
52 when OTHERS then
53 raise_application_error(-20004, SQLERRM||" on select 12");
54 end;
55
56 begin
57 select lpad(to_char(12), 9, "0") into v_external_id from SYS.DUAL;
58 exception
59 when OTHERS then
60 raise_application_error(-20005, SQLERRM||" on select 12");
61 end;
62
63 begin
64 insert into employee (
65 id,
66 employee_type_id,
67 external_id,
68 first_name,
69 middle_name,
70 last_name,
71 name,
72 birth_date,
73 gender_id )
74 values (
75 n_id,
76 n_employee_type_id,
77 v_external_id,
78 v_first_name,
79 v_middle_name,
80 v_last_name,
81 v_name,
82 d_birth_date,
83 n_gender_id );
84
85 n_inserted := sql%rowcount;
86 exception
87 when OTHERS then
88 raise_application_error(-20006, SQLERRM||" on insert employee");
89 end;
90 else
91 begin
92 update employee
93 set employee_type_id = n_employee_type_id
94 where id = n_id;
95
96 n_updated := sql%rowcount;
97 exception
98 when OTHERS then
99 raise_application_error(-20007, SQLERRM||" on update employee");
100 end;
101 end if;
102
103 DBMS_OUTPUT.PUT_LINE(to_char(n_inserted)||" row(s) inserted.");
104 DBMS_OUTPUT.PUT_LINE(to_char(n_updated)||" row(s) updated.");
105 end;
106 /
1 row(s) inserted.
0 row(s) updated.
PL/SQL procedure successfully completed.
SQL>
SQL> drop table gender;
Table dropped.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL> drop table employee_type;
Table dropped.
SQL>
IF...ELSE statements
Using ELSE in a Condition Statement
IF <condition> then
...<<set of statements>>...
else
...<<set of statements>>...
end if;
IF..ELSIF ladder
if <condition> then
...<<set of statements>>...
elsif <condition> then
...<<set of statements>>...
elsif <condition> then
...<<set of statements>>...
else
...<<set of statements>>...
end if;
IF with ELSE
SQL>
SQL> set serveroutput on
SQL> set echo on
SQL> DECLARE
2 v_A Number := 50;
3 v_B Number;
4 BEGIN
5 IF v_A > 40 THEN
6 v_B := v_A - 40;
7 DBMS_OUTPUT.PUT_LINE("V_B = " || v_B);
8 ELSE
9 v_B := 0;
10 END IF;
11 END;
12 /
V_B = 10
PL/SQL procedure successfully completed.
JUMP out of a IF statement with goto
SQL>
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL> DECLARE
2 v_Status NUMBER := 1;
3 BEGIN
4 IF v_Status = 1 THEN
5 GOTO mybranch;
6 ELSE
7 v_Status := 1;
8 END IF;
9 <<mybranch>>
10 NULL;
11 END;
12 /
PL/SQL procedure successfully completed.
SQL>
PLW-06002: Unreachable code
SQL>
SQL> ALTER SESSION SET plsql_warnings = "enable:all"
2 /
Session altered.
SQL>
SQL> DROP FUNCTION plw6002;
Function dropped.
SQL>
SQL> CREATE OR REPLACE PROCEDURE plw6002
2 AS
3 l_checking BOOLEAN := FALSE;
4 BEGIN
5 NULL;
6 IF l_checking
7 THEN
8 DBMS_OUTPUT.put_line ("Never here...");
9 ELSE
10 DBMS_OUTPUT.put_line ("Always here...");
11 GOTO end_of_function;
12 END IF;
13 <<end_of_function>>
14 NULL;
15 END plw6002;
16 /
SP2-0804: Procedure created with compilation warnings
SQL>
SQL> SHOW ERRORS PROCEDURE plw6002
Errors for PROCEDURE PLW6002:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/4 PLW-06002: Unreachable code
6/7 PLW-06002: Unreachable code
8/7 PLW-06002: Unreachable code
14/4 PLW-06002: Unreachable code
SQL>
SQL>
The IF...THEN...ELSE Statement
The IF...THEN...ELSE statement allows you to process a series of statements under ELSE if the condition is false.
The Syntax for the IF...THEN...ELSE Statement
IF <some_condition_evaluates_to_true>
THEN
<perform_statements_condition_true>
ELSE
<perform_statements_condition_false>
END IF;
The Syntax for IF...ELSIF
IF <condition1_evaluates_to_true>
THEN
<perform_statements>
ELSIF <condition2_evaluates_to_true>
THEN
<perform_statements>
ELSIF <condition3_evaluates_to_true>
THEN
<perform_statements>
ELSE <this is always optional as the default value>
<perform_statements>
END IF;
The Syntax for Nested IF Statements
IF <condition1 evaluates to true>
THEN
IF <condition2 evaluates to true>
THEN
<perform statements>
ELSE <both conditions have been evaluated to false>
IF <condition3 evaluates to true>
THEN
<perform statements>
ELSE
<perform statements>
END IF;
END IF;
END IF;
Three valued comparison
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 a INTEGER;
3 b BOOLEAN;
4 n INTEGER; --this will be our null value.
5 BEGIN
6 a := 2;
7
8 b := (a <> n);
9 IF b THEN
10 DBMS_OUTPUT.PUT_LINE("a <> n is TRUE");
11 ELSIF NOT b THEN
12 DBMS_OUTPUT.PUT_LINE("a <> n is FALSE");
13 ELSE
14 DBMS_OUTPUT.PUT_LINE("a <> n is NULL");
15 END IF;
16 END;
17 /
a <> n is NULL
PL/SQL procedure successfully completed.
SQL>
Use IF THEN ELSE IF
SQL>
SQL> SET SERVEROUTPUT ON
SQL> SET ECHO ON
SQL> DECLARE
2 employee_name_c CHAR(32);
3 employee_name_v VARCHAR2(32);
4 BEGIN
5 --Assign the same value to each string.
6 employee_name_c := "CHAR";
7 employee_name_v := "VARCHAR";
8
9 --Test the strings for equality.
10 IF employee_name_c = employee_name_v THEN
11 DBMS_OUTPUT.PUT_LINE("The names are the same");
12 ELSE
13 DBMS_OUTPUT.PUT_LINE("The names are NOT the same");
14 END IF;
15 END;
16 /
The names are NOT the same
PL/SQL procedure successfully completed.
SQL>
SQL>
Use if with "IN"
SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 test_date DATE;
3 day_of_week VARCHAR2(3);
4 years_ahead INTEGER;
5 BEGIN
6 test_date := TO_DATE("1-Jan-1997","dd-mon-yyyy");
7
8 FOR years_ahead IN 1..10 LOOP
9 day_of_week := TO_CHAR(test_date,"Dy");
10
11 IF day_of_week IN ("Sat","Sun") THEN
12 DBMS_OUTPUT.PUT_LINE(TO_CHAR(test_date,"dd-Mon-yyyy")|| " A long weekend!");
13 ELSE
14 DBMS_OUTPUT.PUT_LINE(TO_CHAR(test_date,"dd-Mon-yyyy")|| " Not a long weekend.");
15 END IF;
16 test_date := ADD_MONTHS(test_date,12);
17 END LOOP;
18 END;
19 /
01-Jan-1997 Not a long weekend.
01-Jan-1998 Not a long weekend.
01-Jan-1999 Not a long weekend.
01-Jan-2000 A long weekend!
01-Jan-2001 Not a long weekend.
01-Jan-2002 Not a long weekend.
01-Jan-2003 Not a long weekend.
01-Jan-2004 Not a long weekend.
01-Jan-2005 A long weekend!
01-Jan-2006 A long weekend!
PL/SQL procedure successfully completed.
SQL>
SQL>
Using an ELSIF Statement
SQL>
SQL>
SQL> create or replace function f_getDateType (in_dt DATE)
2 return VARCHAR2
3 is
4 v_out VARCHAR2(10);
5 begin
6 if to_char(in_dt,"MMDD") in ("0101","0704") then
7 v_out:="HOLIDAY";
8 elsif to_char(in_dt,"d") = 1 then
9 v_out:="SUNDAY";
10 elsif to_char(in_dt,"d") = 7 then
11 v_out:="SATURDAY";
12 else
13 v_out:="WEEKDAY";
14 end if;
15 return v_out;
16 end;
17 /
Function created.