plsql基础相关小结

 

----------------------------
----------plsql的语言基础
----------------------------

---一个基础的plsql块，衍生的对象有函数，存储过程，匿名块等
<< LABEL >> --(optional)可选
DECLARE    -- Declarative part (optional)--可选
  -- Declarations of local types, variables, & subprograms

BEGIN      -- Executable part (required)--必须
  -- Statements (which can use items declared in declarative part)

[EXCEPTION -- Exception-handling part (optional)--可选
  -- Exception handlers for exceptions (errors) raised in executable part]
END;

----开启屏幕输出显示
SET SERVEROUTPUT ON;

--一切从helloworld开始
BEGIN
    DBMS_Output.Put_Line('hello world');
END;

----一个典型的FOR循环
BEGIN
  FOR someone IN (
    SELECT * FROM employees
    WHERE empno < 7600
    ORDER BY empno
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE('ename = ' || someone.ename ||
                         ', job = ' || someone.job);
  END LOOP;
END;
/

--数据库不区分大小写，
--同时引用的时候可以没有双引号
--变量声明时可以没有引号，也可以有
\set SQLTERM /
DECLARE
    "hello" varchar2(10) := 'hello';
BEGIN
    DBMS_Output.Put_Line("hello");
END;
/
或者
DECLARE
    hello varchar2(10) := 'hello';
BEGIN
    DBMS_OUTPUT.PUT_LINE("HELLO");
END;
--结果：
HELLO
或者
DECLARE
"HELLO" varchar2(10) := 'hello';
BEGIN
DBMS_OUTPUT.PUT_LINE(HELLO);
END;
--下列语句oracle输出报错，为撒？？？？？？？？？？？
--KES输出正确
DECLARE
    "hello" varchar2(100) := 'hello';
BEGIN
    DBMS_OUTPUT.PUT_LINE(hello);
END;

错误报告:
ORA-06550: 第 4 行, 第 23 列: 
PLS-00201: 必须声明标识符 'HELLO'
ORA-06550: 第 4 行, 第 2 列: 
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

---用户定义的标识符可以是中文
--这个例子运行正确
\set SQLTERM /
DECLARE
    你好 varchar2(10) := 'hello';
BEGIN
    DBMS_Output.Put_Line(你好);
END;
/
--结果：
hello

--双引号在手，天下我有，这个变量on/off switch在双引号下也是可以的
--双引号下一些标识符的约束不管用了
\set SQLTERM /
DECLARE
    "on/off switch" varchar2(10) := 'hello';
BEGIN
    DBMS_Output.Put_Line("on/off switch");
END;
/
结果
HELLO

--数据库不区分大小写
--下列语句oracle运行报错，为撒？？？？？？？
\set SQLTERM /
DECLARE
  "HELLO" varchar2(100) := 'hello';
BEGIN
  DBMS_Output.Put_Line("Hello");
END;
/
--KES运行结果：
hello

----个人理解，定义时双引号时可选的，引用该变量时双引号也是可选的

--保留字作为引用的用户定义标识符
/*
本例中第一个示例将保留字BEGIN加双引号后”BEGIN”，可以作为用户定义的标识符使用，引用该标识符时，不区分大小写；
*/
\set SQLTERM /
DECLARE
    "BEGIN" varchar2(15) := 'UPPERCASE';
BEGIN
    DBMS_Output.Put_Line("BEGIN");
    DBMS_Output.Put_Line("Begin");
    DBMS_Output.Put_Line("begin");
    --DBMS_Output.Put_Line(bEgin); --关键字作为变量必须使用双引号
END;
/
--结果：
UPPERCASE
UPPERCASE
UPPERCASE

--下列示例定义了”BEGIN”和”Begin”两个变量，但因为不区分大小写，所以认为是同一个变量，运行结果提示报错。
\set SQLTERM /
DECLARE
    "BEGIN" varchar2(15) := 'UPPERCASE';
    "Begin" varchar2(15) := 'Initial Capital';
BEGIN
    DBMS_Output.Put_Line("BEGIN");
    DBMS_Output.Put_Line("Begin");
END;
/
--结果：
错误:  重复声明 在 ""Begin"" 或附近的

----个人理解，关键字也可以作为变量，但是不建议这么做，但是关键字作为变量必须双引号

--下列例子执行这是会换行的
--PUT_LINE中换行，实际输出也跟着换行
\set SQLTERM /
BEGIN
    DBMS_OUTPUT.PUT_LINE('This string breaks
    here.');
END;
/
--结果如下：
This string breaks
here.

--如果上述例子不想换行，则使用字符串连接运算符（| |）构造字符串。
--如下所示
BEGIN
    DBMS_OUTPUT.PUT_LINE('This string breaks' || 
    ' here.');
END;
--结果如下：
THIS STRING BREAKS HERE.

--------注释功能-----------
--单行注释
--这个例子有4处单行注释。如下：
\set SQLTERM /
DECLARE
    howmany NUMBER;
    num_tables NUMBER;
BEGIN
    -- Begin processing
    SELECT COUNT(*) INTO howmany   -- 没有分号，这个地方也可以注释
    FROM USER_OBJECTS
    WHERE OBJECT_TYPE = 'TABLE'; -- Check number of tables
    num_tables := howmany; -- Compute another value
END;
/

--多行注释
\set SQLTERM /
DECLARE
    some_condition BOOLEAN;
    pi NUMBER := 3.1415926;  --pi
    radius NUMBER := 15;  --半径
    area NUMBER;  --可以存放浮点数
BEGIN
    /* 执行简单的测试和赋值 */
    IF 2 + 2 = 4 THEN
        some_condition := TRUE;
        /* 我们期望THEN后的语句总是被执行 */
    END IF;
    /* 
     * 根据pi和半径计算圆的面积 
     * 并将结果转换成字符串，在PUT_LINE中输出显示
     * TO_CHAR()将结果转换成字符串
     * */
    area := pi* radius* radius;
    DBMS_OUTPUT.PUT_LINE('The area is: ' || TO_CHAR(area));
END; 
/
--结果：
The area is: 706.858335

--注意空格和排版换行等
\set SQLTERM /
DECLARE
    x NUMBER := 10;
    y NUMBER := 5;
    max NUMBER;
BEGIN
    --IF x>y THEN max:=x;ELSE max:=y;END IF; -- 虽然正确，但是难以理解
    -- Easier to read:下面比较容易阅读
    IF x > y THEN
        max:=x;
    ELSE
        max:=y;
    END IF;
END;
/

--声明变量，可以赋值，但是本例中未赋值，使用:=赋值
\set SQLTERM /
DECLARE
    part_number NUMBER(6); -- SQL data type
    part_name VARCHAR2(20); -- SQL data type
    in_stock BOOLEAN; -- PL/SQL-only data type
    part_price NUMBER(6,2); -- SQL data type
    part_description VARCHAR2(50); -- SQL data type
BEGIN
    NULL;
END;
/

----声明常量，常量需要添加关键字CONSTANT，常量必须初始化
\set SQLTERM /
DECLARE
    credit_limit CONSTANT REAL := 5000.00; -- SQL data type
    max_days_in_year CONSTANT INTEGER := 366; -- SQL data type
    URBAN_LEGEND CONSTANT BOOLEAN := FALSE; -- PL/SQL-only data type
  --urban_legend1 CONSTANT BOOLEAN; -- 常量必须初始化，不初始化会报错
BEGIN
    NULL;
END;
/

--变量在未赋值情况下
\set SQLTERM /
DECLARE
    counter INTEGER; -- 未赋值的情况下，初始值是NULL
BEGIN
    counter := counter + 1; -- NULL + 1依然是 NULL
    IF counter IS NULL then  ---IS NULL 是个运算符
        DBMS_OUTPUT.PUT_LINE('counter is NULL.');
    END IF;
END;
/
--结果：
counter is NULL.

----------测试%TYPE属性------------

\set SQLTERM /
drop table if exists user_profile;
CREATE TABLE user_profile (
    id int NOT NULL,
    device_id int NOT NULL,
    gender varchar(14) NOT NULL,
    age int ,
    university varchar(32) NOT NULL,
    province varchar(32)  NOT null,
    gpa float
);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,'北京师范大学','BeiJing',3.3);
INSERT INTO USER_PROFILE VALUES(7,2240,'male',NULL,'清华大学','BeiJing',3.3);
INSERT INTO user_profile VALUES(8,2241,'female',null,'北京大学'，'BeiJing',3.7);
/
SELECT * FROM USER_PROFILE;
--
\set SQLTERM /
DECLARE
    id_test_TYPE user_profile.id%TYPE;
begin
    DBMS_OUTPUT.PUT_LINE('id_test=' || id_test_TYPE);
END;
/
--结果：
id_test=     --实测结果为空

--个人理解：id_test_TYPE的数据类型就是 user_profile表中id列的数据类型（%TYPE），即int;
--比如下列这个就是能赋值，且正常输出123的例子
\set SQLTERM /
DECLARE
    id_test_TYPE user_profile.id%TYPE; --定义为表id_test_TYPE变量为user_profile表的id烈的数据类型
begin
    id_test_TYPE := 123;
    DBMS_OUTPUT.PUT_LINE('id_test=' || id_test_TYPE);
END;
/
--或者声明变量后直接赋值
\set SQLTERM /
DECLARE
    id_test_TYPE user_profile.id%type := 123;  --或者写成这样，
begin
    DBMS_OUTPUT.PUT_LINE('id_test=' || id_test_TYPE);
END;
/
--结果
ID_TEST=123

---NOT NULL约束，即必须初始化，不能为空
DECLARE
  acct_id INTEGER(4) NOT NULL := 9999;
  a NATURALN                  := 9999;
  b POSITIVEN                 := 9999;
  c SIMPLE_INTEGER            := 9999;
BEGIN
  NULL;
END;
/
--声明的时候可以进行计算
DECLARE
  hours_worked    INTEGER := 40;
  employee_count  INTEGER := 0;

  pi     CONSTANT REAL := 3.14159;
  radius          REAL := 1;
  area            REAL := (pi * radius**2);
BEGIN
  NULL;
END;
/

-----------------------------------
-----------作用域和可见性

--变量的可见性：全局均可见，但局部覆盖全局的变量
--实际上和C语言是一样的
-- 最外部块:
DECLARE
  a CHAR;  -- Scope of a (CHAR) begins
  b REAL;    -- Scope of b begins
BEGIN
  -- 可见变量: a (CHAR), b
  
  -- 第一个子块:
  DECLARE
    a INTEGER;  -- Scope of a (INTEGER) begins
    c REAL;       -- Scope of c begins
  BEGIN
    -- 可见变量: a (INTEGER), b, c，局部的变量将覆盖全局的
    NULL;
  END;          -- Scopes of a (INTEGER) and c end

  -- 第二个子块:
  DECLARE
    d REAL;     -- Scope of d begins
  BEGIN
    -- 可见变量: a (CHAR), b, d
    NULL;
  END;          -- Scope of d ends

-- 可见变量: a (CHAR), b
END;            -- Scopes of a (CHAR) and b end
/

---这个案例说明，同名变量情况下局部的会覆盖全局的，
\set SQLTERM /
<<outer>>  -- label
DECLARE
  birthdate DATE := TO_DATE('09-AUG-70', 'DD-MON-YY');
BEGIN
  DECLARE
    birthdate DATE := TO_DATE('29-SEP-70', 'DD-MON-YY');
  BEGIN
    IF birthdate = outer.birthdate THEN
      DBMS_OUTPUT.PUT_LINE ('Same Birthday');
    ELSE
      DBMS_OUTPUT.PUT_LINE ('Different Birthday');
    END IF;
  END;
END;
/
--结果：
Different Birthday

----不能在同一个作用域进行变量的重复声明，所有的数据库对象都有该限制，
--PLS-00371: 'ID' 最多允许有一个声明

\set SQLTERM /
DECLARE
    id BOOLEAN;
    id VARCHAR2(5); -- duplicate identifier
BEGIN
    id := FALSE;
END;
/
--上述例子会报错

--演示了存储过程的创建和调用，存储过程的声明，函数的调用
--创建一个存储过程，接手一个参数，内部还创建了一个函数
CREATE OR REPLACE PROCEDURE CHECK_CREDIT (CREDIT_LIMIT NUMBER) 
AS
  rating NUMBER := 3;
  
  FUNCTION CHECK_RATING RETURN BOOLEAN --函数都是有返回值的吗？这里还处在存储过程的申明阶段
  IS --创建了一个函数，判断全局变量是否大于参数CREDIT_LIMIT，如果大于则局部赋值为CREDIT_LIMIT，且返回是否大于情况
    rating  NUMBER := 1;
    over_limit  BOOLEAN;
  BEGIN
    IF check_credit.rating <= credit_limit THEN  -- reference global variable引用全局变量
      over_limit := FALSE;
    ELSE
      OVER_LIMIT := TRUE;
      rating := credit_limit;                    -- reference local variable引用本地变量
    END IF;
    RETURN over_limit;
  END check_rating;
BEGIN
  IF check_rating THEN  --判断函数的结果，直接使用函数名即可调用
    DBMS_OUTPUT.PUT_LINE
      ('Credit rating over limit (' || TO_CHAR(credit_limit) || ').  '
      || 'Rating: ' || TO_CHAR(RATING));
  ELSE  --全局小于传进来的数值则输出下列
    DBMS_OUTPUT.PUT_LINE
      ('Credit rating OK.  ' || 'Rating: ' || TO_CHAR(rating));
  END IF;
END;
/
--执行存储过程需要包含下列三行
BEGIN
  check_credit(8);
END;
/
--如果想通过一行代码调用存储过程，可以用call
 CALL   CHECK_CREDIT(8);

--不要再同一个作用域声明变量两次，下列会报错
DECLARE
  id  BOOLEAN;
  id  VARCHAR2(5);  -- duplicate identifier
BEGIN
  id := FALSE;
END;
/
报错结果：
  id := FALSE;
  *
ERROR at line 5:
ORA-06550: line 5, column 3:
PLS-00371: at most one declaration for 'ID' is permitted
ORA-06550: line 5, column 3:
PL/SQL: STATEMENT IGNORED

---不同的子域中声明两次，是可以的
DECLARE
  PROCEDURE p  --存储过程还可以这样创建？？？
  IS
    x VARCHAR2(1);
  BEGIN
    x := 'a';  -- Assign the value 'a' to x
    DBMS_OUTPUT.PUT_LINE('In procedure p, x = ' || x);
  END;
 
  PROCEDURE q
  IS
    x VARCHAR2(1);
  BEGIN
    x := 'b';  -- Assign the value 'b' to x
    DBMS_OUTPUT.PUT_LINE('In procedure q, x = ' || x);
  END;
 
BEGIN
  p;
  q;
END;
/
结果
In procedure p, x = a
IN PROCEDURE Q, X = B

---在下面的例子中，echo即是块的名字，也是子模块的名字
--显然通过民国结果来看，不管是变量还是模块名字，局部都是会覆盖全局的
<<echo>>
DECLARE
  x  NUMBER := 5;
  
  PROCEDURE echo AS
    x  NUMBER := 0;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('x = ' || x);
    DBMS_OUTPUT.PUT_LINE('echo.x = ' || echo.x);
  END;
 
BEGIN
  echo;
END;
/
结果
x = 0
ECHO.X = 0

--标签的局部覆盖：
--
<<COMPUTE_RATIO>>
<<another_label>>--连个外部模块的标签
DECLARE
  numerator   NUMBER := 22;
  denominator NUMBER := 7;
BEGIN
  <<another_label>>
  DECLARE
    denominator NUMBER := 0;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Ratio with compute_ratio.denominator = ');
    DBMS_OUTPUT.PUT_LINE(numerator/compute_ratio.denominator);--这个是正确的的，因为它引用的是外部的denominator参数，这个值不是0
 
    DBMS_OUTPUT.PUT_LINE('Ratio with another_label.denominator = ');
    DBMS_OUTPUT.PUT_LINE(numerator/another_label.denominator);--这个会报错，因为引用的是内部的，这个值是0
 
  EXCEPTION
    WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('Divide-by-zero error: can''t divide '
        || numerator || ' by ' || denominator);
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Unexpected error.');
  END another_label;
END compute_ratio;
/
结果
Ratio with compute_ratio.denominator =
3.14285714285714285714285714285714285714
RATIO WITH ANOTHER_LABEL.DENOMINATOR =
Divide-by-zero error: cannot divide 22 by 0

------------------------------------------
-----给变量赋值的多种方式
------------------------------------------
---变量的赋值语句是：=
DECLARE  -- You can assign initial values here，也可以在生命模块赋值
  wages          NUMBER;
  hours_worked   NUMBER := 40;
  hourly_salary  NUMBER := 22.50;
  bonus          NUMBER := 150;
  country        VARCHAR2(128);
  counter        NUMBER := 0;
  done           BOOLEAN;
  valid_id       BOOLEAN;
  emp_rec1       employees%ROWTYPE;
  emp_rec2       employees%ROWTYPE;
  TYPE commissions IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  comm_tab       commissions;
 
BEGIN  -- You can assign values here too也可以在执行语句模块赋值
  wages := (hours_worked * hourly_salary) + bonus;
  country := 'France';
  country := UPPER('Canada');
  done := (counter > 100);
  valid_id := TRUE;
  emp_rec1.first_name := 'Antonio';
  emp_rec1.last_name := 'Ortiz';
  emp_rec1 := emp_rec2;
  comm_tab(5) := 20000 * 0.15;
END;
/

/*
--除了：=方式赋值，也可以使用下一种方式：即SELECT ......INTO....FROM.......
SELECT select_item [, select_item ]... 
INTO variable_name [, variable_name ]...
FROM TABLE_NAME;
*/

---例子：
DECLARE
  bonus   NUMBER(8,2);
BEGIN
  SELECT salary * 0.10 INTO bonus
  FROM employees
  WHERE employee_id = 100;
END;

DBMS_OUTPUT.PUT_LINE('bonus = ' || TO_CHAR(bonus));
/
上面这是ORACLE的例子，写一个自己的例子：
DECLARE
  resultval   NUMBER(8,2);
BEGIN
  SELECT gpa * 10 INTO resultval
  FROM USER_PROFILE
  WHERE GPA = 3.4;
  DBMS_OUTPUT.PUT_LINE('resultval = ' || TO_CHAR(RESULTVAL));
END;
--结果:
bonus = 34

--将值作为子程序的参数分配给变量
DECLARE
  emp_sal  NUMBER(8,2);
 
  PROCEDURE adjust_salary (  ---子程序adjust_salary的参数，用于调整薪水
    emp        NUMBER, 
    sal IN OUT NUMBER,
    adjustment NUMBER
  ) IS
  BEGIN
    sal := sal + adjustment;
  END;
 
BEGIN
  SELECT sal INTO emp_sal
  FROM employees
  WHERE empno = 7369;
 
  DBMS_OUTPUT.PUT_LINE('Before invoking procedure, emp_sal: ' || emp_sal);  --引用子程序之后
 
  adjust_salary (7369, emp_sal, 1000); --内部调用存储过程
 
  DBMS_OUTPUT.PUT_LINE('After invoking procedure, emp_sal: ' || emp_sal);  --引用子程序之后
END;
/
--select * from employees;

--结果
Before invoking procedure, emp_sal: 800
AFTER invoking PROCEDURE, emp_sal: 1800

-----------------------------
-------给布尔变量赋值
-----------------------------
DECLARE
  done    BOOLEAN;              -- Initial value is NULL by default
  counter NUMBER := 0;
BEGIN
  done := FALSE;                -- Assign literal value
  WHILE done != TRUE            -- Compare to literal value
    LOOP
      counter := counter + 1;
      done := (counter > 500);  -- Assign value of BOOLEAN expression
    END LOOP;
END;
/

------------------------------------------------------
------------------------------------------------------
---PLSQL控制语句-------------------------------------
------------------------------------------------------
------------------------------------------------------

--CASE的典型结构----根据选择器，
--选择器是一个表达式（通常是单个变量）
CASE selector
WHEN selector_value_1 THEN statements_1
WHEN selector_value_2 THEN statements_2
...
WHEN selector_value_n THEN statements_n
[ ELSE
  else_statements ]
END CASE;]

--一个简单的例子
--根据值进行简单比较，即根据表达式的结果是否匹配进行比较
DECLARE
  grade CHAR(1);
BEGIN
  grade := 'A';  --注意在字符中，A和a是不一样的
  --
  CASE grade
    WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
    WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
    WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
    WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
    ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
  END CASE;
END;
/
--The searched CASE statement（搜索CASE语句） runs the first statements for which condition is true. Remaining conditions are not evaluated. 
--If no condition is true, the CASE statement runs else_statements if they exist and raises the predefined exception CASE_NOT_FOUND otherwise. 
--(For complete syntax, see "CASE Statement".)
--是根据when的布尔表达式来判断是否要执行当前语句
CASE
WHEN condition_1 THEN statements_1
WHEN condition_2 THEN statements_2
...
WHEN condition_n THEN statements_n
[ ELSE
  else_statements ]
END CASE;]

--例子--根据搜索进行匹配
set serveroutput on
DECLARE
  grade CHAR(1);
BEGIN
  grade := 'B';
  
  CASE
    WHEN grade = 'A' THEN 
      DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN grade = 'B' THEN 
      DBMS_OUTPUT.PUT_LINE('Very Good');
    WHEN grade = 'C' THEN 
      DBMS_OUTPUT.PUT_LINE('Good');
    WHEN grade = 'D' THEN 
      DBMS_OUTPUT.PUT_LINE('Fair');
    WHEN grade = 'F' THEN 
      DBMS_OUTPUT.PUT_LINE('Poor');
    ELSE 
      DBMS_OUTPUT.PUT_LINE('No such grade');
  END CASE;
END;
/
---用异常表达代替ELSE与语句
DECLARE
  grade CHAR(1);
BEGIN
  grade := 'G';
  
  CASE
    WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
    WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
    WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
    WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
  END CASE;
EXCEPTION
  WHEN CASE_NOT_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No such grade');
END;
/

------------------------------------------
------------FOR循环------------------------
------------------------------------------
--下列例子说明，迭代i是不能在可见域进行值得修改的，即迭代变量不可修改。
BEGIN
  FOR i IN 1..3 LOOP
    IF i < 3 THEN
      DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));
    ELSE
      i := 2;
    END IF;
  END LOOP;
END;

--这个语句就是试图在FOR循环外边进行i索引的引用，然后会报错
BEGIN
  FOR i IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE ('Inside loop, i is ' || TO_CHAR(i));
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE ('Outside loop, i is ' || TO_CHAR(i));
END;
/ 
--If the index of a FOR LOOP statement has the same name as a variable declared in an enclosing block, 
--the local implicit declaration hides the other declaration, as this example shows.

DECLARE
  i NUMBER := 5;
BEGIN
  FOR i IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE ('Inside loop, i is ' || TO_CHAR(i));
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE ('Outside loop, i is ' || TO_CHAR(i));
END;
/

--要想在循环体中引用外部的i可以使用标签
<<main>>  -- Label block.
DECLARE
  i NUMBER := 5;
BEGIN
  FOR i IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE ('local: ' || TO_CHAR(i) || ', global: ' || TO_CHAR(MAIN.i)  -- Qualify reference with block label.
    );
  END LOOP;
END MAIN;
/
--使用标签了区分内部和外部相同的迭代i（循环体前是可以打标签的）
BEGIN
  <<outer_loop>>
  FOR i IN 1..3 LOOP
    <<inner_loop>>
    FOR i IN 1..3 LOOP
      IF outer_loop.i = 2 THEN
        DBMS_OUTPUT.PUT_LINE
          ('outer: ' || TO_CHAR(outer_loop.i) || ' inner: '
           || TO_CHAR(inner_loop.i));
      END IF;
    END LOOP inner_loop;
  END LOOP outer_loop;
END;
/

--结果
outer: 2 inner: 1
OUTER: 2 INNER: 2
outer: 2 inner: 3

/

/
------------------------------------------------------
------------------------------------------------------
---清理实验表等数据-------------------------------------
------------------------------------------------------
------------------------------------------------------
----创建实验表user_profile
\set SQLTERM /
drop table if exists user_profile;
CREATE TABLE user_profile (
    id int NOT NULL,
    device_id int NOT NULL,
    gender varchar(14) NOT NULL,
    age int ,
    university varchar(32) NOT NULL,
    province varchar(32)  NOT null,
    gpa float
);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,'北京师范大学','BeiJing',3.3);
INSERT INTO USER_PROFILE VALUES(7,2240,'male',NULL,'清华大学','BeiJing',3.3);
INSERT INTO USER_PROFILE VALUES(8,2241,'female',NULL,'北京大学'，'BeiJing',3.7);
/

---创建实验表（oracle和KES实测通过）

DROP TABLE DEPT;
CREATE TABLE DEPT(
  DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
    DNAME VARCHAR2(14) ,
    LOC VARCHAR2(13) 
);
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
--查询该表
SELECT * FROM dept;
--创建表EMP
DROP TABLE employees;
CREATE TABLE employees(
  EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
  
INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-1-1987','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-4-1987','dd-mm-yyyy'),1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
SELECT * FROM employees;

DROP TABLE BONUS;
CREATE TABLE BONUS
    (
    ENAME VARCHAR2(10)    ,
    JOB VARCHAR2(9)  ,
    SAL NUMBER,
    COMM NUMBER
    ) ;
SELECT * FROM BONUS;

DROP TABLE SALGRADE;
CREATE TABLE SALGRADE( 
  GRADE NUMBER,
    LOSAL NUMBER,
    HISAL NUMBER 
);
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
SELECT * FROM SALGRADE;