CHANDIGARH UNIVERSITY Institute of Distance and Online Learning Course Development Committee Prof. (Dr.) R.S.Bawa Pro Chancellor, Chandigarh University, Gharuan, Punjab Advisors Prof. (Dr.) Bharat Bhushan, Director – IGNOU Prof. (Dr.) Majulika Srivastava, Director – CIQA, IGNOU Programme Coordinators & Editing Team Master of Business Administration (MBA) Bachelor of Business Administration (BBA) Coordinator – Dr. Rupali Arora Coordinator – Dr. Simran Jewandah Master of Computer Applications (MCA) Bachelor of Computer Applications (BCA) Coordinator – Dr. Raju Kumar Coordinator – Dr. Manisha Malhotra Master of Commerce (M.Com.) Bachelor of Commerce (B.Com.) Coordinator – Dr. Aman Jindal Coordinator – Dr. Minakshi Garg Master of Arts (Psychology) Bachelor of Science (Travel &TourismManagement) Coordinator – Dr. Samerjeet Kaur Coordinator – Dr. Shikha Sharma Master of Arts (English) Bachelor of Arts (General) Coordinator – Dr. Ashita Chadha Coordinator – Ms. Neeraj Gohlan Academic and Administrative Management Prof. (Dr.) R. M. Bhagat Prof. (Dr.) S.S. Sehgal Executive Director – Sciences Registrar Prof. (Dr.) Manaswini Acharya Prof. (Dr.) Gurpreet Singh Executive Director – Liberal Arts Director – IDOL © No part of this publication should be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording and/or otherwise without the prior written permission of the authors and the publisher. SLM SPECIALLY PREPARED FOR CU IDOL STUDENTS Printed and Published by: TeamLease EdtechLimited www.teamleaseedtech.com CONTACT NO:01133002345 For: CHANDIGARH UNIVERSITY Institute of Distance and Online Learning
PL/SQL LAB Course Code: 21MCA638 Credit: 4 Course Objectives: To equip the students with the fundamentals concepts necessary for designing, using and implementing database systems and applications. To learn about the Basics of basic programming constructs To develop skills for implement Functions To learn the concept of trigger, cursor, functions stored procedure, exception handling. PRACTICAL UNIT 1: INSTALLATION OF DATABASE MANAGEMENT SYSTEM 1 Installation of Database Management System A database management system (DBMS) is a software package designed to define, manipulate, retrieve, and manage data in a database. The system allows deleting, adding, and modifying databases. SQL Server databases are some of the most common databases in use, thanks in part to how easy it is to create and maintain them. With a free graphical user interface (GUI) program such as SQL Server Management, you don't need to worry about fumbling around with the command line. See Step 1 below to create a database and start entering your information in just a few minutes.
1. Install the SQL Server Management Studio software. This software is available for free from Microsoft, and allows you to connect to and manage your SQL server from a graphical interface instead of having to use the command line. In order to connect to a remote instance of an SQL server, you will need this or similar software. 2.Startup SQL Server Management Studio-When you first start the program, you will
be asked what server you would like to connect to. If you already have a server up and running, and have the permissions necessary to connect to it, you can enter the server address and authentication information. If you want to create a local database, set the Database Name to and the authentication type to \"Windows Authentication\". 3. Locate the Database folder. After the connection to the server, either local or remote, is made, the Object Explorer window will open on the left side of the screen. At the top of the Object Explorer tree will be the server you are connected to. if it is not expanded, click the \"+\" icon next to it. Located the Databases folder.
4.Create a new database-Right-click on the Databases folder and select \"New Database...\". A window will appear, allowing you to configure the database before creating it. Give the database a name that will help you identify it. Most users can leave the rest of the settings at their default. You will note that as you type the database name, two additional files will be created automatically: The Data and the Log file. The data file houses all of the data in your database, while the log file tracks changes to the database. Click OK to create the database. You will see your new database appear in the expanded Database folder. It will have a cylinder icon.
5. Create a table. A database can only store data if you create a structure for that data. A table holds the information that you enter into your database, and you will need to create it before you can proceed. Expand the new database in your Database folder, and right-click on the Tables folder and select \"New Table...” Windows will open on the rest of the screen which will allow you to manipulate your new table.
6. Create the Primary Key. It is highly recommended that you create a Primary Key as the first column on your table. This acts as an ID number, or record number, that will allow you to easily recall these entries later. To create this, enter \"ID\" in the Column Name field, type int into the Data Type field, and uncheck the \"Allow Nulls.\" Click the Key icon in the toolbar to set this column as the Primary Key. You don't want to allow null values because you always want the entry to be at least \"1\". If you allow nulls, your first entry will be \"0\". In the Column Properties window, scroll down until you find the Identity Specification option. Expand it and set \"(ls Identity)\" to \"Yes\". This will automatically increase the value of the ID column for each entry, effectively automatically numbering each new entry. 7. Understand how tables are structured. Tables are composed of fields or columns. Each column represents one aspect of a database entry. For example, if you were creating a database of employees, you might have a \"FirstName\" column, a \"LastName\" column, an \"Address\" column, and a \"PhoneNumber\" column.
8. Create the rest of your columns. When you finish filling out the fields for the Primary Key, you will notice that new fields appear underneath it. These allow you to enter in your next column. Fill out the fields as you see fit, and ensure that you pick the right data type for the information that will be entered in that column: nc har ( # ) - This is the data type you should use for text, such as names, addresses, etc. The number in parentheses is the maximum number of characters allowed for this field. Setting a limit ensures that your database size stays manageable. Phone numbers should be stored with this format, as you don't perform mathematical functions on them. int - This is for whole numbers, and is typically used in the ID field. dec ima l( x, y) - This will store numbers in decimal form, and the numbers within the parentheses denote the total number of digits and the number digits following the decimal, respectively. For example, dec ima l ( 6, 2) would store numbers as 0000.00.
9. Save your table. When you are finished creating your columns, you will need to save the table before entering information. Click the Save icon in the toolbar, and then enter in a name for the table. Naming your table in a way that helps you recognize the contents is advisable, especially for larger databases with multiple tables. 10. Add data to your table. Once you've saved your table, you can begin adding data to it. Expand the Tables folder in the Object Explorer window. If your new table is not
listed, right-click on the Tables folder and select Refresh. Right-click on the table and select \"Edit Top 200 Rows\". The center window will display fields for you to begin entering data. Your ID field will be filled automatically, so you can ignore it right now. Fill out the information for the rest of the fields. When you click on the next row, you will see the ID field in the first-row fill automatically. Continue this process until you've entered all the information you need. 11. Execute the table to save the data. Click the Execute SQL button on the toolbar when you are finished entering the information to save it to the table. The SQL server will run in the background, parsing all of the data into the columns you created. The button looks like a red exclamation point. You can also press Ctrl+R to execute as well. If there are any errors, you will be shown which entries are filled out incorrectly before the table can be executed.
12. Query your data. At this point, your database has been created. You can create as many tables as you need within each database (there is a limit, but most users will not need to worry about that unless they are working on enterprise-level databases). You can now query your data for reports or any other administrative purposes.
PRACTICAL UNIT 2: PROGRAMS TO UNDERSTAND BASIC PROGRAMMING CONSTRUCTS 1. Write a PL/SQL block to calculate the incentive of an employee whose ID is 110. PL/SQL Code: Output 2. Write a PL/SQL block to show single and multiline comments. PL/SQL Code:
Output: 3. Write a PL/SQL block to learn how to declare a character type variable. PL/SQL Code: DROP TABLE test; CREATE TABLE test (col1 CHAR(5)); DECLARE var1 VARCHAR2(5 CHAR) := 'abc '; BEGIN INSERT INTO test(col1) VALUES(var1); END; /
Sample Output: PL/SQL procedure successfully completed
PRACTICAL UNIT 3: PROGRAM RELATED TO CONTROL STRUCTURES 1. Write a PL/SQL program to arrange the number of two variable in such a way that the small number will store in num_small variable and large number will store in num_large variable. PL/SQL Code: Sample Output: 2. Write a PL/SQL block to define Variable (adding two numbers).
PL/SQL Code: Sample Output: Program based LOOP WHEN EXIT statement. 1 Write a program in PL/SQL to print the value of a variable inside and outside a loop using LOOP WHEN EXIT statement. PL/SQL Code:
/ Sample Output:
PRACTICAL UNIT 4: QUERIES FOR INSERTING, DELETING, UPDATING AND SELECTING THE CONTENTS OF TABLE. PERFORM THE COMMANDS- TRUNCATE, DROP, ALTER ON THE TABLE Queries for inserting the contents of table. 1 Write a SQL statement to insert a record with your own value into the table countries against each column. Here in the following is the structure of the table countries. PL/SQL Code:
Sample Output: Queries for deleting the contents of table. 1 Delete all the records from the Customers table where the Country value is 'Norway'. Sample output Queries updating the contents of table. 2 Write a SQL statement for a table. Hereis the sample table customer.
Query Update the City column of all records in the Customers table. Sample output Update the City column of all records in the Customers table. Queries updating the contents of table. 3 Write a query in SQL to display the first name and last name of employees working for the department which allotment amount is more than Rs.50000. Sample Table: Emp_Department
Sample Table: Emp_Details Sample Solution: Output of the query:
Truncate on the table 1 Use the TRUNCATE statement to delete all data inside a table. Query Persons; Statement output Drop on the table 1 Write the correct SQL statement to delete a table called Persons. SQL Statement Statement output
Alter on the table 1 Add a column of type DATE called Birthday. SQL statement Persons ; Statement output
PRACTICAL UNIT 5: PROGRAMS RELATED EXPLICIT CURSORS 1 Write a program in PL/SQL to create an explicit cursor with for loop. PL/SQL Code: Cursor: A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set. Explicit Cursor: Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row. The syntax for creating an explicit cursor is − CURSOR cursor_name IS select_statement; Working with an explicit cursor includes the following steps − Declaring the cursor for initializing the memory Opening the cursor for allocating the memory Fetching the cursor for retrieving the data Closing the cursor to release the allocated memory Declaring the Cursor Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example −
CURSOR c_customers IS SELECT id, name, address FROM customers; Opening the Cursor Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open the above defined cursor as follows − OPEN c_customers; Fetching the Cursor Fetching the cursor involves accessing one row at a time. For example, we will fetch rows from the above-opened cursor as follows − FETCH c_customers INTO c_id, c_name, c_addr; Closing the Cursor Closing the cursor means releasing the allocated memory. For example, we will close the above-opened cursor as follows − CLOSE c_customers; Example Following is a complete example to illustrate the concepts of explicit cursors &minua; DECLARE
c_id customers.id%type; c_name customer.name%type; c_addr customers.address%type; CURSOR c_customers is SELECT id, name, address FROM customers; BEGIN OPEN c_customers; LOOP FETCH c_customers into c_id, c_name, c_addr; EXIT WHEN c_customers%notfound; dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); END LOOP; CLOSE c_customers; END; / When the above code is executed at the SQL prompt, it produces the following result − 1 Ramesh Ahmedabad 2 Khilan Delhi 3 kaushik Kota
4 Chaitali Mumbai 5 Hardik Bhopal 6 Komal MP PL/SQL procedure successfully completed. Sample Output:
2. Write a program in PL/SQL to show the uses of static PL/SQL statement. PL/SQL Code: Static SQL refers to those SQL statements which are fixed and can be hard coded into the application. As static sqls are fixed queries, these statements can be analysed and optimized and do not require any specific handling for security purpose. Uses: In Static SQL, database access procedure is predetermined in the statement. Static SQL statements are more faster and efficient. Static SQL statements are compiled at compile time. Static SQL is used in case of uniformly distributed data. Statements like EXECUTE IMMEDIATE, EXECUTE, PREPARE are not used. Static SQL is less flexible.
Sample Output: 3 Create a PL/SQL block to increase salary of employees in the department 50 using WHERE CURRENT OF clause. PL/SQL Code:
Sample Output:
PRACTICAL UNIT 6: PROGRAMS RELATED IMPLICIT CURSORS 1 Write a program in PL/SQL to create an implicit cursor with for loop. PL/SQL Code: Sample Output:
2 Write A Program in Pl/SQL To Find the Number of Rows Effected by The Use of Sql%Rowcount Attributes of An Implicit Cursor. PL/SQL Code: Both Implicit cursor and the explicit cursor has certain attributes that can be accessed. These attributes give more information about the cursor operations. Below are the different cursor attributes and their usage. Cursor Attribute Description %FOUND It returns the Boolean result 'TRUE' if the most recent fetch operation fetched a record successfully, else it will return FALSE. %NOTFOUND This works oppositely to %FOUND it will return 'TRUE' if the most recent fetch operation could not able to fetch any record. %ISOPEN It returns Boolean result 'TRUE' if the given cursor is already opened, else it returns 'FALSE' %ROWCOUNT It returns the numerical value. It gives the actual count of records that got affected by the DML activity. Example 1: In this example, we are going to see how to declare, open, fetch and close the explicit cursor.
DECLARE CURSOR guru99_det IS SELECT emp_name FROM emp; lv_emp_name emp.emp_name%type; BEGIN OPEN guru99_det; LOOP FETCH guru99_det INTO lv_emp_name; IF guru99_det%NOTFOUND THEN EXIT; END IF; Dbms_output.put_line(‘Employee Fetched:‘||lv_emp_name); END LOOP; Dbms_output.put_line(‘Total rows fetched is‘||guru99_det%R0WCOUNT); CLOSE guru99_det; END: /
Output Employee Fetched:BBB Employee Fetched:XXX Employee Fetched:YYY Total rows fetched is 3 Sample Output:
PRACTICAL UNIT 7: COMBINING MULTIPLE TABLES USING JOIN 1 Write a query to find those customers with their name and those salesmen with their name and city who lives in the same city Joins: A JOIN clause is used to combine rows from two or more tables, based on a related column between them. Let's look at a selection from the \"Orders\" table: OrderID CustomerID OrderDate 10308 2 1996-09-18 10309 37 1996-09-19 10310 77 1996-09-20 Then, look at a selection from the \"Customers\" table: OrderID CustomerID ContactName Country Germany 1 Alfreds Futterkiste Maria Anders Mexico 2 Ana Trujillo Ana Trujillo Emparedados y helados
3 Antonio Moreno Antonio Moreno Mexico Taquería Notice that the \"CustomerID\" column in the \"Orders\" table refers to the \"CustomerID\" in the \"Customers\" table. The relationship between the two tables above is the \"CustomerID\" column. Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables: Example SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID; Output: OrderID CustomerName OrderDate 10308 Ana Trujillo Emparedados y helados 9/18/1996 10365 Antonio Moreno Taquería 11/27/1996 10383 Around the Horn 12/16/1996 10355 Around the Horn 11/15/1996 10278 Berglunds snabbköp 8/12/1996 Different types of Joins:
Here are the different types of the JOINs in SQL: (INNER) JOIN: Returns records that have matching values in both tables LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table Sample Table: Salesman Sample table: salesman Query:
Output of the query: 2 Write a SQL statement to display all those orders by the customers not located in the same cities where their salesmen live. Sample table: salesman Sample table: customer Sample table: orders
Query: Output of the query:
PRACTICAL UNIT 8: PROGRAMS TO IMPLEMENT FUNCTIONS 1 To create a function that takes the name as input and returns the welcome message as output. FUNCTIONS: A function is same as a procedure except that it returns a value. Therefore, all the discussions of the previous chapter are true for functions too. Creating a Function A standalone function is created using the CREATE FUNCTION statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows − CREATE [OR REPLACE] FUNCTION function_name [(parameter_name [IN | OUT | IN OUT] type [, ...])] RETURN return_datatype {IS | AS} BEGIN < function_body > END [function_name]; Where, function-name specifies the name of the function. [OR REPLACE] option allows the modification of an existing function. The optional parameter list contains name, mode and types of the parameters. IN represents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure.
The function must contain a return statement. The RETURN clause specifies the data type you are going to return from the function. function-body contains the executable part. The AS keyword is used instead of the IS keyword for creating a standalone function. Example The following example illustrates how to create and call a standalone function. This function returns the total number of CUSTOMERS in the customers table. We will use the CUSTOMERS table, which we had created in the PL/SQL Variables – Select * from customers; ID Name Age Address Salary Ahmedabad 2000.00 1 Ramesh 32 Delhi 1500.00 Kota 2000.00 2 Khilan 25 Mumbai 6500.00 Bhopal 8500.00 3 kaushik 23 MP 4500.00 4 Chaitali 25 5 Hardik 27 6 Komal 22
CREATE OR REPLACE FUNCTION totalCustomers RETURN number IS total number(2) := 0; BEGIN SELECT count(*) into total FROM customers; RETURN total; END; / When the above code is executed using the SQL prompt, it will produce the following result − Function created. Calling a Function While creating a function, you give a definition of what the function has to do. To use a function, you will have to call that function to perform the defined task. When a program calls a function, the program control is transferred to the called function. A called function performs the defined task and when its return statement is executed or when the last end statement is reached, it returns the program control back to the main program. To call a function, you simply need to pass the required parameters along with the function name and if the function returns a value, then you can store the returned value. Following program calls the function totalCustomers from an anonymous block − DECLARE c number(2);
BEGIN c := totalCustomers(); dbms_output.put_line('Total no. of Customers: ' || c); END; / When the above code is executed at the SQL prompt, it produces the following result − Total no. of Customers: 6 PL/SQL procedure successfully completed. Example The following example demonstrates Declaring, Defining, and Invoking a Simple PL/SQL Function that computes and returns the maximum of two values. DECLARE a number; b number; c number; FUNCTION findMax(x IN number, y IN number) RETURN number IS z number; BEGIN IF x > y THEN z:= x; ELSE Z:= y; END IF; RETURN z; END;
BEGIN a:= 23; b:= 45; c := findMax(a, b); dbms_output.put_line(' Maximum of (23,45): ' || c); END; / When the above code is executed at the SQL prompt, it produces the following result − Maximum of (23,45): 45 PL/SQL procedure successfully completed. PL/SQL Recursive Functions When a subprogram calls itself, it is referred to as a recursive call and the process is known as recursion. To illustrate the concept, let us calculate the factorial of a number. Factorial of a number n is defined as − n! = n*(n-1)! = n*(n-1)*(n-2)! ... = n*(n-1)*(n-2)*(n-3)... 1 The following program calculates the factorial of a given number by calling itself recursively − DECLARE num number; factorial number;
FUNCTION fact(x number) RETURN number IS f number; BEGIN IF x=0 THEN f := 1; ELSE f := x * fact(x-1); END IF; RETURN f; END; BEGIN num:= 6; factorial := fact(num); dbms_output.put_line(' Factorial '|| num || ' is ' || factorial); END; / When the above code is executed at the SQL prompt, it produces the following result − Factorial 6 is 720 PL/SQL procedure successfully completed. Query
Output Using date functions 1 Write a query to display the first day of the month (in datetime format) three months before the current month Sample current date : 2014-09-03 Expected result : 2014-06-01 Sample Code Sample Output: String Functions 1 Write a query to fill the maximum and minimum salary with leading asterisks whetherthese two columns do not contain a seven-digit number.
STRING FUNCTIONS: The string in PL/SQL is actually a sequence of characters with an optional size specification. The characters could be numeric, letters, blank, special characters or a combination of all. PL/SQL offers three kinds of strings − Fixed-length strings − In such strings, programmers specify the length while declaring the string. The string is right-padded with spaces to the length so specified. Variable-length strings − In such strings, a maximum length up to 32,767, for the string is specified and no padding takes place. Character large objects (CLOBs) − These are variable-length strings that can be up to 128 terabytes. PL/SQL strings could be either variables or literals. A string literal is enclosed within quotation marks. For example, 'This is a string literal.' Or 'hello world' To include a single quote inside a string literal, you need to type two single quotes next to one another. For example, 'this isn't what it looks like' Declaring String Variables
Oracle database provides numerous string datatypes, such as CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB. The datatypes prefixed with an 'N' are 'national character set' datatypes, that store Unicode character data. If you need to declare a variable-length string, you must provide the maximum length of that string. For example, the VARCHAR2 data type. The following example illustrates declaring and using some string variables − DECLARE name varchar2(20); company varchar2(30); introduction clob; choice char(1); BEGIN name := 'John Smith'; company := 'Infotech'; introduction := ' Hello! I''m John Smith from Infotech.'; choice := 'y'; IF choice = 'y' THEN dbms_output.put_line(name); dbms_output.put_line(company); dbms_output.put_line(introduction);
END IF; END; / When the above code is executed at the SQL prompt, it produces the following result − John Smith Infotech Hello! I'm John Smith from Infotech. PL/SQL procedure successfully completed To declare a fixed-length string, use the CHAR datatype. Here you do not have to specify a maximum length for a fixed-length variable. If you leave off the length constraint, Oracle Database automatically uses a maximum length required. The following two declarations are identical − red_flag CHAR(1) := 'Y'; red_flag CHAR := 'Y'; PL/SQL String Functions and Operators PL/SQL offers the concatenation operator (||) for joining two strings. The following table provides the string functions provided by PL/SQL − S.No Function & Purpose
1 ASCII(x); Returns the ASCII value of the character x. 2 CHR(x); Returns the character with the ASCII value of x. 3 CONCAT(x, y); Concatenates the strings x and y and returns the appended string. 4 INITCAP(x); Converts the initial letter of each word in x to uppercase and returns that string. 5
Search
