PLSQL Class 3 in Telugu | Banking Tables| Nested Block| Labeled block
Posts
SQL * Loader
CHAPTER 9-SQL * Loader v SQL*Loader allows you to load data from an external file into a table in the database. It can parse many delimited file formats such as CSV, tab-delimited, and pipe-delimited. v Method 1 by using par file in command prompt v We will load email data CSV file format into the emails table in the database. v The following is the content of the email.dat file: 1, john.doe@example.com 2, jane.doe@example.com 3, peter.doe@example.com v The contents of the control file ( email.ctl ) is as follows: load data into table emails insert fields terminated by "," ( email_id, email ) In the control file: The load data into table emails insert instruct the SQL*Loader to load data into the emails table using the ...
CASE()| Multiple inserts| MERGE| How to create user defined datatype & Table
CASE Function Ø It can be used to perform if then else logic in SQL. Ø These are two types 1) Simple case expression 2) Search case expression Ø 1. Simple case expression Select empno, ename, CASE deptno When 10 then ‘ACCOUNTING’ When 20 then ‘RESEARCH’ When 30 then ‘SALES’ When 40 then ‘OPERATION’ Else ‘others’ End Department_name from emp; Ø 2. Search case expression Select empno, ename, job, sal, Case When job=’MANAGER’ then ‘MGR’ When sal=3000 then ‘hipay’ When job=’SALESMAN’ then ‘sman’ Else ‘not specified’ End Employee_name from emp; Multiple inserts Ø Insert all When deptno<20 then into d1 values (deptno, dname, loc) When deptno>20 and deptno<=30 then into d2 values (deptno, dname, loc) Else into d3 values (deptno, dname, loc) Select * from dept d; Commit; DML MERGE statement à It is used to select rows from one o...
