Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle conditional select statements and looping statements     - in no backup ex: error: Xin Tourou ah backup child process has Singles DAT / US person / bin / in no backup ex (Database)

- Changes in C # asynchronous programming model (Programming)

- Linux Command study manual - GPG command (Linux)

- Linux vi command list (Linux)

- Learning how to teach safety system to prevent your own IP leakage (Linux)

- Getting Started with Linux system to learn: how to install the kernel headers on Linux (Linux)

- Try debugfs restore the deleted files ext3 file system (Linux)

- Linux, modify / retrieve the root password (Linux)

- HBase in MVCC implementation mechanism and its application (Database)

- Redis data types Introduction (Database)

- 2 minutes to read large data framework Hadoop and Spark similarities and differences (Server)

- Deb package installation method under ubuntu (Linux)

- CentOS 6.x systems installation + NIC driver installation (Realtek PCIe GBE Family Controller for Linux) (Linux)

- How to install Linux on a MacBook Pro Retina (Linux)

- Log analysis is done with Grafana Elasticsearch (Server)

- bash login and welcome message: / etc / issue, / etc / motd (Linux)

- Hadoop1.2.1 plug compilation (Server)

- Linux FTP setting file description (Server)

- C ++ free store and heap (Programming)

- Achieve single backup of MySQL by innobackupex (Database)

 
         
  Oracle conditional select statements and looping statements
     
  Add Date : 2017-08-31      
         
         
         
  Oracle conditional select statements (IF, CASE), loops (LOOP, WHILE, FOR), and sequential control statements (GOTO, NULL).
1, IF condition selection statement
1.1 simple conditions to determine

DECLARE
  
 V_sal NUMBER (6,2);
  
BEGIN
  
 SELECT sal INTO v_sal FROM emp WHERE lower (ename) = lower ( '&& name');
  
IFv_sal < 2000 THEN
  
 UPDATE emp SET sal = v_sal + 200 WHERE lower (ename) = lower ( '& name');
  
END IF;
  
END;

 
1.2 Dual conditional branching

DECLARE
  
 V_comm NUMBER (6,2);
  
BEGIN
  
 SELECT comm INTO v_comm FROM emp WHERE empno = & no;
  
 IF v_comm < > 0 THEN
  
  UPDATE emp SET comm = v_comm + 100 WHERE empno = & no;
  
 ELSE
  
  UPDATE emp SET comm = 200 WHERE empno = & no;
  
 END IF;
  
END;

 
Multiple conditional branches

DECLARE
  
 V_job VARCHAR2 (10);
  
 V_sal NUMBER (6,2);
  
BEGIN
  
 SELECT job, sal INTO v_job, v_sal FROM emp WHERE empno = & no;
  
 IF v_job = 'PRESIDENT' THEN
  
  UPDATE emp SET sal = v_sal + 1000 WHERE empno = & no;
  
 ELSEIF
  
  V_job = 'MANAGER' THEN
  
  UPDATE emp SET sal = v_sal + 500 WHERE empno = & no;
  
 ELSE
  
  UPDATE emp SET sal = v_sal + 200 WHERE empno = & no;
  
 END IF;
  
END;

2, CASE condition select statement
2.1 Use a single selector for equality comparison

DECLARE
  
 V_deptno emp.deptno% TYPE;
  
BEGIN
  
 V_deptno: = & no;
  
CASE v_deptno
  
 WHEN 10 THEN
  
  UPDATE emp SET comm = 100 WHERE deptno = v_deptno;
  
 WHEN 20 THEN
  
  UPDATE emp SET comm = 80 WHERE deptno = v_deptno;
  
 WHEN 30 THEN
  
  UPDATE emp SET comm = 30 WHERE deptno = v_deptno;
  
 ELSE
  
  DBMS_OUTPUT.PUT_LINE ( 'does not exist for this department');
  
END CASE;
  
END;

 Remark: ELSE is the default result when the condition is not satisfied.

