PL/SQL

Table Of Contents

Installation
PL/SQL Structure
If then Else Statement
Case Statement
While Loop Statement
For Loop Statement
Select Into
Insert into
Delete
Create Procedure
Create Function
Exception

User Defined Exception

Packages
Copy Table
Insert data from other table
Explicit Cursor
Implicit Cursor

Installation

Install Oracle Express Edition : https://www.oracle.com/id/database/technologies/xe-downloads.html

Install Java SDK : https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

Install Oracle Developer : https://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Create New User:

CREATE USER INVENTORY1 IDENTIFIED BY INVENTORY1;
GRANT CONNECT, RESOURCE, DBA TO INVENTORY1;
GRANT CREATE SESSION, GRANT ANY PRIVILEGE TO INVENTORY1;
GRANT UNLIMITED TABLESPACE TO INVENTORY1;

Create table and record:

CREATE TABLE CUSTOMER
( CUSTOMER_ID NUMBER,
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50),
MIDDLE_NAME VARCHAR2(50),
ADDRESS_LINE1 VARCHAR2(80),
ADDRESS_LINE2 VARCHAR2(80),
CITY VARCHAR2(40),
COUNTRY VARCHAR2(50),
DATE_ADDED DATE,
REGION VARCHAR2(10)
);
CREATE TABLE PRODUCT
( PRODUCT_ID NUMBER,
PRODUCT_NAME VARCHAR2(100),
STANDARD_COST NUMBER(10,2),
COLOR VARCHAR2(20),
LIST_PRICE NUMBER(10,2),
PRODUCT_SIZE NUMBER(8,2),
WEIGHT NUMBER(10,2),
PRODUCT_CATEGORY VARCHAR2(20)
);
CREATE TABLE SALES
( SALES_DATE DATE,
ORDER_ID NUMBER,
PRODUCT_ID NUMBER,
CUSTOMER_ID NUMBER(5,0),
SALESPERSON_ID NUMBER(5,0),
QUANTITY NUMBER(4,0),
UNIT_PRICE NUMBER(10,2),
SALES_AMOUNT NUMBER(10,2),
TAX_AMOUNT NUMBER(10,2),
TOTAL_AMOUNT NUMBER(10,2)
);
CREATE TABLE SALES_HISTORY
( SALES_DATE DATE,
ORDER_ID NUMBER,
PRODUCT_ID NUMBER,
CUSTOMER_ID NUMBER(5,0),
SALESPERSON_ID NUMBER(5,0),
QUANTITY NUMBER(4,0),
UNIT_PRICE NUMBER(10,2),
SALES_AMOUNT NUMBER(10,2),
TAX_AMOUNT NUMBER(10,2),
TOTAL_AMOUNT NUMBER(10,2)
);
CREATE TABLE SALESPERSON
( SALESPERSON_ID NUMBER,
JOB_TITLE VARCHAR2(80),
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50),
MIDDLE_NAME VARCHAR2(50),
ADDRESS_LINE1 VARCHAR2(80),
ADDRESS_LINE2 VARCHAR2(80),
CITY VARCHAR2(40),
COUNTRY VARCHAR2(50),
DATE_ADDED DATE
);
Insert into CUSTOMER
(CUSTOMER_ID,FIRST_NAME,LAST_NAME,MIDDLE_NAME,ADDRESS_LINE1,ADDRESS_LINE2,CITY,
COUNTRY,DATE_ADDED,REGION) values
(10,'JOHN','AMIRTHRAJ','T','2345 PETERDRIVE',null,'Atlanta','USA',
to_date('12-JAN-15','DD-MON-YY'),'SOUTH');
Insert into CUSTOMER
(CUSTOMER_ID,FIRST_NAME,LAST_NAME,MIDDLE_NAME,ADDRESS_LINE1,ADDRESS_LINE2,CITY,
COUNTRY,DATE_ADDED,REGION) values
(11,'TOM','JOSEPH','A','123SANDY DRIVE',null,'New York','USA',
to_date('12-JAN-15','DD-MON-YY'),'SOUTH');
Insert into CUSTOMER
(CUSTOMER_ID,FIRST_NAME,LAST_NAME,MIDDLE_NAME,ADDRESS_LINE1,ADDRESS_LINE2,CITY,
COUNTRY,DATE_ADDED,REGION) values
(12,'PETER','MANN','J','3456 GATES DRIVE',null,'Washington','USA',
to_date('13-JAN-15','DD-MON-YY'),'NORTH');
COMMIT;

Insert into PRODUCT
(PRODUCT_ID,PRODUCT_NAME,STANDARD_COST,COLOR,LIST_PRICE,PRODUCT_SIZE,WEIGHT,
PRODUCT_CATEGORY)
values (100,'Mobile Cover',30,null,35,10,100,'Mobile');
Insert into PRODUCT
(PRODUCT_ID,PRODUCT_NAME,STANDARD_COST,COLOR,LIST_PRICE,PRODUCT_SIZE,WEIGHT,
PRODUCT_CATEGORY)
values (200,'Samsung F7100',80,'BLACK',35,6,50,'Mobile');
Insert into PRODUCT
(PRODUCT_ID,PRODUCT_NAME,STANDARD_COST,COLOR,LIST_PRICE,PRODUCT_SIZE,WEIGHT,
PRODUCT_CATEGORY)
values (105,'HTC 7800',70,'BLACK',80,6,40,'Mobile');
Insert into PRODUCT
(PRODUCT_ID,PRODUCT_NAME,STANDARD_COST,COLOR,LIST_PRICE,PRODUCT_SIZE,WEIGHT,
PRODUCT_CATEGORY)
values (106,'Microsoft Keyboard 7865',50,'BLACK',60,3,20,'Computer');
Insert into PRODUCT
(PRODUCT_ID,PRODUCT_NAME,STANDARD_COST,COLOR,LIST_PRICE,PRODUCT_SIZE,WEIGHT,
PRODUCT_CATEGORY)
values (501,'Microsoft Mouse 7863',30,'GREY',40,1,25,'Computer');
Insert into PRODUCT
(PRODUCT_ID,PRODUCT_NAME,STANDARD_COST,COLOR,LIST_PRICE,PRODUCT_SIZE,WEIGHT,
PRODUCT_CATEGORY)
values (101,'iPhone',500,'GOLD',600,6,20,'Mobile');
COMMIT;

Insert into SALES
(SALES_DATE,ORDER_ID,PRODUCT_ID,CUSTOMER_ID,SALESPERSON_ID,QUANTITY,UNIT_PRICE,
SALES_AMOUNT,TAX_AMOUNT,TOTAL_AMOUNT)
values (to_date('01-FEB-15','DD-MON-YY'),1269,200,12,1000,20,80,1600,160,1760);
Insert into SALES
(SALES_DATE,ORDER_ID,PRODUCT_ID,CUSTOMER_ID,SALESPERSON_ID,QUANTITY,UNIT_PRICE,
SALES_AMOUNT,TAX_AMOUNT,TOTAL_AMOUNT)
values (to_date('01-JAN-15','DD-MON-YY'),1267,100,10,1000,2,20,40,4,44);
Insert into SALES
(SALES_DATE,ORDER_ID,PRODUCT_ID,CUSTOMER_ID,SALESPERSON_ID,QUANTITY,UNIT_PRICE,
SALES_AMOUNT,TAX_AMOUNT,TOTAL_AMOUNT)
values (to_date('01-JAN-15','DD-MON-YY'),1267,101,10,1000,2,30,60,6,66);
Insert into SALES
(SALES_DATE,ORDER_ID,PRODUCT_ID,CUSTOMER_ID,SALESPERSON_ID,QUANTITY,UNIT_PRICE,
SALES_AMOUNT,TAX_AMOUNT,TOTAL_AMOUNT)
values (to_date('02-JAN-15','DD-MON-YY'),1268,100,11,2000,10,30,300,30,330);
Insert into SALES
(SALES_DATE,ORDER_ID,PRODUCT_ID,CUSTOMER_ID,SALESPERSON_ID,QUANTITY,UNIT_PRICE,
SALES_AMOUNT,TAX_AMOUNT,TOTAL_AMOUNT)
values (to_date('09-FEB-15','DD-MON-YY'),1270,105,10,3000,20,70,1400,140,1540);
Insert into SALES
(SALES_DATE,ORDER_ID,PRODUCT_ID,CUSTOMER_ID,SALESPERSON_ID,QUANTITY,UNIT_PRICE,
SALES_AMOUNT,TAX_AMOUNT,TOTAL_AMOUNT)
values (to_date('09-FEB-15','DD-MON-YY'),1270,106,10,3000,10,50,500,50,550);
Insert into SALES
(SALES_DATE,ORDER_ID,PRODUCT_ID,CUSTOMER_ID,SALESPERSON_ID,QUANTITY,UNIT_PRICE,
SALES_AMOUNT,TAX_AMOUNT,TOTAL_AMOUNT)
values (to_date('09-FEB-15','DD-MON-YY'),1270,101,10,3000,10,30,300,30,330);
COMMIT;

Insert into SALES_HISTORY
(SALES_DATE,ORDER_ID,PRODUCT_ID,CUSTOMER_ID,SALESPERSON_ID,QUANTITY,UNIT_PRICE,
SALES_AMOUNT,TAX_AMOUNT,TOTAL_AMOUNT)
values (to_date('01-JAN-15','DD-MON-YY'),1267,100,10,1000,2,20,40,4,44);
Insert into SALES_HISTORY
(SALES_DATE,ORDER_ID,PRODUCT_ID,CUSTOMER_ID,SALESPERSON_ID,QUANTITY,UNIT_PRICE,
SALES_AMOUNT,TAX_AMOUNT,TOTAL_AMOUNT)
values (to_date('01-JAN-15','DD-MON-YY'),1267,101,10,1000,2,30,60,6,66);
Insert into SALES_HISTORY
(SALES_DATE,ORDER_ID,PRODUCT_ID,CUSTOMER_ID,SALESPERSON_ID,QUANTITY,UNIT_PRICE,
SALES_AMOUNT,TAX_AMOUNT,TOTAL_AMOUNT)
values (to_date('07-JAN-15','DD-MON-YY'),1268,100,11,2000,10,30,300,30,330);
COMMIT;

Insert into SALESPERSON
(SALESPERSON_ID,JOB_TITLE,FIRST_NAME,LAST_NAME,MIDDLE_NAME,ADDRESS_LINE1,
ADDRESS_LINE2,CITY,COUNTRY,DATE_ADDED)
values (1000,'South Manager','PETER','MANN','R',
'4567 ASHFORDROAD',null,'CUMMING','USA',to_date('12-JAN-14','DD-MON-YY'));
Insert into SALESPERSON
(SALESPERSON_ID,JOB_TITLE,FIRST_NAME,LAST_NAME,MIDDLE_NAME,ADDRESS_LINE1,
ADDRESS_LINE2,CITY,COUNTRY,DATE_ADDED)
values (2000,'North Manager','JOHN','KING','A','67 DUNWOODY ROAD',null,'LASVEGAS','USA',
to_date('14-JAN-14','DD-MON-YY'));
Insert into SALESPERSON
(SALESPERSON_ID,JOB_TITLE,FIRST_NAME,LAST_NAME,MIDDLE_NAME,ADDRESS_LINE1,
ADDRESS_LINE2,CITY,COUNTRY,DATE_ADDED)
values (3000,'East Manager','BOB','MORIS','A','679 GLENDRIGEDR',null,'FLORIDA','USA',
to_date('14-FEB-14','DD-MON-YY'));
COMMIT

PL/SQL Structure

PL/SQL has a structured syntax that includes the following elements:

DECLARE: This section declares any variables, constants, cursors, and types that will be used in the PL/SQL block.
BEGIN: This section starts the executable part of the block, which contains the code that performs the desired actions.
EXCEPTION: This section handles any errors that might occur during the execution of the block, and specifies what should happen when an error occurs.
END: This section marks the end of the block.

Here is an example PL/SQL block that demonstrates this structure:

DECLARE
/*
multiline comment
*/
  -- Declare any variables or constants here
  my_var VARCHAR2(50) := 'Hello World';
BEGIN
  -- Perform some actions here
  DBMS_OUTPUT.PUT_LINE(my_var);
EXCEPTION
  -- Handle any errors that occur here
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

If then Else Statement

DECLARE
  score NUMBER := 75;
BEGIN
  IF score >= 90 THEN
    DBMS_OUTPUT.PUT_LINE('Your grade is A.');
  ELSIF score >= 80 THEN
    DBMS_OUTPUT.PUT_LINE('Your grade is B.');
  ELSIF score >= 70 THEN
    DBMS_OUTPUT.PUT_LINE('Your grade is C.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Your grade is F.');
  END IF;
END;

Case Statement

DECLARE
  day_of_week VARCHAR2(10) := 'Monday';
BEGIN
  CASE day_of_week
    WHEN 'Monday' THEN
      DBMS_OUTPUT.PUT_LINE('Today is Monday.');
    WHEN 'Tuesday' THEN
      DBMS_OUTPUT.PUT_LINE('Today is Tuesday.');
    WHEN 'Wednesday' THEN
      DBMS_OUTPUT.PUT_LINE('Today is Wednesday.');
    WHEN 'Thursday' THEN
      DBMS_OUTPUT.PUT_LINE('Today is Thursday.');
    WHEN 'Friday' THEN
      DBMS_OUTPUT.PUT_LINE('Today is Friday.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Today is a weekend day.');
  END CASE;
END;

While Loop Statement

DECLARE
  count NUMBER := 1;
BEGIN
  WHILE (count <= 5) LOOP
    DBMS_OUTPUT.PUT_LINE('Count is ' || count);
    count := count + 1;
  END LOOP;
END;

For Loop Statement

DECLARE
  total NUMBER := 0;
BEGIN
  FOR i IN 1 .. 5 LOOP
    total := total + i;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('The total is ' || total);
END;

Select Into

DECLARE
S_DATE SALES.SALES_DATE%TYPE;
S_ORDERID SALES.ORDER_ID%TYPE:=1295;
S_PRODUCTID SALES.PRODUCT_ID%TYPE;
S_CUSTOMERID SALES.CUSTOMER_ID%TYPE;
S_SALESPERSONID SALES.SALESPERSON_ID%TYPE;
S_QUANTITY SALES.QUANTITY%TYPE;
S_UNITPRICE SALES.UNIT_PRICE%TYPE;
S_SALESAMOUNT SALES.SALES_AMOUNT%TYPE;
S_TAXAMOUNT SALES.TAX_AMOUNT%TYPE;
S_TOTALAMOUNT SALES.TOTAL_AMOUNT%TYPE;
BEGIN
SELECT SALES_DATE, ORDER_ID, PRODUCT_ID, CUSTOMER_ID, SALESPERSON_ID, QUANTITY,
UNIT_PRICE, SALES_AMOUNT, TAX_AMOUNT, TOTAL_AMOUNT
INTO
S_DATE, S_ORDERID, S_PRODUCTID,S_CUSTOMERID, S_SALESPERSONID, S_QUANTITY,
S_UNITPRICE, S_SALESAMOUNT, S_TAXAMOUNT, S_TOTALAMOUNT
FROM SALES
WHERE ORDER_ID = S_ORDERID;
 DBMS_OUTPUT.PUT_LINE (S_DATE);
 DBMS_OUTPUT.PUT_LINE (S_ORDERID);
 DBMS_OUTPUT.PUT_LINE (S_PRODUCTID);
 DBMS_OUTPUT.PUT_LINE (S_CUSTOMERID);
 DBMS_OUTPUT.PUT_LINE (S_SALESPERSONID);
 DBMS_OUTPUT.PUT_LINE (S_QUANTITY);
 DBMS_OUTPUT.PUT_LINE (S_UNITPRICE);
 DBMS_OUTPUT.PUT_LINE (S_SALESAMOUNT);
 DBMS_OUTPUT.PUT_LINE (S_TAXAMOUNT);
 DBMS_OUTPUT.PUT_LINE (S_TOTALAMOUNT);
END;

Insert into

DECLARE
S_DATE SALES.SALES_DATE%TYPE:=TO_DATE('01-JAN-2016','DD-MON-YYYY');
S_ORDERID SALES.ORDER_ID%TYPE:=129512;
S_PRODUCTID SALES.PRODUCT_ID%TYPE:=200;
S_CUSTOMERID SALES.CUSTOMER_ID%TYPE:=12;
S_SALESPERSONID SALES.SALESPERSON_ID%TYPE:=1000;
S_QUANTITY SALES.QUANTITY%TYPE:=20;
S_UNITPRICE SALES.UNIT_PRICE%TYPE:=30;
S_SALESAMOUNT SALES.SALES_AMOUNT%TYPE:=600;
S_TAXAMOUNT SALES.TAX_AMOUNT%TYPE:=60;
S_TOTALAMOUNT SALES.TOTAL_AMOUNT%TYPE:=660;
BEGIN
INSERT INTO SALES(SALES_DATE, ORDER_ID, PRODUCT_ID, CUSTOMER_ID, SALESPERSON_ID,
QUANTITY, UNIT_PRICE, SALES_AMOUNT, TAX_AMOUNT, TOTAL_AMOUNT)
VALUES
(S_DATE, S_ORDERID, S_PRODUCTID,S_CUSTOMERID, S_SALESPERSONID, S_QUANTITY,
S_UNITPRICE, S_SALESAMOUNT, S_TAXAMOUNT, S_TOTALAMOUNT);
 
COMMIT;
END;

Update

DECLARE
S_ORDERID SALES.ORDER_ID%TYPE:=129512;
BEGIN
UPDATE SALES SET SALES_AMOUNT = 100
WHERE ORDER_ID = S_ORDERID;
COMMIT;
END

Delete

DECLARE
S_ORDERID SALES.ORDER_ID%TYPE:=129512;
BEGIN
DELETE FROM SALES
WHERE ORDER_ID = S_ORDERID;
COMMIT;
END;

Create Procedure

CREATE PROCEDURE FETCH_SALES (S_ORDERID NUMBER)
AS
L_DATE SALES.SALES_DATE%TYPE;
L_ORDERID SALES.ORDER_ID%TYPE;
L_PRODUCTID SALES.PRODUCT_ID%TYPE;
L_CUSTOMERID SALES.CUSTOMER_ID%TYPE;
L_SALESPERSONID SALES.SALESPERSON_ID%TYPE;
L_QUANTITY SALES.QUANTITY%TYPE;
L_UNITPRICE SALES.UNIT_PRICE%TYPE;
L_SALESAMOUNT SALES.SALES_AMOUNT%TYPE;
L_TAXAMOUNT SALES.TAX_AMOUNT%TYPE;
L_TOTALAMOUNT SALES.TOTAL_AMOUNT%TYPE;
BEGIN
SELECT SALES_DATE, ORDER_ID, PRODUCT_ID, CUSTOMER_ID, SALESPERSON_ID, QUANTITY,
UNIT_PRICE, SALES_AMOUNT, TAX_AMOUNT, TOTAL_AMOUNT
INTO
L_DATE, L_ORDERID, L_PRODUCTID, L_CUSTOMERID, L_SALESPERSONID, L_QUANTITY, L_UNITPRICE,
L_SALESAMOUNT, L_TAXAMOUNT, L_TOTALAMOUNT
FROM SALES
WHERE ORDER_ID = S_ORDERID;
 DBMS_OUTPUT.PUT_LINE (L_DATE);
 DBMS_OUTPUT.PUT_LINE (L_ORDERID);
 DBMS_OUTPUT.PUT_LINE (L_PRODUCTID);
 DBMS_OUTPUT.PUT_LINE (L_CUSTOMERID);
 DBMS_OUTPUT.PUT_LINE (L_SALESPERSONID);
 DBMS_OUTPUT.PUT_LINE (L_QUANTITY);
 DBMS_OUTPUT.PUT_LINE (L_UNITPRICE);
 DBMS_OUTPUT.PUT_LINE (L_SALESAMOUNT);
 DBMS_OUTPUT.PUT_LINE (L_TAXAMOUNT);
 DBMS_OUTPUT.PUT_LINE (L_TOTALAMOUNT);
END;

-- Execute Procedure
exec fetch_sales(1269);

Create Function

CREATE OR REPLACE FUNCTION MY_POWER (N1 IN NUMBER, N2 IN NUMBER)
RETURN NUMBER
AS
POWER_VALUE NUMBER:= 1;
BEGIN
FOR LCNTR IN 1..N2 
LOOP
 POWER_VALUE := POWER_VALUE * N1;
END LOOP;
RETURN POWER_VALUE;
END;

--use the function
SELECT MY_POWER(10,3) FROM DUAL

CREATE OR REPLACE FUNCTION GET_COUNT (S_DATE DATE)
RETURN NUMBER
AS
T_ROWS NUMBER;
BEGIN
SELECT COUNT(1) INTO T_ROWS FROM SALES
WHERE SALES_DATE = S_DATE;
RETURN T_ROWS;
END;

--use the function
SELECT GET_COUNT (TO_DATE ('01-JAN-2015','DD-MON-YYYY')) FROM DUA

Exception

CREATE OR REPLACE PROCEDURE FETCH_SALES (S_ORDERID NUMBER)
AS
L_DATE SALES.SALES_DATE%TYPE;
L_ORDERID SALES.ORDER_ID%TYPE;
L_PRODUCTID SALES.PRODUCT_ID%TYPE;
L_CUSTOMERID SALES.CUSTOMER_ID%TYPE;
L_SALESPERSONID SALES.SALESPERSON_ID%TYPE;
L_QUANTITY SALES.QUANTITY%TYPE;
L_UNITPRICE SALES.UNIT_PRICE%TYPE;
L_SALESAMOUNT SALES.SALES_AMOUNT%TYPE;
L_TAXAMOUNT SALES.TAX_AMOUNT%TYPE;
L_TOTALAMOUNT SALES.TOTAL_AMOUNT%TYPE;
BEGIN
SELECT SALES_DATE, ORDER_ID, PRODUCT_ID, CUSTOMER_ID, SALESPERSON_ID, QUANTITY,
UNIT_PRICE, SALES_AMOUNT, TAX_AMOUNT, TOTAL_AMOUNT
INTO
L_DATE, L_ORDERID, L_PRODUCTID, L_CUSTOMERID, L_SALESPERSONID, L_QUANTITY, L_UNITPRICE,
L_SALESAMOUNT, L_TAXAMOUNT, L_TOTALAMOUNT
FROM SALES
WHERE ORDER_ID = S_ORDERID;
 DBMS_OUTPUT.PUT_LINE (L_DATE);
 DBMS_OUTPUT.PUT_LINE (L_ORDERID);
 DBMS_OUTPUT.PUT_LINE (L_PRODUCTID);
 DBMS_OUTPUT.PUT_LINE (L_CUSTOMERID);
 DBMS_OUTPUT.PUT_LINE (L_SALESPERSONID);
 DBMS_OUTPUT.PUT_LINE (L_QUANTITY);
 DBMS_OUTPUT.PUT_LINE (L_UNITPRICE);
 DBMS_OUTPUT.PUT_LINE (L_SALESAMOUNT);
 DBMS_OUTPUT.PUT_LINE (L_TAXAMOUNT);
 DBMS_OUTPUT.PUT_LINE (L_TOTALAMOUNT);
EXCEPTION
 WHEN no_data_found THEN
 dbms_output.put_line('No such Order!');
 WHEN too_many_rows THEN
 dbms_output.put_line('You got more than 1 row!');
 WHEN others THEN
 dbms_output.put_line('Error!');
