PL/SQL笔记

PL/SQL笔记

使用Oracle数据库的SCOTT用户中的表来演示

匿名块

-- 结构
declare -- 声明
    v_ename varchar2(20)
begin
    select ename into v_ename from emp where empno=7839;
    dbms_output.put_line('president is' || v_ename);
end;
/

演示用游标读取emp表

-- 开启屏幕打印（命令行窗口打印）
SET SERVEROUTPUT ON;

DECLARE
    --1 定义游标
    cursor emp_cursor IS
    SELECT ename, sal from emp;
    --2 定义记录
    emp_record emp_cursor%rowtype;
BEGIN
    open emp_cursor;-- 打开游标：意味着执行SELECT ename, sal from emp;
    -- 对游标进行遍历
    loop
        exit WHEN emp_cursor%notfound;-- 如果已经遍历了游标所以数据，跳出循环
        FETCH emp_cursor into emp_record;--将游标中的下一条记录放入record数据
        dbms_output.put_line(emp_record.ename || ":" || emp_record.sal);--打印一条记录中的字段
    end loop;
    close emp_cursor;-- 释放锁获取的数据库资源--连接，sql引擎
END;

存储过程

CREATE [OR REPLACE] FUNCTION function_name
    [ (parameter [,parameter]) ]
    RETURN return_datatype
IS | AS
    [declaration_section]
BEGIN
    executable_section
[EXCEPTION
    exception_section]
END [function_name];

使用存储过程进行CRUD

-- 增删改
CREATE OR REPLACE 
PROCEDURE pro_addEmp (v_empno  NUMBER, v_ename  VARCHAR2, v_job  VARCHAR2, v_mgr  NUMBER, v_hiredate  DATE, v_sal  NUMBER, v_comm  NUMBER, v_deptno  NUMBER)
AS
BEGIN
    insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values(v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno);
    -- delete ...
    -- update ...
END;

-- 查
SET SERVEROUTPUT ON;
SET AUTOCOMMIT ON;
CREATE OR REPLACE 
PROCEDURE proc_fuzzyQuery(
fuzzy_name IN VARCHAR2,
result_cursor OUT SYS_REFCURSOR)
IS
BEGIN
--根据输入条件生成游标,并通过procedure的OUT参数返回给调用者
--注意:此处采用的是投影查询方式,没有查询全部
OPEN result_cursor FOR SELECT empno,ename,sal,deptno FROM emp
WHERE ename LIKE '%'||fuzzy_name||'%';
END;
/

使用匿名块演示执行查询

set SERVEROUTPUT on;
set AUTOCOMMIT on;
-- 在PL/SQL匿名块中验证proc_fuzzyQuery的执行效果
DECLARE
--1 声明记录数据类型对应投影查询结果
type EMP_RECORD_TYPE
IS
    record(
    empno EMP.empno%type,
    ename EMP.ename%type,
    sal EMP.sal%TYPE,
    deptno EMP.deptno%TYPE
);
--2 生成记录变量
emp_record EMP_RECORD_TYPE;
--3 声明系统游标类型变量
result_cursor SYS_REFCURSOR;

BEGIN
--4 调用存储过程，传递参数，获取产生的游标
proc_fuzzyQuery('t', result_cursor);
LOOP
    FETCH result_cursor into emp_record;-- 由于存储过程返回的游标不支持
    EXIT                                                                -- 简单的for循环，需要使用fetch遍历
WHEN result_cursor%notfound;

    dbms_output.put_line(
    emp_record.empno||CHR(10)||emp_record.ename||CHR(10)
    ||emp_record.sal||CHR(10)||emp_record.deptno||CHR(10)||'************');
end loop;
close result_cursor;
END;
/

演示使用java的jdbc调用存储过程

需要ojdbc14.jar包

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

import oracle.jdbc.OracleTypes;

public class GetCursorByProcDemo {
    public static void main(String[] args) throws Exception {
        // 1 加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        // 2 获取数据库连接
        String dbUrl = "jdbc:oracle:thin:@localhost:1521:orcl";
        String username = "scott";
        String password = "tigger";
        Connection con = DriverManager.getConnection(dbUrl, username, password);
        // 3 调用存储过程
        String proc_sql = "{call proc_fuzzyQuery(?,?)}";
        CallableStatement cs = con.prepareCall(proc_sql);
        // 4 为存储过程赋予参数
        cs.setString(1, "t");
        cs.registerOutParameter(2, OracleTypes.CURSOR);
        // 5 执行存储过程
        cs.execute();
        // 6 获取游标对象，封装到ResultSet结果集对象中
        ResultSet rs = (ResultSet) cs.getObject(2);
        // 7 遍历游标
        while (rs.next()) {
            System.out.println(rs.getInt("empno"));
            System.out.println(rs.getString("ename"));
            System.out.println(rs.getDouble("sal"));
            System.out.println(rs.getInt("deptno"));
            System.out.println("*******************");
        }
    }
}