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.
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.
Calculate salary by adding salary with max(salary)
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.
Catch too_many_rows Exception for "Select into" statement
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> --
If no records are retrieved for a SELECT - INTO statement the following error is returned
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.
If too many records are returned for a SELECT - INTO statement the following error is returned
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.
Multiple-Row SELECT Command with Several Exception-Handling Routines
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> --
no_data_found from select ... into
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>
Oracle returns an error when a SELECT statement returns more than one row
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>
Output variable after "select into"
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>
select bulk collect into table collection
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>
Select count result into a variable
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> --
Select data for update
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.
Select into and subquery
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.
SELECT into value pair
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>
Select single value into variable
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.
Select the number of employees into the l_emp_count variable
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.
Select two columns into a cursor variable
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>
Select value from aggregate function to variable
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>
Select value from table into variable
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>
Select value into a number variable in a for loop
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.
Select value to variable one by one
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.
Store max(salary) to a variable
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.
Store max(tableName.column) to tableName.column.type variable
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>
This script demonstrates how to do a non-bulk select into elements of a PL/SQL table.
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>
TOO_MANY_ROWS exception and select into command
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>
Use subquery in pl/sql block
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;