javafaqs-edu.blogspot.com

oraclefaqs-edu.blogspot.com

Oracle Tutorial: Oracle PL/SQL Varrays

http://oraclefaqs-edu.blogspot.com/2008/12/oracle-plsql-varrays.html

This blog contains the Oracle concepts like sql commands,stored procedures,pl/sql,cursors,functions and triggers. These can be stored in the columns of your tables. When you create them you must provide the maximum size for them. These are dense and Not sparse, which means there is no way to delete individual elements of a varrays. Create type project work type as varray(20) of varchar2(30);. Create table student projects(. Each student can have upto 20 projects. Projects project work type,. T1 id number,.

oraclefaqs-edu.blogspot.com

Oracle Tutorial: Oracle PL/SQL Nested Tables

http://oraclefaqs-edu.blogspot.com/2008/12/oracle-plsql-nested-tables.html

This blog contains the Oracle concepts like sql commands,stored procedures,pl/sql,cursors,functions and triggers. Oracle PL/SQL Nested Tables. These are similar to index by table but these can be stored in database columns but index by tables cannot be stored in database columns. Can only add or delete elements from the end of the array. Nested Table. Is known as a sparse collection because a nested table can contain empty elements. Are a superior choice when:. The index values are not consecutive. 8 num...

oraclefaqs-edu.blogspot.com

Oracle Tutorial: Examples of Oracle PL/SQL Cursors

http://oraclefaqs-edu.blogspot.com/2008/12/examples-of-oracle-plsql-cursors.html

This blog contains the Oracle concepts like sql commands,stored procedures,pl/sql,cursors,functions and triggers. Examples of Oracle PL/SQL Cursors. Examples of Oracle PL/SQL Cursors:. Cursor c 1 is. Select * from emp;. For emp rec in c 1 loop. Dbms output.put line(emp rec.ename);. Create or replace procedure sum of salary IS. Cursor c 1 is. Select * from emp;. Salary sum integer;. Salary sum := 0;. For emp rec in c 1 loop. Salary sum := salary sum emp rec.sal;. CREATE OR REPLACE PROCEDURE my proc IS.

oraclefaqs-edu.blogspot.com

Oracle Tutorial: Oracle PL/SQL Cursor For Loops

http://oraclefaqs-edu.blogspot.com/2008/12/oracle-plsql-cursor-for-loops.html

This blog contains the Oracle concepts like sql commands,stored procedures,pl/sql,cursors,functions and triggers. Oracle PL/SQL Cursor For Loops. The following procedure is followed in most of the situations in PL/SQL:. Check whether rows are returned. Allows us to simplify this procedure by letting PL/SQL do most of the things for us. Let us rewrite the previous example (used in Cursors with Parameters) again using Cursor FOR loop. Using a cursor FOR loop. CURSOR cur1 dept IS SELECT deptno, dname.

oraclefaqs-edu.blogspot.com

Oracle Tutorial: SQL Cross Join

http://oraclefaqs-edu.blogspot.com/2008/12/sql-cross-join.html

This blog contains the Oracle concepts like sql commands,stored procedures,pl/sql,cursors,functions and triggers. You will be able to find Cartesian product with a Cartesian join. When we join every row of a table to every row of another table we get Cartesian join. Where every row from one table is matched with every row from another. Are one and the same thing. If T1 and T2 are two sets then cross join = T1 X T2. Examples of a cross join:. FROM emp CROSS JOIN dept. FROM emp, dept;.

oraclefaqs-edu.blogspot.com

Oracle Tutorial: Oracle PL/SQL Explicit Cursors

http://oraclefaqs-edu.blogspot.com/2008/12/oracle-plsql-explicit-cursors.html

This blog contains the Oracle concepts like sql commands,stored procedures,pl/sql,cursors,functions and triggers. Oracle PL/SQL Explicit Cursors. Are created by the programmer, and with these you can do operations on a set of rows, which can be processed one by one. You use explicit cursors. When you are sure that the SQL statement will return more than one row. You have to declare an explicit cursor. In the declare section at the beginning of the PL/SQL. Will go through these steps:. 14 End loop;. It re...

oraclefaqs-edu.blogspot.com

Oracle Tutorial: PL/SQL Triggers

http://oraclefaqs-edu.blogspot.com/2008/12/plsql-triggers.html

This blog contains the Oracle concepts like sql commands,stored procedures,pl/sql,cursors,functions and triggers. A database trigger is a block of code that is automatically executed in response to certain events. Triggers are executed implicitly whenever the triggering event happens. The triggering event is either a INSERT, DELETE, or UPDATE command. The timing can be either BEFORE or AFTER, INSTEAD OF trigger. Example of creating a trigger based on the following two tables:. CREATE TABLE T1 (a INTEGER);.

oraclefaqs-edu.blogspot.com

Oracle Tutorial: Oracel PL/SQL Tables

http://oraclefaqs-edu.blogspot.com/2008/12/oracel-plsql-tables.html

This blog contains the Oracle concepts like sql commands,stored procedures,pl/sql,cursors,functions and triggers. These are one dimensional arrays and are indexed by integers. Type my first table is table of varchar2(10). Index by binary integer;. Var of table my first table;. Var of table(1) := ‘hello world’. Var of table(2) := ‘bye’. Type my emp table is table of emp%rowtype. Index by binary integer:. Var of emp my emp table;. Var1 of emp my emp table;. Var of emp(1).ename := ‘sachin’. 13 End loop;.

oraclefaqs-edu.blogspot.com

Oracle Tutorial: SQL Outer Join

http://oraclefaqs-edu.blogspot.com/2008/12/sql-outer-join.html

This blog contains the Oracle concepts like sql commands,stored procedures,pl/sql,cursors,functions and triggers. The purpose of an outer join is to include non-matching rows, and the outer join returns these missing columns as NULL values. A LEFT OUTER JOIN. Results in a table which includes joined rows and any unmatched rows from the table listed to the left. The keyword LEFT in a LEFT OUTER JOIN. A RIGHT OUTER JOIN. Is just reverse of LEFT OUTER JOIN. An outer join extends the result of a simple join&...

oraclefaqs-edu.blogspot.com

Oracle Tutorial: Oracle PL/SQL REF Cursors

http://oraclefaqs-edu.blogspot.com/2008/12/oracle-plsql-ref-cursors.html

This blog contains the Oracle concepts like sql commands,stored procedures,pl/sql,cursors,functions and triggers. Oracle PL/SQL REF Cursors. Is just a reference or a handle to a static cursor. It allows a user to pass this “reference to the same cursor” among all the programs that need access to the cursor. Cursor variables. Give you easy access to centralized data retrieval. There are two types of cursor variables. One is called strong and the other is called weak. What is the difference between cursor.