[bbk4980]第07集 Chapter 04 Interacting with Oracle Database Server:SQL Statements in PL/SQL Programs(02)

1、在一个pl/sql block中，可以有多个transaction，一个transaction可以跨越多个PL/SQL block;

SQL> begin
  2  insert into tt values(1);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select * from tt;

        ID
----------
         0
         1

SQL> select addr from v$transaction;

ADDR
----------------
000000007695F508

DML and Records

You can use records inside INSERT and UPDATE statements.

DECLARE
        my_book books%ROWTYPE;
BEGIN
        my_book.isbn := '1-234234-213123';
        my_book.title := 'Oracle PL/SQL Programming 5th';
        my_book.summary := 'General user guide';
        my_book.author := 'Steven';
        my_book.page_count := 1000;

        INSERT INTO books VALUES my_book;

        COMMIT;
END;

/

SELECT * FROM books;

DECLARE
        my_book books%ROWTYPE;
BEGIN
        my_book.isbn := '1-234234-213123';
        my_book.title := 'book title';
        my_book.summary := 'hell world';
        my_book.author := 'arcerzhang';
        my_book.page_count := 2000;


        UPDATE books SET ROW = my_book WHERE isbn = my_book.isbn;

        COMMIT;
END;

/

SELECT * FROM books;

Deleting Data : Example

Delete rows that belong to department 10 from the employees table.

SET SERVEROUT ON;

DECLARE
        deptno  employees.department_id%TYPE := 10;
BEGIN
        DELETE FROM dept WHERE department_id = deptno;

        COMMIT;
END;

/

SELECT * FROM dept;

Merging Rows

Insert or update rows in the copy_emp table to match the employees table.

BEGIN

INSERT INTO T1 VALUES(0,'a');
INSERT INTO T1 VALUES(1,'b');
INSERT INTO T1 VALUES(3,'d');

COMMIT;

END;

/

SELECT * FROM T1;

/*
        -------------------------------------------------------------------------------------------------
*/

DROP TABLE T2;

CREATE TABLE T2 AS SELECT * FROM T1 WHERE 1=2;

BEGIN

--CREATE TABLE T2 AS SELECT * FROM T1 WHERE 1=2;
INSERT INTO T2 VALUES(2,'c');
INSERT INTO T2 VALUES(4,'e');
INSERT INTO T2 VALUES(1,'e');
INSERT INTO T2 VALUES(5,'f');

COMMIT;

END;

/

SELECT * FROM T2;

/*
        -------------------------------------------------------------------------------------------------
*/

BEGIN
        MEGER INTO T1 a
        USING T2 b
        ON(a.id = b.id)
        WHEN MATCHED THEN
                UPDATE SET
                        a.name = b.name
        WHEN NOT MATCHED THEN
                INSERT VALUES(b.id,b.name);
END;

/

SELECT * FROM T1;
SELECT * FROM T2;

SQL Cursor

如果ORACLE SERVER是dedicate mode,游标是存放在PGA里面的；如果ORACLE SERVER是SHARED SERVER mode，游标是存放在SGA里面的. 

• A cursor is a pointer to private memory area allocated by the Oracle Server.It is used to handle the result set of a SELECT  statement.
• There are two types of cursors:implicit and explicit.
  • -Implicit:Created and managed internally by the Oracle Server to process SQL statements.
  • -Explicit:Declared explicitly by the programmer.

SQL Cursor Attributes for Implicit Cursors

(为什么是for Implicit cursors，那么Explicit cursors怎么办?)

Using SQL cursor attributes,you can test the outcome of your SQL statements.

SQL%FOUND	Boolean attribute that evaluates to TRUE if the most recent SQL statement affected at least one row.
SQL%NOTFOUND	Boolean attribute that evaluates to TRUE if the most recent SQL statement did not affect even one row.
SQL%ROWCOUNT	An integer value that represents the number of rows affected by the most recent SQL statement.

当想知道UPDATE、DELETE执行完成之后，影响了多少条记录的话，就可以时尚上述三个属性(SQL%FOUND、SQL%NOTFOUND、SQL%ROWCOUNT);

SQL Cursor Attributes for Implicit Cursors

Delete rows that have the specified employee ID from the employees table.Print the number of rows deleted.

Example:

DECLARE
        v_rows_deleted VARCHAR2(64);
        v_emp_id        emp.employee_id%TYPE := 204;
BEGIN
        DELETE FROM emp
        WHERE employee_id = v_emp_id;

        v_rows_deleted :=SQL%ROWCOUNT || ' Rows affected';

        COMMIT;

        DBMS_OUTPUT.PUT_LINE(v_rows_deleted);
END;

/

注意：v_rows_deleted :=SQL%ROWCOUNT || ' Rows affected';这句需要放在COMMIT之前，否则，就不能显示正常的SQL%ROWCOUNT数据.
总结：游标的属性必须在COMMIT之前获取.

DECLARE
        myname  emp.last_name%TYPE;
        mysal   emp.salary%TYPE;
        empid   emp.employee_id%TYPE;

        CURSOR emp_cursor IS SELECT * FROM emp ORDER BY 1;
BEGIN

        FOR i IN emp_cursor
        LOOP
                UPDATE emp SET salary = salary*1.5 WHERE employee_id = i.employee_id
                RETURNING employee_id,salary,last_name INTO empid,mysal,myname;

                DBMS_OUTPUT.PUT_LINE(empid || '--->' || myname || '--->' || mysal);
        END LOOP;
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('Execute Successfully!');
EXCEPTION
        WHEN OTHERS THEN
                ROLLBACK;
                DBMS_OUTPUT.PUT_LINE('Error Occur');
END;

/

UPDATE语句中使用RETURNING子句，保留更新后的值.

Quiz

When using the SELECT statement in PL/SQL,the INTO clause is required and queries can return one or more row.

1. True
2. False

Summary

In this lesson,you should have learned how to: 

• Embed DML statements,transaction control statements,and DDL statements in PL/SQL
• Use the INTO cluase,which is mandatory for SELECT statements in PL/SQL
• Differentiate between implicit cursors and explicit cursors
• Use SQL cursor attributes to determine the outcome of SQL statements.