Tech Articles PL SQLThis introductory tutorial to PL/SQL will help you to understand the basic concepts of PL/SQL.Please review the following tutorials and practice the sample SQL Statements on your local Oracle Database. Please note that you must learn these basic things before we actually start getting in to Advanced Oracle Concepts in the OCP Certification track. Fell free to print the articles for offline preparation and post any questions in the discussion board.
We are excited to let you know that we are launching shortly The New Technology Learning Series. We will be starting with Oracle 9i: SQL, PLSQL, and SQL *Plus. Below is the course schedule by week. Please let us know if you have suggestions.
Before we actually start with the lessons, we need to install and setup Oracle on your pc to be able to practice. I would recommend all of you to install Oracle before you start with any of our lessons in the following weeks.
After completing this week’s section you will learn about the basic concepts on SQL, SQL *Plus and PL/SQL. Please use discussion board if you have any questions, we will help you with more details explanation. If you don’t understand and practice in the order we provide it will be difficult for you to learn Oracle.
This Week we will be learning about Creating PL/SQL Blocks which includes Declaration Section, Variables, Scope of Variables, Constants Records, Manipulating Data in PL/SQL and Using DML in PL/SQL .
This tutorial teaches about how to structure flow of control through a PL/SQL program. The control structures of PL/SQL are simple yet powerful. Control structures in PL/SQL can be divided into selection or conditional, iterative and sequential.
This tutorial covers Defining and Using Collections - Declaring, Initializing, and Referencing PL/SQL Collections and Collection Methods - Using the Collection Methods.
|
hi friends, please any one give me the difference between natural join and eqi join. dynamic view and static view regards Prakash |
|
what is arrays and varrays in oracle any example query tuning and user exceptions |
|
can we force not null constrint. suppose i already have null values in a column. and after that i don't want to allow null values in a column. so is it possible Regrads Rohan |
|
WHAT IS TRIGER ? What is the difference between static & dynamic Triger? What is cursor ?Explain different types of Triger? |
| Any budy suggest me. how can use isql*plus our local system. And what configure is also requried. |
|
can any body tell me when materialized view is used, |
|
Can anyone send me some sort of document for basic pl/sql with exaples and then mastering it slowly ? An immediate response would be appreciated !!! |
| What is a procedure, function, trigger and package? |
| how packages are created |
| How business triggers are utilized in PL/SQL can anybody suggest me. |
| The difference between natural join and equi-join is in natural join all the data can be retrived (didn't search for match) whereas in equi-join it search for exact match(the values present in two tables must be equal). |
|
The difference between natural join is is the natural join we didn't using the condition and prefixes. We don't mention the common column. The common column is taken in the conditions are automatically. But the equi join we compulsory using the prefixes and condition in where clause(The condition should get in the Equal To (=) operator). Thank You |
|
finally i m telling there is vast role of oracle that is pl/sql in big company.but i want to know, what should be coverage and limitation in pl/sql ,that covers whole big company requiremt,since pl/sql is not small thing and easy to cover whole thing. Thanks & regards krishna mail id is sirat.kumar@yahoo.com |
|
Natural join Vs Equi join Equi join: 1.In Equi join we have to specify the equal operator in its join condition. eg: select dept.deptno from dept join emp on emp.deptno=dept.deptno; 2.It return the records, only if it matches the equality conditions. 3.In this the common column is repeated twice eg:select * from employee inner join department on employee.departmentid = department.departmentid; In this the equality condition is on the departmentid column.This appears twice.It can be reduced to a single column by 'using' clause. eg:select * from employee inner join department using (departmentid); In this a single department id column will be displayed. Natural join: 1.We need not to specify column names for the join condition.It will automatically join the common column in the two tables. eg:select deptno from dept natural join emp; 2.The output is normally greater than,equal to or lesser than the equality condition in Equi join. eg: table representation for the natural join Employee(Table 1) Name EmpId DeptName Harry 3415 Finance Sally 2241 Sales George 3401 Finance Harriet 2202 Sales Dept(Table 2) DeptName Manager Finance George Sales Harriet Production Charles Employee Natural Join Dept Name EmpId DeptName Manager Harry 3415 Finance George Sally 2241 Sales Harriet George 3401 Finance George Harriet 2202 Sales Harriet eg:Equi Join Table representation. Employee(Table 1) Name EmpId DeptName Harry 3415 Finance Sally 2241 Sales George 3401 Finance Harriet 2202 Production Dept(Table 2) DeptName Manager Sales Harriet Production Charles Employee Equi Join Dept Name EmpId DeptName Sally 2241 Sales Harriet 2202 Production Considering the two results.,The output of Natural Join is greater than the Equi Join output(The number of records displayed as result). 3.In Natural join no need of using the Clause Using like in Equi join(See the above example in Equi join).It can automatically consider the common column and display it only once in output. Note:Equi join,Natural Join and Cross Join are subtypes of INNER JOIN. |
|
PL/SQL Collections The composite data types are known as collections and are available in 3 different forms. 1. Nested tables 2. Varrays 3. PL/SQL Associative arrays 1. Nested Tables Single dimensional arrays which can be declared in the database as a column in another table and in PL/SQL as stand alone. No upper or lower limits. Constrained by the memory available. Nested tables are densely populated when created. Each row has a value. But rows can be logically deleted, so nested tables can be sparsely populated. Each row of the nested table must be of same type. Declaration: Declaration is in two types Declare a type TYPE <type_name> IS TABLE OF <existing_type>; Then declare the variable of that type <variable_name> <type_name>; For example, suppose we want to create a table of numbers, the declarations would be TYPE number_table_type IS TABLE OF NUMBER; my_tab number_table_type; (Example is written in Varray section) 2. Varrays: Is a variable lenght, one dimentional, densely populated arrays (dense in the sense that each cell must exit, although it may be empty) Each element must be of the same type like nested tables they can be defined in the database or in PL/SQL. The declaration defines the size of the array which is fixed. We cannot add new cells to or delete calls from the Varray once we have declared it. These are designed for storing the small value of attributes. Declaration: TYPE <type_name> IS VARRAY <size> OF <existing_type>; <variable_name> <type_name>; Considering an example, the declarations would be:- TYPE phone_no_tab_type IS VARRAY(3) OF NUMBER; TYPE address_tab_type IS VARRAY(3) OF VARCHAR2(500); TYPE email_addr_tab_type IS VARRAY(3) OF VARCHAR2(100); TYPE name_tab_type IS TABLE OF VARCHAR2(100); addresses address_tab_type; phone_nos phone_no_tab_type; names name_tab_type; email_addresses email_addr_tab_type; Note that at the moment, each of the types of data is stored separately and therefore has to be worked with separately.To put this all together we need to create another type as a record then create a nested table to hold the records as follows:- TYPE contact_rec IS RECORD ( name VARCHAR2(100); addrs address_tab_type; phone_nums phone_no_tab_type; email_addrs email_addr_tab_type; ); TYPE contacts_tab_type IS TABLE OF contact_rec; contacts contacts_tab_type; |
|
hi frds, can u please tell me that, is there any possible to add the content of the records in sql. |
