Com s'utilitza la declaració d'inserció, actualització, eliminació i selecció de PL SQL
how use pl sql insert
Aquest tutorial explica com utilitzar ordres PL SQL INSERT, UPDATE, DELETE i SELECT amb exemples de programació:
En aquest article, continuarem amb Sèrie PL / SQL . A la PL / SQL DataTypes, constants i variables tutorial, hem conegut detalladament els tipus de dades, variables, constants i literals de PL SQL amb l'ajut d'exemples de programació. Aquí analitzarem les sentències PL SQL DML i el seu funcionament.
Explorarem les ordres PL SQL INSERT, DELETE, UPDATE i SELECT per manipular les dades de les taules PL / SQL.
Comencem a entendre la implementació d’ordres INSERT, DELETE, UPDATE i SELECT a les taules. Col·lectivament s’anomenen sentències DML (Data Manipulation Language).
Ordre PL SQL INSERT
La instrucció INSERT INTO s'utilitza per inserir dades en una taula. Es diu a Llenguatge de manipulació de dades . Utilitza el nom de la taula i els noms i els valors de les columnes com a entrades i realitza la inserció de valors a la taula.
Sintaxi:
INSERT INTO <
> VALUES (<>, <>, …..);
El nom i els valors de la taula que cal inserir són la part obligatòria de la sentència PL SQL INSERT. Opcionalment, també podem esmentar els noms de columna on s’han d’inserir els valors.
Hem creat la taula EMPLOYEE amb l'ajut de la sentència SQL que es mostra a continuació:
CREATE TABLE EMPLOYEE ( CODE INT NOT NULL, NAME VARCHAR (15) NOT NULL, AGE INT NOT NULL, PRIMARY KEY (CODE) );
Implementació de codi amb PL SQL INSERT:
BEGIN INSERT INTO EMPLOYEE VALUES (1, 'FLEX', 34); INSERT INTO EMPLOYEE VALUES (2, 'HARRY', 35); END
La sortida del codi anterior hauria de ser:
SELECT * from EMPLOYEE;
INSERTAR A SELECCIONAR
INSERTAR A TOTES LES COLUMNES
INSERT INTO SELECT s'utilitza per inserir files en una taula actuant sobre el resultat de la sentència SELECT. Pot ser necessari obtenir una fila de la taula i inserir-la en una taula diferent.
Sintaxi:
INSERT INTO <> (col1, col2, col3) SELECT col1, col2, col3 FROM <> WHERE condition;
L'ordre INSERT INTO SELECT necessita que els tipus de dades de la taula d'origen i de destinació siguin els mateixos. En cas que necessitem copiar totes les files de la taula font a la taula de destinació, podem ometre la condició WHERE.
Hem creat una altra taula anomenada MANAGER amb l'ajut de la sentència SQL que es mostra a continuació:
CREATE TABLE MANAGER ( CODE INT NOT NULL, NAME VARCHAR (15) NOT NULL, AGE INT NOT NULL, PRIMARY KEY (CODE) );
Considerem de nou la taula EMPLOYEE.
Implementació de codi amb INSERT INTO SELECT .:
BEGIN INSERT INTO MANAGER (CODE, NAME, AGE) SELECT CODE, NAME, AGE FROM EMPLOYEE WHERE CODE = 1; END;
Sortida del codi anterior:
SELECT * from MANAGER;
INSERTAR DADES PARCIALS
També podem inserir dades parcials en una altra taula. Primer creem el fitxer DETALL DEL DIRECTIU de la taula EMPLOYEE amb la consulta següent.
CREATE TABLE MANAGERDETAIL AS SELECT * FROM EMPLOYEE WHERE 1 = 0;
Aquí, ON 1 = 0 fa referència a la primera columna de la taula EMPLOYEE que és CODE.
A continuació, utilitzarem la sentència INSERT INTO SELECT per duplicar les dades de la taula EMPLOYEE a la taula MANAGERDETAIL.
Implementació de codi amb INSERT INTO SELECT;
BEGIN INSERT INTO MANAGERDETAIL SELECT CODE, NAME, AGE FROM EMPLOYEE WHERE CODE! = 0 END;
Sortida del codi anterior:
SELECT * from MANAGERDETAIL;
A l'exemple anterior, no hem esmentat els noms de columna a l'ordre INSERT, ja que la sortida de l'ordre SELECT té els valors que coincideixen amb les columnes de la taula MANAGERDETAIL. A més, hem inclòs una condició amb la clàusula WHERE.
INSERTAR DADES PARCIALS I LITERALS
Podem inserir dades parcials i valor literal a una taula.
Primer creem la taula MANAGERCONTACT amb la següent consulta:
CREATE TABLE MANAGERCONTACT ( CODE INT NOT NULL, NAME VARCHAR (15) NOT NULL, AGE INT NOT NULL, PRIMARY KEY (CODE) );
Ara, tornem a agafar l’ajuda de la taula EMPLOYEE i intentem duplicar les dades de la taula EMPLOYEE a la taula MANAGERCONTACT.
Implementació del codi amb INSERT INTO:
BEGIN INSERT INTO MANAGERCONTACT (CODE, NAME, AGE) SELECT CODE, NAME, 0 FROM EMPLOYEE; END;
La sortida del codi anterior:
SELECT * from MANAGERCONTACT;
A l'exemple anterior, hem obtingut els valors de la taula EMPLOYEE i també hem inclòs 0 literal a la sentència INSERT que ha modificat els valors de la columna AGE a 0 a la taula MANAGERCONTACT.
INSERTAR TOT
Una instrucció INSERT ALL s'utilitza per afegir més d'una fila a una sola taula o diverses taules. També s’anomena ordre de diverses taules i és de dos tipus condicional i incondicional.
# 1) INSERCIÓ INCONDICIONAL DE TOTS
Per inserir més d'una fila en una taula, s'utilitza una sentència INSERT ALL.
Sintaxi:
INSERT ALL INTO <
> (c1, c2, c3) VALUES (v1, v2, v3) INTO <
> (c1, c2, c3) VALUES (v4, v5, v6) INTO <
> (c1, c2, c3) VALUES (v7, v8, v9)
Aquí, c1, c2 i c3 són els noms de les columnes i v1, v2 i v3 són els valors a inserir. En el cas, hem d’utilitzar el valor literal en lloc de la subquesta; hem d’utilitzar la següent consulta:
SELECT * FROM dual;
Hem creat la taula SOCCERPLAYERS amb l'ajut de la sentència SQL que es mostra a continuació:
CREATE TABLE SOCCERPLAYERS ( PLAYERID INT NOT NULL, NAME VARCHAR (15) NOT NULL, COUNTRY VARCHAR (15), PRIMARY KEY (PLAYERID) );
Implementació de codi amb INSERT ALL:
BEGIN INSERT ALL INTO SOCCERPLAYERS (PLAYERID, NAME, COUNTRY) VALUES (4, 'DANIEL', 'USA') INTO SOCCERPLAYERS (PLAYERID, NAME, COUNTRY) VALUES (8, 'DANNY', 'ITALY') INTO SOCCERPLAYERS (PLAYERID, NAME, COUNTRY) VALUES (7, 'FRANK', 'FRANCE') SELECT 1 FROM dual; END;
La sortida del codi anterior:
SELECT * from SOCCERPLAYERS;
A l'exemple anterior, s'han inserit tres files alhora en una taula amb l'ordre INSERT ALL.
Per inserir més d'una fila en diverses taules, s'utilitza una sentència INSERT ALL.
Sintaxi:
INSERT ALL INTO <> (c1, c2, c3) VALUES (v1, v2, v3) INTO <> (c1, c2, c3) VALUES (v4, v5, v6) INTO <> (c1, c2, c3) VALUES (v7, v8, v9) Subquery;
Aquí, c1, c2 i c3 són els noms de les columnes i v1, v2 i v3 són els valors a inserir. El nom_table1, el_table2 i el_table3 són els noms de les taules.
# 2) INSERCIÓ CONDICIONAL DE TOTS
Una sentència INSERT ALL pot inserir més d'una fila a les taules en funció d'algunes condicions.
Sintaxi:
INSERT (ALL | FIRST) WHEN cond1 THEN INTO table_name1 <> VALUES <> WHEN cond2 THEN INTO table_name2 <> VALUES <> ELSE INTO table_name3 <> VALUES <> Subquery;
La paraula clau ALL s'utilitza per especificar que s'executaran totes les condicions de la clàusula WHERE. Si es compleix una condició, s'executaria la clàusula INTO corresponent.
Si s’utilitza la paraula clau FIRST per a cada fila individual obtinguda d’una subconsulta, Oracle executa els criteris de la clàusula WHEN de dalt a baix. Un cop es compleixi una condició, s'executaria la clàusula INTO corresponent i saltaria a una altra clàusula WHEN per a aquesta fila.
Hem creat les taules VEHICLE, CARS, SMALLCARS i LARGECARS amb l'ajut de les sentències SQL que es donen a continuació:
CREATE TABLE VEHICLE ( CARID INT NOT NULL, NAME VARCHAR (15) NOT NULL, COLOR VARCHAR (15), PRIMARY KEY (CARID) );
Inseriu els valors següents a la taula VEHICLE:
INSERT INTO VEHICLE VALUES (1, 'AUDI', 'RED'); INSERT INTO VEHICLE VALUES (4, 'BMW', 'WHITE'); INSERT INTO VEHICLE VALUES (7, 'KIA', 'GREEN'); CREATE TABLE CARS ( CARID INT NOT NULL, NAME VARCHAR (15) NOT NULL, COLOR VARCHAR (15), PRIMARY KEY (CARID) ); CREATE TABLE SMALLCARS AS SELECT * FROM CARS; CREATE TABLE LARGECARS AS SELECT * FROM CARS;
Implementació de codi amb INSERT ALL.
BEGIN INSERT ALL WHEN CARID = 1 THEN INTO CARS WHEN CARID = 4 THEN INTO SMALLCARS WHEN CARID = 7 THEN INTO LARGECARS SELECT CARID, NAME, COLOR FROM VEHICLE; END;
La sortida del codi anterior:
SELECT * from CARS;
SELECT * from SMALLCARS;
SELECT * from LARGECARS;
A l'exemple anterior, s'ha inserit una fila a cadascuna de les tres taules en funció del valor CARDID.
Hi ha certes restriccions amb la instrucció INSERT ALL:
S’utilitza només per inserir valors a les taules. No funciona amb vistes ni vistes materialitzades.
No és capaç d'inserir valors a taules remotes.
El nombre de columnes que pot gestionar una ordre INSERT INTO no pot ser superior a 999.
No es pot utilitzar una expressió de col·lecció de taules en una ordre INSERT de diverses taules.
Una subconsulta INSERT de diverses taules no és capaç d'utilitzar una seqüència.
Ordre PL SQL Update
La sentència UPDATE s’utilitza per modificar els valors d’una taula. També s’anomena a Llenguatge de manipulació de dades . Utilitza els noms de la taula, la columna i els valors com a entrades i realitza la modificació dels valors de la taula.
Sintaxi:
UPDATE <
> SET <>=<>, <>=<>,… WHERE <>;
La paraula clau SET s'utilitza per canviar el valor de la columna amb el valor nou. Opcionalment, s'utilitza la paraula clau WHERE, si s'omet, el valor de la columna especificada a la taula completa es modificarà.
ACTUALITZA UNA COLUMNA D'UNA FILA
Considerem de nou la taula EMPLOYEE.
Implementació del codi amb ACTUALITZACIÓ:
BEGIN UPDATE EMPLOYEE SET NAME = 'HENRY' WHERE CODE=1; END;
La sortida del codi anterior:
SELECT * from EMPLOYEE;
ACTUALITZA MÚLTIPLES COLUMNES D'UNA FILA
Considerem de nou la taula EMPLOYEE.
Implementació del codi amb ACTUALITZACIÓ:
BEGIN UPDATE EMPLOYEE SET NAME = 'COMO', AGE = 30 WHERE CODE=2; END;
La sortida del codi anterior:
SELECT * from EMPLOYEE;
ACTUALITZA MÚLTIPLES FILES
Considerem de nou la taula EMPLOYEE.
Implementació del codi amb ACTUALITZACIÓ:
BEGIN UPDATE EMPLOYEE SET AGE = AGE +1; END;
Sortida del codi anterior:
SELECT * from EMPLOYEE;
A l’exemple anterior, l’EDAT de tots els empleats va augmentar en 1.
Ordre PL SQL Delete
La sentència DELETE s'utilitza per eliminar un registre sencer de la taula. També s’anomena a Llenguatge de manipulació de dades.
Sintaxi:
DELETE FROM <
> WHERE <>;
La paraula clau FROM no és obligatòria i una sentència DELETE produirà el mateix resultat si FROM s'afegeix o no a la consulta. Opcionalment, s'utilitza la paraula clau WHERE, si s'omet, la taula completa se suprimirà.
ELIMINEU UNA FILA DE LA TAULA
Considerem de nou la taula EMPLOYEE.
Implementació de codi amb DELETE:
BEGIN DELETE FROM EMPLOYEE WHERE CODE=2; END;
Sortida del codi anterior:
SELECT * from EMPLOYEE;
ELIMINEU MULTIPLES FILES DE LA TAULA
Tornem a considerar la taula MANAGERDETAIL.
Implementació de codi amb DELETE:
BEGIN DELETE FROM MANAGERDETAIL WHERE CODE > 0; END;
Sortida del codi anterior:
SELECT * from MANAGERDETAIL;
ELIMINEU TOTES LES FILES DE LA TAULA
Considerem de nou la taula VEHICLE.
Implementació de codi amb DELETE:
BEGIN DELETE FROM VEHICLE; END;
Sortida del codi anterior:
SELECT * from VEHICLE;
Ordre PL SQL Select
La sentència SELECT s'utilitza per obtenir dades de la base de dades. La sentència SELECT INTO s'utilitza per recuperar els valors de la base de dades i emmagatzemar-los a les variables locals introduïdes per PL / SQL.
Si només fem servir una sentència SELECT, retorna un registre únic. En cas que una sentència SELECT obtingui diversos valors, TOO_MANY_ROWS PL / SQL genera una excepció. Mentre s’utilitza la sentència SELECT INTO, assignem com a mínim un valor a la variable. Tanmateix, si no s'obté cap registre de la base de dades, el fitxer NO S'HAN TROBAT DADES es llança una excepció.
El recompte de columnes i el seu tipus han de ser iguals al recompte de variables i el seu tipus a la paraula clau INTO. Els valors s’obtenen de la base de dades en la mateixa seqüència que es defineix a la sentència SELECT.
La paraula clau WHERE dins de la sentència SELECT és opcional i ens permet obtenir registres que compleixin determinats criteris. És important tenir en compte que la consulta SELECT amb ordres DELETE, UPDATE i INSERT no té una clàusula INTO.
Sintaxi:
SELECT <>, <>, ..<> INTO <>, <>, … <> FROM <
> WHERE <>;
RECUPERA DADES DE TOTES LES COLUMNES D’UNA TAULA
Considerem de nou la taula EMPLOYEE.
Implementació de codi amb SELECT:
BEGIN SELECT * FROM EMPLOYEE WHERE CODE=1; END;
Sortida del codi anterior:
Implementació de codi amb sentència SELECT que genera una excepció:
BEGIN SELECT * FROM EMPLOYEE WHERE CODE=2; END;
Sortida del codi anterior:
L'excepció que no s'ha trobat cap dada es genera ja que la sentència SELECT no ha pogut obtenir una fila que coincideixi amb la condició especificada, ja que no hi ha cap empleat amb CODI 2 a la base de dades.
RECUPERAR DADES D’UNA COLUMNA D’UNA TAULA
Considerem de nou la taula EMPLOYEE.
BEGIN SELECT CODE FROM EMPLOYEE; END;
La sortida del codi anterior:
RECUPERACIÓ DE DADES DE MÚLTIPLES COLUMNES D’UNA TAULA
Considerem de nou la taula EMPLOYEE.
BEGIN SELECT CODE, NAME FROM EMPLOYEE; END;
Sortida del codi anterior:
A l'exemple anterior, s'han afegit dues columnes CODE i NAME a la sentència SELECT.
Preguntes i respostes freqüents
Q # 1) Què és INSERT, UPDATE i DELETE a SQL?
Resposta: INSERT, UPDATE i DELETE són ordres de SQL que ajuden a operar i actualitzar dades. La sentència INSERT insereix files a una taula. La sentència DELETE suprimeix files d'una taula i la sentència UPDATE actualitza els valors de les files de la taula.
Q # 2) Podem INSERTAR, ACTUALITZAR i SUPRIMIR a la vista a SQL?
Resposta: Sí, podem INSERIR, ACTUALITZAR i SUPRIMIR a la vista a SQL. Si la vista s'ha unit entre les taules, només podem realitzar accions INSERT i UPDATE però no podem fer una operació DELETE. La sentència DROP s'utilitza per suprimir una vista.
P # 3) INSERT DDL o DML és?
Resposta: INSERT és un llenguatge de manipulació de dades o DML). Les altres declaracions DML inclouen UPDATE, DELETE, etc. La sentència DDL és capaç de crear taules, esquemes i bases de dades.
P # 4) Podem fer servir la consulta d'eliminació de JOIN IN?
Resposta: Sí, podem utilitzar la sentència INNER JOIN a l’ordre DELETE per suprimir files d’una taula.
P # 5) Quina diferència hi ha entre l'ordre trunca i suprimeix?
Resposta: TRUNCATE és una sentència DDL, mentre que DELETE és DML. L'ordre TRUNCATE és capaç d'eliminar la taula completa. No manté la integritat de la taula. La sentència DELETE només pot suprimir dades concretes de la taula. També pot incorporar condicions per a la supressió.
Q # 6) Què són DML, DCL i DDL?
Resposta: Les sentències SQL són del tipus: DML, DCL i DML.
El llenguatge de definició de dades o DDL inclou ordres com ALTER, DROP, CREATE, RENAME, TRUNCATE i COMMENT. El llenguatge de manipulació de dades o DML inclou ordres com INSERT, DELETE, MERGE, UPDATE, CALL, LOCK TABLE i EXPLAIN PLAN.
El llenguatge de control de dades o DCL inclou ordres com GRANT i REVOKE.
Q # 7) Es trunca més ràpid que deixar caure?
Resposta: Sí, l'operació TRUNCATE és més ràpida que DROP. L'ordre TRUNCATE només suprimeix les dades de la taula. L'ordre DROP esborra l'estructura de la taula i les dades de la taula.
Conclusió
En aquest tutorial, hem analitzat en detall alguns conceptes bàsics de les ordres PL SQL que són essencials per desenvolupar coneixements sobre ell. A continuació, hem tractat els temes següents:
