What is the difference between a procedure and a function?
This is one of those questions that, depending on your interview, you may get right or wrong depending on how you answer it. In the past, I've often stated that the difference between a procedure and a function is that a procedure is a program that performs one or more actions while a function's main purpose is to return a value. This is true but since a procedure can also return (interestingly with a RETURN clause) values, there is something else missing. The difference, that should get you some brownie points, is in the way procedures and functions are called. A procedure is called as an executable PL/SQL statement while a function is called like a PL/SQL expression. Consider the following and you will see the difference.
Procedure call
BEGIN
raiseEmployeeSalary(7369, 200);
END;
Function call
BEGIN
employeeSalary := getEmployeeSalary(7369);
END;
Explain the difference between IN and OUT parameters.
An IN parameter allows us to pass values into PL/SQL code while the OUT parameter allows us to pass values back out of PL/SQL code. Also, remember that a parameter can be specified as both IN and OUT with the IN OUT declaration.
What is module overloading and why might you use it?
Overloading modules is nothing more than a mechanism that allows the coder to reuse the same name for different programs that are within the same scope. Overloading is probably one of my favorite mechanisms to share and increase usability within code.
Describe read consistency
Read consistency is nothing more than Oracle's way of quarantining that the data / result set you request at a specific time will be available until the request is complete. This means that if I issue a SQL statement at 7:00am, and it takes 10 minutes to produce the result set, and someone were to alter the data at 7:05am, the data I get back will look like it did at 7:00am as if no one had modified it.
What is an autonomous transaction and how does it affect the commit process?
An autonomous transaction creates, when called from another transaction, an independent and separate transaction that can issue commits without affecting the calling transaction.
What are packages?
A package is nothing more than a way to consolidate/group/organize/etc., common elements of PL/SQL code into a single named entity. While packages do help improve things like portability and maintainability of code, packages can also help improve the performance of the code.
How might you hide/protect your source code if distributed to customers?
Oracle's wrap utility provides a good way of hiding PL/SQL source code; protecting it and making it difficult for others to view. You can either wrap source code with the wrap utility or use the DBMS_DDL subprograms.
Name two PL/SQL conditional control statements.
Very easily, the conditional statements are the IF and the CASE statements.
Name two loop control statements.
The loop control statements consist of LOOP, FOR LOOP, and WHILE LOOP.
Identify the primary key and foreign keys within the table structures
One of the very first things for any proficient SQL coder to do is properly understand the data model they are working with. The given model is actually quite easy as it is one that most everyone has seen in some form or fashion, a quick customer order system. All primary keys are of a single column except for the table ORDERLINE, which has a concatenated primary key (ORDERID, LINEID) to uniquely identify a given row. Foreign keys are also straight forward as the CUSTOMERID in CUSTOMERORDER references CUSTOMERID in CUSTOMER and PARTID in ORDERLINE references PARTID in PRODUCT.
Customer
|
Customerorder
|
orderline
|
Product
|
CUSTOMERID (PK)
NAME
ADDRESS
CITY
STATE
ZIP
|
ORDERID (PK)
CUSTOMERID (FK Customer)
ORDERDATE
|
ORDERID (PK) (FK Customerorder)
LINEID (PK)
PARTID (FK Product)
QTY
PRICE
|
PARTID (PK) DESCRIPTION
COST
QTYONHAND
PRICE
|
Where might I put the total amount of an order (which table)? Why?
Again, understanding and having simple design skills is important to be able to talk intelligently during code reviews and data modeling sessions with DAs and DBAs. Here, to put a total order amount clearly breaks some of the normalization rules but would be put in the CUSTOMERORDER table to give us quick access to a total amount without having to look up each order line and calculating price, shipping, etc.; clearly for performance reasons.
If I were writing an application what would have to be the order of population for this set of tables?
The order of population is dependent upon the referential integrity between the tables. Here CUSTOMERORDER references CUSTOMER and ORDERLINE references PRODUCT and CUSTOMERORDER. Therefore, before CUSTOMERORDER can be populated, the CUSTOMER table must have an entry for the customer. Likewise, populating ORDERLINE is dependent on having an open order (CUSTOMERORDER) and some product to order (PRODUCT). Therefore, proper population of these tables would dictate something like PRODUCT, CUSTOMER, CUSTOMERORDER, and then ORDERLINE. Please note that I put PRODUCT first before CUSTOMER as we more than likely would have walk-in customers and product is clearly something we should have before a customer.
For the following SQL (A through F) identify which SQL is the best for displaying the customers that have placed an order
1. select customerid
from customer
where customerid in (select customerid from customerorder);
2. select customer.customerid
from customer, customerorder
where customer.customerid = customerorder.customerid;
3. select customer.customerid
from customer inner join customerorder on customer.customerid = customerorder.customerid;
4. select unique customer.customerid
from customer inner join customerorder on customer.customerid = customerorder.customerid;
5. select customerid
from customer
where exists ( select customerid from customerorder where customer.customerid = customerorder.customerid);
6. select customerid
from customer
where customerid = ( select customerid from customerorder where customer.customerid = customerorder.customerid);
Let's first begin by saying that all of these SQL statements will run without error EXCEPT SQL statement F, as the subquery could possibly return more than one row. SQL statements B & C produce multiple rows for the same customer (not the best, as we would have to filter in the application). SQL statement D is the same as C except for the UNIQUE keyword that, while it produces one row for each customer will incur unnecessary sorting. SQL statement A, while producing the proper results unfortunately (within the IN clause) will first produce a complete list of customer. SQL statement E, on the other hand, will through the subquery immediately return to the main part of the query as soon as an existence condition is met, thus the best SQL statement.
If you had to put any indexes on these tables, where would you put them?
If I were asked to create indexes on these tables, about the only place I'd add an index, not knowing the application, would be on the foreign keys. This helps safeguard against full table scans when validating the referential integrity between the two tables when INSERTing, DELETEing, and UPDATEing rows.
What is the difference between %ROWTYPE and %TYPE and what is the main advantage to using these?
What is the difference between %ROWTYPE and %TYPE and what is the main advantage to using these?
The %ROWTYPE allows the coder to indirectly represent a full or partial row of a database table or view, whereas the %TYPE allows for the coder to indirectly represent the data type from a previously declared variable or column. Basically, %ROWTYPE works on a full object whereas %TYPE works on a single column. The advantage to using either of these enables the coder to maintain data type declarations without ever having to know or change the data type for the items that use these. Below is an example of how the %TYPE allows for a layer of abstraction between names; allowing the coder to just change the first occurrence of the data type.
DECLARE
name VARCHAR(50);
fname name%TYPE;
lname name%TYPE;
city name%TYPE;
country name%TYPE;
BEGIN
Execution section;
END;
/
How might you display compile time warnings for PL/SQL code?
There are actually two methods to show compile time warnings. While both 'SHOW ERRORS' and the *_errors views (USER_ERRORS used here) show basically the same information; I tend to like the SHOW ERRORS command as it seems quicker to type. The advantage to using the *_errors views is that you can actually monitor every developer's current errors when using a view such as DBA_ERRORS, as there is an additional column for OWNER that will tell you the user encountering errors.
SQL> SHOW ERRORS
Errors for PROCEDURE CALLIT:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/10 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
:= . ( @ % ;
The symbol ":= was inserted before "=" to continue.
8/7 PLS-00103: Encountered the symbol "DBMS_OUTPUT" when expecting
one of the following:
then or
The symbol "then" was substituted for "DBMS_OUTPUT" to continue.
SQL> SELECT * FROM user_errors;
NAME TYPE SEQUENCE LINE POSITION TEXT ATTRIBUTE MESSAGE_NUMBER
------ ------------ -------- ---- -------- -------------------- --------- --------------
CALLIT PROCEDURE 1 4 10 PLS-00103: Encounter ERROR 103
ed the symbol "=" wh
en expecting one of
the following:
:= . ( @ % ;
The symbol ":= was i
nserted before "=" t
o continue.
CALLIT PROCEDURE 2 8 7 PLS-00103: Encounter ERROR 103
ed the symbol "DBMS_OUTPUT" when expecti
ng one of the following: then or
The symbol "then" was substituted for "D
BMS_OUTPUT" to continue.
Define 'scope' and 'visibility' for PL/SQL variables.
The definition of scope and visibility for a variable is actually quite close with the only difference being if you have to qualify the variable. The scope of a variable refers to the region (breadth) of code where the variable can be referenced. The visibility refers to the region of code you can reference the variable without qualifying it. So, hopefully you can see, visibility is a subset of the scope and requires the variable to be qualified (told where it comes from) in order to use. An example is clearly the best option here to help explain. Consider the PL/SQL code:
SQL> l
1 CREATE OR REPLACE PROCEDURE zero IS
2 x VARCHAR2(1); -- scope of zero.x begins
3 PROCEDURE a
4 IS
5 x VARCHAR2(1); -- scope of a.x begins
6 BEGIN -- visible a.x
7 x := 'a';
8 DBMS_OUTPUT.PUT_LINE('In procedure a, x = ' || x);
9 -- even though zero.x is not visible it can still be qualified/referenced
10 DBMS_OUTPUT.PUT_LINE('In procedure a, zero.x = ' || zero.x);
11 END; -- scope of a.x ends
12 PROCEDURE b
13 IS
14 BEGIN -- visible zero.x
15 DBMS_OUTPUT.PUT_LINE('In procedure b, x(zero) = ' || x);
16 DBMS_OUTPUT.PUT_LINE('In procedure a, zero.x = ' || zero.x);
17 END;
18 BEGIN -- visible zero.x
19 x:='0';
20 DBMS_OUTPUT.PUT_LINE('In zero, x = ' || x);
21 a;
22 b;
23* END; -- scope of zero.x ends
SQL> exec zero
In zero, x = 0
In procedure a, x = a
In procedure a, zero.x = 0
In procedure b, x(zero) = 0
In procedure a, zero.x = 0
PL/SQL procedure successfully completed.
Probably the biggest thing to notice about the scope of a variable, while all variables referenced ('x') are the same, just ask yourself if you need to qualify it and that will determine if it is visible. Notice in 'PROCEDURE b' where there is no local 'x' variable so the 'x' from 'PROCEDURE zero' is still visible and really doesn't need to be qualified, even though you still can. Moreover, if you ever get lost, Oracle is sometimes gracious to help by telling you something is out of scope.
LINE/COL ERROR
-------- -----------------------------------------------------------------
15/2 PL/SQL: Statement ignored
15/44 PLS-00225: subprogram or cursor 'A' reference is out of scope
What is an overloaded procedure?
An overloaded procedure is nothing more than the a mechanism that allows the coder to reuse the same procedure name for different subprograms inside a PL/SQL block by varying the number of parameters or the parameter data type. Below is an example of where the same subprogram (callit) is reused but the data type for the input parameter is changed from INTEGER to VARCHAR2; Oracle is smart enough to know the input parameter type and call the proper subprogram.
SQL>
1 DECLARE
2 PROCEDURE callit (anumber INTEGER) IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('Invoked number callit');
5 END callit;
6
7 PROCEDURE callit (acharacter VARCHAR2) IS
8 BEGIN
9 DBMS_OUTPUT.PUT_LINE('Invoked character callit');
10 END callit;
11
12 BEGIN
13 callit(1);
14 callit('1');
15* END;
SQL> /
Invoked number callit
Invoked character callit
PL/SQL procedure successfully completed.