oracle PL / SQL basic composition, structure, control statements, branch structure, choose the structure, loop loop structure

2010-12-08  来源:本站原创  分类:Database  人气:85 

Branch structure of the branch structure is the most basic program structure, branch structure, achieved by the IF statement.
Using the IF statement, according to the conditions can change the program logic flow. IF statement has the following form:
IF condition 1 THEN
Statement sequence 1;
[ELSIF condition 2 THEN
Statement sequence 2;
ELSE
Statement sequence n;]
END IF;
Of which:
Condition part is a logical expression, the value can only be true (TRUE), false (FALSE) or empty (NULL).
Sequence of statements as a number of executable statements.
Depending on the circumstances, the branch structure can have the following form:
IF-THEN-END IF
IF-THEN-ELSE-END IF
IF-THEN-ELSIF-ELSE-END IF

1. IF-THEN-END IF This is the simplest form of IF structure, practice is as follows:
[1] If the temperature is greater than the training of 30 ℃ , then display "temperature too high."
Enter and execute the following procedure:
Sql Code

SET SERVEROUTPUT ON
                DECLARE
                 V_temprature           NUMBER(5):=32;
                 V_result               BOOLEAN:=false;
                 BEGIN
                  V_result:= v_temprature >30;
                 IF V_result THEN
                DBMS_OUTPUT.PUT_LINE(' Temperature '|| V_temprature ||' Degrees, higher ');
                 END IF;
                END;

Execution results:
Sql Code

Temperature of 32 degrees , On the high
                PL/SQL Process completed successfully .

Description: This program uses a Boolean variable, initial value is false, said temperature is below 30 ℃ . Expression v_temprature> 30 return value is Boolean, assigned to the logical variable V_result. If the value of the variable v_temprature than 30, the return value is true, otherwise false. V_result is true to the END IF IF will be executed between the output statement, or no output.
Modify the initial test temperature is 25 ℃ , re-run, observe the results.

2. IF-THEN-ELSE-END IF form of this form of exercise is as follows:
[2] according to gender training, showing known.
Enter and execute the following procedure:
Sql Code

SET SERVEROUTPUT ON
                DECLARE
                v_sex   VARCHAR2(2);
                v_titil         VARCHAR2(10);
BEGIN
  v_sex:=' Male ';
  IF v_sex =' Male ' THEN
    v_titil:=' Mr ';
  ELSE
    v_titil:=' Lady ';
  END IF;
  DBMS_OUTPUT.PUT_LINE(v_titil||' Hello !');
END;

Execution results:
Sql Code

Hello !
                PL/SQL  Process completed successfully .

Description: This program displays known and greetings by gender, regardless of gender of the value, there will always display the results output. If V_sex value is not a 'male' and 'female', then the output will be?
[Exercise 1] added to modify the above procedures, embedded in the ELSE part of an IF structure, if V_sex value is not 'women', it displays "Hello, dear."
3. IF-THEN-ELSIF-ELSE-END IF form of this form of exercise is as follows:
[3] training outline based on wage taxes.
Enter and run the following program:
Sql Code

SET SERVEROUTPUT ON
DECLARE
  v_sal  NUMBER(5);
  v_tax  NUMBER(5,2);
BEGIN
  SELECT sal INTO v_sal
  FROM emp
  WHERE empno=7788;
IF v_sal >=3000 THEN
                        V_tax:= v_sal*0.08;-- Tax rate 8%
                 ELSIF v_sal>=1500 THEN
                         V_tax:= v_sal*0.06; -- Tax rate 6%
                ELSE
                         V_tax:= v_sal*0.04; -- Tax rate 4%
                 END IF;
                DBMS_OUTPUT.PUT_LINE(' Tax due :'||V_tax);
                END;

Execution results:
Sql Code

Tax due :240
                PL/SQL  Process completed successfully .

Description: The program No. 7788 employees based on wage taxes due, different wage levels of different tax rates.

Choose structure
The CASE statement for the case of multi-branching points, may have the following three uses.
1. CASE statement syntax of the basic structure is as follows:
Select the name of the variable CASE
WHEN 1 THEN expression
Sequence of statements 1
WHEN 2 THEN expression
Statement sequence 2
N THEN WHEN expression
Statement sequence n
ELSE
Statement sequence n +1
END CASE;
Throughout the structure, choose the value of the variable expression of the same order of values match if equal, then execute the appropriate sequence of statements, if not equal, the ELSE part of the execution sequence of statements.
The following is a selection using the CASE structure of practice.
[1] using the CASE structure the training to achieve post conversion.
Enter and run the program:
Sql Code

