cursor. cursors operations cursors adalah suatu nama area yang berisi hasil dari suatu statement...
TRANSCRIPT
![Page 1: Cursor. Cursors Operations Cursors adalah suatu nama area yang berisi hasil dari suatu statement SQL. 1. OPEN 2. FETCH 3. CLOSE](https://reader036.vdocuments.us/reader036/viewer/2022083008/56649ee75503460f94bf8e0d/html5/thumbnails/1.jpg)
Cursor
![Page 2: Cursor. Cursors Operations Cursors adalah suatu nama area yang berisi hasil dari suatu statement SQL. 1. OPEN 2. FETCH 3. CLOSE](https://reader036.vdocuments.us/reader036/viewer/2022083008/56649ee75503460f94bf8e0d/html5/thumbnails/2.jpg)
Cursors Operations
Cursors adalah suatu nama area yang berisi
hasil dari suatu statement SQL.
1. OPEN
2. FETCH
3. CLOSE
![Page 3: Cursor. Cursors Operations Cursors adalah suatu nama area yang berisi hasil dari suatu statement SQL. 1. OPEN 2. FETCH 3. CLOSE](https://reader036.vdocuments.us/reader036/viewer/2022083008/56649ee75503460f94bf8e0d/html5/thumbnails/3.jpg)
Cursors Attribute
1. %FOUND
2. %NOTFOUND
3. %ROWCOUNT
4. %ISOPEN
![Page 4: Cursor. Cursors Operations Cursors adalah suatu nama area yang berisi hasil dari suatu statement SQL. 1. OPEN 2. FETCH 3. CLOSE](https://reader036.vdocuments.us/reader036/viewer/2022083008/56649ee75503460f94bf8e0d/html5/thumbnails/4.jpg)
Function & Procedure
![Page 5: Cursor. Cursors Operations Cursors adalah suatu nama area yang berisi hasil dari suatu statement SQL. 1. OPEN 2. FETCH 3. CLOSE](https://reader036.vdocuments.us/reader036/viewer/2022083008/56649ee75503460f94bf8e0d/html5/thumbnails/5.jpg)
Function
FUNCTION Nama_Function (parameter) RETURN TYPE IS deklarasi variabel
BEGIN statement; statement; RETURN(variable/value);
END;
![Page 6: Cursor. Cursors Operations Cursors adalah suatu nama area yang berisi hasil dari suatu statement SQL. 1. OPEN 2. FETCH 3. CLOSE](https://reader036.vdocuments.us/reader036/viewer/2022083008/56649ee75503460f94bf8e0d/html5/thumbnails/6.jpg)
Procedure
PROCEDURE Nama_Procedure (parameter) IS deklarasi variabel
BEGIN statement; statement;
EXCEPTION
statement;
END;
![Page 7: Cursor. Cursors Operations Cursors adalah suatu nama area yang berisi hasil dari suatu statement SQL. 1. OPEN 2. FETCH 3. CLOSE](https://reader036.vdocuments.us/reader036/viewer/2022083008/56649ee75503460f94bf8e0d/html5/thumbnails/7.jpg)
Parameter Syntax
parameter_name [IN | OUT | IN OUT] datatype [:= expr]
![Page 8: Cursor. Cursors Operations Cursors adalah suatu nama area yang berisi hasil dari suatu statement SQL. 1. OPEN 2. FETCH 3. CLOSE](https://reader036.vdocuments.us/reader036/viewer/2022083008/56649ee75503460f94bf8e0d/html5/thumbnails/8.jpg)
Procedure Parameter Modes
Procedure
IN OUT Argumen
(DECLARE)
BEGIN
END;
EXCEPTION
Calling Environment
IN Argument
OUT Argument
![Page 9: Cursor. Cursors Operations Cursors adalah suatu nama area yang berisi hasil dari suatu statement SQL. 1. OPEN 2. FETCH 3. CLOSE](https://reader036.vdocuments.us/reader036/viewer/2022083008/56649ee75503460f94bf8e0d/html5/thumbnails/9.jpg)
Function Parameter Modes
Function
(DECLARE)
BEGIN
END;
EXCEPTION
Calling Environment
IN Argument
![Page 10: Cursor. Cursors Operations Cursors adalah suatu nama area yang berisi hasil dari suatu statement SQL. 1. OPEN 2. FETCH 3. CLOSE](https://reader036.vdocuments.us/reader036/viewer/2022083008/56649ee75503460f94bf8e0d/html5/thumbnails/10.jpg)
Procedure vs Function
Procedure Function
Execute as a PL/SQL statement
Called as part of an expression
No RETURN datatype Must contain a RETURN datatype
Can return a value Must return a value
![Page 11: Cursor. Cursors Operations Cursors adalah suatu nama area yang berisi hasil dari suatu statement SQL. 1. OPEN 2. FETCH 3. CLOSE](https://reader036.vdocuments.us/reader036/viewer/2022083008/56649ee75503460f94bf8e0d/html5/thumbnails/11.jpg)
Example
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);EXCEPTIONWHEN NO_DATA_FOUND THEN
v_tax := 0;END;
FUNCTION Tax(v_value IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN(v_value * .07);
END Tax;
![Page 12: Cursor. Cursors Operations Cursors adalah suatu nama area yang berisi hasil dari suatu statement SQL. 1. OPEN 2. FETCH 3. CLOSE](https://reader036.vdocuments.us/reader036/viewer/2022083008/56649ee75503460f94bf8e0d/html5/thumbnails/12.jpg)
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);EXCEPTIONWHEN 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;