2.2 CASE statement in the use of a variety of conditional comparison
 You can use the CASE xxx syntax when comparing values using a single condition selector. If you have multiple conditions for unequal comparisons, you must specify the comparison condition in the WHEN clause.

DECLARE
  
 V_sal emp.sal% TYPE;
  
 V_ename emp.ename% TYPE;
  
BEGIN
  
 SELECT sal, ename INTO v_sal, v_ename FROM emp WHERE empno = & no;
  
CASE
  
 WHEN v_sal < 1000 THEN
  
  UPDATE emp SET comm = 100 WHERE ename = v_ename;
  
 WHEN v_sal < 2000 THEN
  
  UPDATE emp SET comm = 80 WHERE ename = v_ename;
  
 WHEN v_sal < 6000 THEN
  
  UPDATE emp SET comm = 50 WHERE ename = v_ename;
  
END CASE;
  
END;

 
3, LOOP cycle

 DECLARE
  
 I NUMBER (10): = 1;
  
BEGIN
  
 LOOP
  
  DBMS_OUTPUT.PUT_LINE (i);
  
  I: = i + 1;
  
  EXIT WHEN i = 10;
  
 END LOOP;
  
END;

NOTE: You must conditionally exit the loop using the WHEN clause.

4, WHILE cycle
 The basic loop executes at least one statement in the body of the loop, whereas for a WHILE loop, the statement inside the loop is executed only if the condition is TRUE. End of WHILE..LOOP END LOOP.

DECLARE
  
 I NUMBER (10): = 1;
  
BEGIN
  
 WHILE i < = 10 LOOP
  
  DBMS_OUTPUT.PUT_LINE (i);
  
  I: = i + 1;
  
 END LOOP;
  
END;

 
5, FOR loop

BEGIN
  
 FOR i IN 1..10 LOOP
  
  DBMS_OUTPUT.PUT_LINE (i);
  
 END LOOP;
  
END;

 

 

BEGIN
  
 FOR i IN REVERSE 1..10 LOOP
  
  DBMS_OUTPUT.PUT_LINE (i);
  
 END LOOP;
  
END;

Remark: The keyword REVERSE indicates the reverse direction, that is, descending from the maximum value. FOR loops can be nested.
 
 6, GOTO statement
    Jump unconditionally to a label. Eg: goto tag name;
    NOTE: The Oracle tag is defined as: < < Tag Name >>
                Oracle uses two single quotation marks that escape meaning, eg: '' 'represents a single quotation mark.
                GOTO statement on the system performance is great, it is recommended to use as little as possible.
 
7, NULL statement
    Do nothing, just give control to the next statement.
     
         
         
         
  More:      
 
- RPM package management tools under Linux (Linux)
- Linux see whether there is a hacker program (Linux)
- Apache2.4.7 make an error [exports.lo] Error 1 Solution (Server)
- Modern Objective-C syntax and new features (Programming)
- Fedora 8 configured Heartbeat (Linux)
- VirtualBox CentOS is configured as a local disk mirroring software source (Linux)
- High-performance JavaScript loops and flow control (Programming)
- Compile and install Redis and register as a system service under RedHat5.8 environment (Database)
- Retro terminal in Linux (Linux)
- Analysis of potential problems through custom Orabbix monitoring Oracle (Database)
- Linux Network Analysis Tcpdump Command Guide (Linux)
- tar command to extract a file error: stdin has more than one entry (Linux)
- CentOS 6.5 makes the LAN http source (Linux)
- Preliminary understanding of SELinux security management (Linux)
- Nine artifact control disk partition under Linux (Linux)
- Selection sort, insertion sort, and Shell sort (Programming)
- OpenGL Superb Learning Notes - Fragment Shader (Programming)
- How to use Aptik to backup and restore Apps/PPAs under ubuntu (Linux)
- CentOS 5.10 installed Oracle 11G R2 (Database)
- Two strokes to improve development productivity Struts2 (Programming)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.