-

 

PLSQL-Course Contents

1. About PLSQL and features

2. Anonymous &Named Blocks

3. About Editors and IDE's

4. Data types in PLSQL

5. Declaring, Assigning variables.

6. DB programming with PLSQL

7. Conditional Statements(if then else, multi-if,nested if)

8. Reference types

9. Loops in PLSQL(while, for)

10.Cursor,ref cursor and dynamic cursor

12. Collections

13. Bulk Collect

14. collection methods

15. Rowtype array

16. User Defined records

17. Bulk Bind

18. VArray

19. Nested Table

20. pl/sql table vs varray vs nested table

21. Error/Exception handling

22. SQLcode, SQLerrm.

23. user defined errors using Raise statements

24. Pragma Exception_init

25. Named PLSQL blocks(Procedures, functions,Packages and Triggers)

26. Parameters, Execution

27. User Defined functions

28. Listagg and user_source

29. Packages

30. Triggers

31. Testing, old&new variables, Auditing

32. UTL_File package

33. prgram to copy the data from table to file viseversa and table to csv

34. RegExp_Substr

35. Storing images in DB.

36. Normalization


Comments

Post a Comment

Popular posts from this blog

Oracle SQL

-
SQL Course Content Topic_Details  1. DB,DBMS,RDBMS,ORDMBS & DB development lifecycle Click Here 2. About Oracle Click Here 3. Client/Server Click Here 4. About SQL Click Here 5. Data, data definitions, schema, and SQL Developer tool and DB install process. Click Here 6. Creating a User/Schema/Account in Oracle DB. Click Here 7. Data types in Oracle. Click Here 8. Creating,inserting a Table in Oracle DB. Click Here 9. Operators in Oracle DB. Click Here 10. Displaying data from Oracle DB. Click Here 12. Where Clause Click Here 13. In,between,Like,Is Operators Click Here 14. Alias, All_users,User tables. Click Here 15.Order by, Distinct,fetch clauses ...
Read more

SubQueries

-
Single Row_SubQuery -------------------- Single-Row Sub-Queries Returns one value SELECT <columns> FROM <table_name> WHERE <col_name> <OP> (SELECT STMNT); > , >=, <, <=, =. <> A single-row subquery in Oracle SQL is a subquery (a query nested inside another query) that returns only one row. The main/outer query uses the inner query result as its conditions If single row sub-query returns more than one row then you will get an error ORA-01422: exact fetch returns more than requested number of rows) It can be used in Select, Where, Having clauses Single Row_SubQuery -------------------- --1. Find who is getting highest salary in the employee table? select max(sal) from emp; select *from emp where sal=(select max(sal) from emp); select max(salary) from employees; select *from employees where salary=(select max(salary) from employees); --2.Find second maximum salary emp? select *from emp where sal=( select max(sal) from emp where sal!=(select max(sa...
Read more

With Clause

-
 WITH Clause  ====================================== What? With Clause is a Sub-Query factoring or (Common Table Expressions-CTEs) This features is introduced in Oracle 9i.Using this feature: We assign query result set to a temporary tables. We can access data from temporary tables in main Select clause as many times we require with out repeating the sub-queries. When? Usage of (2,3 level-Nested Queries) can  be minimized or avoided when using With Clause. If we use sub-queries up to 1 level then performance may be good but when we use 2 level or 3 level sub-queries then there may chance of performance issues when we try to fetch large large volume of data. How to implement CTEs ====================== SELECT deptno,        Sum(sal) total_sal FROM   emp GROUP  BY deptno; --fetch sum of sal for each dept. SELECT Avg(total_sal) FROM  (SELECT deptno,               Sum(sal) total_sal     ...
Read more