20210812 oracle日常

--创建存储过程，根据员工编号输出员工信息
create or replace procedure find_emp_no(emp_no in employees.employee_id%type)
is
     emp_row employees%rowtype;
begin
     select e.* into emp_row from employees e where e.employee_id=emp_no;
     dbms_output.put_line('编号：'||emp_row.employee_id||'，薪资：'||emp_row.salary);
end;

--调用存储过程
--方式一:使用pl/sql程序调用
declare 

begin
  --过程名称和参数调用
  find_emp_no(&员工编号);
end;
--方式二：在命令行窗口execute find_emp_no(100)，如果控制台不输出结果，执行set serveroutput on

--创建存储过程，根据员工编号获取薪资,参数模式in默认的用来传入，out用来传出去给调用过程者使用
create or replace procedure get_salary_empno(empno in employees.employee_id%type,salary out employees.salary%type)
is
begin
  select e.salary into salary  from employees e where e.employee_id = empno;
end;

--调用   返回多个值就用存储过程
declare 
  salary employees.salary%type;
begin
    dbms_output.put_line('工资'||salary);
  --过程名称和参数调用
  get_salary_empno(&员工编号,salary);
  dbms_output.put_line('工资'||salary);
end;

--创建一个过程，，实现两个数交换功能

create or replace procedure exchange_num(num1 in out number, num2 in out number)
is 
  tmp number;
begin
  tmp := num1;
  num1:=num2;
  num2 :=tmp;
  
end;

--调用   返回多个值就用存储过程
declare 
 
begin
   
  exchange_num(10,20);

end;

--创建一个过程，，实现两个数交换功能

create or replace procedure swap(a in out number , b in out number)
is
temp number;
begin
  temp := a;
  a := b;
  b :=temp;
end;

declare 
 a number :=&a;
 b number :=&b;
begin
   dbms_output.put_line('a='||a||'b='||b);
  swap(a,b);
  dbms_output.put_line(',a='||a||',b='||b);

end;

--创建函数，根据员工编号返回工资
create or replace function get_salary_by_emp_no(empno employees.employee_id%type)return employees.salary%type

is
salary employees.salary%type;
begin
  select e.salary into salary from employees e where e.employee_id=empno;
  return salary;--return返回一个值，类型必须声明是一致 
  
  end;

--函数调用
--方式一:ql/sql调用
declare
 salary employees.salary%type;
begin
  salary := get_salary_by_emp_no(&员工编号);
  dbms_output.put_line('工资'||salary);
  end;

--方式二：使用dual伪表输出
select get_salary_by_emp_no(101) from dual;

--创建函数，实现根据部门编号返回总人数get_total_by_dept_no
create or replace function get_total_by_dept_no(emp_dmp employees.department_id%type)return number
is
num_total number;
begin
  select count(*) into num_total  from employees e where e.department_id = emp_dmp;
  return num_total;
end;

--函数的调用
select get_total_by_dept_no(90) from dual;
select * from employees e where e.department_id=100;

--从一个数到另一个的之和
--创建函数，计算a数到整数b之间的累计和
create or replace function sum_num(a in number,b in number )return number
is 
temp number;
begin
  if (a-b)/2=0  then
        temp :=  (a+b)* (b-a)/2; 
    else 
      temp := (a+b)*(b-a)/2+(a+b)/2;
     
  end if;
      return temp;
  end;

select sum_num(2,2) from dual;

declare
 num_1 number;
 num_2 number;
 num_3 number;
begin
  salary := sum_num(&员工编号);
  dbms_output.put_line('工资'||salary);
  end;

--创建函数，计算a数到整数b之间的累计和
create or replace function total_sum(a number ,b number) return number
is
  total number :=0;
 
begin
    
  for i in a..b
    loop
      total :=total + i;
    end loop;
    return total;
end;

select total_sum(2,2) from dual;
-----------------------------------------------------

create or replace function total_sum(a number ,b number) return number
is
  total number :=0;
 
begin
   if a<=b then
     for i in a..b
       loop
         total :=total +i;
       end loop;
   else 
     for i in b..a
       loop
         total :=total+i;
       end loop;
    end if;
    return total;
end;

/**
函数跟过程联系和区别
相同点：都是子程序，封装pl\sql语句块，可以接收参数，拥有封装，模块化，可复用性，安全性作用
不同点：函数有返回值，过程参数有三种模式，过程本身不返回值，但是可以通过参数out模式回写多个值，函数使用
select 函数名 from dual 调用，过程在命令窗口使用execute过程名调用
*/

