三、ORACLE之PLSQL编程

2017-12-11 本文已影响0人 cqzhangjian

ORACLE 数据库中包含一种过程化语言，称为 PL/SQL(Procedural Language/SQL)。PL/SQL 可以用来编写包含 SQL 语句的程序。在 PL/SQL 中可以使用 IF 语句或者 LOOP 循环语句实现控制程序的执行流程，还可以定义变量，实现语句之间传递数据信息，从而 PL/SQL 语言可以封装程序实现操控程序处理的细节。PL/SQL 是 ORACLE 的专用语言，它是对 SQL 语言的扩展，它允许在其内部嵌套普通的 SQL 语句。

1.PL/SQL 块结构

PL/SQL 程序都是以块为基本单位，整个 PL/SQL 块分为三部分：声明部分、执行部分和异常处理部分。

1.1 语句结构如下:

[DECLARE]
--声明部分，可选
BEGIN
--执行部分，必选
[EXCEPTION]
--异常处理部分,可选
END

例子：

DECLARE
 v_num1 int:=400;
 v_num2 int:=2;
 v_result int;
BEGIN
  v_result := v_num1 / v_num2;
  DBMS_OUTPUT.put_line(v_result);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('出现异常了!');
END;

2.变量和类型

2.1 定义变量语法：

变量的名称变量的类型; eg: v_num number;
变量的名称变量的类型 := 值; eg:v_num number := 100;
定义一个常量 eg: PI constant number := 3.1415926;常量的定义一定要初始化，不然就要报错。
使用表名.列名%type; eg: v_sal emp.sal%type; 该方式引用emp表中的sal列作为变量的类型
使用表名%rowtype; eg:v_emprow emp%rowtype;该方式引用了emp表中的一行数据类型作为变量的类型。

[:=]是赋值运算符

2.2 PLSQL中的类型

1.数值型:number、int 、Integer 、 float等
2.字符型：varchar 、varchar2 、char等
3.日期型:Date
注意，只有字符型要指定长度，其他可以不指定

例子：

DECLARE
  -- 定义都是变量 语法结构： 变量的名称 变量的类型; 称为声明一个变量； 变量的名称 变量的类型 :=[赋值运算符] 值; 称为声明变量并初始化；
  v_num1 number :=200; 
  v_num2 number := 0;
  PI constant number := 3.1415926;
  v_result number;
BEGIN
  v_result := v_num1 / v_num2;
  -- 以下你就认为是java中的 system.out.println();
  DBMS_OUTPUT.put_line('结果为:' || v_result);
EXCEPTION
  WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('程序有异常！');
END;

-- PLSQL 跟  ORACLE中表联系起来 查询SMITH员工的工资，部门编号，入职日期信息，员工姓名打印到控制台
DECLARE
   v_sal number;
   v_deptno number;
   v_hiredate date;
   v_ename varchar2(50);
BEGIN
   -- INTO 用于 SELECT 查询语句中，表示把查询的列数据赋值给相应的变量
   SELECT SAL,DEPTNO,HIREDATE,ENAME  INTO  v_sal, v_deptno, v_hiredate, v_ename FROM EMP WHERE ENAME = 'SMITH';
   
   DBMS_OUTPUT.put_line( v_sal || ',' || v_deptno||',' ||v_hiredate||','|| v_ename);

END;
-- 表.列%type;指定表中某个列的类型长度定义变量
DECLARE
   v_sal emp.sal%type;
   v_deptno emp.deptno%type;
   v_hiredate emp.hiredate%type;
   v_ename emp.ename%type;
BEGIN
   -- INTO 用于 SELECT 查询语句中，表示把查询的列数据赋值给相应的变量
   SELECT SAL,DEPTNO,HIREDATE,ENAME  INTO  v_sal, v_deptno, v_hiredate, v_ename FROM EMP WHERE ENAME = 'SMITH';
   
   DBMS_OUTPUT.put_line( v_sal || ',' || v_deptno||',' ||v_hiredate||','|| v_ename);

END;


DECLARE
   -- 表中的一行作为变量的类型
   v_emprow emp%rowtype;
BEGIN
   
   SELECT *  INTO v_emprow  FROM EMP WHERE ENAME = 'SMITH';
   
   DBMS_OUTPUT.put_line( v_emprow.sal || ',' || v_emprow.deptno||',' ||v_emprow.hiredate||','||v_emprow.ename);

END;

3.条件语句

条件语句用于控制程序的流程。在 PLSQL 中定义了有：

IF 条件 THEN 执行的语句
ELSE 条件 THEN 执行语句
ELSE 执行语句
END IF标志条件语句结束

例子：-- 输入年龄，可以判断该人是步入什么阶段儿童、青年、中年、老人、去死吧

-- 判断语句  输入年龄 ，可以判断该人是步入什么阶段  儿童 、 青年 、 中年 、老人 、 去死吧

DECLARE
   -- &age 模拟输入的年龄
   v_age number:=&age;
BEGIN
   
   IF v_age < 12 THEN
     DBMS_OUTPUT.put_line('你是儿童');
     
     ELSIF v_age < 30 THEN
           DBMS_OUTPUT.put_line('你是青年');
      ELSIF v_age < 50 THEN
           DBMS_OUTPUT.put_line('你是中年');
       
      ELSIF v_age < 100 THEN
           DBMS_OUTPUT.put_line('你是老人');
   ELSE
           DBMS_OUTPUT.put_line('你去死吧！！');
   END IF;  
END;

4.循环语句

循环语句有而是来控制程序执行的。PLSQL 中循环分三种。

LOOP 循环体 END LOOP
WHILE 条件 LOOP 循环体 END LOOP
FOR 变量名称 IN [ REVERSE ] 范围1..100 LOOP
循环体
END LOOP;

例子：

-- 数数，从1数到100，输出到控制台  FOR 变量名称 IN 范围 LOOP 循环体 END LOOP 
DECLARE
   v_num number :=&num;
BEGIN
   
   FOR i IN  REVERSE 1..v_num LOOP
     
   DBMS_OUTPUT.put_line(i);
   
   END LOOP;
END;

-- LOOP  循环体  END LOOP : EXIT WHEN条件
DECLARE
   v_num number :=&num;
   v_index number := 1;
BEGIN
   LOOP
    -- 循环退出的语句
    EXIT WHEN v_index > v_num;
    DBMS_OUTPUT.put_line(v_index);
    v_index := v_index + 1;
   END LOOP;
END;

-- WHILE 条件   LOOP  循环体  END LOOP;

DECLARE
   v_num number :=&num;
   v_index number := 1;
BEGIN
   WHILE v_index <=  v_num LOOP
    DBMS_OUTPUT.put_line(v_index);
    v_index := v_index + 1;
   END LOOP;
END;

5.游标

数据类型，用于接收一组数据，通过游标对象中的属性进行数据抓取
游标分两类：1.显示游标 2.隐式游标
游标对象中的属性：%found(判断当前是否还有数据行，如果有返回true)、%notfount(判断当前是否还有数据行，如果没有返回true)、 %isopen(游标是否是打开状态) 、 %ROWCOUNT (返回受影响的行数)、%ROWNUM(返回游标读取行的位置)

显示游标的使用过程
1.定义游标类型
2.定义游标变量
3.指定游标关联的查询SQL
4.先打开游标 open
5.抓取游标行数据 fetch
6.关闭游标 close

例子：查询emp表中所有数据，打印到控制台

-- 游标(动态游标) 使用方式1 该方式游标变量可以重复利用
DECLARE
   -- 1.定义游标类型
   type cur_emp is ref cursor;
   -- 2.定义游标变量
   my_cur cur_emp;
   -- 定义一个游标抓取行的临时变量
   v_emprow emp%rowtype;
BEGIN
  -- 3.游标变量关联上查询SQL
     open my_cur  for  SELECT * FROM EMP;
  -- 4.打开游标
    -- open my_cur;
  -- 5.抓取游标行数据
     LOOP
      fetch my_cur into  v_emprow;
      EXIT WHEN my_cur%NOTFOUND;
      DBMS_OUTPUT.put_line( v_emprow.sal || ',' || v_emprow.deptno||',' ||v_emprow.hiredate||','||v_emprow.ename);
     END LOOP;
  -- 6.关闭游标
  close my_cur;
  
END;


-- 游标(静态游标) 使用方式2
DECLARE
   -- 1.定义游标变量,并关联上查询SQL
   CURSOR my_cur is select * from  emp;
   -- 定义一个游标抓取行的临时变量
   v_emprow emp%rowtype;
BEGIN
 
  -- 3.打开游标
  open my_cur;
  -- 4.抓取游标行数据
     LOOP
      fetch my_cur into  v_emprow;
      EXIT WHEN my_cur%NOTFOUND;
      DBMS_OUTPUT.put_line( v_emprow.sal || ',' || v_emprow.deptno||',' ||v_emprow.hiredate||','||v_emprow.ename);
     END LOOP;
  -- 5.关闭游标
  close my_cur;
END;

静态游标可以使用 for in loop 遍历游标方式

-- 静态游标遍历数据，简化，使用 for in LOOP 语句
DECLARE
   -- 1.定义游标变量,并关联上查询SQL
   CURSOR my_cur is select * from  emp;

BEGIN
      FOR v_emprow IN my_cur LOOP
      DBMS_OUTPUT.put_line( v_emprow.sal || ',' || v_emprow.deptno||',' ||v_emprow.hiredate||','||v_emprow.ename); 
      END LOOP;
END;

带参数的静态游标

-- 带参数的静态游标  查询10号部门的员工信息,并打印到控制台。

DECLARE
   v_input number := &depno;
   CURSOR emp_cursor(v_deptno emp.deptno%type) is SELECT * FROM EMP WHERE DEPTNO = v_deptno;
BEGIN
  FOR v_emprow IN emp_cursor(v_input) LOOP
     DBMS_OUTPUT.put_line( v_emprow.sal || ',' || v_emprow.deptno||',' ||v_emprow.hiredate||','||v_emprow.ename); 
  END LOOP;
END;

隐式游标

没有显示的名字的游标，但是有一个默认名字SQL，，当然也具有游标的属性
%notfound、 %found、 %isopen 、%rownum、 %ROWCOUNT

-- 隐式游标
DECLARE
   v_input number := &depno;
BEGIN
   FOR v_emprow IN (SELECT * FROM EMP WHERE DEPTNO = v_input) LOOP
      DBMS_OUTPUT.put_line( v_emprow.sal || ',' || v_emprow.deptno||',' ||v_emprow.hiredate||','||v_emprow.ename); 
   END LOOP;
END;

-- 传一个员工编号，就把该条记录删除掉
DECLARE
   v_input number := &empno;
BEGIN
  DELETE FROM EMP WHERE EMPNO = v_input;
  DBMS_OUTPUT.put_line(SQL%rowcount);
END;

6.函数

函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句。而在函数体内必须包含return语句返回的数据。

创建一个函数的结构:
CREATE [OR REPLACE] FUNCTION 函数的自定义名称 (形参列表) RETUREN 返回数据的类型
IS
--声明部分
BEGIN
-- 函数主题部分
END;

例子:

-- 函数
CREATE OR REPLACE FUNCTION FUN_EMP(v_input  number) RETURN NUMBER
IS
 -- 定义变量的部分
BEGIN
  DELETE FROM EMP WHERE EMPNO = v_input;
  RETURN SQL%rowcount;
END;

使用函数方式1：
DECLARE
  v number;
begin
  v := FUN_EMP(7369);
end;
=========
方式2，只支持函数的操作不能是DML操作：
 
SQL> SELECT FUN_EMP2(7369) FROM DUAL;
 
FUN_EMP2(7369)
--------------
           800

7.过程

过程用于执行特定的操作，当建立过程时，既可以指定输入参数(in)，也可以指定输出参数(out)，通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境

