Oracle SQL Statment to create user, grant permission , Creating Table, views, Trigger and Procedure

Reference:- http://www.techonthenet.com/oracle/schemas/create_schema.php

=======Table Space
#1 - PERMANENT TABLESPACE
CREATE BIGFILE TABLESPACE tbs_perm_siddhu_01
  DATAFILE 'tbs_perm_siddhu_01.dat'
    SIZE 10M
    AUTOEXTEND ON;

===DROP TABLESPACE tbs_01 
INCLUDING CONTENTS 
CASCADE CONSTRAINTS; 

#2 - TEMPORARY TABLESPACE

CREATE TEMPORARY TABLESPACE tbs_temp_siddhu_01
  TEMPFILE 'tbs_temp_siddhu_01.dbf'
    SIZE 5M
    AUTOEXTEND ON;

#3 Creating User called siddhu/siddhu

CREATE USER siddhu
  IDENTIFIED BY siddhu
  DEFAULT TABLESPACE tbs_perm_siddhu_01
  TEMPORARY TABLESPACE tbs_temp_siddhu_01
  QUOTA 20M on tbs_perm_siddhu_01;

#4 Assigning Role to the user siddhu

GRANT create session TO siddhu;
GRANT create table TO siddhu;
GRANT create view TO siddhu;
GRANT create any trigger TO siddhu;
GRANT create any procedure TO siddhu;
GRANT create sequence TO siddhu;
GRANT create synonym TO siddhu;  


--From here we can login to the oracle db with user siddhu


#5 Create Table
CREATE TABLE customers
( customer_id number(10) NOT NULL,
  customer_name varchar2(50) NOT NULL,
  address varchar2(50),
  city varchar2(50),
  state varchar2(25),
  zip_code varchar2(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

#6 Create View

CREATE VIEW view_customers AS
  SELECT customers.customer_id, customers.customer_name, customers.city
  FROM customers
  WHERE customers.customer_name like '%siddhu%';


#7 Grant Object Privilage

GRANT ALL ON  customers TO siddhu;  

#7 CREATE SYNONYMS FOR OBJECTS

CREATE PUBLIC SYNONYM customers
FOR siddhu.customers;

-- Now we can execute select queries direcly on customers i.e. SELECT * FROM customers; without giving schema names.



#8 Delete all rows of the Table
DELETE FROM customers;

#8 Alter table
alter table
   customers
add
   salary  NUMBER NOT NULL;

#8 Trigger

CREATE OR REPLACE TRIGGER trigger_siddhu
BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN (NEW.CUSTOMER_ID > 0)
DECLARE
   sal_diff NUMBER;
BEGIN
   sal_diff := :NEW.salary  - :OLD.salary;
   dbms_output.put_line('Old salary: ' || :OLD.salary);
   dbms_output.put_line('New salary: ' || :NEW.salary);
   dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/


#10 Procedure
CREATE OR REPLACE PROCEDURE adjust_salary(
    new_customer_id IN NUMBER,
    salary IN NUMBER
) IS
BEGIN
 IF salary > 7000 
THEN 
UPDATE customers
   SET salary = '7007'
   WHERE customer_id = new_customer_id;
END IF;
END;