BEGIN
-- Création de la table Client
EXECUTE IMMEDIATE '
CREATE TABLE Client (
code_cli VARCHAR2(5) PRIMARY KEY,
nom VARCHAR2(50),
prenom VARCHAR2(50),
ville VARCHAR2(50),
age NUMBER(3)
)';
-- Insertion des données dans la table Client
EXECUTE IMMEDIATE 'INSERT INTO Client (code_cli, nom, prenom, ville, age) VALUES (''C11'', ''ABBADI'', ''Mohamed'', ''Tanger'', 20)';
EXECUTE IMMEDIATE 'INSERT INTO Client (code_cli, nom, prenom, ville, age) VALUES (''C12'', ''BARGHDADI'', ''Salima'', ''Tetouan'', 19)';
EXECUTE IMMEDIATE 'INSERT INTO Client (code_cli, nom, prenom, ville, age) VALUES (''C13'', ''ELALAQUI'', ''Samir'', ''Tanger'', 21)';
EXECUTE IMMEDIATE 'INSERT INTO Client (code_cli, nom, prenom, ville, age) VALUES (''C14'', ''KRAIMI'', ''Mounir'', ''Tanger'', 25)';
EXECUTE IMMEDIATE 'INSERT INTO Client (code_cli, nom, prenom, ville, age) VALUES (''C15'', ''ELBOUDALI'', ''Latifa'', ''Casa'', 35)';
EXECUTE IMMEDIATE 'INSERT INTO Client (code_cli, nom, prenom, ville, age) VALUES (''C16'', ''BAKKALI'', ''Hajar'', ''Marrakech'', 24)';
-- Création de la table Commande
EXECUTE IMMEDIATE '
CREATE TABLE Commande (
num_cmd NUMBER PRIMARY KEY,
date_cmd DATE,
code_cli VARCHAR2(5),
FOREIGN KEY (code_cli) REFERENCES Client (code_cli)
)';
-- Insertion des données dans la table Commande
EXECUTE IMMEDIATE 'INSERT INTO Commande (num_cmd, date_cmd, code_cli) VALUES (1, TO_DATE(''20-05-2010'', ''DD-MM-YYYY''), ''C11'')';
EXECUTE IMMEDIATE 'INSERT INTO Commande (num_cmd, date_cmd, code_cli) VALUES (2, TO_DATE(''20-05-2010'', ''DD-MM-YYYY''), ''C12'')';
EXECUTE IMMEDIATE 'INSERT INTO Commande (num_cmd, date_cmd, code_cli) VALUES (3, TO_DATE(''01-05-2010'', ''DD-MM-YYYY''), ''C11'')';
EXECUTE IMMEDIATE 'INSERT INTO Commande (num_cmd, date_cmd, code_cli) VALUES (4, TO_DATE(''28-05-2010'', ''DD-MM-YYYY''), ''C11'')';
EXECUTE IMMEDIATE 'INSERT INTO Commande (num_cmd, date_cmd, code_cli) VALUES (5, TO_DATE(''28-05-2010'', ''DD-MM-YYYY''), ''C13'')';
EXECUTE IMMEDIATE 'INSERT INTO Commande (num_cmd, date_cmd, code_cli) VALUES (6, TO_DATE(''28-05-2010'', ''DD-MM-YYYY''), ''C13'')';
EXECUTE IMMEDIATE 'INSERT INTO Commande (num_cmd, date_cmd, code_cli) VALUES (7, TO_DATE(''08-05-2010'', ''DD-MM-YYYY''), ''C13'')';
EXECUTE IMMEDIATE 'INSERT INTO Commande (num_cmd, date_cmd, code_cli) VALUES (8, TO_DATE(''21-05-2010'', ''DD-MM-YYYY''), ''C13'')';
EXECUTE IMMEDIATE 'INSERT INTO Commande (num_cmd, date_cmd, code_cli) VALUES (9, TO_DATE(''18-05-2010'', ''DD-MM-YYYY''), ''C11'')';
EXECUTE IMMEDIATE 'INSERT INTO Commande (num_cmd, date_cmd, code_cli) VALUES (10, TO_DATE(''01-06-2010'', ''DD-MM-YYYY''), ''C14'')';
EXECUTE IMMEDIATE 'INSERT INTO Commande (num_cmd, date_cmd, code_cli) VALUES (11, TO_DATE(''15-05-2010'', ''DD-MM-YYYY''), ''C12'')';
EXECUTE IMMEDIATE 'INSERT INTO Commande (num_cmd, date_cmd, code_cli) VALUES (12, TO_DATE(''22-05-2010'', ''DD-MM-YYYY''), ''C14'')';
EXECUTE IMMEDIATE 'INSERT INTO Commande (num_cmd, date_cmd, code_cli) VALUES (13, TO_DATE(''20-05-2010'', ''DD-MM-YYYY''), ''C14'')';
-- Création de la table Produit
EXECUTE IMMEDIATE '
CREATE TABLE Produit (
code_art NUMBER PRIMARY KEY,
designation VARCHAR2(50),
qte_st NUMBER,
pu NUMBER
)';
-- Insertion des données dans la table Produit
EXECUTE IMMEDIATE 'INSERT INTO Produit (code_art, designation, qte_st, pu) VALUES (1, ''PC'', 12, 123)';
EXECUTE IMMEDIATE 'INSERT INTO Produit (code_art, designation, qte_st, pu) VALUES (2, ''Imprimante'', 23, 4578)';
EXECUTE IMMEDIATE 'INSERT INTO Produit (code_art, designation, qte_st, pu) VALUES (3, ''Souris'', 243, 15)';
EXECUTE IMMEDIATE 'INSERT INTO Produit (code_art, designation, qte_st, pu) VALUES (4, ''Clavier'', 50, 60)';
EXECUTE IMMEDIATE 'INSERT INTO Produit (code_art, designation, qte_st, pu) VALUES (5, ''Scanner'', 34, 500)';
EXECUTE IMMEDIATE 'INSERT INTO Produit (code_art, designation, qte_st, pu) VALUES (6, ''Bureau'', 34, 1500)';
EXECUTE IMMEDIATE 'INSERT INTO Produit (code_art, designation, qte_st, pu) VALUES (7, ''Projecteur'', 10, 10000)';
-- Création de la table detail_cmmande
EXECUTE IMMEDIATE '
CREATE TABLE detail_cmmande (
num NUMBER,
ref NUMBER,
qte NUMBER,
PRIMARY KEY (num, ref),
FOREIGN KEY (num) REFERENCES Commande (num_cmd),
FOREIGN KEY (ref) REFERENCES Produit (code_art)
)';
-- Insertion des données dans la table detail_cmmande
EXECUTE IMMEDIATE 'INSERT INTO detail_cmmande (num, ref, qte) VALUES (1, 1, 10)';
EXECUTE IMMEDIATE 'INSERT INTO detail_cmmande (num, ref, qte) VALUES (2, 1, 12)';
EXECUTE IMMEDIATE 'INSERT INTO detail_cmmande (num, ref, qte) VALUES (3, 1, 17)';
EXECUTE IMMEDIATE 'INSERT INTO detail_cmmande (num, ref, qte) VALUES (4, 2, 2)';
EXECUTE IMMEDIATE 'INSERT INTO detail_cmmande (num, ref, qte) VALUES (2, 2, 1)';
EXECUTE IMMEDIATE 'INSERT INTO detail_cmmande (num, ref, qte) VALUES (1, 4, 1)';
EXECUTE IMMEDIATE 'INSERT INTO detail_cmmande (num, ref, qte) VALUES (2, 3, 1)';
EXECUTE IMMEDIATE 'INSERT INTO detail_cmmande (num, ref, qte) VALUES (1, 3, 1)';
EXECUTE IMMEDIATE 'INSERT INTO detail_cmmande (num, ref, qte) VALUES (3, 2, 4)';
EXECUTE IMMEDIATE 'INSERT INTO detail_cmmande (num, ref, qte) VALUES (3, 4, 2)';
EXECUTE IMMEDIATE 'INSERT INTO detail_cmmande (num, ref, qte) VALUES (5, 5, 12)';
EXECUTE IMMEDIATE 'INSERT INTO detail_cmmande (num, ref, qte) VALUES (1, 6, 2)';
COMMIT;
END;
/
---------------------------------------------------------------------------------------
SET SERVEROUTPUT ON;
-- create or replace function ajout_modif(
-- v_code_cli in Client.code_cli%TYPE,
-- v_nom in Client.nom%TYPE,
-- v_prenom in Client.prenom%TYPE,
-- v_ville in Client.ville%TYPE,
-- v_age in Client.age%TYPE
-- )return VARCHAR2
-- is
-- CURSOR CODE is
-- SELECT code_cli from Client where code_cli=v_code_cli;
-- BEGIN
-- open CODE;
-- FETCH cur_client INTO rec_client;
-- if CODE%FOUND then
-- UPDATE Client SET cli.nom=v_nom,cli.prenom=v_prenom,cli.ville=v_ville,cli.age=v_age
-- WHERE code_cli = v_code_cli;
-- close CODE;
-- return 'UPDATE AVEC SUCCES';
-- ELSE
-- INSERT INTO Client(code_cli, nom, prenom, ville, age)
-- VALUES (v_code_cli,v_nom,v_prenom,v_ville,v_age);
-- close CODE;
-- return 'INSERTION AVEC SUCCES';
-- END IF;
-- END;
-- /
---------------------------------------------------------------------------------------
create or replace procedure collect_name(
p_code in Client.code_cli%TYPE
) IS
rec_nom Client.nom%TYPE;
BEGIN
SELECT nom into rec_nom from Client
WHERE code_cli=p_code;
dbms_output.put_line(rec_nom);
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;