SET SERVEROUTPUT ON
DECLARE
v_job  VARCHAR2(10);
BEGIN
SELECT job INTO v_job
FROM emp
WHERE empno=7788;
CASE v_job
WHEN 'PRESIDENT' THEN
 DBMS_OUTPUT.PUT_LINE(' Employee positions : CEO ');
WHEN 'MANAGER' THEN
 DBMS_OUTPUT.PUT_LINE(' Employee positions : Manager ');
WHEN 'SALESMAN' THEN
 DBMS_OUTPUT.PUT_LINE(' Employee positions : The salesman ');
WHEN 'ANALYST' THEN
 DBMS_OUTPUT.PUT_LINE(' Employee positions : Systems analyst ');
WHEN 'CLERK' THEN
 DBMS_OUTPUT.PUT_LINE(' Employee positions : Staff ');
ELSE
 DBMS_OUTPUT.PUT_LINE(' Employee positions : Unknown ');
END CASE;
END;

The results:
Sql Code

Employee positions : Systems analyst
                PL/SQL  Process completed successfully .

Note: The above example retrieves the duties of employees 7788, through the CASE structure into Chinese output.
[Exercise 1] to change to other known employee number employee number, re-run.

2. CASE statement expression structure in Oracle, CASE structure, but also in the form of an assignment, it is variable depending on the chosen values obtained different results.
Its basic structure is as follows:
Choose variable names variable = CASE
WHEN 1 THEN value of an expression
WHEN 2 THEN value of the expression 2
Value n n THEN WHEN expression
ELSE value n +1
END;
[2] trained using CASE expressions structure.
Sql Code

SET SERVEROUTPUT ON
                DECLARE
                          v_grade       VARCHAR2(10);
                         v_result       VARCHAR2(10);
                BEGIN
                         v_grade:='B';
                         v_result:=CASE v_grade
                          WHEN 'A' THEN ' Excellent '
WHEN 'B' THEN ' Benign '
                        WHEN 'C' THEN ' In the '
                         WHEN 'D' THEN ' Difference '
                        ELSE ' Unknown '
                END;
                DBMS_OUTPUT.PUT_LINE(' Evaluation rating :'||V_result);
                END;

Execution results:
Sql Code

Evaluation rating : Benign
                PL/SQL  Process completed successfully .

Note: The CASE expression to determine the variable v_grade by the value of the variable V_result assigned different values.

3. Search CASE structure
Oracle also provides a search CASE structure, it does not select variables directly determine the value of conditional expression, the expression under the conditions decided to switch.
CASE
WHEN 1 THEN conditional expression
Sequence of statements 1
WHEN 2 THEN conditional expression
Statement sequence 2
WHEN condition n THEN expression
Statement sequence n
ELSE
Statement sequence n +1
END CASE;
[3] using the CASE training search structure.
Sql Code

SET SERVEROUTPUT ON
                DECLARE
                   v_sal        NUMBER(5);
                BEGIN
                   SELECT sal INTO v_sal FROM emp
                         WHERE empno=7788;
                CASE
                        WHEN v_sal>=3000 THEN
                DBMS_OUTPUT.PUT_LINE(' Wage level : High ');
                         WHEN v_sal>=1500 THEN
DBMS_OUTPUT.PUT_LINE(' Wage level : In the ');
           ELSE
   DBMS_OUTPUT.PUT_LINE(' Wage level : Low ');
END CASE;
END;

Execution results:
Sql Code

Wage level : High
                PL/SQL  Process completed successfully .

Description: This structure is similar to IF-THEN-ELSIF-ELSE-END IF structure. The training of employees to determine the 7788 salary levels.

Loop structure
Loop structure is the most important program control structure, a program used to control the repeated execution. For example, we want to accumulate, you can cycle through the appropriate procedures for implementation. PL / SQL loop structure can be divided into the following three kinds:
* Basically the LOOP.
* FOR LOOP loop.
* WHILE LOOP loop.

1. The LOOP basic cycle of the basic structure is as follows:
LOOP - Loop Start identification statement 1;
Statement 2;
EXIT [WHEN condition];
END LOOP; - end of the cycle to identify the role of the cycle is repeated between the implementation of LOOP and END LOOP statements.
EXIT to exit the cycle loop, WHEN EXIT exit is used to define the conditions. If no WHEN condition is met unconditional EXIT statement exits the loop.
[1] Training requirements: 12 +32 +52 +...+ 152 value.
Enter and execute the following procedure:
Sql Code

SET SERVEROUTPUT ON
                DECLARE
                 v_total                NUMBER(5):=0;
                v_count         NUMBER(5):=1;
                BEGIN
                LOOP
                    v_total:=v_total+v_count**2;
                EXIT WHEN v_count=15;-- Conditions to exit
v_count:=v_count+2;
                END LOOP;
                 DBMS_OUTPUT.PUT_LINE(v_total);
                END;

The output is:
Sql Code

680
                PL/SQL  Process completed successfully .

Note: Be sure to use the basic loop EXIT exit, otherwise it will become a dead cycle.
[Exercise 1] seeking 1 * 2 * 3 * 4 *...* 10 value.

2. FOR LOOP cycle
FOR loop is a fixed number of cycles in the following format:
FOR control variable in [REVERSE] lower limit ..
LOOP
Statement 1;
Statement 2;
END LOOP;
Loop control variable is implicitly defined, no need to declare.
Lower and upper limits for the specified number of cycles. Under normal circumstances the loop control variable values from the lower to the upper limit increase, REVERSE keyword indicates that the loop control variable values decreasing from the upper to the lower limit.
The following is a FOR loop construct practice.
[2] with a FOR loop training output graphics.
Sql Code

SET SERVEROUTPUT ON
                BEGIN
                FOR I IN 1..8
                LOOP
            DBMS_OUTPUT.PUT_LINE(to_char(i)||rpad('*',I,'*'));
                END LOOP;
                END;

The output is:
Sql Code

1*
2**
3***
4****
5*****
6******
7*******
8********
                PL/SQL  Process completed successfully .

Description: The program uses a loop in the loop control variable I, the variable is implicitly defined. In each loop the loop control variable according to the value of I, using the RPAD function to control the display of the corresponding number of "*."
[2] for the above exercise program to increase REVERSE keyword to observe the implementation of the results.
[3] trained a hollow triangle output.
Sql Code

BEGIN
                FOR I IN 1..9
                LOOP
                 IF I=1 OR I=9 THEN
                 DBMS_OUTPUT.PUT_LINE(to_char(I)||rpad(' ',12-I,' ')||rpad('*',2*i-1,'*'));
ELSE
          DBMS_OUTPUT.PUT_LINE(to_char(I)||rpad(' ',12-I,' ')||'*'||rpad(' ',I*2-3,' ')||'*');
                END IF;
                END LOOP;
                END;

The output is:
Sql Code

1           *
2          * *
3         *   *
4        *     *
5       *       *
6      *         *
7     *           *
8    *             *
9   *****************
PL/SQL  Process completed successfully .

Description: This example uses a combination of loops and IF structures, on the line 1 and line 9 (I = 1 OR I = 9) to perform the same output statement, the other line to perform additional output statements.
[Exercise 3] to modify the program, output a solid triangle.

3. WHILE LOOP cycle
WHILE loop is a conditional loop, the format is as follows:
WHILE condition
LOOP
Statement 1;
Statement 2;
END LOOP;
When the conditions are met, the loop body is executed; when conditions are not satisfied, then the end of the cycle. First determine if the condition is false, the loop is not executed.
The following is a WHILE loop architecture practice.
[3] use the WHILE loop training to the emp table into five consecutive records.
Step 1: Perform the following procedure:
Sql Code

SET SERVEROUTPUT ON
DECLARE
v_count NUMBER(2) := 1;
BEGIN
  WHILE v_count <6 LOOP
    INSERT INTO emp(empno, ename)
    VALUES (5000+v_count, ' Temporary ');
v_count := v_count + 1;
  END LOOP;
  COMMIT;
END;

The output is:
Sql Code

PL/SQL  Process completed successfully .

Step 2: Insert the record shows:
Sql Code

SELECT empno,ename FROM emp WHERE ename=' Temporary ';

The output is:

Sql Code

EMPNO ENAME
                ------------------ ----------
      5001  Temporary
      5002  Temporary
      5003  Temporary
      5004  Temporary
      5005  Temporary
                 Choose 5 rows .

