--创建存储过程，根据员工编号输出员工信息
create or replace procedure find_emp_no(emp_no employees.employee_id%type)
is
--emp_no employees.employee_id%type;
emp_row employees%rowtype;
begin
--emp_no := %员工编号
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 serveroutout on
--创建存储过程，根据员工编号获取薪资,参数默认in的，用来调用者传入，out用来传出去给调用者使用
create or replace procedure get_salary_by_empno(empno in employees.employee_id%type,sarlary out employees.salary%type)
is
begin
select e.salary into sarlary from employees e where e.employee_id = empno;
end;
--调用
declare
salary employees.salary%type;
begin
dbms_output.put_line('工资'||salary);
get_salary_by_empno(&员工编号,salary);
dbms_output.put_line('工资'||salary);
end;
--创建存储过程，交换两个字功能。
create or replace procedure swap(i in out number,j in out number)
is
k_no number;
begin
k_no := i;
i := j;
j := k_no;
end;
--调用
declare
i number := &i;
j number := &j;
begin
dbms_output.put_line('交换前：i='||i||',j='||j);
swap(i,j);
dbms_output.put_line('交换后：i='||i||',j='||j);
end;
--存储过程，根据员工编号返回工资
create or replace function get_by_salary_emp_empno(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;
end;
--函数调用
--方式一：pl/sql程序块调用
declare
salary employees.salary%type;
begin
salary := get_by_salary_emp_empno(&员工编号);
dbms_output.put_line('工资：'||salary);
end;
--方式二：使用dual伪装
select get_by_salary_emp_empno(100) from dual;
--练习：创建函数，实现根据部门编号返回总人数 total_by_dept_no
create or replace function total_by_dept_no(dept_no employees.department_id%type) return number
is
total number;
begin
select count(*) into total from employees e where e.department_id = dept_no;
return total ;
end;
--调用
declare
total number;
begin
total := total_by_dept_no(&number);
dbms_output.put_line('总数：'||total);
end;
select total_by_dept_no(80) from dual;
--loop循环计算1~100的和
declare
he number :=0;
i number :=1;
begin
loop
he := he + i;
exit when i = 100;
i := i + 1;
end loop;
dbms_output.put_line('总和：'||he);
end;
--创建函数，计算整数a数到整数b之间的累计和total_sum
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;
select total_sum(-100,0) from dual;
/**
函数跟过程练习与区别
相同点：都是子过程，封装pl/sql语句块，可以接收参数，拥有封装，模块化，可复用性，安全性作用，都可以使用pl/sql程序块调用
不同点：函数有返回值，过程参数有三种模式，可以返回多个值，过程本身不返回值，但是可以通过out模式会写多个值。
函数使用select函数名 from dual调用，过程在命令窗口使用execute 过程名 调用。
*/
--创建程序包
create or replace package my_pack
as
procedure find_emp_no(emp_no employees.employee_id%type);
procedure get_salary_by_empno(empno in employees.employee_id%type,sarlary out employees.salary%type);
procedure swap(i in out number,j in out number);
function get_by_salary_emp_empno(empno employees.employee_id%type) return employees.salary%type;
function total_by_dept_no(dept_no 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_no employees.employee_id%type;
emp_row employees%rowtype;
begin
--emp_no := %员工编号
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_by_empno开始-------------------------------------------
procedure get_salary_by_empno(empno in employees.employee_id%type,sarlary out employees.salary%type)
is
begin
select e.salary into sarlary from employees e where e.employee_id = empno;
end get_salary_by_empno;
----------------------------swap开始-------------------------------------------
procedure swap(i in out number,j in out number)
is
k_no number;
begin
k_no := i;
i := j;
j := k_no;
end;
-----------------------------------get_by_salary_emp_empno开始--------------------------------
function get_by_salary_emp_empno(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;
end get_by_salary_emp_empno;
------------------------------------total_by_dept_no开始----------------------------
function total_by_dept_no(dept_no employees.department_id%type) return number
is
total number;
begin
select count(*) into total from employees e where e.department_id = dept_no;
return total ;
end total_by_dept_no;
------------------------------------total_sum-----------------------------------
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_by_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);
salary := my_pack.get_by_salary_emp_empno(100);
dbms_output.put_line('工资'||salary);
total := my_pack.total_by_dept_no(90);
dbms_output.put_line('总数'||total);
dbms_output.put_line('总和'||my_pack.total_sum(1,100));
end;
--创建程序包，输入部门编号，输出所有员工信息
create or replace package emp_package
as
--声明一个游标，指定返回值类型，即游标中存放的数据类型
cursor emp_cursor(dept 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_package;
--创建程序包主体
create or replace package body emp_package
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.department_id||'，工资'||emp_row.salary);
end loop;
close emp_cursor;
end get_emps_by_deptno;
----------------------------total--------------------------------
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_package;
--测试
declare
deptno employees.department_id%type := &部门编号;
begin
dbms_output.put_line('部门总人数'||emp_pack.(deptno)||',详细如下：');
emp_pack.get_emps_by_deptno(deptno);
end;
---创建程序包规范和主体，完成下列功能
--1 过程，根据页码查询输出员工信息，每页显示10条（包含游标）
select e.* from employees e order by e.employee_id;
--创建包规范
create or replace package emp_pack
as
procedure emp_msg_pro(emp_no employees.employee_id%type);
end emp_pack;
--创建过程例子
--创建存储过程，根据员工编号获取薪资,参数默认in的，用来调用者传入，out用来传出去给调用者使用
create or replace procedure get_salary_by_empno(empno in employees.employee_id%type,sarlary out employees.salary%type)
is
begin
select e.salary into sarlary from employees e where e.employee_id = empno;
end;
--创建程序包规范和主体，完成下列功能
--1 过程，根据页码查询输出员工信息，每页显示10条（包含游标）
--2 函数，根据员工编号获取部门名称
--3 函数，根据部门编号求平均工资，保留两位小数（输出部门编号，返回平均工资）
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
