Oracle PL/SQL/Cursor/Implicit Cursor
Содержание
- 1 Cursor for loop
- 2 explicit cursor with cursor variable
- 3 for data in ( select * from tableName )
- 4 Implicit cursor by for loop
- 5 Implicit cursors: SQL%FOUND returns TRUE if SQL statement found one or more records
- 6 Implicit Cursors: sql%rowcount
- 7 Reference implicit in insert statement
- 8 Test cursor attributes with an implicit cursor
- 9 use cursor attributes on the implicit SQL cursor.
- 10 use for loop cursor
- 11 Use implicit cursor
- 12 Use implicit or explicit cursor to insert 50000 rows to a table
- 13 Use passed in value with creating a cursor
- 14 Write an implicit cursor in a FOR loop and use the data
Cursor for loop
SQL>
SQL> CREATE TABLE departments
2 (department_id number(10) not null,
3 department_name varchar2(50) not null,
4 CONSTRAINT departments_pk PRIMARY KEY (department_id)
5 );
Table created.
SQL>
SQL>
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 1, "Data Group" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 2, "Purchasing" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 3, "Call Center" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 4, "Communication" );
1 row created.
SQL>
SQL>
SQL> set serverout on
SQL>
SQL> declare
2 begin
3 for my_dept_rec in (select department_id, department_name
4 from departments
5 order by 1)
6 loop
7 dbms_output.put("Department #" || my_dept_rec.department_id);
8 dbms_output.put_line(" is named " || my_dept_rec.department_name);
9 end loop;
10 end;
11 /
Department #1 is named Data Group
Department #2 is named Purchasing
Department #3 is named Call Center
Department #4 is named Communication
PL/SQL procedure successfully completed.
SQL>
SQL> drop table departments;
Table dropped.
SQL>
explicit cursor with cursor variable
SQL>
SQL> create table myTable (x primary key) organization index as select 1 from dual;
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> create or replace procedure explicit is
2 cursor explicit_cur is select 1 from myTable;
3 dummy number;
4 begin
5 for i in 1 .. 500 loop
6 open explicit_cur;
7 fetch explicit_cur
8 into dummy;
9 close explicit_cur;
10 end loop;
11 end;
12 /
Procedure created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
for data in ( select * from tableName )
SQL>
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>
SQL> create or replace procedure implicit
2 as
3 begin
4 for x in ( select * from dept )
5 loop
6 null;
7 end loop;
8 end;
9 /
Procedure created.
SQL>
SQL>
SQL> drop table dept;
Table dropped.
SQL>
SQL>
SQL>
Implicit cursor by for loop
SQL> create table myTable (x primary key) organization index as select 1 from dual;
Table 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 into dummy from myTable;
6 end loop;
7 end;
8 /
Procedure created.
SQL>
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
Implicit cursors: SQL%FOUND returns TRUE if SQL statement found one or more records
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name 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> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, 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 Employee(ID, First_Name, Last_Name, 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> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
Hit a key to continue
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL> -- Implicit cursors: SQL%FOUND returns TRUE if SQL statement found one or more records.
SQL>
SQL> BEGIN
2 UPDATE employee
3 SET salary = salary *2
4 WHERE id = "01";
5
6 IF SQL%FOUND THEN
7 DBMS_OUTPUT.PUT_LINE("Rows updated.");
8 END IF;
9 END;
10 /
Rows updated.
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
Implicit Cursors: sql%rowcount
SQL>
SQL> CREATE TABLE departments
2 (department_id number(10) not null,
3 department_name varchar2(50) not null,
4 CONSTRAINT departments_pk PRIMARY KEY (department_id)
5 );
Table created.
SQL>
SQL>
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 1, "Data Group" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 2, "Purchasing" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 3, "Call Center" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 4, "Communication" );
1 row created.
SQL>
SQL> declare
2 begin
3 update departments
4 set department_name = department_name
5 where 1 = 2;
6
7 dbms_output.put ("An update with a WHERE clause 1 = 2 effects ");
8 dbms_output.put_line(sql%rowcount || " records.");
9
10 update departments
11 set department_name = department_name;
12
13 dbms_output.put("No WHERE clause in an update effects ");
14 dbms_output.put_line(sql%rowcount || " records.");
15 end;
16 /
An update with a WHERE clause 1 = 2 effects 0 records.
No WHERE clause in an update effects 4 records.
PL/SQL procedure successfully completed.
SQL>
SQL> drop table departments;
Table dropped.
SQL> --
Reference implicit in insert statement
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> CREATE TABLE myStudent (
2 student_id NUMBER(5) NOT NULL,
3 department CHAR(3) NOT NULL,
4 course NUMBER(3) NOT NULL,
5 grade CHAR(1)
6 );
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "CS", 102, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "CS", 102, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "CS", 102, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10001, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10004, "HIS", 101, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10005, "HIS", 101, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10006, "HIS", 101, "E");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10007, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10008, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10009, "HIS", 101, "D");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10010, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10008, "NUT", 307, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10010, "NUT", 307, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10009, "MUS", 410, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10006, "MUS", 410, "E");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10011, "MUS", 410, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "MUS", 410, "B");
1 row created.
SQL>
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL> BEGIN
2 FOR myLecturer IN (SELECT id, first_name, last_name
3 FROM lecturer
4 WHERE major = "History") LOOP
5 INSERT INTO myStudent (student_id, department, course)
6 VALUES (myLecturer.ID, "HIS", 301);
7
8 INSERT INTO MyTable (num_col, char_col)
9 VALUES (myLecturer.ID,
10 myLecturer.first_name || " " || myLecturer.last_name);
11 END LOOP;
12 END;
13 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select * from MyTable;
NUM_COL CHAR_COL
-------- ------------------------------------------------------------
######## Mar Wells
######## Pat Poll
######## Tim Viper
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
SQL> drop table myStudent;
Table dropped.
Test cursor attributes with an implicit cursor
SQL>
SQL> CREATE TABLE book (
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 book (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 book (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 book (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> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL>
SQL> BEGIN
2 DBMS_OUTPUT.ENABLE(1000000);
3 UPDATE book SET price = price * .90 WHERE isbn = "1";
4
5 DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||" rows updated");
6
7 IF SQL%NOTFOUND
8 THEN
9 DBMS_OUTPUT.PUT_LINE("Unable to update isbn 1");
10 END IF;
11
12 COMMIT;
13
14 EXCEPTION
15 WHEN OTHERS
16 THEN
17 DBMS_OUTPUT.PUT_LINE(SQLERRM);
18 END;
19 /
1 rows updated
PL/SQL procedure successfully completed.
SQL>
SQL> drop table book;
Table dropped.
use cursor attributes on the implicit SQL cursor.
SQL>
SQL>
SQL> CREATE TABLE place (
2 room_id NUMBER(5) PRIMARY KEY,
3 building VARCHAR2(15),
4 room_number NUMBER(4),
5 number_seats NUMBER(4),
6 description VARCHAR2(50)
7 );
Table created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20001, "Building 7", 201, 1000, "Large Lecture Hall");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20002, "Building 6", 101, 500, "Small Lecture Hall");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20003, "Building 6", 150, 50, "Discussion Room A");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20004, "Building 6", 160, 50, "Discussion Room B");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20005, "Building 6", 170, 50, "Discussion Room C");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20006, "Music Building", 100, 10, "Music Practice Room");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20007, "Music Building", 200, 1000, "Concert Room");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20008, "Building 7", 300, 75, "Discussion Room D");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20009, "Building 7", 310, 50, "Discussion Room E");
1 row created.
SQL>
SQL> BEGIN
2 UPDATE place
3 SET number_seats = 100
4 WHERE room_id = 99980;
5 IF SQL%NOTFOUND THEN
6 INSERT INTO place (room_id, number_seats)
7 VALUES (99980, 100);
8 END IF;
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table place;
Table dropped.
SQL>
use for loop cursor
SQL>
SQL> CREATE TABLE departments
2 (department_id number(10) not null,
3 department_name varchar2(50) not null,
4 CONSTRAINT departments_pk PRIMARY KEY (department_id)
5 );
Table created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 1, "Data Group" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 2, "Purchasing" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 3, "Call Center" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 4, "Communication" );
1 row created.
SQL>
SQL> set serveroutput on
SQL> declare
2 begin
3 for my_dept_rec in (select department_id, department_name from departments order by 1)
4 loop
5 dbms_output.put("Department #" || my_dept_rec.department_id);
6 dbms_output.put_line(" is named " || my_dept_rec.department_name);
7 end loop;
8 end;
9 /
Department #1 is named Data Group
Department #2 is named Purchasing
Department #3 is named Call Center
Department #4 is named Communication
PL/SQL procedure successfully completed.
SQL>
SQL> DROP TABLE departments;
Table dropped.
SQL> --
Use implicit cursor
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name 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> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, 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 Employee(ID, First_Name, Last_Name, 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> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL> set serverout on;
SQL>
SQL> -- implicit cursor
SQL>
SQL> declare
2 begin
3 update employee set first_name = last_name where 1 = 2;
4
5 dbms_output.put ("An update with a WHERE clause 1 = 2 effects ");
6 dbms_output.put_line(sql%rowcount || " records.");
7
8 update employee set first_name = last_name;
9
10 dbms_output.put("No WHERE clause in an update effects ");
11 dbms_output.put_line(sql%rowcount || " records.");
12 end;
13 /
An update with a WHERE clause 1 = 2 effects 0 records.
No WHERE clause in an update effects 8 records.
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
Use implicit or explicit cursor to insert 50000 rows to a table
SQL> create table myTable ( x primary key )
2 organization index as select 1 from dual;
Table created.
SQL> create or replace procedure implicit is
2 dummy number;
3 begin
4 for i in 1 .. 50000 loop
5 select 1 into dummy from myTable;
6 end loop;
7 end;
8 /
Procedure created.
SQL> create or replace procedure explicit is
2 cursor explicit_cur is select 1 from myTable;
3 dummy number;
4 begin
5 for i in 1 .. 50000 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> drop table myTable;
Table dropped.
Use passed in value with creating a cursor
SQL> CREATE TABLE products(
2 product_id NUMBER(6),
3 name VARCHAR2(50),
4 price NUMBER(8,2),
5 min_price NUMBER(8,2)
6 );
Table created.
SQL>
SQL> create or replace function GetProductTaxIn (in_product_id number) return number
2 is
3 priceValue number;
4 cursor dataCursor is select nvl(round(price * 1.15,2),0) from products where product_id = in_product_id;
5 begin
6 open dataCursor;
7 fetch dataCursor into priceValue;
8 return priceValue;
9 exception
10 when others then priceValue := 0;
11 return priceValue;
12 end;
13 /
Function created.
SQL>
SQL>
SQL>
SQL> select product_id, price, GetProductTaxIn(product_id)
2 from products
3 where GetProductTaxIn(product_id)>= 500
4
SQL>
SQL> drop table products;
Table dropped.
SQL>
SQL>
Write an implicit cursor in a FOR loop and use the data
SQL> BEGIN
2 FOR i IN (SELECT item_title FROM item) LOOP
3 dbms_output.put_line("The title is ["||i.item_title||"]");
4 END LOOP;
5 END;
6 /
SQL>