当SELECT * INTO v_Row FROM TABLE WHERE CON = X;]只返回一行时 可以这样写:--------------------------------------------------------------- SET SERVEROUTPUT ON;DECLARE X NUMBER; v_Row TABLE%rowtype; BEGIN X := 3; SELECT * INTO v_Row FROM TABLE WHERE CON = X; Dbms_Output.put_line( v_Row.column1 || v_Row.column2..... ); END; / ---------------------------------------------------------------
当SELECT * INTO v_Row FROM TABLE WHERE CON = X;]返回多行时,用游标---------------------------------------------------------------------- SET SERVEROUTPUT ON;DECLARE x TABLE.CON%TYPE := 3; v_Row TABLE%rowtype; CURSOR c_TABLE IS SELECT * FROM TABLE WHERE CON = x; BEGIN OPEN c_TABLE; LOOP FETCH c_TABLE INTO v_Row; EXIT WHEN c_TABLE%NOTFOUND; DBMS_OUTPUT.PUT_LINE( v_Row.column1 || v_Row.column2...... ); END LOOP; CLOSE c_TABLE; END; / ----------------------------------------------------------------------
DECLARE type t_sor is ref curosr; v_sor t_sor; X NUMBER; BEGIN open v_sor for 'SELECT * FROM TABLE WHERE CON = '||X; loop fetch v_sor into 变量1,变量2,...; exit when v_sor%notfound; dbms_output.put_line(变量1||变量2||....); end loop; END; /
X NUMBER;
y number;
BEGIN
SELECT count(*) into y FROM TABLE WHERE CON = X;
END;
不能在過程里面直接寫select * from ...
不能在過程里面直接寫select * from ...
可以这样写:---------------------------------------------------------------
SET SERVEROUTPUT ON;DECLARE
X NUMBER;
v_Row TABLE%rowtype;
BEGIN
X := 3;
SELECT * INTO v_Row FROM TABLE WHERE CON = X;
Dbms_Output.put_line( v_Row.column1 || v_Row.column2..... );
END;
/
---------------------------------------------------------------
SET SERVEROUTPUT ON;DECLARE
x TABLE.CON%TYPE := 3;
v_Row TABLE%rowtype; CURSOR c_TABLE IS
SELECT *
FROM TABLE
WHERE CON = x;
BEGIN
OPEN c_TABLE;
LOOP
FETCH c_TABLE INTO v_Row;
EXIT WHEN c_TABLE%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( v_Row.column1 || v_Row.column2...... );
END LOOP; CLOSE c_TABLE;
END;
/
----------------------------------------------------------------------
type t_sor is ref curosr;
v_sor t_sor;
X NUMBER;
BEGIN
open v_sor for 'SELECT * FROM TABLE WHERE CON = '||X;
loop
fetch v_sor into 变量1,变量2,...;
exit when v_sor%notfound;
dbms_output.put_line(变量1||变量2||....);
end loop;
END;
/