Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle conditional select statements and looping statements     - Oracle physical storage structure outline (Database)

- Normal start Lazarus 1.0.8 under Ubuntu (Linux)

- Source code compiled by the installation program under Linux (Linux)

- Oracle Client Dedicated and Shared connection mode (Database)

- The basic principles of AIX system security (Linux)

- Linux Crontab Timing task command Detailed (Linux)

- iOS constants, variables, properties and characteristics (Programming)

- Nodejs nano library to handle couchdb: need a timeout (Database)

- Install Ubuntu open source drawing program MyPaint 1.2.0 (Linux)

- Ubuntu 14.04, 13.10 install OpenCV 2.4.9 (Linux)

- Linux basic articles of the boot process (Linux)

- OpenGL ES 3.0 vertex buffer (Programming)

- systemd run levels and service management command Introduction (Linux)

- ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], [] (Database)

- 20+ Best Practices article MySQL Performance Optimization (Database)

- Restore database fault encountered ORA-0600 (Database)

- sa weak passwords intrusion prevention (Linux)

- Hanoi problem Java Solution (Programming)

- Oracle Client + PL SQL Developer enables remote access to the Oracle database (Database)

- Build Docker based MongoDB replication cluster environment (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:      
 
- MYSQL root password for the database user passwords are weak attack analysis (Linux)
- After installation of Debian 6.0 do a few things first (Linux)
- Get and Post requests Comments (Linux)
- Use small network command to check whether PC Security (Linux)
- To read the Linux ext3 / ext4 format partitions under Windows system software (Linux)
- Dialogue UNIX:! $ # @ *% (Linux)
- Solaris 10 installation configuration mrtg monitoring system (Linux)
- Install and use automated tools Ansible in CentOS 7 (Linux)
- Usage sed some of the parameters (Linux)
- Linux console password solution (Programming)
- MySQL monitoring tool -Innotop (Database)
- How to use GRUB2 files directly from the hard disk to run ISO (Linux)
- Nginx-1.9.7 TCP reverse proxy (Server)
- AngularJS notes --- Data Binding (Programming)
- Seven kinds of NIC binding mode Detail (Linux)
- MySQL function: group_concat () function (Database)
- Installation and Configuration JDK8 In CentOS 7 (Linux)
- Java filter (Programming)
- Different between Linux file path and the windows (Linux)
- JSON Introduction and Usage Summary (Programming)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.