Oracle-流程控制

1.要求：向学生表中添加记录，值为并说明是否成功

DECLARE
	v_xm varchar2(8):='Jame';
  v_zym varchar2(10):='计算机';
  v_zxf number(2):=45;
BEGIN
  insert into XS(XH,XM,ZYM,ZXF) values('007',v_xm,v_zym,v_zxf);
  IF SQL%FOUND THEN 
    DBMS_OUTPUT.PUT_LINE('插入成功!');
  ELSE 
      DBMS_OUTPUT.PUT_LINE('插入失败!');
  END IF;
END;

2.要求：针对scott.emp表，计算7788号雇员的应交税金情况，薪金>=3000,应缴税金为薪金的0.08,薪金在1500和3000之间，应缴薪金的0.06，其它应缴薪金的0.04.

DECLARE
  v_sal SCOTT.EMP.SAL%TYPE;
  v_tax SCOTT.EMP.SAL%TYPE;
BEGIN
  SELECT SAL INTO v_sal FROM SCOTT.EMP WHERE empno=7788;
  IF v_sal >= 3000 THEN 
    v_tax:=v_sal*0.08;
  ELSIF v_sal >= 1500 THEN 
    v_tax:=v_sal*0.06;
  ELSE
    v_tax:=v_sal*0.04;
  END IF;
   DBMS_OUTPUT.PUT_LINE('应缴税金为->'||v_tax);
END;

3.要求：涉及表为scott.emp，输入一个员工号，修改该员工的工资，如果该员工为10号部门(deptno)，则要求工资增加100；若为20号部门，要求工资增加150；若为30号部门，工资增加200；否则增加300。

DECLARE
   v_deptno scott.emp.deptno%TYPE;
   v_zj NUMBER(4); 
BEGIN
  SELECT deptno INTO v_deptno FROM scott.emp WHERE empno = &&v_empno;
  IF v_deptno=10 THEN v_zj:=100;
  ELSIF v_deptno=20 THEN v_zj:=150;
  ELSIF v_deptno=30 THEN v_zj:=200;
  ELSE v_zj:=300;
  END IF;
  UPDATE SCOTT.EMP SET sal=sal+v_zj  WHERE empno=&v_empno;
END;

4.关于成绩等级制和百分制的相互转换，简单型CASE

DECLARE
 grade  varchar2(4):='良好';
BEGIN
 CASE grade
   WHEN '优秀'  THEN dbms_output.put_line('大于等于90');
   WHEN '良好'  THEN dbms_output.put_line('大于等于80，小于90');
   WHEN '及格'  THEN dbms_output.put_line('大于等于60，小于80');
   ELSE dbms_output.put_line('不及格');
 END CASE;
END;

DECLARE
   v_deptno scott.emp.deptno%type;
   v_increment NUMBER(4);
   v_empno  scott.emp.empno%type;
   v_sal scott.emp.sal%type;
BEGIN
  v_empno:=&x;
 SELECT deptno INTO v_deptno FROM scott.emp WHERE empno=v_empno;
  CASE v_deptno  
    WHEN 10 THEN v_increment:=100;
    WHEN 20 THEN v_increment:=150;
    WHEN 30 THEN v_increment:=200;
    ELSE  v_increment:=300;
END CASE;
UPDATE scott.emp SET sal=sal+v_increment WHERE empno=v_empno; 
IF SQL%FOUND THEN 
  dbms_output.put_line('更改成功');
  select sal into v_sal from scott.emp where empno='7788';
  dbms_output.put_line(v_sal);
END IF;
END; 

6.关于成绩等级制和百分制的相互转换。搜索case表达式

declare
    score int:=91;
begin
    case 
      when score>=90 then dbms_output.put_line('优秀');
      when score>=80 then dbms_output.put_line('良好');
      when score>=60 then dbms_output.put_line('及格');
    else dbms_output.put_line('不及格');
    end case;
end;

select  xh,xm,zxf,
(
case
    when zxf>50 then '高'
    when zxf>=40 then '中'
    else '学分不够，需继续'
end
) as 获得学分情况  from xs;

注意事项：
     1.  整个case 语句没有标点符号
     2.  case 语句的结束用end 而非end case
     3.  then 之后没有dbms_output.put_line().
     4.  as 之后无引号.

select Months_Between(sysdate,to_date('20151001','yyyymmdd')) from dual;

select trunc(sysdate-to_date('20181001','yyyymmdd')) 天数 from dual;
# trunc用于截断字符串 不是四舍五入 而是直接截断

10.检验图书是否过期：

select empno,ename,job,hiredate,
(case
    when trunc(sysdate-HIREDATE)>360  then '过期'
    when hiredate is null then '没借书'
    else  '没过期'
end) 
   as 是否过期 from scott.emp;

11.求10的阶乘

DECLARE 
      s NUMBER:=1;
	    n  NUMBER:=2;
BEGIN
		LOOP
		        s:=s*n;
		        n:=n+1;
		        EXIT WHEN n>10;
		END LOOP;
		dbms_output.put_line(to_char(s));
END;

12.用WHILE-LOOP-END循环结构求10的阶乘

DECLARE 
      s NUMBER:=1;
	    n  NUMBER:=2;
BEGIN
    WHILE n<=10
		LOOP
		        s:=s*n;
		        n:=n+1;
		END LOOP;
		dbms_output.put_line(to_char(s));
END;

13.用FOR-IN-LOOP-END循环结构求10的阶乘

DECLARE 
     s NUMBER:=1;
	   n NUMBER:=1;
BEGIN
		for n in 1..10
    Loop
		      s:=s*n;
		end loop;
		dbms_output.put_line(to_char(s));
END;

14.For 循环中的逆序

DECLARE 
     s NUMBER:=1;
	   n NUMBER:=2;
BEGIN
  for n in reverse 1..10
		loop
      s:=s*n;
      end loop;
		dbms_output.put_line(to_char(s));
END;