//创建一个提取订单信息的存储过程
create or replace procedure proc_yanglei_view_order(
p_name in yanglei_order_user_pro.uname%type,
p_cursor out PKG_CURSOR.my_cursor
)is
begin
open p_cursor for select * from yanglei_order_user_pro where uname= p_name;
end proc_yanglei_view_order ;
记得 p_cursor(自定义的游标) 这段代码是引用了游标和包 但是忘了怎么定义它的了 哪位大侠能帮我还原游标的定义啊
create or replace procedure proc_yanglei_view_order(
p_name in yanglei_order_user_pro.uname%type,
p_cursor out PKG_CURSOR.my_cursor
)is
begin
open p_cursor for select * from yanglei_order_user_pro where uname= p_name;
end proc_yanglei_view_order ;
记得 p_cursor(自定义的游标) 这段代码是引用了游标和包 但是忘了怎么定义它的了 哪位大侠能帮我还原游标的定义啊
这个是你的自定义游标类型,你去你的PKG_CURSOR包里看看,里面有你的自定义游标my_cursor
的定义
--定义TYPE as Strong REF CURSOR
create or replace package pkg_cursor is
type my_cursor is ref cursor;
end pkg_cursor;
//创建一个提取订单信息的存储过程
create or replace procedure proc_yanglei_view_order(
p_name in yanglei_order_user_pro.uname%type,
p_cursor out PKG_CURSOR.my_cursor
)is
begin
open p_cursor for select * from yanglei_order_user_pro where uname= p_name;
end proc_yanglei_view_order ;
请问:这是一套完整的吗?
public static void main(String[] args){
try {
Connection conn = BaseJDBC.getJDBCConnection("ORACLE");
CallableStatement cs = (CallableStatement) conn.prepareStatement("{proc_yanglei_view_order(?,?)}");
cs.setString(1, "1007");
cs.registerOutParameter(2,Types.ARRAY);
ResultSet rs = cs.executeQuery();
while(rs.next()){
System.out.println(rs.getString("pk_corp"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
这段代码错在哪呢?
如#4楼所说的我在数据库中建立了存储过程,但是测试代码却忘了怎么写 请问四楼高手:
public static void main(String[] args){
try {
Connection conn = BaseJDBC.getJDBCConnection("ORACLE");
CallableStatement cs = (CallableStatement) conn.prepareStatement("{proc_yanglei_view_order(?,?)}");
cs.setString(1, "1007");
cs.registerOutParameter(2,Types.ARRAY);
ResultSet rs = cs.executeQuery();
while(rs.next()){
System.out.println(rs.getString("pk_corp"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
这段代码错在哪呢?
set long 10000
select dbms_metadata.get_ddl('PACKAGE','PKG_CURSOR','SYSTEM') FROM DUAL;--'PACKAGE',对象类型
--'PKG_CURSOR',对象名
--'SYSTEM' 哪个用户创建的
--记得要大写这里面
DECLARE
CURSOR c_student IS
SELECT first_name, last_name, student_id
FROM student
WHERE last_name LIKE 'J%';
CURSOR c_coruse (i_student_id IN student.student_id%TYPE) IS
SELECT c.description, s.section_id sec_id
FROM course c, section s, enrollment e
WHERE e.student_id = i_studnet_id
AND c.course_no = s.course_no
AND s.section_id = e.section_id;
COURSOR c_grade(i_section_id IN section.section_id%TYPE, i_student_id IN student.student_id%TYPE) IS
SELECT gt.description grd_desc, TO_CHAR(AVG(g.numeric_grade), '999.99') num_grd
FROM enrollment e, grade g, grade_type gt
WHERE e.section_id = i_section_id
AND e.student_id = g.student_id
AND e.student_id = i_student_id
AND e.section_id = g.section_id
AND g.grade_type_code = gt.grade_type_code
GROUP BY gt.description;
BEGIN
FOR r_student IN c_student
LOOP
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(r_student.first_name||' '||r_student.last_name);
FOR r_course IN c_course(r_student.student_id)
LOOP
DBMS_OUTPUT.PUT_LINE('Grades for course: '||r_course.description);
FOR r_grade IN c_grade(r_course.sec_id, r_student.student_id)
LOOP
DBMS_OUTPUT.PUT_LINE(r_grade.num_grd||' '||r_grade.grd_desc);
END LOOP;
END LOOP;
END LOOP;
END;
/
CURSOR c_stud_zip IS
SELECT s.student_id, z.city
FROM student s, zipcode z
WHERE z.city = 'Brooklyn'
AND s.zip = z.zip
FRO UPDATE OF phone;
BEGIN
FOR r_stud_zip IN c_stud_zip
LOOP
DBMS_OUTPUT.PUT_LINE(r_stud_zip.student_id);
UPDATE student
SET phone = '718'||SUBSTR(phone,4)
WHERE CURRENT OF c_stud_zip;
END LOOP;
END;
/
CURSOR c_zip (p_state IN zipcode.state%TYPE) IS
SELECT zip, city, state
FROM zipcode
WHERE state = p_state
BEGIN
FOR r_zip IN c_zip('NJ')
LOOP
DBMS_OUTPUT.PUT_LINE(r_zip.city||' '||r_zip.zip');
END LOOP;
END;
/