存储过程创建语法:
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 在等待资源时超时
实例学习:
1. 建立一个存储过程 一个入参,一个出参
create or replace procedure test_xg_p5(a in number,x out varchar2) is
begin
if a=0 then
select count(1) into x from log;
else
select count(1) into x from id_table;
end if;
exception
when others then
ROLLBACK;
end test_xg_p5;
在sql 模式下运行 以上存储过程
调用方式 3种
a. 在 windows 客户端 使用PLSQL Developer 的 command模式 执行
b.在安装 oracle 的linux 客户机上使用 oracle 用户在控制台 使用sqlpuls 执行存贮过程
图 pd2
c. 使用java客户端
/*掉oracle存储过程 返回一个 数字*/
Connection conn=null;
CallableStatement proc = null;
conn= OracleJDBCTest.getConn();
proc = conn.prepareCall("{call test_xg_p5(?,?)}");
//传递0 返回1 传递1 返回106 两个表的记录数目不同
proc.setInt(1,1);
proc.registerOutParameter(2, Types.VARCHAR);
proc.execute();
System.out.println(proc.getString(2));
conn.close();
2 建立一个存储过程 返回一个结果集合
步骤:
第一步: 首先建立一个包,定义游标类型
CREATE OR REPLACE PACKAGE TYPES AS
TYPE BJ_CURSOR IS REF CURSOR;
END;
第二步: 建立 中间存贮过程过程 返回游标
CREATE OR REPLACE PROCEDURE BJ_TEST_PROC
(
V_TEMP OUT TYPES.BJ_CURSOR,
PID IN VARCHAR
)
AS
BEGIN
OPEN V_TEMP FOR select * from id_table t where t.quantity=PID;
END BJ_TEST_PROC;
调用方式2 种
a. 在sqlplus 控制台下 (貌似不能在 commond 模式下执行,不然var a refcursor会报错)
var a refcursor
call BJ_TEST_PROC(:a,1);
print a;
图 pb3
b. 使用java客户端
首先看下表名 为id_table的表结构
图pd4
调用代码
Connection conn=null;
CallableStatement proc = null;
conn= OracleJDBCTest.getConn();
proc = conn.prepareCall("{call BJ_TEST_PROC(?,?)}");
proc.setString(2,"1");
proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
proc.execute();
ResultSet rs = (ResultSet) proc.getObject(1);
while (rs.next()) {
System.out.println(rs.getString("TABLE_NAME"));
}
conn.close();
一个在eusp 项目中自己写的存储过程 以及 客户端调用代码
功能:给学校初始化 客户管理菜单
create or replace procedure pr_initCustManageMenu(flag in varchar2,sysOrg in varchar2,outPut out number) is
begin
-- 增加菜单 flag=0,删除菜单 flag=1
if flag='0' then
insert into xs_menu (menu_name,menu_type,menu_icon,SYS_ORGANIZATION) values('客户管理','系统类','customer.gif',sysOrg);
insert into xs_menu (upper_menu_id,menu_name,menu_type,tech_type,SYS_ORGANIZATION)
select menu_id,'教职工管理','系统类','tecOrgCustPanel',sysOrg
from XS_MENU where XS_MENU.MENU_NAME='客户管理' and SYS_ORGANIZATION=sysOrg;
insert into xs_menu (upper_menu_id,menu_name,menu_type,tech_type,SYS_ORGANIZATION)
select menu_id,'临时人员管理','系统类','worOrgCustPanel',sysOrg
from XS_MENU where XS_MENU.MENU_NAME='客户管理' and SYS_ORGANIZATION=sysOrg;
insert into xs_menu (upper_menu_id,menu_name,menu_type,tech_type,SYS_ORGANIZATION)
select menu_id,'学生管理','系统类','stuOrgCustPanel',sysOrg
from XS_MENU where XS_MENU.MENU_NAME='客户管理' and SYS_ORGANIZATION=sysOrg;
insert into xs_menu (upper_menu_id,menu_name,menu_type,tech_type,SYS_ORGANIZATION)
select menu_id,'营业组管理','系统类','shoOrgCustPanel',sysOrg
from XS_MENU where XS_MENU.MENU_NAME='客户管理' and SYS_ORGANIZATION=sysOrg;
insert into xs_menu (upper_menu_id,menu_name,menu_type,tech_type,SYS_ORGANIZATION)
select menu_id,'设备管理','系统类','xxTermPanel',sysOrg
from XS_MENU where XS_MENU.MENU_NAME='客户管理' and SYS_ORGANIZATION=sysOrg;
--- 添加权限
insert into XS_LEVEL_SYSMODULE(MENU_ID,ROLE_ID)
select m.menu_id,(select role_id from xs_role where name='ROLE_admin' and SYS_ORGANIZATION=sysOrg)
from xs_menu m where m.sys_organization=sysOrg start with
m.menu_id=(select m.menu_id from xs_menu m where m.menu_name='客户管理' and m.sys_organization=sysOrg)
connect by prior m.menu_id = m.upper_menu_id;
-- 添加成功返回
outPut:=0;
commit;
else
-- 删除权限
delete from XS_LEVEL_SYSMODULE ls where exists
(
select menu_id from (
(select m.menu_id from xs_menu m where m.sys_organization=sysOrg start with
m.menu_id=(select m.menu_id from xs_menu m where m.menu_name='客户管理' and m.sys_organization=sysOrg)
connect by prior m.menu_id = m.upper_menu_id)
) a where a.menu_id=ls.menu_id);
-- 删除菜单
delete from xs_menu ls where exists
(
select menu_id from (
(select m.menu_id from xs_menu m where m.sys_organization=sysOrg start with
m.menu_id=(select m.menu_id from xs_menu m where m.menu_name='客户管理' and m.sys_organization=sysOrg)
connect by prior m.menu_id = m.upper_menu_id)
) a where a.menu_id=ls.menu_id and ls.sys_organization=sysOrg);
--删除成功返回
outPut:=0;
commit;
end if;
exception
when others then
ROLLBACK;
--失败
outPut:=1;
end pr_initCustManageMenu;
客户端调用代码:使用了spring的 JdbcDaoSupport
@Override
public int initCustManageMenu(final String flag, final String sysOrg) {
String sql = "{call pr_initCustManageMenu(?,?,?)}";
Object obj=getJdbcTemplate().execute(sql,new CallableStatementCallback(){
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
cs.setString(1,flag);
cs.setString(2,sysOrg);
cs.registerOutParameter(3,Types.INTEGER);
cs.execute();
return cs.getInt(3);
}
});
return Integer.valueOf(obj.toString());
}