Basic Structure of PL/SQL
PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block...
Wednesday, August 29, 2012
Tuesday, August 28, 2012
even or odd Numbers
declare
num number(5);
begin
num:=#
if num mod 2 =0 then
dbms_output.put_line('Accepted Number '||num||' is even');
else
dbms_output.put_line('Accepted Number '||num||' is odd');
end if;
end;
...
Write PL/SQL Block Which Will Find Maximum Number Between Three Numbers?
Q. Write PL/SQL Block Which Will Find Maximum Number Between Three Numbers?
DECLARE
a NUMBER:=&Input_First_no.;
b NUMBER:=&Input_Second_no.;
c NUMBER:=&Input_Third_no.;
BEGIN
IF (a>b) THEN
IF(a>c)THEN
DBMS_OUTPUT.PUT_LINE(a||' is greatest !');
ELSE
DBMS_OUTPUT.PUT_LINE(c||' is greatest !');
END IF;
ELSIF(b>c)THEN
...
Monday, August 27, 2012
nested Procedure
In general, something that is nested is fully contained within something else of the same kind.
In programming, nested describes code that performs a particular function and that is contained within code that performs a broader function.
One well-known example is the procedure known as the nested do-loop .A Procedure within a Procedure...
Even & odd numbers
--Write a PL_SQL block to insert numbers into temp table Using For Loop
Example :-
Insert the numbers 1..10 excludining 6
BEGIN
FOR i in 1..10 LOOP
IF i = 6 THEN
null;
ELSE
INSERT INTO temp(results)
VALUES (i);
END IF;
END LOOP;
COMMIT;
END;
Execute a PLSQL...
Concatenate Function
2:18:00 AM
Concatenate Function, odd_even_numbers
No comments
Concatenate Function
Sometimes it is necessary to combine together (concatenate) the results from several different fields. Each database provides a way to do this:
CONCAT(str1, str2, str3, ...): Concatenate str1, str2, str3, and any other strings together. Please note the Oracle CONCAT() function only allows two arguments -- only two strings can...
Thursday, August 23, 2012
Raise Application_Error
What is Raise Application_Error
The procedure RAISE_APPLICATION_ERROR lets user to issue user-defined ORA- error messages from stored subprograms. That way, user can report errors to your
application and avoid returning unhandled exceptions.
the syntax is
raise_application_error(error_number, message[, {TRUE | FALSE}]);
DECLARE
num_of_employees...
Mutating Table Errors ?
1:50:00 AM
Mutating Table Errors, odd_even_numbers
No comments
What is Mutating Table Errors ?
A mutating table error occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement).
sql>CREATE OR REPLACE FUNCTION rec_call(sal NUMBER)
RETURN NUMBER IS
BEGIN
INSERT INTO employees(employee_id, last_name,...
Implicit Cursor & Explicit Cursor
1:49:00 AM
Implicit Cursor, Mutating Table Errors, odd_even_numbers
No comments
Implicit Cursor are declared and used by the oracle internally oracle automatically opens and closes the cursor .Where as the explicit cursors are declared and used by the user to .process multi row select statement.
Implicit CURSOR Example
--CREATE TABLE employees (empid NUMBER,empname varchar2(256));
CREATE OR REPLACE PROCEDURE test_implict_cursor
AS
BEGIN
...
Monday, August 20, 2012
WRITE A PROGRAM TO FIND SUM OF NUMBERS FROM 1 TO 100
11:25:00 PM
Mutating Table Errors, odd_even_numbers, Sum_of_numbers
No comments
-- WRITE A PROGRAM TO FIND SUM OF NUMBERS FROM 1 TO 100
DECLARE
N NUMBER(3):=1;
S NUMBER(4):=0;
BEGIN
WHILE N<=10
LOOP
S := S+N;
N :=N+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE SUM IS '||S);
END...
Monday, August 13, 2012
Create View
SQL ViewsA VIEW is a virtual table, through which a selective portion of the data from one or more tables can be seen. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity. A view is stored as a SELECT statement in the database. DML operations on a view like INSERT, UPDATE, DELETE...
Alter Table
SQL ALTER TABLE StatementThe SQL ALTER TABLE command is used to modify the definition (structure) of a table by modifying the definition of its columns. The ALTER command is used to perform the following functions.1) Add, drop, modify table columns2) Add and drop constraints3) Enable and Disable constraintsSyntax to add a columnALTER TABLE table_name...
Tuesday, August 7, 2012
WRITE A PROGRAM TO PRINT EVEN NUMBERS FROM 1 TO 100 DECLARE
Pl -sql : Programming Concept. --WRITE A PROGRAM TO PRINT EVEN NUMBERS FROM 1 TO 100 DECLARE N NUMBER(3) :=0; BEGIN WHILE N<=100 LOOP N :=N+2; DBMS_OUTPUT.PUT_LINE(N); END LOOP; E...
Distinct Clause
The SQL SELECT DISTINCT StatementIn a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.The DISTINCT keyword can be used to return only distinct (different) values.SQL SELECT DISTINCT SyntaxSELECT DISTINCT column_name(s)FROM table_nameSELECT...
Where
SQL HAVING ClauseHaving clause is used to filter data based on the group functions. This is similar to WHERE condition but is used with groupfunctions. Group functions cannot be used in WHERE Clause but can be used in HAVING clause.For Example: If you want to select the department that has total salary paid for its employees more than 25000, the sqlquery...
Monday, August 6, 2012
Sql-Fundamental
11:51:00 PM
Distinct, Sql-Tutorial, Where_clause
No comments
SQL TutorialSQL (Structured Query Language) is used to modify and access data or information from a storage area called database.This beginner sql tutorial website teaches you the basics of SQL and how to write SQL queries. I will be sharing myknowledge on SQL and help you learn SQL better. The sql concepts discussed in this tutorial can be applied...
