Oracle第九课
一、in,exists的用法
-
in:在一个范围内
select * from emp where empno in (
select empno from emp where deptno=10
)
-- 编号和姓名重复
delete from emp where empno not in (
select min(empno) from emp group by empno,ename)
) -
exists比in效率高
-
主子表的存在关系，返回的是true,false
-
SELECT * FROM emp o WHERE EXISTS (SELECT
empno FROM emp i WHERE o.empno=i.empno AND deptno=10); -
where exists(结果)
-
-
作业
CREATE TABLE team(NAME CHAR(1));
INSERT INTO team VALUES(&a);
-- 显示所有的可能结果
-- ab,ac,ad,ae bc,bd,be cd,ce de
SELECT * FROM team;
SELECT t1.*,t2.* FROM team t1,team t2 WHERE t1.name<t2.name;
二、PL/SQL编程
-
test window
-
declare:定义变量,变量要提前定义，只能在定义区定义
-
-
begin是SQL主体
-
输出：dbms包里面有put_line和put方法
-
put:输出到缓存
-
put_line:把缓存中的数据一起输出
-
-
简单示例
declare
-- Local variables here
-- 只能在这里定义变量
i integer;
begin
-- Test statements here
dbms_output.put(1);
dbms_output.put_line(2);
end; -
变量的定义和赋值
-- Created on 2021/1/25 by 96093
declare
-- Local variables here
-- 只能在这里定义变量
i integer;
v_username VARCHAR(10);
v_clazz NUMBER:=11;
v_college NUMBER(10) DEFAULT 1;
pai CONSTANT NUMBER := 3.14;
BEGIN
-- =只是比较的功能，不能赋值，赋值要加：=
username:='张三';
-- Test statements here
dbms_output.put(1);
dbms_output.put_line(2);
dbms_output.put_line(v_username||'-'||v_clazz||'-'||v_college);
END; -
把sql的结果放到变量中
declare
-- Local variables here
i integer;
-- 数据类型来自于字段的类型，随着表字段改变而改变
-- 始终保持一致
v_empno emp.empno%TYPE;
begin
-- Test statements here
-- 工资最低的员工编号,把sql的结果放到变量中
SELECT empno INTO v_empno FROM (SELECT empno FROM emp ORDER BY sal DESC) WHERE ROWNUM=1;
dbms_output.put_line('最高工资'||v_empno);
end; -
行变量定义
v_emprow emp%ROWTYPE; -- 行记录类型变量，数据结构
dbms_output.put_line('最高工资'||v_emprow.empno||'-'||v_emprow.ename); -
select...into...应该只能得到一条记录
-
异常处理
-- 捕获异常
EXCEPTION
-- others 包罗万象
-- sqlcode:异常编号 sqlerrom：异常信息
WHEN no_data_found THEN -- oracle内部预定义的异常名称
dbms_output.put_line('没有数据');
WHEN too_many_rows THEN
dbms_output.put_line('返回太多行');
WHEN OTHERS THEN -- 对未知异常的处理
dbms_output.put_line(SQLCODE||'-'||SQLERRM); -
IF ELSIF
-- Created on 2021/1/25 by 96093
declare
-- Local variables here
i integer;
begin
-- Test statements here
i:=60;
IF i<60 THEN
dbms_output.put_line('no pass');
ELSE
dbms_output.put_line('pass');
END IF;
/* IF i<60 THEN
dbms_output.put_line('no pass');
ELSIF i<80 THEN
dbms_output.put_line('普通');
ELSIF i<90 THEN
dbms_output.put_line('良好');
ELSE
dbms_output.put_line('优秀');
END IF;*/
end; -
随机数
-- Created on 2021/1/25 by 96093
declare
-- Local variables here
v_score INTEGER;-- 成绩
i integer;
begin
-- Test statements here
DELETE FROM t_score;-- 测试前先删除表数据
FOR i IN 1..10 LOOP -- 2. for循环
v_score:=abs(mod(dbms_random.random,100)); -- 1. 随机数
INSERT INTO t_score(NAME,score) VALUES('name'||seq1.nextval,v_score);
END LOOP;
COMMIT;
dbms_output.put_line(i);
end; -
case when
SELECT score,CASE
WHEN score BETWEEN 0 AND 59 THEN '不及格'
WHEN score BETWEEN 60 AND 80 THEN '普通'
WHEN score BETWEEN 80 AND 90 THEN '良好'
WHEN score BETWEEN 90 AND 100 THEN '优秀'
END
FROM t_score;
-- 第二个示例
SELECT CASE
WHEN score BETWEEN 1 AND 60 THEN 'no pass'
ELSE 'pass'
END
FROM t_score; -
表里有多少条数据
SELECT COUNT(*) INTO i FROM t_score;
dbms_output.put_line(i); -
while循环
-- 变量定义时为初始化为null，无法进行比较
declare
-- Local variables here
i INTEGER;
BEGIN
IF i IS NULL THEN
dbms_output.put_line('i is not init');
i:=1;
ELSE
IF i<10 THEN
dbms_output.put_line('i<10');
ELSE
dbms_output.put_line('i>=10');
-- Test statements here
WHILE i<10 LOOP
i:=i+1;
dbms_output.put_line(i);
END LOOP;
end; -
loop end loop
declare
-- Local variables here
i integer;
begin
-- Test statements here
/*
for ... loop
end loop
while loop
end loop
*/
LOOP
IF i IS NULL THEN
i:=0;
ELSIF i<10 THEN
i:=i+1;
ELSE
EXIT; -- 退出循环
END IF;
dbms_output.put_line(i);
END LOOP;
end;
-- 示例2
declare
-- Local variables here
i integer;
begin
-- Test statements here
i:=0;
LOOP
i:=i+1;
EXIT WHEN i>10;
dbms_output.put_line(i);
END LOOP;
end;
三、作业
-
输出100以内的质数
-
建一张表，t_money(id,name,money),money是收入，1万以内，分段显示：0-1500属于贫困，1501-3000是蓝领,3001-8000是白领,8001-10000是精英
select case when end 来实现
-
写一个程序，找出上述t_money表中有多少条记录，并显示工资最高的人的信息，排序再过滤，结果存储到变量