-- 过程 函数----------
-- 过程 ----------
create or replace procedure pro_printemp
is
-- 定义部分
cursor cur_emp is select * from emp;
begin
-- 执行部分
for v_row in cur_emp loop
dbms_output.put_line(v_row.empno||' '||v_row.ename||' '||v_row.deptno);
end loop;
exception when others then
dbms_output.put_line(sqlerrm);
end pro_printemp;
-- 执行过程
SQL> execute pro_printemp;
SQL> exec pro_printemp;
-- JDBC调用方式
SQL> call pro_printemp();
-- 定义带参的过程
create or replace procedure pro_pebd(dno number)
as cursor cur_emp is select * from emp where deptno=dno;
begin
for v_row in cur_emp loop
dbms_output.put_line(v_row.empno||' '||v_row.ename||' '||v_row.deptno);
end loop;
exception when others then
dbms_output.put_line(sqlerrm);
end pro_pebd;
-- 执行
SQL> exec pro_pebd(10);
-- 参数的分类：
-- 1- 输入类型参数：参数只负责接收值到过程中
-- 2- 输出类型参数：参数只负责将过程中结果带出
-- 根据员工编号得到工资
create or replace procedure pro_getsal(eno number,esal out number)
is
begin
select sal into esal from emp where empno=eno;
end pro_getsal;
-- 执行带输出类型参数的过程：
SQL> variable e_sal number; -- 定义一个变量，承载输出值
SQL> exec pro_getsal(7788,:e_sal); -- 执行，注意：变量使用时格式： ":变量名"
SQL> print e_sal; -- 打印变量值，注意：打印时不能带符号":";
-- 3- 输入输出类型参数：参数负责将值带入过程执行，并带出结果
create or replace procedure pro_getsal(val in out number)
is
begin
select sal into val from emp where empno=val;
exception when others then
dbms_output.put_line(sqlerrm);
end pro_getsal;
-- 执行带输入、输出类型的参数：
SQL> var val number; -- 定义变量
SQL> exec :val := 7839; -- 给变量赋值
SQL> exec pro_getsal(:val); -- 执行
SQL> print val;-- 打印结果
/**
参数定义规范：
尽量只使用输入类型参数，而尽量不要使用输出类型参数和输入输出类型参数。
*/
-- 函数 ----- 有返回值的过程
-- 注意： 形参不能定义精度
create or replace function fun_getsal(eno number)
return number
as
v_sal number;
begin
select sal into v_sal from emp where empno=eno;
return v_sal;
exception when others then
dbms_output.put_line(sqlerrm);
end fun_getsal;
-- 执行1：使用变量接收函数结果
SQL> var v_sal number; -- 定义变量
SQL> exec :v_sal := fun_getsal(7788); -- 使用变量接收函数执行结果
SQL> print v_sal; -- 打印变量
-- 执行2：直接将函数结果输出
SQL> exec dbms_output.put_line(fun_getsal(7839));
-- 执行3：JDBC执行
-- 1 定义变量，在JavaSE中定义
-- 2 执行
SQL> call :v_sal := fun_getsal(7788); -- 使用变量接收函数执行结果
-- 3- 获取变量，在JavaSE中
-- 执行4：像系统提供的函数一样，使用在DML、DDL ... 语句中
-- 输出每个部门员工工资占部门总工资百分比
create or replace function fun_get_dept_sal(dno number) return number
is
sumsal number;
begin
select sum(sal) into sumsal from emp where deptno=dno;
return sumsal;
exception when others then
dbms_output.put_line(sqlerrm);
end fun_get_dept_sal;
select e.*,round(e.sal/fun_get_dept_sal(e.deptno)*100,2)||'%' percent_sal
from emp e
/**
定义过程和函数的规范：
如果不需要返回值，则定义为过程：procedure
如果需要返回值，则定义为函数：function
尽量不要使用输出类型的参数
*/
-- 调用函数或者过程时，实参填写的分类：
create or replace procedure pro_addemp(
v_empno number,v_ename varchar2,v_sal number,v_deptno number) is
begin
insert into emp(empno,ename,sal,deptno) values(v_empno,v_ename,v_sal,v_deptno);
commit;
exception when others then
dbms_output.put_line(sqlerrm);
end pro_addemp;
-- 1- 按位置输入
SQL> exec pro_addemp(1000,'aa',2000,10);
-- 2- 按名称输入
SQL> exec pro_addemp(v_ename => 'bb',
v_deptno => 20,v_sal => 3000,v_empno => 7777);
-- 3- 混合输入
SQL> exec pro_addemp(1001,'cc',v_deptno => 20,v_sal => 2500);
-- 注意：混合输入时：必须先按位置再按名称，一旦使用了按名称后不能再
-- 使用按位置赋值。
-- 错误方式：
-- SQL> exec pro_addemp(1001,'cc',v_sal=>2500,20);
/**
赋予实参的规范：
只按照位置赋予，不允许按照名称和混合赋予；
*/
------- 包 ---------
-- 整合一组功能的 过程和函数、变量所形成的一个功能体
-- 1- 声明部分
create or replace package pak_scott is
-- Author : ADMINISTRATOR
-- Created : 2014/8/13 10:35:14
-- Purpose : operation scott
-- Public variable declarations
v_sal number;
-- Public function and procedure declarations
function get_sal(eno number) return number;
procedure print_sal(eno number);
procedure print_sal(v_ename varchar2);
end pak_scott;
-- 2- 实现部分
create or replace package body pak_scott is
-- Private variable declarations
v_deptno number;
-- public Function and procedure implementations
function get_sal(eno number) return number
is
begin
select sal into v_sal from emp where empno=eno;
return v_sal;
end;
procedure print_sal(eno number)
as
begin
dbms_output.put_line(get_sal(eno));
end;
-- private function or procedure
function get_eno(v_ename varchar2) return number
is
v_eno number;
begin
select empno into v_eno from emp where ename=v_ename;
return v_eno;
end;
-- public print_sal
procedure print_sal(v_ename varchar2)
is
begin
dbms_output.put_line(get_sal(get_eno(v_ename)));
end;
end pak_scott;
-- 执行包：
-- 执行过程
SQL> exec pak_scott.print_sal(7788);
SQL> exec pak_scott.print_sal('KING');
-- 执行函数
SQL> exec dbms_output.put_line(pak_scott.get_sal(7788));
-- 执行（使用）变量
SQL> exec pak_scott.v_sal:=10;
SQL> exec dbms_output.put_line(pak_scott.v_sal);
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;