PLSQL基础(三).pptVIP

PL/SQL开发 基础篇（三） 本篇主要讲解如下语言点： PL/SQL Static SQL PL/SQL Dynamic SQL Static SQL SELECT,INSERT,UPDATE,DELETE语句 DECLARE emp_id employees_temp.employee_id%TYPE := 299; emp_first_name employees_temp.first_name%TYPE := Bob; emp_last_name employees_temp.last_name%TYPE := Henry; BEGIN INSERT INTO employees_temp (employee_id, first_name, last_name) VALUES (emp_id, emp_first_name, emp_last_name); UPDATE employees_temp SET first_name = Robert WHERE employee_id = emp_id; DELETE FROM employees_temp WHERE employee_id = emp_id; COMMIT; END; Cursors - 1 隐式游标(Implicit Cursors) 你不能够控制隐式游标，但是你可以得到它的相关信息 SQL%ISOPEN，SQL%FOUND，SQL%NOTFOUND和SQL%ROWCOUNT Cursors - 2 显式游标(Explicit Cursors) 定义方法 CURSOR cursor_name [ parameter_list ] [ RETURN return_type ] IS select_statement; Cursors - 3 显式游标的使用方法以及结果集的取得 DECLARE CURSOR myCursor1(param1 NUMBER DEFAULT 100) IS SELECT * FROM T1 WHERE C1 = param1; myResult myCursor1%ROWTYPE; BEGIN IF NOT myCursor1%ISOPEN THEN OPEN myCursor1; END IF; LOOP FETCH myCursor1 INTO myResult; EXIT WHEN myCursor1%NOTFOUND; DBMS_OUTPUT.put_line(Result: || myResult.C2); END LOOP; IF myCursor1%ISOPEN THEN CLOSE myCursor1; END IF; END; Cursors - 4 DECLARE CURSOR myCursor1(param1 NUMBER DEFAULT 100) IS SELECT C1, C2 FROM T1 WHERE C1 = param1; TYPE TypeResult IS TABLE OF myCursor1%ROWTYPE INDEX BY PLS_INTEGER; myResult TypeResult; BEGIN OPEN myCursor1(10); LOOP FETCH myCursor1 BULK COLLECT INTO myResult LIMIT 2; EXIT WHEN myResult.COUNT = 0; FOR i IN myResult.FIRST .. myResult.LAST LOOP DBMS_OUTPUT.put_line(Result: || myResult(i).C2); END LOOP; END LOOP; CLOSE myCursor1; END; Cursors - 5 利用FOR LOOP进行数据读取 DECLARE CURSOR myCursor1(param1 NUMBER DEFAULT 100) IS SELECT * FROM T1 WHERE C1 = param1; BEGIN FOR item IN myCursor1 LOOP DBMS_OUTPUT.put_line(Result: || item.C2); END LOOP; END; Cursors - 6