OraclePL/SQL教程（知识点全覆盖）

最新推荐文章于 2021-04-03 01:28:18 发布

最新推荐文章于 2021-04-03 01:28:18 发布 · 146 阅读

文章标签：

#SQL #Oracle #JDBC #JSP #CSS

本文提供了一个Oracle PL/SQL的综合示例，包括表、序列、触发器、包、对象、存储过程及动态SQL的创建与使用，并通过Java代码调用存储过程，适用于初学者实践学习。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本文通过一个例子演示，基本涵盖了Oracle PL/SQL中的大部分知识点，但只是基本使用，敬请参考

创建表

create table test_table(
       t_index number(4) primary key not null,
       t_name varchar2(5),
       t_value number(4)
);

创建序列

create sequence test_sequence start with 1 increment by 1;

创建触发器

create or replace trigger test_trigger
before insert on test_table for each row
declare
 v_t_index test_table.t_index%type;
begin
 select test_sequence.nextval into v_t_index from dual;
 :NEW.t_index := v_t_index;
end;

创建包含输出游标（动态SQL）的包

create or replace package cursor_package is
       type out_cursor is ref cursor;
end cursor_package;

创建Oracle对象

create or replace type test_object as object(
       v_t_index number,
       v_t_name varchar2(5),
       v_t_value number
)

创建嵌套集合类型

create or replace type test_object_table as table of test_object

创建存储过程

--注释
create or replace procedure test_procedure(
       p_in1 in number, --传入参数 内置类型 （参数名称 参数类型）
       p_in2 in test_table.t_index%type, --传入参数 声明为表列对应的类型
       p_out out cursor_package.out_cursor --传出参数 声明为游标类型
) 
authid current_user --存储过程中使用role权限
as

 type t_name_table is table of test_table.t_name%type index by binary_integer; --声明表结构
 type t_value_table is table of test_table.t_value%type index by binary_integer;
 t_name_tables t_name_table; --声明联合数组
 t_value_tables t_value_table;
 v_sql varchar2(100);
 v_t_tables test_object_table := test_object_table(); --声明嵌套表
 v_t_index test_table.t_index%type;
 v_t_name test_table.t_name%type;
 v_t_value test_table.t_value%type;
 cursor c_test_table is --声明游标
  select t_index, t_name, t_value from test_table where t_index=p_in1 or t_index=p_in2 order by t_index;
 v_index number;
 
begin
 v_index := 1;
 for i in 1..10 loop
  t_name_tables(i) := 'row' || i;
  t_value_tables(i) := i;
 end loop;
 for i in 1..10 loop
  insert into test_table(t_name, t_value) values(t_name_tables(i), t_value_tables(i));
 end loop;
 v_sql := 'create table test_table1 as select * from test_table';
 execute immediate v_sql; --动态SQL（创建新表）
 open c_test_table; --打开游标
 loop
 exit when c_test_table%notfound;
 fetch c_test_table into v_t_index, v_t_name, v_t_value;
 v_t_tables.extend;
 v_t_tables(v_index) := test_object(v_t_index, v_t_name, v_t_value); --给嵌套表赋值
 v_index := v_index + 1;
 end loop;
 close c_test_table;
 open p_out for select * from Table(cast(v_t_tables as test_object_table)); --打开游标（传出参数）
end test_procedure;

以上代码在PLSQL Developer上运行即可（全部通过测试）

调用存储过程的Java代码（jsp实现）

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@page import="oracle.jdbc.OracleTypes"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%@ page import="java.sql.*" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'index.jsp' starting page</title>
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
  </head>
  
  <body>
    <%	///////////////////////////////////////////////////////////////////////
    	Connection con = null;
    	CallableStatement csmt = null;
    	ResultSet rs = null;
    	try {
    		Class.forName("oracle.jdbc.driver.OracleDriver");
			con = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521:LANG", "scott",
					"tiger");
			csmt = con.prepareCall("{call test_procedure(?, ?, ?)}");
			csmt.setInt(1,1);
			csmt.setInt(2,2);
			csmt.registerOutParameter(3, OracleTypes.CURSOR);
			csmt.execute();
			rs = (ResultSet)csmt.getObject(3);
			while(rs.next()) {
				out.println(rs.getString(1));
				out.println(rs.getString(2));
				out.println(rs.getString(3));
			}
    	} catch(Exception e) {
    		System.out.println(e.getMessage());
    	} finally {
    		
    		if(null != rs) {
    			
    			rs.close(); 
    		}
			if(null != csmt) {
    			
				csmt.close(); 
    		}
			if(null != con) {
	
				con.close(); 
			}
    	}
    	//////////////////////////////////////////////////////////////////////////
     %>
  </body>
</html>

代码中的

////////////////////////////

部分是调用存储过程的核心代码

本实例只要按照代码排版的顺序依次执行，即可通过测试，希望对阁下的Oracle PL/SQL学习有所帮助