Saturday, 17 February 2018

Update Statement

UPDATE STATEMENT

Update Statement :- Update statement is used to modify existing data in Table.Update statement is a DML (Data Manipulation Language)
statement that's why  Rollback is possible for update statement.

Syntax :-  Below is the syntax for delete statement

UPDATE <Table_name> SET <column_name1>=<value1>,<column_name1>=<value1>,.. WHERE <condition>;

Note:- If "where <condition>" is not used in statement then update statement will update all records of table

1) Update all records (Without "Where" clause)Example1:- Update COURSE to 'BSC' in Student table.
SQL>UPDATE STUDENT set COURSE='BSC' ;

2) Update selected records    a) Single column updateExample2:-Update course to BSC where course is BCA
SQL>UPDATE STUDENT set COURSE='BSC' where course='BCA';
Example3:-Update course to BSC where regid is less or equal to 105
SQL>UPDATE STUDENT set COURSE='BSC' where regid<=105;

    b) multiple column update
Example4:-Update course to BSC and DOB to sysdate where course is BCA   
SQL>UPDATE STUDENT set COURSE='BSC',DOB=sysdate  where course='BCA';
Example5:-Update course to BSC and regid to regid+10 where regid less or equal to 105
SQL>UPDATE STUDENT set COURSE='BSC',regid=regid+10  where regid<=105;
  
   c) Update on one table on basis of another table
Example6:-  
SQL>UPDATE STUDENT set COURSE='BSC',DOB=sysdate  where regid in (select regno from student1);

Friday, 16 February 2018

Generating create SQL through dbms_metadata package

Generating create SQL through dbms_metadata package

DBMS_METADATA

DBMS_METADATA:-This package is used to generate the create SQL for Table,Index,Package etc.

Syntax:-

SQL> SELECT DBMS_METADATA.GET_DDL('<object_type','<object_name>','<object owner>') FROM DUAL;

Example1:-Generate the CREATE SQL for EMP table OF SCOTT SCHEMA

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT') FROM DUAL;

Example2:-Generate the CREATE SQL for PK_EMP index OF SCOTT SCHEMA

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','PK_EMP','SCOTT') FROM DUAL;
  
Example3:-Generate the CREATE SQL for EMP_PKG Package Specification OF SCOTT SCHEMA

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE','EMP_PKG','SCOTT') FROM DUAL;

Example4:-Generate the CREATE SQL for EMP_PKG Package Body OF SCOTT SCHEMA

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','EMP_PKG','SCOTT') FROM DUAL;

Example5:-Generate the CREATE SQL for USER TABLESPACE

SQL>  SELECT DBMS_METADATA.GET_DDL('TABLESPACE','USERS') FROM dual;

Example6:-Generate the ALTER SQL for FK_DEPTNO foreign key constraints of SCOTT SCHEMA.

SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','EMP','SCOTT') from dual;

Example7:-Generate GRANT SQL for the System privileges grants for a SCOTT schema.

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT') from dual;

Example8:-Generate the GRANT SQL for the Role grant for SCOTT schema,

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT') from dual;

Example9:-Generate the GRANT SQL for object grants for SCOTT schema

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT') from dual;

Example10:-Generate the CREATE SQL for all tables of SCOTT SCHEMA

