Delete records from multiple tables using procedure

Create two tables[mytable1 and mytable2]

mytable1 & mytable2

Create Procedure:

create PROCEDURE multi_delete(id1 number,id2 VARCHAR2)
as 
begin
DELETE MYTABLE1 WHERE MYNO=id1;
DELETE MYTABLE2 where NAME=id2;
end multi_delete;

Execute:

EXECUTE MULTI_DELETE(111,'gabrial');

Select statement:

SELECT * from MYTABLE1;

SELECT * from MYTABLE2;

Output:

mytable1

mytable2

Update two columns of two tables using trigger

Create three tables: [customer,purchase,payment]

create table  customer(customerid number not null,customername VARCHAR2(20),

                                   CONSTRAINT customerid_pk PRIMARY KEY(customerid));

                     

create table purchase(purchaseid number not null,customerid number not null,

                                  CONSTRAINT purchaseid_pk PRIMARY KEY(purchaseid),

                                  CONSTRAINT customerid_fk foreign key(customerid) REFERENCES

                                  customer(customerid));

                     

create table payment(paymentid number not null,customerid number not null,

                                 CONSTRAINT paymentid_pk PRIMARY KEY(paymentid),

                                 CONSTRAINT customerid1_fk foreign key(customerid) REFERENCES

                                 customer(customerid));

customer                                 

purchase

payment

Creating trigger

create or replace TRIGGER customertrigger

BEFORE UPDATE of customerid on customer

FOR EACH ROW

BEGIN

UPDATE PAYMENT

set customerid= :new.customerid

where PAYMENT.customerid= :old.customerid;

update purchase 

set customerid = :new.customerid

where purchase.customerid= :old.customerid;

end;

Update a query

update CUSTOMER

set CUSTOMERID=117 WHERE CUSTOMERNAME='aria';

OUTPUT

FOR i IN SELECT oracle LOOP

Create tables

create table table1(rollno number,name VARCHAR2(15));

table TABLE1 created.

create table table2(myno number,name VARCHAR2(20));

table TABLE2 created.

Insert values into table1

insert all into table1(rollno,name) values (111,'luke')
           into table1(rollno,name) values (112,'owen')
           into table1(rollno,name) values (113,'aria')
           into table1(rollno,name) values (114,'samuel')
           into table1(rollno,name) values (115,'gabrial')

SELECT * FROM dual;

5 rows inserted.

Code

BEGIN
for i in (SELECT ROLLNO,NAME FROM TABLE1)
LOOP
INSERT into TABLE2(MYNO,NAME) values(i.rollno,i.name);
End Loop;
End;

anonymous block completed.

Select table2

SELECT * FROM table2;

Output

111 luke
112 owen
113 aria
114 samuel

115 gabrial

Collection Methods

are  EXISTS,COUNT, LIMIT, FIRST, LAST, PRIOR, NEXT, EXTEND, TRIM, and DELETE.

We cannot use collection methods in a SQL statement.

FIRST AND LAST[example]

declare

        type months is varray(13) of VARCHAR2(20);

        mon months;

    begin

        mon:=months('jan','feb','mar','apr','may','jun','jul');

        for i in mon.first..mon.last

       loop

           DBMS_OUTPUT.put_line(mon(i));

       end loop;

   end;

OUTPUT

jan

feb

mar

apr

may

jun

jul

Create an auto increment using sequence and trigger

Step 1: Create Table[student]

Step 2: Create Sequence[sequence_student]

Step 3: Create Trigger[student_trigger]

Step 4: Insert values and select the table [student]

Auto Increment In Oracle[Example]

Step 1: Create Table [student]

Step 2: Create Sequence [sequence_student]

Step 3: Insert all into student

Step 4: output

This is not our expecting answer

Step 5: again output

Success !! this time right..  

UNION AND UNION ALL[EXAMPLE]

 Create two tables(CUSTOMERTABLE and CUSTOMERTABLE1)

CUSTOMERTABLE :

CUSTOMERTABLE1 :

UNION:

SELECT CUSTOMERID,name FROM CUSTOMERTABLE

UNION

 

SELECT CUSTOMERID,CUSTOMERNAME from CUSTOMERTABLE1;

 

UNION ALL :

SELECT CUSTOMERID,name FROM CUSTOMERTABLE

UNION all

SELECT CUSTOMERID,CUSTOMERNAME from CUSTOMERTABLE1;

General Error

SELECT CUSTOMERID,name FROM CUSTOMERTABLE

UNION all

SELECT CUSTOMERID,CUSTOMERNAME,NICKNAME from CUSTOMERTABLE1;

output:

ORA-01789: query block has incorrect number of result columns

General Error

SELECT name FROM CUSTOMERTABLE

UNION all

SELECT CUSTOMERID from CUSTOMERTABLE1;

output:

ORA-01790: expression must have same datatype as corresponding expression

SQL Error: ORA-00947

insert INTO EVERGREEN(name,salary,grade) values('Colin',6000);

Error report -

SQL Error: ORA-00947: not enough values

00947. 00000 -  "not enough values"

*Cause:    

*Action:

SOLUTION

insert INTO EVERGREEN(name,salary) values('Colin',6000);

1 rows inserted.

Simple Oracle's Merge statement[example]

step 1: Create two tables [EXAM and REEXAM] and insert                   values

insert all into exam(ROLLNO,SCORE) VALUES(111,80)

               into exam(ROLLNO,SCORE) VALUES(112,null)

               into exam(ROLLNO,SCORE) VALUES(113,68)

               into exam(ROLLNO,SCORE) VALUES(114,72)

               into exam(ROLLNO,SCORE) VALUES(115,null)

               into exam(ROLLNO,SCORE) VALUES(116,89)

               into exam(ROLLNO,SCORE) VALUES(117,76)

               into exam(ROLLNO,SCORE) VALUES(118,null)

               into exam(ROLLNO,SCORE) VALUES(119,null)

select * from dual;

EXAM table: 

ROLLNO	SCORE
111	80
112	(Null)
113	68
114	72
115	(Null)
116	89
117	76
118	(Null)
119	(Null)

INSERT all INTO REEXAM(ROLLNO,SCORE)VALUES(112,77)

                    INTO REEXAM(ROLLNO,SCORE)VALUES(115,67)

                    INTO REEXAM(ROLLNO,SCORE)VALUES(118,58)

                    INTO REEXAM(ROLLNO,SCORE)VALUES(119,83)

SELECT * FROM dual;

REEXAM table:

ROLLNO	SCORE
112	76
115	94
118	82
119	58

step 2: Merge tables

 MERGE INTO exam a USING (SELECT ROLLNO,score FROM reexam) b on (a.rollno=b.rollno)

  when MATCHED then UPDATE set a.score=b.score;

ROLLNO	SCORE
111	80
112	76
113	68
114	72
115	94
116	89
117	76
118	82
119	58

4 rows merged.

Multi Table Insert

Step 1: Create table

create table mybuddy(name VARCHAR2(25),salary NUMBER);

Step 2: Create three more tables

CREATE table table1 as SELECT * FROM MYBUDDY where 1=2;

CREATE table table2 as SELECT * FROM MYBUDDY where 1=2;

CREATE table table3 as SELECT * FROM MYBUDDY where 1=2;

Step 3: Insert all -  in to table mybuddy

insert all INTO MYBUDDY(NAME,SALARY) VALUES ('Luke',7000)

           INTO MYBUDDY(NAME,SALARY) VALUES ('Henry',9500)

           INTO MYBUDDY(NAME,SALARY) VALUES ('Logan',3900)

           INTO MYBUDDY(NAME,SALARY) VALUES ('Benjamin',8000)

  SELECT * FROM dual;

Step 4: Multi Table Insert

Insert all

    into table1

    into table2

    into table3

select name,salary from mybuddy;

INSERT ALL

This allows you multiple inserts.

Step 1: Create table

create table mybuddy(name VARCHAR2(25),salary NUMBER);

table MYBUDDY created.

Step 2: Insert all

insert all INTO MYBUDDY(NAME,SALARY) VALUES ('Luke',7000)

           INTO MYBUDDY(NAME,SALARY) VALUES ('Henry',9500)

           INTO MYBUDDY(NAME,SALARY) VALUES ('Logan',3900)

           INTO MYBUDDY(NAME,SALARY) VALUES ('Benjamin',8000)

  SELECT * FROM dual;

4 rows inserted.