PL/SQL Flashcards
(15 cards)
What are bind or host variables?
Variables that are not pl/sql variables, but defined in SQL developer. Define them at the top as VARIABLE … and PRINT after the block prints them. They exist for the whole of the session
What is a SQL cursor?
A pointer to the private memory area that stores information about processing a specific SELECT or DML statement
- SQL%FOUND: true if the most recent statement affected at least one row
- SQL%NOTFOUND: true if the most recent statement affected zero rows
- SQL%ROWCOUNT: nr of rows affected by most recent statement
What is a CASE statement?
Evaluate the condition and based on the outcome, do an action (instead of returning a value)
End each WHEN with a ;
What are associative arrays?
A PL/SQL collection with two columns: key, value (scalar or record)
TYPE type IS TABLE OF datatype INDEX BY datatype
What are nested tables?
Sequentially indexed data
TYPE type IS TABLE OF datatype
and fill it with values
What are Varrays?
Sequentially indexed data with an upper limit
TYPE type IS VARRAY(eg.5) OF datatype
and fill it with values
What are the differences between anonymous blocks and subprograms?
anonymous blocks: unnamed, compiled every time, not stored in database, cannot be invoked, do not return values, cannot take parameters
subprograms: named, compiled once, stored in database, can be invoked, can return values, can take parameters
Why would you type CREATE OR REPLACE when creating a procedure?
If you want to change the procedure, you can do so without having to regrant all the privileges (which you have to do if you drop it and recreate it)
What is the difference between a procedure and a function?
A function always has to return a value and a procedure does not
What are the three data structures of the memory architecture of the database instance?
- System Global Area: group of shared memory structures, information of one database instance
- User Global Area: associated with user session connecting with the database instance
- Program Global Area: unshared memory containing data and control information for use by an Oracle process
When should we use the DBMS_SQL package?
When we have a query with an unknown number of select-list item or input host variables
What can be triggering events?
- INSERT, UPDATE or DELETE
- CREATE, ALTER, DROP
- database startup or instance shutdown
- error message
- user logon or logoff
Why would you use a compound trigger?
You can’t update and query a table at the same time, so when the trigger contains a query to table A on eg. an INSERT into table A, we get an error. With compound trigger, you can already query the table BEFORE STATEMENT to put the results into variables and then also trigger AFTER EACH ROW.
When can you use the DETERMINISTIC clause?
If you want to indicate that a function will return the same values when it is called with the same parameters (so not when the result depends on the state of variables or schema objects)
