CseWay

A Way For Learning

Showing posts with label pl/sql. Show all posts
Showing posts with label pl/sql. Show all posts

Triggers : PL-SQL

Triggers are used to add restrictions to data entered into the tables.It is used to store the details if there are any changes made in fields of a table.By using triggers we can know where the change has been happened ,what was the value that was changed and who changed it.

Triggers are used on the DML statements insert,update and delete. Triggers can be applied either before or after the change.

6 Types of Triggers:
  1. AFTER INSERT
  2. BEFORE INSERT
  3. AFTER DELETE
  4. BEFORE DELETE
  5. AFTER UPDATE
  6. BEFORE UPDATE
Example :
The following program

CREATE OR REPLACE TRIGGER T1
BEFORE INSERT ON TICKET
FOR EACH ROW
BEGIN
IF(:NEW.JOURNEY_DATE<SYSDATE) THEN
RAISE_APPLICATION_ERROR(-20001,'Invalid Date');
END IF;
END;

 Log or Audit tables for triggers:
These tables are used to store the information about the changes they are taken place in the table.One can store the user name,the old and new values in the table while executing the triggers.
Example
 Log table
CREATE TABLE LOG(      USERNAME VARCHAR2(20),OLDVALUE VARCHAR 2(20),NEWVALUE VARCHAR2(20)   );


Log table in trigger
CREATE OR REPLACE TRIGGER TRIG_NAME
before update or delete on TABLE_NAME for each row                                                DECLARE                                                                                                                                 T_USERNAME VARCHAR2(20)
begin
if deleting or updating then                                                                                                   SELECT USER INTO T_USERNAME FROM DUAL;
  insert into comment_audit(
    USERNAME, OLDVALUE,NEWVAL
  )
  values (                                                                                                                                     T_USERNAME,
    :old.COLUMN_NAME,
    :NEW.COLUMN_NAME
  );
end if;
end;
Read More

PL-SQL : View

View plays an important role for displaying the results of the queries. View is a representation of another table or combinations of tables.We can get the required type of display by using views.
The table from which data is derived to view is called BASE TABLE. Base tables can be either the original tables in the database or view tables.

Any changes made in the view will also effect the view of the base table..View are use to join multiple tables and display the required output view.

SYNTAX of view :
CREATE OR REPLACE VIEW view_name AS
  SELECT columns
  FROM table
  WHERE conditions;
In the above syntax 'REPLACE' is optional because if a view is already created with that name,it will replace the older one with the new one so that we will not get errors.

To drop the view syntax is:
DROP VIEW view_name;

 Materialized Views : A Materialized view is a sub table of a given table or combination tables.One cannot insert or delete the data,but can drop the view.
This view is the replica of the original table.This view has high performance.
Syntax :
CREATE OR REPLACE MATERIALIZED VIEW view_name AS
  SELECT columns
  FROM table
  WHERE conditions;


Read-Only View : This view can only be view and one cannot apply and any DML statements on this view.
Syntax of read-only view:
CREATE OR REPLACE VIEW view_name AS
  SELECT columns
  FROM table
  WHERE conditions
  WITH READ ONLY;

Uses of view :
  • Performance is faster by using views.
  • Complexity of the SQL statements can be reduced.
  • Hiding of data is possible ie Security
  • Change the column names 



Read More

Differences between SQL and PL-SQL

SQL is a query language that allows you to issue a single query or execute a single insert/update/delete. PL-SQL is Oracle's "Programming Language" SQL, which allows you to write a full program (loops, variables, etc.) to accomplish multiple selects/inserts/updates/deletes. You should learn SQL first, then move on to PL-SQL. You will probably need both to be an Oracle DBA.

1.) SQL is a data oriented language for selecting and manipulating sets of data. PL/SQL is a procedural language to create applications.

2.) PL/SQL can be the application language just like Java or PHP can. PL/SQL might be the language we use to build, format and display those screens, web pages and reports.SQL may be the source of data for our screens, web pages and reports.

3.) SQL is executed one statement at a time. PL/SQL is executed as a block of code.

4.) SQL tells the database what to do (declarative), not how to do it. In contrast, PL/SQL tell the database how to do things (procedural).

5.) SQL is used to code queries, DML and DDL statements. PL/SQL is used to code program blocks, triggers, functions, procedures and packages.

6.) We can embed SQL in a PL/SQL program, but we cannot embed PL/SQL within a SQL statement.

For PLSQL code to be excute you should use the following command :
SET SERVEROUTPUT ON;

Difference between SQL and PL/SQL:-

SQL is a Structured Query Language used to issue a single query or execute a single insert/update/delete.
PL-SQL is a  programming language SQL, used to write full programs using variables, loops,operators etc. to carry out multiple selects/inserts/updates/deletes.

SQL may be considered as the source of data for our reports, web pages and screens.
PL/SQL can be considered as the application language similar to  Java or PHP. It might be the language used to build, format and display those reports, web pages and screens.

SQL is a data oriented language used to select and manipulate sets of data.
PL/SQL is a procedural language used to create applications.

SQL is used to write queries, DDL and DML statements.
PL/SQL is used to write program blocks, functions, procedures triggers,and packages.

SQL is executed one statement at a time.
PL/SQL is executed as a block of code.

SQL is declarative, i.e., it tells the database what to do but not how to do it.
Whereas, PL/SQL is procedural, i.e., it tells the database how to do things.

SQL can be embedded within a PL/SQL program.
But PL/SQL can’t be embedded within a SQL statement.
Read More
,
Subscribe to: Posts ( Atom )