Oracle PL/SQL/PL SQL/Update Data
Содержание
- 1 Ajust price based on price range
- 2 Bundle several update and insert statements into one procedure
- 3 Change price and output the result
- 4 Check row count being updating
- 5 Check SQL%ROWCOUNT after updating
- 6 Decrease salary with user procedure
- 7 Exception handling for update statement
- 8 Run an anonymous block that updates the number of book IN STOCK
- 9 Run an anonymous block that updates the number of pages for this book
- 10 Run the anonymous block to update the position column
- 11 Select for update
- 12 Two UPDATE statements.
- 13 Update salary with stored procedure
- 14 UPDATE statement can be used within PL/SQL programs to update a row or a set of rows
- 15 Update table and return if success
- 16 Update with variable
- 17 Use procedure to update table
Ajust price based on price range
<source lang="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> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("4", "Database", "SQL", 535, 39.99, 2002, 4, 5, 9);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
2 VALUES ("5", "Database", "Java", 487, 39.99, 2002, 10, 11);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
2 VALUES ("6", "Database", "JDBC", 592, 39.99, 2002, 12, 13);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("7", "Database", "XML", 500, 39.99, 2002, 1, 2, 3);
1 row created. SQL> SQL> SQL> DECLARE
2 v_price books.price%TYPE; 3 v_isbn books.isbn%TYPE := "3"; 4 BEGIN 5 SELECT price INTO v_price FROM books WHERE isbn = v_isbn; 6 7 DBMS_OUTPUT.PUT_LINE("Starting price: "||v_price); 8 9 IF v_price < 40 10 THEN 11 DBMS_OUTPUT.PUT_LINE("This book is already discounted"); 12 ELSIF v_price BETWEEN 40 AND 50 13 THEN 14 v_price := v_price - (v_price * .10); 15 UPDATE books SET price = v_price WHERE isbn = v_isbn; 16 ELSIF v_price > 50 17 THEN 18 v_price := v_price - (v_price * .10); 19 UPDATE books SET price = v_price WHERE isbn = v_isbn; 20 END IF; 21 22 DBMS_OUTPUT.PUT_LINE("Ending price: "||v_price); 23 24 ROLLBACK; 25 EXCEPTION 26 WHEN OTHERS 27 THEN 28 DBMS_OUTPUT.PUT_LINE(SQLERRM); 29 ROLLBACK; 30 END; 31 /
Starting price: 39.99 This book is already discounted Ending price: 39.99 PL/SQL procedure successfully completed. SQL> drop table books; Table dropped. SQL>
</source>
Bundle several update and insert statements into one procedure
<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> alter table dept add tot_sal number; Table altered. SQL> SQL> update dept set tot_sal = ( select sum(sal) from emp where deptno = dept.deptno ); 4 rows updated.
SQL> SQL> create table EMP_AUDIT (date_rec date, empno number, sal number(4)); Table created. SQL> SQL> insert into emp_deltas select empno, null from emp; 10 rows created.
SQL> SQL> create or replace procedure UPDATE_EMP(id number, p_sal number) is
2 begin 3 update DEPT set TOT_SAL = TOT_SAL + ( select p_sal-sal from EMP where empno = id ); 4 5 update EMP set sal = p_sal where empno = id; 6 7 update EMP_DELTAS set change_type = "SAL" where empno = id; 8 9 insert into EMP_AUDIT values (sysdate,id,p_sal); 10 11 exception 12 when others then 13 rollback; 14 raise; 15 end; 16 /
Procedure created. SQL> SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped.
</source>
Change price and output the result
<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> SET SERVEROUTPUT ON ESCAPE OFF SQL> SQL> DECLARE
2 v_price BOOKS.PRICE%TYPE; 3 BEGIN 4 5 SELECT price INTO v_price FROM books WHERE isbn = "3"; 6 7 DBMS_OUTPUT.PUT_LINE("The original price for isbn 3 was: "||v_price); 8 9 v_price := v_price * .9; 10 11 UPDATE books SET price = v_price WHERE isbn = "3"; 12 13 DBMS_OUTPUT.PUT_LINE(CHR(0)); 14 DBMS_OUTPUT.PUT_LINE("The discounted price for isbn 3 is: "||v_price); 15 16 EXCEPTION 17 WHEN OTHERS 18 THEN DBMS_OUTPUT.PUT_LINE (SQLERRM); 19 END; 20 /
The original price for isbn 3 was: 39.99 The discounted price for isbn 3 is: 35.991 PL/SQL procedure successfully completed. SQL> drop table books; Table dropped. SQL>
</source>
Check row count being updating
<source lang="sql">
SQL> SQL> 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> 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>
</source>
Check SQL%ROWCOUNT after updating
<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"), null, NULL, 10); 1 row created. SQL> SQL> select sal from emp;
SAL
800 1600 1250 2975 1250 2850 2450 3000 5000 1500 1100 SAL
950 3000
14 rows selected. SQL> SQL> BEGIN
2 UPDATE emp 3 SET sal = nvl(sal, 0) * 1.10; 4 DBMS_OUTPUT.PUT_LINE("Number of Rows Updated: " || 5 SQL%ROWCOUNT); 6 END; 7 /
Number of Rows Updated: 14 PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped. SQL> SQL> SQL> SQL> SQL>
</source>
Decrease salary with user procedure
<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> SQL> select * from emp; Enter...
2 Jack Tester 6 20-02-1981 1600 300 30 3 Wil Tester 6 22-02-1981 1250 500 30 4 Jane Designer 9 02-04-1981 2975 [N/A] 20 5 Mary Tester 6 28-09-1981 1250 1400 30 7 Chris Designer 9 09-06-1981 2450 [N/A] 10 8 Smart Helper 4 09-12-1982 3000 [N/A] 20 9 Peter Manager [N/A] 17-11-1981 5000 [N/A] 10 10 Take Tester 6 08-09-1981 1500 0 30 13 Fake Helper 4 03-12-1981 3000 [N/A] 20
9 rows selected. SQL> create or replace
2 procedure UPDATE_EMP(p_empno number, p_decrease number) is 3 begin 4 update EMP 5 set SAL = SAL / p_decrease 6 where empno = p_empno; 7 end; 8 /
Procedure created. SQL> exec UPDATE_EMP(1,2); PL/SQL procedure successfully completed. SQL> exec UPDATE_EMP(1,0); PL/SQL procedure successfully completed. SQL> SQL> select * from emp; Enter...
2 Jack Tester 6 20-02-1981 1600 300 30 3 Wil Tester 6 22-02-1981 1250 500 30 4 Jane Designer 9 02-04-1981 2975 [N/A] 20 5 Mary Tester 6 28-09-1981 1250 1400 30 7 Chris Designer 9 09-06-1981 2450 [N/A] 10 8 Smart Helper 4 09-12-1982 3000 [N/A] 20 9 Peter Manager [N/A] 17-11-1981 5000 [N/A] 10 10 Take Tester 6 08-09-1981 1500 0 30 13 Fake Helper 4 03-12-1981 3000 [N/A] 20
9 rows selected. SQL> drop table emp; Table dropped.
</source>
Exception handling for update 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-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
Page 1 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
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
Page 2
14 rows selected. SQL> BEGIN
2 3 UPDATE emp 4 SET sal= nvl(sal, 0) * 1.10; 5 DBMS_OUTPUT.PUT_LINE("Update Process Succeeded."); 6 EXCEPTION 7 WHEN OTHERS THEN 8 9 DBMS_OUTPUT.PUT_LINE("Update Process Failed."); 10 ROLLBACK; 11 END; 12 /
Update Process Succeeded. PL/SQL procedure successfully completed. SQL> SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 880 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1760 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1375 500 30 7566 JONES MANAGER 7839 02-APR-81 3272.5 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1375 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 3135 30 7782 CLARK MANAGER 7839 09-JUN-81 2695 10 7788 SCOTT ANALYST 7566 09-DEC-82 3300 20
Page 1 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5500 10 7844 TURNER SALESMAN 7698 08-SEP-81 1650 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1210 20 7900 JAMES CLERK 7698 03-DEC-81 1045 30 7902 FORD ANALYST 7566 03-DEC-81 3300 20 7934 MILLER CLERK 7782 23-JAN-82 1430 10
Page 2
14 rows selected. SQL> SQL> SQL> drop table emp; Table dropped. SQL>
</source>
Run an anonymous block that updates the number of book IN STOCK
<source lang="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> CREATE TABLE inventory (
2 isbn CHAR(10), 3 status VARCHAR2(25) CHECK (status IN ("IN STOCK", "BACKORDERED", "FUTURE")), 4 status_date DATE, 5 amount NUMBER 6 );
SQL> SQL> INSERT INTO inventory (isbn, status, status_date, amount)VALUES ("1", "BACKORDERED", TO_DATE("06-JUN-2004", "DD-MON-YYYY"), 1000);
SQL> INSERT INTO inventory (isbn, status, status_date, amount)VALUES ("2", "IN STOCK", NULL, 5000);
SQL> INSERT INTO inventory (isbn, status, status_date, amount)VALUES ("3", "IN STOCK", NULL, 1000);
SQL> SQL> SQL> DECLARE
2 3 v_isbn INVENTORY.ISBN%TYPE; 4 v_amount INVENTORY.AMOUNT%TYPE; 5 6 CURSOR inventory_cur IS SELECT isbn, amount FROM inventory 7 WHERE status = "IN STOCK" 8 AND isbn IN (SELECT isbn FROM book) 9 FOR UPDATE OF amount; 10 11 BEGIN 12 13 FOR y IN inventory_cur 14 LOOP 15 FETCH inventory_cur INTO v_isbn, v_amount; 16 EXIT WHEN inventory_cur%NOTFOUND; 17 18 DBMS_OUTPUT.PUT_LINE(v_isbn||"Amount IN STOCK before: "||v_amount); 19 20 v_amount := v_amount + 50; 21 22 UPDATE inventory SET amount = v_amount WHERE CURRENT OF inventory_cur; 23 24 DBMS_OUTPUT.PUT_LINE(v_isbn||"Amount IN STOCK after: "||v_amount); 25 26 END LOOP; 27 28 COMMIT; 29 30 EXCEPTION 31 WHEN OTHERS 32 THEN 33 DBMS_OUTPUT.PUT_LINE(SQLERRM); 34 ROLLBACK; 35 END; 36 /
SQL> drop table book; Table dropped.
</source>
Run an anonymous block that updates the number of pages for this book
<source lang="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> DECLARE
2 3 v_num_pages book.NUM_PAGES%TYPE; 4 v_isbn book.ISBN%TYPE := "3"; 5 6 BEGIN 7 8 SELECT num_pages INTO v_num_pages FROM book WHERE isbn = v_isbn; 9 10 DBMS_OUTPUT.PUT_LINE("Number of pages before: "||v_num_pages); 11 12 v_num_pages := v_num_pages + 200; 13 14 UPDATE book SET num_pages = v_num_pages WHERE isbn = v_isbn; 15 16 DBMS_OUTPUT.PUT_LINE("Number of pages after: "||v_num_pages); 17 18 COMMIT; 19 20 EXCEPTION 21 WHEN OTHERS 22 THEN 23 DBMS_OUTPUT.PUT_LINE(SQLERRM); 24 ROLLBACK; 25 END; 26 /
Number of pages before: 404 Number of pages after: 604 PL/SQL procedure successfully completed. SQL> SQL> drop table book; Table dropped.
</source>
Run the anonymous block to update the position column
<source lang="sql">
SQL> CREATE TABLE book (
2 isbn VARCHAR2(10) PRIMARY KEY, 3 parent_isbn VARCHAR2(10), 4 series VARCHAR2(20), 5 category VARCHAR2(20), 6 title VARCHAR2(100), 7 num_pages NUMBER, 8 price NUMBER, 9 copyright NUMBER(4));
SQL> SQL> INSERT INTO book (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
2 VALUES ("1", "2", "Oracle", "Oracle Server", "SQL", 664, 49.99, 2002);
SQL> SQL> INSERT INTO book (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
2 VALUES ("2", null, "Oracle", "Oracle Server", "Java", 772, 49.99, 2000);
SQL> SQL> INSERT INTO book (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
2 VALUES ("3", "1", "Oracle", "Oracle Server", "XML", 1008, 54.99, 2004);
SQL> SQL> INSERT INTO book (isbn, parent_isbn, series, category, title, num_pages, price, copyright)
2 VALUES ("4", null, "Oracle Ebusiness", "Oracle Ebusiness", "Oracle E-Business Suite Financials Handbook", 820, 59.99, 2002);
SQL> SQL> SQL> ALTER TABLE book
2 ADD position NUMBER(10);
SQL> SQL> PROMPT SQL> SQL> DECLARE
2 v_level PLS_INTEGER; 3 v_title book.TITLE%TYPE; 4 5 CURSOR cur_tree IS SELECT isbn, title, series FROM book; 6 BEGIN 7 8 FOR l IN cur_tree 9 LOOP 10 11 SELECT max(LEVEL) INTO v_level FROM book START WITH isbn = l.isbn CONNECT BY PRIOR parent_isbn = isbn; 12 13 UPDATE book SET position = v_level WHERE isbn = l.isbn; 14 15 END LOOP; 16 17 COMMIT; 18 19 EXCEPTION 20 WHEN OTHERS 21 THEN 22 DBMS_OUTPUT.PUT_LINE(sqlerrm); 23 END; 24 /
PL/SQL procedure successfully completed. SQL> SQL> SELECT title, position FROM book ORDER BY series, position; TITLE
POSITION
Oracle8.0 PL/SQL Programming
1
Java
2
SQL
3
XML
4
Oracle E-Business Suite Financials Handbook
1
5 rows selected.
SQL> drop table book; Table dropped.
</source>
Select for update
<source lang="sql">
SQL> SQL> create table my_data(
2 id number, 3 data varchar2(100) );
Table created. SQL> SQL> insert into my_data
2 select rownum, to_char( to_date( rownum, "J" ), "JSP" ) from all_objects 3 where rownum < 6;
5 rows created. SQL> SQL> select * from my_data;
ID DATA
----------------------------------------------------------------------------------------------------
1 ONE 2 TWO 3 THREE 4 FOUR 5 FIVE
SQL> insert into my_data values ( 6, "SIC" ); 1 row created. SQL> SQL> declare
2 l_row my_data%rowtype; 3 begin 4 select * into l_row from my_data where id = 6 for update; 5 update my_data set data = "SIX" where id = 6; 6 commit; 7 exception 8 when NO_DATA_FOUND then 9 insert into my_data values ( 6, "SIX" ); 10 end; 11 /
PL/SQL procedure successfully completed. SQL> SQL> select * from my_data;
ID DATA
----------------------------------------------------------------------------------------------------
1 ONE 2 TWO 3 THREE 4 FOUR 5 FIVE 6 SIX
6 rows selected. SQL> SQL> drop table my_data; Table dropped. SQL>
</source>
Two UPDATE statements.
<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> 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> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> DECLARE
2 v_Major lecturer.major%TYPE; 3 v_CreditIncrease NUMBER := 3; 4 BEGIN 5 v_Major := "History"; 6 UPDATE lecturer 7 SET current_credits = current_credits + v_CreditIncrease 8 WHERE major = v_Major; 9 10 UPDATE MyTable 11 SET num_col = 1, char_col = "abcd"; 12 END; 13 /
PL/SQL procedure successfully completed. SQL> SQL> select * from lecturer;
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
-------------------- -------------------- ------------------------------ ---------------
- Scott Lawson Computer Science 11.00
- Mar Wells History 7.00
- Jone Bliss Computer Science 8.00
- Man Kyte Economics 8.00
- Pat Poll History 7.00
- Tim Viper History 7.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> select * from MyTable; no rows selected SQL> SQL> SQL> drop table lecturer; Table dropped. SQL> SQL> drop table MyTable; Table dropped. SQL>
</source>
Update salary with stored procedure
<source lang="sql">
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 OR REPLACE PROCEDURE update_sal(id in number,p_sal in number)
2 authid current_user 3 AS 4 BEGIN 5 update EMP set sal = p_sal where empno = id; 6 END; 7 /
Procedure created. SQL> SQL> drop procedure update_sal; Procedure dropped. SQL> drop table emp; Table dropped.
</source>
UPDATE statement can be used within PL/SQL programs to update a row or a set of rows
<source lang="sql">
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 /
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> SQL> SQL> SQL> --UPDATE statement can be used within PL/SQL programs to update a row or a set of rows with a new -- SQL> --value. SQL> SQL> BEGIN
2 UPDATE employee 3 SET salary = salary * 2; 4 END; 5 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> select * from employee; 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 2469.12 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 13323.56 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 13089.56 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 4689.56 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 4669.56 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 8645.56 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 15795.56 New York Manager 08 James Cat 17-SEP-96 15-APR-02 2465.56 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL>
</source>
Update table and return if success
<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> select * from emp; Enter...
2 Jack Tester 6 20-02-1981 1600 300 30 3 Wil Tester 6 22-02-1981 1250 500 30 4 Jane Designer 9 02-04-1981 2975 [N/A] 20 5 Mary Tester 6 28-09-1981 1250 1400 30 7 Chris Designer 9 09-06-1981 2450 [N/A] 10 8 Smart Helper 4 09-12-1982 3000 [N/A] 20 9 Peter Manager [N/A] 17-11-1981 5000 [N/A] 10 10 Take Tester 6 08-09-1981 1500 0 30 13 Fake Helper 4 03-12-1981 3000 [N/A] 20
9 rows selected. SQL> create or replace procedure UPDATE_EMP(id number, val number,isSuccess out boolean) is
2 begin 3 if val = 0 then 4 isSuccess := false; 5 else 6 update EMP set SAL = SAL / val where empno = id; 7 isSuccess := true; 8 end if; 9 end; 10 /
Procedure created. SQL> SQL> SQL> drop table emp; Table dropped. SQL>
</source>
Update with variable
<source lang="sql">
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> 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> DECLARE
2 v_NumCredits session.num_credits%TYPE; 3 BEGIN 4 v_NumCredits := 3; 5 UPDATE session 6 SET num_credits = v_NumCredits 7 WHERE department = "HIS" 8 AND course = 101; 9 END; 10 /
PL/SQL procedure successfully completed. 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 3.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> drop table session; Table dropped.
</source>
Use procedure to update table
<source lang="sql">
SQL> SQL> SQL> SQL> CREATE TABLE Department (
2 DepartmentID INT NOT NULL PRIMARY KEY, 3 Name VARCHAR(50) NOT NULL, 4 Description VARCHAR(200) NULL);
Table created. SQL> SQL> CREATE SEQUENCE DepartmentIDSeq; Sequence created. SQL> SQL> CREATE OR REPLACE TRIGGER DepartmentAutonumberTrigger
2 BEFORE INSERT ON Department 3 FOR EACH ROW 4 BEGIN 5 SELECT DepartmentIDSeq.NEXTVAL 6 INTO :NEW.DepartmentID FROM DUAL; 7 END; 8 /
Trigger created. SQL> SQL> INSERT INTO Department (Name, Description)
2 VALUES ("Software", "Coding");
1 row created. SQL> INSERT INTO Department (Name, Description)
2 VALUES ("Hardware", "Building");
1 row created. SQL> INSERT INTO Department (Name, Description)
2 VALUES ("QA", "Testing");
1 row created. SQL> SQL> SQL> SQL> CREATE or replace PROCEDURE UpdateDepartment
2 (DeptID IN integer, 3 DepartmentName IN varchar2, 4 DepartmentDescription IN varchar2) 5 6 AS 7 BEGIN 8 UPDATE Department 9 SET Name = DepartmentName, 10 Description = DepartmentDescription 11 WHERE DepartmentID = DeptID; 12 END; 13 /
Procedure created. SQL> SQL> show errors No errors. SQL> SQL> EXECUTE UpdateDepartment (1, "Strange new name", "Strange new description"); PL/SQL procedure successfully completed. SQL> SELECT * FROM Department; DEPARTMENTID NAME
--------------------------------------------------
DESCRIPTION
1 Strange new name
Strange new description
2 Hardware
Building
3 QA
Testing
SQL> SQL> SQL> drop sequence DepartmentIDSeq; Sequence dropped. SQL> drop table Department; Table dropped. SQL> SQL> SQL> SQL>
</source>