Concept explainers
a.
Stored procedures:
- A procedure is a collection of procedural and SQL statements.
- A procedure may have input parameter, output parameter and both parameters.
- It has a declared with a unique named with a unit of procedural code using the proprietary RDBMS and it is invoked by a host language library routine.
Syntax for stored procedure:
CREATE FUNCTION fun_name(argument IN data-type)RETRUN data-type[IS]
BEGIN
PL/SQL statements;
Return (value or expression);
END;
Explanation of Solution
Query to create stored procedure:
CREATE OR REPLACE PROCEDURE DISP_CUST_CRED (I_CUSTOMER_NUM IN CUSTOMER.CUSTOMER_NUM%TYPE) AS
I_CUSTOMER_NAME CUSTOMER.CUSTOMER_NAME%TYPE;
I_CREDIT_LIMIT CUSTOMER.CREDIT_LIMIT%TYPE;
BEGIN
SELECT CUSTOMER_NAME, CREDIT_LIMIT
INTO I_CUSTOMER_NAME, I_CREDIT_LIMIT
FROM CUSTOMER
WHERE CUSTOMER_NUM = I_CUSTOMER_NUM;
DBMS_OUTPUT.PUT_LINE (I_CUSTOMER_NAME);
DBMS_OUTPUT.PUT_LINE (I_CREDIT_LIMIT);
END;
/
Explanation:
- The above query is used to create a procedure named “DISP_CUST_CRED” to select the records in the “CUSTOMER” table.
- Change the “CUSTOMER_NUM” into “I_CUSTOMER_NUM” and place the “CUSTOMER_NAME” and “CREDIT_LIMIT” values into “I_CUSTOMER_NAME” and “I_CREDIT_LIMIT”.
- After placing these values, display the “I_CUSTOMER_NAME” and “I_CREDIT_LIMIT” from the “CUSTOMER” table.
- Once the stored procedure is created, it needs to be executed.
Query to view the customer name and credit limit:
BEGIN
DISP_CUST_CRED (126);
END;
The above query is used to view the customer name and credit limit for the number 126.
Output:
Toys Galore
7500
Explanation of Solution
b.
Query to create stored procedure:
CREATE OR REPLACE PROCEDURE DISP_ORDERS (I_ORDER_NUM ORDERS.ORDER_NUM%TYPE) AS
I_ORDER_DATE ORDERS.ORDER_DATE%TYPE;
I_CUSTOMER_NUM CUSTOMER.CUSTOMER_NUM%TYPE;
I_CUSTOMER_NAME CUSTOMER.CUSTOMER_NAME%TYPE;
BEGIN
SELECT ORDER_DATE, CUSTOMER.CUSTOMER_NUM, CUSTOMER_NAME
INTO I_ORDER_DATE, I_CUSTOMER_NUM, I_CUSTOMER_NAME
FROM ORDERS, CUSTOMER
WHERE ORDERS.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NUM
AND ORDER_NUM = I_ORDER_NUM;
DBMS_OUTPUT.PUT_LINE(I_ORDER_DATE);
DBMS_OUTPUT.PUT_LINE(I_CUSTOMER_NUM);
DBMS_OUTPUT.PUT_LINE(I_CUSTOMER_NAME);
END;
/
Explanation:
- The above query is used to create a procedure named “DISP_ORDERS” to select the records in the “CUSTOMER” and “ORDERS” tables.
- Change the “ORDER_NUM” into “I_ORDER_NUM” and place the “ORDER_DATE”, “CUSTOMER_NUM”, and “CUSTOMER_NAME” values into “I_ORDER_DATE”, “I_CUSTOMER_NUM”, and “I_CUSTOMER_NAME”.
- After placing these values, display the “I_ORDER_DATE”, “I_CUSTOMER_NUM”, and “I_CUSTOMER_NAME” from the “CUSTOMER” and “ORDERS” tables.
- Once the stored procedure is created, it needs to be executed.
Query to view the order date, customer name and customer number:
BEGIN
DISP_ORDERS (51608);
END;
The above query is used to view the order date, customer name and customer number for the number 51608.
Output:
10/12/2015
126
Toys Galore
Explanation of Solution
c.
Query to insert the value:
CREATE OR REPLACE PROCEDURE ADD_ORDER
(I_ORDER_NUM IN ORDERS.ORDER_NUM%TYPE,
I_ORDER_DATE IN ORDERS.ORDER_DATE%TYPE,
I_CUSTOMER_NUM IN ORDERS.CUSTOMER_NUM%TYPE) AS
BEGIN
INSERT INTO ORDERS (ORDER_NUM, ORDER_DATE, CUSTOMER_NUM)
VALUES
(I_ORDER_NUM, I_ORDER_DATE, I_CUSTOMER_NUM);
END;
/
Explanation:
The above query is used to create a stored procedure named “ADD_ORDER” to insert the new record in the “ORDERS” table. Once the stored procedure is created, it needs to be executed.
Query to execute the stored procedure:
BEGIN
ADD_ORDER (51627,'10/16/2015', 334);
END;
After executing the above query, the new record is inserted into the table “ORDERS”.
Output:
Query to view the contents in “ORDERS” table is as follows:
SELECT * FROM ORDERS;
Screenshot of output
Explanation of Solution
d.
Query to update stored procedure:
CREATE OR REPLACE PROCEDURE UPDATE_ORDER_DATE
(I_ORDER_NUM IN ORDERS.ORDER_NUM%TYPE,
I_ORDER_DATE IN ORDERS.ORDER_DATE%TYPE) AS
BEGIN
UPDATE ORDERS
SET ORDER_DATE = I_ORDER_DATE
WHERE ORDER_NUM = I_ORDER_NUM;
END;
/
Explanation:
The above query is used to create a stored procedure named “UPDATE_ORDER_DATE” to update the date of the order whose number is stored in “I_ORDER_DATE” to the date presently found in “I_ORDER_DATE”, it needs to be executed.
Executing the stored procedure:
The Content of “ORDERS” table before creating the procedure is given below:
Query to view the contents in “ORDERS” table is as follows:
SELECT*FROM ORDERS;
Screenshot of output
Query to execute the stored procedure:
BEGIN
UPDATE_ORDER_DATE (51614, '02/12/2019');
END;
/
After executing the above query, the date is changed in the table “ORDERS”.
Output:
Query to view the contents in “ORDERS” table is as follows:
SELECT * FROM ORDERS;
Screenshot of output
Explanation of Solution
e.
Query to delete the value:
CREATE OR REPLACE PROCEDURE DELETE_ORDERS
(I_ORDER_NUM ORDERS.ORDER_NUM%TYPE) AS
BEGIN
DELETE
FROM ORDERS
WHERE ORDER_NUM = I_ORDER_NUM;
END;
/
Explanation:
- The above query is used to create a procedure named “DELETE_ORDERS” to delete a record in the “ORDERS” table.
- Once the record is deleted, a procedure should create order number as a parameter.
- Once the stored procedure is created, it needs to be executed.
Executing the stored procedure:
The Content of “ORDERS” table before creating the procedure is given below:
Query to view the contents in “ORDERS” table is as follows:
SELECT * FROM ORDERS;
Screenshot of output
Query to execute the stored procedure:
BEGIN
DELETE_ORDERS (51613);
END;
The above query is used to delete the order number 51613.
Output:
Query to view the contents in “ORDERS” table after deleting the order number 51613 as follows:
SELECT * FROM ORDERS;
Screenshot of output
Want to see more full solutions like this?
Chapter 8 Solutions
A Guide to SQL
- T-SQL procedure SQL SERVER ONLY A: obtain the name and credit limit of the customer whose number currently is stored in I_CUSTOMER_NUM. Place these valuse in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. Output the content of I_CUSTOMER_NAME and I_CREDIT_LIMIT.arrow_forwardThe following figure is part of a university database. LNAME and FNAME stands for last name and first name, respectively. Both CNUM and CNO stand for course number. CNAME stands for course name. Specify the following queries in both relational algebra expression and SQL state- ments: 1) List the names and majors of all students who live in the city of Fullerton. 2) List the course number (CNUM), course name (CNAME) and number of units (UNITS) of each course that the student "Mary Willson" has enrolled. 3) For the course number "CPSC332", list the names of the studens who enrolled in the course. 4) For the students who live in in the city of either Fullerton or Brea, count the number of students in each city. 5) List the names of all students who live in Fullerton and do not enroll in any courses. STUDENT SSN FNAME LNAME DBIRTH ADRRESS CITY MAJOR ENROLL SNO CNO GRADE COURSE CNUM CNAME ТЕXTBOOK UNITS DEPARTMENTarrow_forwardPl/sql Program to increase the salary of employee whose id is 400 based on the following conditions :If experience is more than 10 years, increase salary by 20%, if experience is greater than 5 years, increase salary by 10% otherwise 5%arrow_forward
- Database course: Write SQL queries to do the following A. List names of IT students. B. List titles of courses of type E. C. Display the results of student with ID 201006 D. List student names and the courses they took. E. List total credits that student with ID 201002.arrow_forwardWrite a PL\SQL program to increase the salary of employee whose ID is 400 based on the following conditions: 17 experience is more than 10 years, increase salary by 20% If experience is greater than 5 years, increase salary by 10% Otherwise 5% en minutes remaining .arrow_forwardConsider the following table: Instructor (Instructor_Id, Instructor_Name, specialization, Address, #Department_Id) Write in PL/SQL a function named NumberInstructor_department that takes the id of a department as an input parameter and returns the number of instructors who are working in that department.arrow_forward
- Assume the following tables: Student (studid, email, name) Club (clubid, telephone, name, #president) MemberOf(#clubid, #studid ) Activities(actid, actdt, place) Organize (#actid , #clubid , fee), Use SQL Developer to accomplish the following tasks: Create in PL/SQL a stored procedure called 'StudentsActivities' to display the list of students and the activities in which they participated.arrow_forwardUSE NORTHWIND DATABASE Write a SELECT statement using the CASE function. The statement returns two columns basedon how many orders the customer has in the database. The first column is the customer name andthe second column, Membership Type, is based on which case applies to the customer:If the customer has more than 25 orders they are a Diamond Customer.If the customer has more than 15 orders they are a Gold Customer.If the customer has more than 10 orders they are a Silver Customer.If the customer has more than 5 orders they are an Iron Customer.Otherwise the customer is a Valued Customer.arrow_forwardin SQL, Create a Stored Function to identify the total number of staff assigned to each ward (the input to the function is ward number, the output of the function is the total number of staff assigned to that ward)arrow_forward
- Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL. 1. Write an anonymous block to retrieve the doctor’s ID and name which in charge of certain patient. Allow the user to enter the patient’s ID. 2. Write an anonymous PL/SQL block that will insert a new doctor into the DOCTOR table. Verify that insert has been done successfully by issuing a select * from doctor. 3. Write an anonymous PL/SQL block that will update the salary of all doctors in the Pediatrics area by 1000 (Note: Current salary + 1000). Verify that the salary has been updated by issuing a select * from doctor where area = ‘Pediatrics’. You may have to run the select statement twice to check the data before and after the update. 4. Write an anonymous PL/SQL block that will delete all patients for doctors that works in the Family Practice area. Verify that patients have been deleted accordingly. Then do a select * from patients where doc_id = (select doc_id from doctor where area = ‘Family Practice’.…arrow_forwardInstructor(Instructor_Id, Instructor_Name, specialization, Address,#Department_Id)Department(Department_Id, Department_Name)Course(Course_code, Course_Title,Credit_hours,Contact_hours,#Department_Id)Teaching(#Instructor_Id, #Course_code)a) Express in SQL the following queries:i. Change to 6 the number of contact hours of the course which has thecode “ITDR2104”.ii. Add a new column named instructorLoad of type Number(2) to thetable instructor.iii. List the details of all the courses having a code that starts with“ITDR”. The output should be sorted in descending order by credithours.iv. Find the number of courses taught by the instructor whose name is“Ahmed”.v. List the names of all departments along with the number of coursesoffered by each department.vi. Find the titles of courses, which are taught by the instructor who isnamed “Ahmed”.b) Create a view named instructor_of_DBCourse to contain the details(Instructor_Name, specialization, Address, Department_Id) ofinstructors who teach…arrow_forward12. Write a pl / sql program to calculate the overtime hours using if then and end if .arrow_forward
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology PtrDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning