BEGIN
NULL;
END;
DECLARE
a VARCHAR2(10);
b VARCHAR2(10) := 'bbb';
c VARCHAR2(10) NOT NULL := 'ccc';
d CONSTANT VARCHAR2(10) := 'ddd';
BEGIN
dbms_output.put_line('a : ' || a);
dbms_output.put_line('b : ' || b);
dbms_output.put_line('c : ' || c);
dbms_output.put_line('d : ' || d);
END;
実行結果
a :
b : bbb
c : ccc
d : ddd
- SELECT結果の代入(単一項目のSELECT INTO)
DECLARE
now DATE;
BEGIN
SELECT sysdate INTO now FROM dual;
dbms_output.put_line('now : ' || now);
END;
実行結果
now : 14-02-04
- SELECT結果の代入(複数項目のSELECT INTO)
DECLARE
yyyymmdd CHAR(8);
hh24miss CHAR(6);
BEGIN
SELECT TO_CHAR(sysdate, 'YYYYMMDD'),
TO_CHAR(sysdate, 'HH24MISS')
INTO yyyymmdd,
hh24miss
FROM dual;
dbms_output.put_line('yyyymmdd : ' || yyyymmdd);
dbms_output.put_line('hh24miss : ' || hh24miss);
END;
実行結果
yyyymmdd : 20140204
hh24miss : 234759
DECLARE
CURSOR c_emp
IS
SELECT * FROM emp WHERE rownum < 3;
r_emp c_emp%rowtype;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO r_emp;
EXIT
WHEN c_emp%notfound;
dbms_output.put_line(r_emp.ename);
END LOOP;
CLOSE c_emp;
END;
実行結果
SMITH
ALLEN
DECLARE
CURSOR c_emp
IS
SELECT * FROM emp WHERE rownum < 3;
r_emp c_emp%rowtype;
BEGIN
OPEN c_emp;
FETCH c_emp INTO r_emp;
WHILE c_emp%found
LOOP
dbms_output.put_line(r_emp.ename);
FETCH c_emp INTO r_emp;
END LOOP;
CLOSE c_emp;
END;
実行結果
SMITH
ALLEN
DECLARE
CURSOR c_emp
IS
SELECT * FROM emp WHERE rownum < 3;
r_emp c_emp%rowtype;
BEGIN
OPEN c_emp;
FETCH c_emp INTO r_emp;
FOR i IN 1..2
LOOP
dbms_output.put_line(r_emp.ename);
FETCH c_emp INTO r_emp;
END LOOP;
CLOSE c_emp;
END;
実行結果
SMITH
ALLEN
DECLARE
CURSOR c_emp
IS
SELECT * FROM emp WHERE rownum < 3;
r_emp c_emp%rowtype;
BEGIN
FOR r_emp IN c_emp
LOOP
dbms_output.put_line(r_emp.ename);
END LOOP;
END;
実行結果
SMITH
ALLEN
DECLARE
CURSOR c_emp
IS
SELECT * FROM emp WHERE rownum < 3;
type t_emp
IS
TABLE OF emp.job%type INDEX BY emp.ename%type;
w_emp t_emp;
i emp.ename%type;
BEGIN
FOR r_emp IN c_emp
LOOP
w_emp(r_emp.ename) := r_emp.job;
END LOOP;
i := w_emp.first;
WHILE i IS NOT NULL
LOOP
dbms_output.put_line(i || ' : ' || w_emp(i));
i := w_emp.next(i);
END LOOP;
END;
実行結果
ALLEN : SALESMAN
SMITH : CLERK
DECLARE
CURSOR c_emp
IS
SELECT * FROM emp WHERE rownum < 3;
type t_emp
IS
TABLE OF emp.ename%type;
w_emp t_emp := t_emp();
BEGIN
FOR r_emp IN c_emp
LOOP
w_emp.extend;
w_emp(w_emp.last) := r_emp.ename;
END LOOP;
FOR i IN w_emp.first .. w_emp.last
LOOP
dbms_output.put_line(i || ' : ' || w_emp(i));
END LOOP;
END;
実行結果
1 : SMITH
2 : ALLEN
DECLARE
CURSOR c_emp
IS
SELECT * FROM emp WHERE rownum < 3;
type t_emp IS varray(10) OF emp.ename%type;
w_emp t_emp := t_emp();
BEGIN
FOR r_emp IN c_emp
LOOP
w_emp.extend;
w_emp(w_emp.last) := r_emp.ename;
END LOOP;
FOR i IN w_emp.first .. w_emp.last
LOOP
dbms_output.put_line(i || ' : ' || w_emp(i));
END LOOP;
END;
実行結果
1 : SMITH
2 : ALLEN
DECLARE
CURSOR c_emp
IS
SELECT * FROM emp WHERE rownum < 3;
type t_emp_tab
IS
TABLE OF c_emp%rowtype INDEX BY pls_integer;
w_emp_tab t_emp_tab;
BEGIN
OPEN c_emp;
FETCH c_emp bulk collect INTO w_emp_tab;
CLOSE c_emp;
IF w_emp_tab.count = 0 THEN
dbms_output.put_line('data not found');
ELSE
FOR i IN w_emp_tab.first .. w_emp_tab.last
LOOP
dbms_output.put_line(w_emp_tab(i).empno || ' : ' || w_emp_tab(i).ename || ' : ' || w_emp_tab(i).job);
END LOOP;
END IF;
END;
実行結果
7369 : SMITH : CLERK
7499 : ALLEN : SALESMAN
※実行環境は下記の通りです。
SQL> select * from v$version;
BANNER
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for 64-bit
Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production
※コードはSQL Developerで整形しています。
ショートカットキー:Ctrl+Shift+F7