oracle存储过程函数和程序包

Oracle创建存储过程、创建函数、创建包⼀、Oracle创建存储过程1、基本语法create or replace procedure update_emp_sal(Name in out type,Name in out type, ...) isbeginend update_emp_sal;2、写⼀个简单的例⼦修改emp表的ename字段create or replace procedure update_emp(v_empno varchar2,v_ename varchar2) isbeginupdate emp set ename=v_ename where empno=v_empno;end update_emp;调⽤⽅法如下：SQL>exec update_emp('7935','test');2、有返回值的存储过程就写⼀个简单的返回empno=7935的sal值create or replace procedure emp_out_sal(v_empno in varchar2,v_sal out number) isvsal number(7,2);beginselect sal into vsal from emp where empno=v_empno;v_sal:=vsal;end;调⽤有返回值的过程SQL>var vsal numberSQL>exec emp_out_sal('7935',:vsal);PL/SQL procedure successfully completedvsal---------700SQL>var vsal numberSQL> call emp_out_sal('7935',:vsal);Method calledvsal---------700⼆、Oracle创建函数（function）1、基本语法规则如下：create or replace function (Name in type, Name in type, ...) return number isResult number;beginreturn (Result);end ;2、写⼀个简单的查询例⼦查询出empno=7935的sal值create or replace function ret_emp_sal(v_ename varchar2)return numberisv_sal number(7,2);beginselect nvl(sal,0) into v_sal from emp where lower(ename)=lower(v_ename);return v_sal;end;调⽤此函数：SQL>var vsla numberSQL> call ret_emp_sal('7935') into :vsal;Method calledvsal---------700三、Oracle创建包包⽤于组合逻辑相关的过程和函数，它由包规范和包体两个部分组成。

Oracle数据库的函数，存储过程，程序包，游标，触发器Oracle⾃定义函数函数的主要特性是它必须返回⼀个值。

创建函数时通过 RETURN ⼦句指定函数返回值的数据类型。

函数的⼀些限制：●函数只能带有 IN 参数，不能带有 IN OUT 或 OUT 参数。

●形式参数必须只使⽤数据库类型，不能使⽤ PL/SQL 类型。

●函数的返回类型必须是数据库类型Create function 函数名称 return 返回值类型 asBegin····End 函数名称;--创建不带参数函数，返回t_book中书的数量create function getBookCount return number asbegindeclare book_count number;beginselect count(*) into book_count from t_book;return book_count;end;end getBookCount;--函数调⽤set serveroutput on;begindbms_output.put_line('表t_book中有'||getBookCount()||'本书');end;--创建带参数函数，查找某个表的记录数create function getTableCount(table_name varchar2) return number asbegindeclare recore_count number;query_sql varchar2(300);--定义sql语句beginquery_sql:='select count(*) from '||table_name;--execute immediate：⽴即执⾏该SQL语句execute immediate query_sql into recore_count;return recore_count;end;end getTableCount;--函数调⽤set serveroutput on;begindbms_output.put_line('表中有'||getTableCount('t_book_log')||'条数据');end;CREATE OR REPLACE FUNCTION item_price_rage (price NUMBER)/* 参数、指定返回类型 */RETURN varchar2AS/* 定义局部变量 */min_price NUMBER;max_price NUMBER;BEGINSELECT MAX(ITEMRATE), MIN(ITEMRATE) INTO max_price, min_priceFROM itemfile;IF price >= min_price AND price <= max_price THENRETURN '输⼊的单价介于最低价与最⾼价之间';ELSERETURN '超出范围';END IF;END;匿名块执⾏函数p NUMBER := 300;MSG varchar2(200);BEGINMSG := item_price_range(p);DBMS_OUTPUT.PUT_LINE(MSG);END;SELECT查询调⽤（因为函数必须有返回值）SELECT myfunction FROM dual;Oracle存储过程存储过程(Stored Procedure)，就是⼀组⽤于完成特定功能的SQL语句集，该SQL语句集经过编译后存储在数据库中。

