Oralce statement use case ... when ...

2011-10-09  来源:本站原创  分类:Database  人气:39 

Title: the following case statement:
declare
v_sal number: = 2000;
v_tax number;
begin
case
when v_sal <1500 then
v_tax: = v_sal * 0.03;
when v_sal <2500 then
v_tax: = v_sal * 0.04;
when v_sal <3500 then
v_tax: = v_sal * 0.05;
when v_sal <8000 then
v_tax: = v_sal * 0.08;
else
v_tax: = v_sal * 0.1;
end case;
end;
/
Execution is completed, the value of the variable v_tax how much?

Under the first test results?
cmd database on the sqlplus connection, perform set serveroutput on; open the console output displays,
And then copy the above statement, in the end; before the a: dbms_output.put_line (v_tax);
Re-enter,
Console output: 80

The correct answer is: 80

It can be seen: Oracle in the case ... when the block is different from Java in the case ... when, oracle once a match is found in the results will be returned directly.

If the case statement is not found eligible when statements and positions with no ELSE clause, then the error will oralce: ORA-06592: implementation of CASE statement CASE not found

case, there are two expressions:

1 simple Oracle case expressions, using the expression to determine the return value.
Syntax:
case search_expression
WHEN expression1 THEN result1
WHEN expression2 THEN result2
....
WHEN expressionN THEN resultN
ELSE default_result
END

2 search case expression, the use of conditions to determine the return value.
Syntax:
case
WHEN condition1 THEN result1
WHEN condistion2 THEN result
....
WHEN condistionN THEN resultN
ELSE default_result
END

Although the two forms in different places can be slightly different wording, such as PL / SQL fast, the words behind each statement has a semicolon when, in the other query is not required, but added an error!

相关文章
  • Oralce statement use case ... when ... 2011-10-09

    Title: the following case statement: declare v_sal number: = 2000; v_tax number; begin case when v_sal <1500 then v_tax: = v_sal * 0.03; when v_sal <2500 then v_tax: = v_sal * 0.04; when v_sal <3500 then v_tax: = v_sal * 0.05; when v_sal <8000

  • LIKE statement specifies case sensitivity 2011-03-03

    The current environment OS: MS Windows7 DB: 5.1.50-community MySQL Community Server (GPL) DB charset: utf8 In the MySql console input show collation like 'utf8%'; MySql support to find out the collation Among them, utf8_general_ci is not case sensiti

  • oracle update set from statement in the format [turn] 2011-09-01

    Two tables a, b, b, a memo to the field value is equal to a table id name value corresponding to the table a: id, name 1 King 2 Li 3 Table b: id, ClientName 1 2 3 (MS SQL Server) statement: update b set ClientName = a.name from a,b where a.id = b.id

  • EJB3 JPA JPQL statement subquery query use the function page 2010-04-12

    JPQL is a query language, has the similar features of SQL, JPQL is fully object-oriented, with inheritance, polymorphism and association, and hibernate HQL is very similar. Query parameters JPQL statement supports the parameters defined in two ways b

  • Java switch-case statement usage 2010-06-10

    For the flow control switch-case statement, has been one of my weaknesses. When the written examination or interview time, always help loving mind, I think this should be the basis for my bad reasons it! In order to solve this disease, had to spend s

  • mysql dump.sql case conversion 2010-06-24

    Case: Export from the company's mysql database, in linux and into the mysql, sql statement application case led to the problem: Export mysql (windows): mysqldump-h host-u userName-p password> dump.sql Under linux into mysql: mysql-u root-p mysql> so

  • plsql strengthen the case / when / then 2010-07-19

    Oracle / PLSQL: Case Statement -------------------------------------------------- ------------------------------ In Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement. Translation:

  • JavaScript if statement in comparison with the switch statement describes 2010-08-16

    If conditional statements in JavaScript for the completion of the behavior under different conditions. Examples If statement <html> <body> If this example demonstrates <p> statement. </ P> <p> If the browser time to less than

  • Use switch-case statement 2010-08-27

    2007-12-25 08:11 if statement for the two branches need to use when handling multiple branch if-else-if structure, but if more branches are nested if statement, the more layers, the program not only substantial but also quite difficult to understand.

  • Three major issues in the database: constraints. CASE statements and documents (transfer) 2010-09-20

    This paper describes the way by example on constraints, CASE statements and documents three major issues. Constraint "Defined as the delay (deferrable)" constraints can be specified as: 1. Initially immediate (real-time execution initialization)

  • CASE WHEN to use the string replacement process 2011-03-01

    /* mysql> select * from sales; +-----+------------+--------+--------+--------+------+------------+ | num | name | winter | spring | summer | fall | category | +-----+------------+--------+--------+--------+------+------------+ | 1 | Java | 1067 | 200

  • On the update set from 2010-09-15

    Keywords: update set from Here is an example: Two tables a, b, want to b in the memo field value is equal to a table of name value corresponding to id Table a:id, name 1 Wang 2 Lee 3 Zhang Table b:id,ClientName 1 2 3 (MS SQL Server) Statement : updat

  • On the update set from, the first encounter, Khan! 2010-12-07

    On the update set from, the first encounter, Khan! Here is an example: Two tables a, b, b in the memo to the field value is equal to the corresponding id of a table name value table a: id, name 1 king 2 Li 3 Table b: id, ClientName 1 2 3 (MS SQL Serv

  • Chapter 5 using Oracle PL / SQL 2011-05-12

    Chapter 4 Oracle synonyms, sequences, views, indexes Chapter 6 Oracle cursor Chapter 5 using Oracle PL / SQL 1, the technical objectives Understanding PL / SQL functions and features Understanding of data types and their usage Understand the logic of

  • JAVA materials (recommended novice learning) 2009-07-26

    The contents of a very comprehensive JAVA materials, from J2SE to J2EE, easy-to-digest and explain, very suitable for beginners Contents Chapter I: Java started 1 Learning Objective 1 Java History 2 Java Technical Overview 3 3 advantages of Java tech

  • Java programming ideas (1 ~ 4 s) 2010-03-23

    Introduction Chapter Object "" "" Package "" "" Is hidden (ie packaging) on behalf of the part is usually inside the fragile parts of the object, they could easily be damaged by the programmer, it will achieve hidde

  • LUA scripting language entry 2010-03-28

    LUA scripting language entry Preliminary Study Lua Programming: Mu Feng (Second Life members) This article comes from CSDN blog: http://blog.csdn.net/skyremember/archive/2008/10/17/3089447.aspx In this article, I want to tell you how to Lua programmi

  • Oracle Study Notes (3) PLSQL program control structure 2010-03-20

    This is the third chapter of the study notes, study completed after the second chapter of the programming basics, from now to learn Oracle programming the ... ..., I hope that we can give some support ah! This is after all not to work overtime on Sat

  • Transact-SQL command programming 2010-04-01

    1. The state of the database set to read-only statement the following format: exec sp_dboption 'book', 'read only', false 2. The database space is set to minimum capacity: dbcc shrinkdatabase ('book') 3. The space capacity of the database set to 100M

  • Chapter VIII of the list. Menu and other views - Next 2010-03-19

    Here is your complete AndroidManifest.xml project file: <? Xml version = "1.0" encoding = "utf-8"?> <Manifest xmlns: android = http://schemas.android.com/apk/res/android package = "android_programmers_guide.AndroidViews &