Oracle Code Examples

DDL

SQL Code Examples

Creating Tables

  • CREATE TABLE ddl statement.
CREATE TABLE customer( 
  cust_no INTEGER NOT NULL,
  cust_name VARCHAR(10) NOT NULL 
);

With Primary Key

  • Primary is nothing but which identify the row in a table.
CREATE TABLE customer( 
  cust_no number(4) PRIMARY KEY,
  cust_name VARCHAR(10) NOT NULL
);

With Check Constraint

  • instead of writing the logic in application program we can enforce a rule as a check constraint at the data base level
CREATE TABLE customer(
  cust_no number(4) constraint pk_customer PRIMARY KEY,
  cust_name VARCHAR(10) NOT NULL,
  credit_limit number(10,2) CHECK (credit_limit BETWEEN 5000 AND 100000),
  state_cd char(2) CHECK (state_cd IN ('TX', 'CA', 'WA')) 
);

Default

  • if you do not specify any value for a particular column then the default value will be inserted in the column on which you define the default constraint
CREATE TABLE customer(
  cust_no number(4) constraint pk_customer PRIMARY KEY,
  cust_name VARCHAR(10) NOT NULL,
  credit_limit number(10,2) CHECK ( credit_limit BETWEEN 5000 AND 100000 ),
  state_cd char(2) CHECK ( state_cd IN ('TX', 'CA', 'WA') ),
  crt_id char(10) DEFAULT user 
);

More than one column as Primary Key

  • this is just an example that we can make more than one column as a primary key.
CREATE TABLE invoice_detail(
  inv_no INTEGER NOT NULL,
  line_no INTEGER NOT NULL,
  item_no INTEGER NOT NULL,
  qty INTEGER NOT NULL,
  constraint pk_inv_detail PRIMARY KEY ( inv_no, line_no) 
);

Foreign Key

  • a foreign key is nothing but referential integrity constraint. You cannot have a value in a foreign key column until you have that value as a primary key column in another table.
CREATE TABLE invoice_detail(
  inv_no INTEGER NOT NULL,
  ling_no INTEGER NOT NULL,
  item_no INTEGER NOT NULL,
  qty INTEGER NOT NULL,
  constraint fk_inv_detail FOREIGN KEY (inv_no) REFERENCES invoice(inv_no),
  constraint pk_inv_detail PRIMARY KEY ( inv_no, line_no) 
);

Alter Table add column

  • used to add a column to the existing table structure. In general alter table command used to chage the attributes of a column like increasing the column length or changing the data type, enabling or disabling the constraints defined on a column.
ALTER TABLE invoice_detail ADD ( discount NUMBER(4,2) );

Alter Table Modify column

  • used to change the attributes of an existing column like increasing the column length or changing the data type.
ALTER TABLE invoice_detail MODIFY ( discount NUMBER(6,2) ) ;

Enable a constraint

  • to enable a constraint on a table.
ALTER TABLE invoice_detail ENABLE CONSTRAINT fk_inv_detail;

Disable a constraint

  • to disable a constraint. usually you do this when you load the bulk data into the table.
ALTER TABLE invoice_detail DISABLE CONSTRAINT fk_inv_detail;

Drop table with cascade

  • when you drop a table which has columns referred by another table then you will get the error which says reference value exists in another table so you cannot drop the table, at that time we can use this cascade so that it actually deletes the rows in child table and then drops the table.

Drop a table

  • once you drop a table all the constraints and triggers will get automatically dropped, if there are any stored procedure references this table those will become invalid status.
DROP TABLE invoice;

Create Index

  • index is used to retrieve the data faster.
CREATE INDEX ind_custname ON customer(cust_name);

Create unique index

  • we cannot have two primary keys on a table where as we can have as many unique indexes on a table. (some times we need to have unique values in a column or more other than primary key to enforce the business rules.)
CREATE UNIQUE INDEX ind_lgcy_cust_code ON customer(cust_old_code);

Creating a structure of one table to another

  • the select statement will return 0 rows because the where condition is not true
CREATE TABLE customer_copy AS SELECT * FROM customer WHERE 1 = 2;

Creating a copy of a table with another name

  • usually to create the copy of a table including the rows. ( just to make a copy )
CREATE TABLE customer_copy1 AS SELECT * FROM customer;

Creating Sequence

  • sequence is used to generate the seq numbers. used in multi user environment where there are many inserts
CREATE SEQUENCE cust_seq
START WITH 1
INCREMENT BY 1
CACHE 50
NOCYCLE;

Creating Sequence increment by 2

  • We can increment the seq number by any number
CREATE SEQUENCE office_seq
START WITH 1000
INCREMENT BY 2
CACHE 50
NOCYCLE;

Creating Synonym

  • This is called private synonym. Only who has permission on the private synonym.
CREATE SYNONYM employee FOR david.employee;

Creating Public Synonym

  • Public Synonym can be seen by all the users.
CREATE PUBLIC SYNONYM department FOR david.department;

DML

DML Statements

Select all columns

  • just to show how we can select all the columns from a table. We should never use this type of select statement in the application development.
SELECT * FROM course;
SELECT * FROM students;
SELECT * FROM customers;  -- * represents all the columns from a select statement.

Select particular columns

  • this is the way we should write select statements. Even though you are selecting all the columns its better to specify all the column names respectively.
SELECT course_id, course_nm FROM course;
SELECT student_id, student_nm FROM students WHERE course_nm = 'JavaIII'

Concatinating two strings in SQL

  • is handy while we need to show some extra information or user friendly text. following sql will give the result in readable format ie like Mike lives in Dallas.
SELECT student_nm || ' lives in ' || city FROM students;
SELECT course_nm || ' is taught by ' || instructor_name FROM course WHERE course_id = 88;

Using Psedocolumns

  • is a column which does not exists in the table. rowid and rownum are not columns but we can select from the table, that is called psedocolumns.
SELECT office_seq.NEXTVAL FROM dual;
SELECT rowid, cust_no FROM customer WHERE state = 'TX';
SELECT rownum, cust_no FROM customer WHERE state = 'CA';

Using DECODE

  • if then else in the select statment. If state_cd iis TX then display Texas, else if CA then California else if WA Washington and goes on like this.
SELECT cust_no, cust_name, DECODE (state_cd, 'TX', 'Texas',
                                              'CA', 'California',
                                              'WA', 'Washington')
FROM customer
WHERE credit_limit > 20000;

Using NVL

  • is used to check if the column is null, if so send back the second argument as the value
SELECT cust_no, cust_name, NVL ( TO_CHAR(credit_limit) , 'No Credit for this customer ' )
FROM customer
WHERE state_cd = 'TX'

group by

  • used while calculation aggregate functions or group by functions. In the following example we are trying to find the number of students by course, so we are grouping the rows by course name and using count function to get the number of rows in each group. all the aggregate functions will be based on the grouped rows respectively.
SELECT course.course_name, count(*)
FROM course, student
WHERE course.course_id = student.course_id
GROUP BY course_name;

SELECT course.course_name, start_date, count(*)
FROM course, student
WHERE course.course_id = student.course_id
GROUP BY course.course_name, start_date;

group by having

  • is nothing but a where clause on a grouped rows
SELECT course.course_name, city_nm, count(*)
FROM course, student
WHERE course.course_id = student.course_id
GROUP BY course_name, city_nm
HAVING COUNT(*) > 10;

Sysdate (Date Functions)

  1. Select sysdate from dual;
  2. Select to_char(sysdate, 'month-day-yyyy') from dual;
  3. Select to_char(sysdate, 'dd-mm-yyyy:hh:mi:ss') from dual;
  4. Select sysdate + 25 from dual;
  5. Select sysdate - 20 from dual;
  6. Select add_months ( sysdate, 4) from dual;
  7. Select months_between ( sysdate, to_date('10-JAN-1978', 'DD-MON-YYYY') );

Range Queries

  • to get the rows which falls in the small or wide range of values.
SELECT cust_no, cust_name, state_cd
FROM customer
WHERE cust_no BETWEEN 100 AND 250

