参考:
http://www.cnblogs.com/sc-xx/archive/2011/12/03/2275084.html
http://blog.csdn.net/liyong199012/article/details/8948952
SET SERVEROUTPUT ON
DECLARE
v_ename VARCHAR2(10);
v_job VARCHAR2(10);
v_sal number;
CURSOR emp_cursor IS
SELECT ename,job,sal FROM emp order by sal desc;
BEGIN
OPEN emp_cursor;
for i in 1..10 loop
FETCH emp_cursor INTO v_ename,v_job,v_sal;
DBMS_OUTPUT.PUT_LINE(v_ename||','||v_job||','||v_sal);
end loop;
CLOSE emp_cursor;
END;
declare
cursor emp_sur is
select * from emp;
begin
for recod in emp_sur loop
DBMS_OUTPUT.PUT_LINE(recod.empno||'#'||recod.ename||'#'||recod.job);
end loop;
end;
declare
cursor csr_dept
is
select job,ename from emp;
begin
for row_dept in csr_dept loop
dbms_output.put_line('部门名称:'||row_dept.ename);
end loop;
end;
1:
CREATE OR REPLACE PROCEDURE test_oracle(p_id IN NUMBER)
IS
v_ename VARCHAR2(10);
v_job VARCHAR2(10);
v_sal NUMBER(10);
CURSOR user_gz IS
SELECT ename,job,sal FROM emp WHERE empno = p_id;
BEGIN
OPEN user_gz;
-- {FOR i IN 1..3 LOOP
-- FETCH user_gz INTO v_ename,v_job,v_sal;
-- dbms_output.put_line(v_ename||'#'||v_job||'#'||v_sal);
-- END LOOP;}
IF user_gz%ISOPEN THEN LOOP
FETCH user_gz INTO v_ename,v_job,v_sal;
dbms_output.put_line(v_ename||'#_#'||v_job||'#_#'||v_sal);
END LOOP;
ELSE
dbms_output.put_line(v_ename||'#'||v_job||'#'||v_sal);
END IF;
CLOSE user_gz;
END test_oracle;
2:
CREATE OR REPLACE PROCEDURE test_oracle(p_id IN NUMBER)
IS
v_ename VARCHAR2(10);
v_job VARCHAR2(10);
v_sal NUMBER(10);
CURSOR user_gz IS
SELECT ename,job,sal FROM emp WHERE empno = p_id;
BEGIN
OPEN user_gz;
IF user_gz%ISOPEN THEN
LOOP
FETCH user_gz INTO v_ename,v_job,v_sal;
EXIT WHEN user_gz%NOTFOUND;
dbms_output.put_line(v_ename||'#_#'||v_job||'#_#'||v_sal);
END LOOP;
ELSE
dbms_output.put_line(v_ename||'#'||v_job||'#'||v_sal);
END IF;
CLOSE user_gz;
END test_oracle;
declare
v_ename VARCHAR2(10);
v_comm NUMBER(10);
v_sal NUMBER(10);
CURSOR user_gz IS
SELECT distinct(a.sal),a.comm,b.ename FROM han a,emp b WHERE a.sal=b.sal;
BEGIN
OPEN user_gz;
IF user_gz%ISOPEN THEN
LOOP
FETCH user_gz INTO v_sal,v_comm,v_ename;
EXIT WHEN user_gz%NOTFOUND;
dbms_output.put_line(v_sal||'#'||v_comm||'#'||v_ename);
END LOOP;
ELSE
dbms_output.put_line(v_sal||'#-'||v_comm||'#-'||v_ename);
END IF;
CLOSE user_gz;
END test_oracle;
阅读(1105) | 评论(0) | 转发(0) |