Concept explainers
a.
Stored procedures:
- A procedure is a collection of procedural and SQL statements.
- A procedure may have input parameter or output parameter or sometimes both the parameters.
- It is declared with a unique name 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_OWNER (I_OWNER_NUM IN OWNER.OWNER_NUM%TYPE) AS
I_FIRST_NAME OWNER.FIRST_NAME%TYPE;
I_LAST_NAME OWNER.LAST_NAME%TYPE;
BEGIN
SELECT FIRST_NAME, LAST_NAME
INTO I_FIRST_NAME, I_LAST_NAME
FROM OWNER
WHERE OWNER_NUM = I_OWNER_NUM;
DBMS_OUTPUT.PUT_LINE(I_OWNER_NUM);
DBMS_OUTPUT.PUT_LINE(I_FIRST_NAME);
DBMS_OUTPUT.PUT_LINE(I_LAST_NAME);
END;
/
Explanation:
- The above query is used to create a procedure named “DISP_OWNER” to select the records in the “OWNER” table.
- Change the “OWNER_NUM” into “I_OWNER_NUM” and place the “FIRST_NAME” and “LAST_NAME” values into “I_FIRST_NAME” and “I_LAST_NAME”.
- After placing these values, display the “I_OWNER_NUM”, “I_FIRST_NAME”, and “I_LAST_NAME” from the “OWNER” table.
- Once the stored procedure is created, it needs to be executed.
Query to view the owner name and ID:
BEGIN
DISP_OWNER ('AN175');
END;
The above query is used to view the owner ID, first name, and last name for the number “AN175”.
Output:
AN175
Bill
Anderson
Explanation of Solution
b.
Query to create stored procedure:
CREATE OR REPLACE PROCEDURE DISP_CONDO_UNIT
(I_CONDO_ID IN CONDO_UNIT.CONDO_ID%TYPE) AS
I_LOCATION_NUM CONDO_UNIT.LOCATION_NUM%TYPE;
I_UNIT_NUM CONDO_UNIT.UNIT_NUM%TYPE;
I_OWNER_NUM CONDO_UNIT.OWNER_NUM%TYPE;
I_FIRST_NAME OWNER.FIRST_NAME%TYPE;
I_LAST_NAME OWNER.LAST_NAME%TYPE;
BEGIN
SELECT LOCATION_NUM, UNIT_NUM, CONDO_UNIT.OWNER_NUM, FIRST_NAME, LAST_NAME
INTO I_LOCATION_NUM, I_UNIT_NUM, I_OWNER_NUM, I_FIRST_NAME, I_LAST_NAME
FROM CONDO_UNIT, OWNER
WHERE CONDO_UNIT.OWNER_NUM = OWNER.OWNER_NUM
AND CONDO_ID = I_CONDO_ID;
DBMS_OUTPUT.PUT_LINE(I_CONDO_ID);
DBMS_OUTPUT.PUT_LINE(I_LOCATION_NUM);
DBMS_OUTPUT.PUT_LINE(I_UNIT_NUM);
DBMS_OUTPUT.PUT_LINE(I_OWNER_NUM);
DBMS_OUTPUT.PUT_LINE(I_FIRST_NAME);
DBMS_OUTPUT.PUT_LINE(I_LAST_NAME);
END;
/
Explanation:
- The above query is used to create a procedure named “DISP_CONDO_UNIT” to select the records in the “CONDO_UNIT” and “OWNER” tables.
- Change the “CONDO_ID” into “I_CONDO_ID” and place the “LOCATION_NUM”, “UNIT_NUM”, “OWNER_NUM”, “FIRST_NAME”, and “LAST_NAME” values into “I_LOCATION_NUM”, “I_UNIT_NUM”, “I_OWNER_NUM”, “I_FIRST_NAME”, and “I_ LAST_NAME”.
- After placing these values, display the “I_CONDO_ID”, I_LOCATION_NUM”, “I_UNIT_NUM”, “I_OWNER_NUM”, “I_FIRST_NAME”, and “I_ LAST_NAME” from the “CONDO_UNIT” and “OWNER” tables.
- Once the stored procedure is created, it needs to be executed.
Query to view a result:
BEGIN
DISP_CONDO_UNIT(3);
END;
The above query is used to view the condo Id, location number, unit number, owner number, first name and last name for the condo id “3”.
Output:
3
1
306
AN175
Bill
Anderson
Explanation of Solution
c.
Query to insert the value:
CREATE OR REPLACE PROCEDURE ADD_OWNER
(I_OWNER_NUM IN OWNER.OWNER_NUM%TYPE,
I_LAST_NAME IN OWNER.LAST_NAME%TYPE,
I_FIRST_NAME IN OWNER.FIRST_NAME%TYPE,
I_ADDRESS IN OWNER.ADDRESS%TYPE,
I_CITY IN OWNER.CITY%TYPE,
I_STATE IN OWNER.STATE%TYPE,
I_POSTAL_CODE IN OWNER.POSTAL_CODE%TYPE) AS
BEGIN
INSERT INTO OWNER (OWNER_NUM, LAST_NAME, FIRST_NAME, ADDRESS, CITY, STATE, POSTAL_CODE)
VALUES
(I_OWNER_NUM, I_LAST_NAME, I_FIRST_NAME, I_ADDRESS, I_CITY, I_STATE,
I_POSTAL_CODE);
END;
/
Explanation:
The above query is used to create a stored procedure named “ADD_OWNER” to insert the new record in the “OWNER” table. Once the stored procedure is created, it needs to be executed.
Query to execute the stored procedure:
BEGIN
ADD_OWNER ('AZ245', 'William', 'John', '125 North St.', 'Mills', 'SC', 29707);
END;
After executing the above query, the new record is inserted into the table “OWNER”.
Output:
Query to view the contents in “OWNER” table is as follows:
SELECT * FROM OWNER;
Screenshot of output
Explanation of Solution
d.
Query to update stored procedure:
CREATE OR REPLACE PROCEDURE CHG_OWNER
(I_OWNER_NUM IN OWNER.OWNER_NUM%TYPE,
I_LAST_NAME IN OWNER.LAST_NAME%TYPE) AS
BEGIN
UPDATE OWNER
SET LAST_NAME = I_LAST_NAME
WHERE OWNER_NUM = I_OWNER_NUM;
END;
/
Explanation:
The above query is used to create a stored procedure named “CHG_OWNER” to update the last name of the owner whose number is stored in “I_OWNER_NUM” to the value presently found in “I_OWNER_NUM”, it needs to be executed.
Query to execute the stored procedure:
BEGIN
CHG_OWNER ('AZ245', 'Andrew');
END;
After executing the above query, the last name is changed in the table “OWNER”.
Output:
Query to view the contents in “OWNER” table is as follows:
SELECT * FROM OWNER;
Screenshot of output
Explanation of Solution
e.
Query to delete the value:
CREATE OR REPLACE PROCEDURE DELETE_OWNER
(I_OWNER_NUM IN OWNER.OWNER_NUM%TYPE) AS
BEGIN
DELETE
FROM OWNER
WHERE OWNER_NUM = I_OWNER_NUM;
END;
/
Explanation:
- The above query is used to create a procedure named “DELETE_OWNER” to delete a record in the “OWNER” table.
- Once the record is deleted, a procedure should create owner number as a parameter.
- Once the stored procedure is created, it needs to be executed.
Query to execute the stored procedure:
BEGIN
DELETE_OWNER ('AZ245');
END;
The above query is used to delete the order number AZ245.
Output:
Query to view the contents in “OWNER” table after deleting the order number AZ245 as follows:
SELECT * FROM OWNER;
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_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_forward
- Pl/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_forwardSchema: Employees (EmpID int, Name varchar(50), Age int, Salary float, DepartmentID int) Departments (DepartmentID int, DepartmentName varchar(50), ManagerID int) Write a query to find the names of all employees who work in the "Sales" department and earn a salary greater than $50,000 per year, sorted by their age in ascending order. In MS SQL pleasearrow_forwardWrite a PL/SQL stored procedure to insert a row in the "employees" table. Use %type to declare each variable of correct/compatible type, matching each column of employee table. The procedure call must send the value to each column at the run time. DEPARTMENTS LOCATIONS * DEPARTMENT_ID * DEPARTMENT_NAME MANAGER_ID LOCATION_ID P P * LOCATION_ID STREET ADDRESS POSTAL_CODE * CITY STATE_PROVINCE F JOB_HISTORY PF EMPLOYEE_ID P * START_DATE END DATE F * JOB_ID COUNTRY_ID F DEPARTMENT_ID EMPLOYEES * EMPLOYEE_ID FIRST_NAME * LAST_NAME U * EMAIL PHONE_NUMBER HIRE_DATE * JOB_ID SALARY P COUNTRIES * COUNTRY_ID COUNTRY_NAME F P JOBS REGION_ID * JOB_ID * JOB_TITLE MIN_SALARY MAX_SALARY P COMMISSION_PCT REGIONS F MANAGER_ID * REGION_ID REGION_NAME F DEPARTMENT_ID IParrow_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_forwardthis is SQL.arrow_forwarduse sql to answer the following question an erd also the pl/sql procedure has been providing as below shown Logon Procedure. Code: create or replace PROCEDURE member_ck_sp (p_user IN VARCHAR2, p_pass IN VARCHAR2, p_FullName OUT VARCHAR2) IS failour_msg varchar2(50) := 'INVALID USER NAME'; CURSOR MEMBER_CUR IS SELECT firstname, lastname, cookie, username, password FROM bb_shopper WHERE upper(username) = upper(p_user) AND password = p_pass; BEGIN p_FullName := failour_msg; FOR REC_CUR IN MEMBER_CUR LOOP p_FullName := rec_cur.firstname || ' ' || rec_cur.lastname; dbms_output.put_line('Welcome: ' || p_pass || 'Cookie: ' || rec_cur.cookie); END LOOP; if p_FullName = failour_msg then dbms_output.put_line(failour_msg); end if; END member_ck_sp; Task2 : Updating Order Status Create a procedure named STATUS_SHIP_SP that allows an employee in the Shipping. Department to update an order status to add shipping information. The BB_BASKETSTATUS table lists events for each order so that a…arrow_forward
- Nortwind database: One of our suppliers "Bigfoot Breweries" just went out of business. Write the SQL to mark all of their products as discontinued by setting IsDiscontinued = 1 for all products they supply. Note: This question requires you to write SQL syntax ONLY. Absolutely no python code should be used. Only the SQL itself.arrow_forwardT-SQL procedure for MICROSOFT SQL SERVER A: obtain the name and credit limit of the customer whose number currently is stored in I_CUSTOMER_NUM. Place these values in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. Output the content of I_CUSTOMER_NAME and I_CREDIT_LIMIT. HINT use cursor instructions as a template for the problem. Instructions goes as follows CREATE PROCEDURE usp_DISP_REP_CUST @repnum char(2) AS DECLARE@custnum char(3) DECLARE@custname char(35) DECLARE mycursor CURSOR READ_ONLY FOR SELECT CUSTOMER_NUM, CUSTOMER_NAME FROM CUSTOMER WHERE REP_NUM = @repnum OPEN mycursor FETCH NEXT FROM mycursor INTO @custnum, @custname WHILE @@FETCH_STATUS=0 BEGIN PRINT@custnum+' '+@custname FETCH NEXT FROM mycursor INTO @custnum, @custname END CLOSE mycursor DEALLOCATE mycursorarrow_forwardSQL Database Write PL/SQL or T-SQL procedures to retrieve and output the reservation ID, trip ID, number of persons, and trip price for every reservation whose customer number is stored in I_CUSTOMER_NUMarrow_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