Wednesday, February 13, 2013

SQL Alias Syntax for Tables

  • RUS means, a union operation on R and S that will produce another table which will have all the rows of R and S except the duplicates
  • There are 2 Sally in R and S, so, just take 1 from them 
  • R-S means, a difference operation on R and S that will produce another table which will have all the rows which is in R but not in S. 
  • As Sally is in R and in S, so, she has been omitted.
  • R∩S means, an intersection operation on R and S that will produce another table which will have all the rows which are in both R and S.
  • As Sally is in R and in S, so, she has been selected.
  • You can give a table or a column another name by using an alias. 
  • This can be a good thing to do if you have very long or complex table names or column names. 
SQL Alias Syntax for Tables
  SELECT column_name(s)
FROM table_name AS alias_name 
SQL Alias Syntax for Columns
  SELECT column_name AS alias_name
FROM table_name

-->
-- Script to create tables / views
-- You should have a tablespace USERS,
-- and the user running this script should have sufficient quota on USERS tablespace.
-- For simplicity data as well as indexes are kept in one tablespace.

DROP TABLE CUST_ORDER;
DROP TABLE CUSTOMER;
DROP TABLE DEPARTMENT;
DROP TABLE EMPLOYEE;
DROP TABLE INVENTORY_CLASS;
DROP TABLE JOB;
DROP TABLE LINE_ITEM;
DROP TABLE LOCATION;
DROP TABLE MONTHS;
DROP TABLE MTD_ORDERS;
DROP TABLE ORDERS;
DROP TABLE PART;
DROP TABLE SALESPERSON;
DROP TABLE SUPPLIER;
DROP TABLE REGION;
DROP TABLE ASSEMBLY;
DROP TABLE RETAILER;


CREATE TABLE CUST_ORDER(
    ORDER_NBR           NUMBER(7)           NOT NULL,
    CUST_NBR            NUMBER(5)           NOT NULL,
    SALES_EMP_ID        NUMBER(5)           NOT NULL,
    SALE_PRICE          NUMBER(9,2),
    ORDER_DT            DATE                NOT NULL,
    EXPECTED_SHIP_DT    DATE                NOT NULL,
    CANCELLED_DT        DATE,
    SHIP_DT             DATE,
    STATUS              VARCHAR2(20),
    CONSTRAINT cust_order_pk PRIMARY KEY (ORDER_NBR)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 20K
                NEXT 20K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 50K
        NEXT 50K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE CUSTOMER(
    CUST_NBR    NUMBER(5)       NOT NULL,
    NAME        VARCHAR2(30)    NOT NULL,
    REGION_ID   NUMBER(5),
    INACTIVE_DT DATE,
    INACTIVE_IND CHAR(1),
    TOT_ORDERS  NUMBER(5),
    LAST_ORDER_DT DATE,
    CONSTRAINT customer_pk PRIMARY KEY (CUST_NBR)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE DEPARTMENT(
    DEPT_ID    NUMBER(5)       NOT NULL,
    NAME       VARCHAR2(20),
    LOCATION_ID NUMBER(3),
    CONSTRAINT department_pk PRIMARY KEY (DEPT_ID)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE EMPLOYEE(
    EMP_ID            NUMBER(5)    NOT NULL,
    FNAME             VARCHAR2(20),
    LNAME             VARCHAR2(20),
    DEPT_ID           NUMBER(5)    NOT NULL,
    MANAGER_EMP_ID    NUMBER(5),
    SALARY            NUMBER(5),
    HIRE_DATE         DATE,
    JOB_ID            NUMBER(3),
    CONSTRAINT employee_pk PRIMARY KEY (EMP_ID)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE INVENTORY_CLASS(
    INV_CLASS    VARCHAR2(3) NOT NULL,
    LOW_COST     NUMBER(8,2),
    HIGH_COST    NUMBER(8,2),
    CONSTRAINT inv_class_pk PRIMARY KEY (INV_CLASS)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE JOB(
    JOB_ID    NUMBER(3) NOT NULL,
    FUNCTION  VARCHAR2(30),
    CONSTRAINT job_pk PRIMARY KEY (JOB_ID)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);
  
CREATE TABLE LINE_ITEM(
    ORDER_NBR     NUMBER(9)      NOT NULL,
    PART_NBR      VARCHAR2(20)   NOT NULL,
    QTY           NUMBER(5)      NOT NULL,
    FILLED_QTY    NUMBER(5),
    CONSTRAINT line_item_pk PRIMARY KEY (ORDER_NBR,PART_NBR)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 20K
                NEXT 20K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 50K
        NEXT 50K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE LOCATION(
    LOCATION_ID    NUMBER(3) NOT NULL,
    REGIONAL_GROUP VARCHAR2(20),
    CONSTRAINT location_pk PRIMARY KEY (LOCATION_ID)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE MONTHS(
    YEAR       NUMBER(4) NOT NULL,
    MONTH      NUMBER(2) NOT NULL,
    CONSTRAINT months_pk PRIMARY KEY (YEAR, MONTH)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 1K
        NEXT 1K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE MTD_ORDERS(
    TOT_ORDERS                  NUMBER(7) NOT NULL,
    TOT_SALE_PRICE              NUMBER(11,2) NOT NULL,
    MAX_SALE_PRICE              NUMBER(9,2) NOT NULL,
    EUROPE_TOT_ORDERS           NUMBER(7) NOT NULL,
    EUROPE_TOT_SALE_PRICE       NUMBER(11,2) NOT NULL,
    EUROPE_MAX_SALE_PRICE       NUMBER(9,2) NOT NULL,
    NORTHAMERICA_TOT_ORDERS     NUMBER(7) NOT NULL,
    NORTHAMERICA_TOT_SALE_PRICE NUMBER(11,2) NOT NULL,
    NORTHAMERICA_MAX_SALE_PRICE NUMBER(9,2) NOT NULL
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 1K
        NEXT 1K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE ORDERS(
    CUST_NBR       NUMBER(5) NOT NULL,
    REGION_ID      NUMBER(5) NOT NULL,
    SALESPERSON_ID NUMBER(5) NOT NULL,
    YEAR           NUMBER(4) NOT NULL,
    MONTH          NUMBER(2) NOT NULL,
    TOT_ORDERS     NUMBER(7) NOT NULL,
    TOT_SALEs      NUMBER(11,2) NOT NULL,
    CONSTRAINT orders_pk PRIMARY KEY (YEAR, MONTH, CUST_NBR, REGION_ID,
      SALESPERSON_ID)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 1K
        NEXT 1K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);
  
CREATE TABLE PART(
    PART_NBR         VARCHAR2(20)        NOT NULL,
    NAME             VARCHAR2(30)        NOT NULL,
    SUPPLIER_ID      NUMBER(5)           NOT NULL,
    STATUS           VARCHAR2(20)        NOT NULL,
    INVENTORY_QTY    NUMBER(6),
    UNIT_COST        NUMBER(8,2),
    RESUPPLY_DATE    DATE,
    CONSTRAINT part_pk PRIMARY KEY (PART_NBR)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 25K
        NEXT 25K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE SALESPERSON(
    SALESPERSON_ID    NUMBER(5) NOT NULL,
    NAME              VARCHAR2(50) NOT NULL,
    PRIMARY_REGION_ID NUMBER(5) NOT NULL,
    CONSTRAINT salesperson_pk PRIMARY KEY (SALESPERSON_ID)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 1K
        NEXT 1K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE SUPPLIER(
    SUPPLIER_ID    NUMBER(5)     NOT NULL,
    NAME           VARCHAR2(30)  NOT NULL,
    CONSTRAINT supplier_pk PRIMARY KEY (SUPPLIER_ID)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

CREATE TABLE REGION(
    REGION_ID    NUMBER(5)       NOT NULL,
    NAME       VARCHAR2(20),
    SUPER_REGION_ID NUMBER(5),
    CONSTRAINT region_pk PRIMARY KEY (REGION_ID)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);
      
      
CREATE TABLE ASSEMBLY (
 ASSEMBLY_TYPE              VARCHAR2(4)     NOT NULL,
 ASSEMBLY_ID                NUMBER(6)         NOT NULL,
 DESCRIPTION                VARCHAR2(20)     NOT NULL,
 PARENT_ASSEMBLY_TYPE       VARCHAR2(4),
 PARENT_ASSEMBLY_ID         NUMBER(6),
     CONSTRAINT assembly_pk PRIMARY KEY (ASSEMBLY_TYPE, ASSEMBLY_ID)
     USING INDEX
         PCTFREE 10
         INITRANS 2
         MAXTRANS 255
         TABLESPACE USERS
         STORAGE(INITIAL 10K
                 NEXT 10K
                 MINEXTENTS 1
                 MAXEXTENTS 121
                 PCTINCREASE 0)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);


-- Create table retailer

CREATE TABLE RETAILER(
 RTLR_NBR             NUMBER(6)    NOT NULL ,
 NAME                 VARCHAR2(45),                  
 ADDRESS              VARCHAR2(40),
 CITY                 VARCHAR2(30),
 STATE                VARCHAR2(2),
 ZIP_CODE             VARCHAR2(9),
 AREA_CODE            NUMBER(3),
 PHONE_NUMBER         NUMBER(7),
 SALESPERSON_ID       NUMBER(4),
 CREDIT_LIMIT         NUMBER(9,2),
 COMMENTS             LONG,
    CONSTRAINT RETAILER_PK PRIMARY KEY (RTLR_NBR)
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE USERS
        STORAGE(INITIAL 10K
                NEXT 10K
                MINEXTENTS 1
                MAXEXTENTS 121
                PCTINCREASE 0)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0);

-- Create view DEPT

CREATE OR REPLACE VIEW DEPT AS
SELECT *
FROM DEPARTMENT
WHERE DEPT_ID IN (10,20,30,40);


      
      
       


  
-->
       DROP TABLE students;
DROP TABLE english_group;
CREATE TABLE students(
    Roll    Int,
    Name    Varchar (20),
    Subject    Varchar(20)
);

CREATE TABLE english_group(
    Roll    Int,
    Name    Varchar (20)
);

INSERT INTO students VALUES (1, 'Ratul', 'Science');
INSERT INTO students VALUES (2, 'Nafee', 'Science');
INSERT INTO students VALUES (3, 'Shawon', 'English');
INSERT INTO students VALUES (4, 'Biva', 'Geography');
INSERT INTO students VALUES (5, 'Farhana', 'English');

COMMIT;


-->
--Setup the needed default date format, and language, for the session.
PROMPT Changing date language to 'AMERICAN', and
PROMPT the date format to 'DD-MON-RR'.
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR';
PROMPT
PROMPT You may wish to reconnect after this script
PROMPT completes, as reconnecting will restore your
PROMPT date language and date format settings to
PROMPT their defaults.
PROMPT
PAUSE Press ENTER to continue...

-- Delete existing data from tables used in chapter 3.


delete from employee;
delete from location;
delete from department;
delete from job;
delete from supplier;
delete from part;
delete from retailer;
delete from inventory_class;


-- Insert new data relevant to chapter 3.


-- Insert data into employee

insert into employee
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7369,'JOHN','SMITH',20,7902,800,'17-DEC-80',667);
insert into employee
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7499,'KEVIN','ALLEN',30,7698,1600,'20-FEB-81',670);
insert into employee
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7521,'CYNTHIA','WARD',30,7698,1250,'22-FEB-81',670);
insert into employee
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7566,'TERRY','JONES',20,7839,2000,'02-APR-81',671);
insert into employee
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7654,'KENNETH','MARTIN',30,7698,1250,'28-SEP-81',670);
insert into employee
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7698,'MARION','BLAKE',30,7839,2850,'01-MAY-80',671);
insert into employee
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7782,'CAROL','CLARK',10,7839,2450,'09-JUN-81',671);
insert into employee
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7788,'DONALD','SCOTT',20,7566,3000,'19-APR-87',669);
insert into employee
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7839,'FRANCIS','KING',10, null,5000,'17-NOV-81',672);
insert into employee
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7844,'MARY','TURNER',30,7698,1500,'08-SEP-81',670);
insert into employee
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7876,'DIANE','ADAMS',20,7788,1100,'23-MAY-87',667);
insert into employee
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7900,'FRED','JAMES',30,7698,950,'03-DEC-81',667);
insert into employee
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7902,'JENNIFER','FORD',20,7566,3000,'03-DEC-81',669);
insert into employee
(EMP_ID,FNAME,LNAME,DEPT_ID,MANAGER_EMP_ID,SALARY,HIRE_DATE,JOB_ID)
values
(7934,'BARBARA','MILLER',10,7782,1300,'23-JAN-82',667);

commit;


-- Insert data into location

insert into location
(LOCATION_ID,REGIONAL_GROUP)
values
(122,'NEW YORK');
insert into location
(LOCATION_ID,REGIONAL_GROUP)
values
(124,'DALLAS');
insert into location
(LOCATION_ID,REGIONAL_GROUP)
values
(123,'CHICAGO');
insert into location
(LOCATION_ID,REGIONAL_GROUP)
values
(167,'BOSTON');
insert into location
(LOCATION_ID,REGIONAL_GROUP)
values
(144,'SAN FRANCISCO');

commit;


-- Insert data into department

insert into department
(DEPT_ID,NAME,LOCATION_ID)
values
(10,'ACCOUNTING',122);
insert into department
(DEPT_ID,NAME,LOCATION_ID)
values
(20,'RESEARCH',124);
insert into department
(DEPT_ID,NAME,LOCATION_ID)
values
(30,'SALES',123);
insert into department
(DEPT_ID,NAME,LOCATION_ID)
values
(40,'OPERATIONS',167);
insert into department
(DEPT_ID,NAME,LOCATION_ID)
values
(12,'RESEARCH',122);
insert into department
(DEPT_ID,NAME,LOCATION_ID)
values
(13,'SALES',122);
insert into department
(DEPT_ID,NAME,LOCATION_ID)
values
(14,'OPERATIONS',122);
insert into department
(DEPT_ID,NAME,LOCATION_ID)
values
(23,'SALES',124);
insert into department
(DEPT_ID,NAME,LOCATION_ID)
values
(24,'OPERATIONS',124);
insert into department
(DEPT_ID,NAME,LOCATION_ID)
values
(34,'OPERATIONS',123);
insert into department
(DEPT_ID,NAME,LOCATION_ID)
values
(43,'SALES',167);
insert into department
(DEPT_ID,NAME,LOCATION_ID)
values
(50,'MARKETING',null);
insert into department
(DEPT_ID,NAME,LOCATION_ID)
values
(60,'CONSULTING',null);

commit;


-- Insert data into Job

insert into Job
(JOB_ID,FUNCTION)
values
(667,'CLERK');
insert into Job
(JOB_ID,FUNCTION)
values
(668,'STAFF');
insert into Job
(JOB_ID,FUNCTION)
values
(669,'ANALYST');
insert into Job
(JOB_ID,FUNCTION)
values
(670,'SALESPERSON');
insert into Job
(JOB_ID,FUNCTION)
values
(671,'MANAGER');
insert into Job
(JOB_ID,FUNCTION)
values
(672,'PRESIDENT');
insert into Job
(JOB_ID,FUNCTION)
values
(665,'SECRETARY');

commit;



-- Insert data into Supplier

insert into Supplier
(SUPPLIER_ID, NAME)
values
(101, 'Pacific Disks, Inc.');


insert into Supplier
(SUPPLIER_ID, NAME)
values
(102, 'Silicon Valley MicroChips');

insert into Supplier
(SUPPLIER_ID, NAME)
values
(103, 'Blue River Electronics');

commit;


-- Insert data into Part

insert into Part
(PART_NBR, NAME, SUPPLIER_ID, STATUS, INVENTORY_QTY, UNIT_COST, RESUPPLY_DATE)
values
('HD211', '20 GB Hard Disk', 101, 'ACTIVE', 5, 2000, '12-DEC-00');

insert into Part
(PART_NBR, NAME, SUPPLIER_ID, STATUS, INVENTORY_QTY, UNIT_COST, RESUPPLY_DATE)
values
('P3000', '3000 MHz Processor', 102, 'ACTIVE', 12, 600, '03-NOV-00');

commit;


-- Insert data into Inventory_class

insert into Inventory_class
(INV_CLASS, LOW_COST, HIGH_COST)
values
('C',0,99);

insert into Inventory_class
(INV_CLASS, LOW_COST, HIGH_COST)
values
('B',100,999);

insert into Inventory_class
(INV_CLASS, LOW_COST, HIGH_COST)
values
('A',1000,9999);

commit;


-- Inserting data into retailer


insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(100,'JOCKSPORTS','345 VIEWRIDGE','BELMONT','CA','96711',7844);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(101,'TKB SPORT SHOP','490 BOLI RD.','REDWOOD CITY','CA','94061',7521);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(102,'VOLLYRITE','9722 HAMILTON','BURLINGAME','CA','95133',7654);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(103,'JUST TENNIS','HILLVIEW MALL','BURLINGAME','CA','97544',7521);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(104,'EVERY MOUNTAIN','574 SURRY RD.','CUPERTINO','CA','93301',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(105,'K + T SPORTS','3476 EL PASEO','SANTA CLARA','CA','91003',7844);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(106,'SHAPE UP','908 SEQUOIA','PALO ALTO','CA','94301',7521);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(107,'WOMENS SPORTS','VALCO VILLAGE','SUNNYVALE','CA','93301',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(201,'STADIUM SPORTS','47 IRVING PL.','NEW YORK','NY','10003',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(202,'HOOPS','2345 ADAMS AVE.','LEICESTER','MA','01524',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(203,'REBOUND SPORTS','2 E. 14TH ST.','NEW YORK','NY','10009',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(204,'THE POWER FORWARD','1 KNOTS LANDING','DALLAS','TX','75248',7654);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(205,'POINT GUARD','20 THURSTON ST.','YONKERS','NY','10956',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(206,'THE COLISEUM','5678 WILBUR PL.','SCARSDALE','NY','10583',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(207,'FAST BREAK','1000 HERBERT LN.','CONCORD','MA','01742',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(208,'AL AND BOB''S SPORTS','260 YORKTOWN CT.','AUSTIN','TX','78731',7654);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(211,'AT BAT','234 BEACHEM ST.','BROOKLINE','MA','02146',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(212,'ALL SPORT','1000 38TH ST.','BROOKLYN','NY','11210',7844);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(213,'GOOD SPORT','400 46TH ST.','SUNNYSIDE','NY','11104',7844);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(214,'AL''S PRO SHOP','45 SPRUCE ST.','SPRING','TX','77388',7654);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(215,'BOB''S FAMILY SPORTS','400 E. 23RD','HOUSTON','TX','77026',7654);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(216,'THE ALL AMERICAN','547 PRENTICE RD.','CHELSEA','MA','02150',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(217,'HIT, THROW, AND RUN','333 WOOD COURT','GRAPEVINE','TX','76051',7654);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(218,'THE OUTFIELD','346 GARDEN BLVD.','FLUSHING','NY','11355',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(221,'WHEELS AND DEALS','2 MEMORIAL DRIVE','HOUSTON','TX','77007',7844);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(222,'JUST BIKES','4000 PARKRIDGE BLVD.','DALLAS','TX','75205',7844);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(223,'VELO SPORTS','23 WHITE ST.','MALDEN','MA','02148',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(224,'JOE''S BIKE SHOP','4500 FOX COURT','GRAND PRARIE','TX','75051',7844);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(225,'BOB''S SWIM, CYCLE, AND RUN','300 HORSECREEK CIRCLE','IRVING','TX','75039',7844);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(226,'CENTURY SHOP','8 DAGMAR DR.','HUNTINGTON','NY','11743',7521);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(227,'THE TOUR','2500 GARDNER RD.','SOMERVILLE','MA','02144',7499);
insert into retailer
(RTLR_NBR,NAME,ADDRESS,CITY,STATE,ZIP_CODE,SALESPERSON_ID)
values
(228,'FITNESS FIRST','5000 85TH ST.','JACKSON HEIGHTS','NY','11372',7521);

commit;


-->
--Setup the needed default date format, and language, for the session.
PROMPT Changing date language to 'AMERICAN', and
PROMPT the date format to 'DD-MON-RR'.
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR';
PROMPT
PROMPT You may wish to reconnect after this script
PROMPT completes, as reconnecting will restore your
PROMPT date language and date format settings to
PROMPT their defaults.
PROMPT
PAUSE Press ENTER to continue...

-- Delete existing data from tables used in chapter 7.


delete from customer;
delete from cust_order;


-- Insert new data relevant to chapter 7.


-- Insert data into customer

insert into customer (cust_nbr, name, region_id, tot_orders, last_order_dt)
select c.c_nbr, c.c_nm, c.rgn, c.tot_ord, to_date(c.lst_ord, 'DD-MON-RR')
from
(select 1 c_nbr, 'Cooper Industries' c_nm, 5 rgn,  96 tot_ord, '15-JUN-00' lst_ord from dual union all
select 2 c_nbr, 'Emblazon Corp.' c_nm, 5 rgn,  96 tot_ord, '27-JUN-00' lst_ord from dual union all
select 3 c_nbr, 'Ditech Corp.' c_nm, 5 rgn,  96 tot_ord, '07-JUL-00' lst_ord from dual union all
select 4 c_nbr, 'Flowtech Inc.' c_nm, 5 rgn,  96 tot_ord, '15-JUL-00' lst_ord from dual union all
select 5 c_nbr, 'Gentech Industries' c_nm, 5 rgn,  96 tot_ord, '01-JUN-00' lst_ord from dual union all
select 6 c_nbr, 'Spartan Industries' c_nm, 6 rgn,  96 tot_ord, '10-JUN-00' lst_ord from dual union all
select 7 c_nbr, 'Wallace Labs' c_nm, 6 rgn,  101 tot_ord, '17-JUN-00' lst_ord from dual union all
select 8 c_nbr, 'Zantech Inc.' c_nm, 6 rgn,  96 tot_ord, '22-JUN-00' lst_ord from dual union all
select 9 c_nbr, 'Cardinal Technologies' c_nm, 6 rgn,  96 tot_ord, '25-JUN-00' lst_ord from dual union all
select 10 c_nbr, 'Flowrite Corp.' c_nm, 6 rgn,  96 tot_ord, '01-JUN-00' lst_ord from dual union all
select 11 c_nbr, 'Glaven Technologies' c_nm, 7 rgn,  96 tot_ord, '05-JUN-00' lst_ord from dual union all
select 12 c_nbr, 'Johnson Labs' c_nm, 7 rgn,  96 tot_ord, '07-JUN-00' lst_ord from dual union all
select 13 c_nbr, 'Kimball Corp.' c_nm, 7 rgn,  96 tot_ord, '07-JUN-00' lst_ord from dual union all
select 14 c_nbr, 'Madden Industries' c_nm, 7 rgn,  101 tot_ord, '05-JUN-00' lst_ord from dual union all
select 15 c_nbr, 'Turntech Inc.' c_nm, 7 rgn,  96 tot_ord, '01-JUN-00' lst_ord from dual union all
select 16 c_nbr, 'Paulson Labs' c_nm, 8 rgn,  96 tot_ord, '31-MAY-00' lst_ord from dual union all
select 17 c_nbr, 'Evans Supply Corp.' c_nm, 8 rgn,  96 tot_ord, '28-MAY-00' lst_ord from dual union all
select 18 c_nbr, 'Spalding Medical Inc.' c_nm, 8 rgn,  96 tot_ord, '23-MAY-00' lst_ord from dual union all
select 19 c_nbr, 'Kendall-Taylor Corp.' c_nm, 8 rgn,  96 tot_ord, '16-MAY-00' lst_ord from dual union all
select 20 c_nbr, 'Malden Labs' c_nm, 8 rgn,  96 tot_ord, '01-JUN-00' lst_ord from dual union all
select 21 c_nbr, 'Crimson Medical Inc.' c_nm, 9 rgn,  101 tot_ord, '26-MAY-00' lst_ord from dual union all
select 22 c_nbr, 'Nichols Industries' c_nm, 9 rgn,  96 tot_ord, '18-MAY-00' lst_ord from dual union all
select 23 c_nbr, 'Owens-Baxter Corp.' c_nm, 9 rgn,  96 tot_ord, '08-MAY-00' lst_ord from dual union all
select 24 c_nbr, 'Jackson Medical Inc.' c_nm, 9 rgn, 96 tot_ord, '26-APR-00' lst_ord from dual union all
select 25 c_nbr, 'Worcester Technologies' c_nm, 9 rgn,  96 tot_ord, '01-JUN-00' lst_ord from dual union all
select 26 c_nbr, 'Alpha Technologies' c_nm, 10 rgn,  96 tot_ord, '21-MAY-00' lst_ord from dual union all
select 27 c_nbr, 'Phillips Labs' c_nm, 10 rgn,  96 tot_ord, '08-MAY-00' lst_ord from dual union all
select 28 c_nbr, 'Jaztech Corp.' c_nm, 10 rgn,  101 tot_ord, '23-APR-00' lst_ord from dual union all
select 29 c_nbr, 'Madden-Taylor Inc.' c_nm, 10 rgn,  96 tot_ord, '06-APR-00' lst_ord from dual union all
select 30 c_nbr, 'Wallace Industries' c_nm, 10 rgn,  96 tot_ord, '01-JUN-00' lst_ord from dual) c;

commit;



-- Insert data into cust_order

insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1001,1,7354,99,'22-JUL-01','23-JUL-01',null,null,'DELIVERED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1000,1,7354,null,'19-JUL-01','24-JUL-01','21-JUL-01',null,'CANCELLED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1002,5,7368,null,'12-JUL-01','25-JUL-01','14-JUL-01',null,'CANCELLED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1003,4,7654,56,'16-JUL-01','26-JUL-01',null,null,'DELIVERED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1004,4,7654,34,'18-JUL-01','27-JUL-01',null,null,'PENDING');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1005,8,7654,99,'22-JUL-01','24-JUL-01',null,null,'DELIVERED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1006,1,7354,null,'22-JUL-01','28-JUL-01','24-JUL-01',null,'CANCELLED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1007,5,7368,25,'20-JUL-01','22-JUL-01',null,null,'PENDING');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1008,5,7368,25,'21-JUL-01','23-JUL-01',null,null,'PENDING');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1009,1,7354,56,'18-JUL-01','22-JUL-01',null,null,'DELIVERED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1012,1,7354,99,'22-JUL-01','23-JUL-01',null,null,'DELIVERED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1011,1,7354,null,'19-JUL-01','24-JUL-01','21-JUL-01',null,'CANCELLED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1015,5,7368,null,'12-JUL-01','25-JUL-01','14-JUL-01',null,'CANCELLED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1017,4,7654,56,'16-JUL-01','26-JUL-01',null,null,'DELIVERED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1019,4,7654,34,'18-JUL-01','27-JUL-01',null,null,'PENDING');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1021,8,7654,99,'22-JUL-01','24-JUL-01',null,null,'DELIVERED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1023,1,7354,null,'22-JUL-01','28-JUL-01','24-JUL-01',null,'CANCELLED');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1025,5,7368,25,'20-JUL-01','22-JUL-01',null,null,'PENDING');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1027,5,7368,25,'21-JUL-01','23-JUL-01',null,null,'PENDING');
insert into cust_order
(ORDER_NBR,CUST_NBR,SALES_EMP_ID,SALE_PRICE,ORDER_DT,EXPECTED_SHIP_DT,CANCELLED_DT,SHIP_DT,STATUS)
values
(1029,1,7354,56,'18-JUL-01','22-JUL-01',null,null,'DELIVERED');
commit;
Posted by OPP(Object Oriented Programming) at 2:24 AM 0 comments

What is PL/SQL?

SQL
—Structured language to access database
—ANSI Standard
—Declarative
○Specifies what to access but not how to
   - SELECT id, first_name, family_name FROM authors
PL/SQL
—Developed by Oracle as a procedural extension to SQL
—Declare variables, IF..ELSE, WHILE & FOR loops, functions, procedures and other features found in a programming language.
 Server Side Programming 
PL/SQL executes inside DBMS
—DBMS maintains Relational data
—Also stores and executes Procedural code!
 Advantages
—Results from one query can be used as a basis for the next query without having to pull data from DBMS to client side for processing!
DBMS maintains relational data
Client side programs implement procedural logic

Is there a problem?

Performance impact due increased network traffic
—Cumulative effect if many clients run at the same time
Code re-use may not be possible
—Many applications may incorporate processing that are quite similar but sharing may not be easy!
Block structured
—Basic program unit is a block
—Contains variables, code and error handler
A BLOCK is contained within BEGIN and END statements with executable commands in between
—Must contain some commands, even if they do nothing!
PL/SQL programs must at least contain 1 block
Blocks can be nested (block within another block)

EXCEPTIONS
—“unexpected errors” that occur during execution
—occurs at run-time not at compile time!
EXCEPTION HANDLER
—Code that executes when EXCEPTION occurs
—Makes the code more robust
Oracle Server has many pre-defined errors
—no_data_found, value_error, too_many_rows, others.
  PL/SQL - Anonymous Block
DECLARE
  today  date;
BEGIN
    SELECT sysdate INTO today FROM dual;
   DBMS_OUTPUT.PUT_LINE (‘Today -’ || today);
END;
•What exactly happens in this code?
•What is DUAL? (revision test !)
PL/SQL - Named Block
Has a name and stored in Oracle Server
Contains Header section
—name,
—key word - a function, procedure or trigger
—type of value it returns in case of function
At the time of creation, the code within the named block is NOT executed but compiled and stored in Oracle Server

DECLARE
family_name  VARCHAR2 (20);
Age         NUMBER(3);
Assignment
   family_name := ‘Anderson’;
   Age := 21;

PL/SQL – %Type

%TYPE
—To map a variable directly to the same datatype as the table column
DECLARE
author_id  AUTHORS.ID%TYPE;
PL/SQL variable ‘author_id’ is of same datatype
  which is used to define column name ‘id’ of table AUTHORS.
If column type changes PL/SQL Code would still work!
   {Example: VARCHAR2(20) to VARCHAR(30)}

PL/SQL - Scope Rules

Variables, procedures and functions can be referenced by the code executing inside the block in which they are defined
Understanding of scope of variables, functions is especially important in the context of nested blocks!
PL/SQL Scope Example

DECLARE
  father_name VARCHAR2(20):='Patrick';
  date_of_birth DATE:='20-Apr-1972';

  BEGIN
  DECLARE
    child_name VARCHAR2(20):='Mike';
    date_of_birth DATE:='12-Dec-2002';

  BEGIN
    DBMS_OUTPUT.PUT_LINE('Father''s Name: '||father_name);
    DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth);
    DBMS_OUTPUT.PUT_LINE('Child''s Name: '||child_name);
  END;

  DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth);
END;
/

PL/SQL Operators
Expressions consist of PL/SQL operators and operands
—Arithmetic Operators
○**, *, /, +, -
—Comparison Operators
○=, <>, !=, <, >, <=, >=, LIKE, BETWEEN, IN,  IS NULL
—Logical Operators
○AND, OR, NOT
String Operator – Concatenation using ‘||’
NULL means “UNKNOWN” value
Use IS NULL or IS NOT NULL to check for NULL value
NULL value comparison with ‘= NULL’ or ‘!= NULL’ may produce unpredictable results!
Use NVL function when appropriate
NVL (, )

DECLARE
     name      VARCHAR2(20);
     surname  VARCHAR2(20);
BEGIN
     SELECT first_name, family_name
     INTO name, surname 
     FROM  AUTHORS
     WHERE id = 1 ;
     DBMS_OUTPUT.PUT_LINE
           (‘Row selected is : ‘ || name || ‘-’ || surname);
END;
You can write INSERT, UPDATE, and DELETE statements directly in PL/SQL programs, without any special notation:
%ROWCOUNT Attribute: How Many Rows Affected So Far?
Example
SET SERVEROUTPUT ON;
BEGIN
   UPDATE employees SET salary = salary * 1.05 WHERE ...;
    dbms_output.put_line('Updated ' || SQL%ROWCOUNT || ' salaries.');
END;
/


  


Conditional execution
—IF-THEN, IF-THEN-ELSE, IF-THEN-ELSIF
—CASE
Repeated execution until some condition
— LOOP-END LOOP, FOR-LOOP-END LOOP
—WHILE-LOOP-END LOOP
—EXIT WHEN
Jump to code section
—GOTO 
IF    
THEN
   statement1;
   statement2;
   …..
END  IF;
can be evaluated to TRUE, FALSE or NULL – statement1, statement2 etc., are executed only if it evaluates to TRUE

IF sales > quota THEN compute_bonus(empid);
   UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id; 
END IF;

IF    
THEN
   statement1;
   statement2;
   …..
ELSE
   statement3;
   statement4;
   …..
END  IF;


IF  1>  
THEN
   statement1;
   …..
ELSIF
   statement3;
   ……
ELSE
  …..
END  IF;
  

Example


BEGIN ...
   IF sales > 50000 THEN bonus := 1500;
   ELSIF sales > 35000 THEN bonus := 500;
   ELSE bonus := 100; END IF;
       INSERT INTO payroll VALUES (emp_id,
           bonus, ...);
END;

  

Posted by OPP(Object Oriented Programming) at 2:22 AM 0 comments
Subscribe to: Posts (Atom)

Vision