Step 3: Remove the inserted record:
Sql Code

DELETE FROM emp WHERE ename=' Temporary ';
                COMMIT;

The output is:
Sql Code

Removed 5 lines .
                 Submit completed .

Description: This exercise uses the WHILE loop to the emp table, inserts five new records (employee number generated based on loop variables), and through the query shows the newly inserted record, then delete.

4. Loops can be nested multiple loops, the following is a double loop of practicing.
[4] use two re-training cycle requirements 1! +2! +...+ 10! Value.
Step 1: 1 algorithms:
Sql Code

SET SERVEROUTPUT ON
DECLARE
  v_total       NUMBER(8):=0;
  v_ni  NUMBER(8):=0;
  J             NUMBER(5);
BEGIN
FOR I IN 1..10
  LOOP
    J:=1;
          v_ni:=1;
    WHILE J<=I
    LOOP
      v_ni:= v_ni*J;
      J:=J+1;
    END LOOP;-- Cycle in seeking n!
v_total:=v_total+v_ni;
  END LOOP;-- Circulation sum ask
  DBMS_OUTPUT.PUT_LINE(v_total);
END;

The output is:
Sql Code

4037913
PL/SQL  Process completed successfully .

Step 2: The first two algorithms:
Sql Code

SET SERVEROUTPUT ON
DECLARE
  v_total               NUMBER(8):=0;
  v_ni          NUMBER(8):=1;
BEGIN
  FOR I IN 1..10
  LOOP
    v_ni:= v_ni*I;      -- Seeking n!
    v_total:= v_total+v_ni;
  END LOOP;             -- Loop sum ask
  DBMS_OUTPUT.PUT_LINE(v_total);
END;

The output is:
Sql Code

409114
                PL/SQL  Process completed successfully .

Description: The first algorithms of the program requirements within the WHILE loop recycling levels, demand outside the loop using the FOR loop sum. 2 algorithms are simplified algorithm, based on: n! = n * (n? 1)!.

From: black hair http://heisetoufa.iteye.com/

相关文章
  • oracle PL / SQL basic composition, block structure and basic syntax requirements, data types, variable definitions, operators and functions 2011-10-08

    oracle PL / SQL basic composition, block structure and basic syntax requirements, data types, variable definitions, operators and functions Feature PL / SQL language is the SQL language extensions, designed for application developers with features su

  • oracle PL / SQL basic composition, structure, control statements, branch structure, selection structures, loop loop structure 2010-12-08

    Branch of the branch structure is the basic program structure, branch structure, achieved by the IF statement. Using the IF statement, condition can change the program according to the logic of the process. IF statement has the following form: IF con

  • oracle PL / SQL basic composition, structure, control statements, branch structure, choose the structure, loop loop structure 2010-12-08

    Branch structure of the branch structure is the most basic program structure, branch structure, achieved by the IF statement. Using the IF statement, according to the conditions can change the program logic flow. IF statement has the following form:

  • 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

  • Oracle PL / SQL study notes 01 2010-05-05

    Chapter PL / SQL Introduction 1. Why use PL / SQL PL / SQL and SQL combined with tight, both use the same data type. PL / SQL to use less code and have the speed of execution PL / SQL simplifying application logic to the database layer of the transfe

  • based oracle pl / sql basics 2011-01-05

    1. Block (Block) is a pl / sql basic program unit, write pl / sql program actually is to write pl / sql departments and regions, by definition part of the operative part and exception handling parts. Definitions used to define constants, variables, c

  • oracle pl / sql examples of practice 2010-03-30

    Part I: oracle pl / sql instance of practice (1) First, using the scott / tiger emp table under the user and the dept table to complete the following exercises, table structure described below Staff table emp (empno Staff No. / ename employee name /

  • Oracle PL/SQL语言入门基础 2014-09-12

    正在看的ORACLE教程是:Oracle PL/SQL语言入门基础. PL/SQL是ORACLE对标准数据库语言的扩展,ORACLE公司已经将PL/SQL整合到ORACLE 服务器和其他工具中了,近几年中更多的开发人员和DBA开始使用PL/SQL,本文将讲述PL/SQL基础语法,结构和组件.以及如何设计并执行一个PL/SQL程序. PL/SQL的优点 从版本6开始PL/SQL就被可靠的整合到ORACLE中了,一旦掌握PL/SQL的优点以及其独有的数据管理的便利性,那么你很难想象ORACLE缺了P

  • Oracle PL / SQL exception 2010-03-10

    Oracle PL / SQL exception Named system exception causes ACCESS_INTO_NULL Undefined object CASE_NOT_FOUND CASE If does not contain the appropriate WHEN, and if there are no settings ELSE When COLLECTION_IS_NULL A collection of elements is not initiali

  • Oracle 11g SQL Basic Training 2010-07-21

    Oracle 11g SQL Basic Training

  • Does not appear to delete all the replies show replies show star returns to score back ORACLE PL / SQL string functions. Mathematical functions. Date function 2010-08-04

    ORACLE PL / SQL string functions, math functions, date functions - String functions] [ - String interception substr (field name, the starting point, number) select Name, substr (Name, 2,4), substr (Name, 0,3), substr (Name, -2,3), substr (Name, -2,1)

  • Using oracle pl / sql developer summed up the points 2010-09-04

    Using oracle pl / sql developer summed up the points 1. Only the implementation of the cursor sql statement setting: / Tool / Preferences / SQL Windows / select Auto Select Statement, if not selected, the sql windows have all the sql execution. Note:

  • oracle pl \ sql book recommendations 2010-09-10

    Development for some time, had never used stored procedures, has recently started to write oracle pl \ sql Entry is very easy to find, Reference books: Oracle.PL.SQL.Programming Written in easy to understand, with a small example of O_Reilly Oracle.P

  • oracle pl sql connect 2010-10-05

    oracle pl sql connect

  • Oracle PL / SQL study 2010-11-30

    Write a stored procedure that you can add data to a table. 1. To create a simple table create table mytest( name varchar2(30), passwd varchar2(30) ); 2. Creation process. --replace Indicates if any sp_pro, replaces create or replace procedure sp_pro

  • ORACLE PL / SQL function 2011-05-07

    ORACLE PL / SQL string functions, math functions, date functions --[ String Functions ] -- String intercept substr( Field name, the starting point , Number ) select Name,substr(Name,2,4),substr(Name,0,3),substr(Name,-2,3),substr(Name,-2,1) from t1; -

  • Oracle PL / SQL e-books to download 2011-03-17

    Oracle.PL.SQL.Programming.4th.Edition

  • Oracle PL/SQL入门慨述 2013-12-28

    正在看的ORACLE教程是:Oracle PL/SQL入门慨述. 一.PL/SQL出现的目的 结构化查询语言(Structured Query Language,简称SQL)是用来访问关系型数据库一种通用语言,它属于第四代语言(4GL),其执行特点是非过程化,即不用指明执行的具体方法和途径,而是简单的调用相应语句来直接取得结果即可.显然,这种不关注任何实现细节的语言对于开发者来说有着极大的便利. 然而,对于有些复杂的业务流程又要求相应的程序来描述,那么4GL就有些无能为力了.PL/SQL的出现正

  • ORACLE PL/SQL 触发器编程篇介绍 2014-11-08

    ORACLE PL/SQL 触发器能够完成由数据库的完整性约束难以完成的复杂业务规则的约束:监视数据库的各种操作以及实现审计功能 1.基本概念 两种功能:完成由数据库的完整性约束难以完成的复杂业务规则的约束:监视数据库的各种操作,实现审计功能. 触发器分为:DML触发器(对表或视图执行DML操作时触发),INSTEAD OF触发器(只定义在视图上,替代实际的操作语句),系统触发器(对数据库系统进行操作时触发,如DDL语句.启动或关闭数据库等) 触发事件: 上述触发器中括号内容都是触发事件. 触发

  • Oracle PL/SQL入门案例实践 2015-02-19

    正在看的ORACLE教程是:Oracle PL/SQL入门案例实践. 前面已经了解了关于PL/SQL编程的基础,本文将结合一个案例来加深对这些知识点的理解. 一. 案例介绍 某数据库有两张表,是关于某公司员工资料.薪水和部门信息的,它们分别是emp表和dept表,两张表的结构如下: 要求如下: 1.按照上表结构建立相应的表,并每张表写入5组合法数据. 2.操纵相关表,使得"技术部"的员工的薪水上涨20%. 3.建立日志,追踪薪水变动情况. 4.建立测试包. 二. 案例的分析与实现 从前