Oracle常用代码总结(7)

java开发中的总结 大家可以看看哦

rchar2(20);
user_age number;
begin
users_pro(1,user_name,user_age);
dbms_output.put_line(user_id||'姓名：'||user_name||' '||'年龄：'||user_age);
end;


---作业调度---
给用户的授权语句：
grant execute on dbms_job to scott;



---游标---
第一类游标：
弱类型游标
declare
type cursor_type is ref cursor;
cur cursor_type;
eguyuan guyuan%rowtype;
begin
open cur for
select * from guyuan;
loop
fetch cur into eguyuan;
exit when cur%notfound;
dbms_output.put_line(eguyuan.employee_id||eguyuan.fast_name);
end loop;
close cur;
end;

第二类游标：
--强类型游标--
declare
type record_type is record(employee_id number,fast_name varchar2(30));
type cursor_type is ref cursor return record_type;
rec record_type;
cur cursor_type;
begin
open cur for
select employee_id,fast_name from guyuan;
loop
fetch cur into rec;
exit when cur%notfound;
dbms_output.put_line('雇员号 '||rec.employee_id||' 员工名 '||rec.fast_name);
end loop;
close cur;
end;

---查找实例---
declare
cursor users_cur(did number) is
select * from guyuan where department_id = did;
employees users_cur%rowtype;
begin
for employees in users_cur(&雇员部门)
loop
dbms_output.put_line(employees.department_id||employees.fast_name||employees.job_id)
close users_cur;
end;

-----和jsp实现交互的功能的模块代码----
功能：实现从数据库中users表中查询数据并在jsp页面输出。
create or replace package user_pkg is
第一步：创建package
--用来装载一个user_cur的游标
type user_cursor is ref cursor;
end user_pkg;

第二步：
---创建一个users的存储过程
---插入的存储过程---
declare
uid number(10);
uname varchar2(20);
uage number(3);
begin
insert_pro(uid,uname,uage);
end insert_pro;
----查找的存储过程---
create or replace procedure select_pro(uid in number,uname out varchar2,uage out number) is
begin
select name,age into uname,uage from users where id = uid;
end select_pro;
----定义一个含有游标的存储过程---
create or replace procedure select_pro2(user_cur out user_er_cur,uid in number) is
begin
open user_cur for
select * from users where id = uid;
end select_pro2;

--实现类的方法----
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

public class OracleConnection {
public static void main(String[] args) {

DBConnection db = new DBConnection();
Connection conn = db.getConnection();
CallableStatement cst = null;
ResultSet rs = null;

try {
/**
*
cst = conn.prepareCall("{call insert_pro(?,?,?)}");
cst.setInt(1, 6);
cst.setString(2, &q
uot;lisi");
cst.setInt(3, 25);
int flag = cst.executeUpdate();
System.out.println(flag);
cst.registerOutParameter(1,oracle.jdbc.OracleT