Oracle、plsql、游标、存储过程、函数、包

最新推荐文章于 2024-05-07 23:26:14 发布

李鑫海。

最新推荐文章于 2024-05-07 23:26:14 发布

阅读量197

点赞数

文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/weixin_57102902/article/details/127004894

版权

Oracle

一、常用命令

sqlplus

sys as sysdba

conn scott/tiger

alter user scott identified by 密码

show user

二、sql语句

DDL 自动提交事务

DML 不会自动提交事务

DCL 自动提交事务

TCL 终结事务

1、创建用户，只有管理员可用（DDL）

create user 用户名 identified by 密码
alter user 用户名 idetified by 密码

2、新建用户没有权限，要分配权限（DCL）

grant connect,resource to 用户名

3、锁定/解锁用户，只有管理员可用（DDL）

alter user 用户名 account lock
alter user 用户名 account unlock

三、体系结构

四、深度理解Oracle启动和关闭

startup nomount

startup mount

startup open

shutdown normal

任何新的连接都将再不允许连接到数据库。在数据库关闭之前，Oracle将等待目前连接的所有用户都从数据库中退出后才开始关闭数据库。

shutdown

系统不等待连接到数据库的所有用户退出系统，强行回滚当前所有的活动事务，然后断开所有的连接用户。

shutdown immediate

在所有活动的事务完成后，数据库将和SHUTDOWN IMMEDIATE同样的方式关闭数据库。

shutdown abort

PLSQL

select * from emp;
select * from dept;

-- DDL  alter  create  drop  (会自动提交事务)
-- DML  insert delete update select  （不会自动提交事务）
-- DCL  grant  revoke
-- TCL  commit  rollback （终结事务）


update dept set dname = lower(dname) where deptno = 20;
commit;

select name from v$datafile;

-- 单行注释
/*
   多行注释
*/
-- 基本操作
-- 1- 查看当前用户命令：
   show user 
--2- 创建用户： 只有管理员可用
  create  user  用户名  identified  by  密码
  alter  user  用户名  identified by 新密码
  
--注：新建的用户缺少权限，需要分配使用权限才能连接
  grant  connect,resource  to  用户名  -- (连接和数据资源使用权限)
  
-- 3- 锁定/解锁用户： 只有管理员可用
  alter  user  用户名  account  lock;
  alter  user  用户名  account  unlock;   


-- 注意：语句块输出需要打开输出标记： set serveroutput on;

/*
匿名语句块
*/
declare
  -- 声明部分（定义变量、类型、游标等）
  -- 声明变量的格式： 变量名  类型(精度)[:= 初始值]
  v_var number(5) := 10;
  v_name varchar2(20) := 'abc';
begin
  -- 使用变量
  -- 注意  = 判断是否相等；    := 赋值号
  --  || 字符串连接符号
  v_var := v_var + 10;
  dbms_output.put_line(v_var || ', ' || v_name );
  
  insert into emp(empno, deptno) values(1001, 30);
  
  -- 异常处理部分
  exception 
        when others then 
          dbms_output.put_line('出现异常' );
end;


/*
 嵌套语句块
*/
declare
  -- 声明部分（定义变量、类型、游标等）
  -- 声明变量的格式： 变量名  类型(精度)[:= 初始值]
  -- 变量的声明周期：变量declare声明之后紧跟的 begin和 end 之间
  v_var number(5) := 10;
  v_name varchar2(20) := 'abc';
begin
  -- 嵌套语句块
  declare
     v_var2 number(5) := 11;
  begin
    dbms_output.put_line('inner block: ' || (v_var2 + v_var));
    
    exception 
    when others then
         dbms_output.put_line('出现异常');
  end;
  
  --dbms_output.put_line('outer block: ' || (v_var2 + v_var));
      
  dbms_output.put_line('main end ...');
  
end;

