create or replace procedure aa(address varchar2) is begin SELECT ID,NAME,SEX,AGE,ADDRESS FROM USERINFO where address = :address; end aa;
create or replace procedure pd_test (address varchar2) as str_sql varchar2(1000); begin str_sql:='select ID,NAME,SEX,AGE,ADDRESS from USERINFO where address ='||address||''; execute immediate str_sql; end; 注意其中你的参数类型要与表中的设计一致
create table userinfo( id number(18,0), name varchar2(10), sex char(4), age number(18,0), address varchar2(50) );INSERT INTO userinfo VALUES(1,'luoyoumou','男',24,'北京朝阳');CREATE OR REPLACE PACKAGE userinfo_pkg AS TYPE myrctype IS REF CURSOR; PROCEDURE userinfo_proc(i_address IN VARCHAR2, p_rc OUT myrctype); END userinfo_pkg; /CREATE OR REPLACE PACKAGE BODY userinfo_pkg AS PROCEDURE userinfo_proc(i_address IN VARCHAR2, p_rc OUT myrctype) IS sqlstr VARCHAR2(1000); BEGIN sqlstr := 'SELECT ID, NAME, SEX, AGE, ADDRESS FROM USERINFO WHERE address = :i_address'; open p_rc for sqlstr using i_address; END userinfo_proc; END userinfo_pkg; /
各位大侠 小弟刚接触Oracle 对里面的语法不是很明白 能否写上注释 真心的谢谢了.......
create or replace procedure aa(vaddress varchar2, cur out sys_refcursor) is begin open cur for SELECT ID,NAME,SEX,AGE,ADDRESS FROM USERINFO where address = vaddress; end aa;
create table userinfo( id number(18,0), name varchar2(10), sex char(4), age number(18,0), address varchar2(50) );INSERT INTO userinfo VALUES(1,'luoyoumou','男',24,'北京朝阳'); INSERT INTO userinfo VALUES(1,'zhangtao','男',35,'上海');CREATE OR REPLACE PACKAGE userinfo_pkg AS TYPE myrctype IS REF CURSOR; PROCEDURE userinfo_proc(i_address IN VARCHAR2, p_rc OUT myrctype); END userinfo_pkg; /CREATE OR REPLACE PACKAGE BODY userinfo_pkg AS PROCEDURE userinfo_proc(i_address IN VARCHAR2, p_rc OUT myrctype) IS sqlstr VARCHAR2(1000); BEGIN sqlstr := 'SELECT ID, NAME, SEX, AGE, ADDRESS FROM USERINFO WHERE address = :i_address'; open p_rc for sqlstr using i_address; END userinfo_proc; END userinfo_pkg; /-------------------------- 测试: ------------------------------------- set serveroutput on; var p_rc refcursor; exec userinfo_pkg.userinfo_proc('上海',:p_rc); print p_rc;
create or replace procedure pd_test (address varchar2) as str_sql varchar2(1000); begin str_sql:='select ID,NAME,SEX,AGE,ADDRESS from USERINFO where address ='||address||''; execute immediate str_sql; end;1,2楼的都错误! 应该把查询出来的结果集用游标或集合类型存储起来。
CREATE OR REPLACE PROCEDURE userinfo_proc(i_address VARCHAR2, o_cur OUT SYS_REFCURSOR) IS sqlstr VARCHAR2(200); BEGIN sqlstr := 'SELECT Id, Name, Sex, Age, Address FROM userinfo WHERE Address = :i_address'; OPEN o_cur FOR sqlstr USING i_address; END; /
begin
SELECT ID,NAME,SEX,AGE,ADDRESS FROM USERINFO where address = :address;
end aa;
(address varchar2)
as
str_sql varchar2(1000);
begin
str_sql:='select ID,NAME,SEX,AGE,ADDRESS from USERINFO where address ='||address||'';
execute immediate str_sql;
end;
注意其中你的参数类型要与表中的设计一致
create table userinfo(
id number(18,0),
name varchar2(10),
sex char(4),
age number(18,0),
address varchar2(50)
);INSERT INTO userinfo VALUES(1,'luoyoumou','男',24,'北京朝阳');CREATE OR REPLACE PACKAGE userinfo_pkg
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE userinfo_proc(i_address IN VARCHAR2, p_rc OUT myrctype);
END userinfo_pkg;
/CREATE OR REPLACE PACKAGE BODY userinfo_pkg
AS
PROCEDURE userinfo_proc(i_address IN VARCHAR2, p_rc OUT myrctype)
IS
sqlstr VARCHAR2(1000);
BEGIN
sqlstr := 'SELECT ID, NAME, SEX, AGE, ADDRESS FROM USERINFO WHERE address = :i_address';
open p_rc for sqlstr using i_address;
END userinfo_proc;
END userinfo_pkg;
/
create or replace procedure aa(vaddress varchar2, cur out sys_refcursor) is
begin
open cur for
SELECT ID,NAME,SEX,AGE,ADDRESS FROM USERINFO where address = vaddress;
end aa;
id number(18,0),
name varchar2(10),
sex char(4),
age number(18,0),
address varchar2(50)
);INSERT INTO userinfo VALUES(1,'luoyoumou','男',24,'北京朝阳');
INSERT INTO userinfo VALUES(1,'zhangtao','男',35,'上海');CREATE OR REPLACE PACKAGE userinfo_pkg
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE userinfo_proc(i_address IN VARCHAR2, p_rc OUT myrctype);
END userinfo_pkg;
/CREATE OR REPLACE PACKAGE BODY userinfo_pkg
AS
PROCEDURE userinfo_proc(i_address IN VARCHAR2, p_rc OUT myrctype)
IS
sqlstr VARCHAR2(1000);
BEGIN
sqlstr := 'SELECT ID, NAME, SEX, AGE, ADDRESS FROM USERINFO WHERE address = :i_address';
open p_rc for sqlstr using i_address;
END userinfo_proc;
END userinfo_pkg;
/-------------------------- 测试: -------------------------------------
set serveroutput on;
var p_rc refcursor;
exec userinfo_pkg.userinfo_proc('上海',:p_rc);
print p_rc;
(address varchar2)
as
str_sql varchar2(1000);
begin
str_sql:='select ID,NAME,SEX,AGE,ADDRESS from USERINFO where address ='||address||'';
execute immediate str_sql;
end;1,2楼的都错误!
应该把查询出来的结果集用游标或集合类型存储起来。
IS
sqlstr VARCHAR2(200);
BEGIN
sqlstr := 'SELECT Id, Name, Sex, Age, Address FROM userinfo WHERE Address = :i_address';
OPEN o_cur FOR sqlstr USING i_address;
END;
/
CREATE OR REPLACE PROCEDURE userinfo_proc(i_address VARCHAR2, o_cur OUT SYS_REFCURSOR)
IS
sqlstr VARCHAR2(200); -- 定义变量,用以存放SQL语句
BEGIN
sqlstr := 'SELECT Id, Name, Sex, Age, Address FROM userinfo WHERE Address = :i_address'; -- 给SQL变量赋值,其中 :i_address 是绑定变量,以提高执行效率!
OPEN o_cur FOR sqlstr USING i_address; -- 给游标变量赋值
END;
/-- 解释不太正确的地方请原谅!