open for cur select .......open cur loop end loop;
--返回结果集 CREATE OR REPLACE PROCEDURE p_getEmp(o OUT SYS_REFCURSOR) IS BEGIN OPEN o FOR 'select * from emp'; END; /其它的看一下这本书《精通Oracle 10g PL/SQL编程》(http://download.csdn.net/source/552111)
[Quote=引用 4 楼 tangren 的回复:] SQL code --返回结果集 CREATE OR REPLACE PROCEDURE p_getEmp(o OUT SYS_REFCURSOR) IS BEGIN OPEN o FOR 'select * from emp'; END; / 里边有个WHILE 循环,在帮忙简单写下,参考下呗
CREATE OR REPLACE PROCEDURE p_getEmp(ptime timestamp,ltime timestamp, o OUT SYS_REFCURSOR) IS tp timestamp; tp:=ptime; BEGIN while tp<=ltime loop tp=tp+1; OPEN o FOR select (select count(fid) from T_SHE_FDCCUSTOMER c where c.freceptiontypeid='dpNwoAEbEADgABNYwKgSwmv0964=' and to_char(c.flastupdatetime,'yyyy-mm-dd')=tp) + (select count(fid) from T_SHE_TrackRecord t where t.freceptiontypeid='dpNwoAEbEADgABNYwKgSwmv0964=' and to_char(c.flastupdatetime,'yyyy-mm-dd')=tp) from dual end loop END; 就是这个意思,哪位帮忙改写下
create or replace procedure pro_query_emp (v_no in emp.ename%type, v_name out emp.ename%type, v_sal out emp.sal%type) is e_sal_error exception; begin select ename,sal into v_name,v_sal from emp where empno = v_no; if v_sal >= 2500 then dbms_output.put_line('该雇员工资'|| v_sal); dbms_output.put_line('该雇员工资'|| v_name); raise e_sal_error; end if; exception when no_data_found then dbms_output.put_line('没有该雇员:' || v_no); when e_sal_error then dbms_output.put_line('该雇员工资高于2500'); end pro_query_emp; 在plsql中的命令窗口中运行 exec pro_query_emp; 或者找到存储过程,点击测试,也是可以运行的。
select .......open cur
loop
end loop;
CREATE OR REPLACE PROCEDURE p_getEmp(o OUT SYS_REFCURSOR) IS
BEGIN
OPEN o FOR 'select * from emp';
END;
/其它的看一下这本书《精通Oracle 10g PL/SQL编程》(http://download.csdn.net/source/552111)
SQL code
--返回结果集
CREATE OR REPLACE PROCEDURE p_getEmp(o OUT SYS_REFCURSOR) IS
BEGIN
OPEN o FOR 'select * from emp';
END;
/
里边有个WHILE 循环,在帮忙简单写下,参考下呗
tp:=ptime;
BEGIN
while tp<=ltime loop
tp=tp+1;
OPEN o FOR
select
(select count(fid) from T_SHE_FDCCUSTOMER c
where c.freceptiontypeid='dpNwoAEbEADgABNYwKgSwmv0964='
and to_char(c.flastupdatetime,'yyyy-mm-dd')=tp)
+
(select count(fid) from T_SHE_TrackRecord t
where t.freceptiontypeid='dpNwoAEbEADgABNYwKgSwmv0964='
and to_char(c.flastupdatetime,'yyyy-mm-dd')=tp)
from dual
end loop
END;
就是这个意思,哪位帮忙改写下
(v_no in emp.ename%type,
v_name out emp.ename%type,
v_sal out emp.sal%type)
is
e_sal_error exception;
begin
select ename,sal into v_name,v_sal from emp where empno = v_no;
if v_sal >= 2500 then
dbms_output.put_line('该雇员工资'|| v_sal);
dbms_output.put_line('该雇员工资'|| v_name);
raise e_sal_error;
end if;
exception
when no_data_found then
dbms_output.put_line('没有该雇员:' || v_no);
when e_sal_error then
dbms_output.put_line('该雇员工资高于2500');
end pro_query_emp;
在plsql中的命令窗口中运行
exec pro_query_emp;
或者找到存储过程,点击测试,也是可以运行的。