二十三、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 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!
发表评论
最新留言
不错!
[***.144.177.141]2024年03月26日 04时30分28秒
关于作者
喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!
推荐文章
linux 模拟键盘输入到进程,Linux 下模拟键盘输入
2019-04-21
linux服务器上已安装R 用户下载R包,R语言安装R package的2种方法
2019-04-21
linux下mysql 备份方法,Linux下mysql数据库备份方法小结
2019-04-21
肺部ct重建_胸片检查容易漏诊肺癌,去年正常今年晚期常发生,体检一定要做CT...
2019-04-21
x86so文件装换成arm64位_64位系统正式发布说明及介绍!!
2019-04-21
for循环中取出最大最小 累加_LeetCode之长度最小的子数组
2019-04-21
如何打开老公人脸识别_【话题】南宁已有小区启用人脸识别门禁,有人点赞有人忧...
2019-04-21
makex机器人程序_机器人教育为相城青少年叩开科学世界大门
2019-04-21
米哈游客户端笔试题_Garena校招 游戏客户端开发通关面经
2019-04-21
airpodspro没有弹窗_使用AirPods Pro一天的主观感受
2019-04-21
创建物化视图commit_视图及范式
2019-04-21
函数传参字典_Python新手上车17:函数传递任意多个参数
2019-04-21
秦九韶算法递推公式_算法讲解之复杂度分析
2019-04-21
添加绝对路径_网站中如何添加绝对路径
2019-04-21