Oracle存储过程学习

songze39

浏览: 243897 次
性别:
来自: 上海

最近访客更多访客>>

changan258

sdywcd

schoolgan

haven123

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

数据库

Oracle SQL JDBC Access Microsoft

存储过程创建语法：

create or replace procedure 存储过程名（param1 in type，param2 out type）

变量1 类型（值范围）;

变量2 类型（值范围）;

Begin

Select count(*) into 变量1 from 表A where列名=param1；

If (判断条件) then

Select 列名 into 变量2 from 表A where列名=param1；

Dbms_output。Put_line(‘打印信息’);

Elsif (判断条件) then

Dbms_output。Put_line(‘打印信息’);

Else

Raise 异常名（NO_DATA_FOUND）;

End if;

Exception

When others then

Rollback;

End;

注意事项：

1，存储过程参数不带取值范围，in表示传入，out表示输出

2，变量带取值范围，后面接分号

3，在判断语句前最好先用count（*）函数判断是否存在该条操作记录

4，用select 。。。into。。。给变量赋值

5，在代码中抛异常用 raise+异常名

以命名的异常

命名的系统异常产生原因

ACCESS_INTO_NULL 未定义对象

CASE_NOT_FOUND CASE 中若未包含相应的 WHEN ，并且没有设置

ELSE 时

COLLECTION_IS_NULL 集合元素未初始化

CURSER_ALREADY_OPEN 游标已经打开

DUP_VAL_ON_INDEX 唯一索引对应的列上有重复的值

INVALID_CURSOR 在不合法的游标上进行操作

INVALID_NUMBER 内嵌的 SQL 语句不能将字符转换为数字

NO_DATA_FOUND 使用 select into 未返回行，或应用索引表未初始化的

TOO_MANY_ROWS 执行 select into 时，结果集超过一行

ZERO_DIVIDE 除数为 0

SUBSCRIPT_BEYOND_COUNT 元素下标超过嵌套表或 VARRAY 的最大值

SUBSCRIPT_OUTSIDE_LIMIT 使用嵌套表或 VARRAY 时，将下标指定为负数

VALUE_ERROR 赋值时，变量长度不足以容纳实际数据

LOGIN_DENIED PL/SQL 应用程序连接到 oracle 数据库时，提供了不

正确的用户名或密码

NOT_LOGGED_ON PL/SQL 应用程序在没有连接 oralce 数据库的情况下

访问数据

PROGRAM_ERROR PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL

系统包

ROWTYPE_MISMATCH 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容

SELF_IS_NULL 使用对象类型时，在 null 对象上调用对象方法

STORAGE_ERROR 运行 PL/SQL 时，超出内存空间

SYS_INVALID_ID 无效的 ROWID 字符串

TIMEOUT_ON_RESOURCE Oracle 在等待资源时超时

分享到：

sql server 2000全文目录安装 | Windows XP系统总命令集

2008-12-07 21:14
浏览 4162
评论(21)
论坛回复 / 浏览 (19 / 72870)
分类:非技术
查看更多

21 楼 songze39 2009-11-04

Mrpublic 写道

shiyiwan 写道

用select count(1)比较好，如果只是单纯记录记录条数。

你好可不可以說具體一點呀一個存儲過程只做查詢然后返回記錄條數是在ejb3.0 +orcle的條件下

n久没用过ejb了,在什么条件不管

转:

如果你的数据表没有主键，那么count(1)比count(*)快
如果有主键的话，那主键（联合主键）作为count的条件也比count(*)要快
如果你的表只有一个字段的话那count(*)就是最快的啦
count(*) count(1) 两者比较。主要还是要count(1)所相对应的数据字段。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(*),自动会优化指定到那一个字段。所以没必要去count(?)，用count(*),sql会帮你完成优化的

20 楼 mida 2009-10-16

这个我终于明白了，谢谢。

19 楼 Mrpublic 2009-10-15

shiyiwan 写道

用select count(1)比较好，如果只是单纯记录记录条数。

你好可不可以說具體一點呀一個存儲過程只做查詢然后返回記錄條數是在ejb3.0 +orcle的條件下

