Updating using cursor oracle dating a shy friend

updating using cursor oracle-68

If the statement doesn’t identify any rows to be fetched, Oracle Database will raise the NO_DATA_FOUND exception.

Here are some examples of using SELECT-INTO: Table 1: Possible error messages if INTO and SELECT lists do not match Fetching from Explicit Cursors A SELECT-INTO is also referred to as an implicit query, because Oracle Database implicitly opens a cursor for the SELECT statement, fetches the row, and then closes the cursor when it finishes doing that (or when an exception is raised).

CREATE TABLE employee 2 (employee_id NUMBER(7), 3 last_name VARCHAR2(25), 4 first_name VARCHAR2(25), 5 userid VARCHAR2(8), 6 start_date DATE, 7 comments VARCHAR2(255), 8 manager_id NUMBER(7), 9 title VARCHAR2(25), 10 department_id NUMBER(7), 11 salary NUMBER(11, 2), 12 commission_pct NUMBER(4, 2) 13 ); Table created.

SQL DECLARE 2 CURSOR emp Cursor IS 3 SELECT employee_id, department_id, NVL(salary,0) salary, ROWID 4 FROM employee; 5 lv_record_num PLS_INTEGER DEFAULT 0; 6 BEGIN 7 FOR emp Record IN emp Cursor LOOP 8 lv_record_num := lv_record_num 1; 9 IF emp Record.department_id = 10 OR 10 emp Record.department_id = 41 THEN 11 IF emp Record.salary 2000 THEN 24 emp Record.salary := emp Record.salary * 1.05; 25 ELSE 26 emp Record.salary := emp Record.salary * 1.10; 27 END IF; 28 END IF; 29 UPDATE employee 30 SET salary = emp Record.salary 31 WHERE rowid = emp Record. PUT_LINE(); 43 ROLLBACK; 44 END; 45 / Employee: 1 Department: 50 New Salary: $2,625.00 Employee: 2 Department: 41 New Salary: $1,522.50 Employee: 3 Department: 31 New Salary: $1,540.00 Employee: 4 Department: 10 New Salary: $1,522.50 Employee: 5 Department: 50 New Salary: $1,705.00 Employee: 6 Department: 41 New Salary: $1,260.00 Employee: 7 Department: 41 New Salary: $1,312.50 Employee: 8 Department: 41 New Salary: $1,155.00 Employee: 9 Department: 41 New Salary: $1,365.00 Employee: 10 Department: 41 New Salary: $1,372.35 Update Process Complete.

CREATE OR REPLACE PROCEDURE get_emp_rs (p_deptno IN emp.deptno%TYPE, p_recordset OUT SYS_REFCURSOR) AS BEGIN OPEN p_recordset FOR SELECT ename, empno, deptno FROM emp WHERE deptno = p_deptno ORDER BY ename; END Get Emp RS; / Dim conn, cmd, rs Set conn = Server. Open "DSN=TSH1; UID=scott; PWD=tiger" Set cmd = Server.

Last modified 14-Sep-2019 03:45