Sub Query

  • Inner most query actually executes the select and returns the rows to the outer select i.e the inner select results sets will become the filter clause for the outer query.
SELECT cust_no, cust_name
FROM customer
WHERE invoice_no in (
  SELECT invoice_no
  FROM invoice_detail
  WHERE state_cd = 'TX'
  GROUP BY invoice_no
  HAVING COUNT(*) > 5 
);

Correlated Sub Query

  • In correlated sub query the outer select executes first then for every row retireived the inner select will get executed. So here we are trying to see in what courses there are no students. So we are using not exits in the where clause, if the inner query does not have any row which means there is no student for the course the outer select will display the row.
SELECT course_nm, 'No Students for this course'
FROM course
WHERE not exists
  ( SELECT 1 FROM students WHERE student.course_id = course.course_id );

Left Outer Joins

  • Retrieving the matching and un matching rows.
SELECT course_nm, count(*)
FROM course, students
WHERE course.course_id = student.course_id(+)
GROUP BY course_nm

Right Outer Joins

  • Retrieving the matching and un matching rows.
SELECT course_nm, count(*)
FROM course, students
WHERE course.course_id(+) = student.course_id
GROUP BY course_nm;

Like statement

  • if you know starting characters of a name, or partial name then we can use like clause to retrieve the row from the table. ( If you call a doctor to get an appointment if you just say starting letters of your first or last name they can pull your record )
SELECT cust_nm FROM customer WHERE cust_name LIKE 'ABC%'

AND, OR, IN, NOT IN

  • just some filtering rows
SELECT course_nm
FROM courses, locations
WHERE courses.course_id = locations.course_id AND locations.state_cd = 'TX';

SELECT student_id, student_name FROM students
WHERE state_cd = 'TX' OR state_cd = 'OK';

SELECT student_id, student_name FROM students
WHERE state_cd IN ( 'TX', 'OK' );

SELECT student_id, student_name FROM students
WHERE state_cd NOT IN ( 'GA', 'NY' );

Table Aliases

  • using table aliases. Here for course we call that as table alias a and for instructor table we are calling it as b. Usually its very handy to make your sql more readable and if the table names are longer then you dont need to type whole table name, instead specify a short alias name. When you are writing self join we must use the table alias.
SELECT a.course_id, b.instructor_name
FROM course a, instructors b
WHERE a.instructor_id = b.instructor_id;

Self Join

SELECT a.employee_name, a.emp_salary, b.manager_name
FROM employee a, employee b
WHERE b.employee_number = a.manager_number;

Distinct

  • Suppose we want to display the manager names from the employee tables then we can use distinct so that we wont get duplicate rows.
SELECT distinct manager_number FROM emloyee.

Insert into Syntax1 (Single row insert)

  • If you know all the column values
INSERT INTO course
VALUES
( course_seq.NEXTVAL, 'Oracle', user, sysdate )

Insert into Syntax2

  • if you know the partial values ( at least all NOT NULL columns )
INSERT INTO course
( course_id, course_nm, crt_id, crt_date )
VALUES
( course_seq.NEXTVAL, 'SQL Server', user, sysdate);

Insert into Select... (Multi row insert)

  • usually its insert into a select statement syntax. If the select returns 0 rows then the insert inserts 0 rows, if one or more it inserts the selected rows into the table.
INSERT INTO course
SELECT * FROM course_old

Update table

  • Changing the value in a particular column in one or more rows
UPDATE course
SET course_nm = 'Java'
WHERE course_id = 8

Update with a sub query

  • Sub query returns the courses in Dallas and then change the course name for those courses
UPDATE course
SET course_nm = 'JavaII'
WHERE course_id in ( SELECT course_id FROM locations WHERE location = 'Dallas')

Delete a row

  • Removing one row from a table
DELETE course WHERE course_id = 23

Delete multiple rows

  • Removing one or more rows from a table
DELETE course WHERE course_nm = 'Oracle'

Truncate the table

  • deleting all the rows from a table. The delete trigger wont fire if you use this statement.
TRUNCATE TABLE course

DCL

Data Control Language