END;

User Defined Exception

CREATE OR REPLACE FUNCTION MY_POWER (N1 IN NUMBER, N2 IN NUMBER)
RETURN NUMBER
AS
POWER_VALUE NUMBER:= 1;
EXCEP_ZERO EXCEPTION;
EXCEP_GREAT_100 EXCEPTION;
BEGIN
IF (N1 IS NULL OR N1 = 0 OR N2 IS NULL OR N2 = 0) THEN
 RAISE EXCEP_ZERO;
END IF; 
IF N1 > 100 OR N2 > 100 THEN
 RAISE EXCEP_GREAT_100;
END IF;
 
FOR LCNTR IN 1..N2 
LOOP
 POWER_VALUE := POWER_VALUE * N1;
END LOOP;
RETURN POWER_VALUE;
EXCEPTION
 WHEN EXCEP_ZERO THEN
 dbms_output.put_line('N1 or N2 is null or zero!');
 RETURN 0;
 WHEN EXCEP_GREAT_100 THEN
 dbms_output.put_line('N1 OR N2 is greater than 100!');
 RETURN 0;
 WHEN others THEN
 dbms_output.put_line('Error!');
END

Packages

CREATE OR REPLACE PACKAGE math_utils AS
  FUNCTION add_numbers (x NUMBER, y NUMBER) RETURN NUMBER;
  FUNCTION subtract_numbers (x NUMBER, y NUMBER) RETURN NUMBER;
END math_utils;

CREATE OR REPLACE PACKAGE BODY math_utils AS
  FUNCTION add_numbers (x NUMBER, y NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN x + y;
  END;
  
  FUNCTION subtract_numbers (x NUMBER, y NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN x - y;
  END;
END math_utils;

How to use it:

DECLARE
  x NUMBER := 10;
  y NUMBER := 5;
  result NUMBER;
BEGIN
  result := math_utils.add_numbers(x, y);
  DBMS_OUTPUT.PUT_LINE('The sum of ' || x || ' and ' || y || ' is ' || result);
  
  result := math_utils.subtract_numbers(x, y);
  DBMS_OUTPUT.PUT_LINE('The difference between ' || x || ' and ' || y || ' is ' || result);
END;

Copy Table

CREATE TABLE SALES_COPY AS
SELECT * FROM SALES WHERE 1 = 2;

Insert data from other table

CREATE OR REPLACE PROCEDURE FETCH_SALES (S_ORDERID NUMBER, S_REC OUT
SALES%ROWTYPE)
AS
SALE_REC SALES%ROWTYPE;
BEGIN
SELECT SALES_DATE, ORDER_ID, PRODUCT_ID, CUSTOMER_ID, SALESPERSON_ID, QUANTITY,
UNIT_PRICE, SALES_AMOUNT, TAX_AMOUNT, TOTAL_AMOUNT
INTO
SALE_REC
FROM SALES
WHERE ORDER_ID = S_ORDERID;
S_REC := SALE_REC;
 
EXCEPTION
 WHEN NO_DATA_FOUND THEN
 DBMS_OUTPUT.PUT_LINE('No such Order!');
 WHEN TOO_MANY_ROWS THEN
 DBMS_OUTPUT.PUT_LINE('You got more than 1 row!');
 WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE('Error!');
END;

Procedure fetch_sales will be used in this procedure below:

CREATE OR REPLACE PROCEDURE INSERT_SALES (S_ORD NUMBER)
AS
SLE_REC SALES%ROWTYPE;
BEGIN
FETCH_SALES (S_ORD, SLE_REC);
INSERT INTO SALES_COPY VALUES (
 SLE_REC.SALES_DATE,
 SLE_REC.ORDER_ID,
 SLE_REC.PRODUCT_ID,
 SLE_REC.CUSTOMER_ID,
 SLE_REC.SALESPERSON_ID,
 SLE_REC.QUANTITY,
 SLE_REC.UNIT_PRICE,
 SLE_REC.SALES_AMOUNT,
 SLE_REC.TAX_AMOUNT,
 SLE_REC.TOTAL_AMOUNT
 );
 COMMIT;
END;
--execute the procedure
EXEC INSERT_SALES (1268);

Explicit Cursor

CREATE OR REPLACE PROCEDURE FETCH_SALES_CUR (S_DATE DATE)
AS
 
 CURSOR SALE_CURSOR 
 IS 
 SELECT SALES_DATE, ORDER_ID, PRODUCT_ID, CUSTOMER_ID, SALESPERSON_ID, QUANTITY,
UNIT_PRICE, SALES_AMOUNT, TAX_AMOUNT, TOTAL_AMOUNT
 FROM SALES
 WHERE SALES_DATE = S_DATE;
SALE_REC SALES%ROWTYPE; 
BEGIN
 OPEN SALE_CURSOR;
 
 LOOP
 FETCH SALE_CURSOR INTO SALE_REC; 
 EXIT WHEN SALE_CURSOR%NOTFOUND; 
 DBMS_OUTPUT.PUT_LINE (SALE_REC.SALES_DATE);
 DBMS_OUTPUT.PUT_LINE (SALE_REC.ORDER_ID);
 DBMS_OUTPUT.PUT_LINE (SALE_REC.PRODUCT_ID);
 DBMS_OUTPUT.PUT_LINE (SALE_REC.CUSTOMER_ID);
 DBMS_OUTPUT.PUT_LINE (SALE_REC.SALESPERSON_ID);
 DBMS_OUTPUT.PUT_LINE (SALE_REC.QUANTITY);
 DBMS_OUTPUT.PUT_LINE (SALE_REC.UNIT_PRICE);
 DBMS_OUTPUT.PUT_LINE (SALE_REC.SALES_AMOUNT);
 DBMS_OUTPUT.PUT_LINE (SALE_REC.TAX_AMOUNT);
 DBMS_OUTPUT.PUT_LINE (SALE_REC.TOTAL_AMOUNT);
 END LOOP; 
 CLOSE SALE_CURSOR;
 
END;
EXEC FETCH_SALES_CUR (TO_DATE('01-JAN-2015','DD-MON-YYYY'));

Implicit Cursor

CREATE OR REPLACE PROCEDURE FETCH_SALES_CURLOOP (S_DATE DATE)
AS
BEGIN
FOR SALE_REC IN
 ( SELECT SALES_DATE, ORDER_ID, PRODUCT_ID, CUSTOMER_ID, SALESPERSON_ID, QUANTITY,
UNIT_PRICE, SALES_AMOUNT, TAX_AMOUNT, TOTAL_AMOUNT
 FROM SALES
 WHERE SALES_DATE = S_DATE
 )
LOOP
 DBMS_OUTPUT.PUT_LINE (SALE_REC.SALES_DATE);
 DBMS_OUTPUT.PUT_LINE (SALE_REC.ORDER_ID);
 DBMS_OUTPUT.PUT_LINE (SALE_REC.PRODUCT_ID);
 DBMS_OUTPUT.PUT_LINE (SALE_REC.CUSTOMER_ID);
 DBMS_OUTPUT.PUT_LINE (SALE_REC.SALESPERSON_ID);
 DBMS_OUTPUT.PUT_LINE (SALE_REC.QUANTITY);
 DBMS_OUTPUT.PUT_LINE (SALE_REC.UNIT_PRICE);
 DBMS_OUTPUT.PUT_LINE (SALE_REC.SALES_AMOUNT);
 DBMS_OUTPUT.PUT_LINE (SALE_REC.TAX_AMOUNT);
 DBMS_OUTPUT.PUT_LINE (SALE_REC.TOTAL_AMOUNT);
END LOOP;
 
END;
--run the procedure
EXEC FETCH_SALES_CURLOOP (TO_DATE('01-JAN-2015','DD-MON-YYYY'));

Flash News

Storing Django Static and Media Files on Amazon S3

Install a LAMP Web Server with the Amazon Linux AMI