1 / 15

PL/SQL Advanced

PL/SQL Advanced. 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. Example - Procedure. DECLARE

patch
Download Presentation

PL/SQL Advanced

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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;

More Related