create procedure getAll
as
begin
select * from scott.emp;
end;begin
getAll;
end;
as
begin
select * from scott.emp;
end;begin
getAll;
end;
解决方案 »
- 初学xml查询,请大侠们介绍点资料.
- 通过COM+,DEXPRESS连接oracle,连接之后oracle,session里的username为空
- Oracle导入sql文件时出错。SP2-0310: 无法打开文件 "D:\My.sql"
- 数据库主键问题
- 紧急求救,有关oracle事务最大并发处理问题?
- 【求助】oracle接口表、临时表、中间表?
- ora-00922:缺少或无效选项
- 请问可以在WINDOWS平台上管理UNIX上的ORACLE数据库服务端吗?
- 谁有ORACLE官方文档,请共享一下,谢谢
- 【急】写oracle数据库中clob字段的错误,错误代码:OraOLEDB (0x80004005)
- 关于下列问题
- 创建存储过程出错。。。。。
as
begin
select * from scott.emp;
end;存储过程或者函数的SELECT语句得到的结果必须要用变量接收,你写的有问题
应该这样写
create procedure getAll
as
v_name varchar2(30);
begin
select ename into v_name from scott.emp;
end;
as
begin
open p_cur for select * from scott.emp;
end;SQL> var results REFCURSOR;
SQL> exec getAll(:results);
SQL> print results;
-- alter table mobileFrends_tmp_proc add frendname VARCHAR2(100);create or replace package pkg_mobileFrends_op
as
type myrctype is ref cursor; --定义游标用以 查询 返回好友手机号
procedure mobileFrends_in_proc(v_mobile in varchar2,v_frendsMobile in clob,v_frendsName clob); --添加 好友手机号
procedure mobileFrends_de_proc(v_mobile in varchar2,v_frendsMobile in clob); --删除 部分好友手机号
procedure mobileFrends_deall_proc(v_mobile in varchar2); --删除 所有好友手机号
procedure mobileFrends_se_proc(v_mobile in varchar2,p_rc out myrctype); --查询 好友手机号
procedure mobileFrends_seb_proc(v_fromMobile in varchar2, v_toMobile in varchar2, p_rc out myrctype); --查询 好友手机号(按 手机号 区间)
procedure mobileFrends_se2_proc(v_mobile in varchar2,p_rc out myrctype); --查询 好友手机号,包括其好友状态
procedure mobileFrends_se2b_proc(v_fromMobile in varchar2, v_toMobile in varchar2, p_rc out myrctype); --查询 好友手机号,包括其好友状态(按 手机号 区间)
procedure mobileFrends_se3_proc(v_frendmobile in varchar2,p_rc out myrctype); --根据好友手机号 查询 宿主手机号(查看有哪些人将这个手机号加为了好友)
procedure mobileFrends_se3b_proc(v_fromFrendMobile in varchar2, v_toFrendMobile in varchar2, p_rc out myrctype); --根据好友手机号 查询 宿主手机号(查看有哪些人将这个手机号加为了好友)(按 手机号 区间)
end pkg_mobileFrends_op;
/------------------------------------------------create or replace package body pkg_mobileFrends_op
as
--添加 好友手机号
procedure mobileFrends_in_proc(v_mobile varchar2,v_frendsMobile clob,v_frendsName clob)
is
v_frendsMobile_str clob;
v_frendsName_str clob;
v_frendMobile varchar2(20);
v_frendName varchar2(100);
v_dot_var1 NUMBER(18,0);
v_dot_var2 NUMBER(18,0);
begin
v_frendsMobile_str := v_frendsMobile||',';
v_frendsName_str := v_frendsName||',';
while length(v_frendsMobile_str)>1 loop
begin
v_dot_var1 := instr(v_frendsMobile_str,',',1,1);
v_dot_var2 := instr(v_frendsName_str,',',1,1);
v_frendMobile := substr(v_frendsMobile_str,1,v_dot_var1-1);
v_frendName := substr(v_frendsName_str,1,v_dot_var2-1);
v_frendsMobile_str := substr(v_frendsMobile_str,v_dot_var1+1,length(v_frendsMobile_str)-v_dot_var1);
v_frendsName_str := substr(v_frendsName_str,v_dot_var2+1,length(v_frendsName_str)-v_dot_var2);
insert into mobileFrends_tmp_proc(mobile,frendMobile,frendName) values(v_mobile,v_frendMobile,v_frendName);
end;
end loop;
insert into mobileFrends(mobile,frendMobile,frendName)
select t.mobile, t.frendMobile, t.frendName
from mobileFrends_tmp_proc t
where t.frendMobile is not null
and not exists ( select 1
from mobileFrends m
where m.mobile=t.mobile
and m.frendMobile=t.frendMobile );
commit;
end mobileFrends_in_proc; --删除 部分好友手机号
procedure mobileFrends_de_proc(v_mobile varchar2,v_frendsMobile clob)
is
v_frendsMobile_str clob;
v_frendMObile varchar2(20);
v_dot_var NUMBER(18,0);
begin
v_frendsMobile_str := v_frendsMobile||',';
while length(v_frendsMobile_str)>1 loop
begin
v_dot_var := instr(v_frendsMobile_str,',',1,1);
v_frendMobile := substr(v_frendsMobile_str,1,v_dot_var-1);
v_frendsMobile_str := substr(v_frendsMobile_str,v_dot_var+1,length(v_frendsMobile_str)-v_dot_var);
insert into mobileFrends_tmp_proc(mobile,frendMobile) values(v_mobile,v_frendMobile);
end;
end loop;
delete from mobileFrends
where mobile=v_mobile
and frendMobile in ( select t.frendMobile from mobileFrends_tmp_proc t );
commit;
end mobileFrends_de_proc; --删除 所有好友手机号
procedure mobileFrends_deall_proc(v_mobile varchar2)
is
sqlstr VARCHAR2(400);
begin
if v_mobile is not null then
sqlstr := 'delete from mobileFrends where mobile = :v_mobile';
execute immediate sqlstr using v_mobile;
commit;
end if;
end mobileFrends_deall_proc; --查询 好友手机号
procedure mobileFrends_se_proc(v_mobile in varchar2,p_rc out myrctype)
is
sqlstr VARCHAR2(400);
begin
sqlstr := 'SELECT frendmobile FROM mobileFrends WHERE mobile = :v_mobile';
OPEN p_rc FOR sqlstr USING v_mobile;
end mobileFrends_se_proc;
--查询 好友手机号2(按 手机号区间)
procedure mobileFrends_seb_proc(v_fromMobile in varchar2, v_toMobile in varchar2, p_rc out myrctype)
is
sqlstr VARCHAR2(400);
begin
sqlstr := 'SELECT frendmobile FROM mobileFrends WHERE mobile >= :v_fromMobile AND mobile <= :v_toMobile';
OPEN p_rc FOR sqlstr USING v_fromMobile, v_toMobile;
end mobileFrends_seb_proc; --查询 好友手机号,包括其好友状态
procedure mobileFrends_se2_proc(v_mobile in varchar2,p_rc out myrctype)
is
sqlstr VARCHAR2(400);
begin
sqlstr := 'SELECT m.mobile,m.frendmobile,nvl(u.state,-1) as u_state,u.sign,u.md5 FROM mobileFrends m left join u_state u on m.frendmobile=u.mobile WHERE m.mobile = :v_mobile';
OPEN p_rc FOR sqlstr USING v_mobile;
end mobileFrends_se2_proc;
--查询 好友手机号,包括其好友状态2(按 手机号区间)
procedure mobileFrends_se2b_proc(v_fromMobile in varchar2, v_toMobile in varchar2, p_rc out myrctype)
is
sqlstr VARCHAR2(400);
begin
sqlstr := 'SELECT m.mobile,m.frendmobile,nvl(u.state,-1) as u_state,u.sign,u.md5,u.CUSTOMIZE_STATUS FROM mobileFrends m left join u_state u on m.frendmobile=u.mobile WHERE m.mobile >= :v_fromMobile AND m.mobile <= :v_toMobile';
OPEN p_rc FOR sqlstr USING v_fromMobile, v_toMobile;
end mobileFrends_se2b_proc; --根据好友手机号 查询 宿主手机号(查看有哪些人将这个手机号加为了好友)
procedure mobileFrends_se3_proc(v_frendmobile in varchar2,p_rc out myrctype)
is
sqlstr VARCHAR2(400);
begin
sqlstr := 'SELECT m.mobile,m.frendmobile FROM mobileFrends m WHERE m.frendmobile = :v_frendmobile';
OPEN p_rc FOR sqlstr USING v_frendmobile;
end mobileFrends_se3_proc;
--根据好友手机号 查询 宿主手机号2(查看有哪些人将这个手机号加为了好友) (按 手机号区间)
procedure mobileFrends_se3b_proc(v_fromFrendMobile in varchar2, v_toFrendMobile in varchar2, p_rc out myrctype)
is
sqlstr VARCHAR2(400);
begin
sqlstr := 'SELECT m.mobile,m.frendmobile FROM mobileFrends m WHERE EXISTS ( SELECT t.mobile FROM u_state t WHERE t.mobile=m.frendmobile AND t.mobile >= :v_fromFrendMobile AND t.mobile <= :v_toFrendMobile )';
OPEN p_rc FOR sqlstr USING v_fromFrendMobile, v_toFrendMobile;
end mobileFrends_se3b_proc;end pkg_mobileFrends_op;
/-------------------------------------------------- 测试 :--------------------------------------------------
var p_rc refcursor;exec pkg_mobileFrends_op.mobileFrends_se2b_proc('13834570306','13834570399',:p_rc);
print p_rc;