skip to content »

freeautotest.ru

Program using cursor for updating salary of employee

program using cursor for updating salary of employee-76

Instead, you must wait for runtime to complete the SQL statement and then parse and execute it.Oracle Database makes it easy to execute SQL statements (and PL/SQL blocks) dynamically with the EXECUTE IMMEDIATE statement.

program using cursor for updating salary of employee-1program using cursor for updating salary of employee-69program using cursor for updating salary of employee-80

The function picks the appropriate SELECT statement, opens the cursor variable for that statement, and then returns the variable pointing to that result set.Unlike with an explicit cursor, you can pass a cursor variable as an argument to a procedure or a function.There are several excellent use cases for cursor variables, including the following: Cursor variables can be used with either embedded (static) or dynamic SQL.create table Employee( 2 ID VARCHAR2(4 BYTE) NOT NULL primary key, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 / Table created.SQL select * from Employee 2 / ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION ---- ---------- ---------- --------- --------- ---------- ---------- --------------- 01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected.SQL DECLARE 2 CURSOR employee_cur IS 3 SELECT * FROM employee 4 FOR UPDATE OF salary; 5 BEGIN 6 FOR employee_rec IN employee_cur 7 LOOP 8 UPDATE employee 9 SET salary = 10000 10 WHERE CURRENT OF employee_cur; 11 END LOOP; 12 END; 13 / PL/SQL procedure successfully completed.

SQL SELECT * FROM employee; ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION ---- ---------- ---------- --------- --------- ---------- ---------- --------------- 01 Jason Martin 25-JUL-96 25-JUL-06 10000 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 10000 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 10000 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 10000 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 10000 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 10000 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 10000 New York Manager 08 James Cat 17-SEP-96 15-APR-02 10000 Vancouver Tester 8 rows selected.

The following is a procedure that will display the values of any numeric column for all rows specified in the WHERE clause: CREATE OR REPLACE PROCEDURE show_number_values ( table_in IN VARCHAR2, column_in IN VARCHAR2, where_in IN VARCHAR2) IS TYPE values_t IS TABLE OF NUMBER; l_values values_t; BEGIN EXECUTE IMMEDIATE ' SELECT ' || column_in || ' FROM ' || table_in || ' WHERE ' || where_in BULK COLLECT INTO l_values; FOR indx IN 1 .. COUNT LOOP DBMS_OUTPUT.put_line (l_values (indx)); END LOOP; END;.

This occurs when a malicious user “injects,” or inserts into the statement, code that changes the behavior of that SQL statement.

Listing 2 includes the names_for function, which returns a cursor variable that fetches either employee or department names, depending on the argument passed to the function.

Code Listing 2: Block and description of the names_for function, which returns a cursor variable 1 CREATE OR REPLACE FUNCTION names_for ( 2 name_type_in IN VARCHAR2) 3 RETURN SYS_REFCURSOR 4 IS 5 l_return SYS_REFCURSOR; 6 BEGIN 7 CASE name_type_in 8 WHEN ' EMP' 9 THEN 10 OPEN l_return FOR 11 SELECT last_name 12 FROM employees 13 ORDER BY employee_id; 14 WHEN ' DEPT' 15 THEN 16 OPEN l_return FOR 17 SELECT department_name 18 FROM departments 19 ORDER BY department_id; 20 END CASE; 21 22 RETURN l_return; 23 END names_for; DECLARE l_names SYS_REFCURSOR; l_name VARCHAR2 (32767); BEGIN l_names := names_for (' DEPT'); LOOP FETCH l_names INTO l_name; EXIT WHEN l_names%NOTFOUND; DBMS_OUTPUT.put_line (l_name); END LOOP; CLOSE l_names; END; As you can see, all the information about the query being opened is “hidden” behind the function header.

So even though your code looks as if you are fetching one row at a time, Oracle Database will actually fetch 100 rows at a time—and enable you to work with each row individually.