创建一个带参数的sp，通过变量传入值...

原创

mob604756fc093d 2021-05-10 06:37:15 ©著作权

文章标签 Java 文章分类 Java 后端开发

©著作权归作者所有：来自51CTO博客作者mob604756fc093d的原创作品，请联系作者获取转载授权，否则将追究法律责任

--1、创建一个存储过程，以一个整数为参数，打印工资最高的前几个（参数值）员工的信息，并对该参数值做判断(尽量写全)。 --写sql -- 给个具体的值：5 select f.* from ( select e.*, row_number()over(order by e.sal desc) rn from emp e) f where f.rn <= 5;

--实现功能： declare v_x number := &X;

cursor c_rn is select f.* from (select e.*, row_number() over(order by e.sal desc) rn from emp e) f where f.rn <= v_x;

ct number; begin select count(*) into ct from emp;

if v_x >= 1 and v_x <= ct then if mod(v_x, 1) = 0 then for x in c_rn loop dbms_output.put_line('工号' || x.empno || ' 姓名' || x.ename || ' 职位' || x.job || ' 领导' || x.mgr || ' 入职日期' || to_char(x.hiredate, 'yyyymmdd') || ' 工资' || x.sal || ' 奖金' || x.comm || ' 部门编号' || x.deptno); end loop; end if; end if; end;

--封装成sp：把上面实现功能的plsql块中的接收用户输入的变量换成参数 --有几个变量接收用户的输入，那么就定义几个参数，分别放到对应的位置（原本该变量所放的位置换成参数） create or replace procedure sp_x(p_x number) is --v_x number := &X;

cursor c_rn is select f.* from (select e.*, row_number() over(order by e.sal desc) rn from emp e) f where f.rn <= p_x;

ct number; begin select count(*) into ct from emp;

if p_x >= 1 and p_x <= ct then if mod(p_x, 1) = 0 then for x in c_rn loop dbms_output.put_line('工号' || x.empno || ' 姓名' || x.ename || ' 职位' || x.job || ' 领导' || x.mgr || ' 入职日期' || to_char(x.hiredate, 'yyyymmdd') || ' 工资' || x.sal || ' 奖金' || x.comm || ' 部门编号' || x.deptno); end loop; end if; end if; end;

--验证 begin sp_x(10); end;

**/2、创建一个带参数的sp，通过变量传入值，打印某个员工的领导的领导的姓名，工作岗位，以及该员工的姓名和岗位如果该员工没有领导，打印‘我是老板’ 如果该员工的领导没有领导，打印‘我是二把手’ / --写sql，给一个具体的值：工号为 7788 select mgr from emp where empno = 7788; --结果有值说明这个员工有领导，如果没有值就表示这个人是老板

--查该员工的领导是否有领导，结果有值说明这个员工的领导上面还有领导，如果没有值就表示找个人是二把手 select mgr from emp where empno = (select mgr from emp where empno = 7788);

--实现功能 declare v_empno number := &empno; ct_mgr number; ct_mgr_mgr number; v_ename varchar2(10); v_job varchar2(10); v_mgr_ename varchar2(10); v_mgr_job varchar2(10); begin select mgr into ct_mgr from emp where empno = v_empno; if ct_mgr is null then dbms_output.put_line('我是老板'); else

select mgr into ct_mgr_mgr from emp where empno = (select mgr from emp where empno = v_empno);

if ct_mgr_mgr is null then dbms_output.put_line('我是二把手'); else select ename, job into v_ename, v_job from emp where empno = v_empno; select ename, job into v_mgr_ename, v_mgr_job from emp where empno = (select mgr from emp where empno = (select mgr from emp where empno = v_empno)); dbms_output.put_line('员工的信息：'); dbms_output.put_line('姓名:' || v_ename || ' 岗位：' || v_job); dbms_output.new_line();
dbms_output.put_line('员工的领导的领导的信息：'); dbms_output.put_line('姓名:' || v_mgr_ename || ' 岗位：' || v_mgr_job); end if; end if; end;

--封装（套壳子） create or replace procedure sp_mgr(p_empno number) is --v_empno number := &empno; ct_mgr number; ct_mgr_mgr number; v_ename varchar2(10); v_job varchar2(10); v_mgr_ename varchar2(10); v_mgr_job varchar2(10); begin select mgr into ct_mgr from emp where empno = p_empno;

if ct_mgr is null then dbms_output.put_line('我是老板'); else
select mgr into ct_mgr_mgr from emp where empno = (select mgr from emp where empno = p_empno);
if ct_mgr_mgr is null then dbms_output.put_line('我是二把手'); else select ename, job into v_ename, v_job from emp where empno = p_empno;
select ename, job into v_mgr_ename, v_mgr_job from emp where empno = (select mgr from emp where empno = (select mgr from emp where empno = p_empno));
dbms_output.put_line('员工的信息：'); dbms_output.put_line('姓名:' || v_ename || ' 岗位：' || v_job);
dbms_output.new_line();
dbms_output.put_line('员工的领导的领导的信息：'); dbms_output.put_line('姓名:' || v_mgr_ename || ' 岗位：' || v_mgr_job); end if; end if; end;

--验证 declare x number := &empno; begin sp_mgr(x); end;

select e2.ename 员工姓名, e2.job 员工岗位, e1.ename 员工的直属领导的姓名, e1.job 员工的直属领导的岗位, e3.ename 员工的二级领导的姓名, e3.job 员工的二级领导的岗位 from emp e1 right join emp e2 on e1.empno = e2.mgr left join emp e3 on e1.mgr = e3.empno where e2.empno = 7566;

**/3、创建存储过程，根据输入的入职时间范围，打印出在这个时间内各地区工资前2名的员工的姓名，入职时间，工资，需要确保输入的入职时间在最早的入职和最晚的入职之间 / ** --写sql select f.ename,f.hiredate,f.sal from ( select e., d., row_number()over(partition by d.loc order by e.sal desc) rn from emp e join dept d on e.deptno = d.deptno where e.hiredate between to_date(19810101,'yyyymmdd') and to_date(19871231,'yyyymmdd')) f where f.rn <= 2;

--实现功能 declare date1 date := to_date('&日期1', 'yyyymmdd'); date2 date := to_date('&日期2', 'yyyymmdd'); min_hiredate date; max_hiredate date; temp_date date;

cursor c_hire is select f.ename, f.hiredate, f.sal from (select e., d., row_number() over(partition by d.loc order by e.sal desc) rn from emp e join dept d on e.deptno = d.deptno where e.hiredate between date1 and date2) f where f.rn <= 2; begin select min(hiredate), max(hiredate) into min_hiredate, max_hiredate from emp;

if date1 > date2 then temp_date := date1; date1 := date2; date2 := temp_date; end if;

if (date1 between min_hiredate and max_hiredate) and (date2 between min_hiredate and max_hiredate) then

for x in c_hire loop
dbms_output.put_line('姓名：' || x.ename); end loop; end if; end;

--封装 create or replace procedure sp_hire(p_date1 number,p_date2 number) is date1 date := to_date(p_date1, 'yyyymmdd'); date2 date := to_date(p_date2, 'yyyymmdd'); min_hiredate date; max_hiredate date; temp_date date;

if date1 > date2 then temp_date := date1; date1 := date2; date2 := temp_date; end if;

if (date1 between min_hiredate and max_hiredate) and (date2 between min_hiredate and max_hiredate) then

for x in c_hire loop
dbms_output.put_line('姓名：' || x.ename); end loop; end if; end;

*/1 创建一个存储过程，以员工号和部门号作为参数，修改员工所在的部门为所输入的部门号。如果修改成功，则显示“员工由……号部门调入调入……号部门”；如果不存在该员工，则显示 “员工号不存在，请输入正确的员工号。”；如果不存在该部门，则显示 “该部门不存在，请输入正确的部门号。”。

注：修改的表不要用emp表，要创建一个emp的备份表，在该备份表中进行修改操作 */ --建一个emp备份表 create table emp_bak as select * from emp where 1 = 1;

--创建sp create or replace procedure sp_change_deptno(p_empno number, p_deptno number) is ct_empno number; ct_deptno number; v_deptno number; --员工本身所在的部门 begin --查询员工号是否存在 select count() into ct_empno from emp_bak where empno = p_empno; --查询部门号是否存在 select count() into ct_deptno from dept where deptno = p_deptno;

if ct_empno = 0 then dbms_output.put_line('员工号不存在，请输入正确的员工号。'); end if;

if ct_deptno = 0 then dbms_output.put_line('该部门不存在，请输入正确的部门号。'); end if;

if ct_empno != 0 and ct_deptno != 0 then
--查这个员工在修改部门号之前的部门编号 select deptno into v_deptno from emp_bak where empno = p_empno;
update emp_bak set deptno = p_deptno where empno = p_empno; commit;
dbms_output.put_line('员工由' || v_deptno || '号部门调入' || p_deptno || '号部门'); end if; end;

--调用 begin sp_change_deptno(7788,40); end;

select * from emp_bak;

*/2 创建一张表，往表中插入10000 行数据，格式如下： stu（表名） sno sname gender phone_no 1 2 3 4 5 ....... 要求：开发一个自动造数据的SP 或者写一个PLSQL程序块实现如下的要求：带参数实现，需要造多少行数据以参数为行数。 1 每次调用SP或者PLSQL程序块往表里插入 10000条数据，插入 10000个 SNO。 2 sno能被3整除，gender为男，其余为女 3 sno不能被3整除，余数为1，则姓张，余数为2，则姓王；能被3整除，姓李，Sname的信息为张数字或者王数字或者李数字 .... 4 phone_no 使用随机数（11位数）生成; */ --建表 create table stu(sno number,sname varchar2(10),gender varchar2(2),phone_no number(11));

--建sp create or replace procedure sp_insertData(p_x number) is max_sno number; begin select count(sno) into max_sno from stu;

for x in max_sno+1 .. max_sno+p_x loop insert into stu values(x, case mod(x,3) when 1 then '张'||x --在插入/更新数据时带有判断，照样可以写case when when 2 then '王'||x when 0 then '李'||x end, decode(mod(x,3),0,'男','女'), trunc(dbms_random.value(13000000000,19999999999))); commit; end loop; end;

--调用 begin sp_insertData(10000); end;

select * from stu;

-- select count(*) from stu;

*/3 创建一个存储过程，创建一张备份表EMP_BAK2 , 备份EMP表结构和表中数据以员工号为参数，修改该员工的工资。若该员工属于10号部门，则工资增加150；
若属于20号部门，则工资增加200；若属于30号部门，则工资增加250；
若属于其他部门，则增加300。 */ create table EMP_BAK2 as select * from emp where 1 = 1;

create or replace procedure sp_changeSal(p_empno number) is v_deptno number(2); ct number; begin select count(*) into ct from emp_bak2 where empno = p_empno;

if ct = 1 then select deptno into v_deptno from emp_bak2 where empno = p_empno;

if v_deptno = 10 then
  update emp_bak2 set sal = sal + 150 where empno = p_empno;

elsif v_deptno = 20 then
  update emp_bak2 set sal = sal + 200 where empno = p_empno;

elsif v_deptno = 30 then
  update emp_bak2 set sal = sal + 250 where empno = p_empno;

else
  update emp_bak2 set sal = sal + 300 where empno = p_empno;

end if;
commit;

end if; end;

-- begin sp_changeSal(7844); end;

select * from EMP_BAK2 where empno in (7782,7788,7844);

--在插入/更新数据时带有判断，照样可以写case when update emp_bak2 set sal = case deptno when 10 then sal+150 when 20 then sal + 200 when 30 then sal + 250 else sal + 300 end where empno in (7782,7788,7844); commit;