1、在一个pl/sql block中,可以有多个transaction,一个transaction可以跨越多个PL/SQL block;
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SQL> begin 2 insert into tt values(1); 3 end; 4 / PL/SQL procedure successfully completed. SQL> select * from tt; ID ---------- 0 1
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SQL> select addr from v$transaction; ADDR ---------------- 000000007695F508
DML and Records
You can use records inside INSERT and UPDATE statements.
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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;
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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.
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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.
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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.
- True
- 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.