SQL UNION ALL Example
EXPLICIT CURSORS:
They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.
DECLARE the cursor in the declaration section. OPEN the cursor in the Execution Section. FETCH the data from cursor into PL/SQL variables or records in the Execution Section. CLOSE the cursor in the Execution Section before you end the PL/SQL Block.
DECLARE
emp_rec emp_tbl%rowtype; CURSOR emp_cur IS
SELECT * FROM WHERE salary > 10; BEGIN
OPEN emp_cur;
FETCH emp_cur INTO emp_rec;
dbms_output.put_line (emp_rec.first_name || ' ' || emp_rec.last_name); CLOSE emp_cur; END; Attributes %FOUND %NOTFOUND %ISOPEN %ROWCOUNT SP – STORED PROCECDURE
A stored procedure or in simple a proc is a named PL/SQL block which performs one or more specific task.
Syntax
CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters] IS Declaration section BEGIN Execution section EXCEPTION Exception section END; Example
CREATE OR REPLACE PROCEDURE employer_details IS
CURSOR emp_cur IS
SELECT first_name, last_name, salary FROM emp_tbl; emp_rec emp_cur%rowtype;
BEGIN
FOR emp_rec in sales_cur LOOP dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name || ' ' ||emp_cur.salary); END LOOP; END; /
Execute a Procedure Execute [or EXEC]Proc_name; procedure_name; in SQL>
Create Table Table-Name(Col1DataTypeNOTNULL,…..PRIMARY KEY(COL1)); MODIFY
Alter table Table-Nameaddcolumn……….. DELETE TABLE
Drop Table Table-Name
Index --> Index can speed execution of SQL With Condition
Create Index Index-Name on Table-Name (Column [order]) --> Asc(default) --> Desc
Composite Index --> involves more than 1 column Unique Index --> Prevent Duplication of Data
Clustered Index --> Sorted both Logically & Physically. --> Created on Primary Key
Create index I1 on Employee (c1,c2); c1,c2 --> Columns in table Employee Drop Index Index-Name;
Views -->namedTable,won‘tPhysicallySeparateDatadefineinterimofothertable ( base table )
Create View View-Name as Sub-Query Can be used for multiple queries Both Updatable & nonUpdatable NULL --> Values desired
Insert into Table-Name[Column List ];
Update Table set Column =Update table-Name Where Condition; Delete from Table-Name Condition;
Select Trunc(* ) from Table-Name --> Deletes the entire data leaves Table Structure alone. Cast --> Scalar value to scalar Data-Type
Cast (scalar-exp) as { Data-Type } CASE
When Cond-Exp then Scalar exp Else
scalar Exp end;
INDICATOR --> null indicator variable parameter sent to -1
SELECT --> Select ( all / Distinct) exp (Query) from Table-Name; Where Condition Group By --> Usually refers for Aggregate functions / Columns
--> Having --> include certain Groups ,Equivalent to [ where ] --> Order By --> Sort by Order --> Asc [default]
--> Desc [descending Order ]
Select Distinct (* ) from Table-Name --> Eliminate Duplicates Select * from Table-Name where year in (1993,1996,1999);
--> check if it matches year values given Select * from Table-Name where year not in (1993,1996,1999);
checkifitnotmatchesyearvaluesgiven.
Select * from Table-Name where C1 between [2 specific values ] Or Using Sub-Queries Correlated Sub Query
--> execute Outer Query first , Sub-Query Next Usual --> Sub Query Executed first then outer query .
Parallel Sub Query --> More than 1 sub query in a query. Join
EQUI-JOIN --> comparison operator is equality
Non EQUI-JOIN--> comparison operator is not equal sign
Natural Join --> produce result that contain 2 identical rows ,1 row being Eliminated Self Join --> A Table joined with Itself
Outer Join --> Extended Inner Join
Table are joined contain matching values both matching & non matching rows returned. LEFT Outer Join --> Use Keyword LEFT [OUTER] or (*)
RIGHT Outer Join --> use keyword Right [OUTER] FULL Outer Join --> use (+)
Grant Permission Grant (All/Privilege)
On { Table Name Column-Name VIEW NAME } To { PUBLIC /USER LIST }
With Grant option
Public --> to all users of the system ALL --> all Privileges
With Grant --> All users can access Privileges REVOKE --> Take away privileges given by Grant Revoke { Privileges List / ALL }
On Table /Coloumn /View } From { Public /User List }
I ntegrity Constraints --> restrict data values that can be inserted into database that could be inserted into database that could be deleted & Modified.
Referential Entity
Create Assertation – check (not Exists (cond)); Domain
Create Domain D-Name as Defn
Alter Domain D-NameAdd………../Drop………
Candidate Key --> unique identifier ( a column or combination of columns) of table
Foreign Key --> a column or combination of column in one table ,values required to match some candidate key in another table
Lock --> On Reading Data --> on Updating Data
Exclusive Lock --> during data modification on insert / delete / update Update --> no access to other users
Shared Lock --> read only access to other users.
--> Need to be executed only once and will get invoked automatically till dropped Row Level Trigger --> once Fro each row of transaction
Statement Level Trigger --> Execute once for each transaction Before Trigger , After Trigger
Create or Replace Trigger Trigger-Name Before /After
Delete / INSERT / UPDATE On [USER] Table-Name For Each Row
[PL/SQL Block] Backup: COMMIT; ROLLBACK;
SavePoint --> A point on transaction That you can rollback without rollback entire transaction. SavePoint sp1 SQL1; …….. SavePoint sp2 SQL2; Rollback sp2; --> rollback SP2 String DataTypes
CONCATINATION | | STRING | | STRING2
RPAD & LPAD --> Pad right side with any set of characters. LTRIM --> delete unwanted character on left end of string. RTRIM --> delete unwanted character on right end of string Length (string) --> 6
SUBSTR(String, Start[count]); --> generate substring from start.
INSTR(String, Set[,start[ocandea]); --> insert a string at desired Location. SOUNDEX --> Compare sound entry in selected coloumn
Dates
ADD_MONTHS (PDate ,2); PDATE --> Present date in Table Least
Greatest
DUAL --> Inbuilt Table provides with 1 row & 1 column initialized by Oracle. NEXT_DAY LAST_DAY MONTHS_BETWEEN TO_DATE ROUND TRUNC TO-CHAR NEW-DATE
SYNONYM --> Alias Name to Object.
Create Synonym emp for emp1.employee select * from empl.employee --> replace emp in table name; Drop Synonym emp;
SNAPSHOT --> Read Only Table Simple SnapShot
Complex SnapShot Create SnapShot emp;
EXECUTE Immediate --> Prepare / Execute Statement. DECLARE --> Define a CURSOR
OPEN --> Define a executable statement FETCH --> Return Data from Resultant Table CLOSE --> Release all resources.
FUNCTION
A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.
Syntax
CREATE [OR REPLACE] FUNCTION function_name [parameters] RETURN return_datatype; IS Declaration_section BEGIN Execution_section Return return_variable; EXCEPTION exception section Return return_variable; END; PARAMETER PASSING
IN - We can pass values to the stored procedure through these parameters or variables. This type of parameter is a read only parameter
OUT - The OUT parameters are used to send the OUTPUT from a procedure or a function
IN OUT - The IN OUT parameter allows us to pass values into a procedure and get output values from the procedure. This parameter is used if the value of the IN parameter can be changed in the calling program
DECLARE
empName varchar(20);
CURSOR id_cur SELECT id FROM emp_ids; BEGIN
FOR emp_rec in id_cur LOOP
emp_name(emp_rec.id, empName);
dbms_output.putline('The employee ' || empName || ' has id ' || emp-rec.id); END LOOP;
END; /
CREATE OR REPLACE PROCEDURE emp_salary_increase
(emp_id IN emptbl.empID%type, salary_inc IN OUT emptbl.salary%type) IS
tmp_sal number; BEGIN
SELECT salary INTO tmp_sal FROM emp_tbl
WHERE empID = emp_id;
IF tmp_sal between 10000 and 20000 THEN salary_inout := tmp_sal * 1.2;
ELSIF tmp_sal between 20000 and 30000 THEN salary_inout := tmp_sal * 1.3;
ELSIF tmp_sal > 30000 THEN salary_inout := tmp_sal * 1.4; END IF;
END; /
The below PL/SQL block shows how to execute the above 'emp_salary_increase' procedure. DECLARE CURSOR updated_sal is SELECT empID,salary FROM emp_tbl; pre_sal number; BEGIN
FOR emp_rec IN updated_sal LOOP pre_sal := emp_rec.salary;
emp_salary_increase(emp_rec.empID, emp_rec.salary); dbms_output.put_line('The salary of ' || emp_rec.empID || ' increased from '|| pre_sal || ' to '||emp_rec.salary); END LOOP;
END; / PL/ SQL JFile.java
A Java class that draws together various pieces of information about operating system files and offers it through an API accessible from PL/SQL.
Polishing up the delete method
public static int delete (String fileName) { File myFile = new File (fileName); boolean retval = myFile.delete(); if (retval) return 1; else return 0; }
CREATE OR REPLACE FUNCTION fDelete ( file IN VARCHAR2) RETURN NUMBER AS LANGUAGE JAVA
NAME 'JDelete.delete (java.lang.String) return int'; IF fdelete ('c:\temp\temp.sql') = 1 THEN ... FUNCTION fDelete (
/* Filename on companion disk: JFile.java * / import java.io.File;
public class JFile { public static int tVal () { return 1; } ;
public static int fVal () { return 0; } ; public static int delete (String fileName) { File myFile = new File (fileName); boolean retval = myFile.delete(); if ( retval ) return tVal();
else return fVal(); } }
FUNCTION delete (file IN VARCHAR2) RETURN BOOLEAN; END xfile;
So now we have the Boolean function specified. But how do we implement it? I have two design objectives:
Hide the fact that I am relying on numeric values
to pass back TRUE or FALSE.Avoid hardcoding the 1 and 0 values in the package.To achieve these objectives, I will define two global
variables in my package to hold the numeric values: CREATE OR REPLACE PACKAGE BODY xfile IS g_true INTEGER;
g_false INTEGER;
And way down at the end of the package body, I will create an initialization section that calls these programs to initialize my globals. By taking this step in the initialization section, I avoid unnecessary calls (and overhead) to Java methods:
BEGIN
g_true := tval; g_false := fval; END
xfile;
Back up in the declaration section of the package body, I will define two private functions, whose only purpose is to give me access in my PL/SQL code to the JFile methods that have encapsulated the 1 & 0:
FUNCTION tval RETURN NUMBER AS LANGUAGE JAVA NAME 'JFile.tVal() return int';
FUNCTION fval RETURN NUMBER AS LANGUAGE JAVA NAME 'JFile.fVal () return int';
I have now succeeded in soft-coding the TRUE/FALSE values in the JFile package. To enable the use of a true Boolean function in the package specification, I create a private "internal delete" function that is a wrapper for the JFile.delete( ) method. It returns a number:
FUNCTION Idelete (file IN VARCHAR2)
RETURN NUMBER AS LANGUAGE JAVA NAME 'JFile.delete (java.lang.String) return int';
Finally, my public delete function can now call Idelete and convert the integer value to a Boolean by checking against the global variable:
BEGIN
RETURN Idelete (file) = g_true; EXCEPTION WHEN OTHERS THEN RETURN FALSE;
END;
And that is how you convert a Java Boolean to a PL/SQL Boolean. You will see this same method employed again and again in the xfile package body. One of my favorite features of JFile is its ability to return a list of files found in a directory. It accomplishes this feat by calling the File.list( ) method; if the string you used to construct a new File object is the name of a directory, it returns a String array of filenames found in that directory. Let's see how I can make this information available in PL/SQL. I create a String method called dirContents, as follows:
/* Filename on companion disk: JFile.java * / public static String dirContents (String dir) {
File myDir = new File (dir);
String[] filesList = myDir.list(); String contents = new String(); for (int i = 0; i < filesList.length; i+ +)
contents = contents + listDelimiter + filesList[i]; return contents; }
This method instantiates a File object called myDir and then assigns the myDir.list( ) to a String array called filesList. I then use a Java "for" loop to concatenate each of the files into a single String, separated by the listDelimiter, and return that String. Over on the PL/SQL side of the world, I will create a wrapper that calls this method:
FUNCTION dirContents (dir IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'JFile.dirContents (java.lang.String) return java.lang.String';
Self-joins can also be written as outer joins.
A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.
Stored procedures are supported by most DBMSs, but there is a fair amount of variation in their syntax and capabilities. For this reason, we will show you a simple example of what a stored procedure looks like and how it is invoked from JDBC, but this sample is not intended to be run.
MVC APPLICATION FLOW