120 likes | 138 Views
Cursor. Cursors Operations. Cursors adalah suatu nama area yang berisi hasil dari suatu statement SQL. 1. OPEN 2. FETCH 3. CLOSE. Cursors Attribute. 1. %FOUND 2. %NOTFOUND 3. %ROWCOUNT 4. %ISOPEN. Function & Procedure. Function. FUNCTION Nama_Function (parameter) RETURN TYPE IS
E N D
Cursors Operations Cursors adalah suatu nama area yang berisi hasil dari suatu statement SQL. 1. OPEN 2. FETCH 3. CLOSE
Cursors Attribute 1. %FOUND 2. %NOTFOUND 3. %ROWCOUNT 4. %ISOPEN
Function FUNCTION Nama_Function (parameter) RETURN TYPE IS deklarasi variabel BEGIN statement; statement; RETURN(variable/value); END;
Procedure PROCEDURE Nama_Procedure (parameter) IS deklarasi variabel BEGIN statement; statement; EXCEPTION statement; END;
Parameter Syntax parameter_name [IN | OUT | IN OUT] datatype [:= expr]
Procedure Parameter Modes Procedure Calling Environment IN Argument OUT Argument IN OUT Argumen (DECLARE) BEGIN EXCEPTION END;
Function Parameter Modes Function Calling Environment IN Argument (DECLARE) BEGIN EXCEPTION END;
DECLARE v_tax number; v_sal emp.salary%TYPE; BEGIN SELECT salary into v_sal FROM emp WHERE id = 97001; v_tax := Tax(v_sal); EXCEPTION WHEN NO_DATA_FOUND THEN v_tax := 0; END; FUNCTION Tax(v_value IN NUMBER) RETURN NUMBER IS BEGIN RETURN(v_value * .07); END Tax; Example
DECLARE v_id emp.id%TYPE := 97001; v_sal emp.salary%TYPE; BEGIN SELECT salary into v_sal FROM emp WHERE id = v_id; Change_Salary(v_id, v_sal*1.1); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; PROCEDURE Change_Salary (v_emp_id IN NUMBER, v_new_salary IN NUMBER) IS BEGIN UPDATE s_emp SET salary = v_new_salary WHERE id = v_emp_id; COMMIT; END Change_Salary; Example