oracle函数创建及调用

 

创建函数的语法如下：

CREATE [OR REPLACE] FUNCTION function_name
[ (argment [ { IN | OUT | IN OUT } ] Type ，
argment [ { IN | OUT | IN OUT } ] Type ]
RETURN return_type
{ IS | AS }
<类型.变量的说明>
BEGIN
FUNCTION_body
EXCEPTION
其它语句
END;

例：

CREATE OR REPLACE FUNCTION get_salary(
　　Dept_no NUMBER, Emp_count OUT NUMBER)
　　RETURN NUMBER 
　　IS
　　V_sum NUMBER;
BEGIN
　　SELECT SUM(sal), count(*) INTO V_sum, emp_count
　　FROM emp WHERE deptno=dept_no;
　　RETURN v_sum;
　　EXCEPTION
　　　　WHEN NO_DATA_FOUND THEN 
　　　　DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
　　　　WHEN OTHERS THEN 
　　　　DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END get_salary;

 

调用函数方法
函数声明时所定义的参数称为形式参数，应用程序调用时为函数传递的参数称为实际参数。应用程序在调用函数时，可以使用以下三种方法向函数传递参数：

第一种参数传递格式称为位置表示法，格式为：
例：

DECLARE
　　V_num NUMBER;
　　V_sum NUMBER;
BEGIN
　　V_sum :=get_salary(30, v_num);
　　DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||'，人数：'||v_num);
END;

 

第二种参数传递格式称为名称表示法，格式为 ：
例：

DECLARE
　　V_num NUMBER;
　　V_sum NUMBER;
BEGIN
　　V_sum :=get_salary(emp_count => v_num, dept_no => 30);
　　DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||'，人数：'||v_num);
END;

 

第三种参数传递格式称为混合表示法 ：
例：

DECLARE 
　　Var VARCHAR2(32);
BEGIN
　　Var := demo_fun('user1', 30, sex => '男');
　　DBMS_OUTPUT.PUT_LINE(var);
　　Var := demo_fun('user2', age => 40, sex => '男');
　　DBMS_OUTPUT.PUT_LINE(var);
　　Var := demo_fun('user3', sex => '女', age => 20);
　　DBMS_OUTPUT.PUT_LINE(var);
END;

 

参数默认值
在CREATE OR REPLACE FUNCTION 语句中声明函数参数时可以使用DEFAULT关键字为输入参数指定默认值。
例：

CREATE OR REPLACE FUNCTION demo_fun(
　　Name VARCHAR2,vAge INTEGER,
　　Sex VARCHAR2 DEFAULT '男')
　　RETURN VARCHAR2 
IS
　　V_var VARCHAR2(32);
BEGIN
　　V_var := name||'：'||TO_CHAR(age)||'岁，'||sex;
　　RETURN v_var;
END; 

 

具有默认值的函数创建后，在函数调用时，如果没有为具有默认值的参数提供实际参数值，函数将使用该参数的默认值。
但当调用者为默认参数提供实际参数时，函数将使用实际参数值。
在创建函数时，只能为输入参数设置默认值，而不能为输入/输出参数设置默认值。
例：

DECLARE 
　　Var VARCHAR(32);
BEGIN
　　Var := demo_fun('user1', 30);
　　DBMS_OUTPUT.PUT_LINE(var);
　　Var := demo_fun('user2', age => 40);
　　DBMS_OUTPUT.PUT_LINE(var);
　　Var := demo_fun('user3', sex => '女', age => 20);
　　DBMS_OUTPUT.PUT_LINE(var);
END;

 

 

 
可以使用DROP语句删除函数：
DROP FUNCTION function_name;

 

授权执行权给相关的用户或角色
GRANT语法：
GRANT system_privilege | role
TO user | role | PUBLIC [WITH ADMIN OPTION]

 

GRANT object_privilege | ALL ON schema.object
TO user | role | PUBLIC [WITH GRANT OPTION]
例：

GRANT EXECUTE ON dbms_job TO PUBLIC WITH GRANT OPTION

 

与过程相关的权限：
CREATE ANY PROCEDURE
DROP ANY PROCEDURE

与过程相关数据字典
USER_SOURCE ，USER_PROCEDURES ，USER_ERRORS