SQL：第三章與Oracle服務(wù)器交互

1、與Oracle服務(wù)器交互,本章目的,學(xué)完本章之后，我們會(huì)掌握以下的內(nèi)容: 在PL/SQL里面寫(xiě)一個(gè)成功的select語(yǔ)句 在PL/SQL里面寫(xiě)一個(gè)DML語(yǔ)句 判定SQL DML語(yǔ)句的成果,PL/SQL里面的SQL語(yǔ)句,用SELECT語(yǔ)句提取數(shù)據(jù). 用DML語(yǔ)句對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)進(jìn)行修改. 隱式游標(biāo)校驗(yàn)SQL語(yǔ)句的執(zhí)行結(jié)果,PL/SQL里面的SELECT語(yǔ)句,語(yǔ)法,SELECT select_list INTO variable_name, variable_name. | record_name FROM table WHERE condition,PL/SQL里面的SELECT語(yǔ)句,INTO 子句

2、是必須的. 例子,DECLARE v_deptnoNUMBER(2); v_locVARCHAR2(15); BEGIN SELECTdeptno, loc INTOv_deptno, v_loc FROMdept WHEREdname = SALES; . END,在PL/SQL里面提取數(shù)據(jù),例子,DECLARE v_orderdate ord.orderdate%TYPE; v_shipdate ord.shipdate%TYPE; BEGIN SELECT orderdate, shipdate INTO v_orderdate, v_shipdate FROM ord WHERE id

3、 = 157; . END,在PL/SQL里面提取數(shù)據(jù),例子,DECLARE v_sum_sal emp.sal%TYPE; v_deptno NUMBER NOT NULL := 10; BEGIN SELECTSUM(sal) - group function INTOv_sum_sal FROMemp WHEREdeptno = v_deptno; END,在PL/SQL里面操縱數(shù)據(jù),用DML語(yǔ)句對(duì)數(shù)據(jù)庫(kù)進(jìn)行修改: INSERT UPDATE DELETE,插入數(shù)據(jù),加入新的員工到emp表 例子,DECLARE v_empnoemp.empno%TYPE; BEGIN SELECTemp

4、no_sequence.NEXTVAL INTOv_empno FROMdual; INSERT INTOemp(empno, ename, job, deptno) VALUES(v_empno, HARDING, CLERK, 10); END,更新數(shù)據(jù),修改崗位Analysts的的員工的工資 例子,DECLARE v_sal_increase emp.sal%TYPE := 2000; BEGIN UPDATEemp SETsal = sal + v_sal_increase WHEREjob = ANALYST; END,刪除數(shù)據(jù),刪除部門(mén)代碼為10的員工 例子,DECLARE v_d

5、eptno emp.deptno%TYPE := 10; BEGIN DELETE FROM emp WHERE deptno = v_deptno; END,SQL 游標(biāo)(cursor,一個(gè)游標(biāo)(cursor) 是一個(gè)SQL工作的私有內(nèi)存區(qū)域. 有兩種類(lèi)型的游標(biāo): 隱式游標(biāo) 顯式游標(biāo) Oracle服務(wù)器用隱式游標(biāo)來(lái)解析與執(zhí)行SQL語(yǔ)句 顯式游標(biāo)是編程人員顯式定義的游標(biāo),SQL 游標(biāo)屬性,使用SQL 游標(biāo)屬性, 我們能夠測(cè)試SQL的執(zhí)行的結(jié)果,SQL%ROWCOUNTNumber of rows affected by the most recent SQL statement (an int

6、egervalue). SQL%FOUNDBoolean attribute that evaluates to TRUE if the most recent SQL statement affects oneor more rows. SQL%NOTFOUNDBoolean attribute that evaluates to TRUE if the most recent SQLstatement does not affect any rows. SQL%ISOPENAlways evaluates to FALSE because PL/SQL closes implicit cu

7、rsorsimmediately after they are executed,SQL 游標(biāo)屬性,從item表里面刪除指定訂單號(hào)碼的行。 SQL%ROWCOUNT里面保存被刪除的行數(shù) 例子,VARIABLE rows_deleted DECLARE v_ordid NUMBER := 605; BEGIN DELETEFROM item WHEREordid = v_ordid; rows_deleted := SQL%ROWCOUNT | rows deleted.); END; PRINT rows_deleted,總結(jié),在 PL/SQL塊里面插入SQL語(yǔ)句: SELECT, INSERT, UP