2006年01月18日

create or replace function get_sal(v_id in emp.empno%type) return number is
  v_sal emp.sal%type := 0;
begin
  select sal into v_sal from emp where empno = v_id;
  return v_sal;
end;
/
调用函数
1.
SQL> select get_sal(7934) from dual;
2.
variable a_sal number;
execute :a_sal:=get_sal(7934);
print a_sal;

create or replace function get_sal_sum(v_deptno in emp.deptno%type,
                                       v_job    in emp.job%type)
  return number is
  v_sal emp.sal%type := 0;
begin
  select sum(sal)
    into v_sal
    from emp
   where deptno = v_deptno
     and job = upper(v_job);
  return v_sal;
end;
/
select get_sal_sum(20,'CLERK') from dual;

--求长方形面积
create or replace function f_get_area(a number, b number) return number as
  v_area number;
begin
  v_area := a * b;
  return v_area;
end;
/
select f_get_area(4,5) from dual;

create or replace function f_get_total_sal(v_empno number) return number as
  v_sal       number;
  v_comm      number;
  v_total_sal number;
begin
  select sal, comm into v_sal, v_comm from emp where empno = v_empno;
  v_total_sal := v_sal * 12 + nvl(v_comm, 0);
  return v_total_sal;
end;
/
select f_get_total_sal(7934) from dual;

 

 

CREATE OR REPLACE PACKAGE comm_package IS
  g_comm NUMBER := 10; --initialized to 10
  PROCEDURE reset_comm(v_comm IN NUMBER);
  procedure Set_comm(v_empno in emp.empno%type);
END comm_package;
/

CREATE OR REPLACE PACKAGE BODY comm_package IS
FUNCTION validate_comm(v_comm IN NUMBER) RETURN BOOLEAN IS
  v_max_comm NUMBER;
BEGIN
  SELECT MAX(comm) INTO v_max_comm FROM emp;
  IF v_comm > v_max_comm THEN
    RETURN(FALSE);
  ELSE
    RETURN(TRUE);
  END IF;
END validate_comm;

PROCEDURE reset_comm(v_comm IN NUMBER) IS
  v_valid BOOLEAN;
BEGIN
  v_valid := validate_comm(v_comm);
  IF v_valid = TRUE THEN
    g_comm := v_comm;
  ELSE
    RAISE_APPLICATION_ERROR(-20210, '奖金额太大了');
  END IF;
END reset_comm;

procedure Set_comm(v_empno in emp.empno%type) is
begin
  update emp_t set comm = sal * 0.5 where empno = v_empno;
  if SQL%rowcount = 0 then
    raise_application_error(-20003, '该员工不存在！');
    --dbms_output.put_line('该员工不存在.');
  else
    dbms_output.put_line('记录已更新！');
  end if;

end Set_comm;
END comm_package;
/

exec comm_package.reset_comm(500);
exec dbms_output.put_line(comm_package.g_comm);

--游标
CREATE OR REPLACE PACKAGE pack_cur IS
  CURSOR c1 IS
    SELECT empno FROM emp ORDER BY empno desc;
  PROCEDURE proc1_3rows;
  PROCEDURE proc4_6rows;
END pack_cur;
/

CREATE OR REPLACE PACKAGE BODY pack_cur IS
  v_empno NUMBER;
  PROCEDURE proc1_3rows IS
  BEGIN
    OPEN c1;--第一行开始
    LOOP
      FETCH c1
        INTO v_empno;
      DBMS_OUTPUT.PUT_LINE('Id :' || (v_empno));
      EXIT WHEN c1%ROWCOUNT >= 3;
    END LOOP;
  END proc1_3rows;
 
  PROCEDURE proc4_6rows IS
  BEGIN
    LOOP
      FETCH c1--游标不关闭，则游标并不停在第一行
        INTO v_empno;
      DBMS_OUTPUT.PUT_LINE('Id :' || (v_empno));
      EXIT WHEN c1%ROWCOUNT >= 6;
    END LOOP;
    CLOSE c1;
  END proc4_6rows;
 
END pack_cur;
/

exec pack_cur.proc1_3rows;
exec pack_cur.proc4_6rows;

--返回值是表类型
CREATE OR REPLACE PACKAGE emp_package IS
  TYPE emp_table_type IS TABLE OF emp%ROWTYPE
     INDEX BY BINARY_INTEGER;
  PROCEDURE read_emp_table(emp_table OUT
                           emp_table_type);
END emp_package;

CREATE OR REPLACE PACKAGE BODY emp_package IS
  PROCEDURE read_emp_table(emp_table OUT
                           emp_table_type)
  IS
     i BINARY_INTEGER:=0;
  BEGIN
    FOR emp_record IN (SELECT * FROM emp) LOOP
     emp_table(i):=emp_record;
     I:=I+1;
    END LOOP;
  END;
END emp_package;
/

CREATE OR REPLACE PACKAGE emp_package IS
  TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
  PROCEDURE read_emp_table(emp_table OUT emp_table_type);
END emp_package;
/
CREATE OR REPLACE PACKAGE BODY emp_package IS
  PROCEDURE read_emp_table(emp_table OUT emp_table_type) IS
    i BINARY_INTEGER := 0;
  BEGIN
    FOR emp_record IN (SELECT * FROM emp) LOOP
      emp_table(i) := emp_record;
      I := I + 1;
    END LOOP;
  END;
END emp_package;
/
--调用出错了
variable tmpt emp_package.emp_table_type;
exec :emp_package.read_emp_table(tmpt);
dbms_output.put_line(tmpt(1));