Oracle PL/SQL/PL SQL/Update Data

Материал из SQL эксперт
Перейти к: навигация, поиск

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

-------------------- -------------------- ------------------------------ ---------------
                1. Scott Lawson Computer Science 11.00
                2. Mar Wells History 4.00
                3. Jone Bliss Computer Science 8.00
                4. Man Kyte Economics 8.00
                5. Pat Poll History 4.00
                6. Tim Viper History 4.00
                7. Barbara Blues Economics 7.00
                8. David Large Music 4.00
                9. Chris Elegant Nutrition 8.00
                10. Rose Bond Music 7.00
                11. Rita Johnson Nutrition 8.00
     ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS

-------------------- -------------------- ------------------------------ ---------------
                1. 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

-------------------- -------------------- ------------------------------ ---------------
                1. Scott Lawson Computer Science 11.00
                2. Mar Wells History 7.00
                3. Jone Bliss Computer Science 8.00
                4. Man Kyte Economics 8.00
                5. Pat Poll History 7.00
                6. Tim Viper History 7.00
                7. Barbara Blues Economics 7.00
                8. David Large Music 4.00
                9. Chris Elegant Nutrition 8.00
                10. Rose Bond Music 7.00
                11. Rita Johnson Nutrition 8.00
     ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS

-------------------- -------------------- ------------------------------ ---------------
                1. 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>