Oracle第九课课后作业

一、作业

输出100以内的质数

-- 定义3个变量
-- i:外循环，循环2到100的数
-- j：内循环，判断从2到i-1是否能被整除
-- flag：是否是素数的标志，执行完一次内循环，判断flag的值，如果内循环中有被整除的，flag变为0，若没有，flag值为1，输出i
declare 
  -- Local variables here
  i integer;
  j INTEGER;
  flag INTEGER;
begin
  -- Test statements here
  i:=2;
  j:=2;
  FOR i IN 2..100 LOOP
    flag:=1;
    FOR j IN 2..i-1 LOOP
      IF MOD(i,j)=0 THEN
        flag:=0;
        EXIT;
        END IF;
      END LOOP;
      IF flag=1 THEN
      dbms_output.put_line(i);
      END IF;
    END LOOP;
end;

建一张表，t_money(id,name,money),money是收入，1万以内，分段显示：0-1500属于贫困，1501-3000是蓝领,3001-8000是白领,8001-10000是精英

select case when end 来实现

CREATE TABLE t_money(ID NUMBER(2),NAME VARCHAR(8),money NUMBER(6));
CREATE SEQUENCE s1 MINVALUE 0 START WITH 0;
INSERT INTO t_money VALUES(s1.nextval,&n,&m);
SELECT NAME,CASE
WHEN money BETWEEN 0 AND 1500 THEN '贫困'
  WHEN money BETWEEN 1501 AND 3000 THEN '蓝领'
    WHEN money BETWEEN 3001 AND 8000 THEN '白领'
      WHEN money BETWEEN 8001 AND 10000 THEN '精英'
END
FROM t_money;

写一个程序，找出上述t_money表中有多少条记录，并显示工资最高的人的信息，排序再过滤，结果存储到变量

declare 
  -- Local variables here
  i integer;
  -- 行变量的定义
  v_userrow t_money%ROWTYPE;
begin
  -- Test statements here
  SELECT COUNT(*) INTO i FROM t_money;
  SELECT id,NAME,money INTO v_userrow.id,v_userrow.name,v_userrow.money FROM
  (SELECT * FROM t_money ORDER BY money DESC) WHERE rownum=1;
  dbms_output.put_line(i);
  dbms_output.put_line('工资最高人的信息:'||v_userrow.id||v_userrow.name||v_userrow.money);
end;

二、in和exists

执行顺序
- in:由内而外，先执行子查询，将子查询作为结果集，再执行外查询
- exists：由外而内，相当于loop循环，先从外表取出一条记录，然后进入子查询看这条记录是否符合条件，若符合，返回true,否则返回false，然后继续判断下一条记录
例题
- 员工表employee,有2个字段salary , deptid ,查询所有数据,按照部门号从高到低,工资从低到高顺序输出
```
SELECT * FROM employee ORDER BY deptid DESC,salary;
```
- 员工表employee,有2个字段salary , deptid ,查询各个部门中高于所有员工平均工资的人数:查询的结果列为: 部门,人数
```
SELECT deptid 部门,COUNT(*) 人数 FROM employee WHERE salary>(
SELECT AVG(salary) FROM employee) GROUP BY deptid;
```
- 员工表employee,有2个字段salary , deptid ,查询出最高工资和最低工资的差是多少
```
SELECT MAX(salary)-MIN(salary) FROM employee;
```
- 用exists替换下面的sql中的in SELECT * FROM tb1 WHERE u_id IN (SELECT u_id FROM tb2 WHERE name = ‘张三’);
```
SELECT * FROM tb1 WHERE EXISTS (
SELECT * FROM tb2 WHERE NAME='张三' AND tb1.u_id=tb2.u_id);
```
当子查询返回的结果集很少时，采用in比较好；

当子查询返回的结果集很多时，采用exists比较好

三、PL/SQL编程

区域：在Test window进行编程

组成：

declare部分进行一些变量的定义

begin end;部分里面写执行程序

declare 
  -- Local variables here
  -- 进行一些变量的定义
  i integer;
begin
  -- Test statements here
  -- 执行程序在这里编写
  
end;

变量定义

直接定义字段类型
```
  i integer;
  v_username VARCHAR(10);
```
常量的定义
```
 pai CONSTANT NUMBER := 3.14;
```
字段类型来源于表中
```
v_empno emp.empno%TYPE;
```
行变量的定义
```
 v_emprow emp%ROWTYPE;
```

变量的赋值（用:=进行赋值）

-- 在定义变量时进行赋值，或者在使用前进行赋值
  v_clazz NUMBER:=11;
  v_college NUMBER(10) DEFAULT 1;
-- 在查询时进行赋值:将查询到的最高工资赋值给i
SELECT MAX(sal) INTO i FROM emp;
-- 行变量的赋值
SELECT emp.empno,emp.ename INTO v_emprow.empno,v_emprow.ename FROM emp WHERE rownum=1;

-- select只能查询到一条记录

输出

dbms_output.put():输出到缓存

dbms_output.put_line()：将缓存中的和要输出的进行输出

dbms_output.put(1);
dbms_output.put_line(2);
 dbms_output.put_line(v_username||'-'||v_clazz||'-'||v_college);

异常处理：exception

-- sqlcode:异常编号，sqlerrom:异常信息

-- 捕获异常
  EXCEPTION
    WHEN no_data_found THEN 
      dbms_output.put_line('没有数据');
    WHEN too_many_rows THEN
      dbms_output.put_line('返回太多行');
    WHEN OTHERS THEN -- 对未知异常的处理
      dbms_output.put_line(SQLCODE||'-'||SQLERRM);

选择语句(在编程窗口执行)：IF ELSIF

-- 成绩大于小于60为没有通过，大于60通过
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;
end;

选择语句(在SQL窗口执行): 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;

for循环

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;

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;

软件下载提取码：qwer