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
<source lang="sql">
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></source>
A Simple Condition Statement with BOOLEAN variable
<source lang="sql">
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.</source>
Block IF statement
<source lang="sql">
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></source>
Comparing with NULL
<source lang="sql">
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></source>
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:
<source lang="sql">
IF condition1 THEN
statements1
ELSIF condition2 THEN
statements2
ELSE
statements3
END IF;</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>
ELSIF Ladder
<source lang="sql">
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></source>
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).
<source lang="sql">
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></source>
If block statement
<source lang="sql">
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></source>
IF...ELSE statements
Using ELSE in a Condition Statement
<source lang="sql">
IF <condition> then
...<<set of statements>>...
else
...<<set of statements>>...
end if;</source>
IF..ELSIF ladder
<source lang="sql">
if <condition> then
...<<set of statements>>...
elsif <condition> then
...<<set of statements>>...
elsif <condition> then
...<<set of statements>>...
else
...<<set of statements>>...
end if;</source>
IF with ELSE
<source lang="sql">
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.</source>
JUMP out of a IF statement with goto
<source lang="sql">
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></source>
PLW-06002: Unreachable code
<source lang="sql">
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></source>
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
<source lang="sql">
IF <some_condition_evaluates_to_true> THEN
<perform_statements_condition_true>
ELSE
<perform_statements_condition_false>
END IF;</source>
The Syntax for IF...ELSIF
<source lang="sql">
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;</source>
The Syntax for Nested IF Statements
<source lang="sql">
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;</source>
Three valued comparison
<source lang="sql">
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></source>
Use IF THEN ELSE IF
<source lang="sql">
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></source>
Use if with "IN"
<source lang="sql">
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></source>
Using an ELSIF Statement
<source lang="sql">
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.</source>
