RIGA TECHNICAL UNIVERSITYFACULTY OF COMPUTER SCIENCE AND INFORMATION

TECHNOLOGY INSTITUTE OF APPLIED COMPUTER SYSTEMS

Homework #2 “Large Databases”

Database server programming fundamentals. Use of SQL extension language – PL/SQL

Author: Oleg TsoyCourse, group: ADBD0Student card no.: 131ADB042

2014 / 2015 study year

Content

1 Goal...........................................................................................................................................32 Task...........................................................................................................................................33 Database description..................................................................................................................44 SQL queries...............................................................................................................................55 Conclusions.............................................................................................................................256 References...............................................................................................................................26

1 GoalLearn more about creation of object-relational database (ORDB) data storage structures and data extraction.

2 Task

1. Use of implicit defined cursor (SELECT ... INTO) for data extraction from database table to PL/SQL indexed table (associative array). Data output with package DBMS_OTPUT function PUT_LINE() (use of server text buffer).

2. Use of explicit defined cursor (CURSOR ... IS) for data extraction from database table to PL/SQL table with collection. Data output.

3. Use of BULK_COLLECT construction for data extraction from database table to PL/SQL array. Data output.

4. Use of cursor variable for data extraction from database table to PL/SQL table with collection. Data output.

5. Create a PL/SQL function, which use cursor variable for data extraction. The returned value is aggregate (SUM(), COUNT(), ... ) of extracted rows column values.

6. Create INSERT and UPDATE type triggers. Test their work (present results).

7. Create INSTEAD OF type trigger to view, which is created from two tables. Test its work (present results).

8. Give the examples for dynamic SQL un PL/SQL use.

9. Conclusions (what seems good, what bad, what like, what is problematic).

3 SQL queries

1. Query goal (CREATE a table Project_Table): Query SQL code

CREATE TABLE Project_Table ( P_Level NUMBER, P_Num NUMBER, Resp_Dept NUMBER );

Result of execution

Analysis of results, what in these data can be seen: Firstly, I organized the basic parts of my Database system and create a table Project_Table.

2. Query goal (CREATE a table Employee_table): Query SQL code

CREATE TABLE Employee_table ( E_Num NUMBER NOT NULL, E_Name VARCHAR2(10), E_Job VARCHAR2(9), Mgr NUMBER(4), Hiredate DATE, Salary NUMBER(7,2), Comm NUMBER(7,2), Department_Num NUMBER(2) NOT NULL );

Result of execution

Analysis of results, what in these data can be seen: The second step was to create the second table which were interconnected with the initial one for creating such kind of relations.

3. Query goal (CREATE a table Department_table): Query SQL code

CREATE TABLE Department_Table ( Dept_Num NUMBER(2) NOT NULL, Dept_Name VARCHAR2(14), Loc VARCHAR2(13), Mgr_no NUMBER, Dept_Type NUMBER );

Result of execution

Analysis of results, what in these data can be seen: The final element of my work is to show the Department table which was the third and final part of my database puzzle.

4. Query goal (CREATE a VIEW manager_info): Query SQL code

CREATE OR REPLACE VIEW manager_infoAS SELECT e.E_NAME, e.E_NUM, d.DEPT_TYPE, d.DEPT_NUM, p.P_LEVEL, p.P_NUM FROM EMPLOYEE_TABLE e, DEPARTMENT_TABLE d, PROJECT_TABLE p WHERE e.E_NUM = d.MGR_NO AND d.DEPT_NUM = p.RESP_DEPT;

Result of execution

Analysis of results, what in these data can be seen: The creation of the view provided the wide spread possibility interconnectional relationship between tables which include the basic parts of my database.

5. Query goal (Create a INSTEAD OF type trigger to view): Query PL/SQL code

CREATE OR REPLACE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n FOR EACH ROW DECLARE rowcnt NUMBER;BEGIN SELECT COUNT(*) INTO rowcnt FROM EMPLOYEE_TABLE WHERE E_NUM = :n.E_NUM; IF rowcnt = 0 THEN INSERT INTO EMPLOYEE_TABLE (E_NUM,E_NAME ) VALUES (:n.E_NUM, :n.E_NAME ); ELSE UPDATE EMPLOYEE_TABLE SET EMPLOYEE_TABLE.E_NAME = :n.E_NAME WHERE EMPLOYEE_TABLE.E_NUM = :n.E_NUM; END IF; SELECT COUNT(*) INTO rowcnt FROM DEPARTMENT_TABLE WHERE DEPT_NUM = :n.DEPT_NUM; IF rowcnt = 0 THEN INSERT INTO DEPARTMENT_TABLE (DEPT_NUM, DEPT_TYPE ) VALUES (:n.DEPT_NUM, :n.DEPT_TYPE );

ELSE UPDATE DEPARTMENT_TABLE SET DEPARTMENT_TABLE.DEPT_TYPE = :n.DEPT_TYPE WHERE DEPARTMENT_TABLE.DEPT_NUM = :n.DEPT_NUM; END IF; SELECT COUNT(*) INTO rowcnt FROM PROJECT_TABLE WHERE PROJECT_TABLE.P_NUM = :n.P_NUM; IF rowcnt = 0 THEN INSERT INTO ROJECT_TABLE (P_NUM, P_LEVEL ) VALUES (:n.P_NUM, :n.P_LEVEL ); ELSE UPDATE PROJECT_TABLE SET PROJECT_TABLE.P_LEVEL = :n.P_LEVEL WHERE PROJECT_TABLE.P_NUM = :n.P_NUM; END IF;END;

Result of execution

Analysis of results, what in these data can be seen: In this case I made INSTEAD OF TYPE trigger with 3 tables which I had during all project (EMPLOYEE, DEPARTMENT and PROJECT tables). As can be seen from the screenshots it compilation was successfully done.

