PLSQL 入门，包含在java程序中的使用

PL/SQL总结

一、为什么需要PL/SQL？

理由：1、原来在java应用程序中直接传入sql语句，这样在oracle数据库再编译执行，效率较低，而且网络传输不安全，容易被修改。

模块化编程。

      2、PL/SQL过程很函数保存在数据库中，java程序直接调用，效率和安全性都更高。

缺点：移植性能差。过程函数等都存在数据库中，换了数据库不兼容就不能在用了。

二、pl/sql分类 -- 块，过程，函数，包，触发器。

块：

PL/SQL块是程序的最基本单元，它由定义部分，执行部分，和异常处理部分组成。

declare

--定义部分：定义变量、常量、游标和自定义数据类型等

begin

--需要执行的语句

 exception

--异常处理部分

 End；

其中，定义部分和异常处理部分是可以选择不要的，最简单的块就只有begin和end。

--在PL/SQL中需要输出在窗口的话，需要设置

Set serveroutput on；

--根据学号查询学生姓名，并输出

Set serveroutput on；

Declare

v_name varchar2(20);

Begin

Select sname into v_name from student where sid=1;

dbms_output.put_line('学生姓名'||v_sname);

End； 

 

过程：过程可以指定输入参数和输出参数，它用于执行特定的操作，编译好之后可以重复调用。

语法：

--创建或者替换过程，后面是过程名，之后是is或者as,接着是定义变量

注意：在plsql中，定义变量是变量名在前，而且在定义参数的时候不需要指定参数的字符串长度或者number长度。

创建一个过程，根性id查找学生姓名

Create or replace procedure pro_01（v_sid number） is

V_sname varchar2(20);

Begin

Select sname into v_sname from student where sid=v_sid;

dbms_output.put_line('学生姓名'||v_sname);

End;

调用过程：

Exec pro_01（1）；

 

函数：

函数用于返回特定的数据，在定义时候头部不要return + 数据类型，在代码块中也必须包含return 变量

定义语法：

Create or replace function fun_01(v_sid numnber) return varchar2 is 

V_sname varchar(20);

Begin

Select sname into v_sname from student where sid=v_sid;

Return v_sname;

End;

调用函数的语法

1、定义变量

var v_sname varchar(20)

Call fun_01(1) into :v_sname;

 

包：便于管理函数和过程，它由规范和包体两部分注册，规范是对过程和函数的定义，包体是对过程和函数的具体实现，包还可以用于保持下自定义类型。

包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。

1、创建一个包

Create or replace package package_01  is

  Procedure pro_01(v_sid number);

  Function fun_01(v_sid number);

End;

2、包体，对包中函数和方法的具体实现

Create or replace package body package_01 is

   Procedure pro_01(v_sid number)  is

   V_sname varchar2(20);

  Begin

  Select sname into v_sname from student where sid=v_sid;

  Dbms_output.put_line('学生姓名'||v_sname);

 

  End;

 

Function  fun_01(v_sid number)  return varchar2 is 

V_sname varchar(20);

Begin

Select sname into v_sname from student where sid=v_sid;

Return v_sname;

End;

End;

 

三、数据类型

分类：标量、复合类型、参照类型

标量是已经预定义好的类型：

比如：定义一个number并给其赋值

V_sid number(3):=100;

注意：在plsql中赋值时使用的 ：= 一定要特别注意。

 

参照类型，比如变量的类型是表中某一类的属性，这个时候我们直接参照表中的列的类型，比如varchar2，这个时候我们就不用再自己来定义它的长度，以免浪费和不够。

V_sname student.sname%type;

 

复合类型：就相当于c语言中的结构体，又比如java中的类，只有成员属性没有成员方法，这个类可以保存多个不同的数据。

1.   type emp_record_type is record(   

2.     name   emp.ename%type,   

3.     salary emp.sal%type,   

4.     title  emp.job%type);   

游标：游标是用于多结果集的一行一行的处理，按照编程的方式来访问，在过程中，定义输出参数为游标，在java程序中来得到游标，也就是得到了一个结果集来操作。

Ref游标，在语句块中打开并且定义select语句，一般游标在声明时候定义select语句。

//游标类型在Oracle包 他在输入     sql语句的时候时候调用方式。                              cstm.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);

那么在java程序中得到该输出参数的时候返回的也是一个结果集

//获取数据,游标类型可以强转成ResultSet

// 2表示第二个参数，他是输出参数并且是游标类型

ResultSet rs = (ResultSet)cstm.getObject(2);

 

 

四、在java程序中操作数据库的三个类，和调用过程

*********************************************************

Statement---这种方式非常不安全，因为，那么可以随意拼接，后面还可以拼接or ‘1’=‘1’这种逻辑值为true的表达式，这样的话查询的每行值都是正确的，造成数据被盗。

//这里name是函数传过来的

String sql="select sal from scott.emp where ename='"+name+"'";

Statement stm = conn.createStatement();

 

 stm.executeQuery(sql);在执行的时候传入sql，非常不安全

 

*********************************************************

PreparedStatement--他可以解决传入一个不合法的表达式比如上面说的，他会自动屏蔽掉

 

String sql="select sal from scott.emp where ename=?";

//先将sql进行编译，然后再设这sql中的？的值

PreparedStatement pst= conn.prepareStatement(sql);

//这个时候再传值，第一个位置表示第几个问号，后面就是要传的值

pst.setString(1, name);

//执行的时候不需要再传入sql语句，所以没有再编译，可以直接执行。

        ResultSet set=pst.executeQuery();

 

****************************************************************

CallableStatement --该类可以调用知道的过程和函数，可以利用返回的游标类型来得到结果集，按照编程的方式访问数据。

// 定义sql语句

String sql = "{call selectNameSal4id(?,?,?)}";

// 获得编译对象

CallableStatement csmt = conn.prepareCall(sql);

// 输入参数传入

csmt.setInt(1, id);

// 注入输出参数

csmt.registerOutParameter(2, java.sql.Types.VARCHAR);

csmt.registerOutParameter(3, java.sql.Types.FLOAT);

// 执行过程

csmt.execute();

// 获得输出参数的过程，第2个和第三个是输出参数

String username = (String) csmt.getObject(2);

Float sal =  csmt.getFloat(3);

System.out.println("姓名：" + username + "  工资:" + sal);

//java程序中得到游标

try {

Connection conn = DBUtil.getConn();

 

String sql = "{call pro_getAllEmp(?,?)}";

 

CallableStatement cstm = conn.prepareCall(sql);

 

cstm.setInt(1, deptno);

//游标类型在Oracle包中

cstm.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);

 

cstm.execute();

 

//获取数据,游标类型可以强转成ResultSet

ResultSet rs = (ResultSet)cstm.getObject(2);

while(rs.next()){

String ename = rs.getString(1);

float sal = rs.getFloat(2);

System.out.println(ename+"  "+sal);

}

 

} catch (Exception ef) {

ef.printStackTrace();

}

 

<!--EndFragment-->