oracle数据库的存储过程PROCEDURE与函数FUNCTION示例

程序结构
=============================================================
例题1:
取出s_emp表（参见oracle默认数据库test中的相关表）中的记录用变量接收并输出
s_emp //变量要和s_emp表中的数据类型相匹配
___________________________________________________________
DECLARE
  v_id NUMBER(7);
  v_fname VARCHAR2(25);
  v_salary NUMBER(11,2);
BEGIN
  select id,first_name,salary
  into v_id,v_fname,v_salary
  from s_emp
  where id =1;     //唯一确定一条数据,否则会出错
  DBMS_OUTPUT.PUT_LINE(v_id||' '||v_fname||' '||v_sarary);
END;
___________________________________________________________

如果把s_emp表中的first_name字段的长度改为VARCHAR2(30)
那么也要把v_fname改成VARCHAR2(30)
解决方法:
v_fname VARCHAR2(25);--->
v_fname s_emp.first_name%TYPE;

v_id NUMBER(7);--->
v_id s_emp.id%TYPE;

v_salary NUMBER(11,2);--->
v_salary s_emp.salary%TYPE;

==============================================================
例题2
_______________________________________________________
DECLARE
  TYPE t_emp IS RECORD(
  id s_emp.id%TYPE,
  fname s_emp.first_name%TYPE,
  salary s_emp.salary%TYPE
  );
  v_emp t_emp;
BEGIN
  select id,first_name,salary
  into v_emp.id,v_emp.fname,v_emp.salary   <=>into v_emp
  from s_emp
  where id =1;  
  DBMS_OUTPUT.PUT_LINE(v_emp.id||' '||v_emp.fname
  ||' '||v_emp.sarary);
END;
_______________________________________________________
into v_emp.id,v_emp.fname,v_emp.salary<=>into v_emp
into简化前提
RECORD定义的变量顺序和select查询时的顺序必须相同

=============================================================

例题3
_______________________________________________________
DECLARE
  TYPE t_emp IS RECORD(
  id s_emp.id%TYPE,
  fname s_emp.first_name%TYPE,
  salary s_emp.salary%TYPE
  );

  v_emp t_emp;
 
  v_emp2 t_emp;

BEGIN
  select id,first_name,salary
  into v_emp
  from s_emp
  where id =1;  

/*v_emp2.id := v_emp.id;
  v_emp2.fname := v_emp.fname;
  v_emp2.salary := v_emp.salary;*/
  v_emp2 := v_emp;

  DBMS_OUTPUT.PUT_LINE(v_emp.id||' '||v_emp.fname
  ||' '||v_emp.sarary);
END;
_______________________________________________________
  (v_emp2.id := v_emp.id;
  v_emp2.fname := v_emp.fname;
  v_emp2.salary := v_emp.salary;)
  <=>
  (v_emp2 := v_emp;)

==============================================================
例题4
_____________________________________________________
DECLARE
  TYPE t_emp IS RECORD(
  id s_emp.id%TYPE,
  fname s_emp.first_name%TYPE,
  salary s_emp.salary%TYPE
  );

  TYPE t_emp2 IS RECORD(
  id s_emp.id%TYPE,
  fname s_emp.first_name%TYPE,
  salary s_emp.salary%TYPE
  );

  v_emp t_emp;
 
  v_emp2 t_emp2;

BEGIN
  select id,first_name,salary
  into v_emp.id,v_emp.fname,v_emp.salary   <=>into v_emp
  from s_emp
  where id =1;  
  v_emp2 := v_emp;                 --error类型不一致
  DBMS_OUTPUT.PUT_LINE(v_emp.id||' '||v_emp.fname
  ||' '||v_emp.sarary);
END;
____________________________________________________________

===============================================================
例题5:
1>__________________________________________________________
DECLARE
  TYPE t_emp IS RECORD(
  id s_emp.id%TYPE,
  lname s_emp.last_name%TYPE,
  fname s_emp.first_name%TYPE,  
  uid s_emp.userid%TYPE
  .....
  salary s_emp.salary%TYPE,
  );

  v_emp t_emp;

BEGIN
  select *
  into v_emp
  from s_emp
  where id =1;  
  DBMS_OUTPUT.PUT_LINE(v_emp.id||' '||v_emp.fname
  ||' '||v_emp.sarary);
END;
_________________________________________________________

<1>vs<2>
  TYPE t_emp IS RECORD(
  id s_emp.id%TYPE,
  lname s_emp.last_name%TYPE,
  fname s_emp.first_name%TYPE,  <=>v_emp s_emp%ROWTYPE;
  uid s_emp.userid%TYPE
  .....
  salary s_emp.salary%TYPE,
  );

  v_emp t_emp;


2>________________________________________________________
DECLARE
  v_emp s_emp%ROWTYPE;    //v_emp和表s_emp始终有相同的结构

BEGIN
  select *
  into v_emp
  from s_emp
  where id =1;  
  DBMS_OUTPUT.PUT_LINE(v_emp.id||' '||v_emp.first_name
  ||' '||v_emp.sarary);
END;

输出时用的字段名就是s_emp表中的字段名
__________________________________________________________

============================================================

table类型
BINARY_INTEGER的范围(-2147483647<-->+2147483647)
底层是用二叉数实现的.插入时排序
_________________________________________________________
DECLARE
  TYPE t_emp IS TABLE OF s_emp%ROWTYPE INDEX BY BINARY_INTEGER;

  v_emp t_emp;
BEGIN
  SELECT *
     INTO v_emp(100)  --索引的位置没有太多要求v_emp(-100)
   FROM s_emp
 WHERE id=1;
 
 SELECT *
   INTO v_emp(2000)
  FROM s_emp
 WHERE id=2;
 
 DBMS_OUTPUT.PUT_LINE(v_emp(2000).id||' '||
 v_emp(2000).first_name);
END;
_________________________________________________________

=============================================================

练习:
从s_emp表中查id,first_name,dept_id,salary
1>RECORD
2>ROWTYPE
3>找出信息后保存到TABLE类型变量中
lhj_test1.sql
<1>
DECLARE
  TYPE v_emp IS RECORD(
    id s_emp.id%TYPE,
    fname s_emp.first_name%TYPE,
    did s_emp.dept_id%TYPE,
    salary s_emp.salary%TYPE
  );
  t_emp v_emp;
BEGIN
  SELECT id,first_name,dept_id,salary
  INTO t_emp
  FROM s_emp
  WHERE id=1;
 DBMS_OUTPUT.PUT_LINE(t_emp.id||' '||t_emp.fname||
 ' '||t_emp.did||' '||t_emp.salary);
END;

<2>
DECLARE
  t_emp s_emp%ROWTYPE;
BEGIN
  SELECT *
  INTO t_emp
  FROM s_emp
  WHERE id=1;
 DBMS_OUTPUT.PUT_LINE(t_emp.id||' '||t_emp.first_name||
 ' '||t_emp.dept_id||' '||t_emp.salary);
END;

<3>
DECLARE
 TYPE t_emp IS TABLE OF s_emp%ROWTYPE INDEX BY BINARY_INTEGER;

 v_emp temp;

BEGIN
 SELECT *
  INTO v_emp(1)
  FROM s_emp
  WHERE id=1;
 DBMS_OUTPUT.PUT_LINE(v_emp(1).id||' '||v_emp(1).first_name||
 ' '||v_emp(1).dept_id||' '||v_emp(1).salary);

END;
==============================================================
变量的作用域与可见性
1>
<<Outer>>--标号
DECLARE
  v_Num NUMBER := 100;
BEGIN
  v_Num := 1;
 
  DECLARE
   v_Str VARCHAR2(20) := 'hello';
   v_Num NUMBER := 200;
  BEGIN
   DBMS_OUTPUT.PUT_LINE('v_Str = ' || v_Str);
   DBMS_OUTPUT.PUT_LINE('v_Num = ' || v_Num);  --v_Num=200
 --DBMS_OUTPUT.PUT_LINE('v_Num = ' || Outer.v_Num);
  END;

 --DBMS_OUTPUT.PUT_LINE('v_Str = ' || v_Str);   --error
END;

2>
DECLARE
  v_Num NUMBER := 100;
BEGIN
  v_Num1 := 1;
 
  DECLARE
   v_Str VARCHAR2(20) := 'hello';
   v_Num2 NUMBER := 200;
  BEGIN
   DBMS_OUTPUT.PUT_LINE('v_Str = ' || v_Str);
   DBMS_OUTPUT.PUT_LINE('v_Num = ' || v_Num2);  
 --DBMS_OUTPUT.PUT_LINE('v_Num = ' || v_Num);
  END;

 --DBMS_OUTPUT.PUT_LINE('v_Str = ' || v_Str);   --error
END;
==============================================================
控制语句
1.条件语句
<1>
DECLARE
  v_flag BOOLEAN;
BEGIN
  IF v_flag THEN
   DBMS_OUTPUT.PUT_LINE('v_flag is TRUE');
  ELSIF NOT v_flag THEN
   DBMS_OUTPUT.PUT_LINE('v_flag is FALSE');
  ELSE
   DBMS_OUTPUT.PUT_LINE('v_flag is NULL');
  END IF;
END;

<2>
DECLARE
  v_flag BOOLEAN;
BEGIN
  IF v_flag THEN
   DBMS_OUTPUT.PUT_LINE('v_flag is TRUE');
  ELSIF v_flag IS NULL THEN
   DBMS_OUTPUT.PUT_LINE('v_flag is NULL');
  ELSE
   DBMS_OUTPUT.PUT_LINE('v_flag is FALSE');
  END IF;
END;

练习
lhj_test2.sql
如果工资大于1500,奖金按照20%计算.
如果工资在1000-1500,奖金按10%计算.
如果工资小于1000,奖金按照5%计算.
_______________________________________________
DECLARE
  v_sal s_emp.salary%TYPE;
  v_bonus s_emp.salary%TYPE;
BEGIN
  SELECT salary
   INTO v_sal
   FROM s_emp
  WHERE id=1;

  IF v_sal > 1500 THEN
   v_bonus := v_sal * 0.2;
  ELSIF v_sal>=1000 THEN
   v_bonus := v_sal * 0.1;
  ELSE
   v_bonus := v_sal * 0.05;
  END IF;

  DBMS_OUTPUT.PUT_LINE('BONUS: ' || v_bonus);
END;
_______________________________________________

2.循环语句
1>简单循环
LOOP
END LOOP;
例题1
lhj_test3.sql
求1-100的连加和
__________________________________
DECLARE
 v_ret NUMBER :=0;
 i     NUMBER :=1;
BEGIN
 LOOP
  v_ret := v_ret+i;
  i := i + 1;

  EXIT WHEN i>100;
 END LOOP;
 DBMS_OUTPUT.PUT_LINE(v_ret);
END;
____________________________________

2>WHILE循环
WHILE boolean_expression LOOP
END LOOP;
求1-100的连加和
_____________________________________
DECLARE
 v_ret NUMBER :=0;
 i     NUMBER :=1;
BEGIN
 WHILE i<=100 LOOP
  v_ret := v_ret+i;
  i := i + 1;
 
 END LOOP;
 DBMS_OUTPUT.PUT_LINE(v_ret);
END;
_____________________________________

3>FOR循环
FOR loop_counter IN[REVERSE] low_bound...high_bound LOOP
...
END LOOP;
求1-100的连加和
_____________________________________
DECLARE
 v_ret NUMBER :=0;
BEGIN
  for i IN 1..100 LOOP
  v_ret := v_ret+i;  
 
 END LOOP;
 DBMS_OUTPUT.PUT_LINE(v_ret);
END;
_____________________________________



练习
lhj_test4.sql
1>从s_emp表中循环找出id为1-5的员工，把结果保存在table类型的变量里。
2>循环的从table变量中把5名员工信息取出来依次输出
方法一:
_______________________________________________________________
DECLARE
 TYPE t_emp IS TABLE OF s_emp%ROWTYPE INDEX BY BINARY_INTEGER;
 v_emp t_emp;
 i     BINARY_INTEGER :=1;
 j     BINARY_INTEGER :=1;

BEGIN
 WHILE i<=5 LOOP
  SELECT *
   INTO v_emp(i)
   FROM s_emp
  WHERE id = i;

  i := i + 1;  
 END LOOP;

 LOOP
  DBMS_OUTPUT.PUT_LINE(v_emp(j).id||' '||v_emp(j).first_name);

   j := j+1;
   EXIT WHEN j>5;
 END LOOP;

END;
________________________________________________________________

方法二:
for 不用为变量v_cnt声明就可以用,编译器会根据 1..5自动为
s_emp声明,并且可以自动加1.
________________________________________________________
DECLARE
 v_emp s_emp%ROWTYPE;
BEGIN
  for v_cnt IN 1..5 LOOP   
  SELECT *
    INTO v_emp
    FROM s_emp
  WHERE id = v_cnt;

  DBMS_OUTPUT.PUT_LINE(v_emp.id||' '||v_emp.first_name);
 END LOOP;
 
END;

1 Carmen
2 LaDoris
3 Midori
4 Mark
5 Audry
______________________________________________________
DECLARE
 v_emp s_emp%ROWTYPE;
BEGIN
  for v_cnt IN REVERSE 1..5 LOOP   
  SELECT *
    INTO v_emp
    FROM s_emp
  WHERE id = v_cnt;

  DBMS_OUTPUT.PUT_LINE(v_emp.id||' '||v_emp.first_name);
 END LOOP;
 
END;
//REVERSE从上限值到下限值递减循环.
5 Audry
4 Mark
3 Midori
2 LaDoris
1 Carmen
_______________________________________________________

SQL in PL/SQL
动态SQL
lhj_test6.sql
__________________________________________________________________
DECLARE
   v_Str VARCHAR2(400);
BEGIN
   v_Str := 'create table temp_lhj(id number,coll varchar2(20))';
  EXECUTE IMMEDIATE v_Str;
END;
__________________________________________________________________

DECLARE
  v_Date VARCHAR2(20);
BEGIN
  v_Date := to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
  DBMS_OUTPUT.PUT_LINE(v_Date);
END;
______________________________________________________


======================================================================

Cursor游标
例:lhj_test7.sql
1>________________________________________________________________
DECLARE
  v_deptId   s_emp.dept_id%TYPE :=31;
  v_emp      s_emp%ROWTYPE;
 
  CURSOR  cur_emp1 IS
    SELECT * FROM s_emp
    WHERE dept_id = v_deptId;
BEGIN
 --v_deptId := 50;
   OPEN cur_emp1;
 
   FETCH cur_emp1 INTO v_emp;
   DBMS_OUTPUT.PUT_LINE(v_emp.id||' '||v_emp.first_name);

   FETCH cur_emp1 INTO v_emp;
   DBMS_OUTPUT.PUT_LINE(v_emp.id||' '||v_emp.first_name);

   CLOSE cur_emp1;
 
END;
2>简单循环
_______________________________________________________________
DECLARE
  v_deptId   s_emp.dept_id%TYPE :=31;
  v_emp      s_emp%ROWTYPE;
 
  CURSOR  cur_emp1 IS
    SELECT * FROM s_emp
    WHERE dept_id = v_deptId;
BEGIN
   OPEN cur_emp1;
   LOOP
   FETCH cur_emp1 INTO v_emp;
   EXIT WHEN cur_emp1%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE(v_emp.id||' '||v_emp.first_name);   
   
   END LOOP;

   CLOSE cur_emp1;
 
END;

lhj_test8.sql
3>while循环
____________________________________________________
DECLARE
  v_deptId   s_emp.dept_id%TYPE :=31;
  v_emp      s_emp%ROWTYPE;
 
  CURSOR  cur_emp1 IS
    SELECT * FROM s_emp
    WHERE dept_id = v_deptId;
BEGIN
   OPEN cur_emp1;

   FETCH cur_emp1 INTO v_emp;

   while cur_emp1%FOUND  LOOP      
   DBMS_OUTPUT.PUT_LINE(v_emp.id||' '||v_emp.first_name);
   FETCH cur_emp1 INTO v_emp;  
   
   END LOOP;

   CLOSE cur_emp1;
 
END;
4>for循环
___________________________________________________________
DECLARE
  v_deptId   s_emp.dept_id%TYPE :=31;  
 
  CURSOR  cur_emp1 IS
    SELECT *
     FROM s_emp
    WHERE dept_id = v_deptId;
BEGIN
   FOR v_emp IN cur_emp1 LOOP       
     DBMS_OUTPUT.PUT_LINE(v_emp.id||' '||v_emp.first_name);   
   END LOOP;   
 
END;

FOR循环不需要做变量声明,不用OPEN,FETCH语句 for采用隐式游标.
FOR 会根据提供的查询语句,来确定v_emp的类型.*代表和s_emp表结构相同.
___________________________________________________________

带参数的游标
游标可以带多个参数cur_emp1(p_did s_emp.dept_id%TYPE,p_id NUMBER).
cur_emp1(p_did NUMBER)
p_did     如果只写成NUMBER(标量)时不要叫精度,和刻度NUMBER(4,2)
建议使用TYPE,ROWTYPE

DECLARE   
  v_emp s_emp%ROWTYPE;
  CURSOR  cur_emp1(p_did s_emp.dept_id%TYPE) IS
    SELECT *
     FROM s_emp
    WHERE dept_id = P_did;
BEGIN
   OPEN cur_emp1(31);
   LOOP
     FETCH cur_emp1 INTO v_emp;
     EXIT WHEN cur_emp1%NOTFOUND;
        
     DBMS_OUTPUT.PUT_LINE(v_emp.id||' '||v_emp.first_name);   
   END LOOP;   
   CLOSE cur_emp1;

 /* FOR v_emp IN cur_emp1(31) LOOP       
     DBMS_OUTPUT.PUT_LINE(v_emp.id||' '||v_emp.first_name);   
   END LOOP;  */
 
END;
________________________________________________________________

====================================================================
Handle Exception
oracle预定义异常
_________________________________________________
DECLARE
   v_emp s_emp%ROWTYPE;
BEGIN
   SELECT *
     INTO v_emp
     FROM s_emp
   WHERE id = 100;

  DBMS_OUTPUT.PUT_LINE('emp: '||v_emp.id);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
     DBMS_OUTPUT.PUT_LINE('emp : no data in emp');
END;
___________________________________________________

自定义异常
___________________________________________________________
DECLARE
   e_MyException EXCEPTION;

   v_emp s_emp%ROWTYPE;
BEGIN
   SELECT *
     INTO v_emp
     FROM s_emp
   WHERE id = 100;  --id=1|id=17
 
  IF v_emp.salary < 1000 THEN
    RAISE e_MyException;
  END IF;

  DBMS_OUTPUT.PUT_LINE('emp: '||v_emp.id||' '||v_emp.salary);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
     DBMS_OUTPUT.PUT_LINE('emp : no data in emp');

   WHEN e_MyException THEN
     DBMS_OUTPUT.PUT_LINE('emp : salary is too low');
     UPDATE s_emp SET salary = 1000
         WHERE id = v_emp.id;

   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('emp : other exception');
END;
//匿名块
__________________________________________________

Sub program
带名块
procedure,function,package,trigger
lhj_pro1.sql
不带参数的存储过程
_______________________________________
CREATE OR REPLACE PROCEDURE  pro_lhj1 AS
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello,world');
END;
________________________________________

lhj_pro11.sql
______________________________________
DECLARE
   v_name   s_emp.first_name%TYPE;
BEGIN
  --pro_lhj1;
  -- pro_lhj1(1);--in 模式的参数,从调用者的地方传值给存储过程
  pro_hello(1,v_name);

  DBMS_OUTPUT.PUT_LINE('hello, '||v_name);
END;
_____________________________________

===============================================================
带参数的存储过程(in模式)lhj_pro1.sql
______________________________________________________
CREATE OR REPLACE PROCEDURE  pro_lhj1(
      p_id s_emp.id%TYPE) AS
      v_emp s_emp%ROWTYPE;
BEGIN
   SELECT * INTO v_emp
     FROM s_emp
   WHERE id = p_id;

   DBMS_OUTPUT.PUT_LINE('hello, '||v_emp.first_name);
END;
_____________________________________________________

lhj_pro11.sql
______________________________________

BEGIN   
   pro_lhj1(1);--in 模式的参数,从调用者的地方传值给存储过程  
END;
_____________________________________
=================================================================
带参数的存储过程(out模式)lhj_pro1.sql
__________________________________________________________
CREATE OR REPLACE PROCEDURE  pro_lhj1(
      p_id s_emp.id%TYPE,
      p_name OUT s_emp.first_name%TYPE) AS
    v_emp s_emp%ROWTYPE;
BEGIN
   SELECT * INTO v_emp
     FROM s_emp
   WHERE id = p_id;
   
   p_name := v_emp.first_name;
   --DBMS_OUTPUT.PUT_LINE('hello, '||v_emp.first_name);
END;
___________________________________________________________

lhj_pro11.sql
______________________________________
DECLARE
   v_name   s_emp.first_name%TYPE;
BEGIN   
  pro_lhj1(1,v_name);
--pro_lhj1(p_name=>v_name,p_id=>1);   名字标示法
--pro_lhj1(1,p_name=>v_name);   两种方法混用时，
                                  第一个必须用位置标示法

  DBMS_OUTPUT.PUT_LINE('hello, '||v_name);
END;
_____________________________________

例：加法
lhj_pro2.sql
_____________________________________
CREATE OR REPLACE PROCEDURE pro_lhj2(
   p_a NUMBER,
   p_b NUMBER,
   p_ret OUT NUMBER) AS
   v_ret NUMBER;
BEGIN
   v_ret := p_a+p_b;
   p_ret := v_ret;
END;
______________________________________

lhj_pro22.sql
______________________________________
DECLARE
   v_name   s_emp.first_name%TYPE;
   v_ret    NUMBER;
BEGIN   
  pro_lhj1(1,v_name);
  DBMS_OUTPUT.PUT_LINE('hello, '||v_name);

  pro_lhj2(2,3,v_ret);
 
  DBMS_OUTPUT.PUT_LINE('2+3= '||v_ret);
END;
_____________________________________

================================================================
FUNCTION

lhj_fun1.sql
__________________________________________    
CREATE OR REPLACE FUNCTION fun_lhj1(
    p_a NUMBER,
    p_b NUMBER) RETURN NUMBER
IS
   v_ret NUMBER;
BEGIN
   v_ret :=p_a+p_b;
   RETURN v_ret;
END;
__________________________________________
lhj_fun11.sql
________________________________________
DECLARE
  v_ret NUMBER;
BEGIN
  v_ret := fun_add(2,3);
  DBMS_OUTPUT.PUT_LINE('2+3='||v_ret);
END;
________________________________________

=================================================================
Package
lhj_pack1.sql
________________________________________
CREATE OR REPLACE PACKAGE pack_lhj1 AS
   v_emp s_emp%ROWTYPE;

   PROCEDURE addEmp(p_emp s_emp%ROWTYPE);
END pack_lhj1;
________________________________________

lhj_pack2.sql
___________________________________________________
CREATE OR REPLACE PACKAGE BODY pack_lhj1 AS
    PROCEDURE addEmp(p_emp s_emp%ROWTYPE) AS
    BEGIN
     INSERT INTO s_emp(id,last_name,first_name,salary)
     VALUES(s_emp_lhjid.nextval,p_emp.last_name, --s_emp_lhjid序列名
            p_emp.first_name,p_emp.salary);
    
     COMMIT;
   END addEmp;
END pack_lhj1;
________________________________________________________

lhj_pack12.sql
______________________________________
BEGIN
   pack_lhj1.v_emp.last_name := 'wang';
   pack_lhj1.v_emp.first_name := 'wu';
   pack_lhj1.v_emp.salary := 2000;

   pack_lhj1.addEmp(pack_lhj1.v_emp);
END;
______________________________________

作业：
1：(存储过程)s_emp
已知员工id，找出员工的领导姓名
2：(函数)
写一个函数，给出地区id(s_region),
          找出此地区下工资最高的员工(s_emp)所在的部门名称(s_dept).
         
1.
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
lhj_test_pro1.sql
____________________________________________
CREATE OR REPLACE PROCEDURE pro_lhj_test1(
       v_id s_emp.id%TYPE,
       v_fname OUT s_emp.first_name%TYPE) AS
       
   p_fname s_emp.first_name%TYPE;
BEGIN
   SELECT b.first_name INTO p_fname
     FROM s_emp a,s_emp b
   where a.id = v_id and  a.manager_id=b.id;
   v_fname := p_fname;
END;
___________________________________________

lhj_test_pro11.sql
_____________________________________________________________
DECLARE
   t_name s_emp.first_name%TYPE;
BEGIN
   pro_lhj_test1(2,v_fname=>t_name);
   DBMS_OUTPUT.PUT_LINE(t_name||' is id=2 employees manager');
END;
_____________________________________________________________
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
2.
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
lhj_test_pro2.sql
______________________________________________________
CREATE OR REPLACE FUNCTION fun_lhj_test1(
      r_id s_region.id%TYPE) RETURN s_dept.name%TYPE
IS
   f_name s_dept.name%TYPE;
BEGIN
   select name
   into f_name
   from s_dept where id=(
   select dept_id from s_emp where salary=(
   select max(salary) from s_emp where dept_id in(
   select id from s_dept where region_id = r_id)));

   RETURN f_name;
END;
_______________________________________________________
lhj_test_pro22.sql
___________________________________________________
DECLARE
   v_name s_dept.name%TYPE;
BEGIN
   v_name := fun_lhj_test1(1);

   DBMS_OUTPUT.PUT_LINE(v_name);   
END;
_____________________________________________________  
      





* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *