Variations violines

SQL for Oracle : insert data

Syntaxe
insert into [nameSchema.] {nameTable | nameView | requestSelect}
    [(nameColumn1, nameColumn2, ...)]
    values (value1, value2, ...)

Exemples 

Let us take the "employees" table consisted of the following columns:
id_Employee                number
lastName_Employee    varchar2(30)
firstName_Employee    varchar2(30)

Inform all the columns
insert into employees
    values (1, 'SMITH', 'John');

Inform certain columns
insert into employees
    (id_Employee,  lastName_Employee)
    values (2, 'DOE');

SQL pour Oracle : l'insertion de données.

Syntaxe
insert into [nomSchéma.] {nomTable | nomVue | requêteSelect}
    [(nomColonne1, nomColonne2, ...)]
    values (valeur1, valeur2, ...)

Exemples 

Prenons la table salarie composée des colonnes suivantes :
id_Salarie              number
nom_Salarie          varchar2(30)
prenom_Salarie     varchar2(30)

Renseigner toutes les colonnes
insert into salarie
    values (1, 'SMITH', 'John');

Renseigner certaines colonnes
insert into salarie
    (id_Salarie, nom_Salarie)
    values (2, 'DOE');

Miniatures gourmandes

PL SQL : Cursor and imbricated table

The aim of this code is to get some values, including the name of table from all_tables of an Oracle Database,  and using this table_name in an instruction select ... from table_name.
First, I thought at 2 imbricated cursors but it is impossible to declare the second cursor :

cursor cur1 is select table_name, column_name from all_tables where conditions;

v_cur1 cur1%rowtype;

cursor cur2 is select * from v_cur1.table_name;

return a compilation error :  v_cur1.table_name : non-existing table or view.

There is one possible solution  :

declare

    cursor cur_tables is
    select table_name
    from all_tables;

    vr_cur_tables       cur_tables%rowtype;
    req_index           varchar2(400);
    nb_key_index        number;

    --create a record type object
    type keyIndex is record (index_name varchar2(20), index_value varchar2(20));
    --create a table type object composed of an index and a column of keyIndex, record type object
    type tKeyIndex is table of keyindex;

    tableKeyIndex tKeyIndex;

    i               number;
    req_nb_key varchar2(400);
     
begin
    for vr_cur_tables in cur_tables loop
        dbms_output.put_line(vr_cur_tables.table_name);
        -- select the data of the current table
        req_index := 'select * from ' || vr_cur_tables.table_name;
        -- set the data into a table
        execute immediate req_index bulk collect into tableKeyIndex;
        req_nb_key :='select count(*) from ' || vr_cur_tables.table_name;
        execute immediate req_nb_key into nb_key_index;
        --For each tupple of the select, display the column nomindex value of the current table
        for i in 1..nb_key_index loop
            dbms_output.put_line(tableKeyIndex(i).index_name);
        end loop;
    end loop;
end;

Boucles d'oreilles feu et mauve

Perles svarowski que j'ai montées sur une armature argentée

Architecture et bonnes pratiques de codage

Pour ceux qui ne serait pas encore convaincus de l'intérêt des bonnes pratiques en programmation et d'une architecture solide pour une application  :

Erreurs de programmation : Java coûte plus cher que Cobol