PLSQL函数参数.doc

游标for循环-查询所有员工编号，姓名，工资declarecursor emp_cursor isselect * from employees;begin for e IN emp_cursor loop dbms_output.put_line(编号：|e.employee_id|姓名：|e.last_name|工资：|e.salary); end loop;end;-查询所有员工编号，姓名，工资(参数)declarecursor emp_cursor(p_dept_id employees.department_id%type)isselect * from employeeswhere department_id=p_dept_id;begin for e in emp_cursor(90) loop dbms_output.put_line(编号：|e.employee_id|姓名：|e.last_name|工资：|e.salary); end loop;end;declarebegin for e in(select * from employees where department_id=60) loop dbms_output.put_line(编号：| e.employee_id |姓名|e.last_name|工资|e.salary); end loop;end;-隐式游标-用户输入一个任意的部门编号，更新这个部门员工的工资 如果有员工的工资被更新，输出更新成功，有多少个员工被更新，如果没有，输出部门不存在v_deptid number:=&input;begin update employees set salary = salary+1 where department_id=v_deptid; if sql%found then dbms_output.put_line(更新成功，有 | SQL%ROWCOUNT |个员工被更新); else dbms_output.put_line(部门不存在); end if; commit;end;-动态游标-用户输入一个字母，输入E，查的是员工姓名，输入D，查的是部门名称select count(employee_id) from employees函数和过程ORACLE 提供可以把PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。 创建函数创建函数的语法如下：CREATE OR REPLACE FUNCTION function_name (argment IN | OUT | IN OUT Type ， argment IN | OUT | IN OUT Type RETURN return_type IS | AS BEGINFUNCTION_bodyEXCEPTION其它语句END; CREATE OR REPLACE FUNCTION get_salary(Dept_no NUMBER, Emp_count OUT NUMBER)RETURN NUMBER ISV_sum NUMBER;BEGINSELECT SUM(sal), count(*) INTO V_sum, emp_countFROM 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; 调用函数方法函数声明时所定义的参数称为形式参数，应用程序调用时为函数传递的参数称为实际参数。应用程序在调用函数时，可以使用以下三种方法向函数传递参数： 第一种参数传递格式称为位置表示法，格式为： argument_value1,argument_value2 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; 第二种参数传递格式称为名称表示法，格式为 ：argument = parameter , 其中：argument 为形式参数，它必须与函数定义时所声明的形式参数名称相同。Parameter 为实际参数。在这种格式中，形势参数与实际参数成对出现，相互间关系唯一确定，所以参数的顺序可以任意排列。 DECLAREV_num NUMBER;V_sum NUMBER;BEGINV_sum :=get_salary(emp_count = v_num, dept_no = 30);DBMS_OUTPUT.PUT_LINE(30号部门工资总和：|v_sum|，人数：|v_num);END;第三种参数传递格式称为混合表示法 ：即在调用一个函数时，同时使用位置表示法和名称表示法为函数传递参数。采用这种参数传递方法时，使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面。也就是说，无论函数具有多少个参数，只要其中有一个参数使用名称表示法，其后所有的参数都必须使用名称表示法。DECLARE Var VARCHAR2(32);BEGINVar := 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 ISV_var VARCHAR2(32);BEGINV_var := name|：|TO_CHAR(age)|岁，|sex;RETURN v_var;END; 具有默认值的函数创建后，在函数调用时，如果没有为具有默认值的参数提供实际参数值，函数将使用该参数的默认值。但当调用者为默认参数提供实际参数时，函数将使用实际参数值。在创建函数时，只能为输入参数设置默认值，而不能为输入/输出参数设置默认值。 DECLARE Var VARCHAR(32);BEGINVar := 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; 存储过程在 ORACLE SERVER上建立存储过程,可以被多个应用程序调用,可以向存储过程传递参数,也可以向存储过程传回参数 创建存储过程语法 ：CREATE OR REPLACE PROCEDURE Procedure_name (argment IN | OUT | IN OUT Type, argment IN | OUT | IN OUT Type IS | AS BEGINEXCEPTIONEND; CREATE OR REPLACE PROCEDURE DelEmp(v_empno IN emp.empno%TYPE) ASNo_result EXCEPTION;BEGIN DELETE FROM emp WHERE empno=v_empno; IF SQL%NOTFOUND THEN RAISE no_result; END IF; DBMS_OUTPUT.PUT_LINE(编码为|v_empno|的员工已被除名!); EXCEPTION WHEN no_result THEN DBMS_OUTPUT.PUT_LINE(你需要的数据不存在!); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE|-|SQLERRM);END DelEmp;调用存储过程存储过程建立完成后，只要通过授权，用户就可以在SQLPLUS 、ORACLE开发工具或第三方开发工具中来调用运行。ORACLE 使用EXECUTE 语句来实现对存储过程的调用： EXECUTE Procedure_name( parameter1, parameter2)例： EXECUTE DelEmp； EXCEPTION WHEN no_result THEN DBMS_OUTPUT.PUT_LINE(你需要的数据不存在!); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE|-|SQLERRM);END DelEmp; CREATE OR REPLACE PROCEDURE proc_demo (Dept_no NUMBER DEFAULT 10 , Sal_sum OUT NUMBER, Emp_count OUT NUMBER) IS BEGIN SELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count FROM emp WHERE deptno=dept_no; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(你需要的数据不存在!); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE|-|SQLERRM);END proc_demo;V_num NUMBER;V_sum NUMBER(8, 2); BEGINProc_demo(30, v_sum, v_num);DBMS_OUTPUT.PUT_LINE(30号部门工资总和：|v_sum|，人数：|v_num);Proc_demo(sal_sum = v_sum, emp_count = v_num);DBMS_OUTPUT.PUT_LINE(10号部门工资总和：|v_sum|，人数：|v_num); END;删除过程和函数可以使用DROP语句删除过程：DROP PROCEDURE proceduer_name;可以使用DROP语句删除函数：DROP FUNCTION function_name;授权执行权给相关的用户或角色 如果调式正确的存储过程没有进行授权，那就只有建立者本人才可以运行。所以作为应用系统的一部分的存储过程也必须进行授权才能达到要求。可以用GRANT命令来进行存储过程的运行授权GRANT语法：GRANT system_privilege | role TO user | role | PUBLIC WITH ADMIN OPTIONGRANT 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 在SQL*PLUS 中，可以用DESCRIBE 命令查看过程的名字及其参数表DESCRIBE Procedure_name; 存储过程和函数区别函数是有返回值的，过程是没有返回值的（要返回值的话，借助参数去返回并且一般用于有多个返回值的时候使用）函数可以在查询语句中直接使用，过程必须单独调用创建函数获得当前时间输出字符串的形式create OR REPLACE FUNCTION get_date -传入参数return varchar2 -设置参数不写,参数和返回值类型不能写子参数的 number （number（8）错），不知道返回类型和长度画蛇添足asv_date varchar2(50); -不是参数和返回值类型可以写beginv_date:=to_char(sysdate,YYYY-MM-DD); -方法体return v_date; -返回值调用函数end;view查错 调用参数begin dbms_output.put_line(get_date);end;函数的参数创建一个get_result函数，传入两个数字，返回两个数字的和输入参数in，把实参赋值给形参（默认的）输出参数out，把形参赋值给实参输入输出参数 IN OUT 两个都可以互相赋值create or replace function get_result(p_numl in number,p_num2 in number)-传入参数 p_num1是形参 ，声明形参调用实参，in形参赋值给实参往里面赋值 return numberisv_result number(10);begin v_result:=p_numl+p_num2;return v_result; -过程无返回值，函数有end;测试函数begin dbms_output.put_line(get_result(10,20);-实参end;创建一个get_result2函数，传入两个数字，返回两个数字的和，差，积创建一个get_result1函数，传入两个数字，返回两个数字的和create or replace function get_result1(p_num1 number,p_num2 number)-声明形参调用实参return number设参数不写isv_result number(10);要赋值的对象begin v_result:=p_num1+p_num2; 方法体p_num1赋值给v_result return v_result;-返回值调用函数end;测试函数begindbms_output.put_line(get_result1(10,20);形参赋值给实参end;-创建一个get_result2函数，传入两个数字,返回两个数字的和,差，积，create or replace function get_result2(p_num1 number,p_num2 number,p_result3 OUT NUMBER,p_result4 OUT NUMBER)-输入参数out实参到形参，往里面赋值return numberisv_result number(10);begin v_result:=p_num1+p_num2; p_result3:=p_num1-p_num2; p_result4:=p_num1*p_num2; return v_result;end;测试declarev_result3 number(20)：=10;-p_result赋给v_resultv_result4 number(20)：=20;begin dbms_output.put_line(get_result2(10,20,v_result3,v_result4); dbms_output.put_line(v_result3); dbms_output.put_line(v_result4);end;输出30 -10 200-创建一个get_result3函数，传入两个数字,返回两个数字的和,差，积(输入输出参数)create or replace function get_result3(p_num1 in out number,p_num2 in out number)in out参数引进来 ，这是形参return numberasv_result number(10);-v扮演in较色，把实参赋值给形参v_temp1 number:=p_num1; -temp中间变量传递两个参数，引进来和接受参数,temp代替了参数resultv_temp2 number:=p_num2;begin v_result:=v_temp1+v_temp2;-IN p_num1:=v_temp1-v_temp2;-OUT p_num2:=v_temp1*p_num2;-p扮演out，把形参赋值给实参 return v_result;end;传递参数的形式declarev_result3 number:=10;v_result4 number:=20;begin dbms_output.put_line(get_result2(p_num2=3,p_num1=5,p_result3=v_result1,p_result4=v_result2); dbms_output.put_line(v_result1); dbms_output.put_line(v_result2);end;3赋值给了p_num2， p_result3接受了v_result1输出 8 2 15传参不默认，给值default参数传递测试-创建一个get_result3函数，传入两个数字,返回两个数字的和,差，积(输入输出参数)create or replace function get_result3(p_num1 in out 10number,p_num2 in out number) 20 return numberasv_result number(10);v_temp1 number:=p_num1; 10 -和下面的declare相对应的位置v_temp2 number:=p_num2; 20 -temp没有resultbegin v_result:=v_temp1+v_temp2;-IN 30 p_num1:=v_temp1-v_temp2; -OUT -10 p_num2:=v_temp1*v_temp2;-OUT 300 return v_result;end;测试declarev_result3 number(20):=10;-定义变量v_result4 number(20):=20;begin dbms_output.put_line(get_result3(v_result3,v_result4);-result3 上面定义的是3 10 20