Oracle中使用存储过程和函数摘要：存储过程是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数来执行它。

存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

函数也是存储程序的一种，它建立后存储在数据库服务器中，用户可以直接调用。

熟练地使用存储过程能帮助DBA更好地管理数据库。

关键字：存储过程函数一．为什么要使用存储过程呢？因为课程设计中需要用到存储过程和函数，所以就选择了这个题目，好让自己对这个知识点更加熟悉。

在SQL*Plus中编写并执行PL/SQL块，PL/SQL块的代码存放在SQL*Plus的缓冲区中。

如果在SQL*Plus中执行其它SQL语句或者PL/SQL块，SQL*Plus的缓冲区中就会存放新的内容，原来的内容会被从缓冲区中清除出去。

如果希望PL/SQL块能被随时调用执行，并能与数据库中的其它用户共享，那就需要创建有名字的PL/SQL块，并经过编译与优化，存放在数据库中，这就是存储程序。

具体的优点如下：1.存储过程的能力大大增强了SQL语言的功能和灵活性。

存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

2.可保证数据的安全性和完整性。

3.通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。

4.通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。

5.再运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。

这种已经编译好的过程可极大地改善SQL语句的性能。

由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。

6.可以降低网络的通信量。

7.体现企业规则的运算程序放入数据库服务器中，以便集中控制。

8.当企业规则发生变化时在服务器中改变存储过程即可，无须修改任何应用程序。

企业规则的特点是要经常变化，如果把体现企业规则的运算程序放入应用程序中，则当企业规则发生变化时，就需要修改应用程序工作量非常之大（修改、发行和安装应用程序）。

Oracle （存储过程、存储函数、⽤程序调⽤）指存储在数据库中的供所有⽤户程序带哦⽤的⼦程序（PL/SQL ）叫存储过程（不能返回值）、存储函数（可以通过return 语句返回值）1、存储过程为了完成特定功能的SQL 语句集，经编译后存储在数据库中。

（1）新建：（2）书写存储过程的代码：（3）编译运⾏代码：（4）调⽤存储过程：2、存储函数create or replace procedure raiseSalary(eno in number)ispsal emp.sal %type;beginselect sal into psal from emp where empno=eno;update emp set sal= sal + 100 where empno = eno ;dbms_output.put_line('前:'||psal||'后:'||(psal+100));end raiseSalary;存储函数与存储过程的结构类似，但是必须有⼀个return ⼦句，⽤于返回函数值。

