Pemrograman Basis Data
Dosen : Titik Lusiani, M.Kom., OCP
1. 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
User source memuat nama,type procedure,line
number dan isi dari user source
2. The database administrator has informed you that the
CREATE PROCEDURE privilege has been granted to your account. Which objects can
you now create?
D.
procedures,
functions, and packages
Privilege (Hak Akses) merupakan tipe
khusus dari pernyataan SQL untuk mengakses object dari user lain. dua jenis privilege yaitu : system privilege
dan object privilege.
Ø
System
privilege adalah hak untuk user agar bisa mengeksekusi perintah ke
databasenya. ada tujuh system privilege yang disediakan oleh ORACLE.
1. create table = ijin buat table
2. create any tabel= ijin buat tabel di user lain
3. create sequence = ijin buat sequence
4. create view = ijin buat view
5. create indextype= ijin buat index
6. create procedure= ijin buat stored procedure
7. create session = ijin login database
Ø
Object privilege :
hak akses user unutk bisa mengakses database user lain berikut object privilege
yang terdapat dalam oracle :
·
select = ijin melihat data
·
insert = ijin memasukkan data
·
update = ijin melakukan perubahan data
·
delete = ijin menghapus data
·
alter = ijin mengubah struktur table
·
index = ijin membuat index
·
references = ijin membuat foreign key pada kolom tabel
3.
Which data dictionary table can you query to determine all stand-alone
procedures that reference the THEATER_PCK package?
D.
USER_DEPENDENCIES
User_dependencies merupakan data dictionary
yang saling terkait antar object.
4.
Which data dictionary view can you query to examine all the dependencies
between the objects that you own?
C. USER_DEPENDENCIES
User_dependencies dapat melakukan
refenerce untuk data dictionary view.
5.
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?
C. USER_OBJECTS
User_object memuat informasi tentang semua
object yang dimiliki, valid tidaknya suatu procedure.
6.
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?
A.
SELECT text FROM user_source WHERE name = 'THEATER_PCK';
Perintah yang digunakan untuk memanggil
tehater_pck adalah seperti syntax diatas.
7.
Which view can you query to determine the validity of a particular
procedure?
C.USER_OBJECTS
User_object digunakan untuk
memvalidasi suatu prosedure.
8.
Which data dictionary view must you query to determine when a particular
procedure or function was created?
C.USER_OBJECTS
User_object
melihat semua informasi tentang object yang dimiliki.
9.
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.)
C.REVOKE UPDATE ON employee FROM hr_emp;
E.GRANT EXECUTE ON update_employee TO hr_emp;
GRANT, merupakan perintah yang
digunakan untuk mengizinkan seorang user
untuk mengakses tabel pada database tertentu. Seorang administrator database biasanya akan melakukan pembatasan hak akses user terhadap tabel dalam database. Tujuan akhirnya adalah untuk manajemen keamanan database.
untuk mengakses tabel pada database tertentu. Seorang administrator database biasanya akan melakukan pembatasan hak akses user terhadap tabel dalam database. Tujuan akhirnya adalah untuk manajemen keamanan database.
REVOKE, merupakan
perintah yang digunakan untuk mencabut hak akses seorang user pada tabel dalam database tertentu. REVOKE digunakan untuk mencabut hak akses
seorang user mengakses tabel dalam database tertentu. Pencabutan hak akses ini
dengan klausa REVOKE. Pemakai adalah nama user yang akan dicabut hak aksesnya.
10.
Which statement concerning the use of a procedure is true?
C . A user needs only the privilege to execute the
procedure and does not need privileges on the underlying tables.
User
menggunakan hak akses (privilege) untuk membuat prosedure. Pada lingkungan
multi-user, dapat mengatur keamanan dari akses database dan mengontrol akses user. Security dari database Oracle
mengijinkan untuk:
·
Mengontrol akses ke
database
·
Memberikan akses
terhadap objek-objek tertentu di dalam database
·
Mengkonfirmasi
privilege yang diberikan dan diterima melalui data dictionary
·
Menciptakan synonym
untuk objek-objek database
11. Examine this procedure:
CREATE
OR REPLACE PROCEDURE update_theater (v_name IN VARCHAR2) IS
BEGIN
DELETE
theater WHERE id = 34;
END
update_theater;
This
procedure is owned by PROD. The user JSMITH must execute this procedure. Which
command(s) must PROD execute to grant the necessary privileges to JSMITH?
A.
GRANT EXECUTE ON update_theater TO jsmith
GRANT EXECUTE digunakan untuk memberi hak akses
melalui sebuah prosedure yang bernama update_theater kepada JSMITH.
12. You have just successfully
dropped the CALC_COMM procedure and deleted the script file containing the
source code. Which command can you execute to recover this procedure?
D. Only the database
administrator can recover this procedure using backups.
Drop digunakan
untuk melakukan penghapusan tabel. Melakukan penghapusan table dengan perintah DROP, berarti mengerjakan hal berikut :
->Semua data dan struktur dari table akan dihapus
->Semua transaksi pending akan di-commit
->Semua indeks akan dihapus
->Perintah drop ini tidak bisa di-rollback
->Semua data dan struktur dari table akan dihapus
->Semua transaksi pending akan di-commit
->Semua indeks akan dihapus
->Perintah drop ini tidak bisa di-rollback
Hanya database
administrator yang dapat merecovernya.
13.
Examine this procedure:
CREATE OR REPLACE PROCEDURE remove_department (v_deptno
IN NUMBER) IS
BEGIN
DELETE dept WHERE deptno = v_deptno;
END;
After executing this procedure, you receive this message:
ORA-02292: integrity constraint (SCOTT.FK_DEPTNO)
violated - child record found
What must be added to the procedure to handle this error?
D. Declare a new exception and
associate it with error code -2292. Create an exception section, and add code
to handle this non-predefined exception that you just declared.
Handling Exceptions merupakan salah satu kelebihan dari PL/SQL yang digunakan untuk
menangani error yang terjadi pada saat run time.
Sebagai contoh, pada saat kita menuliskan sebuah query, dan
data tersebut tidak ditemukan di dalam database, maka oracleakan
membangkitkan eksepsi NO_DATA_FOUND dengan kode ORA-01403 yang
berarti data tidak ditemukan.
16.
Examine this procedure:
CREATE
PROCEDURE add_theater IS
BEGIN
INSERT
INTO theater
VALUES
(35, 'Riverplace Theatre', '1222 River Drive, Austin, Tx.');
END;
This
procedure already exists in the database. You have made a change to the code
and want to recreate the procedure. Which command must you now use to prevent
an error?
D .CREATE OR REPLACE PROCEDURE
Command yang digunakan untuk mengubah database yang telah
ada sebelumnya yaitu menggunakan CREATE or REPLACE PROCUDERE.
Schema Trigger:
CREATE [ OR REPLACE ] TRIGGER
[ schema. ]trigger
{ BEFORE | AFTER
| INSTEAD OF }
{ dml_event_clause
| { ddl_event [ OR
ddl_event ]...
| database_event [ OR database_event ]...
} ON { [ schema. ]SCHEMA
| DATABASE
}
}
[ WHEN
(condition) ]
{ pl/sql_block |
call_procedure_statement } ;
19.
You execute this code:
CREATE OR REPLACE PROCEDURE find_seats_sold (v_movie_id IN
NUMBER DEFAULT 34) IS
v_seats_sold gross_receipt.seats_sold%TYPE;
BEGIN
null;
END;
Which statement is true?
A.
The statement compiles, and the procedure is created.
Pernyataan yang benar yang benar yang dapat dijalankan dari
code diatas yaitu procedure berhasil dibuat.
23.
Which statement about formal parameters is true?
B.
An IN OUT formal parameter does not require a value before returning to
the calling environment.
Perlu diketahui lebih dahulu, parameter
yang terdapat pada Procedure dinamakan Formal Parameter. Sedangkan
Parameter yang terdapat pada si pemanggil Procedure adalahActual Parameter.
Tipe Parameter pada Procedure ada 3 :
- IN parameter, Merupakan Tipe parameter yang
didefinisikan pada aktual parameter untuk kemudian ditangkap oleh formal
parameter. Kita tidak perlu menuliskan IN untuk mendefinisikan parameter
tersebut, karena parameter IN telah didefinisikan secara DEFAULT oleh
Oracle.
- OUT parameter. Merupakan tipe parameter
pada procedure yang nilainya dapat digunakan oleh si pemanggil procedure
dan bisa dibilang OUT parameter merupakan kebalikan dari IN parameter.
- IN OUT parameter. Tipe parameter
yang digunakan untuk mengirimkan sebuah nilai ke procedure yang kemudian
akan diproses dan dikembalikan kepada si pemanggil procedure.
- Examine this procedure:
CREATE
OR REPLACE PROCEDURE find_seats_sold
(v_movie_id
IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS
v_seats_sold
gross_receipt.seats_sold%TYPE;
BEGIN
SELECT
seats_sold INTO v_seats_sold
FROM
gross_receipt
WHERE
movie_id = v_movie_id AND theater_id = v_theater_id;
END;
Which
command will successfully invoke this procedure in SQL*Plus?
C.
EXECUTE find_seats_sold (v_theater_id => 500);
Command yang digunakan untuk memanggil procedure find_seat_sold adalah
EXECUTE find_seat_sold(v_thetater_id =>500);,v_movie_id telah diisi dengan
34 karena di default dan v_theater_id diisi 500.
27.
Which statement about declaring parameters is true?
B .Data type and maximum length are required.
Pendeklarasian parameter yang benar dalam
oracle dengan tipe varchar2 yaitu harus menambahkan data type dan maximum
length.
29. The CALC_COMM procedure is no longer needed and should
be removed. Which command will successfully remove this procedure from the
database?
A. DROP calc_comm;
DROP digunakan untuk
menghapus suatu object database tertentu.
3 30.
Examine this procedure:
CREATE PROCEDURE add_theater IS
BEGIN
INSERT INTO theater
VALUES (35, 'Riverplace Theatre', '1222
River Drive, Austin, Tx.');
END;
Which three statements about this procedure
are true? (Choose three.)
A. The ADD_THEATER procedure is written in SQL.
B. The ADD_THEATER procedure can be shared by multiple
programs.
C. The ADD_THEATER procedure will be stored in the
database as a schema object.
Practice II
1.
The
UPDATE_EMPLOYEE procedure contains an algorithm that calculates an employee's
commission multiple times throughout the program. If a change is made to the
algorithm, the change must be made multiple times. How can this procedure be
modified to simplify the code and reduce duplicated code?
C.
Add a local subprogram
containing the algorithm
Alasan :
UPDATE_EMPLOYEE
dimodifikasi agar tidak mengulang maka menggunakan local subprogram yang berisi subprogram tersebut.
2.
For
which Alasan might you create a subprogram within a procedure?
D.
to store a repeating block
of code once without creating a separate construct
Alasan :
Prosedure
dengan Subprogram digunakan untuk memanggil berulang-ulang dengan repeat blok
tanpa membuat create ualng subprogramnya, Sehingga lebih mudah untuk digunakan
3.
When
invoking a procedure, you can specify the arguments using the positional method
by listing the values in the order of the argument list. Which method would you
use to list values in an arbitrary order?
D.
named
Alasan :
Method
yang digunakan untuk mengembalikan nilai dengan memakai named Methodnya.
4.
Which
type of construct should you create to solely perform an action without returning
a value?
C.
procedure
Alasan :
a. Suatu
blok PL/SQL yang menyimpan sekumpulan perintah
b. Dapat
pengembalian nilai
c. No
Return datatype
5.
Examine
this procedure:
CREATE
OR REPLACE PROCEDURE find_seats_sold
(v_movie_id
IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS
v_seats_sold
gross_receipt.seats_sold%TYPE;
BEGIN
SELECT
seats_sold INTO v_seats_sold FROM gross_receipt
WHERE
movie_id = v_movie_id AND theater_id = v_theater_id;
END;
Which
command will successfully invoke this procedure in SQL*Plus?
C.
EXECUTE find_seats_sold
(v_theater_id => 500, v_movie_id => 34);
Alasan :
a. Eksekusi
procedure menggunakan sintakEXECUTE
b. (v_movie_id
IN NUMBER DEFAULT 34, v_theater_id IN NUMBER)à
·
Nilai inputan number untuk variable
v_movie_idyang otomatis bernilai 34
·
Nilai inputan number untuk variable
v_theater_id yang bernilai inputannya 500
6.
Examine
this procedure:
CREATE
OR REPLACE PROCEDURE find_seats_sold
(v_movie_id
IN NUMBER) IS
v_seats_sold
gross_receipt.seats_sold%TYPE;
BEGIN
SELECT
seats_sold INTO v_seats_sold
FROM
gross_receipt
WHERE
movie_id = v_movie_id;
END;
Which
command will successfully invoke this procedure in SQL*Plus?
C.EXECUTE find_seats_sold (34);
Alasan :
Mengeksekusi
procedure dengan nama find_seats_sold yang Nilai inputan number untuk variable
v_movie_id yang otomatis bernilai 34
7.
A
stored function can be invoked in many different ways. Which invocation example
is NOT valid?
D.
executing the stored
function within a CHECK constraint of a table
Alasan :
Tidak
boleh menggunakan Constraint tabel (CHECK )
8.
Examine
this function:
CREATE OR REPLACE FUNCTION get_budget
(v_studio_id
IN NUMBER) RETURN number IS
v_yearly_budget
NUMBER;
BEGIN
SELECT
yearly_budget INTO v_yearly_budget
FROM
studio WHERE id = v_studio_id;
RETURN
v_yearly_budget;
END;
C.
VARIABLE g_yearly_budget
NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
Alasan :
Menggunakan
perintah EXECUTE dan INSERT
VARIABLE
g_yearly_budget NUMBER
EXECUTE:g_yearly_budget :=
GET_BUDGET(11); àeksekusi(:g_yearly_budget pada form)
diisi dalamGET_BUDGET yang bernilai 11
9.
Examine
this function:
CREATE
OR REPLACE FUNCTION get_budget
(v_studio_id
IN NUMBER, v_max_budget IN NUMBER) RETURN number IS
v_yearly_budget
NUMBER;
BEGIN
SELECT
yearly_budget INTO v_yearly_budget FROM studio
WHERE
id = v_studio_id;
IF
v_yearly_budget > v_max_budget THEN
RETURN
v_max_budget;
ELSE
RETURN
v_yearly_budget;
END
IF;
END;
A. SELECT id, name, get_budget(id,200) FROM studio;
Alasan :
Menggunakan
perintah EXECUTE dan INSERT
SELECT
id, name, get_budget(id,200) FROM studio;
10.
For
which purpose are formal parameters used when creating functions?
B. passing values to the function
Alasan :
passing
values to the function
11.
When
creating a function in SQL*Plus, you receive an error message stating that the
function created with compilation errors. What must you do to see the
compilation errors?
A. Issue the SHOW ERRORS command.
Alasan :
Menggunakan
perintah SHOW ERROR untuk mengecek erorà(Issue the SHOW
ERRORS command)
12.
Examine
this function:
CREATE
OR REPLACE FUNCTION set_budget
(v_studio_id
IN NUMBER, v_new_budget IN NUMBER) IS
BEGIN
UPDATE
studio SET yearly_budget = v_new_budget
WHERE
id = v_studio_id;
IF
SQL%FOUND THEN RETURN TRUE;
ELSE
RETURN FALSE;
END
IF;
COMMIT;
END;
C. Add "RETURN BOOLEAN" immediately before the
IS keyword.
Alasan :
Add
"RETURN BOOLEAN" immediately before the IS keyword. Karena didalam
sintaknya terdapat fungsi boolean
13.
Procedures
and functions can be created and stored in the database or in an Oracle
Developer application. How is performance improved when storing procedures and
functions in the database?
D. Network traffic is decreased by bundling commands.
Alasan :
Network
traffic is decreased by bundling commands.
14.
Examine
this function:
CREATE
OR REPLACE FUNCTION set_budget
(v_studio_id
IN NUMBER, v_new_budget IN NUMBER)
RETURN
BOOLEAN IS
BEGIN
UPDATE
studio
SET
yearly_budget = v_new_budget
WHERE
id = v_studio_id;
IF
SQL%FOUND THEN
RETURN
TRUE;
ELSE
RETURN FALSE;
END
IF;
COMMIT;
END;
Which code will successfully invoke this function?
D. DECLARE v_updated_flag BOOLEAN;
BEGIN
v_updated_flag
:= set_budget(11,500000000);
END;
Alasan :
DECLARE
v_updated_flag BOOLEAN;
BEGIN
v_updated_flag
:= set_budget(11,500000000);
END;
15.
Which
two subprogram headers are correct? (Choose two.)
B.
CREATE OR REPLACE PROCEDURE get_sal (v_sal IN number)
IS
D. CREATE OR REPLACE FUNCTION calc_comm (p_amnt IN
number) RETURN number
Alasan :
· CREATE
OR REPLACE PROCEDURE get_sal (v_sal IN number) IS
· CREATE
OR REPLACE FUNCTION calc_comm (p_amnt IN number) RETURN number
16. Procedures and functions are very
similar. For which Alasan would you choose a function over a procedure?
B.
A function can be used in a SQL statement.
Alasan :
SQL statement dapat digunakan
untuk mendapatkan nilai dari sebuah function. Contoh SELECT employee_id,last_name, Lihat_gaji(employee_id) FROMemployees.
17.
The GET_BUDGET function is no longer needed and should
be removed. Which command will successfully remove this
function from the database?
C.
DROP FUNCTION get_budget;
Alasan :
Perintah DROP SQL digunakan untuk menghapus objek dari
database.
18. Which code successfully calculates
commission returning it to the calling environment?
D. CREATE OR REPLACE FUNCTION calc_comm
(v_emp_id IN NUMBER) RETURN number IS
v_total
NUMBER;
BEGIN
SELECT
SUM(ord.total) INTO v_total
FROM
ord,customer
WHERE
ord.custid = customer.custid
AND
customer.repid = v_emp_id;
RETURN
(v_total * .20);
END;
Alasan :
CREATE [OR REPLACE] FUNCTION
nama_function
[(argumen [IN|OUT|IN OUT] tipe_data,
argumen [IN|OUT|IN
OUT]tipe_data,
...)]
RETURN tipe_data {IS|AS}
[deklarasi variabel lokal]
BEGIN
badan fungsi
END;
19. Examine this function:
CREATE
OR REPLACE FUNCTION get_budget
(v_studio_id
IN NUMBER, v_max_budget IN NUMBER) RETURN number IS
v_yearly_budget
NUMBER;
BEGIN
SELECT
yearly_budget INTO v_yearly_budget FROM studio
WHERE
id = v_studio_id;
IF
v_yearly_budget > v_max_budget THEN RETURN v_max_budget;
ELSE
RETURN v_yearly_budget;
END
IF;
END;
Which
set of statements will successfully invoke this function within SQL*Plus?
- SELECT id, name, get_budget(id,200) FROM studio;
Alasan :
Perintah diatas adalah query yang dapat dijalankan dengan
SQL*Plus.
- A stored function can be invoked in many
different ways. Which invocation example is NOT valid?
D. executing the stored function within the DEFAULT
clause of the CREATE TABLE
Alasan :
Saat memanggil fungsi dari SELECT, INSERT, UPDATE, DELETE,
fungsi tidak boleh mengandung statement SQL untuk control statement (misal
COMMIT), session control statement (SET ROLE) atau system control statement
(seperti ALTER SYSTEM) dan tidak boleh mengeksekusi DDL statement seperti
CREATE karena akan diikuti dengan atomatik commit.
- You have just created a PL/SQL user-defined
function called CALC_COMM. Which statement will successfully test it?
D. SELECT * FROM ord GROUP BY ordid HAVING
calc_comm(total) > 5000;
Alasan :
- How do functions simplify maintainability?
a. by limiting changes to logic to one
location
Alasan :
Menyederhanakan fungsi adalah dengan memberikan parameter.
- Which two statements are true? (Choose two.)
i.
A function must return a value.
D. A function can be invoked from within a PL/SQL
expression.
Alasan :
Fungsi merupakan suatu blok PL/SQL yang
mengembalikan nilai. Fungsi dapat juga di invoke dengan menggunakan SQL*Plus.
- Examine this statement:
SELECT
id, theater_pck.get_budget(id)
FROM
studio;
What
must be true about the GET_BUDGET function for this statement to be successful?
b. It must not modify the database.
Alasan :
- Examine this function:
CREATE
OR REPLACE FUNCTION get_budget
RETURN
number IS
v_yearly_budget
NUMBER;
BEGIN
SELECT
yearly_budget INTO v_yearly_budget
FROM
studio WHERE id = v_studio_id;
RETURN
v_yearly_budget;
END;
What
additional code is needed to compile this function successfully?
D . Add "(v_studio_id IN NUMBER)" right before
the RETURN statement of the header.
Alasan :
CREATE
[OR REPLACE] FUNCTION function_name
[(parameter1
[mode1] datatype1,
RETURN
datatype
IS | AS
PL/SQL
block;
- Which subprogram type can be invoked from within
a SQL statement?
- function
Alasan :
Function adalah jenis PL/SQL block yang menghasilkan satu
nilai.Secara umum function digunakan untuk melakukan perhitungan,mengecek
eksistensi dan kevalidan suatu data.Function dapat dijalankan dengan SQL*Plus.
27. Examine this function:
CREATE OR REPLACE
FUNCTION get_budget
(v_studio_id IN NUMBER)
RETURN number IS
v_yearly_budget NUMBER;
BEGIN
SELECT yearly_budget
INTO v_yearly_budget
FROM studio WHERE id =
v_studio_id;
END;
To execute this function
successfully, what additional code must be added to the executable section?
D . RETURN v_yearly_budget;
Alasan :
v_yearly_budget telah
dideklarasikan pada query diatas maka RETURN v_yearly_budget dapat mengexecute
function diatas.
- While creating a package, you placed the function
name in the specification and the body. Which type of construct have you
created?
- public
Alasan :
Semua yg dideklarasikan pada package spesifikasi adalah
public, maka jika akan membuat variable, konstanta, prosedur, fungsi, dan
lainnya sebagai public maka deklarasikan pada package spasification.
- Examine this code:
CREATE
OR REPLACE PACKAGE prod_pack IS
g_tax_rate
NUMBER := .08;
END
prod_pack;
Which
statement about this code is true?
- This package specification can exist without a
body.
Alasan : Sebuah Blok PL / SQL terdiri dari tiga bagian.Bagian
Deklarasi (opsional). Bagian Eksekusi (wajib). Eksepsi (atau Kesalahan)
Penanganan bagian (opsional). Contohnya :
DECLARE
deklarasi variable
BEGIN
eksekusi program
EXCEPTION
exception handling
END;
- Examine this package specification:
CREATE
OR REPLACE PACKAGE theater_package IS
PROCEDURE
find_cpt
(v_movie_id
IN NUMBER, v_cost_per_ticket IN OUT NUMBER);
PROCEDURE
update_theater (v_name IN VARCHAR2);
PROCEDURE
find_seats_sold
(v_movie_id
IN NUMBER DEFAULT 34, v_theater_id IN NUMBER);
PROCEDURE
add_theater;
END
theater_package;
Which
statement about the procedures in this specification is true?
- They are public procedures.
Alasan :
Blok Procedure merupakan suatu blok PL/SQL yang
menyimpan sekumpulan perintah yang tidak disertai dengan pengembalian nilai.
Bentuk umum :
CREATE OR
REPLACE PROCEDURE
nama_procedure
(parameter_1
tipe_data, ….) AS
variabel-1
tipe_data;
begin
statment_1
END;
.
