SQL：第三章与Oracle服务器交互

1、与Oracle服务器交互,本章目的,学完本章之后，我们会掌握以下的内容: 在PL/SQL里面写一个成功的select语句 在PL/SQL里面写一个DML语句 判定SQL DML语句的成果,PL/SQL里面的SQL语句,用SELECT语句提取数据. 用DML语句对数据库数据进行修改. 隐式游标校验SQL语句的执行结果,PL/SQL里面的SELECT语句,语法,SELECT select_list INTO variable_name, variable_name. | record_name FROM table WHERE condition,PL/SQL里面的SELECT语句,INTO 子句

2、是必须的. 例子,DECLARE v_deptnoNUMBER(2); v_locVARCHAR2(15); BEGIN SELECTdeptno, loc INTOv_deptno, v_loc FROMdept WHEREdname = SALES; . END,在PL/SQL里面提取数据,例子,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里面提取数据,例子,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里面操纵数据,用DML语句对数据库进行修改: INSERT UPDATE DELETE,插入数据,加入新的员工到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,更新数据,修改岗位Analysts的的员工的工资 例子,DECLARE v_sal_increase emp.sal%TYPE := 2000; BEGIN UPDATEemp SETsal = sal + v_sal_increase WHEREjob = ANALYST; END,删除数据,删除部门代码为10的员工 例子,DECLARE v_d

5、eptno emp.deptno%TYPE := 10; BEGIN DELETE FROM emp WHERE deptno = v_deptno; END,SQL 游标(cursor,一个游标(cursor) 是一个SQL工作的私有内存区域. 有两种类型的游标: 隐式游标 显式游标 Oracle服务器用隐式游标来解析与执行SQL语句 显式游标是编程人员显式定义的游标,SQL 游标属性,使用SQL 游标属性, 我们能够测试SQL的执行的结果,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 游标属性,从item表里面删除指定订单号码的行。 SQL%ROWCOUNT里面保存被删除的行数 例子,VARIABLE rows_deleted DECLARE v_ordid NUMBER := 605; BEGIN DELETEFROM item WHEREordid = v_ordid; rows_deleted := SQL%ROWCOUNT | rows deleted.); END; PRINT rows_deleted,总结,在 PL/SQL块里面插入SQL语句: SELECT, INSERT, UP