Information
AI Chat

View SPTrigger My SQL - Notes with simple explanations and examples.

Notes with simple explanations and examples.

Course

Introduction to Databases and Databases Management Systems:Part 1 (CSIQ2634)

37 Documents

Students shared 37 documents in this course

University

University of the Free State

Academic year: 2020/2021

Uploaded by:

Anonymous Student

This document has been uploaded by a student, just like you, who decided to remain anonymous.

University of the Free State

Comments

Please sign in or register to post comments.

Preview text

Views, Stored Procedures, Functions, and

Triggers

A view is a “virtual” table that is derived from other tables

Allows for limited update operations

Since the table may not physically be stored

Allows full query operations

There are two ways to implement a view:

Approach 1: Query modification

Modify the view query into a query on the underlying base tables
Example: SELECT * FROM Manager WHERE Salary > 100000 becomes SELECT Fname, Lname, Dname, Dnumber, Salary FROM EMPLOYEE, DEPARTMENT WHERE SSN=MgrSSN AND Salary > 100000
Disadvantage: Inefficient for views defined via complex queries

Approach 2: View materialization

Involves physically creating and keeping a temporary table
Concerns: Maintaining correspondence between the base table and the view when the base table is updated

ORACLE

CREATE MATERIALIZEDVIEW or CREATE SNAPSHOT

!"

A stored procedure contains a sequence of SQL commands stored in the database catalog so that it can be invoked later by a program

Stored procedures are declared using the following syntax: Create Procedure<proc-name> (param_spec 1 , param_spec 2 , ..., param_specn) begin -- execution code end;

where each param_spec is of the form: [in | out | inout] <param_name> <param_type>

in mode: allows you to pass values into the procedure,
out mode: allows you to pass value back from procedure to the calling program

Suppose we want to keep track of the total salaries of employees working for each department

We need to write a procedure to update the salaries in the deptsal table

Step 2:

Define a procedure called updateSalary which takes as input a department number.
The body of the procedure is an SQL command to update the totalsalary column of the deptsal table.
Terminate the procedure definition using the delimiter you had defined in step 1 (//)

Step 3: Change the delimiter back to semicolon (;)

Step 5: Show the updated total salary in the deptsal table

!"

Use show procedure status to display the list of stored

procedures you have created

Use drop procedure to remove a stored procedure

" $"

The previous procedure updates one row in deptsal table

based on input parameter

Suppose we want to update all the rows in deptsal

simultaneously

First, let’s reset the totalsalary in deptsal to zero

" $"

Drop the old procedure

Use cursor to iterate the rows

%

Create a procedure to give a raise to all employees

%

Was this document helpful?