2024.07.11 oracle存储过程练习4

--15.创建一个存储过程，传入一个表名，统计该表中的数据行数并返回：
create or replace procedure q1(n in varchar2, n1 out number)
is
begin
  execute immediate 'select count(*) from ' || n into n1;
  dbms_output.put_line('表 ' || n || ' 中的数据行数为: ' || n1);
exception
  when others then
    raise_application_error(sqlerrm);
end;
declare
  n1 number;
begin
  q1('emp', n1);
end;
--16.编写一个存储过程，计算并返回指定部门的员工总数：
create or replace procedure q1(n in number, n1 out number)
is
begin
  execute immediate 'select count(*) from emp where deptno = :1' into n1 using n;
  dbms_output.put_line('部门 ' || n || ' 中的员工总数为: ' || n1);
exception
  when others then
    raise_application_error(-20202, '发生错误: ' || sqlerrm);
end;

declare
  n1 number;
begin
  q1(10, n1);
end;
--17.编写一个存储过程，输入表名、员工编号及工资，更新员工表中指定员工的薪水：
create or replace procedure q1(t in varchar2, n in number, n1 in number)
is
begin
  execute immediate 'update ' || t || ' set sal = :1 where empno = :2' using n1, n;
  dbms_output.put_line('表 ' || t || ' 中员工编号为 ' || n || ' 的工资已更新为 ' || n1);
exception
  when others then
    raise_application_error(-20202, '发生错误: ' || sqlerrm);
end;
declare
  t varchar2(20) := '&t'; 
  n number := &n;         
  n1 number := &n1;      
begin
  q1(t, n, n1);
end;

--18.写出统计某门课选修人数的存储过程，要求输入课程号，输出选修人数：

create or replace procedure q1(cn in varchar2, c out number)
is
begin
  execute immediate 'select count(*) from sc where cno = :1' into c using cn;
  dbms_output.put_line('课程号 ' || cn || ' 的选修人数为 ' || c);
exception
  when others then
    raise_application_error(-20202, '发生错误: ' || sqlerrm);
end;

declare
  c number;
  cn varchar2(20) := '&cn'; -- 输入课程号
begin
  q1(cn, c);
end;

--19.编写一个存储过程pr_get_pass_rate，统计某门课程的及格率，传入参数是课程编号，输出该课程的及格率（格式：86.68%）：
create or replace procedure pr_get_pass_rate(cn in varchar2, c out varchar2)
is
  total_students number;
  passed_students number;
  pass_rate number;
begin
  execute immediate 'select count(*) from sc where cno = :1' into total_students using cn;
  execute immediate 'select count(*) from sc where cno = :1 and score >= 60' into passed_students using cn;
  if total_students > 0 then
    pass_rate := (passed_students / total_students) * 100;
    c := to_char(pass_rate) || '%'; 
  else
    c := '0%'; 
  end if;
  dbms_output.put_line('课程号 ' || cn || ' 的及格率为 ' || c);
exception
  when others then
    dbms_output.put_line(sqlerrm);
end;

declare
  c varchar2(100);
    cn varchar2(20):='&cn';
begin
  pr_get_pass_rate(cn,c);
end;
       
--20.创建一个存储过程，以表名和一个列名为参数，创建一个表，如果该表已存在，则提示“该表名以存在，请重新命名！！！”：
create or replace procedure u2(tna varchar2,cna varchar2)
is 
  e_exception exception;
  pragma exception_init(e_exception,-00955);
begin
  execute immediate 'create table '||tna||'('||cna||' varchar2(100))';
exception
  when e_exception then
    dbms_output.put_line('该表名以存在，请重新命名！！！');
  when others then
    dbms_output.put_line(sqlcode||sqlerrm);
end;
--21.创建名为‘PROC_SECURE_DML’的存储过程，传入一个日期，检查当前用户操作时间是否
--为工作时间，即非周六、周日，时间为08:00～18:00。否则直接报错：
create or replace procedure proc_secure_dml(dat date,bool out boolean)
is 
begin
  if to_char(dat,'dd') between 1 and 5 and to_char(dat,'hh24') between 8 and 18 then
    bool := true;
  else
    bool := false;
  end if;
exception
  when others then
    dbms_output.put_line(sqlcode||sqlerrm);
end;
declare
  b boolean;
  d date := to_date('2024-07-11 19:25:22','yyyy-mm-dd hh24:mi:ss');
begin
  proc_secure_dml(d,b);
  if b then
    dbms_output.put_line('当前用户操作时间为工作时间');
  else 
    raise_application_error(-20100,'当前用户操作时间不是工作时间!');
  end if;
end;

--22.定义存储过程，可以传入以下参数：query(ename,job,sal)。如果传入了某几个参数，则以参数组合的形式查询结果。
/*比如 query('Smith',null,null)，查询的sql为：
select * from emp where upper(ename) like upper('%Smith%')。
query('Smith','Clerk',null)，查询的sql为：
select * from emp where upper(ename) like upper('%Smith%') and upper(job) like upper('%Clerk%')。
query('Smith','Clerk',3000)，查询的sql为：
select * from emp where upper(ename) like upper('%Smith%') and upper(job) like upper('%Clerk%') and length(sal)=length(3000) 
and substr(sal,1,1)=substr(3000,1,1)。
要求输出查询的结果：
*/
create or replace procedure query(en varchar2,jo varchar2,sa number)
is 
  e emp%rowtype;
  str varchar2(500) := 'select * from emp where upper(ename) like upper(''%'||en||'%'')';
  str1 varchar2(500) := 'select * from emp where upper(ename) like upper(''%'||en||'%'') and upper(job) like upper(''%'||jo||'%'')';
  str2 varchar2(500) := 'select * from emp where upper(ename) like upper(''%'||en||'%'') and upper(job) like upper(''%'||jo||'%'') and length(sal)=length('||sa||') and substr(sal,1,1)=substr('||sa||',1,1)';
begin                 
  if jo is null and sa is null then
    execute immediate str into e;
  elsif sa is null then
    execute immediate str1 into e;
  else
    execute immediate str2 into e;
  end if;
  dbms_output.put_line(e.ename||e.job||e.sal);
exception
  when others then
    dbms_output.put_line(sqlerrm);
end;

begin
  query('Smith',null,null);
  query('Smith','man',null);
  query('Smith','man',3000);
end;