过程创建的结构：
CREATE [OR REPLACE] PROCEDURE 过程自定义名称 (参数列表)
IS
-- 申明部分
BEGIN
-- 过程体
END;

例子：

-- 指定员工编号，输出该员工的姓名和薪水以及入职日期
CREATE OR REPLACE PROCEDURE PRO_EMP(v_empno IN emp.empno%type,v_ename OUT EMP.ENAME%TYPE,v_sal OUT emp.sal%type,v_hiredate out emp.hiredate%type)

is
      -- 申明部分
begin
      -- 过程体
      SELECT SAL,ENAME,HIREDATE INTO v_sal,v_ename,v_hiredate FROM EMP WHERE EMPNO = v_empno;

end;

使用过程：

方式1，在sql window窗口中调用
declare
      v_ename  EMP.ENAME%TYPE;
      v_sal  emp.sal%type;
      v_hiredate  emp.hiredate%type;
begin
  pro_emp(7369,v_ename,v_sal,v_hiredate);
  DBMS_OUTPUT.put_line(v_ename);
   DBMS_OUTPUT.put_line(v_hiredate);
    DBMS_OUTPUT.put_line(v_sal);
end;

  方式2，在sql命令窗口中调用
SQL> var c1 varchar2(10);
SQL> var c2 number;
SQL> var c3 date;
SQL> CALL PRO_EMP(7369,:c1,:c2,:c3);
 
Method called
c1
---------
SMITH
c2
---------
800
c3
---------
1980/12/17
==========================

SQL> exec PRO_EMP(7499,:c1,:c2,:c3);
 
PL/SQL procedure successfully completed
c1
---------
ALLEN
c2
---------
1600
c3
---------
1981/2/20

8.JDBC调用过程、函数

8.1JDBC 调用函数

  package com.xingxue.oracle.function;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;

public class JdbcFunction {

    public static void main(String[] args) {

        Connection conn = null;
        // 该对象是用于发出 执行函数(过程)的对象
        CallableStatement prepareCall = null;
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.189:1521:orcl", "scott", "tiger");
            prepareCall = conn.prepareCall("{?=call fun_emp2(?)}");
            // 设置暂位符
            prepareCall.setObject(2, 7902);
            // 注册一个返回的值参数
            prepareCall.registerOutParameter(1, Types.DOUBLE);
            // 发出执行
            prepareCall.execute();
            double sal = prepareCall.getDouble(1);
            System.out.println(sal);

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

}

8.2 JDBC 调用过程

package com.xingxue.oracle.function;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;

public class JDBCPro {

    public static void main(String[] args) {
        Connection conn = null;
        CallableStatement prepareCall = null;
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.189:1521:orcl", "scott", "tiger");
            prepareCall = conn.prepareCall("{call pro_emp(?,?,?,?)}");
            // 设置 in 参数
            prepareCall.setObject(1, 7369);
            // 注册 out 参数
            prepareCall.registerOutParameter(2, Types.VARCHAR);
            prepareCall.registerOutParameter(3, Types.DOUBLE);
            prepareCall.registerOutParameter(4, Types.DATE);
            // 执行
            prepareCall.execute();

            Object o1 = prepareCall.getObject(2);
            Object o2 = prepareCall.getObject(3);
            Object o3 = prepareCall.getObject(4);
            System.out.println(o1);
            System.out.println(o2);
            System.out.println(o3);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

package com.xingxue.oracle.pro;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

import oracle.jdbc.OracleTypes;

public class JDBPro2 {
    public static void main(String[] args) {
        Connection conn = null;
        CallableStatement prepareCall = null;
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.189:1521:orcl", "scott", "tiger");
            prepareCall = conn.prepareCall("{call pro_emp_deptno(?,?)}");
            // 设置 in 参数
            prepareCall.setObject(1, 20);
            // 注册 out 游标参数
            prepareCall.registerOutParameter(2, OracleTypes.CURSOR);
            // 执行
            prepareCall.execute();

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

            while (rs.next()) {
                Object object = rs.getObject("sal");
                Object object1 = rs.getObject("hiredate");
                System.out.println(object + ":" + object1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}