case when usage

The case when keyword of the database is actually a format similar to if, else if, else, which is a way to obtain judgment conditions.

The syntax format of sql written in stored procedures is basically the same as that of sql written in ordinary times, and the usage and calling of case when are also the same.

First example:

select qzh from ywda_swjg_qzh_dz
where swjg = (
select
case when substr('11101823000'),0,7) = '1110182'
then substr('11101960000',0,8) || '000'
else substr('1110196000',0,7) || '0000'
end
swjgdm from ywda_swjg_qzh_dz
where a.swjg_dm = b.swjg_dm);

case when can not only be used in the query results, but can also be used as a judgment condition after the where condition.

This sql includes both situations. Of course, we can also use it directly without adding select to the subsequent where condition, such as:

select qzh from ywda_swjg_qzh_dz
where swjg =
case when substr('11101823000'),0,7) = '1110182'
then substr('11101960000',0,8) || '000'
else substr('1110196000',0,7) || '0000'
end；

The case when method is definitely more efficient.

Note: case when must be used with then and end.

Of course, my stored procedure is not written like this. Today the boss said that you don’t need to be too responsible and just write it in the format of if and else.

The if and else formats of stored procedures are a bit different from the if and else formats used in the front and backend. The company does not have an external network and is purely hand-made. I will not post the java code and directly upload part of the stored procedure. .

if substr(AVC_QXSWJG,0,8) = '11100006' or substr(AVC_QXSWJG,0,7) = '1110182' then
select qzh into avc_qch from ywda_swjg_qzh_dz where swjg_dm = substr(avc_qxswjg,0,8) || '000';
elsif    条件（不加括号） then
................................sql省略.........................................
else
.................................sql省略.......................................
end if;
sql结束。

Note that the logical operators here use SQL logical operators, that is, and and or.

The "||" written above is appending a string after the field, not a logical OR, please note.

After the end of if, be sure to add end if to indicate the end of the current if.

Recommended tutorial: mysql video tutorial

The above is the detailed content of case when usage. For more information, please follow other related articles on the PHP Chinese website!