For each of the PL/SQL exercises, you should submit your script files (named lab6-1.sql and lab6-2.sql, etc.) to the TA, who will make comments, suggestions, and corrections.

1. Write a hello, world program in PL/SQL. Specifically, create a procedure called "hello" that, when executed, prints a message to the screen.

2. Write your own, number-specific version of the nvl function, called mynvl. The function should take a two number parameters, a test value and a substitute value, and return a number value. If the first parameter is non-null, its value should be returned; if it is null, the value of the second parameter should be returned.

   By spooling to an output file, demonstrate your function with the dual table, with null and non-null, hardcoded inputs. Next, demonstrate your function applied to the null fields of the emp table. To begin spooling, enter the command:

   SQL> spool c:/plsql/lab6-2.out

   To turn end spooling, enter:

   SQL> spool off

3. Take a look at this factorial script. Download the script, read through it, and run it. Now, you'll modularize this single-block program.

   First, create a function called fact for computing the factorial, without the all the put_lines. Your function should accept a parameter n and return the resulting value.

   Next write a script (not a procedure!) to prompt for the input value, compute the factorial, and print the result.

4. Complete this exercise.
5. Write a procedure fire_emp() to fire an employee (by employee name), i.e., delete him from the emp table. If the employee manages anyone, his underlings' mgr field should be reset to the employee number of the deleted employee's manager (or null if he has none).

6. The dbms_output.putline() function, it turns out, is overloaded for numbers and strings but not, it turns out, for booleans. Using a CASE statement, write a function booltostring() to take a boolean and return a corresponding string value (i.e., 'true' for true). Remember that a boolean variable may be set to true, false, or null.

7. Download and take a look at the script located here. When you run the script, a package will defined with a single function, and one example call to the function will be demonstrated. Read through the script, run it, and try out the function.

   Once you understand the the bin2dec function, you'll write a function dec2bin to compute the opposite direction. Uncomment-out the declaration of this function and write it in the space provided. You can access the digits of the input the same way as in bin2dec. Since the result is a bitstring, the function's return type should be a varchar.

   Now, you'll re-implement your function in a slightly easier way. Since the parameter is a decimal, it can be represented as an ordinary number var. Two functions that may be helpful are mod (e.g., mod(10,3) -> 1) and trunc (e.g., trunc(10.5) -> 10).

8. Write a PL/SQL function taking a number as a parameter and returning the name of the employee, from table emp whose salary is equal to or closest to the value given. Ties may be broken arbitrarily. (I.e., return any one of a set of tying employees.)
9. Write a procedure get_subords(), taking a string parameter representing the name of an employee, to print a listing of all the named employee's direct subordinates. If this employee does not manage anyone else, print a message saying so. If the manager name is not found, print an error message indicating this.
10. If you have extra time, you can work on these.