/*
IF 语句
  
   if 条件 then 
     条件满足时的语句块
     [elsif 条件  then 条件满足时的语句块]
     [else 条件不满足时的语句块]
   end if
  */
  
  declare
   v_score number := &score;
  begin
    if v_score < 60 then
      dbms_output.put_line('不及格');
    elsif v_score < 75 then
      dbms_output.put_line('良好');
    elsif v_score < 90 then
      dbms_output.put_line('优秀');
    else
      dbms_output.put_line('很秀');
    end if;
  end;

/*
  CASE 多条件语句
    
  */
  -- 不等值 case 语句
  declare
       v_score number := &score;
  begin
    case 
      when v_score < 60  then 
        dbms_output.put_line('不及格');
       when v_score < 75 then
        dbms_output.put_line('良好');
      when v_score < 90 then
           dbms_output.put_line('优秀');
      else
           dbms_output.put_line('很秀');
    end case;
  end;
   
  -- 等值 case 语句 
    declare
       v_menu number := &menu;
  begin
    case v_menu
      when 1 then 
        dbms_output.put_line('选择的是：1');
       when 2 then
        dbms_output.put_line('选择的是：2');
      when 3 then
           dbms_output.put_line('选择的是：3');
      else
           dbms_output.put_line('选择的是：其他');
    end case;
  end;

-- 循环
   -- 无条件Loop循环
   declare
      v_i number := 1;
      v_sum number := 0;
   begin
     loop
       if v_i > 100 then
         -- 跳出循环
         exit;
       end if;
       -- 注意：没有 += 、 ++ 等符号
       v_sum := v_sum + v_i;
       v_i := v_i + 1;
       
     end loop;
     
     dbms_output.put_line('sum=' || v_sum);
   end;
   
   -- 无条件Loop循环
   declare
      v_i number := 1;
      v_sum number := 0;
   begin
     loop
       -- 带条件的跳出循环
       exit when v_i > 10;
       
       -- 注意：没有 += 、 ++ 等符号
       v_sum := v_sum + v_i;
       v_i := v_i + 1;
       
     end loop;
     
     dbms_output.put_line('sum=' || v_sum);
   end;

-- 数值for循环
declare
   v_sum number := 0;
begin
  for v_i in 1..100 loop
    v_sum := v_sum + v_i;
  end loop;
  dbms_output.put_line('for sum=' || v_sum);
end;

--  in reverse 可以翻转数值的循环
begin
  for v_i in reverse 1..10 loop
    dbms_output.put_line('i =' || v_i);
  end loop;
  
end;

-- 条件循环： while
declare
   v_sum number := 0;
   v_i number:=1;
begin
  while v_i <= 100 loop
    v_sum := v_sum + v_i;
    v_i := v_i + 1;
  end loop;
  dbms_output.put_line('while sum=' || v_sum);
end;
                   
-- 九九乘法表    
declare
  v_i number(3); -- 外层循环变量
  v_j number(3); -- 内层循环变量
begin
  for v_i in 1..9 -- 开始外层循环
  loop
      for v_j in 1 .. v_i -- 开始内层循环
      loop
          dbms_output.put(v_i || '*' || v_j || '=' || v_i * v_j || ' ');
      end loop;
      dbms_output.put_line(''); -- 换行
  end loop;
end;
/*
   预设的异常：
      no_data_found ： 没找到数据
      CASE_NOT_FOUND： CASE语句中没有任何WHEN子句满足条件，并且没有编写ELSE子句。
      ...
*/

-- 异常处理
declare
   v_loc varchar2(20);
   v_dname varchar2(20);
   -- 自定义异常名
   no_parent_item exception;
   --捆绑名称到 -2291错误编码
   PRAGMA EXCEPTION_INIT(no_parent_item,-2291);
   
begin
  insert into emp(empno, deptno) values(1002, 50);
  select dname, loc into v_dname, v_loc from dept where deptno = &dno;
  dbms_output.put_line(v_dname  || '  ' || v_loc);
  exception 
    when no_data_found then 
         dbms_output.put_line('没有此部门');
    when no_parent_item then
      dbms_output.put_line('不能添加没有的部门');
    when others then
      dbms_output.put_line('其他异常');