SQL>spool tables.sql
SQL>select 'select dbms_metadata.get_ddl(''TABLE'', '''||TABLE_NAME||''',''SCOTT'') from dual;' FROM DBA_TABLES where owner='SCOTT';


Saturday, 3 February 2018

PLSQL Program Examples

                                 PLSQL Program

Example :- Smallest program of PLSQL

begin
null;
end;
/

Example :-Program to print "HELLO PLSQL WORLD !!" 

begin
dbms_output.put_line('HELLO PLSQL WORLD !!');
end;
/

Example :-Program to print the value of variable 

declare 
a number;
begin
a:=10;
dbms_output.put_line(a);
end;
/

declare 
a number :=10 ;
begin
dbms_output.put_line(a);
end;
/

declare
a varchar2(20);
begin
a:='Hello World';
dbms_output.put_line(a);
end;
/


Example :-Program to print the value of SQL*Plus variable 

We can declare SQL*Plus variable by below 

SQL>VARIABLE x VARCHAR2(50)

BEGIN
:x := 'HELLO PLSQL WORLD';
END;
/

Note:-We can print the SQL*Plus variable by below

SQL>PRINT :x
              OR
SQL>SELECT :X from dual;

Example :- Program that take input from Keyboard and display the value

declare
a number;
begin
a:=&c;
dbms_output.put_line(a);
end;
/

Example :- Program that take input from Keyboard  and print that value
                   with some text

declare
a number;
begin
a:=&c;
dbms_output.put_line('Entered number is :-'||a);
end;
/


Example :-Program to add two numbers 

declare 
a number :=10 ;
b number :=30 ;
c number;
begin
c:=a+b;
dbms_output.put_line(c);
end;
/

Example :-Program to add two numbers given by Keyboard

declare 
a number :=&a ;
b number :=&c ;
c number;
begin
c:=a+b;
dbms_output.put_line(c);
end;
/


declare
a number;
b number;
sum1 number;
begin
a:=&a;
b:=&b;
sum1:=a+b;
dbms_output.put_line('Sum of given numbers is :'||to_char(sum1));
end;
/


declare 
a static number :=10 ;
begin
dbms_output.put_line(a);
end;
/


Example:- Program to print the SALARY of EMPLOYEE

DECLARE
vsal number;
begin
select sal into vsal from emp where empno=7499;
dbms_output.put_line('EMPLOYEE SALARY IS :-'||vsal);
end;
/

DECLARE
vsal number;
begin
select sal into vsal from emp where empno=&empno;
dbms_output.put_line('EMPLOYEE SALARY IS :-'||vsal);
end;
/

Example:- Program to print the SALARY and JOB of EMPLOYEE

DECLARE
vsal emp.sal%type;
vjob emp.job%type; 
begin
select sal,job into vsal,vjob from emp where empno=7499;
dbms_output.put_line('EMPLOYEE SALARY IS :-'||vsal);
dbms_output.put_line('EMPLOYEE JOB IS :-'||vjob);
end;
/

DECLARE
vsal emp.sal%type;
vjob emp.job%type; 
begin
select sal,job into vsal,vjob from emp where empno=&empno;
dbms_output.put_line('EMPLOYEE SALARY IS :-'||vsal);
dbms_output.put_line('EMPLOYEE JOB IS :-'||vjob);
end;
/

DML Operation through PL/SQL Block


Example :-Increase the employee salary by 100

begin
update emp1 set sal=sal+100 where empno=&empno;
end;
/

Example :-Increase the employee salary by 10% of given department

begin
update emp1 set sal=sal+(sal*0.1) where deptno=&deptno;
end;
/


Example:-Program to display EVEN or ODD for entered number

declare
a number :=&a;
begin
if (mod(a,2)!=0) then
dbms_output.put_line('Number :-'||a||' is ODD');
else
dbms_output.put_line('Number :-'||a||' is EVEN');
end if;
end;
/


declare
a number;
b number;--global variable
sum1 number;
begin
a:=&a;
b:=&b;
sum1:=a+b;
dbms_output.put_line('Sum of given numbers is :'||sum1);
begin
dbms_output.put_line('Sum of given numbers is :'||b);
end;
dbms_output.put_line('Sum of given numbers is :'||a);
end;
/


declare
a number:=&a;
b number:=&b;
sum1 number;
begin
sum1:=a+b;
dbms_output.put_line('Sum of given numbers is :'||to_char(sum1));
end;
/


declare
a number:=12;
b number:=13;
sum1 number;
begin
sum1:=a+b;
dbms_output.put_line('Sum of given numbers is :'||to_char(sum1));
end;
/


declare
a number default 12;
b number default 13;
sum1 number;
begin
sum1:=a+b;
dbms_output.put_line('Sum of given numbers is :'||to_char(sum1));
end;
/


declare
a number default 12;
b number default 13;
sum1 number;
begin
a:=16;
b:=18;
sum1:=a+b;
dbms_output.put_line('Sum of given numbers is :'||to_char(sum1));
end;
/


declare
a constant number:=12;
b number default 13;
sum1 number;
begin
b:=18;
sum1:=a+b;
dbms_output.put_line('Sum of given numbers is :'||to_char(sum1));
end;
/


declare
a  number not null:=12 ;
b number default 13;
sum1 number;
begin
a:=18;
b:=18;
sum1:=a+b;
dbms_output.put_line('Sum of given numbers is :'||to_char(sum1));
end;
/

LOOPING:-


begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
/


begin
for i in REVERSE 1..10 loop
dbms_output.put_line(i);
end loop;
end;
/


Update Statement

UPDATE STATEMENT Update Statement :- Update statement is used to modify existing data in Table.Update statement is a DML (Data Manipula...