Stored procedure: why use & why not use

Use Stored Procedure
● Reusability
– Avoid rewriting sub queries and improve readability.
– If you can't store a query in a library that all the applications can access, you can put that query in a stored procedure.
● Separation of duties
– You don't trust non-DBA's to write queries.
● Data integrity
– Use triggers or constraints to prevent bad data from entering.
– Run several interdependent queries in a transaction in a single stored procedure.
● Event handling
– Log changes.
– Notify other systems of new data.

Not Use Stored Procedure
● Views may be all you need.
● An object-relational mapping (ORM) can help write queries safely.
● Difficult to version control stored procedures.
● Software rollouts may require more db changes.
● Could slow software development process.

Return a resultset in stored procedure with SYS_REFCURSOR

The following stored procedure is used to check user login information. If user info is correct, update LAST_LOGIN_DATE

CREATE OR REPLACE
PROCEDURE CHECK_LOGIN
  (I_USER_ID VARCHAR2,
    I_PWD VARCHAR2,
    O_RS OUT SYS_REFCURSOR
  )
IS
 vCount INTEGER:=0; 
BEGIN  
   SELECT COUNT(*) INTO vCount 
 FROM USER
        WHERE USER_ID = I_USER_ID AND PWD = ORA_HASH(I_PWD);
   IF (vCount > 0) THEN
  BEGIN
   UPDATE USER
   SET LAST_LOGIN_DATE = SYSDATE
   WHERE USER_ID = I_USER_ID;
     
   OPEN O_RS FOR
   SELECT USER_ID, USER_NAME, USER_STATUS, EMAIL, WEBSITE
   FROM USER
   WHERE USER_ID = I_USER_ID AND PWD = ORA_HASH(I_PWD);
  END;  
 ELSE
  OPEN O_RS FOR
  SELECT USER_ID, USER_NAME, USER_STATUS, EMAIL, WEBSITE
                FROM USER
  WHERE 1 = 2;   
 END IF;
END CHECK_LOGIN;

Local subprogram

DECLARE curContacts IS
  SELECT FirstName, LastName FROM CONTACTS;
  vFormatName VARCHAR2(50);
  FUNCTION FormatName(vFirstName IN VARCHAR2, vLastName IN VARCHAR2)
    RETURN VARCHAR2 IS
  BEGIN
    RETURN vFirstName || ' ' || vLastName;
  END FormatName
BEGIN
  FOR vContact IN curContacts LOOP
    vFormatName := FormatName(vContact.FirstName, vContact.LastName);
    INSERT INTO temp_table(char_col)
    VALUES(vFormatName);
  END LOOP;
  COMMIT;
END;

PL/SQL quiz

The employee table contains these columns:
LAST_NAME VARCHAR2(50)
FIRST_NAME VARCHAR2(50)
SALARY NUMBER(8, 2)
You need to display the names of employees on more than an average salary of all employees.
Evaluate the SQL statement.
SELECT LAST_NAME, FIRST_NAME
FROM EMPLOYEE
WHERE SALARY < AVG(SALARY);
Which change should you make to achieve the desired results?

A. Change the function in the WHERE clause.
B. Move the function to the SELECT clause and add a GROUP clause.
C. Move the function to the SELECT clause and add a GROUP BY clause and a HAVING clause.
D. Use a sub query in the where clause to compare the average salary value.

Answer: [D]

#Oracle #quiz

How many times does the following loop execute?
FOR iYear IN REVERSE  12 .. 1
LOOP
   CalculateSales(iYear);
END LOOP

A. 12
B. 11
C. 0
D. Error will occur

Answer:[C]
Highlight to find out the answer.

Oracle quiz

Rewrite the following IF statements so that you do not use the IF statement to set the value of no_revenue. What is the difference between the two statements?

IF total_sales <= 0 THEN
   no_revenue := TRUE;
ELSE
   no_revenue := FALSE;
END IF;

Answer:

no_revenue := NVL (total_sales, 1) <= 0;

Highlight to find out the answer.

