Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -------------- Task 1 --------------------------
- alter table student add tallness int;
- alter table student modify email varchar2(40) null;
- -------------- Task 2 --------------------------
- -------------- Task 3 --------------------------
- Nejrozumnější je vložit druhý záznam se stejným primárním
- klíčem a dát ROLLBACK při SET AUTOCOMMIT ON/OFF
- -------------- Task 4 --------------------------
- create or replace
- PROCEDURE AddStudent(
- p_login IN VARCHAR2,
- p_fname IN VARCHAR2,
- p_lname IN VARCHAR2,
- p_tallness IN NUMBER) AS
- BEGIN
- insert into student (login, fname, lname, tallness) values (p_login, p_fname, p_lname, p_tallness);
- END AddStudent;
- execute AddStudent('nov12', 'Jarda', 'Novak', 170);
- execute AddStudent('nov90', 'Lada', 'Novakova', 173);
- execute AddStudent('hla10', 'Uhor', 'Hladky', 185);
- select * from student;
- ------------
- create or replace
- FUNCTION FAddStudent(
- p_login IN VARCHAR2,
- p_fname IN VARCHAR2,
- p_lname IN VARCHAR2,
- p_tallness IN NUMBER)
- RETURN VARCHAR2 AS
- BEGIN
- insert into student (login, fname, lname, tallness) values (p_login, p_fname, p_lname, p_tallness);
- return 'ok';
- exception
- when others then
- return 'error';
- END FAddStudent;
- set serveroutput on;
- execute dbms_output.put_line(FAddStudent('bon007', 'James', 'Bond', 190));
- execute dbms_output.put_line(FAddStudent('bac27', 'Radim', 'Baca', 175));
- -------------- Task 5 --------------------------
- create or replace
- PROCEDURE StudentBecomeTeacher( p_login IN student.login%TYPE, p_department IN teacher.department%TYPE)
- AS
- v_fname student.fname%TYPE;
- v_lname student.lname%TYPE;
- BEGIN
- SELECT fname, lname INTO v_fname, v_lname FROM student WHERE login = p_login;
- DELETE FROM student WHERE login = p_login;
- INSERT INTO teacher VALUES (p_login, v_fname, v_lname, p_department, NULL);
- commit;
- EXCEPTION
- WHEN OTHERS THEN
- rollback;
- END StudentBecomeTeacher;
- execute StudentBecomeTeacher('sob28', 100);
- ------------
- create or replace
- PROCEDURE AddStudent2(
- p_fname IN VARCHAR2,
- p_lname IN VARCHAR2,
- p_tallness IN NUMBER) AS
- v_login VARCHAR2(5);
- BEGIN
- v_login := substr(p_lname, 0, 3) || '00';
- insert into student (login, fname, lname, tallness) values (v_login, p_fname, p_lname, p_tallness);
- END AddStudent2;
- execute AddStudent2('Bill','Gates', 166);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement