二十三、Oracle学习笔记:综合案例
发布日期:2021-07-26 07:20:45 浏览次数:3 分类:技术文章

本文共 9556 字,大约阅读时间需要 31 分钟。

--查看所有雇员的工资

--view(当用户输入view后提示用户输入查看第几页,每页显示几条记录,排序关键列)

create or replace package pack1 istype my_cursor_type is ref cursor;end;create or replace procedure fenye(v_pagenow in number,v_pagesize in number,v_field in varchar2,v_out_res out  pack1.my_cursor_type)isv_sql varchar2(2000);v_start number;v_end number;begin  v_start:=v_pagesize*(v_pagenow-1)+1;  v_end:=v_pagesize*v_pagenow;  v_sql:='select t2.* from (select t1.*,rownum rn from ( select * from emp order by '||v_field||')t1 where rownum<='||v_end||')t2 where rn>='||v_start;  open v_out_res for v_sql;  end;

  

--案例:请编写一个函数,可以接收用户名并返回该用户的年薪.

create function fun1(v_ename varchar2) return number isv_annual_sal number;begin  select (sal+nvl(comm,0))*12 into v_annual_sal from emp where ename=v_ename;  return v_annual_sal;  end;

--请编写一个包,该包有一个过程,该过程可以接收用户名和新的薪水,
--(将来用于通过用户名去更新薪水)还有一个函数,该函数可以接收一个用户名(将来要实现得到该用户的年薪是多少)

create package pack1 isprocedure pro1(v_ename varchar2,v_new_sal number);function fun1(v_ename varchar2) return number;end;create package body pack1 isprocedure pro1(v_ename varchar2,v_new_sal number) isbegin    update emp set sal=v_new_sal where ename=v_ename;end;function fun1(v_ename varchar2) return number isv_annual_sal number;begin    select (sal+nvl(comm,0))*12 into v_annual_sal from emp where ename=v_ename;    return v_annual_sal;end;end;

---案例:以输入员工号,显示雇员姓名、工资、个人所得税(税率为0.03)为例。说明变量的使用,看看如何编写.

create or replace procedure pro2(v_empno in number)isv_ename emp.ename%type;v_sal emp.sal%type;v_tax number;c_tax_rate constant number :=0.03;begin  select ename,sal into v_ename,v_sal from emp where empno=v_empno;  v_tax:=v_sal*c_tax_rate;  dbms_output.put_line('姓名:'||v_ename||'工资:'||v_sal||'所得税:'||v_tax);  end;

  
  --请编写一个过程,该过程可以接收一个用户编号,并显示该用户的名字,薪水,工作岗位(注意:要求用pl/sql记录实现)
  

  create or replace procedure pro3(v_empno number) is  /*type emp_rec_type is record(  v_ename emp.ename%type,  v_sal emp.sal%type,  v_job emp.job%type  );  emp_record emp_rec_type;*/  emp_row emp%rowtype;  begin    select * into emp_row from emp where empno=v_empno;    dbms_output.put_line('名字:'||emp_row.ename||'薪水:'||emp_row.sal||'工作岗位:'||emp_row.job);    end;    

---请使用pl/sql编写一个过程,可以输入部门号,并显示该部门所有员工姓名和他的工资.
--用游标实现

create or replace procedure pro4(v_deptno number)is cursor emp_cursor is select ename,sal from emp where deptno=v_deptno;v_ename emp.ename%type;v_sal emp.sal%type;begin  open emp_cursor;  loop  fetch emp_cursor into v_ename,v_sal;  exit when emp_cursor%notfound;  dbms_output.put_line('姓名:'||v_ename||'工资:'||v_sal);  end loop;  close emp_cursor;  end;

--用游标变量实现

create or replace procedure pro4(v_deptno number)is cursor emp_cursor is select ename,sal from emp where deptno=v_deptno;v_ename emp.ename%type;v_sal emp.sal%type;begin  open emp_cursor;  loop  fetch emp_cursor into v_ename,v_sal;  exit when emp_cursor%notfound;  dbms_output.put_line('姓名:'||v_ename||'工资:'||v_sal);  end loop;  close emp_cursor;  end;

--?编写一个过程,可以输入一个雇员名,如果该雇员的工资低于2000,就给该雇员工资增加10%

create procedure pro1(v_ename varchar2) isv_sal emp.sal%type;begin  select sal into v_sal from emp where ename=v_ename;  if v_sal<2000 then    update emp set sal=sal*1.1 where ename=v_ename;    end if;  end;

  
  --?编写一个过程,可以输入一个雇员名,如果补助为0就把补助设为200;如果该雇员的补助不是0就在原来的基础上增加100;
  
 