18 楼 Mrpublic 2009-10-15

<div class="quote_title">songze39 写道</div>
<div class="quote_div">
存储过程创建语法：
 create or replace procedure 存储过程名（param1 in type，param2 out type） 
as 
变量1 类型（值范围）;
变量2 类型（值范围）;
Begin
 Select count(*) into 变量1 from 表A where列名=param1；
 If (判断条件) then
 Select 列名 into 变量2 from 表A where列名=param1；
 Dbms_output。Put_line(‘打印信息’);
 Elsif (判断条件) then
 Dbms_output。Put_line(‘打印信息’);
 Else
 Raise 异常名（NO_DATA_FOUND）;
 End if;
Exception
 When others then
 Rollback;
End;
 
注意事项：
1，存储过程参数不带取值范围，in表示传入，out表示输出
2，变量带取值范围，后面接分号
3，在判断语句前最好先用count（*）函数判断是否存在该条操作记录
4，用select 。。。into。。。给变量赋值
5，在代码中抛异常用 raise+异常名
 
 
以命名的异常
命名的系统异常 产生原因 
ACCESS_INTO_NULL 未定义对象 
CASE_NOT_FOUND CASE 中若未包含相应的 WHEN ，并且没有设置 
ELSE 时 
COLLECTION_IS_NULL 集合元素未初始化 
CURSER_ALREADY_OPEN 游标已经打开 
DUP_VAL_ON_INDEX 唯一索引对应的列上有重复的值 
INVALID_CURSOR 在不合法的游标上进行操作 
INVALID_NUMBER 内嵌的 SQL 语句不能将字符转换为数字 
NO_DATA_FOUND 使用 select into 未返回行，或应用索引表未初始化的 
 
TOO_MANY_ROWS 执行 select into 时，结果集超过一行 
ZERO_DIVIDE 除数为 0 
SUBSCRIPT_BEYOND_COUNT 元素下标超过嵌套表或 VARRAY 的最大值 
SUBSCRIPT_OUTSIDE_LIMIT 使用嵌套表或 VARRAY 时，将下标指定为负数 
VALUE_ERROR 赋值时，变量长度不足以容纳实际数据 
LOGIN_DENIED PL/SQL 应用程序连接到 oracle 数据库时，提供了不
正确的用户名或密码 
NOT_LOGGED_ON PL/SQL 应用程序在没有连接 oralce 数据库的情况下
访问数据 
PROGRAM_ERROR PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 
系统包 
ROWTYPE_MISMATCH 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容 
SELF_IS_NULL 使用对象类型时，在 null 对象上调用对象方法 
STORAGE_ERROR 运行 PL/SQL 时，超出内存空间 
SYS_INVALID_ID 无效的 ROWID 字符串 
TIMEOUT_ON_RESOURCE Oracle 在等待资源时超时 
</div>
請問。我寫一個存儲過程要想把select的出的結果數據返回呀？如查出20條就返回20 ；我是ejb3.0 是不是它不能用output ?

17 楼 rentianchou 2009-07-21

非常不错，正在学习中

16 楼 aofeng 2009-07-03

简单，但基础的东东都讲了，并且清晰！ GC一下！

15 楼 shiyiwan 2009-06-21

假设已经在COMMON schema下创建了一个名为P_MM_NSP_UMBRELLA_PROC的package,它里面有几个公有procedure,SW_NS_CREATE_TABLE和SW_NS_CREATE_INDEX

那么我们可以在代码中如此调用
   procedure SW_NS_CRT_NS_CTK_TBL(
      v_parm_ns_ctk_tbl in compl_parm.parm_val%type
    , v_parm_run_date    in compl_parm.parm_val%type
    , v_con_def_seq_nbr in p_cap_create_object_util.ddl_stmt_typ
   )
   --************************************************************
   --* Procedure:
   --*   SW_NS_CRT_NS_CTK_TBL
   --*
   --* Purpose:
   --*
   --*
   --* Input:
   --*   v_parm_ns_ctk_tbl -
   --*   v_parm_run_date -
   --*   v_con_def_seq_nbr -
   --*
   --* Output:
   --*   Temp table NS_PCI_CTK_TBL is created
   --************************************************************
   is
      v_parm_ns_ctk_idx      compl_parm.parm_val%type
         := COMMON.P_MM_NSP_UMBRELLA_PROC.FR_GET_PARMVAL( c_app_id
                                                        , 'NS_PCI_CTK_IDX'
                                                        );
      v_sql                   p_cap_create_object_util.ddl_stmt_typ
         := null;

   begin

      v_sql := ' select ctk.store_nbr,
                        ctk.cust_fam_nbr
                 from   contact_tracking_keys ctk
                 where 1 = 1
                       '||v_con_def_seq_nbr||;

      COMMON.P_MM_NSP_UMBRELLA_PROC.SW_NS_CREATE_TABLE( c_app_id
                        , v_parm_ns_ctk_tbl
                        , v_sql);

      COMMON.P_MM_NSP_UMBRELLA_PROC.SW_NS_CREATE_INDEX( c_app_id
                        , v_parm_ns_ctk_tbl
                        , v_parm_ns_ctk_idx
                        , '(store_nbr,cust_fam_nbr)');

      dbms_output.put_line('EX -- '
                           ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS')
                           ||' -- Proc SW_NS_CRT_NS_CTK_TBL completed'
                          );

   exception
   when others
   then
      dbms_output.put_line('EX -- '
                           ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS')
                           ||' -- Proc SW_NS_CRT_NS_CTK_TBL failed'
                          );
      dbms_output.put_line(substr(sqlerrm,1,254));

      raise;
   end SW_NS_CRT_NS_CTK_TBL;

因为这个procedure属于某个package，所以前面没有加create or replace

minma_2009 写道

学习了。下次可以写个在程序中调用存储过程的具体实例吗？

14 楼 shiyiwan 2009-06-21

基本语法确实是如你所言

但是如果要创建一个procedure或者function或者package，让它们编译后不止这次运行一次，以后需要用到的时候依然能够从Oracle中调用，就需要create or replace了。
这样编译的时候，它会先检查是否同名procedure/function/package是否存在，是则用新代码编译替换掉旧的，否则将其编译后存储在对应的schema下。

prince1209 写道

pl/sql 的基本语法不是这样吗？
DECLARE
   声明部分；
BEGIN
   执行部分；
EXCEPTION
   异常处理部分；
END;

怎么开始是 create or replace?/??   why? 不明白？新手の

13 楼 minma_2009 2009-05-25

学习了。下次可以写个在程序中调用存储过程的具体实例吗？

12 楼 511543417 2009-05-02

哎呀，看了有点头晕，这么难。这是哪的的高手写的呢？先记下了，慢慢研究一下子。

11 楼 prince1209 2009-04-26

10 楼 shiyiwan 2009-04-03

用select count(1)比较好，如果只是单纯记录记录条数。

9 楼 dwjun236 2009-03-15

看样子挺复杂的啊

8 楼 xiaogang139452 2009-03-15

7 楼 longgangbai 2009-03-10

6 楼 zhanght327 2009-02-13

呵呵存储过程还是不太明白。。。。谢谢回去慢慢研究

5 楼 songze39 2009-01-04

yingxiongwudi 写道

请教如何用JDBC给存储过程传递数组类型的参数

import java.sql.*;

public class DBDeclareTest{
private Connection con = null;// 数据库连接
private java.sql.CallableStatement cs = null;//执行存储过程的接口

private int key;
private String name;

//在构造函数中构造连接
public DBDeclareTest(){
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1433;databasename=pubs","sa","");
System.out.println ("con ok");
 }
 catch (Exception ex) {
 ex.printStackTrace();
 }
}

//调用无返回值的存储过程
public void getOK1(){
try {
String sql="{call pro_mypro1(?,?)}";
cs = con.prepareCall(sql);
cs.setInt(1,key);
cs.setString(2,name);
cs.executeUpdate();
System.out.println ("执行成功，可以休息了。。。。");
cs.close();
con.close();
 }
 catch (Exception ex) {
 ex.printStackTrace();
 }

}
//调用有返回值的存储过程
public void getOK2(){
try {
String sql = "{call pro_mypro2(?,?)}";
cs = con.prepareCall(sql);
//设置output外部传参
cs.registerOutParameter(1,java.sql.Types.BIT);
cs.setString(2,name);
cs.execute();
//获得output外部传参的值
int numOut = cs.getInt(1);
if(numOut ==1){
System.out.println ("数据表存在");
}else{
System.out.println ("数据表不存在！！！");
}
cs.close();
con.close();
 }
 catch (Exception ex) {
 ex.printStackTrace();
 }
}

//返回结果集的存储过程
public void getOK3(){
try {
String sql = "{call pro_mypro3(?,?)}";
cs = con.prepareCall(sql);
//设置output外部传参
cs.registerOutParameter(1,java.sql.Types.BIT);
cs.setString(2,name);
boolean flag = cs.execute();
System.out.println (flag);
if(flag){
System.out.println ("OKOKOKO");
ResultSet rs = cs.getResultSet();
ResultSetMetaData rsmd =rs.getMetaData();
 int numberOfColumns = rsmd.getColumnCount();
while(rs.next()){
for(int i=0;i<numberOfColumns;i++)
{
System.out.println (rsmd.getColumnName(i)+" ");
System.out.println (rs.getObject(i)+" ");
}
}
}else
{
System.out.println ("表名不存在："+cs.getBoolean(1));
}
cs.close();
con.close();
 }
 catch (Exception ex) {
 ex.printStackTrace();
 }
}

public void setKey(int k){
this.key = k;
}
public int getKey(){
return this.key;
}
public void setName(String n){
this.name = n;
}
public String getName(){
return this.name;
}

}

4 楼 hk.bfeng 2008-12-31

yingxiongwudi 写道

请教如何用JDBC给存储过程传递数组类型的参数

以前的笔记......

A . 嵌套表

1. 声明数组类型
 create or replace type tab_array is table of varchar2(38);暂时不要在包中声明该类型

2. 创建存储过程
 -- 该例子存储过程是在包中创建的,包名 arraydemo
 procedure testArray(resNumber in tab_array,procResult out tab_array) is
 begin
 procResult := new tab_array();
 for i in 1..resNumber.Count loop
 procResult.EXTEND;
 procResult(i) := resNumber(i) || 'lucifer' || i;
 end loop;
 end;

3. Java调用代码
 //必须使用Oracle的连接和Statement,使用了连接池的必须通过一些方法获取原始的连接
 OracleConnection conn = null;
 OracleCallableStatement stmt = null;
 String[] param = { "1001", "1002", "1006" };
 stmt =(转换类型) conn.prepareCall("{call arraydemo.testArray(?,?)}");
 // 类型名必须大写
 ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("TAB_ARRAY", conn);
 stmt.setARRAY(1, new ARRAY(descriptor,conn,param));
 stmt.registerOutParameter(2, OracleTypes.ARRAY, "TAB_ARRAY");
 stmt.execute();

 ARRAY array = stmt.getARRAY(2);
 Datum[] data = array.getOracleArray();
 for (int i = 0; i < data.length; i++) {
 System.out.println(i + " : " + new String(data.shareBytes()));
 }
4 . 注意的问题及尚未解决的问题
 抛出:Non supported character set: oracle-character-set-852 异常---解决:添加 nls_charset12.jar 到classpath,该包在oracle/ora92/jdbc/lib目录下
 待解决问题:
 a) 如何调用在包声明的自定义类型
 b) 比较不同声明类型的优缺点,及使用场合
 嵌套表其它应用:http://zhouwf0726.itpub.net/post/9689/212253

B . 索引表
C . 内置数组
D . 游标方式

3 楼 yingxiongwudi 2008-12-31

请教如何用JDBC给存储过程传递数组类型的参数

2 楼 Sunshine168 2008-12-31

可以， ~~~~

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论