首先CREATE OR REPLACE PROCEDURE SEEK_USER_PROC(szBUser IN OUT VARCHAR2(8), szNPswd OUT VARCHAR2(8)) 参数不需要定义长度,。 VARCHAR2,
解决方案 »
- 请教一个查询的问题
- set role 角色名;与alter user 用户 default role 角色名1;区别?
- 执行存储过程错误:ORA-00904
- 如何把record table里的数据放到object table里
- oracle 9.2.0.1 升级 9.2.0.7 后有问题
- Oracle Clob 字段字数在2000到4000之间内容时出错
- 急,在线等,关于Oracle Management Server的问题
- ***************如何监控共享内存**************
- OCI批量读取、插入BLOB数据问题
- Oracle ORA-00936: missing expression 错误
- 请部大虾,在存储过程中写算法时,如何利用表数据类型,而不用创建临时表?急...给高分
- 两个日期组合,然后减去一个时间(分钟),最后与系统时间比较,怎么写???
CREATE OR REPLACE FUNCTION ClassInfo (
/* Returns 'Full' if the class is completely full,
'Some Room' if the class is over 80% full,
'More Room' if the class is over 60% full,
'Lots of Room' if the class is less than 60% full, and
'Empty' if there are no students registered. */
p_Department classes.department%TYPE,
p_Course classes.course%TYPE)
RETURN VARCHAR2 IS v_CurrentStudents NUMBER;
v_MaxStudents NUMBER;
v_PercentFull NUMBER;
BEGIN
-- Get the current and maximum students for the requested
-- course.
SELECT current_students, max_students
INTO v_CurrentStudents, v_MaxStudents
FROM classes
WHERE department = p_Department
AND course = p_Course; -- Calculate the current percentage.
v_PercentFull := v_CurrentStudents / v_MaxStudents * 100; IF v_PercentFull = 100 THEN
RETURN 'Full';
ELSIF v_PercentFull > 80 THEN
RETURN 'Some Room';
ELSIF v_PercentFull > 60 THEN
RETURN 'More Room';
ELSIF v_PercentFull > 0 THEN
RETURN 'Lots of Room';
ELSE
RETURN 'Empty';
END IF;
END ClassInfo;
/------------------
存儲過程的例子:CREATE OR REPLACE PROCEDURE ModeTest (
p_InParameter IN NUMBER,
p_OutParameter OUT NUMBER,
p_InOutParameter IN OUT NUMBER) IS v_LocalVariable NUMBER;
BEGIN
/* Assign p_InParameter to v_LocalVariable. This is legal,
since we are reading from an IN parameter and not writing
to it. */
v_LocalVariable := p_InParameter; -- Legal /* Assign 7 to p_InParameter. This is ILLEGAL, since we
are writing to an IN parameter. */
p_InParameter := 7; -- Illegal /* Assign 7 to p_OutParameter. This is legal, since we
are writing to an OUT parameter and not reading from
it. */
p_OutParameter := 7; -- Legal /* Assign p_OutParameter to v_LocalVariable. This is
ILLEGAL, since we are reading from an OUT parameter. */
v_LocalVariable := p_outParameter; -- Illegal /* Assign p_InOutParameter to v_LocalVariable. This is legal,
since we are reading from an IN OUT parameter. */
v_LocalVariable := p_InOutParameter; -- Legal /* Assign 7 to p_InOutParameter. This is legal, since we
are writing to an IN OUT parameter. */
p_InOutParameter := 7; -- Legal
END ModeTest;
/
as
type mycursor is ref cursor;
end;
/
CREATE OR REPLACE PROCEDURE SEEK_USER_PROC(szBUser IN OUT VARCHAR2, szNPswd OUT VARCHAR2,p_rc out test_age.mycursor) AS
BEGIN
open p_rc for
SELECT BUSER, NPSWD into szBUser, szNPswd
FROM M_USER
WHERE BUSER=szBUser;
END SEEK_USER_PROC;
/
declare
v_szBUser VARCHAR2(8):='1';
v_szNPswd VARCHAR2(8);
v_rc test_age.mycursor;
begin
SEEK_USER_PROC(v_szBUser,v_szNPswd,v_rc);
loop
fetch v_rc into ...;
exit when v_rc%notfound;
dbms_output.put_line(....);
end loop;
end;
/
CREATE OR REPLACE PROCEDURE FIND_USER_PROC(szBUser IN OUT VARCHAR2, szNPswd OUT VARCHAR2) AS
BEGIN
SELECT BUSER, NPSWD into szBUser, szNPswd
FROM M_USER
WHERE BUSER=szBUser;
END FIND_USER_PROC;
显示:
プロシージャが作成されました。
(我是日文环境)
你好!
我执行下面这部分时,CREATE OR REPLACE PROCEDURE F_USER_PROC(szBUser IN OUT VARCHAR2, szNPswd OUT VARCHAR2,p_rc out TE_age.mycursor) AS
BEGIN
open p_rc for
SELECT BUSER, NPSWD into szBUser, szNPswd
FROM M_USER
WHERE BUSER=szBUser;
END F_USER_PROC;
提示:
警告: プロシージャが作成されましたが、コンパイル・エラーがあります。有错误啊!!
create package My_age
as
type mycursor is ref cursor;
end;
CREATE OR REPLACE PROCEDURE FD_USER_PROC(szBUser IN OUT VARCHAR2, szNPswd OUT VARCHAR2,p_rc out My_age.mycursor) AS
BEGIN
open p_rc for
SELECT BUSER, NPSWD into szBUser, szNPswd
FROM M_USER
WHERE BUSER=szBUser;
END FD_USER_PROC;
declare
v_szBUser VARCHAR2(8):='12345678';
v_szNPswd VARCHAR2(8);
v_rc My_age.mycursor;
begin
find_USER_PROC(v_szBUser,v_szNPswd,v_rc);
loop
fetch v_rc into v_szBUser,v_szNPswd;
exit when v_rc%notfound;
dbms_output.put_line('OK');
end loop;
end;提示:
警告: パッケージが作成されましたが、コンパイル・エラーがあります。
这样在VC程序里面能调用么?
麻烦大哥在给看看谢谢
你在pl/sql developer上执行通过了??
我是在ORACLE的SQL*PLUS WORKSHEET上验证的啊!!
create package My_age
as
type mycursor is ref cursor;
end;
/
CREATE OR REPLACE PROCEDURE FD_USER_PROC(szBUser IN OUT VARCHAR2, szNPswd OUT VARCHAR2,p_rc out My_age.mycursor) AS
BEGIN
open p_rc for
SELECT BUSER, NPSWD into szBUser, szNPswd
FROM M_USER
WHERE BUSER=szBUser;
END FD_USER_PROC;
/
declare
v_szBUser VARCHAR2(8):='12345678';
v_szNPswd VARCHAR2(8);
v_rc My_age.mycursor;
begin
find_USER_PROC(v_szBUser,v_szNPswd,v_rc);
loop
fetch v_rc into v_szBUser,v_szNPswd;
exit when v_rc%notfound;
dbms_output.put_line('OK');
end loop;
end;
/