Hour 22, "Advanced SQL Topics"

Quiz Answers

1: Can a trigger be altered?
A1: No, the trigger must be replaced or re-created.
2: When a cursor is closed, can you reuse the name?
A2: This is implementation-specific. In some implementations, the closing of the cursor will allow you to reuse the name and even free the memory, while for other implementations you must use the DEALLOCATE statement before the name can be reused.
3: What command is used to retrieve the results after a cursor has been opened?
A3: The FETCH command.
4: Are triggers executed before or after an INSERT, DELETE, or UPDATE?
A4: Triggers can be executed before or after an INSERT, DELETE, or UPDATE. There are many different types of triggers that can be created.

Exercise Answers

1: Using your implementation's system catalog tables, write the SQL that creates the following SQL statements. Substitute the name of an actual object for the object names.
  1. GRANT SELECT ON TABLE_NAME TO USERNAME;
										
  2. GRANT, CONNECT, RESOURCE TO USERNAME;
										
  3. SELECT COUNT(*) FROM TABLE_NAME;
A1: 
  1. SELECT 'GRANT SELECT ON '||TABLE_NAME|| ' TO '||
USERNAME||';'
FROM SYSTEM CATALOG TABLE_NAME;

  2. SELECT 'GRANT, CONNECT, RESOURCE TO '
||USERNAME||';'
FROM SYSTEM CATALOG TABLE_NAME;

  3. SELECT 'SELECT COUNT(*) FROM '||TABLE_NAME||';'
FROM SYSTEM CATALOG TABLE_NAME;
2: Write a statement to create a stored procedure that deletes an entry from the PRODUCTS_TBL table; it should be similar to the example used in this hour to insert a new product.
A2: 
CREATE PROCEDURE DELETE_PRODUCT
    (OLD_PROD_ID IN VARCHAR2)
    AS
    BEGIN
        DELETE FROM PRODUCTS_TBL
        WHERE PROD_ID = OLD_PROD_ID;
        COMMIT;
    END;
    /

3: Write the statement that executes the stored procedure that you created in Exercise 2 to delete the row for PROD_ID '9999'.
A3: 
EXECUTE DELETE_PRODUCT ('9999'),

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset