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.什么时候函数与存储过程①不修改数据，只完成计算。

ORACLE存储过程，函数，包，游标1、 PL/SQL语句块PL/SQL语句块只适⽤于Oracle数据库，使⽤时临时保存在客户端，⽽不是保存在数据库。

基本语法:Sql代码1. declare2. 变量声明、初始化3. begin4. 业务处理、逻辑代码5. exception6. 异常捕获7. end;变量声明：<变量名> <类型及长度> [:=<初始值>]例：v_name varchar2(20):=’张三’;2、循环语句loop循环语法：Sql代码1. loop2. exit when 表达式3. end loop;while循环语法：Sql代码1. while 表达式2. loop3. end loop;for循环语法:Sql代码1. for <变量> in <变量取值范围（⼩值..⼤值，如1..100）> loop2. end loop;for循环的变量可不做声明及初始化。

3、 if判断语句基本语法：Sql代码1. if <表达式> then2. …3. else if <表达式> then4. …5. else6. …7. end if;8. end if;例：Sql代码1. declare2. v_identity number(4):=0;3. begin4. loop5. if v_identity=1 then6. dbms_output.put_line('v_identity=1');7. else if v_identity=3 then8. dbms_output.put_line('v_identity=3');9. else if v_identity=6 then10. exit;11. else12. dbms_output.put_line('v_identity is not 1 or 3');13. end if;14. end if;15. end if; -- 注意，有多少个if就要有多少个end if结束标志。

Oracle存储过程和函数一、存储过程过程：将一些内部联系的命令组成一个个过程，通过参数在过程Z间传递数据。

存储过程的特点：1、存储过程里的代码都己经被编译过。

可以直接执行。

使用吋无需编译，提高工作效率2、客户端通过调用存储过程，可以减少网络流量，加快了系统执行速度。

3、可以减少SQL注入，提高系统的安全性。

4、在同时进行主、从表及多表间的数据维护及有效性验证时，使用存储过程比较方便，而且可以有效利用SQL中的事务处理的机制；5、使用存储过程，可以实现存储过程设计和编码工作分开进行存储过程包含1、声明部分：在声明部分可以对不同数据类型的数据进行声明，包括类型、游标、常量、变量、界常等。

2、执行部分：主要是为了完成或者达到一个特定的1=1的或功能3、异常处理部分：在执行过程中，对于引发错误的操作，进行异常捉示。

Oracle中创建存储过程的语法：Create or replace procedure 存储过程名（参数1 in type,参数2 out type）As声明语句Begin执行语句Exception异常处理语句End;注意：1、存储过程参数不带取值范围，in表示传入，out表示输出2、as后的语句声明变量，并且变量有取值范围，后面加上分号3、begin关键字表示PL/SQL的开始4、exception关键字用来处理异常，异常信息常用raise +异常名的方式5、end关键字表示存储过程的结束预定义异常的简单描述asuname varchar(20);beginselect name into uname from test where id = 23;dbms output.put line(uname);带输入参数存储过程create or replace procedure test_proc(uid in number) asuname varcr'iar (20);beginselect name into uname from test where id = uid; dbms cmtput.put line(uname);exceptionwhen NO DATA FOUND thenRAISE APPLICATION ERROR(-20011, 'ERROR：不存在！ *);end;带输出参数存储过程create or replace procedure test_proc(num out number) asbeginselect count(*) into num from test where id = 25; dbms output•put line(num); exceptionwhen NO DATA FOUND thenRAISE APPLICATION ERROR(-20011, 'ERROR:不存在!');end;带输出和输出参数存储过程create or replace procedure test_proc (uio umber z num out number)3 Suname varchar(20);beginselect name into uname from test where id = uid;select count (*) into num from test where id = 25;dbms_output.put_line (uname);dbms output.put line (num);when NO DATA FOUND t-ienRAISE APPLICATION ERROR (-20011, •ERROR：不存在！1 );存储过程的调用通过Call存储过程名称（参数）；Begin存储过程名称（参数）；End;二、函数Oracle屮的函数与存储过程类似，也是将一组能够实现特定功能的SQL或者PL/SQL 语句块组合在一起的程序集，并且能够将执行结果返冋。

Oracle的函数和存储过程【】主键就是区别这个表的唯一关键字比如一个学生表学号不能重复且唯一学号就是关键字(此时学号就可以作为主键)【】外键就是跟其他表联系的字段还是比如有一张学生表还有一张选课表这个时候要修改学生表中的学号选课表里对应的就也得变这样就需要给选课表加学号作为外键约束这样当你修改学号时所有外键关联的就都改了【】视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。

视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。

【】在Java程序中设置事务处理setAutoCommit(false);不让其自动提交。

【函数：】①字符函数：lower(char):将字符串转化为小写的格式upper(char):将字符串转化为大写的格式length(char):返回字符串的长度substr(char,m,n):取字符串的子串写sql语句1分析清晰思路【replace函数】replace(char1,search_string，replace，)例子：select replace(ename,'A','我是老鼠')from emp;----->含义：把字段ename中凡是‘A’用'我是老鼠'替代。

【日期函数】sysdate 返回系统当前时间add_months 超过了指定月份的日期to_char 把其他的转换成字符串的函数【数据库的导入和导出】以.dmp的格式导出数据库到磁盘exp call_out/call_out@orcl owner=call_out file=d:\call_out.dmp导入数据库:(导入自己的表)imp userid=call_out/call_out@orcl tables=(emp) file=d:\xx.dmp【表空间】表空间是数据库的逻辑组成部分，数据库数据存放在数据文件中，表空间是由一个或多个数据文件组成的、【索引】可显著提高数据库的查询速度，比如char定长可以显著提高查询速度。

Oracle常用包、函数、存储过程Oracle常用包、存储过程、函数常用包、存储过程1 dbms_output作用：输入和输出信息，使用过程PUT和PUT_LINES可以将信息发送到缓冲区，使用过程GET_LINE和GET_LINES可以显示缓冲区信息。

1.1 enable该过程用于激活对过程PUT，PUT_LINE，GET_LINE，GET_LINES的调用。

语法如下：dbms_output.enable(buffer_size in integer default 20000);1.2 disable该过程用于禁止对过程PUT，PUT_LINE，GET_LINE，GET_LINES的调用。

语法如下：dbms_output.disable;1.3 put和put_line过程put_line用于将一个完整行的信息写入到缓冲区中，过程put则用地分块建立行信息，当使用过程put_line时，会自动在行的尾部追加行结束符；当使用过程put时,需要使用过程new_line追加行结束符。

示例如下:set serverout onbegindbms_output.put_line('伟大的中华民族');dbms_output.put('中国');dbms_output.put(',伟大的祖国');dbms_output.new_line;end;/伟大的中华民族中国,伟大的祖国1.4 new_line该过程用于在行的尾部追加行结束符。

当使用过程PUT时，必须调用NEW_LINE过程来结束行。

1.5 get_line和get_lines过程get_line用于取得缓冲区的单行信息，过程get_lines用于取得缓冲区的多行信息。

2 dbms_job作用：安排和管理作业队列，通过使用作业，可以使ORACLE数据库定期执行特定的任务。

2.1 submit用于建立一个新作业。