（1）创建⼀个存储函数：（2）书写代码：（3）右键选择test：3、存储过程和存储函数的OUT（1）创建存储过程：（2）书写程序：查询员⼯的信息（3）测试结果：create or replace function queryEmpIncome(eno in number)return numberispsal emp.sal %type;pcomm m %type;beginselect sal,comm into psal,pcomm from emp where empno=eno;return psal*12+nvl(pcomm,0);end queryEmpIncome;create or replace procedure queryEmpInfeno(eno in number,pename out varchar2,psal out number,pjob out varchar2)isbeginselect ename,sal,job into pename,psal,pjob from emp where empno=eno; end queryEmpInfeno;4、java程序调⽤存储过程和存储函数（1）先在虚拟机中找到需要导⼊的jar包并进⾏导⼊：（2）书写⼀个⼯具类：package pers.zhb.utils;import java.sql.*;public class JDBCUtils {private static String driver = "oracle.jdbc.OracleDriver";private static String url = "jdbc:oracle:thin:@192.168.125.129:1521/orcl"; private static String user = "scott";private static String password = "tiger";static{try {Class.forName(driver);} catch (ClassNotFoundException e) {throw new ExceptionInInitializerError(e);}}public static Connection getConnection(){try {return DriverManager.getConnection(url, user, password);} catch (SQLException e) {e.printStackTrace();}return null;}public static void release(Connection conn, Statement st, ResultSet rs){ if(rs != null){try {rs.close();} catch (SQLException e) {e.printStackTrace();}finally{rs = null;}}if(st != null){try {st.close();} catch (SQLException e) {e.printStackTrace();}finally{st = null;}}if(conn != null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}finally{conn = null;}}}}（3）创建测试类，调⽤存储过程和存储函数：public class Test {public void testProcedure(){String sql = "{call raiseSalary(?,?,?,?)}";Connection conn = null;CallableStatement call = null;try {conn = JDBCUtils.getConnection();call = conn.prepareCall(sql);call.setInt(1,7839);call.registerOutParameter(2, OracleTypes.VARCHAR); call.registerOutParameter(3, OracleTypes.NUMBER); call.registerOutParameter(4, OracleTypes.VARCHAR); call.execute();String name = call.getString(2);double sal = call.getDouble(3);String job = call.getString(4);System.out.println(name+"\t"+sal+"\t"+job);} catch (Exception e) {e.printStackTrace();}finally{JDBCUtils.release(conn, call, null);}}public void testFunction(){String sql = "{?=call queryEmpIncome(?)}";Connection conn = null;CallableStatement call = null;try {conn = JDBCUtils.getConnection();call = conn.prepareCall(sql);call.registerOutParameter(1, OracleTypes.NUMBER); call.setInt(2, 7839);call.execute();double income = call.getDouble(1);System.out.println(income);} catch (Exception e) {e.printStackTrace();}finally{JDBCUtils.release(conn, call, null);}}public static void main(String [] args){Test test=new Test();test.testFunction();}}。

Oracle中的函数、存储过程、包1.函数①系统函数②自定义函数③使用函数④编写过程⑤过程的调用例子：一个数的两倍CREATE OR REPLACE FUNCTION f_get_double(p1 NUMBER)RETURN NUMBERISv1 NUMBER;BEGINv1:=p1*2;RETURN v1;END;调用：SELECT scott.f_get_double(100) FROM dual;SELECT ename,sal,f_get_double(sal) AS sal2 FROM emp;2.存储过程例子：删除指定编号的员工信息，如果员工所在部门的人数少于三个就不能删除。

CREATE OR REPLACE PROCEDURE sp_del(v_empno emp.empno%TYPE)ASv_count NUMBER;BEGINDELETE FROM emp WHERE empno=v_empno;SELECT COUNT(*) INTO v_count FROM empWHERE deptno=(SELECT deptno FROM emp WHERE empno=deptno);IF(v_count<3)THENROLLBACK;raise_application_error(-20045,'部门人数太少！');END IF;dbms_output.put_line('删除了'||SQL%ROWCOUNT||'行');COMMIT;END;3.函数与存储过程的区别：①函数必须有返回值②函数不能修改数据③都有in out （in out）三个参数4.常见函数举例①Ltrim与RtrimSELECT RTrim('afesafdafeg','eg') FROM dual; --这里就是把右边的eg截断SELECT LTrim('afesafdafeg','af') FROM dual; --这里就是把左边的af截断②LPad与RPadSELECT LPad('abcdefgtyi', 12, 'YY') FROM dual; --在列的左边粘贴字符，12表示在'abcdefgtyi'出现的索引值，不够就补YY上面的结果为：YYabcdefgtyiSELECT RPad('abcdefgyui', 12, 'YY') FROM dual; --同上结果为：abcdefgyuiYY5.包的使用（可以看成是JAVA里的接口）--先写包里定义函数，不写它的实现CREATE OR REPLACE PACKAGE my_fun ISFUNCTION f_get_double(a NUMBER) RETURN NUMBER; END;--包体CREATE OR REPLACE PACKAGE BODY my_fun IS FUNCTION f_get_double(a NUMBER) RETURN NUMBER ASBEGINRETURN a*2;END;END;6.什么时候函数与存储过程①不修改数据，只完成计算。