Concept explainers
a.
Stored procedures:
- When user expects to running a particular query often, user can expand total performance by saving the query in a file called a stored procedure.
- The stored procedure is located on the server.
- The DBMS compiles the stored procedure and creates an execution plan, which is the well-organized method of finding the results.
- It is a procedure which contains collection of procedural and SQL statements.
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_GUIDE (I_GUIDE_NUM IN GUIDE.GUIDE_NUM%TYPE) AS
I_LAST_NAME GUIDE.LAST_NAME%TYPE;
I_FIRST_NAME GUIDE.FIRST_NAME%TYPE;
BEGIN
SELECT LAST_NAME, FIRST_NAME
INTO I_LAST_NAME, I_FIRST_NAME
FROM GUIDE
WHERE GUIDE_NUM = I_GUIDE_NUM;
DBMS_OUTPUT.PUT_LINE(I_GUIDE_NUM);
DBMS_OUTPUT.PUT_LINE(RTRIM(I_FIRST_NAME)||' '||RTRIM(I_LAST_NAME));
END;
/
Explanation:
- The above query is used to create a procedure named “DISP_GUIDE” to select the records in the “GUIDE” table.
- Change the “GUIDE_NUM” into “I_GUIDE_NUM” and place the “LAST_NAME” and “FIRST_NAME” values into “I_LAST_NAME” and “I_FIRST_NAME”.
- After placing these values, display the “I_GUIDE_NUM” and “I_FIRST_NAME” and “I_LAST_NAME” with separated by a space from the “GUIDE” table.
- Once the stored procedure is created, it needs to be executed.
Query to view the guide number and its first name and last name of guide with space:
BEGIN
DISP_GUIDE ('GZ01');
END;
The above query is used to view the guide number and its first name and last name with separated by a space.
Output:
GZ01
Zach Gregory
Explanation of Solution
b.
Query to create stored procedure:
CREATE OR REPLACE PROCEDURE DISP_RESERVATION_INFO (I_RESERVATION_ID IN RESERVATION.RESERVATION_ID%TYPE) AS
I_NUM_PERSONS RESERVATION.NUM_PERSONS%TYPE;
I_CUSTOMER_NUM CUSTOMER.CUSTOMER_NUM%TYPE;
I_LAST_NAME CUSTOMER.LAST_NAME%TYPE;
BEGIN
SELECT NUM_PERSONS, CUSTOMER.CUSTOMER_NUM, LAST_NAME
INTO I_NUM_PERSONS, I_CUSTOMER_NUM, I_LAST_NAME
FROM RESERVATION, CUSTOMER
WHERE RESERVATION.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NUM
AND RESERVATION_ID = I_RESERVATION_ID;
DBMS_OUTPUT.PUT_LINE(I_NUM_PERSONS);
DBMS_OUTPUT.PUT_LINE(I_CUSTOMER_NUM);
DBMS_OUTPUT.PUT_LINE(I_LAST_NAME);
END;
/
Explanation:
- The above query is used to create a procedure named “DISP_RESERVATION_INFO” to select the records in the “CUSTOMER” and “RESERVATION” tables.
- Change the “RESERVATION_ID” into “I_RESERVATION_ID” and place the “NUM_PERSONS”, “CUSTOMER_NUM”, and “LAST_NAME” values into “I_NUM_PERSONS”, “I_CUSTOMER_NUM”, and “I_LAST_NAME”.
- After placing these values, display the “I_NUM_PERSONS”, “I_CUSTOMER_NUM”, and “I_LAST_NAME” from the “CUSTOMER” and “RESERVATION” tables.
- Once the stored procedure is created, it needs to be executed.
Query to view the number of persons, customer number and customer last name:
BEGIN
DISP_RESERVATION_INFO (1600020);
END;
The above query is used to view the number of persons, customer number and customer last name for the reservation ID “1600020”.
Output:
2
124
Busa
Explanation of Solution
c.
Query to insert the value:
CREATE OR REPLACE PROCEDURE ADD_GUIDE
(I_GUIDE_NUM IN GUIDE.GUIDE_NUM%TYPE,
I_LAST_NAME IN GUIDE.LAST_NAME%TYPE,
I_FIRST_NAME IN GUIDE.FIRST_NAME%TYPE) AS
BEGIN
INSERT INTO GUIDE (GUIDE_NUM, LAST_NAME, FIRST_NAME)
VALUES
(I_GUIDE_NUM, I_LAST_NAME, I_FIRST_NAME);
END;
/
Explanation:
The above query is used to create a stored procedure named “ADD_GUIDE” to insert the new record in the “GUIDE” table. Once the stored procedure is created, it needs to be executed.
Query to execute the stored procedure:
BEGIN
ADD_GUIDE ('QR01', 'John', 'Merry');
END;
After executing the above query, the new record is inserted into the table “GUIDE”.
Output:
Query to view the contents in “GUIDE” table is as follows:
SELECT * FROM GUIDE;
Screenshot of output
Explanation of Solution
d.
Query to update stored procedure:
CREATE OR REPLACE PROCEDURE CHANGE_GUIDE_LASTNAME
(I_GUIDE_NUM IN GUIDE.GUIDE_NUM%TYPE,
I_LAST_NAME GUIDE.LAST_NAME%TYPE) AS
BEGIN
UPDATE GUIDE
SET LAST_NAME = I_LAST_NAME
WHERE GUIDE_NUM = I_GUIDE_NUM;
END;
/
Explanation:
The above query is used to create a stored procedure named “CHANGE_GUIDE_LASTNAME” to change the last name of the guide whose number is stored in “I_GUIDE_NUM” to the value presently found in “I_LAST_NAME”, it needs to be executed.
Executing the stored procedure:
The content of “GUIDE” table before creating the procedure is given below:
Query to view the contents in “GUIDE” table is as follows:
SELECT * FROM GUIDE;
Screenshot of output
Query to execute the stored procedure:
BEGIN
CHANGE_GUIDE_LASTNAME ('GZ01', 'Rose');
END;
/
After executing the above query, the last name is changed for the guide number “GZ01” in the “GUIDE” table.
Output:
Query to view the contents in “GUIDE” table is as follows:
SELECT * FROM GUIDE;
Screenshot of output
Explanation of Solution
e.
Query to delete the value:
CREATE OR REPLACE PROCEDURE DELETE_GUIDE_RECORD
(I_GUIDE_NUM IN GUIDE.GUIDE_NUM%TYPE) AS
BEGIN
DELETE
FROM GUIDE
WHERE GUIDE_NUM = I_GUIDE_NUM;
END;
/
Explanation:
- The above query is used to create a procedure named “DELETE_GUIDE_RECORD” to delete a record in the “GUIDE” table.
- Once the record is deleted, a procedure should create guide number as a parameter.
- Once the stored procedure is created, it needs to be executed.
Executing the stored procedure:
The content of “GUIDE” table before creating the procedure is given below:
Query to view the contents in “GUIDE” table is as follows:
SELECT * FROM GUIDE;
Screenshot of output
Query to execute the stored procedure:
BEGIN
DELETE_GUIDE_RECORD ('QR01');
END;
The above query is used to delete the record of guide number ‘QR01’.
Output:
Query to view the contents in “GUIDE” table after deleting the guide number ‘QR01’ as follows:
SELECT * FROM GUIDE;
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_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_forwardNote: 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_forward
- Instructor(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_forwardCreate a Procedure in PL/SQL takes employee_id, rate_salary, rate_commission. This function will update his commission if null to rate_commission, and increase his salary by the rate_salary. Call this Procedure from PL/SQL block by input the employee_id from the end user.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_forward
- Imagine a student table with following columns: Student(Stuid, Name , Age, Major)Write a PL/SQL program to create a cursor and to display all the records from student table.arrow_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_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
- Use SQL Developer or Oracle Live SQL to write the appropriate SQL commands as follows: A) FUNCTION: Use the region table to create a function called ‘Return_region_name’ which accepts an input parameter ‘region_id’, and print the name of the region with that id. Then execute this function with region_id = 1. [LAB-7] (3) B) PROCEDURE: Use the cust_node table to create a procedure called ‘Get_node_id’ that takes the cust_id as an input parameter to show its corresponding node id to the banker. Then execute this procedure with cust_id=5. [LAB-7] (2) C) Trigger: Create a row-level trigger called ‘Compute_Amount_trigger’ on the cust_txn table to automatically update the amount of withdraw transaction made after inserting or updating amount on the cust_txn table. Additionally, this trigger must show this statement ‘TRIGGER MESSAGE: The amount of cust_id and txn_type is modified successfully'. Then test your trigger by updating the cust_txn table. For example, update the amount of the…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_forwardIn the preceding PL/SQL block, determine the values and data types for each of the following cases. a. The value of v_customer in the nested block is: b. The value of v_name in the nested block is: c. The value of v_credit_rating in the nested block is: d. The value of v_customer in the main block is: e. The value of v_name in the main block is: f. The value of v_credit_rating in the main block is: de Academyarrow_forward
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr