-- Enable server output
SET SERVEROUTPUT ON;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE groupe (
id_grp CHAR(10) PRIMARY KEY,
des_grp CHAR(35),
annee_grp INT
)';
EXECUTE IMMEDIATE 'CREATE TABLE etudiant (
cne CHAR(10) PRIMARY KEY,
nom CHAR(30),
prenom CHAR(30),
id_groupe CHAR(10),
CONSTRAINT Fk1 FOREIGN KEY (id_groupe) REFERENCES groupe(id_grp)
)';
EXECUTE IMMEDIATE 'CREATE TABLE module (
id_mod CHAR(10) PRIMARY KEY,
des_mod CHAR(50)
)';
EXECUTE IMMEDIATE 'CREATE TABLE notes (
cne_etd CHAR(10),
id_module CHAR(10),
semestre CHAR(5),
not_etd INT,
CONSTRAINT PK2 PRIMARY KEY (cne_etd, id_module, semestre),
CONSTRAINT Fk2 FOREIGN KEY (cne_etd) REFERENCES etudiant(cne),
CONSTRAINT Fk3 FOREIGN KEY (id_module) REFERENCES module(id_mod)
)';
END;
/
-- Insert data into groupe
INSERT INTO groupe VALUES ('BCG1', 'BCG', 2008);
INSERT INTO groupe VALUES ('BCG2', 'BCG', 2008);
INSERT INTO groupe VALUES ('CI1', 'Licence génie informatique', 2009);
INSERT INTO groupe VALUES ('LST1', 'Licence Math info', 2008);
INSERT INTO groupe VALUES ('LST2', 'BCG', 2009);
INSERT INTO groupe VALUES ('MIPC1', 'MIPC', 2009);
INSERT INTO groupe VALUES ('MIPC2', 'MIPC', 2009);
INSERT INTO groupe VALUES ('MIPC3', 'MIPC', 2009);
INSERT INTO groupe VALUES ('MIPC4', 'MIPC', 2009);
-- Insert data into module
INSERT INTO module (id_mod, des_mod) VALUES ('C145', 'chimie 1');
INSERT INTO module (id_mod, des_mod) VALUES ('C146', 'chimie 2');
INSERT INTO module (id_mod, des_mod) VALUES ('I45', 'Bases de données');
INSERT INTO module (id_mod, des_mod) VALUES ('I46', 'Réseaux');
INSERT INTO module (id_mod, des_mod) VALUES ('I47', 'Programmation');
INSERT INTO module (id_mod, des_mod) VALUES ('M121', 'Analyse 1');
INSERT INTO module (id_mod, des_mod) VALUES ('M122', 'Analyse 2');
INSERT INTO module (id_mod, des_mod) VALUES ('M123', 'Analyse 3');
INSERT INTO module (id_mod, des_mod) VALUES ('M124', 'Algèbre 1');
INSERT INTO module (id_mod, des_mod) VALUES ('M125', 'Algèbre 2');
INSERT INTO module (id_mod, des_mod) VALUES ('P234', 'Mécanique 1');
INSERT INTO module (id_mod, des_mod) VALUES ('P235', 'Mécanique 2');
INSERT INTO module (id_mod, des_mod) VALUES ('P236', 'Mécanique 3');
INSERT INTO module (id_mod, des_mod) VALUES ('P237', 'Electricité 1');
-- Insert data into etudiant
INSERT INTO etudiant VALUES ('1234567', 'Alaoui', 'mounir', 'CI1');
INSERT INTO etudiant VALUES ('1234568', 'Kraimi', 'siham', 'CI1');
INSERT INTO etudiant VALUES ('1234569', 'Serroukh', 'anas', 'CI1');
INSERT INTO etudiant VALUES ('1234570', 'Elmrabet', 'lamyae', 'CI1');
INSERT INTO etudiant VALUES ('1234571', 'Ibahimi', 'mohamed', 'CI1');
INSERT INTO etudiant VALUES ('1234572', 'Raissouni', 'ilham', 'CI1');
INSERT INTO etudiant VALUES ('1234588', 'Benaissa', 'abderrahim', 'LST2');
INSERT INTO etudiant VALUES ('1234589', 'ben', 'abdo', 'MIPC2');
-- Create function
CREATE OR REPLACE FUNCTION insert_note (
p_cne_etd CHAR,
p_id_module CHAR,
p_semestre CHAR,
p_not_etd INT
) RETURN VARCHAR2 AS
BEGIN
IF p_not_etd < 0 THEN
RETURN 'Erreur: La note doit être supérieure ou égale à 0.';
ELSE
INSERT INTO notes (cne_etd, id_module, semestre, not_etd)
VALUES (p_cne_etd, p_id_module, p_semestre, p_not_etd);
RETURN 'Insertion réussie';
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN 'Erreur: ' || SQLERRM;
END;
/
-- Use the function
BEGIN
DBMS_OUTPUT.PUT_LINE(insert_note('1234567', 'I45', 'S3', 14)); -- Insertion réussie
DBMS_OUTPUT.PUT_LINE(insert_note('1234567', 'I45', 'S3', -5)); -- Erreur: La note doit être supérieure ou égale à 0.
END;
/
Write, Run & Share PL/SQL code online using OneCompiler's Oracle PL/SQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for Oracle PL/SQL running on latest version 23c (23.3.0.0). Getting started with the OneCompiler's Oracle PL/SQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'PL/SQL' and start writing code to learn and test online without worrying about tedious process of installation.
PL/SQL is procedural extension for SQL created by Oracle. It is by default embedded into the Oracle Database. PL/SQL program units are compiled and stored inside the Oracle Database which results in optimal execution times as the PL/SQL and SQL run within the same server process.
Following is the syntax structure for the PL/SQL code blocks
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
DECLARE
message varchar2(100):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/
CREATE OR REPLACE FUNCTION
hello_user
(user_name IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN 'Hello ' || user_name;
END hello_user;
/
BEGIN
dbms_output.put_line(hello_user('Peter'));
END;
/
BEGIN
DBMS_OUTPUT.put_line (1/0);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('error is: ' || SQLERRM);
END;