Oracle PL/SQL Programming Fifth Edition, chapter fourth, condition and sequence control

This chapter exercises the use of that Scott under the EMP table.

1. First use if/end IF;

If.sql

CREATE OR REPLACE PROCEDURE get_sal (emp_no_in in number) AUTHID Current_useris l_sal number; BEGIN SELECT sal into l_sal from emp WHERE empno = emp_no_in;

IF l_sal > then dbms_output.    Put_Line (' OK '); ELSE Dbms_output.    Put_Line (' POOR '); END IF; end;/

The following is the case:

Main.sql

BEGIN get_sal (7521); end;/

And then the most complete, plus the elsif.

If.sql

CREATE OR REPLACE PROCEDURE get_sal (emp_no_in in number) AUTHID Current_useris l_sal number; BEGIN SELECT sal into l_sal from emp WHERE empno = emp_no_in;

IF l_sal between and then Dbms_output.    Put_Line (' OK '); elsif l_sal > then dbms_output.    Put_Line (' GOD '); ELSE Dbms_output.    Put_Line (' POOR '); END IF; end;/

Here I made a mistake, wrote the Elsif, the following did not write then, the error.

2. Here's the case, go ahead and write a simple example

Case.sql

CREATE OR REPLACE PROCEDURE Case_demo (emp_no_in in number) AUTHID Current_useris l_job VARCHAR2 (9); BEGIN SELECT job into l_job from emp WHERE empno = emp_no_in;

Case L_job "Clerk" then Dbms_output.    Put_Line (' clerk '); When the ' MANAGER ' then dbms_output.    Put_Line (' MANAGER '); ELSE Dbms_output.    Put_Line (' shit '); END case; END case_demo;/

is simply to view the job.

Main.sql

BEGIN get_sal (7839);    Case_demo (7369); Case_demo (7782); end;/

This is the code to execute.

The new code is like this, and I did not carefully encountered the mistake, took half a day, only to find the problem.

CREATE OR REPLACE PROCEDURE Case_demo (emp_no_in in number) AUTHID Current_useris l_job VARCHAR2 (9); L_sal number; BEGIN SELECT sal into l_sal from emp WHERE empno = emp_no_in;

Case when L_sal >= and L_sal <= and Dbms_output.    Put_Line (' 1000->2000 '); When L_sal >= and L_sal <= and Dbms_output.    Put_Line (' 2000->3000 '); When L_sal > Dbms_output.    Put_Line (' >3000 '); ELSE Dbms_output.    Put_Line (' >3000 '); END case; END case_demo;/

Originally I thought not to write the last one else, but the original can not, if the use of the beginning of that form, is not to write,

But with case true or direct case, you need the last else.

This is the problem: case is not found while executing case statement

And then it's the case expression, which ends with end with no semicolon, no case, just end with end, this is an expression,

Returns the value of the symbol, and returns null if there is no match.

Then I wrote an example:

CREATE OR REPLACE PROCEDURE give_bonus (emp_no_id in number, bonus in number) AUTHID Current_userisbegin dbms_output.    Put_Line (emp_no_id); Dbms_output. Put_Line (bonus); END give_bonus;/

This is followed by the use of the case expression

declare    l_sal number; begin    select sal into l_sal    from emp     where empno = 7839;    dbms_output. Put_Line (l_sal);     give_bonus (7839,              CASE                 when l_sal >= 1000 and l_sal <= 2000 then  500                when  l_sal >2000 and l_sal <= 3000 THEN 400                 when l_sal > 3000  THEN 200                end); end;/

So it's actually an expression, and then the expression gets a result.

After the introduction to the empty statement, before the microcontroller, I remember there is such a thing, I forgot what the specific is, here

The use of NULL, so that the direct representation of an executable statement, but in fact, does not carry out the meaning of anything.

Oracle PL/SQL Programming Fifth Edition, chapter fourth, condition and sequence control