Oracle PL/SQL/PL SQL/Select Into
Содержание
- 1 Bulk Collection: fetch a single row from the ALL_OBJECTS table.
- 2 Calculate salary by adding salary with max(salary)
- 3 Catch too_many_rows Exception for "Select into" statement
- 4 If no records are retrieved for a SELECT - INTO statement the following error is returned
- 5 If too many records are returned for a SELECT - INTO statement the following error is returned
- 6 Multiple-Row SELECT Command with Several Exception-Handling Routines
- 7 no_data_found from select ... into
- 8 Oracle returns an error when a SELECT statement returns more than one row
- 9 Output variable after "select into"
- 10 select bulk collect into table collection
- 11 Select count result into a variable
- 12 Select data for update
- 13 Select into and subquery
- 14 SELECT into value pair
- 15 Select single value into variable
- 16 Select the number of employees into the l_emp_count variable
- 17 Select two columns into a cursor variable
- 18 Select value from aggregate function to variable
- 19 Select value from table into variable
- 20 Select value into a number variable in a for loop
- 21 Select value to variable one by one
- 22 Store max(salary) to a variable
- 23 Store max(tableName.column) to tableName.column.type variable
- 24 This script demonstrates how to do a non-bulk select into elements of a PL/SQL table.
- 25 TOO_MANY_ROWS exception and select into command
- 26 Use subquery in pl/sql block
Bulk Collection: fetch a single row from the ALL_OBJECTS table.
<source lang="sql">
SQL> SQL> declare
2 x number; 3 begin 4 select object_id 5 into x 6 from all_objects 7 where rownum <= 1; 8 end; 9 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> --Here is the equivalent bulk collection version to get 500 rows in a single call. SQL> SQL> declare
2 type numlist is table of number; 3 x numlist; 4 begin 5 select object_id 6 bulk collect into x 7 from all_objects 8 where rownum <= 500; 9 end; 10 /
PL/SQL procedure successfully completed.
</source>
Calculate salary by adding salary with max(salary)
<source lang="sql">
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> SQL> SQL> declare
2 newSalary number := 10000; 3 v_max_sal number; 4 begin 5 select max(sal) + newSalary into v_max_sal from emp; 6 end; 7 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table emp; Table dropped.
</source>
Catch too_many_rows Exception for "Select into" statement
<source lang="sql">
SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> select * from emp;
EMPNO ENAME JOB MGR
---------- --------- ----------
HIREDATE SAL COMM
---------- ----------
DEPTNO
7369 SMITH CLERK 7902
17-DEC-1980 00:00:00 800
20
7499 ALLEN SALESMAN 7698
20-FEB-1981 00:00:00 1600 300
30
7521 WARD SALESMAN 7698
22-FEB-1981 00:00:00 1250 500
EMPNO ENAME JOB MGR
---------- --------- ----------
HIREDATE SAL COMM
---------- ----------
DEPTNO
30
7566 JONES MANAGER 7839
02-APR-1981 00:00:00 2975
20
7654 MARTIN SALESMAN 7698
28-SEP-1981 00:00:00 1250 1400
30
7698 BLAKE MANAGER 7839
01-MAY-1981 00:00:00 2850
30
7782 CLARK MANAGER 7839
09-JUN-1981 00:00:00 2450
10
7788 SCOTT ANALYST 7566
09-DEC-1982 00:00:00 3000
20
7839 KING PRESIDENT
17-NOV-1981 00:00:00 5000
10
7844 TURNER SALESMAN 7698
08-SEP-1981 00:00:00 1500 0
30
7876 ADAMS CLERK 7788
12-JAN-1983 00:00:00 1100
20
7900 JAMES CLERK 7698
03-DEC-1981 00:00:00 950
30
7902 FORD ANALYST 7566
03-DEC-1981 00:00:00 3000
20
7934 MILLER CLERK 7782
23-JAN-1982 00:00:00 1300
10
14 rows selected. SQL> SQL> DECLARE
2 v_emp_id INTEGER; 3 BEGIN 4 SELECT empno into v_emp_id from emp WHERE ename = "MILLER"; 5 exception 6 when too_many_rows THEN -- type of exception 7 Null; --exception logic can go here as needed 8 END; 9 /
PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped. SQL> --
</source>
If no records are retrieved for a SELECT - INTO statement the following error is returned
<source lang="sql">
SQL> CREATE TABLE books (
2 isbn CHAR(10) PRIMARY KEY, 3 category VARCHAR2(20), 4 title VARCHAR2(100), 5 num_pages NUMBER, 6 price NUMBER, 7 copyright NUMBER(4), 8 emp1 NUMBER, 9 emp2 NUMBER, 10 emp3 NUMBER 11 );
Table created. SQL> SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("1", "Database", "Oracle", 563, 39.99, 2009, 1, 2, 3);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
2 VALUES ("2", "Database", "MySQL", 765, 44.99, 2009, 4, 5);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("3", "Database", "SQL Server", 404, 39.99, 2001, 6, 7, 8);
1 row created. SQL> SQL> SQL> SQL> DECLARE
2 v_title BOOKS.TITLE%TYPE; 3 BEGIN 4 5 SELECT title INTO v_title FROM books WHERE isbn = "2"; 6 7 DBMS_OUTPUT.PUT_LINE(v_title); 8 9 EXCEPTION 10 WHEN OTHERS 11 THEN 12 DBMS_OUTPUT.PUT_LINE(sqlerrm); 13 END; 14 /
MySQL PL/SQL procedure successfully completed. SQL> SQL> drop table books; Table dropped.
</source>
If too many records are returned for a SELECT - INTO statement the following error is returned
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE books (
2 isbn CHAR(10) PRIMARY KEY, 3 category VARCHAR2(20), 4 title VARCHAR2(100), 5 num_pages NUMBER, 6 price NUMBER, 7 copyright NUMBER(4), 8 emp1 NUMBER, 9 emp2 NUMBER, 10 emp3 NUMBER 11 );
Table created. SQL> SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("1", "Database", "Oracle", 563, 39.99, 2009, 1, 2, 3);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
2 VALUES ("2", "Database", "MySQL", 765, 44.99, 2009, 4, 5);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("3", "Database", "SQL Server", 404, 39.99, 2001, 6, 7, 8);
1 row created. SQL> SQL> SQL> SQL> DECLARE
2 v_title BOOKS.TITLE%TYPE; 3 BEGIN 4 5 SELECT title INTO v_title FROM books; 6 7 DBMS_OUTPUT.PUT_LINE(v_title); 8 9 EXCEPTION 10 WHEN OTHERS 11 THEN 12 DBMS_OUTPUT.PUT_LINE(sqlerrm); 13 END; 14 /
SQL> SQL> SET ESCAPE OFF SQL> SQL> SQL> drop table books; Table dropped.
</source>
Multiple-Row SELECT Command with Several Exception-Handling Routines
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> select * from emp;
EMPNO ENAME JOB MGR
---------- --------- ----------
HIREDATE SAL COMM
---------- ----------
DEPTNO
7369 SMITH CLERK 7902
17-DEC-1980 00:00:00 800
20
7499 ALLEN SALESMAN 7698
20-FEB-1981 00:00:00 1600 300
30
7521 WARD SALESMAN 7698
22-FEB-1981 00:00:00 1250 500
30
7566 JONES MANAGER 7839
02-APR-1981 00:00:00 2975
20
7654 MARTIN SALESMAN 7698
28-SEP-1981 00:00:00 1250 1400
30
7698 BLAKE MANAGER 7839
01-MAY-1981 00:00:00 2850
30
7782 CLARK MANAGER 7839
09-JUN-1981 00:00:00 2450
10
7788 SCOTT ANALYST 7566
09-DEC-1982 00:00:00 3000
20
7839 KING PRESIDENT
17-NOV-1981 00:00:00 5000
10
7844 TURNER SALESMAN 7698
08-SEP-1981 00:00:00 1500 0
30
7876 ADAMS CLERK 7788
12-JAN-1983 00:00:00 1100
20
7900 JAMES CLERK 7698
03-DEC-1981 00:00:00 950
30
7902 FORD ANALYST 7566
03-DEC-1981 00:00:00 3000
20
7934 MILLER CLERK 7782
23-JAN-1982 00:00:00 1300
10
14 rows selected. SQL> SQL> DECLARE
2 v_emp_id INTEGER; 3 BEGIN 4 SELECT empno into v_emp_id from emp WHERE ename = "MILLER"; 5 exception 6 when no_data_found THEN v_emp_id := 888; 7 when too_many_rows THEN v_emp_id := 999; 8 END; 9 /
PL/SQL procedure successfully completed. SQL> drop table emp; Table dropped. SQL> SQL> SQL> --
</source>
no_data_found from select ... into
<source lang="sql">
SQL> SQL> create table t ( object_id primary key, object_name )
2 organization index 3 as 4 select object_id, object_name from all_objects;
Table created. SQL> SQL> create or replace procedure implicit
2 as 3 l_object_name t.object_name%type; 4 begin 5 for i in 1 .. 30000 6 loop 7 begin 8 select object_name into l_object_name 9 from t 10 where object_id = i; 11 exception 12 when no_data_found then 13 l_object_name := null; 14 end; 15 end loop; 16 end; 17 /
Procedure created. SQL> SQL> drop table t; Table dropped. SQL>
</source>
Oracle returns an error when a SELECT statement returns more than one row
<source lang="sql">
SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> -- Oracle returns an error when a SELECT statement returns more than one row. SQL> SQL> DECLARE
2 myName emp.ename%TYPE;
3 BEGIN
4 SELECT eName
5 INTO myName
6 FROM emp;
7
8 DBMS_OUTPUT.PUT_LINE("Name is "|| myName);
9 END;
10 /
DECLARE
ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 4
SQL> SQL> SQL> SQL> drop table emp; Table dropped. SQL> SQL> SQL> SQL>
</source>
Output variable after "select into"
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> DECLARE
2 v_Num1 NUMBER := 1; 3 v_Num2 NUMBER := 2; 4 v_String1 VARCHAR2(50) := "Hello World!"; 5 v_String2 VARCHAR2(50) := "-- This message brought to you by PL/SQL!"; 6 v_OutputStr VARCHAR2(50); 7 BEGIN 8 INSERT INTO MyTable (num_col, char_col) VALUES (v_Num1, v_String1); 9 INSERT INTO MyTable (num_col, char_col) VALUES (v_Num2, v_String2); 10 11 SELECT char_col INTO v_OutputStr FROM MyTable WHERE num_col = v_Num1; 12 DBMS_OUTPUT.PUT_LINE(v_OutputStr); 13 14 SELECT char_col INTO v_OutputStr FROM MyTable WHERE num_col = v_Num2; 15 DBMS_OUTPUT.PUT_LINE(v_OutputStr); 16 ROLLBACK; 17 END; 18 /
Hello World! -- This message brought to you by PL/SQL! PL/SQL procedure successfully completed. SQL> SQL> drop table MyTable; Table dropped. SQL>
</source>
select bulk collect into table collection
<source lang="sql">
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created.
SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(6, "Black", "Designer", 9,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created.
SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created.
SQL> SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> declare
2 type numlist is table of number; 3 x numlist; 4 begin 5 select object_id bulk collect into x from all_objects where rownum <= 500; 6 end; 7 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped. SQL> SQL>
</source>
Select count result into a variable
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(10) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> declare
2 l_cnt number; 3 empNo number default 1; 4 begin 5 select count(*) into l_cnt from emp; 6 7 for x in ( select * from emp ) 8 loop 9 insert into emp values( empNo, x.ename, x.job, x.mgr, x.hiredate, x.sal,x.rum, x.deptno ); 10 empNo := empNo+1; 11 end loop; 12 commit; 13 end; 14 /
PL/SQL procedure successfully completed. SQL> SQL> select * from emp;
EMPNO ENAME JOB MGR
---------- --------- ----------
HIREDATE SAL COMM
---------- ----------
DEPTNO
7369 SMITH CLERK 7902
17-DEC-1980 00:00:00 800
20
7499 ALLEN SALESMAN 7698
20-FEB-1981 00:00:00 1600 300
30
7521 WARD SALESMAN 7698
22-FEB-1981 00:00:00 1250 500
30
7566 JONES MANAGER 7839
02-APR-1981 00:00:00 2975
20
7654 MARTIN SALESMAN 7698
28-SEP-1981 00:00:00 1250 1400
30
7698 BLAKE MANAGER 7839
01-MAY-1981 00:00:00 2850
30
7782 CLARK MANAGER 7839
09-JUN-1981 00:00:00 2450
10
7788 SCOTT ANALYST 7566
09-DEC-1982 00:00:00 3000
20
7839 KING PRESIDENT
17-NOV-1981 00:00:00 5000
10
7844 TURNER SALESMAN 7698
08-SEP-1981 00:00:00 1500 0
30
7876 ADAMS CLERK 7788
12-JAN-1983 00:00:00 1100
20
7900 JAMES CLERK 7698
03-DEC-1981 00:00:00 950
30
7902 FORD ANALYST 7566
03-DEC-1981 00:00:00 3000
20
7934 MILLER CLERK 7782
23-JAN-1982 00:00:00 1300
10
1 SMITH CLERK 7902
17-DEC-1980 00:00:00 800
20
2 ALLEN SALESMAN 7698
20-FEB-1981 00:00:00 1600 300
30
3 WARD SALESMAN 7698
22-FEB-1981 00:00:00 1250 500
30
4 JONES MANAGER 7839
02-APR-1981 00:00:00 2975
20
5 MARTIN SALESMAN 7698
28-SEP-1981 00:00:00 1250 1400
30
6 BLAKE MANAGER 7839
01-MAY-1981 00:00:00 2850
30
7 CLARK MANAGER 7839
09-JUN-1981 00:00:00 2450
10
8 SCOTT ANALYST 7566
09-DEC-1982 00:00:00 3000
20
9 KING PRESIDENT
17-NOV-1981 00:00:00 5000
10
10 TURNER SALESMAN 7698
EMPNO ENAME JOB MGR
---------- --------- ----------
HIREDATE SAL COMM
---------- ----------
DEPTNO
08-SEP-1981 00:00:00 1500 0
30
11 ADAMS CLERK 7788
12-JAN-1983 00:00:00 1100
20
12 JAMES CLERK 7698
03-DEC-1981 00:00:00 950
30
13 FORD ANALYST 7566
03-DEC-1981 00:00:00 3000
20
14 MILLER CLERK 7782
23-JAN-1982 00:00:00 1300
10
28 rows selected. SQL> SQL> drop table emp; Table dropped. SQL> SQL> SQL> --
</source>
Select data for update
<source lang="sql">
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY, 3 clobData CLOB NOT NULL 4 );
Table created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE initClob(clob_par IN OUT CLOB,id_par IN INTEGER) IS
2 BEGIN 3 SELECT clobData INTO clob_par FROM myTable WHERE id = id_par; 4 END initClob; 5 /
Procedure created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE readClob(id_par IN INTEGER) IS
2 clobVariable CLOB;
3 charVariable VARCHAR2(50);
4 offsetPos INTEGER := 1;
5 amount_var INTEGER := 50;
6 BEGIN
7 initClob(clobVariable, id_par);
8 DBMS_LOB.READ(clobVariable, amount_var, offsetPos, charVariable);
9 DBMS_OUTPUT.PUT_LINE("charVariable = " || charVariable);
10 DBMS_OUTPUT.PUT_LINE("amount_var = " || amount_var);
11 END readClob;
12 /
Procedure created. SQL> SQL> CREATE OR REPLACE PROCEDURE copy_example IS
2 clobSrc CLOB; 3 clobDest CLOB; 4 src_offsetPos INTEGER := 1; 5 dest_offsetPos INTEGER := 7; 6 amount_var INTEGER := 5; 7 BEGIN 8 SELECT clobData INTO clobSrc FROM myTable WHERE id = 2; 9 SELECT clobData INTO clobDest FROM myTable WHERE id = 1 FOR UPDATE; 10 11 readClob(1); 12 DBMS_LOB.COPY(clobDest, clobSrc, amount_var,dest_offsetPos, src_offsetPos); 13 readClob(1); 14 15 ROLLBACK; 16 END copy_example; 17 /
Procedure created. SQL> SQL> SQL> drop table myTable; Table dropped.
</source>
Select into and subquery
<source lang="sql">
SQL> create table myTable ( x number, y char(100)); Table created. SQL> SQL> create or replace procedure explicit is
2 cursor explicit_cur is select x from myTable order by y desc; 3 dummy number; 4 begin 5 for i in 1 .. 500 loop 6 open explicit_cur; 7 fetch explicit_cur into dummy; 8 close explicit_cur; 9 end loop; 10 end; 11 /
Procedure created. SQL> SQL> create or replace procedure implicit is
2 dummy number; 3 begin 4 for i in 1 .. 500 loop 5 select x into dummy from ( select x from myTable order by y desc ) where rownum = 1; 6 end loop; 7 end; 8 /
Procedure created. SQL> SQL> drop table myTable; Table dropped.
</source>
SELECT into value pair
<source lang="sql">
SQL> SQL> CREATE TABLE session (
2 department CHAR(3), 3 course NUMBER(3), 4 description VARCHAR2(2000), 5 max_lecturer NUMBER(3), 6 current_lecturer NUMBER(3), 7 num_credits NUMBER(1), 8 room_id NUMBER(5) 9 );
Table created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created. SQL> SQL> SQL> select * from session; DEP COURSE --- -------- DESCRIPTION
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
---------------- ----------- --------
HIS 101.00 History 101
30.00 11.00 4.00 ########
HIS 301.00 History 301
30.00 .00 4.00 ########
DEP COURSE --- -------- DESCRIPTION
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
---------------- ----------- --------
CS 101.00 Computer Science 101
50.00 .00 4.00 ########
ECN 203.00 Economics 203 DEP COURSE --- -------- DESCRIPTION
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
---------------- ----------- --------
15.00 .00 3.00 ########
CS 102.00 Computer Science 102
35.00 3.00 4.00 ########
MUS 410.00 DEP COURSE --- -------- DESCRIPTION
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
---------------- ----------- --------
Music 410
5.00 4.00 3.00 ########
ECN 101.00 Economics 101
50.00 .00 4.00 ########
DEP COURSE --- -------- DESCRIPTION
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
---------------- ----------- --------
NUT 307.00 Nutrition 307
20.00 2.00 4.00 ########
MUS 100.00 Music 100
100.00 .00 3.00
DEP COURSE --- -------- DESCRIPTION
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
---------------- ----------- --------
9 rows selected. SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> select * from lecturer;
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
-------------------- -------------------- ------------------------------ ---------------
- Scott Lawson Computer Science 11.00
- Mar Wells History 4.00
- Jone Bliss Computer Science 8.00
- Man Kyte Economics 8.00
- Pat Poll History 4.00
- Tim Viper History 4.00
- Barbara Blues Economics 7.00
- David Large Music 4.00
- Chris Elegant Nutrition 8.00
- Rose Bond Music 7.00
- Rita Johnson Nutrition 8.00
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
-------------------- -------------------- ------------------------------ ---------------
- Sharon Clear Computer Science 3.00
12 rows selected. SQL> SQL> DECLARE
2 myLecturerRecord lecturer%ROWTYPE; 3 v_Department session.department%TYPE; 4 v_Course session.course%TYPE; 5 BEGIN 6 SELECT * 7 INTO myLecturerRecord 8 FROM lecturer 9 WHERE id = 10000; 10 11 SELECT department, course 12 INTO v_Department, v_Course 13 FROM session 14 WHERE room_id = 20003; 15 END; 16 /
DECLARE
ERROR at line 1: ORA-01403: no data found ORA-06512: at line 6
SQL> SQL> drop table session; Table dropped. SQL> drop table lecturer; Table dropped. SQL> SQL>
</source>
Select single value into variable
<source lang="sql">
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created.
SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(6, "Black", "Designer", 9,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created.
SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created.
SQL> SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> declare
2 x number; 3 begin 4 select object_id into x from all_objects where rownum <= 1; 5 end; 6 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped.
</source>
Select the number of employees into the l_emp_count variable
<source lang="sql">
SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> SQL> declare
2 l_emp_count number;
3 i number; -- We will use this as our counter
4 begin
5 select count(*) into l_emp_count from lecturer;
6
7 FOR i IN 1 .. l_emp_count LOOP
8 dbms_output.put_line("Employee " || i);
9 END LOOP;
10 end;
11 /
Employee 1 Employee 2 Employee 3 Employee 4 Employee 5 Employee 6 Employee 7 Employee 8 Employee 9 Employee 10 Employee 11 Employee 12 PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table lecturer; Table dropped.
</source>
Select two columns into a cursor variable
<source lang="sql">
SQL> CREATE TABLE products(
2 name VARCHAR2(50), 3 price NUMBER(8,2), 4 min_price NUMBER(8,2) 5 );
Table created. SQL> SQL> SQL> create or replace procedure print_products
2 as 3 cursor dataCursor is select name, price from products; 4 begin 5 for i in dataCursor LOOP 6 if i.price > 50 then 7 dbms_output.put_line(i.name ||" Price: "|| i.price); 8 else 9 dbms_output.put_line(i.name || " Product under 50"); 10 end if; 11 END LOOP; 12 end; 13 /
Procedure created. SQL> SQL> describe print_products PROCEDURE print_products SQL> SQL> SQL> drop table products; Table dropped. SQL> SQL>
</source>
Select value from aggregate function to variable
<source lang="sql">
SQL> -- create demo table SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 fname VARCHAR2(10 BYTE), 4 lname VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> SQL> SQL> -- prepare data SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> SQL> SQL> declare
2 empCount number;
3 i number;
4 begin
5 select count(*) into empCount from emp;
6
7 FOR i IN 1 .. empCount LOOP
8 dbms_output.put_line("emp " || i);
9 END LOOP;
10 end;
11 /
emp 1 emp 2 PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped. SQL> SQL>
</source>
Select value from table into variable
<source lang="sql">
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> select * from emp; Hit a key to continue
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected. SQL> SQL> DECLARE
2 myName emp.ename%TYPE := "Joe";
3 BEGIN
4 SELECT ename
5 INTO myName
6 FROM emp
7 WHERE ename = "JAMES";
8
9 DBMS_OUTPUT.PUT_LINE("Name is "||myName);
10 END;
11 /
Name is JAMES PL/SQL procedure successfully completed. SQL> SQL> select * from emp; Hit a key to continue
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected. SQL> drop table emp; Table dropped. SQL> SQL> SQL>
</source>
Select value into a number variable in a for loop
<source lang="sql">
SQL> SQL> create table myTable(
2 x number, 3 y char(100) 4 );
Table created. SQL> SQL> insert into myTable
2 select rownum, "padding" 3 from all_objects 4 where rownum < 10001;
10000 rows created. SQL> SQL> SQL> create or replace procedure implicit is
2 dummy number; 3 begin 4 for i in 1 .. 500 loop 5 select 1 6 into dummy 7 from myTable 8 where x = 1; 9 end loop; 10 end; 11 /
Procedure created. SQL> SQL> SQL> SQL> SQL> drop table myTable; Table dropped.
</source>
Select value to variable one by one
<source lang="sql">
SQL> SQL> create table emp(
2 emp_id integer primary key 3 ,lastname varchar2(20) not null 4 ,firstname varchar2(15) not null 5 ,midinit varchar2(1) 6 ,street varchar2(30) 7 ,city varchar2(20) 8 ,state varchar2(2) 9 ,zip varchar2(5) 10 ,shortZipCode varchar2(4) 11 ,area_code varchar2(3) 12 ,phone varchar2(8) 13 ,company_name varchar2(50));
Table created. SQL> SQL> SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (1,"Jones","Joe","J","1 Ave","New York","NY","11202","1111","212", "221-4333","Big Company");
1 row created. SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (2,"Smith","Sue","J","1 Street","New York","NY","11444","1111","212", "436-6773","Little Company");
1 row created. SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (3,"X","Peggy","J","1 Drive","New York","NY","45502","2222","212", "234-4444","Medium Company");
1 row created. SQL> SQL> declare
2 v1 number; 3 begin 4 for x in 1..100 loop 5 select emp_id into v1 from emp 6 where emp_id = 1; 7 end loop; 8 end; 9 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table emp; Table dropped.
</source>
Store max(salary) to a variable
<source lang="sql">
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created.
SQL> create or replace procedure WITHOUT_TYPE is
2 v_salary number(7,2); 3 begin 4 select max(sal) into v_salary from emp; 5 end; 6 /
Procedure created. SQL> SQL> SQL> drop table emp; Table dropped.
</source>
Store max(tableName.column) to tableName.column.type variable
<source lang="sql">
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created.
SQL> create or replace procedure WITH_TYPE is
2 v_salary emp.sal%type; 3 begin 4 select max(sal) into v_salary from emp; 5 end; 6 /
Procedure created. SQL> SQL> drop table emp; Table dropped. SQL>
</source>
This script demonstrates how to do a non-bulk select into elements of a PL/SQL table.
<source lang="sql">
SQL> CREATE TABLE myTable
2 (id INTEGER NOT NULL 3 ,CONSTRAINT id_pk PRIMARY KEY (id));
Table created. SQL> SQL> SQL> SQL> SQL> SQL> DECLARE
2 3 4 TYPE number_table IS TABLE OF myTable.id%TYPE INDEX BY BINARY_INTEGER; 5 6 7 number_list NUMBER_TABLE; 8 9 BEGIN 10 11 12 FOR i IN 1..10000 LOOP 13 INSERT INTO myTable VALUES (i); 14 END LOOP; 15 16 END; 17 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL> SQL>
</source>
TOO_MANY_ROWS exception and select into command
<source lang="sql">
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY, 3 fname VARCHAR2(50), 4 lname VARCHAR2(50) 5 );
Table created. SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z"); 1 row created. SQL> SQL> SQL> SET SERVEROUTPUT ON ESCAPE OFF SQL> SQL> DECLARE
2 v_emp emp.fname%TYPE;
3 BEGIN
4
5
6
7 BEGIN
8 SELECT fname INTO v_emp FROM emp WHERE UPPER(lname) = "H";
9
10 EXCEPTION
11 WHEN TOO_MANY_ROWS
12 THEN
13 DBMS_OUTPUT.PUT_LINE(" ");
14 DBMS_OUTPUT.PUT_LINE("EXCEPTION HANDLER for nested block 2");
15 DBMS_OUTPUT.PUT_LINE("If this is printing, then the both nested");
16 DBMS_OUTPUT.PUT_LINE("blocks"" exception handler worked!");
17 END;
18
19 END;
20 /
PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped. SQL>
</source>
Use subquery in pl/sql block
<source lang="sql">
SQL> create table myTable
2 as 3 select rownum id, a.* 4 from all_objects a 5 where 1=0 6 /
SQL> SQL> create or replace procedure dw_style
2 as 3 i number; 4 begin 5 select count(*) into i 6 from ( 7 select 8 t1.data_object_id, myTable2.data_object_id 9 from myTable t1, myTable myTable2 10 where t1.id = myTable2.id 11 ); 12 end; 13 /
SQL> SQL> drop table myTable;
</source>
