Tuesday 29 March 2016

Create Database manually in SQL SERVER

As salamo alaikum wa rahmatullah


Create database Food
ON
(
 Name=FoodData1,
 Filename='C:\ManualDB\SQLSERVER12.0\DATA\FoodData1.mdf',
 Size=10MB,
 MaxSize=unlimited,
 filegrowth=1GB
)
Log ON
(
 Name=FoodLog1,
 Filename='C:\ManualDB\SQLSERVER12.0\DATA\FoodData1_log.ldf',
 Size=10MB,
 MaxSize=unlimited,
 filegrowth=1024MB
)


--
Ma Asalaam
Passion 4 Oracle

Thursday 17 March 2016

Creating Data Block using Package and Procedure in Oracle 10g

As salamo alaikum wa rahmatullah

create table tbl_mst_student (roll number(3),
name varchar2(50),course varchar2(50))

create or replace package mypack
is
type student_rec is record (stud_id tbl_mst_student.roll%type,sname tbl_mst_student.name%type,scourse tbl_mst_student.course%type);
type stub is table of student_rec index by binary_integer;
procedure stuqry(data IN OUT stub);
procedure stuins(data IN stub);
end mypack;


create or replace package body mypack
is
procedure stuqry(data IN OUT stub)
is 
i number;
cursor cstudent is select * from tbl_mst_student;
begin
open cstudent;
i:=1;
loop
fetch cstudent into data(i).stud_id,data(i).sname,data(i).scourse;
exit when cstudent%notfound;
end loop;
close cstudent;
end stuqry;

procedure stuins(data IN stub)
is
i number;
ct number;
begin
ct:=data.count;
for i in 1..ct loop 
insert into tbl_mst_student values(data(i).stud_id,data(i).sname,data(i).scourse);
commit;
end loop;
end stuins;
end mypack;
/



select * from tbl_mst_student






Ma Asalaam
---------------
Passion 4 Oracle

Thursday 10 March 2016

TYPE EXAMPLE IN ORACLE 11G

As salamo alaikum wa rahmatullah


CREATE OR REPLACE TYPE PersonObj AS OBJECT (
  first_name  VARCHAR2(50),
  last_name   VARCHAR2(50),
  date_of_birth  DATE,
  MEMBER FUNCTION getAge RETURN NUMBER
)

create or replace type body PersonObj AS
Member Function getAge Return Number AS
Begin
    Return Trunc(Months_Between(Sysdate,date_of_birth)/12);
    End getAge;
End;

create table people
(
id number(2), person PersonObj);



INSERT INTO people
VALUES (1, PersonObj('John','Doe',
        TO_DATE('01/01/1999','DD/MM/YYYY')))
        
COMMIT

DECLARE
  v_person  PersonObj;
BEGIN
  v_person := PersonObj('Jane','Doe',
              TO_DATE('01/01/1999','DD/MM/YYYY'));
  INSERT INTO people VALUES (2, v_person);
  COMMIT;
END;


SELECT p.id,
       p.person.first_name,
       p.person.getAge() age
FROM   people p;


----------------
output 
------------




--

Ma asalaam
Passion 4 Oracle

Monday 7 March 2016

EXAMPLE OF AUTO INCREMENT COLUMN IN ORACLE

AS SALAMO ALAIKUM WA RAHMATULLAH



CREATE TABLE TBL_ITEM
(
ITM_ID NUMBER(5),
ITM_CODE VARCHAR2(30) UNIQUE NOT NULL,
ITM_DESC VARCHAR2(250),
ITM_PRICE NUMBER(10,3),
ITM_QTY NUMBER(3),
ITM_CRT_DT DATE,
ITM_UPD_DT DATE,
ITM_IS_DEL CHAR(1),
PRIMARY KEY(ITM_ID),
CHECK (ITM_IS_DEL IN ('Y','N'))
)


CREATE SEQUENCE SEQ_TBL_ITM_ID START WITH 1 INCREMENT BY 1


CREATE OR REPLACE TRIGGER TRG_ITM_ID
BEFORE INSERT ON TBL_ITEM
FOR EACH ROW
BEGIN
    SELECT SEQ_TBL_ITM_ID.NEXTVAL INTO :NEW.ITM_ID FROM DUAL;
END;
/

INSERT INTO TBL_ITEM (ITM_CODE,ITM_DESC,ITM_PRICE,ITM_QTY,ITM_CRT_DT,ITM_UPD_DT,ITM_IS_DEL)
VALUES('LS-GC17103','GRIFFIN FLAT AUX CABLE CONNECT + PLAY',5.000,10,SYSDATE,SYSDATE,'N')

SELECT * FROM TBL_ITEM


--
MA ASALAAM
PASSION 4 ORACLE