PL/SQL块：
PL/SQL块都用于解决特定问题，PL/SQL块可以有参数，也可以相互调用。
PL/SQL块可以作为模式对象在Oracle数据库中保存，根据用途可以分为两类：
1、存储过程：用于特定目的，执行一系列操作的PL/SQL块
2、自定义函数：通过计算返回调用者一个值的PL/SQL块
存储过程：
存储过程是由流控制和SQL语句组成的，经编译和优化后存储在数据库服务器中，使用时只需调用即可。
存储过程类似于高级程序设计语言中模块的概念，它将一些内部联系的命令组成一个个存储过程，通过参数在存储过程之间传递数据是模块化设计思想的主要内容。
SQL语句执行的时候要先编译，然后执行。存储过程就是编译好了的一些SQL语句。可以直接执行，所以执行效率较高。
存储过程的优点：
1、存储过程大大增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
2、可保证数据的安全性和完整性：
a：通过存储过程可以使没有使用权限的用户间接地存取数据，从而保证数据的完全。
b : 通过存储过程可以使相关的动作在一起发生，从而维护数据库的完整性。
3、在运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案，这种已经编译好的存储过程极大地改善SQL语句的性能。
4、使用存储过程可以降低网络的通信量。
5、使体现应用需要的运算程序放入数据库服务器中，以便集中控制。
存储过程定义：
存储过程的创建：
1、创建存储过程的语法结构，完整的存储过程结构如下：
create [or replace] procedure 存储过程名称
as 声明语句
begin 执行语句段；
exception
异常处理语句段
end；
2、存储过程示例：
create or replace procedure scott.tempprocedure as
tempdate scott.emp.hiredate%type;
begin select hiredate into tempdate
from scott.emp
where scott.emp.SAL = 1500;
dbms_output.put_line('存储过程生成的变量值：'||tempdate);
end;
存储过程的修改：
存储过程的修改有两个方法：
方法一：如果以前已经写了存储过程的全部或部分，现在需要修改或者继续完成，可以用SLQPlus Worksheet打开以前保存的文件，方法是在SLQPlus Worksheet的菜单栏选择“文件”-->“ 打开”命令，将创建存储过程的SQL文件调出进行修改，修改完毕后保存并重新执行创建存储过程即可。
方法二：1、在企业管理器里选中要修改的存储过程，用鼠标右键点击，在出现的快捷菜单里选择“查看/编辑详细资料”菜单项；2、在弹出“编辑过程”窗口中的文本域中修改存储过程之后点击窗口中的“应用”按钮即可完成对存储过程的修改。
存储过程的执行：
创建存储过程的最终目的是为了执行，所以创建存储过程后，需要调用它，才会让它执行。要执行创建的存储过程，必须通过主程序来调用存储过程，如果我们要调用前面写好的存储过程，可以在SQLPlus Worksheet中执行下列PL/SQL程序：
带参数的存储过程：
创建带参数的存储过程的语法如下：
create [or replace] procedure 过程名
参数1 [in|out|in out] 数据类型
[,参数2 [in|out|in out] 数据类型] ...
{is|as} pl/sql 语句
1、参数类型
在PL/SQL存储过程中，可以有3种类型的参数，其功能简要概述如下：
in参数：读入参数，主程序向存储过程传递参数。
out参数：读出参数，存储过程向主程序传递参数值。
in out参数：双向参数，存储过程与主程序双向交流数据。
定义带参数的存储过程：
create or replace procedure scott.tempprocedure1(
tempdeptno in scott.dept.deptno%type,
tempdname out scott.dept.dname%type,
temploc in out scott.dept.loc%type)
as loc1 scott.dept.loc%type;
dname1 scott.dept.dname%type;
begin select loc into loc1
from scott.dept
where deptno=tempdeptno;
select dname into dname1
from scott.dept
where deptno = tempdeptno;
temploc:='地址：'||loc1;
tempdname:='姓名'||dname1;
end；
该存储过程的功能是：从数据表scott.dept中寻找deptno字段等于一个变量（即调用参数，这里我们命名为tempdeptno的dname和loc字段，并将这两个字段的值赋给新的变量）
带参数的存储过程的调用：
在主程序中的实际参数和过程中的形式参数的传递有很多中方法，这里推荐采取——对应的办法，按对应的位置传递参数。要求实际参数和形式参数在数据类型和位置排列上做到完全一致。
set serveroutput on
declare myno scott.dept.deptno%type;
mydname scott.dept.dname%type;
myloc scott.dept.loc%type;
begin myno:=10;
mydname:='';
myloc:='';
scott.tempprocedure1(myno,myname,myloc);
dbms_output.put_line(myno);
dbms_output.put_line(myname);
dbms_output.put_line(myloc);
end;
自定义函数：
创建自定义函数语法如下：
create or replace function 函数名（参数名 参数类型，......）
return 返回值类型
is
begin
......
end 函数名；
例：
create or replace function
get_sal(empname in varchar2) return
number is
Result number;
begin
select sal into Result from emp where
ename = empname;
return(Result);
end get_sal;
执行：SQL>var sal number
SQL>exec:sal:=get_sal('scott');
使用包：
包可以看做是过程和函数的集合，对过程和函数进行更好的封装。
包的作用：包可以将任何出现在块声明的语句（过程，函数，游标，类型，变量）放于包中，相当于一个容器。将声明语句放入包中的好处是：用户可以从其他PL/SQL块中对其进行引用，因此包为PL/SQL提供了全程变量。
包的包括包头和包体两部分，需要分别定义。
定义包头：
create or replace package 包名
is
过程名/函数名； --申明了该包中的过程及函数
end 包名；
定义包体：
create or replace package body 包名
is
过程/函数定义实现；
end 包名；
例：
创建包头：
create or replace package fitpackage
is
procedure myproc1(num in number);
procedure myproc2;
function myfunc(stuno varchar2) return number;
end fitpackage;
创建包体：包名以及包内的函数，过程名以及参数列表都要和包头定义中的内容一致
create or replace package body fitpackage
is
procedure myproc1(num in number)
as
......
end myproc1;
procedure myproc2
as
......
end myproc2;
function myfunc(stuno varchar2)
return number
as
......
end myfunc;
end fitpackage;
JAVA程序调用存储过程：
JDBC通过CallableStatement接口提供对此类功能的支持。可以通过Connection对象的prepareCall()方法来，该方法的参数是一个String，是一种SQL转义语法：
例如：
过程的参数：
in参数的占位符为？，使用CallableStatement接口的setXXX（）方法来为参数设置值。
out参数的占位符为？，使用的时候必须先使用CallableStatement接口的registerOutParameter()方法注册此参数，最后通过getXXX()方法来取值。
对于存储过程的返回值，除索引号为1外，其他与out参数用法一样。
带有输出参数：
创建存储过程：
create or replace procedure P_myPro_ConnOracle(empId in int,empName out String)
as
begin
select e.ename
into empName
from emp e
where e.empno = empId;
dbms_output.put_line('empId='||empId);
dbms_output.put_line('empName='||empName);
end;
JDBC操作：
//获得CallableStatement对象
CallableStatement ctmt = conn.prepareCall("{call proc_forvalue(?,?)}");
//设置输入参数
ctmt.setString(1,type);
//注册输出参数及数据类型
ctmt.registerOutParameter(2,java.sql.Types.INTEGER);
//调用存储过程，并查询输出参数的值
ctmt.execute();
result = ctmt.getInt(2);
使用游标返回结果集：
创建存储过程：
create or replace package my_package
as
type my_cursor is ref cursor;
end my_package;
create or replace procedure my_test(test_result
out my_pakcage.my_cursor)
as
begin
open test_result for
select * from dept;
end;
使用游标返回结果集：
JDBC操作:
//获得CallableStatement对象
CallableStatement proc= conn.prepareCall("{call my_test(?)}");
//设置输入参数
proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
//调用存储过程，并获得第一个产生的结果集
proc.execute();
rs = (ResultSet)proc.getObject(1);//1指的是参数1
while(rs.next()){
System.out.println(rs.getString(1)+","+rs.getString(2));
}
京公网安备 11010802041100号