Converting month number to month name

Step 1: Create Table

create table student(name varchar2(15),age number,month_joining VARCHAR2(20));

Step 2: Procedure

create PROCEDURE studentdetail(id1 VARCHAR2,id2 number,id3 number)
as
begin
insert into STUDENT(name,age,month_joining)VALUES(id1,id2,(SELECT TO_CHAR(TO_DATE(id3, 'MM'), 'MONTH') FROM DUAL));
end studentdetail;

Step3 : Execute

EXECUTE STUDENTDETAIL('aadesh',19,2);
EXECUTE STUDENTDETAIL('aagnya',21,1);
EXECUTE STUDENTDETAIL('ddarun',20,4);

Step 4: Select the table

SELECT * FROM STUDENT;

Step 5: Output

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..