----------------------------
----------plsql的语言基础
-------------------------------一个基础的plsql块，衍生的对象有函数，存储过程，匿名块等
<< LABEL >> --(optional)可选
DECLARE -- Declarative part (optional)--可选
-- Declarations of local types, variables, & subprogramsBEGIN -- 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;本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
