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));