end;

-- 自定义异常：
declare
   v_gender varchar2(20) := '&输入性别';
   -- 自定义异常名
   no_gender exception;
   --捆绑名称到 -2291错误编码
   PRAGMA EXCEPTION_INIT(no_gender,-20000);
begin
  if v_gender != '男' and v_gender != '女' then
    -- 抛出异常
    raise_application_error(-20000, '性别只能是男或者女');
  end if;
  dbms_output.put_line('性别：' || v_gender);
  
  exception when no_gender then dbms_output.put_line('性别异常');
end;

------------ sqlerrm : 获取异常的类型，编号，文本信息

declare
   v_gender varchar2(20) := '&输入性别';

begin
  if v_gender != '男' and v_gender != '女' then
    -- 抛出异常
    raise_application_error(-20000, '性别只能是男或者女');
  end if;
  dbms_output.put_line('性别：' || v_gender);
  
  exception when others then dbms_output.put_line(sqlerrm);
end;

--- 使用嵌套语句块，模仿 try catch 后还能有正常语句
declare
   v_gender varchar2(20) := '&输入性别';

begin
  --模拟 java 中 try 代码段
  begin
     if v_gender != '男' and v_gender != '女' then
       -- 抛出异常
       raise_application_error(-20000, '性别只能是男或者女');
     end if;
     dbms_output.put_line('性别：' || v_gender);
     -- 模拟catch
     exception when others then dbms_output.put_line(sqlerrm);
  end;
  -- 后续有正常语句
  dbms_output.put_line('main end ...');
end;

-- select ... into ...

declare
   v_empno number;
   v_ename varchar2(2);
   v_hiredate date;
begin
  select hiredate, ename, empno into v_hiredate, v_ename, v_empno from emp where empno = 7788;
  
  dbms_output.put_line(to_char(v_hiredate,'yyyy-mm-dd hh24:mi:ss')||' | '|| v_ename||' | '||v_empno);
  
  exception when others then 
    dbms_output.put_line(sqlerrm);
end;

-- 列引用类型 %type

declare
   v_empno emp.empno%type;
   v_ename emp.ename%type;
   v_hiredate emp.hiredate%type;
begin
  select hiredate, ename, empno into v_hiredate, v_ename, v_empno from emp where empno = 7788;
  
  dbms_output.put_line(to_char(v_hiredate,'yyyy-mm-dd hh24:mi:ss')||' | '|| v_ename||' | '||v_empno);
  
  exception when others then 
    dbms_output.put_line(sqlerrm);
end;

--------- 行引用类型：  %rowtype
declare
   v_row emp%rowtype;
begin
  select * into v_row from emp where empno = 7788;
  
  dbms_output.put_line(to_char(v_row.hiredate,'yyyy-mm-dd hh24:mi:ss')||' | '|| v_row.ename
          ||' | '||v_row.empno
          ||' | '||v_row.job
          ||' | '||v_row.mgr
          ||' | '||v_row.sal);
  
  exception when others then 
    dbms_output.put_line(sqlerrm);
end;

-- 游标
/*
定义游标：
   CURSOR 游标名称 [(parameter[, parameter]...)] [RETURN return_type] IS 子查询; 
   
   使用游标：
   1- 打开游标（打开游标标记）  -- open
   2- 提取当前游标指向数据      -- fetch
   3- 跳转游标到下一行 ----------|
   4- 判断游标是否指向行尾
   5- 如果没有指向行尾继续返回第2条执行
   6- 关闭游标 ------------------- close
*/

-- 游标的基本使用
declare
   -- 定义游标：
   cursor cur_emp is select * from emp;
   -- 定义行引用类型
   v_row emp%rowtype;
   
begin
  
   -- 打开游标
   open cur_emp;
   
   loop
        -- 提取游标数据
        fetch cur_emp into v_row;
        -- 如果没有提取到数据则跳出循环
        exit when cur_emp%notfound;
   
        dbms_output.put_line(to_char(v_row.hiredate,'yyyy-mm-dd hh24:mi:ss')||' | '|| v_row.ename
          ||' | '||v_row.empno
          ||' | '||v_row.job
          ||' | '||v_row.mgr
          ||' | '||v_row.sal);
        
   end loop;
   -- 关闭游标
   close cur_emp;
  
end;

-- 使用 while循环
declare
   -- 定义游标：
   cursor cur_emp is select empno,ename,hiredate,sal from emp;
   -- 使用游标的行类型，定义行引用类型
   v_row cur_emp%rowtype;
   
begin
  
   -- 打开游标
   open cur_emp;
   -- 提取游标数据
   fetch cur_emp into v_row;
   
   while cur_emp%found loop
   
        dbms_output.put_line(to_char(v_row.hiredate,'yyyy-mm-dd hh24:mi:ss')
          ||' | '||v_row.empno
          ||' | '||v_row.ename
          ||' | '||v_row.sal);
        -- 提取游标数据
        fetch cur_emp into v_row;
        
   end loop;
   -- 关闭游标
   close cur_emp;
end;

-- 游标for循环: 自动 open fetch close;
declare
   -- 定义游标：
   cursor cur_emp is select empno,ename,hiredate,sal from emp;
   
begin
  for v_row in cur_emp loop
     dbms_output.put_line(to_char(v_row.hiredate,'yyyy-mm-dd hh24:mi:ss')
          ||' | '||v_row.empno
          ||' | '||v_row.ename
          ||' | '||v_row.sal);
  end loop;
   
end;

-- 可以直接使用子查询充当匿名游标对象
begin
  for v_row in (select * from emp) loop
     dbms_output.put_line(to_char(v_row.hiredate,'yyyy-mm-dd hh24:mi:ss')
          ||' | '||v_row.empno
          ||' | '||v_row.ename
          ||' | '||v_row.sal);
  end loop;
   
end;
   

-- 参数化游标
declare
   -- 定义游标：
   cursor cur_emp(dno number) is select empno,ename,hiredate,sal,deptno from emp where deptno = dno;
   -- 使用游标的行类型，定义行引用类型
   v_row cur_emp%rowtype;
   
begin
  
   -- 打开游标
   open cur_emp(10);
   -- 提取游标数据
   fetch cur_emp into v_row;
   
   while cur_emp%found loop
   
        dbms_output.put_line(to_char(v_row.hiredate,'yyyy-mm-dd hh24:mi:ss')
          ||' | '||v_row.empno
          ||' | '||v_row.ename
          ||' | '||v_row.sal
          ||' | '||v_row.deptno);
        -- 提取游标数据
        fetch cur_emp into v_row;
        
   end loop;
   -- 关闭游标
   close cur_emp;
end;

/**
显示游标的属性
  %FOUND		指明是否取到了指定的记录行
  %ISOPEN		指明游标是打开的还是关闭的
  %NOTFOUND		指示FETCH是否失败或是否还有可取的记录行
  %ROWCOUNT		指明总共取到了多少行数据

*/
declare
   -- 定义游标：
   cursor cur_emp(dno number) is select empno,ename,hiredate,sal,deptno from emp where deptno = dno;
   -- 使用游标的行类型，定义行引用类型
   v_row cur_emp%rowtype;
   
begin
  
   -- 打开游标
   if not cur_emp%isopen then
      open cur_emp(10);
   end if;
   -- 提取游标数据
   fetch cur_emp into v_row;
   
   while cur_emp%found loop
   
        dbms_output.put_line(
         cur_emp%rowcount || '- ' ||
        to_char(v_row.hiredate,'yyyy-mm-dd hh24:mi:ss')
          ||' | '||v_row.empno
          ||' | '||v_row.ename
          ||' | '||v_row.sal
          ||' | '||v_row.deptno);
        -- 提取游标数据
        fetch cur_emp into v_row;
        
   end loop;
   -- 关闭游标
   if cur_emp%isopen then
      close cur_emp;
   end if;
end;

-- 练习： 使用参数化游标输出：  
       -- select empno,ename,deptno,dname from emp e, dept d 
       --       where e.deptno = d.deptno
       --              and deptno = ?;
--       指定部门的所有行
declare
         cursor cur_emp_dept(dno number) is  
                select empno,ename,d.deptno,dname from emp e, dept d 
                       where e.deptno = d.deptno
                             and d.deptno = dno;
         v_row cur_emp_dept%rowtype;
begin
  open cur_emp_dept(&departmentNO);
  fetch cur_emp_dept into v_row;
  while cur_emp_dept%found loop
    dbms_output.put_line(v_row.empno 
                        ||' | '|| v_row.ename
                        ||' | '|| v_row.deptno
                        ||' | '|| v_row.dname);
    fetch cur_emp_dept into v_row;
  end loop;
  close cur_emp_dept;
end;

-- 游标的本质：将表中的数据，提取到语句块中，进行逻辑应用
-- 更新游标：修改当前游标指向的数据行
/*
   定义更新游标：
   cursor 游标名称 is 查询 for update
   
   使用 for update 是用来锁定数据，以免脏读
*/

-- 更新20部门，人员 工资 + 100;
declare
   cursor cur_emp is select * from emp where deptno=20 for update; 
begin
  for v_row in cur_emp loop
    if v_row.sal < 2000 then
       update emp set sal = sal + 100 where current of cur_emp; -- 游标指向的当前行;
    end if;
  end loop;
end;

-- 游标变量
declare
   -- 定义游标类型
   type cus_cur_type is ref cursor;
   -- 使用游标类型定义游标变量
   v_cur1 cus_cur_type;
   
   -- 定义行引用
   v_row emp%rowtype;
begin
  -- 打开游标变量时，定义游标子查询
  open v_cur1 for select * from emp;
  fetch v_cur1 into v_row;
  while v_cur1%found loop
    dbms_output.put_line(v_row.empno 
                        ||' | '|| v_row.ename
                        ||' | '|| v_row.deptno);
    fetch v_cur1 into v_row;
  end loop;
  
  close v_cur1;
  
end;

-- 查询来自不同表的多个列
declare
   -- 定义游标类型
   type cus_cur_type is ref cursor;
   -- 使用游标类型定义游标变量
   v_cur1 cus_cur_type;
   
   -- 自定义记录类型
   type cur_row_type is record(
        v_empno emp.empno%type,
        v_ename emp.ename%type,
        v_deptno dept.deptno%type,
        v_dname dept.dname%type
   );
   
   -- 定义行引用, 使用自定义记录类型定义
   v_row cur_row_type;
   
   v_dname dept.dname%type;
begin
  -- 打开游标变量时，定义游标子查询
  open v_cur1 for select e.empno, e.ename, d.deptno, d.dname from emp e, dept d where d.deptno = e.deptno;
  fetch v_cur1 into v_row;
  while v_cur1%found loop
    dbms_output.put_line(v_row.v_empno 
                        ||' | '|| v_row.v_ename
                        ||' | '|| v_row.v_deptno
                        ||' | '|| v_row.v_dname);
    fetch v_cur1 into v_row;
  end loop;
  
  close v_cur1;
  
  open v_cur1 for select d.dname from dept d;
  fetch v_cur1 into v_dname;
  while v_cur1%found loop
    dbms_output.put_line(v_cur1%rowcount || ' - '
                        ||v_dname);
     fetch v_cur1 into v_dname;
  end loop;
  
  close v_cur1;
  
end;

-- 带返回类型的游标变量
declare
   type cur_row_type is record(
        v_empno emp.empno%type,
        v_ename emp.ename%type,
        v_deptno dept.deptno%type,
        v_dname dept.dname%type
   );

   -- 定义游标类型
   type cus_cur_type is ref cursor return cur_row_type;
   -- 使用游标类型定义游标变量
   v_cur1 cus_cur_type;

   
   -- 定义行引用, 使用自定义记录类型定义
   v_row cur_row_type;
   
   v_dname dept.dname%type;
begin
  -- 打开游标变量时，定义游标子查询
  open v_cur1 for select e.empno, e.ename, d.deptno, d.dname from emp e, dept d where d.deptno = e.deptno;
  fetch v_cur1 into v_row;
  while v_cur1%found loop
    dbms_output.put_line(v_row.v_empno 
                        ||' | '|| v_row.v_ename
                        ||' | '|| v_row.v_deptno
                        ||' | '|| v_row.v_dname);
    fetch v_cur1 into v_row;
  end loop;
  
  close v_cur1;
  /*
  open v_cur1 for select d.dname from dept d;
  fetch v_cur1 into v_dname;
  while v_cur1%found loop
    dbms_output.put_line(v_cur1%rowcount || ' - '
                        ||v_dname);
     fetch v_cur1 into v_dname;
  end loop;
  
  close v_cur1;
  */
end;


--- 存储过程 ---------------
-- 无参存储过程
create or replace procedure pro_print_emp
as
       -- 声明部分
       cursor cur_emp is select * from emp;
begin
  -- 执行部分
  for v_row in cur_emp loop
    dbms_output.put_line(cur_emp%rowcount 
                        || ' - ' || v_row.empno
                        || ' - ' || v_row.ename);
  end loop;
  
  -- 异常处理部分
end;

-- 调用存储过程：
SQL> execute pro_print_emp; -- 直接输出过程结果

SQL> call pro_print_emp(); -- 外部调用（JDBC操作）

-- 有参存储过程 -----------
-- 注意：形参不能设定其精度，否则编译错误
create or replace procedure pro_print_emp(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(cur_emp%rowcount 
                        || ' - ' || v_row.empno
                        || ' - ' || v_row.ename);
  end loop;
  
  -- 异常处理部分
end;

-- 调用带参数的过程
SQL> call pro_print_emp(30);
SQL> execute pro_print_emp(10);

-- 练习，使用存储过程接收 部门名称，部门位置，进行添加新部门
-- 注意，其部门编号的主键，需要先查询其主键（部门编号）最大值后 + 10 赋予新部门

create or replace procedure pro_add_dept(v_dname varchar2, v_loc varchar2)
as
       --定义保存新部门账号变量
       v_dno dept.deptno%type;
begin
       select max(deptno) + 10 into v_dno from dept;
       insert into dept values(v_dno, v_dname, v_loc);
  
end pro_add_dept;

-- 根据传入参数，生成新员工
create or replace procedure pro_add_emp(v_ename varchar2, v_job varchar2, v_sal number, v_deptno number) is
begin
  insert into emp(empno, ename, job, sal, deptno)
         values(
              (select max(empno)+1 from emp),
              v_ename, v_job,v_sal,v_deptno
         );
   commit;
end pro_add_emp;

------- 调用带参存储过程的方式：
-- 1- 按位置调用
call pro_add_emp('小明', '研发', 12000, 10);

-- 2- 按名称调用
call pro_add_emp(v_sal => 13000,v_deptno => 20,v_job => '维护',v_ename => '小丽');

-- 3- 混合调用：只能先按位置，再按名称调用
call pro_add_emp('小丽2', '维护',v_deptno => 20, v_sal => 13000);
-- 注意：一旦开始按名称调用后，就不能按位置再进行调用
call pro_add_emp('小丽2', '维护', v_sal => 13000, 20); -- 错误示例

-- 调用规范：仅仅使用按位置调用，不能按名称或者混合调用

-------- 形参类别：
--- 1- 输入型 形参： 仅仅用来获取实参值

--- 2- 输出型 形参： 仅仅用来输出结果的形参
-- 根据编号查询姓名
create or replace procedure pro_get_ename(v_empno in number, v_ename out varchar2) is
begin
  select ename into v_ename from emp where empno = v_empno;
  exception when no_data_found then
    v_ename := '查无此员工';
end pro_get_ename;
-- 调用输出类型参数
-- 定义一个，输出类型的全局变量
SQL> variable v_name varchar2(20);
-- 执行过程是，将全局变量用  :变量 格式填入指定参数位置
SQL> exec pro_get_ename(7788, :v_name);


--- 3- 输入输出型 形参： 执行时用来获取实参，执行后将结果保存给实参带出过程
-- 根据员工姓名查询工作
create or replace procedure pro_get_job( v_val in out varchar2) is
begin
  select ename into v_val from emp where ename = v_val;
  exception when no_data_found then
    v_val := '查无此员工';
end pro_get_job;
-- 调用输入输出类型参数过程
-- 3.1- 定义全局变量
SQL> var v_name varchar2(20);
-- 3.2-赋值
begin
  :v_name := 'SCOTT';
end;
--  3.3 - 调用
SQL> call pro_get_job(:v_name);

-- 形参类型定义规范：
-- 1- 仅仅定义输入类型参数，不能定义输出、输入输出类型参数；
-- 2- 如果需要返回信息，则使用函数：function

----- 函数： 带返回值的 命名语句块
-- 根据员工编号获取姓名
create or replace function fun_get_ename(v_empno number) return varchar2 
is
  v_ename emp.ename%type;
begin
  select ename into v_ename from emp where empno = v_empno;
  return v_ename;
  exception when others then
    return '查无此员工';
end fun_get_ename;

-- 函数调用
-- 1- 定义全局变量
SQL> var v_name varchar2(20);
-- 2- 在匿名块中调用函数并将其返回值赋予全局变量
begin
 :v_name := fun_get_ename(7788);
end;

-- 2- 可以在查询中调用
SQL> select fun_get_ename(7839) from dual;

-- 3- 可以调用后，直接将结果输出
SQL> exec dbms_output.put_line(fun_get_ename(7566));
-- 定义存储过程
create or replace procedure pro_print(val varchar2) is
begin
  dbms_output.put_line(val);
end pro_print;
-- 
SQL> exec pro_print(fun_get_ename(7369));

------ 包 --------

-- 包有两部分：
-- 1- 包的声明部分，这个部分声明的变量和函数、存储过程，可以在包以外调用
create or replace package pak_emp is

  -- Public constant declarations
  pi constant number := 3.14;

  -- Public variable declarations
  v_ename varchar2(20);
  v_sal emp.sal%type;

  -- Public function and procedure declarations
  function get_ename_by_empno(v_no number) return varchar2;
  procedure print_ename_by_empno(v_no number);
  procedure print_job_by_empno(v_no number);

end pak_emp;

-- 2- 包的实现部分，这个部分对包声明部分的函数、过程的定义进行实现功能，
-- 如果此部分声明了一个额外的函数或者过程，则是私有的，不能在外部调用。
create or replace package body pak_emp is

  -- Private type declarations
  v_job varchar2(20);
  
  -- 定义私有过程或者函数
  function get_job_by_empno(v_no number) return varchar2
    is
    begin
      select job into v_job from emp where empno = v_no;
      return v_job;
       exception when others then
             return '查无此员工';
    end;
   procedure print_job(v_no number)
     is
     begin
       dbms_output.put_line(get_job_by_empno(v_no));
     end;
  
  
  -- 实现公有函数的定义
  function get_ename_by_empno(v_no number) return varchar2
    is
    begin
           select ename into v_ename from emp where empno = v_no;
           return v_ename;
           
           exception when others then
             return '查无此员工';
    end;
   
  -- 实现公有存储过程的定义 
  procedure print_ename_by_empno(v_no number) 
    is
    begin
      dbms_output.put_line(get_ename_by_empno(v_no));
    end;
    
  procedure print_job_by_empno(v_no number)
       is
       begin
         print_job(v_no);
       end;
  
end pak_emp;