--创建程序包
create or replace package my_pack
as
procedure find_emp_no(emp_no in employees.employee_id%type);
procedure get_salary_empno(empno in employees.employee_id%type,salary out employees.salary%type);
procedure swap(a in out number , b in out number);
function get_salary_by_emp_no(empno employees.employee_id%type)return employees.salary%type;
 function get_total_by_dept_no(emp_dmp employees.department_id%type)return number;
 function total_sum(a number ,b number) return number;
end my_pack;

--创建程序包主体
create or replace package body my_pack
as
  ----------------- find_emp_no开始-----------------------------------
procedure find_emp_no(emp_no employees.employee_id%type) is
  emp_row employees%rowtype;
begin
  select e.* into emp_row from employees e where e.employee_id = emp_no;
  dbms_output.put_line('编号：' || emp_row.employee_id || '，薪资：' ||
                       emp_row.salary);
end find_emp_no;
----------------------get_salary_empno开始---------------------------------------
procedure get_salary_empno(empno  in employees.employee_id%type,
                           salary out employees.salary%type) is
begin
  select e.salary into salary from employees e where e.employee_id = empno;
end get_salary_empno;
----------------------swap开始---------------------------------------
procedure swap(a in out number, b in out number) is
  temp number;
begin
  temp := a;
  a    := b;
  b    := temp;
end swap;
----------------------get_salary_by_emp_no开始---------------------------------------
function get_salary_by_emp_no(empno employees.employee_id%type)return employees.salary%type

is
salary employees.salary%type;
begin
  select e.salary into salary from employees e where e.employee_id=empno;
  return salary;--return返回一个值，类型必须声明是一致

  end get_salary_by_emp_no;
----------------------get_total_by_dept_no开始---------------------------------------
function get_total_by_dept_no(emp_dmp employees.department_id%type)return number
is
num_total number;
begin
  select count(*) into num_total  from employees e where e.department_id = emp_dmp;
  return num_total;
end get_total_by_dept_no;
----------------------get_total_by_dept_no开始---------------------------------------
function total_sum(a number ,b number) return number
is
  total number :=0;

begin
   if a<=b then
     for i in a..b
       loop
         total :=total +i;
       end loop;
   else
     for i in b..a
       loop
         total :=total+i;
       end loop;
    end if;
    return total;
end total_sum;
end my_pack;

--程序包调用
declare
salary employees.salary%type;
num1 number :=100;
num2 number :=200;
total number;

begin
  my_pack.find_emp_no(100);
  my_pack.get_salary_empno(100,salary);
  dbms.output.put_line('工资'||salary);
  dbms.output.put_line('num1='||num1||'num2='||num2);
  my_pack.swap(num1,num2);
  dbms.output.put_line('num1'||num1||'num2'||num2);
  
  end; 

-------------------创建程序包，输入部门编号，输出所有员工信息----------
create or replace package emp_pack
as
 --声明一个游标，指定返回值类型，即游标中存放的数据类型
 cursor emp_cursor(deptno employees.department_id%type) return employees%rowtype;
 --根据员工部门编号，输出员工信息
 procedure get_emps_by_deptno(deptno employees.department_id%type);
 --根据部门编号获取员工信息
 function total(deptno employees.department_id%type) return number;

end emp_pack;

---创建程序包主体
create or replace package body emp_pack
as
cursor emp_cursor(deptno employees.department_id%type) return employees%rowtype is
select e.* from employees e where e.department_id=deptno;
-------------------------get_emps_by_deptno---------------------
procedure get_emps_by_deptno(deptno employees.department_id%type)
is
emp_row employees%rowtype;
begin
  --打开游标
  open emp_cursor(deptno);
  loop
    --逐行读取数据存入变量
  fetch emp_cursor into emp_row;
  --游标没有数据退出循环
  exit when emp_cursor%notfound;
  
  dbms_output.put_line('编号'||emp_row.employee_id||'工资'||emp_row.salary);
  end loop;
  --关闭游标
  close emp_cursor;
end get_emps_by_deptno;
-------------------------tortal---------------------
function total(deptno employees.department_id%type) return number
 is
 total_num number;
 begin
   select count(*) into total_num from employees e  where e.department_id=deptno;
   return total_num;
end total;

end emp_pack;

--测试
declare
deptno employees.department_id%type:=&部门编号;
begin
  emp_pack.get_emps_by_deptno(90);
  dbms_output.put_line(deptno||'..'||emp_pack.total(deptno)||',详细如下：');
  emp_pack.get_emps_by_deptno(deptno);
  end;