Oracle-with-examples.com 

-> Oracle SQL & PL/SQL

Oracle SQL & PL/SQL

     From one point of view in Oracle there are 2 types of SQL  command: there are manipulating commands (INSERT, UPDATE, DELETE) and the SELECT command. SELECT is used to see the date (in a formatted or unformatted mode) we have in the Oracle database, while the INSERT, UPDATE, DELETE statements are used to insert, modify or delete the date in/from our database. 

     Here you can see how a select can be done:

SELECT job, ename FROM dept NATURAL JOIN emp;

SELECT job, ename FROM scott.dept, scott.emp where emp.DEPTNO = dept.DEPTNO ;

SELECT job, ename, dept.DNAME FROM scott.dept, scott.emp where emp.DEPTNO = dept.DEPTNO and ename = 'SMITH';

Here you can see more SELECT statements.

An UPDATE is done like this:

UPDATE <table_name>
    SET <column_name> = <value>
WHERE <column_name> <condition> <value>;

A DELETE is done like this:

DELETE <table_name>
WHERE <column_name> <condition> <value>;

      An INSERT is done like this:

      INSERT INTO <table_name> (<column_name>)
                  VALUES   (<value>);

     However the SQL is not limited to the SELECT, INSERT, DELETE, UPDATE commands. The following commands are SQL commands as well:  CALL, CREATE CLUSTER, ALTER CLUSTER, DROP CLUSTER, COMMENT, COMMIT, CONNECT,CREATE CONTEXT, DROP CONTEXT, CREATE CONTROLFILE, CREATE DATABASE, ALTER DATABASE,
DESCRIBE, CREATE DIMENSION, ALTER DIMENSION, DROP DIMENSION, CREATE DIRECTORY, DROP DIRECTORY, EXEC, EXECUTE IMMEDIATE, EXPLAIN PLAN, CREATE FUNCTION, ALTER FUNCTION, DROP FUNCTION, GRANT, CREATE INDEX, ALTER INDEX, DROP INDEX, CREATE INDEXTYPE, etc.

    As you can see the SQL is a languge created to manipulate data, to see the data in the database and also to create, alter or drop the database structures, connections, etc.

    Whe you have to create procedures (stored in the database or not), you have to use PL/SQL (Procedural Language / SQL). In the PL/SQL you can include SQL commands, and also programming code like in C++, FoxPro, Pascal, .Net, etc. However PL/SQL language has his own commands and syntax.

    Here is a PL/SQL example:

declare
      vnu_number1    number;
      vnu_number2    number(2);

      cursor c1 is
          select 26 NB from dual;
 

begin
      for i in c1 loop
           vnu_number2 := i.NB;
      end loop;

      if vnu_number2 = 26 then
            vnu_number1 := 1;
      else
            vnu_number1 := 0;
      end if;

      dbms_output.put_line('vnu_number1= '||vnu_number1);
end;

Oracle-with-examples.com 

-> Oracle SQL & PL/SQL