6. Query goal (Use of implicit defined cursor (SELECT ... INTO) for data extraction from database table to PL/SQL indexed table (associative array). Data output with package DBMS_OUTPUT function PUT_LINE() (use of server text buffer): Query PL/SQL code

DECLARE

MONTHLY_SALARY NUMBER(6); WORKING_DAYS NUMBER(2); PAY_PER_DAY NUMBER(6,2);BEGIN MONTHLY_SALARY:=3000; WORKING_DAYS :=15; PAY_PER_DAY :=MONTHLY_SALARY/WORKING_DAYS; DBMS_OUTPUT.PUT_LINE('THE WAGE PER DAY IS '||TO_CHAR(PAY_PER_DAY));EXCEPTIONWHEN ZERO_DIVIDE THEN PAY_PER_DAY:=0;END;

Result of execution

Analysis of results, what in these data can be seen: According to the requirements we can see that the daily earnings of the employee could be calculated by this small PL/SQL code. For instance in my case this block was successfully finished and 3000/15 = 200 is a value of daily wage of the employee.

7. Query goal (Use of explicit defined cursor (CURSOR ... IS) for data extraction from database table to PL/SQL table with collection. Data output.Query PL/SQL code

DECLARE E_NUM EMPLOYEE_TABLE.ID%TYPE; E_NAME EMPLOYEE_TABLE.NAME%TYPE; E_JOB EMPLOYEE_TABLE.JOB%TYPE; CURSOR E_EMPLOYEE_TABLE IS SELECT ID,NAME,JOB FROM EMPLOYEE_TABLE;BEGIN OPEN E_EMPLOYEE_TABLE; LOOP FETCH E_EMPLOYEE_TABLE INTO E_NUM, E_NAME, E_JOB;

EXIT WHEN E_EMPLOYEE_TABLE%NOTFOUND; DBMS_OUTPUT.PUT_LINE(E_NUM||''||E_NAME||''||E_JOB); END LOOP; CLOSE E_EMPLOYEE_TABLE;END;

Result of execution

Analysis of results, what in these data can be seen: By this PL/SQL code we can show the usage of explicit defined cursor (CURSOR ... IS).

8. Query goal (Use of BULK_COLLECT construction for data extraction from database table to PL/SQL array. Data output).Query PL/SQL code

DECLARETYPE NumTabIS TABLE OF employee_table.e_num%TYPE;TYPE NameTabIS TABLE OF employee_table.e_name%TYPE; e_num NumTab; e_name NameTab;PROCEDURE print_resultsISBEGIN IF e_num.COUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('No results!'); ELSE DBMS_OUTPUT.PUT_LINE('Results:'); FOR i IN enums.FIRST .. e_num.LAST LOOP

DBMS_OUTPUT.PUT_LINE(' Employee #' || e_num(i) || ': ' || e_name(i)); END LOOP; END IF;END;BEGIN SELECT e_num, e_name BULK COLLECT INTO e_num, e_name FROM employee_table WHERE e_num > 0; print_results(); SELECT e_num, e_name BULK COLLECT INTO e_num, e_name FROM employee_table SAMPLE (20); print_results();END;

Result of execution

Analysis of results, what in these data can be seen: The collections are initialized automatically. Tables with collection and associative arrays are extended to hold as many elements as needed. If you use varrays, all the return values must fit in the varray's declared size. Elements are inserted starting at index 1, overwriting any existing elements. Because the processing of the BULK COLLECT INTO clause is similar to a FETCH loop, it does not raise a NO_DATA_FOUND exception if no rows match the query. You must check whether the resulting tables with collection or varray is null, or if the resulting associative array has no elements.

9. Query goal (Create a PL/SQL function, which use cursor variable for data extraction. The returned value is aggregate (SUM(), COUNT(), ...) of extracted rows column values).Query PL/SQL code

DECLARE V.SUM_SALARY NUMBER(10,2); V.DEPT_NUM NUMBER NOT NULL:=100;BEGIN SELECT SUM(SALARY) INTO V.SUM_SALARY FROM EMPLOYEE_TABLE WHERE DEPT_NUM=V.DEPT_NUM; DBMS_OUTPUT.PUT_LINE('SUM IS'||V.SUM_SALARY);END;

Result of execution

Analysis of results, what in these data can be seen: Associative arrays help you represent data sets of arbitrary size, with fast lookup for an individual element without knowing its position within the array and without having to loop through all the array elements. It is like a simple version of a SQL table where you can retrieve values based on the primary key. For simple temporary storage of lookup data, associative arrays let you avoid using the disk space and network operations required for SQL tables.

10. Query goal (Give the examples for dynamic SQL and PL/SQL use.Query PL/SQL code

CREATE TABLE employees_temp ASSELECT * FROM employees;CREATE OR REPLACE PROCEDURE delete_rows( table_name IN VARCHAR2, condition IN VARCHAR2 DEFAULT NULL)AS where_clause VARCHAR2(100) := ' WHERE ' || condition; v_table VARCHAR2(30);BEGIN SELECT OBJECT_NAME INTO v_table

FROM USER_OBJECTS WHERE OBJECT_NAME = UPPER(table_name) AND OBJECT_TYPE = 'TABLE'; IF condition IS NULL THEN where_clause := NULL; END IF; EXECUTE IMMEDIATE 'DELETE FROM ' || v_table || where_clause;EXCEPTIONWHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('Invalid table: ' || table_name);END;

BEGIN delete_rows('employees_temp', 'employee_id = 111');END;

Result of execution

Analysis of results, what in these data can be seen: As a rule, dynamic SQL does not support PL/SQL-specific types. For example, define variables and bind arguments cannot be Booleans or associative arrays. The only exception is that a PL/SQL record can appear in the INTO clause.You can execute a dynamic SQL statement repeatedly using new values for the bind arguments. However, you incur some overhead because EXECUTE IMMEDIATE re-prepares the dynamic string before every execution.

4 Conclusion

During the learning of Database server programming fundamentals I found many advantages which are supported with my programming skills and logic construction. Otherwise, there are some disadvantages which disappointed me in this session.Unfortunately, my SQL skills are not so strong, that is why the query in this work could be look like the bad variation of coding. However, I tried to reorganize the basic principle by given requirements and added some new extra actions from me. This kind of large step will help to easy use the PL/SQL beginning point.