/*
pl/sql存储函数和存储过程
过程和函数的唯一区别是函数总向调用者返回数据,而过程则不返回数据
存储函数:有返回值,创建完成后,通过select function() from dual;执行
存储过程:由于没有返回值,创建完成后,不能使用select语句,只能使用pl/sql块执行
*/
/*
创建存储函数
语法格式如下:
CREATE OR REPLACE FUNCTION function_name(dept_id NUMBER, salary NUMBER)
RETURN NUMBER
IS --可以把is关键字的作用理解成前面学过的DECLARE关键字的作用,DECLARE里面可以声明变量、记录类型、游标,is里面也是声明这些的
--函数使用过程中,需要声明的变量、记录类型、CURSOR游标(类似于前面的declare的部分)
BEGIN
--函数的执行体
EXCEPTION
--处理函数执行过程中的异常
END;
*/
--写个最简单的存储函数,如下:
--函数的 helloworld: 返回一个 "helloworld" 的字符串
CREATE OR REPLACE FUNCTION helloWorld --不带参数
RETURN VARCHAR2 --返回值的类型
IS
BEGIN
dbms_output.put_line('我是存储函数helloWorld');
RETURN 'helloworld'; --返回值
END;
--调用存储函数
--第1种调用方式
SELECT helloWorld FROM dual;
--第2种调用方式
BEGIN
dbms_output.put_line(helloWorld);
END;
--
CREATE OR REPLACE FUNCTION helloWorld2(v_title VARCHAR2) --带参数
RETURN VARCHAR2 --返回值的类型
IS
BEGIN
dbms_output.put_line('我是存储函数helloWorld2');
RETURN 'helloworld ' || v_title; --返回值
END;
--调用存储函数
--第1种调用方式
SELECT helloWorld2('江西省赣州市于都县') FROM dual;
--第2种调用方式
BEGIN
dbms_output.put_line(helloWorld2('江西省赣州市于都县'));
END;
--创建一个存储函数,返回当前的系统时间
CREATE OR REPLACE FUNCTION getNow
RETURN DATE
IS
--定义变量
v_now DATE := SYSDATE;
BEGIN
dbms_output.put_line('我是存储函数getNow');
--v_now := SYSDATE;
SELECT SYSDATE INTO v_now FROM dual;--其实这句话可以不写
RETURN v_now;
END;
--
--调用存储函数
SELECT getNow FROM dual;
--调用存储函数
SELECT getNow() FROM dual;
--调用存储函数
BEGIN
dbms_output.put_line(getNow);
END;
--调用存储函数
BEGIN
dbms_output.put_line(getNow());
END;
--
--定义带参数的函数: 两个数相加
CREATE OR REPLACE FUNCTION add_fun(num1 NUMBER, num2 NUMBER)
RETURN NUMBER
IS
v_result NUMBER := 0.00;
BEGIN
v_result := num1 + num2;
RETURN v_result;
END;
--调用存储函数add_fun
SELECT add_fun(66.66, 22.22) FROM dual;
--
CREATE OR REPLACE FUNCTION ADD_FUN2(a NUMBER, b NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN(a + b);
END;
--调用存储函数add_fun2
SELECT ADD_FUN2(66.66, 22.22) FROM dual;
--调用存储函数add_fun2
BEGIN
dbms_output.put_line(add_fun2(6, 8));
END;
--定义一个函数: 获取给定部门的工资总和, 要求:部门号定义为参数, 工资总额定义为返回值.
--方式1
CREATE OR REPLACE FUNCTION getSumSalaryByDepid(department_id NUMBER)
RETURN NUMBER
IS
v_Dep_SumSalary NUMBER(20, 3) := 0.000;
v_depId NUMBER := department_id;
BEGIN
SELECT SUM(salary) INTO v_Dep_SumSalary FROM employees WHERE department_id = v_depId;
RETURN v_Dep_SumSalary;
END;
--调用存储函数getSumSalaryByDepid
SELECT getSumSalaryByDepid(80) FROM dual;
--查询80号部门中的所有员工的工资总和
SELECT SUM(salary) FROM employees WHERE department_id = 80;
--方式2(使用游标)
CREATE OR REPLACE FUNCTION getSumSalaryByDepid2(department_id NUMBER)
RETURN NUMBER
IS
v_Dep_SumSalary NUMBER(20, 3) := 0.000;
v_depId NUMBER := department_id;
--定义游标
CURSOR sal_cursor IS SELECT salary FROM employees WHERE department_id = v_depId;
BEGIN
FOR row_obj IN sal_cursor LOOP
v_Dep_SumSalary := v_Dep_SumSalary + row_obj.salary;
END LOOP;
RETURN v_Dep_SumSalary;
END;
--调用存储函数getSumSalaryByDepid2
SELECT getSumSalaryByDepid2(80) FROM dual;
--查询80号部门中的所有员工的工资总和
SELECT SUM(salary) FROM employees WHERE department_id = 80;
--调用存储函数getSumSalaryByDepid2
DECLARE
v_depid number(10, 2) := 80;
BEGIN
dbms_output.put_line(getsumsalarybydepid2(v_depid));
END;
--
--关于 OUT 型的参数: 因为函数只能有一个返回值, PL/SQL 程序可以通过 OUT 型的参数实现有多个返回值
--要求: 定义一个函数: 获取给定部门的工资总和 和 该部门的员工总数(定义为 OUT 类型的参数).
--要求: 部门号定义为参数, 工资总额定义为返回值
--方式1
CREATE OR REPLACE FUNCTION getSumSalaryByDepid3(department_id NUMBER, employeeCount OUT NUMBER)
RETURN NUMBER
IS
v_Dep_SumSalary NUMBER(20, 3) := 0.00;
v_depId NUMBER := department_id;
BEGIN
SELECT SUM(salary) INTO v_Dep_SumSalary FROM employees WHERE department_id = v_depId;
SELECT COUNT(*) INTO employeeCount FROM employees WHERE department_id = v_depId;
RETURN v_Dep_SumSalary;
END;
--查询80号部门中的所有员工的工资总和
SELECT SUM(salary) FROM employees WHERE department_id = 80;
--查询80号部门中的员工人数
SELECT COUNT(*) FROM employees WHERE department_id = 80;
--调用存储函数getSumSalaryByDepid3
DECLARE
v_depid number(6) := 80;
v_emp_count number(10) := 0;
BEGIN
dbms_output.put_line(getSumSalaryByDepid3(v_depid, v_emp_count));
dbms_output.put_line(v_depid || '号部门员工总人数为' || v_emp_count);
END;
----方式2
CREATE OR REPLACE FUNCTION getSumSalaryByDepid4(department_id NUMBER, employeeCount OUT NUMBER)
RETURN NUMBER
IS
v_Dep_SumSalary NUMBER(20, 3) := 0.000;
v_depId NUMBER := department_id;
--定义游标
CURSOR sal_cursor IS SELECT salary FROM employees WHERE department_id = v_depId;
BEGIN
employeeCount := 0;
FOR row_obj IN sal_cursor LOOP
v_Dep_SumSalary := v_Dep_SumSalary + row_obj.salary;
employeeCount := employeeCount + 1;
END LOOP;
RETURN v_Dep_SumSalary;
END;
--查询80号部门中的所有员工的工资总和
SELECT SUM(salary) FROM employees WHERE department_id = 80;
--查询80号部门中的员工人数
SELECT COUNT(*) FROM employees WHERE department_id = 80;
--调用存储函数getSumSalaryByDepid4
DECLARE
v_depid number(6) := 80;
v_emp_count NUMBER;
BEGIN
dbms_output.put_line(getSumSalaryByDepid4(v_depid, v_emp_count));
dbms_output.put_line(v_depid || '号部门员工总人数为' || v_emp_count);
END;
--
--定义一个存储过程: 获取给定部门的工资总和(通过 out 参数), 要求:部门号和工资总额定义为参数
--注意:存储过程没有返回值,存储过程使用PROCEDURE关键字,而不是使用FUNCTION关键字
CREATE OR REPLACE PROCEDURE getSumSalaryByDepid5(department_id NUMBER, dep_SumSalary OUT NUMBER)
IS
v_depId NUMBER := department_id;
--定义游标
CURSOR sal_cursor IS SELECT salary FROM employees WHERE department_id = v_depId;
BEGIN
dbms_output.put_line('-------------------------我是存储过程getSumSalaryByDepid5-------------------------');
dep_SumSalary := 0;
FOR row_obj IN sal_cursor LOOP
dep_SumSalary := dep_SumSalary + row_obj.salary;
END LOOP;
END;
--调用存储函数getSumSalaryByDepid5
DECLARE
v_depid number(6) := 80;
v_Dep_SumSalary NUMBER := 0;
BEGIN
getSumSalaryByDepid5(v_depid, v_Dep_SumSalary);
dbms_output.put_line(v_depid || '号部门的员工薪水总和为' || v_Dep_SumSalary);
END;
--command窗口中可以输入show errors; 查看错误信息
/*
自定义一个存储过程完成以下操作:
对给定部门(作为输入参数)的员工进行加薪操作, 若其到公司的时间在 (? , 95) 期间, 为其加薪 %5
[95 , 98) %3
[98, ?) %1
得到以下返回结果: 为此次加薪公司每月需要额外付出多少成本(定义一个 OUT 型的输出参数).
*/
CREATE OR REPLACE PROCEDURE salary_increase(depId NUMBER ,salary_cost OUT NUMBER)
IS
--定义游标
CURSOR sal_cursor IS SELECT employee_id, hire_date, salary FROM employees WHERE department_id = depId;
--涨薪幅度
v_percent NUMBER(10, 2) := 0;
BEGIN
dbms_output.put_line('----------------------我是存储过程salary_increase----------------------');
--每月需要额外付出的成本
salary_cost := 0;
FOR row_obj IN sal_cursor LOOP
IF to_char(row_obj.hire_date, 'yyyy') < 1995 THEN v_percent := 0.05; --1995不加单引号也可以(字符和数字会自动/隐式转换)
ELSIF to_char(row_obj.hire_date, 'yyyy') < '1998' THEN v_percent := 0.03; --1998加上单引号也可以(字符和数字会自动/隐式转换)
ELSE v_percent := 0.01;
END IF;
--1.更新薪水
UPDATE employees SET salary = salary * (1 + v_percent) WHERE employee_id = row_obj.employee_id;
--2.计算额外付出的成本
salary_cost := salary_cost + row_obj.salary * v_percent;
END LOOP;
END;
--调用存储过程salary_increase
DECLARE
v_deptId NUMBER := 80;
v_salary_cost NUMBER := 0;
BEGIN
salary_increase(v_deptId, v_salary_cost);
dbms_output.put_line(v_deptId || '号部门,每月需要额外付出的成本为' || v_salary_cost);
END;
--存储函数返回一个对象、或者返回一个记录类型、或者返回一个游标、或者返回一个集合?
--存储函数的返回值的类型可以是记录类型吗? 返回值的类型可以是对象?可以是游标?可以是集合?
/*
--如下代码,我是按照我自己猜测的语法来写的,估计是写的不正确,我暂时还没上网查资料,所以暂时先放一放,等有时间再去查一下资料
CREATE OR REPLACE FUNCTION test_function
RETURN RECORD --返回值的类型(返回记录类型),有时间可以上网查一下,返回集合或者返回对象或者返回游标等应该怎么写!
IS
--声明一个记录类型
TYPE emp_record IS RECORD(
v_sal employees.salary%TYPE := 6688.99, -- 动态获取employees表中的salary列的数据类型
v_email employees.email%TYPE := 'test@163.com',
v_hire_date employees.hire_date%TYPE := SYSDATE
);
--定义一个记录类型的成员变量
v_emp_record emp_record;
BEGIN
dbms_output.put_line('我是存储函数test_function');
RETURN v_emp_record; --返回值
END;
--调用存储函数test_function
BEGIN
dbms_output.put_line(test_function());
END;
*/
/*
小总结
[存储函数:有返回值,创建完成后,通过select function() from dual;执行]
[存储过程:由于没有返回值,创建完成后,不能使用select语句,只能使用pl/sql块执行]
[格式]
--函数的声明(有参数的写在小括号里)
create or replace function func_name(v_param varchar2)
--返回值类型
return varchar2
is
--PL/SQL块变量、记录类型、游标的声明(类似于前面的declare的部分)
begin
--函数体(可以实现增删改查等操作,返回值需要return)
return 'helloworld'|| v_logo;
end;
*/