Get permissions of columns

SELECT *
FROM DBA_COL_PRIVS

SELECT *
FROM USER_COL_PRIVS

SELECT *
FROM ALL_COL_PRIVS

#Oracle #quiz

Examine the structure of CONTACT table.
CONTACT(CONTACT_ID NOT NULL NUMBER(3)
, NAME NOT NULL VARCHAR2(25)
, PHONE NOT NULL VARCHAR2(9)
, ADDRESS VARCHAR2(50))
There are hundred records in the contact table. You need to modify the Phone
column to hold only numeric value. Which statement will modify the data type of
the Phone column?

A. ALTER TABLE CONTACT MODIFY PHONE NUMBER(9)
B. ALTER CONTACT TABLE MODIFY COLUMN PHONE NUMBER(9);
C. You can not modify a VARCHAR2 data type to a NUMBER data type.
D. You cannot modify the data type of a column if there is data in the column.

Answer: [D]
Highlight to find out the answer.

Oracle quiz

You need to analyze how long your employees are working for your company from the
date that they are hired to the end of quarter 1/2010. To do this you must create
a report that displays the employee id, employee name, hire date, and the number
of months in whole numbers from the hired date to 03/31/2010. Which statement
produces the required results?

A. SELECT empid, empname, hired_date, ROUND(MONTHS_BETWEEN
('03/31/2010',hired_date)) "Time Taken" FROM emp;
B. SELECT empid, empname, hired_date,ROUND(DAYS_BETWEEN
('03/31/2010',hired_date))/30 FROM emp;
C. SELECT empid, empname, hired_date,
ROUND OFF('03/31/2010'-hired_date) "Time Taken" FROM emp;
D. SELECT empid, empname, hired_date, MONTHS_BETWEEN('03/31/2010',hired_date)
"Time Taken" FROM emp;

Answer:[A]
Highlight to find out the answer.

#Oracle error handling

DECLARE
 vErrorCode NUMBER;
 vErrorMsg VARCHAR2(200);
 vCurrentUser VARCHAR2(8);
 vInfo VARCHAR2(100)
BEGIN
 /*Some code processes*/
EXCEPTION
 WHEN OTHERS THEN
  vErrorCode:= SQLCODE;
  vErrorMsg := SQLERRM;
  vCurrentUser := USER;
  vInfo := 'Error encountered on ' || TO_CHAR(SYSDATE) || ' by database user ' || vCurrentUser;
  INSERT INTO LOG_TABLE(CODE, MSG, INFO)
  VALUES(vErrorCode, vErrorMsg, vInfo);
END;

#Oracle quiz

The contact table contains these columns:
First_Name VARCHAR2(25)
Sales_Commission NUMBER(3,2)
Evaluate this SQL statement
SELECT first_name,commission
FROM Contact
WHERE commission= (SELECT Sales_Comission FROM Contact
WHERE UPPER(first_name)= 'Bill')
Which statement below will cause this statement to fail?

A. Bill has a null salescommission resolution.
B. Bill has a zero sales commission resolution.
C. There is no contact with the first name Bill.
D. The first name values in the database are in the lower case.

Answer: [A]
Highlight to find out the answer.

Which is true?

Which statement about SQL is true?

A. Null values are displayed last in the ascending sequences.
B. Data values are displayed in descending order by default.
C. You cannot specify a column alias in an ORDER BY clause.
D. You cannot sort query results by a column that is not included in the SELECT list.
E. The results are sorted by the first column in the SELECT list, if the ORDER BY
clause is not provided.

Answer:[A]
Highlight to find out the answer.

Modify existing table

Examine the structure of contact table:
CONTACT
(
CONTID NOT NULL NUMBER(3),
NAME VARCHAR2(25),
ADDRESS VARCHAR2(50),
BIRTHDATE DATE
)
Currently the table is empty. You have decided that null values should not be
allowed for the NAME column. Which statement restricts NULL values from being
entered into column?

A. ALTER TABLE CONTACT ADD CONSTRAINT NAME(NOT NULL);
B. ALTER TABLE CONTACT ADD CONSTRAINT NOT NULL (NAME);
C. ALTER TABLE CONTACT MODIFY CONSTRAINT NAME(NOT NULL);
D. ALTER TABLE CONTACT MODIFY(NAME VARCHAR2(25) NOT NULL);

Answer:[D]
Highlight to find out the answer.

Add a foreign key

How would you add a foreign key constraint on the dept_no column in the EMP
table. Referring to the ID column in the DEPT table?

A. Use the ALTER TABLE command with the ADD clause in the DEPT table.
B. Use the ALTER TABLE command with the ADD clause on the EMP table.
C. Use the ALTER TABLE command with the MODIFY clause on the DEPT table.
D. Use the ALTER TABLE command with the MODIFY clause on the EMP table.
E. This task cannot be accomplished.

Answer:[B]
Highlight to find out the answer.

ROLE

Evaluate the SQL statement.
CREATE ROLE manager;
CREATE ROLE clerk;
CREATE ROLE warehouse;
CREATE USER john IDENTIFIED BY john123;
GRANT warehouse TO clerk;
GRANT clerk TO manager;
GRANT warehouse TO john;
GRANT manager TO john
/
How many roles will user john have access to?

A. 0
B. 1
C. 2
D. 3

Answer:[D]
Highlight to find out the answer.

RTRIM

What will be the output of the following script?
SELECT RTRIM('TestxxXXxxx','x') FROM dual;

A. Test
B. TestxxXX
C. TestXX
D. An error occurs: "The RTRIM function requires 1 argument(s)."

Answer:[B]
Highlight to find out the answer.
Prentice Hall Presents Oracle Software - 4 Cd-Roms

LOWER, UPPER

You want to display the details of all employees whose first names is Bill. But you are not sure in which case first names are stored. Which statement will list all the employees whose first name is Bill?
A. Select last_name, first_name FROM emp WHERE first_name= 'bill';
B. Select last_name, first_name FROM emp WHERE UPPER (first_name)= 'bill';
C. Select last_name, first_name FROM emp WHERE first_name = UPPER ('bill');
D. Select last_name, first_name FROM emp WHERE LOWER(first_name)= 'bill';

Answer:[D]
Highlight to find out the answer.

NVL

You need to create a report to display the issue date and total items issued of your warehouseissue table. If the item has not been issued your report must display not issued. If the total is not available your report must say not available. In the warehouseissue table the issue date column has a data type of date the total column has a data type of number. Which statement do you use to create this report?

A. Select itemid, issuedate "Not issued", total "Not available" FROM warehouseissue;
B. Select itemid, NVL(issuedate, 'Not issued'), NVL (total, "Not available") FROM warehouseissue;
C. Select itemid, NVL(TO_CHAR(issuedate), 'Not issued'), NVL(TO_CHAR(total), 'Not available') FROM warehouseissue;
D. Select itemid, TO_CHAR(issuedate, 'Not issued') TO_CHAR(total, 'Not available') FROM warehouseissue;

Answer:[C]
Highlight to find out the answer.

Get disabled constraints

SELECT OWNER,TABLE_NAME,
        CONSTRAINT_NAME,
        DECODE(CONSTRAINT_TYPE,
           'C','Check',
           'P','Primary Key',
           'U','Unique',
           'R','Foreign Key',
           'V','With Check Option') AS CONSTRAINT_TYPE
FROM dba_constraints
WHERE STATUS = 'DISABLED'
ORDER BY OWNER, TABLE_NAME, CONSTRAINT_NAME

Get tables without indexes

SELECT OWNER, TABLE_NAME
FROM (SELECT OWNER, TABLE_NAME
    FROM   dba_tables
    WHERE OWNER NOT IN ('SYS','SYSTEM')
    MINUS
    SELECT TABLE_OWNER, TABLE_NAME
    FROM dba_indexes
    WHERE OWNER NOT IN ('SYS','SYSTEM'))
ORDER BY OWNER,TABLE_NAME;