# Executable Statements #
Function
Function adalah suatu blok PL/SQL yang memiliki konsep sama dengan procedure, hanya saja pada function terdapat pengembalian nilai (return value).
Karena function dapat mengembalikan sebuah nilai, function dapat diakses seperti layaknya sebuah variabel biasa.
Bentuk Umum :
CREATE OR REPLACE FUNCTION nama_function (parameter_1, …)
RETURN tipe_data AS
variabel_1 tipe_data;
…
BEGIN
statemen_1;
…
RETURN nilai_yang_dikembalikan;
END;
Karena function dapat mengembalikan sebuah nilai, function dapat diakses seperti layaknya sebuah variabel biasa.
Bentuk Umum :
CREATE OR REPLACE FUNCTION nama_function (parameter_1, …)
RETURN tipe_data AS
variabel_1 tipe_data;
…
BEGIN
statemen_1;
…
RETURN nilai_yang_dikembalikan;
END;
Contoh Function Tanpa Parameter :
CREATE OR REPLACE FUNCTION tulis_teks RETURN VARCHAR2 AS
S VARCHAR2(20)
BEGIN
S := ‘HALLO SEMUA’;
RETURN S;
END;
/
Contoh Function Dengan Parameter :
SET SERVEROUTPUT ON
DECLARE
H INTEGER;
BEGIN
H := pangkat(2, 3);
DBMS_OUTPUT.PUT_LINE(‘Hasil = ‘ || TO_CHAR(H));
END;
/
DECLARE
H INTEGER;
BEGIN
H := pangkat(2, 3);
DBMS_OUTPUT.PUT_LINE(‘Hasil = ‘ || TO_CHAR(H));
END;
/
Konversi Tipe Data
- Mengkonversi data untuk menyamakan tipe data.
- Tipe data yang tidak sama dapat menyebabkan kesalahan dan mempengaruhi kinerja.
- Fungsi konversi :
- TO_CHAR
- TO_DATE
- TO_NUMBER
DECLARE
v_date VARCHAR2(15) ;
BEGIN
SELECT TO_CHAR(hiredate, ‘MON. DD, YYYY’ )
INTO v_date
FROM emp
WHERE empno = 7839;
END ;
Nested Block dan variabel scope
- Nested Blocks adalah Statement
- Scope adalah Daerah Program yang Dapat Dimasukkan Dalam Objek
CONTOH
1. Counter
V_count := V_count + 1;
2. Operator Boolean
V_equal := (V_n1 = V_n2);
3. Validasi sebuah Field yang Berisi Nilai Tertentu
V_Valid := (V_empno Is Not NULL);
Pembahasan soal :
- Your stored procedure, GET_BUDGET, has a logic problem and must be modified. The script that contains the procedure code has been misplaced. Which data dictionary view can you query to capture the source code for this procedure?
- USER_SOURCE
(“ Karena user_source digunakan untuk melihat source code dari get_budget “)
- The database administrator has informed you that the CREATE PROCEDURE privilege has been granted to your account. Which objects can you now create?
- procedures, functions, and packages
(“ karena procedures,functions,and packages bisa digunakan untuk mengcreate procedure “)
- Which data dictionary table can you query to determine all stand-alone procedures that reference the THEATER_PCK package?
- USER_DEPENDENCIES
(“karena user_dependencies digunakan untuk mereferensi ke theater_pck”)
- Which data dictionary view can you query to examine all the dependencies between the objects that you own?
- USER_DEPENDENCIES
(“karena user_dependencies digunakan untuk mereferensi antar object”)
- Due to a change to a particular table, you are concerned with the number of stored procedures and functions that may have been affected. Which table can you query to check the status of each subprogram and determine which procedures and functions must be recompiled?
- USER_OBJECTS
(“karena user_objects digunakan untuk mengecek status dari subprogram”)
- You have lost the script file that contains the source code for the THEATER_PCK package. Which command will produce the source code stored in the database?
- SELECT text FROM user_source WHERE name = 'THEATER_PCK';
(“ karena untuk melihat source code digunakan user_source dan kondisinya”)
- Which view can you query to determine the validity of a particular procedure?
- USER_OBJECTS
(“karena untuk melihat valid tidaknya status procedurenya”)
- Which data dictionary view must you query to determine when a particular procedure or function was created?
- USER_OBJECTS
(“karena untuk melihat valid tidaknya status procedurenya”)
- All users in the HR_EMP role have UPDATE privileges on the EMPLOYEE table. You create the UPDATE_EMPLOYEE procedure. HR_EMP users should only be able to update the EMPLOYEE table using this procedure. Which two statements should you execute? (Choose two.)
- REVOKE UPDATE ON employee FROM hr_emp;
- GRANT EXECUTE ON update_employee TO hr_emp;
(“karena untuk mengupdate table employee bisa menggunakan revoke update dan untuk menjalankan menggunakan grant execute”)
- Which statement concerning the use of a procedure is true?
- A user needs only the privilege to execute the procedure and does not need privileges on the underlying tables.
(“karena user hanya menggunakan hak akses execute untuk procedure”)
0 komentar:
Posting Komentar