Skip to main content

PLSQL programming ( Learn by examples - Part 3 )

/**
In this section , lets learn the following.
How to use a for loop to process multiple records...
**/
set serveroutput on size 100000
begin
--Select objects of table type that are valid under the schema Susil
for var in (Select * from all_objects where object_name not like '%$%' and object_name in ('EMPLOYEE','EMP') and status='VALID' and owner='SUSIL')
loop
if var.object_type='TABLE'
then
/*
Dbms_metadata package provides us a function get_ddl to get the data definition on object.
For your understanding , i have just chosen two tables in this example
If you remove Object_name condition in the above SQL , it will print DDL definitions for all tables under the specified schema
**/
dbms_output.put_line('=====================================================================');
dbms_output.put_line('Table name =>'||var.object_name);
dbms_output.put_line('=====================================================================');
dbms_output.put_line(' Table definition is below ');
dbms_output.put_line('=====================================================================');
dbms_output.put_line(dbms_metadata.get_ddl(var.object_type,var.object_name,var.owner));
dbms_output.put_line('=====================================================================');
end if;
end loop;

end;
/

Output:

=====================================================================
Table name =>EMP
=====================================================================
Table definition is below
=====================================================================

CREATE TABLE "SUSIL"."EMP"
( "ENAME" VARCHAR2(10),
"SAL" NUMBER(7,2)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USER_TS"

=====================================================================
=====================================================================
Table name =>EMPLOYEE
=====================================================================
Table definition is below
=====================================================================
CREATE TABLE "SUSIL"."EMPLOYEE"
( "EMP_ID" VARCHAR2(6),
"EMP_NAME" VARCHAR2(40),
"DEPT_NO" NUMBER,
CONSTRAINT "E_PK" PRIMARY KEY ("EMP_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USER_TS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USER_TS"

=====================================================================

Comments

Post a Comment

Popular posts from this blog

Design and Operations ( Employee- Model )

-- DB design drop table emp_Salary; drop table emp_Designation; drop table emp; drop table Designations; drop table Departments; drop table Salary_Bands; drop table Addresses; Create table emp( emp_id number, emp_Name varchar2(20), emp_address_id varchar2(10), emp_created_date date, emp_created_by varchar2(20), constraint e_pk primary key(emp_id) ); Create table Departments( dept_id number, dept_name Varchar2(20), constraints dept_pk primary key(dept_id) ); Create table Designations( dept_id number references Departments(dept_id), desg_id number, desg_description varchar2(20), constraints desg_pk primary key(dept_id,desg_id) ); Create table emp_Designation( emp_id number references emp(emp_id), emp_des_dept_id number references departments(dept_...
Post a Comment
Read more

Minimum / Maximum from the given list

File: Execute.java -------------------------------------------------------------------------------------- package org.developersbrain.Solutions; public class Execute { public static int min(int[] arrList){ int minV1=0; int minV2=0; int aLen=arrList.length; minV1=arrList[0]; minV2=arrList[aLen-1]; for(int i=0,j=aLen-1;i<=aLen/2;i++,j--){ if(minV1 > arrList[i]){ minV1=arrList[i]; } if(minV2 > arrList[j]){ minV2=arrList[j]; } } if(minV2<=minV1){ return minV2; }else{ return minV1; } } public static int max(int[] arrList){ int minV1=0; int minV2=0; int aLen=arrList.length; minV1=arrList[0]; minV2=arrList[aLen-1]; for(int i=0,j=aLen-1;i<=aLen/2;i++,j--){ if(minV1 < arrList[i]){ minV1=arrList[i]; } if(minV2 < arrList[j]){ minV2=arrList[j]; } } if(minV2>=minV1){ return minV2; }else{ return minV1; } } ...
Post a Comment
Read more

SQL - Binary Search Tree

Problem Statement You are given a table,   BST , containing two columns:   N  and   P,  where   N   represents the value of a node in   BST , and   P   is the parent of   N . Write a query to find the node type of   BST   ordered by the value of the node. Output one of the following for each node: ·          Root : If node is root node. ·          Leaf : If node is leaf node. ·          Inner : If node is neither root nor leaf node. Sample Input                 N          P 1 2 3 2 6 8 9 8 2 5 8 5 5         null Sample Output 1 Leaf 2 Inner 3 Leaf 5 Root 6 Leaf 8 Inner 9 Leaf Explanation The   BST ...
Post a Comment
Read more