create procedure pro2(v_ename varchar2) is  v_comm emp.comm%type;  begin    select comm into v_comm from emp where ename=v_ename;    if v_comm=0 then      update emp set comm=200 where ename=v_ename;      elsif v_comm=null then                else        update emp set comm=comm+100 where ename=v_ename;        end if;    end;

    
    --?编写一个过程,可以输入一个雇员编号,如果该雇员的职位是PRESIDENT 就给他的工资增加1000,如果该雇员的职位是MANAGER就给他的工资增加500,其它职位的雇员工资增加200.
    
 

  create procedure pro3(v_empno number) is    v_job emp.job%type;    begin      select job into v_job from emp where empno=v_empno;      if v_job='PRESIDENT' then        update emp set sal=sal+1000 where empno=v_empno;        elsif v_job='MANAGER' then          update emp set sal=sal+500 where empno=v_empno;          else            update emp set sal=sal+200 where empno=v_empno;            end if;      end;

      
--请,编写一个过程,可输入用户名,和添加用户的个数n;循环添加n个用户到users表中,用户编号从1开始增加,直到n.

      create table users(id number primary key,name varchar2(32));            create or replace procedure pro4(v_name varchar2,v_n number) is      v_id number :=11;      begin        loop          insert into users values(v_id,v_name);          exit when v_id>=11+v_n;          v_id:=v_id+1;          end loop;          dbms_output.put_line('here');        end;        create or replace procedure pro1(name varchar2) isv_test varchar2(40) ;begin  v_test:='aaa';dbms_output.put_line(v_test);end;create or replace procedure pro1(name out varchar2) isv_test varchar2(40):='aaa';beginname:='你好';dbms_output.put_line(v_test);end;begin    for i in reverse 1..10 loop    dbms_output.put_line('i='||i);    end loop;end;/

--编写一个过程,可以输入雇员的编号,返回该雇员的姓名。

create or replace procedure pro5(v_empno in number,v_ename out varchar2)isbegin  select ename into v_ename from emp where empno=v_empno;  end;

--案例:编写一个过程,输入部门号,返回该部门所有雇员信息。 

--1.定义一个包,把游标变量类型放在包里

create package mypack1 istype v_cursort_type is ref cursor;end;create or replace procedure pro6(v_deptno in number,v_cursor out mypack1.v_cursort_type) isbegin  open v_cursor for select * from emp where deptno=v_deptno;  -不要关闭  end;

使用pl/sql块编程实现,注意必要的异常处理

1.输入一个员工号,输出该员工的姓名,薪金,和大概的服务年限(按年月日显示)

-- 求年份select ceil(months_between(sysdate,date'1996-12-18')/12) as year from dual;--select to_char(to_date('00010101','yyyymmdd')+(sysdate-hiredate)-366-31,'yyyy-mm-dd') from emp;求出服务年限/*首先: sysdate - hiredate 是算出员工服务的总天数  to_date('00010101', 'yyyymmdd') 是 0001 年01月01日  to_date('00010101','yyyymmdd')+(sysdate-hiredate): 把它们加起来是为了把天数转换为日期格式:年/月/日  因为之前为了转换为日期格式而多添加1年1一个月1天,所以为了准确必须要减掉多出来的年月日转换的天数.  366: 是之前有添加0001(一年366天)  31: 01月(一个月31天)*/ create or replace procedure pro1(v_empno in number)isv_ename emp.ename%type;v_sal emp.sal%type;v_date varchar2(16);begin  select ename,sal,to_char(to_date('00010101','yyyymmdd')+(sysdate-hiredate)-366-31,'yyyy-mm-dd') into v_ename,v_sal,v_date from emp where empno=v_empno;  dbms_output.put_line('姓名:'||v_ename||',工资:'||v_sal||',服务年限'||v_date);  end;-- 执行语句:exec pro1(1234);

2.接收一个员工号,输出该员工所在部门的名称

create or replace procedure pro2(v_empno in number) isv_deptno emp.deptno%type;begin  select deptno into v_deptno from emp where empno=v_empno;  dbms_output.put_line('该员工所在的部门号:'||v_deptno);  end;-- 执行语句:exec pro2(1234);

3.接收一个部门号,如果该员工职位是MANAGER,并且在DALLAS工作那么就给他薪金加15%,如果该员工职位是CLERK,并且在NEW YORK工作那么就给他薪金扣除5%,其他情况不做处理

create or replace procedure pro3(v_deptno in number)is cursor emp_cursor is select empno from emp where deptno=v_deptno;v_empno emp.empno%type;v_job emp.job%type;v_loc dept.loc%type;begin  open emp_cursor;  loop  fetch emp_cursor into v_empno;  select job into v_job from emp where empno=v_empno;  select loc into v_loc from dept where deptno=v_deptno;   if v_job='MANAGER' and v_job='DALLAS' then        update emp set sal=sal*1.15 where empno=v_empno;        else if v_job='CLERK' and v_loc='NEWYORK' then          update emp set sal=sal*0.95 where empno=v_empno;          else           null;            end if;             exit when emp_cursor%notfound;  end loop;  close emp_cursor;  end;declare  no emp.empno%type;  dno dept.deptno%type;  v_job emp.job%type;  dnm dept.dname%type;begin    no := &员工号;  select deptno,job into dno,v_job from emp         where empno = no;  select dname into dnm from dept       where deptno = dno;   if v_job = 'MANAGER' and dnm = 'DALLAS' then     update emp set sal = sal * 1.15 where empno = no;   elsif v_job = 'CLERK' and dnm = 'NEW YORK' then     update emp set sal = sal * 0.95 where empno = no;        else     null;   end if;exception when others then   dbms_output.put_line(sqlerrm);end; 

4.接收一个员工号,输出这个员工所在部门的平均工资

create or replace procedure pro4(v_empno in number) isavgsal number;begin  select avg(sal) into avgsal from emp where deptno=(select deptno from emp where empno=1234);  dbms_output.put_line('该员工所在部门的平均工资为:'||avgsal);  end;  --执行语句: exec pro4(1234); 

5.以交互的方式给部门表插入一条记录,如果出现主键冲突的异常,请显示"部门号已被占用"的字样

 

  declare      erow dept%rowtype;    begin      erow.deptno := '&部门号';      erow.dname := '&部门名';      erow.loc := '&地址';      insert into dept values (erow.deptno,erow.dname,erow.loc);    exception when others then   dbms_output.put_line(sqlerrm);    end; 

  

1.建立一个存储过程用来接收一个员工号,返回他的工资和他所在部门的平均工资并作为输出参数输出。

create or replace procedure pro6(v_empno in number,v_sal out number,v_avgsal out number) isv_sql varchar2(128);begin  v_sql:='select sal from emp where empno='||v_empno;  execute immediate v_sql into v_sal;  v_sql:='select avg(sal) from emp where deptno=(select deptno from emp where empno='||v_empno||')';  execute immediate v_sql into v_avgsal; end; --在JAVA中执行

2.建立一个存储过程用来接收一个部门号,找出其中的两位最老的员工的员工号,并打印

create or replace package pack1 istype my_cursor_type is ref cursor;end;create or replace procedure pro7(v_deptno in number,v_empno out pack1.my_cursor_type)isv_sql varchar2(128);begin  v_sql:='select empno from emp where deptno='||v_deptno||' and rownum<=2 order by hiredate';  open v_empno for v_sql;  end;  --在JAVA中执行

3.编写一个过程用来传入一个员工号,在emp表中删除一个员工,当该员工是该部门最后一个员工时,就在dept表中删除该员工所在的部门。

create or replace procedure pro8(v_empno number)isi number(2,0);v_deptno emp.deptno%type;begin   select deptno into v_deptno from emp where empno=v_empno;  select count(deptno) into i from emp where deptno=v_deptno;  delete from emp where empno=v_empno;  i:=i-1;  dbms_output.put_line('部门还有'||i||'个人');  if i<1 then    delete from dept where deptno=v_deptno;  end if; end;--执行语句: exec pro8(1234);

 

转载地址:https://blog.csdn.net/qq_38741971/article/details/81433195 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!

上一篇:快速排序详解(Java实现)
下一篇:二十二、Oracle学习笔记:Oracle异常

发表评论

最新留言

不错!
[***.144.177.141]2024年03月26日 04时30分28秒

关于作者

    喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!

推荐文章

java spring上传文件_Java Spring文件上传,Java文件上传,Java通用文件上传 2019-04-21
linux 模拟键盘输入到进程,Linux 下模拟键盘输入 2019-04-21
linux服务器上已安装R 用户下载R包,R语言安装R package的2种方法 2019-04-21
linux 7 磁盘空间太小,Linux下磁盘保留空间的调整,解决df看到的空间和实际磁盘大小不一致的问题... 2019-04-21
linux下mysql 备份方法,Linux下mysql数据库备份方法小结 2019-04-21
bootstrap 页面垂直居中_iframe中如何让layer提示框显示在垂直居中的位置 2019-04-21
肺部ct重建_胸片检查容易漏诊肺癌,去年正常今年晚期常发生,体检一定要做CT... 2019-04-21
3dmax如何拆分模型_3D建模大佬如何制作出惊艳四方的游戏建模,看完这篇文章我知道了... 2019-04-21
x86so文件装换成arm64位_64位系统正式发布说明及介绍!! 2019-04-21
for循环中取出最大最小 累加_LeetCode之长度最小的子数组 2019-04-21
如何打开老公人脸识别_【话题】南宁已有小区启用人脸识别门禁,有人点赞有人忧... 2019-04-21
makex机器人程序_机器人教育为相城青少年叩开科学世界大门 2019-04-21
一寸照纯红色底图片_Ella陈嘉桦也是“时髦精”,穿玫红色西装配拼色半身裙,高级洋气... 2019-04-21
米哈游客户端笔试题_Garena校招 游戏客户端开发通关面经 2019-04-21
airpodspro没有弹窗_使用AirPods Pro一天的主观感受 2019-04-21
创建物化视图commit_视图及范式 2019-04-21
函数传参字典_Python新手上车17:函数传递任意多个参数 2019-04-21
去掉数组最后一个元素_【一天一大 lee】在排序数组中查找元素的第一个和最后一个位置 (难度:中等) Day20201201... 2019-04-21
秦九韶算法递推公式_算法讲解之复杂度分析 2019-04-21
添加绝对路径_网站中如何添加绝对路径 2019-04-21