pl/sql习题加练习

1、100内的素数

declare
  fag BOOLEAN := TRUE;
begin
  FOR i IN 1..100 LOOP
  FOR k IN 2..i-1 LOOP
  IF  MOD(i,k)=0 THEN
  fag := FALSE;
  END IF;
  END LOOP;
  IF fag THEN
  dbms_output.put_line(i);
  END IF;
  fag := TRUE;-------关键的部分在这边，这里的for循环只有两个功能
  END LOOP;
 
end;
————————————————————————————————————————————————————————

declare
  fag BOOLEAN := TRUE;
begin
  FOR i IN 1..100 LOOP
  FOR k IN 2..i-1 LOOP
 -- IF  MOD(i,k)!=0 THEN
  --dbms_output.put_line(i);
  --END IF;
  IF MOD(i,k)=0 THEN
  fag := FALSE;
  END IF;
 END LOOP;
 IF fag THEN
 dbms_output.put_line(i);
 END IF;
 fag := TRUE;
  END LOOP;
 
end;

2、九九乘法口诀

declare
 
begin
  FOR i IN 1..9 LOOP
  FOR j IN 1..9 LOOP-----------注意连接符号是||
  IF j <= i THEN
  dbms_output.put(i||'*'||j||'='||i*j||'  ');
  END IF;
  END LOOP;
  dbms_output.put_line('');
  END LOOP;
 
end;

3、/*对所有员工，如果给员工是manager，并且工作地点在dallas，
那么就给他薪金加15%，
如果该员工是clerk，并且在new york工作，
那么就给他的薪金扣除5%，其他情况不做处理*/
declare
  CURSOR c1 IS SELECT * FROM emp;
 -- c1rec c1%ROWTYPE;可以省略
  v_loc varchar2(20);
BEGIN--------for循环不需要打开和关闭游标，for 循环更可方便的处理显示游标，隐式地
-----打开，提取和关闭游标，隐式声明记录类型标量，另外使用子查询时不用声明游标
     FOR c1rec IN c1 LOOP
     SELECT loc INTO v_loc FROM dept WHERE deptno=c1rec.deptno;
     IF c1rec.job='MANAGER' AND v_loc = 'DALLAS' THEN
     UPDATE emp SET sal=sal*1.15 WHERE empno = c1rec.empno;
     ELSIF c1rec.job='CLERK' AND v_loc = 'NEW YORK' THEN
     UPDATE emp SET sal=sal*0.95 WHERE empno = c1rec.empno;
     ELSE
     NULL;
     END IF;
     END LOOP;
 
end;

4、/*对直接上级是‘BLAKE的所有员工，按照参加工作的时间加薪：
81年6月以前加薪10%，81年6月以后加薪5%’*/
declare
  CURSOR cur IS
  SELECT sal,hiredate,empno
  FROM emp
  WHERE emp.mgr =
  (SELECT  empno FROM emp WHERE ename='BLAKE');
begin
 
  FOR rec IN cur LOOP
  IF rec.hiredate <'01-6月-81' THEN
  UPDATE emp SET sal = rec.sal *1.1
  WHERE emp.empno = rec.empno;
  ELSE
  UPDATE emp SET sal = rec.sal *1.05
  WHERE emp.empno = rec.empno;
  END IF;
  END LOOP;
end;

5、/*根据员工在各自部门中的工资高低排出在各个部门中的名次
（允许并列）。
一条sql语句    SELECT deptno,ename,sal
  FROM emp
  --GROUP BY deptno,ename,sal这个没有必要
  ORDER BY deptno,sal DESC
 
  答案：select deptno,ename,sal,
  (select count(*)+1 ----这个是重点
  from emp
  where deptno = a.deptno and sal>a.sal) as ord
  from emp a
  order by deptno,sal desc       */
declare
        CURSOR cur IS
        select deptno,ename,sal,
         (select count(*)+1 ----这个是重点
         from emp
          where deptno = a.deptno and sal>a.sal) as ord
          from emp a
          order by deptno,sal desc   ;
       
BEGIN
     FOR rec IN cur LOOP
     dbms_output.put_line(rec.deptno||','||
                          rec.ename||','||
                          rec.sal||','||
                          rec.ord);
     END LOOP;
 
end;

6、/*编写一个触发器，实现如下功能
对修改职工薪金的操作进行合法性的检查：
a)修改后的薪金要大于修改前的薪金
b)工资增量不能超过原工资的10%
c)目前没有单位的职工不能涨工资*/
CREATE OR REPLACE TRIGGER tr1
AFTER UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
      IF :NEW.sal<:OLD.sal THEN
      raise_application_error(-20001,
      '修改后的薪金要大于修改前的薪金');
      ELSIF :NEW.sal>:OLD.sal*1.1 THEN
      raise_application_error(-20002,
      '工资增量不能超过原工资的10%');
      ELSIF :OLD.deptno IS NULL THEN
      raise_application_error(-20003,
      '目前没有单位的职工不能涨工资');
      END IF;
      END;

7、/*编写一个pl/sql程序块，对名字以‘A’或者‘S’开始的所有雇员
按照他们的基本薪水10%加薪*/
declare
  CURSOR cur IS
  SELECT * FROM emp;
BEGIN
     FOR rec IN cur LOOP
  IF rec.ename LIKE 'A%' OR rec.ename LIKE 'S%' THEN
  UPDATE emp SET sal = sal *0.1 WHERE empno=rec.empno;
  END IF;
  END LOOP;
 
end;

8、/*编写一个pl/sql程序块，对所有的"销售员"（salesman）增加
佣金500*/
declare
  CURSOR cur IS
  SELECT * FROM emp
  WHERE emp.job='SALESMAN'
  FOR UPDATE OF sal;------------
begin
  FOR i IN cur LOOP
  UPDATE emp SET sal=NVL(sal,0) -----------检查空值问题
  WHERE CURRENT OF cur;-------------
  END LOOP;
end;

9、/*编写一个pl/sql，以提升两个资格最老的‘职员’(clerk)。
工资越高资历越老*/
declare
      CURSOR cur IS
      SELECT * FROM emp
      WHERE job='CLERK'-------
      ORDER BY hiredate ;
      rec emp%ROWTYPE;
BEGIN
     OPEN cur;
     FETCH cur INTO rec;
     dbms_output.put_line(rec.empno||
                          rec.ename||
                          rec.hiredate);
     FETCH cur INTO rec;
     dbms_output.put_line(rec.empno||
                          rec.ename||
                          rec.hiredate);
    CLOSE cur;
 
end;

10、/*编写一个pl/sql,对所有雇员按他们薪水的10%加薪，
如果所增加的薪水大于5000，则取消加薪*/
declare
  CURSOR cur IS
  SELECT * FROM emp
  FOR UPDATE OF sal;----注意是sal,不是游标
begin
  FOR i IN cur LOOP
  IF (i.sal+i.sal*0.1) <=5000 THEN--i.sal > 0 AND i.sal < 5000
  UPDATE emp SET sal = sal+sal*0.1
  WHERE empno = i.empno;
 -- ELSE
  --NULL;
  END IF;
 
  END LOOP;
 
end;

11、--/显示emp表中的第四条记录/
declare
  CURSOR cur IS
  SELECT *
  FROM emp;
 -- FOR UPDATE OF ename;
 -----如果有这一项代表，更新或删除需要commit
begin
  FOR i IN cur LOOP
  IF cur%ROWCOUNT = 4 THEN
  dbms_output.put_line(i.ename);
  EXIT;
  END IF;
  END LOOP;
 
end;

12、

/*编写一个函数以检查所指定雇员的薪水是否在有效范围内，
不同职位的薪水范围是：
clerk   1500-2500
salesman 2501-3500
analyst 3501-4500
others 4501 and above
如果薪水在指定范围内，则显示信息”salary is ok“,
否则怎更新薪水为该范围内的最水低值*/
create or replace function salleve (
             no emp.empno%TYPE
                                     )
return CHAR
 IS vjob emp.job%TYPE;
    vsal emp.sal%TYPE;
    vmesg CHAR(50);
 -- Result ;
begin
  SELECT job,sal
  INTO vjob,vsal
  FROM emp
  WHERE empno = no;
 -- return(Result);
  IF vjob='CLERK' THEN
  IF vsal>=1500 AND vsal<=2500 THEN
  vmesg :='Salary is OK';
  ELSE
  vsal := 1500;
  vmesg :='Have updated your salary to'||to_char(vsal);
  END IF;
  ELSIF vjob='Salesman' THEN
  IF vsal>=2501 AND vsal<=3500 THEN
  vmesg :='Salary is OK';
  ELSE
  vsal := 2501;
  vmesg :='Have updated your salary to'||to_char(vsal);
  END IF;
  ELSIF vjob='Analyst' THEN
  IF vsal>=3501 AND vsal<=4500 THEN
  vmesg :='Salary is OK';
  ELSE
  vsal := 3501;
  vmesg :='Have updated your salary to'||to_char(vsal);
  END IF;
  ELSIF vjob='Others' THEN
  IF vsal>=4501 THEN
  vmesg :='Salary is OK';
  ELSE
  vsal := 4501;
  vmesg :='Have updated your salary to'||to_char(vsal);
  END IF;
  END IF;
  UPDATE emp SET sal = vsal
  WHERE empno = no;
  RETURN vmesg;
end salleve;
/*declare
  vmesg varchar2(50);
  --vempno emp.empno%TYPE;
BEGIN
     --vempno := &empno;
  vmesg := salleve(7369);
  dbms_output.put_line(vmesg);
end;

SELECT empno,ename,sal,comm,hiredate
FROM emp
WHERE empno = :NO*/
/

13/*一句sql 日期生产：1、 3.3333
                    2、 4.2222
                    3、 1.5555
                    4、 9.8888
                    ....
 要求用sql语句查询，产生如下效果： 
 日期生产：
 1、 3.3333        3.3333
 2、 4.2222        7.5555
 3、 1.5555        9.0000
 4、 9.8888        18.8888
 ....                               */
 SELECT id,quantity,(SELECT (quantity)
                     FROM mytable
                     WHERE ID<=t.ID)AS acount
                     FROM mytable t

14/*创建一个序列，第一次从5循环到10，以后再从0开始*/
CREATE SEQUENCE test_seq
       START WITH 5
       INCREMENT BY 1
       MAXVALUE 10
       MINVALUE 0
       CYCLE
       Nocache
15、create or replace procedure test_avg (
       pro_sal IN NUMBER := 0,------声明参数
       pro_mes OUT VARCHAR2
        )
IS
   v NUMBER;----声明变量
BEGIN
   
    SELECT AVG(sal)
    INTO v
    FROM emp;
    IF  pro_sal IS NOT NULL THEN
    IF pro_sal > v THEN
    pro_mes := '工资还不错';
    ELSE
    pro_mes := '加油努力';
  END IF;
  END IF;
end test_avg;
/