Create an Oracle User

  • once you create an user the user can login to the database after granting resource to that user. There are many types of grants you can assign to the users. When we create the user we have to specify the password after that user can chage the password at any time.
CREATE USER jon IDENTIFIED BY monday

Create a Role

  • A role is an oracle object where we can grant a set of permissions as a collection to a particular job. Example for all customer representatives we can have a role, when a person joins the company we can assign the role instead of granting permissions to each table . It easy to maintenance.
CREATE ROLE custinfo;
GRANT SELECT ON customer, cust_address, cust_hist....

Create a Role

  • In the previous example we created the role and granted set of permissions on some objects. Now we will write a DCL to assign that role to the user. You can assign any number of roles to the user
GRANT custinfo TO jon

PL/SQL Code Examples

Example1

  • just a feel of a pl/sql block. Adding 2 numbers and displaying the result
DECLARE
   var_num1 number(4);
   var_num2 number(4);
   result number(6);
BEGIN
   var_num1 := 15;
   var_num2 := 30;
   result := var_num1 + var_num2;
   dbms_output.put_line ( result );
END;

Example2

  • Displaying numbers 1 to 50 using a Loop simple
DECLARE
  var_running_val number(2);;
BEGIN
  var_running_val := 0;
  LOOP
        var_running_val := var_running_val + 1;
        Dbms_output.put_line ( var_running_val );
        IF var_running_val >= 50 THEN
           EXIT;
        END IF;
  END LOOP;
END;

Example 3

  • getting feel of PL/SQL writing sql inside the pl/sql block
DECLARE --where you declare the variables used in this block.
      var_custname VARCHAR(20);
      var_custno NUMBER(4);
BEGIN
      var_custno := 1234; --Assigning a value to the variable.
      --embedded select statement to get the customer name by passing the customer number.
      SELECT cust_name INTO var_custname
      FROM customer WHERE cust_no = var_custno;
      --display the customer name using dbms_ouput package
      dbms_output.put_line ( var_custname );
EXCEPTION
      WHEN no_data_found THEN
              -- Handle the no data found exception
               raise_application_error ( -20001, 'No data found for customer no 1234');
END;

Example 4

  • Writing a loop which calls the a procedure so many times by passign different arguments. we take an example, a stored procedure which takes the date as an argument and it summarizes the all the transaction level data for the reports.
DECLARE
      v_run_date DATE;
BEGIN
      v_run_date := '01-JAN-01';
      LOOP
             IF v_run_Date >= '01-FEB-01' then
                  EXIT;
             END IF;
             dbms_output.put_line( v_run_Date );
             v_run_date := v_Run_date + 1;
      END LOOP;
END;

RECORD datatypes

  • declaring the record data type and assign the values for the fields
DECLARE
   TYPE courserec is RECORD(
   course_id course.course_id%type,
   course_nm course.course_nm%type);
   course_layout courserec;
BEGIN
   course_layout.course_id := 35;
   course_layout.course_nm := 'Oracle';
END;

Cursor

  • how to declare a cursor, open , fetch and close cursor
DECLARE
      CURSOR c_get_customers ( state_id char ) IS
       SELECT customer_no FROM customer WHERE state = state_id;
        l_cust_no customer.customer_no%type;
BEGIN
    Open c_get_Customers('TX');
    LOOP
          FETCH c_get_Customers INTO l_cust_no;
          EXIT when c_get_customers%notfound;
          dbms_output.put_line ( l_cust_no);
    END LOOP;
    CLOSE c_get_customers;
END;

  • SavePoints

  • Commit and rollback

  • Exceptions (Pre defined Exception )

  • In this example we are handling the no_Data_found exception (one of the pre defined exception)

  • when a select statement does not return any row in a pl/sql block then oracle raises a pre defined exception called no_data_found. If you dont handle the exception your program terminates when that exception occurs.

DECLARE --NO_DATA_FOUND_EXCEPTION
    var_cust_no customer.customer_no%type;
    var_cust_no_hold customer.customer_no%type;
    var_cust_name_hold customer.customer_name%type;
BEGIN
    var_cust_no := &Customer_Number;
    SELECT
    customer_no, customer_name into
    var_cust_no_hold, var_cust_name_hold
    FROM customer
    WHERE customer_no = var_cust_no;
    dbms_output.put_line ( 'Customer Name is ' || var_cust_name_hold );
EXCEPTION
    WHEN no_data_found THEN
     dbms_output.put_line ( 'Customer Number does not exists in the database  ');
END;

User Defined Exception

  • here we are declaring too_many_students variable of type exception
  • You must raise the exception in your pl/sql block processing when your business rule fails.
DECLARE
   too_many_students exception;
   var_kount number;
   var_course_id number;
BEGIN
   var_course_id :=  &Course;
   select count(*) into var_kount from students where course_id = 50;
   IF var_kount > 20 THEN
      raise too_many_students;
  End IF;
EXCEPTION
  WHEN too_many_students thTHENen
      dbms_output.put_line ( 'Time to create a new class schedule, Crossing the dead line for course id ' || var_course_id );
END ;

raise_application_error

  • if you want to terminate the process we can raise an application error using Raise_application_error. When we use this statement in triggers all the data manipulation gets rollback automatically, the error text and number will appear to the called programs
DECLARE
    var_credit_limit number(7, 2);
    var_customer customer.customer_no%type;
    var_ret_value number(2);
BEGIN
    var_customer := &Customer;
    var_ret_value := check_customer_eligible_for_credit ( var_customer );
    IF var_ret_value < 0 then
       Raise_Application_Error ( -20001, 'Customer  ' || var_customer || ' is not eligible for the Credit ' );
   END IF;
END;

Stored Procedure

  • Its a pl/sql block with a name and can pass arguments and more over you can save that as a database object in the database. The advantage is when we create a stored procedure it also saves the execution plan, so when we are calling this stored procedure again it uses the existing plan so that it is not wasting any time to figure out the execution plan.
  • in the following stored procedure we update the credit limit for a customer by passing the credit limit and customer number, if any error then pass the sql code and sql error desc to the calling program
CREATE PROCEDURE update_customer_credit
( v_cust_no IN customer.customer_no%type,
  v_amount IN customer.credit_limit%type,
  v_sqlcode IN OUT number,
  v_sqlerrtext IN OUT VARCHAR ) is
BEGIN
   update customer set credit_limit = v_amount where customer_no = v_cust_no;
EXCEPTION
   WHEN others THEN
       v_sqlcode := SQLCODE;
       v_sqlerrtext := SQLERRM;
END update_customer_credit;

Stored Function

--write a function which converts the local time to GMT time. In this function we use a oracle date function named new_time which takes 3 arguments, first argument is the date you want to convert, second is from which time and lastly to which time you want to convert. You must return a value from the function.

Create function convert_time_from_zone_to_zone
( var_local_time date,
  var_from VARCHAR,
  var_destination VARCHAR  )  return date is
var_gmt_value date;
BEGIN
     var_gmt_value := new_time ( var_local_time, var_from ,var_destination );
     return var_gmt_value;
END convert_time_from_zone_to_zone;

Calling Function from a PL/SQL block

  • Here in this example we are converting Eastern Standard to Greenwich Mean Time. Just call the function by passing all the arguments expected in any sql.
SELECT convert_time_from_zone_to_zone ( sysdate, 'EST','GMT') FROM dual;
  • Global Variable (sort of)
  • PL/SQL Table
  • REF Cursor
  • Create Triggers
  • Row Level Trigger
  • Statement Level Trigger
  • Mutuating

You can create a temporary tables in your select statements. You can write a select statement and call that as table a, create another select statement call that as table b and join those two temp tables for your result.

SELECT a.col1, a.col2, a.col3, b.col1, b.col2
FROM (SELECT col1, col2, col3 FROM table1 WHERE col1 = 123 ) a,
        (SELECT col1, col2, col3 FROM table2 WHERE col1 = 345 ) b
WHERE a.col1 = b.col1;

In the above example the first select statement results is nothing but a temporary table a and the next one is called b and then join those two temp tables to get your selects.