DECLARE ocursor sys_refcursor; S VARCHAR2(4000); begin open ocursor for select column_name,desc_name from TB ; select * from ocursor ; end 1.在PL/SQL里面執行下,如何肉眼看到光標ocursor里面數據表結果 2.在其他模式下如何能看到執行結果 剛學oracle,希望大家能幫忙。
declare ocursor sys_refcursor; tb_type a%rowtype; begin open ocursor for select * from a; loop fetch ocursor into tb_type; exit when ocursor%notfound; dbms_output.put_line(tb_type.id); end loop; close ocursor; end;
SQL> SET SERVEROUTPUT ON; SQL> --1 SQL> DECLARE 2 ocursor SYS_REFCURSOR; 3 S VARCHAR2(4000); 4 v_empno emp.empno%TYPE; 5 v_ename emp.ename%TYPE; 6 BEGIN 7 OPEN ocursor FOR 8 SELECT empno, ename FROM emp; 9 LOOP 10 FETCH ocursor 11 INTO v_empno, v_ename; 12 EXIT WHEN ocursor%NOTFOUND; 13 dbms_output.put_line(v_empno || ',' || v_ename); 14 END LOOP; 15 END; 16 17 / 7369,SMITH 7499,ALLEN 7521,WARD 7566,JONES 7654,MARTIN 7698,BLAKE 7782,CLARK 7788,SCOTT 7839,KING 7844,TURNER 7876,ADAMS 7900,JAMES 7902,FORD 7934,MILLERPL/SQL 过程已成功完成。SQL> SQL> --2 SQL> variable ocursor refcursor; SQL> begin 2 OPEN :ocursor FOR 3 SELECT empno, ename FROM emp; 4 end; 5 /PL/SQL 过程已成功完成。SQL> print :ocursor; EMPNO ENAME ---------- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS EMPNO ENAME ---------- ---------- 7900 JAMES 7902 FORD 7934 MILLER已选择14行。SQL>
另外两种方法是: declare ocursor sys_refcursor; type tb_type is table of tb%rowtype; v_type tb_type; begin open ocursor for select column_name, desc_name from tb; fetch ocursor bulk collect into v_type; for i in v_type.first..v_type.last loop dbms_output.put_line(v_type(i).column_name ||' '||v_type(i).desc_name); end loop; close ocursor; end; declare v_type tb%rowtype; begin cursor ocursor is select column_name, desc_name from tb; open ocursor; loop fetch ocursor into v_type; exit when ocursor%notfound; dbms_output.put_line(v_type.column_name ||' '||v_type.desc_name); end loop; close ocursor; end;
不是在PL/SQL里面執行的吧。 你這個模式下如何進入,謝謝。
SQL> create or replace procedure pro is 2 cursor c is select deptno,dname,loc from scott.dept; 3 begin 4 for c1 in c loop 5 dbms_output.put_line(c1.deptno || ' ' || c1.dname || ' ' || c1.loc); 6 end loop; 7 end pro; 8 /
Procedure created
SQL> exec pro;
10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
ocursor sys_refcursor;
tb_type a%rowtype;
begin
open ocursor for select * from a;
loop
fetch ocursor into tb_type;
exit when ocursor%notfound;
dbms_output.put_line(tb_type.id);
end loop;
close ocursor;
end;
SQL> --1
SQL> DECLARE
2 ocursor SYS_REFCURSOR;
3 S VARCHAR2(4000);
4 v_empno emp.empno%TYPE;
5 v_ename emp.ename%TYPE;
6 BEGIN
7 OPEN ocursor FOR
8 SELECT empno, ename FROM emp;
9 LOOP
10 FETCH ocursor
11 INTO v_empno, v_ename;
12 EXIT WHEN ocursor%NOTFOUND;
13 dbms_output.put_line(v_empno || ',' || v_ename);
14 END LOOP;
15 END;
16
17 /
7369,SMITH
7499,ALLEN
7521,WARD
7566,JONES
7654,MARTIN
7698,BLAKE
7782,CLARK
7788,SCOTT
7839,KING
7844,TURNER
7876,ADAMS
7900,JAMES
7902,FORD
7934,MILLERPL/SQL 过程已成功完成。SQL>
SQL> --2
SQL> variable ocursor refcursor;
SQL> begin
2 OPEN :ocursor FOR
3 SELECT empno, ename FROM emp;
4 end;
5 /PL/SQL 过程已成功完成。SQL> print :ocursor; EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS EMPNO ENAME
---------- ----------
7900 JAMES
7902 FORD
7934 MILLER已选择14行。SQL>
declare
ocursor sys_refcursor;
type tb_type is table of tb%rowtype;
v_type tb_type;
begin
open ocursor for
select column_name, desc_name from tb;
fetch ocursor bulk collect into v_type;
for i in v_type.first..v_type.last loop
dbms_output.put_line(v_type(i).column_name ||' '||v_type(i).desc_name);
end loop;
close ocursor;
end;
declare
v_type tb%rowtype;
begin
cursor ocursor is select column_name, desc_name from tb;
open ocursor;
loop
fetch ocursor into v_type;
exit when ocursor%notfound;
dbms_output.put_line(v_type.column_name ||' '||v_type.desc_name);
end loop;
close ocursor;
end;
你這個模式下如何進入,謝謝。
2 cursor c is select deptno,dname,loc from scott.dept;
3 begin
4 for c1 in c loop
5 dbms_output.put_line(c1.deptno || ' ' || c1.dname || ' ' || c1.loc);
6 end loop;
7 end pro;
8 /
Procedure created
SQL> exec pro;
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
PL/SQL procedure successfully completed
光標ocursor的完整內容如下:column_name desc_name
column_name的值1 desc_name的值1
column_name的值2 desc_name的值2
不過經輸入
SQL> variable ocursor refcursor;
REFCURSOR not supported --這里有問題
但很多sqlplus命令是不被支持的。我是用sqlplus执行的,告诉你一个小窍门,
可以在command窗口直接输入sqlplus
即可打开oracle万能工具sqlplus,enjoy!!
SQL> begin
2 OPEN :ocursor FOR
3 SELECT empno, ename FROM emp;
4 end;
5 /PL/SQL 过程已成功完成。為什么是一欄一欄的顯示???
沒有像表格一樣秀出來?
反正剛學oracle,很多都不懂的,謝謝大家了。
有高手幫忙嗎?
問題1.怎么樣可以顯示整個臨時表,包括他的欄位。
問題2.怎么執行
open 變量1 for
SELECT 變量2 from TB;
變量1是CURSOR變量
變量2是字符串變量
第二個問題答對再加分