DECLARE
CURSOR emp_cursor IS SELECT ename,job,sal FROM emp WHERE empno = 7788;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor
INTO emp_record;
dbms_output.put_line('名前:'||emp_record.ename||' 職業:'||emp_record.job||' 薪水:'||emp_record.sal);
CLOSE emp_cursor;
END;
-------------------------------------------------------------------------------------------------------------
○(まる)、×(ばつ)、※(こめ)
DECLARE
CURSOR emp_cursor IS
SELECT empno,ename FROM emp;
BEGIN
FOR emp_record IN emp_cursor LOOP
dbms_output.put_line(emp_record.empno||emp_record.ename);
END LOOP;
END;
-------------------------------------------------------------------------------------------------------------
--引数
DECLARE
v_empno NUMBER(5);
v_ename VARCHAR2(10);
CURSOR emp_cursor(p_deptno NUMBER,p_job VARCHAR2) IS
SELECT empno,ename FROM emp WHERE deptno = p_deptno AND job = p_job;
BEGIN
OPEN emp_cursor(10,UPPER('clerk') );
LOOP
FETCH emp_cursor
INTO v_empno,v_ename;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);
END LOOP;
END;
----------------------------------------------------------------------------------------------------------------
動態SELECT語句
DECLARE
str VARCHAR2(100);
v_ename VARCHAR2(10);
BEGIN
str := 'select ename from scott.emp where empno = 7788';
EXECUTE IMMEDIATE str INTO v_ename;
dbms_output.put_line(v_ename);
END;
DECLARE
CURSOR emp_cursor IS SELECT ename,sal FROM emp;
BEGIN
FOR emp_record IN emp_cursor LOOP
dbms_output.PUT_LINE(emp_record.ename);
END LOOP;
END;
--------------------------------------------------
BEGIN
FOR emp_record IN
(SELECT ename,sal FROM emp)
LOOP
dbms_output.PUT_LINE(emp_record.ename);
END LOOP;
END;
--------------------------------------------------