Introduction-to-SQL/lab6procedures.sql at main · ssagata/Introduction-to-SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
DELIMITER //
CREATE OR REPLACE PROCEDURE procDeleteGrades (studentDni CHAR(9))
    BEGIN
        DECLARE id INT;
        SET id = (SELECT studentId FROM Students WHERE dni = studentDni);
        DELETE FROM Grades WHERE studentId = id;
    END //
DELIMITER ;

CALL procDeleteGrades('12345678A');

DELIMITER //
CREATE OR REPLACE PROCEDURE procDeleteData()
    BEGIN
        DELETE FROM Grades;
        DELETE FROM GroupsStudents;
        DELETE FROM Students;
        DELETE FROM Groups;
        DELETE FROM Subjects;
        DELETE FROM Degrees;
    END //
DELIMITER ;


DELIMITER //
CREATE OR REPLACE PROCEDURE procTableCreation()
    BEGIN
        CREATE TABLE tbl (
            TableTestId  INT default NULL,
            TableName VARCHAR(30) default NULL);
    end //
DELIMITER ;

CALL procTableCreation() ;


-- populatee thee table  - wypełnianie tabeli

DELIMITER //
CREATE OR REPLACE PROCEDURE procPopulateTable()
    BEGIN
        DECLARE TableTestId ;

        INSERT INTO tbl (TableTestId, TableName)
        VALUES TableTestId = 1, TableName =  'testname';
    end //
DELIMITER ;