PL/SQL Advanced

1. PL/SQL Advanced

2. Procedures and Functions • PL/SQL blocks • A subprogram • Parameters. • IN • you must supply a value for the argument when calling the function or procedure • OUT • function or procedure will set the value of the argument. • IN OUT

3. Example - Procedure DECLARE fname VARCHAR2(20) := 'randall'; lname VARCHAR2(25) := 'dexter'; PROCEDURE upper_name ( v1 IN OUT VARCHAR2, v2 IN OUT VARCHAR2) AS BEGIN v1 := UPPER(v1); -- change the string to uppercase v2 := UPPER(v2); -- change the string to uppercase END upper_name; BEGIN DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname ); upper_name (fname, lname); DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname ); END;

4. Example - Function DECLARE fname VARCHAR2(20) := 'randall'; lname VARCHAR2(25) := 'dexter'; FUNCTION upper_name ( v1 IN VARCHAR2, v2 IN VARCHAR2) RETURN VARCHAR2 AS v3 VARCHAR2(45); -- this variable is local to the function BEGIN v3 := v1 || ' + ' || v2 || ' = ' || UPPER(v1) || ' ' || UPPER(v2); RETURN v3; END upper_name; BEGIN DBMS_OUTPUT.PUT_LINE(upper_name (fname, lname)); END;

5. Example – Advanced Procedure DECLARE empid NUMBER; PROCEDURE avg_min_max_sal (empid IN NUMBER) IS jobid VARCHAR2(10); avg_sal NUMBER; min_sal NUMBER; max_sal NUMBER; BEGIN SELECT job_id INTO jobid FROM employees WHERE employee_id = empid; SELECT AVG(salary), MIN(salary), MAX(salary) INTO avg_sal, min_sal, max_sal FROM employees WHERE job_id = jobid; DBMS_OUTPUT.PUT_LINE ('Employee ID: ' || empid || ' Job ID: ' || jobid); DBMS_OUTPUT.PUT_LINE ('The average salary for job ID: ' || jobid || ' is ' || TO_CHAR(avg_sal)); DBMS_OUTPUT.PUT_LINE ('The minimum salary for job ID: ' || jobid || ' is ' || TO_CHAR(min_sal)); DBMS_OUTPUT.PUT_LINE ('The maximum salary for job ID: ' || jobid || ' is ' || TO_CHAR(max_sal)); END avg_min_max_sal; BEGIN empid := 125; avg_min_max_sal(empid); avg_min_max_sal(112); END;

6. Cursor • Cursor是用來儲存資料庫回傳的結果,並且有指標指向目前操作的資料列 • Implicit • 所有的結果回傳都有隱含的cursor • Explicit • 使用前必須宣告

7. Cursor Attributes • %Found • %Isopen • %Notfound • %Rowcount

8. Example –單一回傳值Cursor DECLARE firstname employees.first_name%TYPE; -- variable for first_name lastname employees.last_name%TYPE; -- variable for last_name CURSOR cursor1 IS SELECT first_name, last_name FROM employees WHERE employee_id = 120; BEGIN OPEN cursor1; -- open the cursor FETCH cursor1 INTO firstname, lastname; DBMS_OUTPUT.PUT_LINE('Employee name: ' || firstname || ' ' || lastname); CLOSE cursor1; END;

9. Example –多列Cursor DECLARE empid employees.employee_id%TYPE; -- variable for employee_id jobid employees.job_id%TYPE; -- variable for job_id lastname employees.last_name%TYPE; -- variable for last_name rowcount NUMBER; CURSOR cursor1 IS SELECT last_name, job_id FROM employees WHERE job_id LIKE ‘%CLERK'; CURSOR cursor2 is SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%MAN' OR job_id LIKE '%MGR'; BEGIN OPEN cursor1; -- open cursor1 before fetching DBMS_OUTPUT.PUT_LINE( '---------- cursor 1-----------------' ); LOOP FETCH cursor1 INTO lastname, jobid; -- fetches 2 columns into variables EXIT WHEN cursor1%NOTFOUND; DBMS_OUTPUT.PUT_LINE( RPAD(lastname, 25, ' ') || jobid ); END LOOP; rowcount := cursor1%ROWCOUNT; DBMS_OUTPUT.PUT_LINE('The number of rows fetched is ' || rowcount ); CLOSE cursor1;

10. OPEN cursor2; DBMS_OUTPUT.PUT_LINE( '---------- cursor 2-----------------' ); LOOP FETCH cursor2 INTO empid, lastname, jobid; EXIT WHEN cursor2%NOTFOUND; DBMS_OUTPUT.PUT_LINE( empid || ': ' || RPAD(lastname, 25, ' ') || jobid ); END LOOP; rowcount := cursor2%ROWCOUNT; DBMS_OUTPUT.PUT_LINE('The number of rows fetched is ' || rowcount ); CLOSE cursor2; END;

11. 在Cursor中使用參數 DECLARE empid employees.employee_id%TYPE; -- variable for employee_id hiredate employees.hire_date%TYPE; -- variable for hire_date firstname employees.first_name%TYPE; -- variable for first_name lastname employees.last_name%TYPE; -- variable for last_name rowcount NUMBER; bonusamount NUMBER; yearsworked NUMBER; CURSOR cursor1 (thismonth NUMBER) IS SELECT employee_id, first_name, last_name, hire_date FROM employees WHERE EXTRACT(MONTH FROM hire_date) = thismonth; BEGIN OPEN cursor1(EXTRACT(MONTH FROM SYSDATE)); DBMS_OUTPUT.PUT_LINE('----- Today is ' || TO_CHAR(SYSDATE, 'DL') || ' -----'); DBMS_OUTPUT.PUT_LINE('Employees with yearly bonus amounts:'); LOOP FETCH cursor1 INTO empid, firstname, lastname, hiredate; EXIT WHEN cursor1%NOTFOUND; yearsworked := ROUND( (MONTHS_BETWEEN(SYSDATE, hiredate)/12) );

12. IF yearsworked > 10 THEN bonusamount := 2000; ELSIF yearsworked > 8 THEN bonusamount := 1600; ELSIF yearsworked > 6 THEN bonusamount := 1200; ELSIF yearsworked > 4 THEN bonusamount := 800; ELSIF yearsworked > 2 THEN bonusamount := 400; ELSIF yearsworked > 0 THEN bonusamount := 100; END IF; DBMS_OUTPUT.PUT_LINE( empid || ' ' || RPAD(firstname, 21, ' ') || RPAD(lastname, 26, ' ') || hiredate || TO_CHAR(bonusamount, '$9,999')); END LOOP; rowcount := cursor1%ROWCOUNT; DBMS_OUTPUT.PUT_LINE('The number of rows fetched is ' || rowcount ); CLOSE cursor1; END;

13. Ref Cursor • CURSOR使用之前必須事先宣告 • 但是使用100個coursor,要宣告100次嗎? • Ref Cursor • 可以適用於各個cursor,只要型態相同 • 稱之為cursor variable

14. Example DECLARE TYPE emp_refcur_typ IS REF CURSOR RETURN employees%ROWTYPE; emp_cursor emp_refcur_typ; PROCEDURE process_emp_cv (emp_cv IN emp_refcur_typ) IS person employees%ROWTYPE; BEGIN DBMS_OUTPUT.PUT_LINE('-- Here are the names from the result set --'); LOOP FETCH emp_cv INTO person; EXIT WHEN emp_cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE(person.last_name || ', ' || person.first_name); END LOOP; END; BEGIN OPEN emp_cursor FOR SELECT * FROM employees WHERE employee_id < 108; process_emp_cv(emp_cursor); CLOSE emp_cursor; OPEN emp_cursor FOR SELECT * FROM employees WHERE last_name LIKE 'R%'; process_emp_cv(emp_cursor); -- pass emp_cursor to the procedure for processing CLOSE emp_cursor; END;

15. Cursor with %Rowtype DECLARE CURSOR cursor1 IS SELECT * FROM employees WHERE department_id = 60; employee_rec cursor1%ROWTYPE; BEGIN OPEN cursor1; LOOP FETCH cursor1 INTO employee_rec; -- retrieve entire row into record EXIT WHEN cursor1%NOTFOUND; DBMS_OUTPUT.PUT_LINE( ' Department ' || employee_rec.department_id || ', Employee: ' || employee_rec.employee_id || ' - ' || employee_rec.last_name || ', ' || employee_rec.first_name ); END LOOP; CLOSE cursor1; END;