Download Plsql and more Lecture notes Advanced Computer Architecture in PDF only on Docsity!
PL/SQL:
- It stands for procedural language/structure query language in SQL we can’t execute the same statement repeatedly to a particular no of times but where as in PL/SQL we can execute the same statement repeatedly because it supports looping mechanism.
- (^) In SQL we can’t execute more than one statement at a time. But where as in PL/SQL we can execute more than one statement concurrently.
- PL/SQL program is a combination of procedural language statements and structure query language statements.
Data types in PL/SQL: The data types which are using in SQL same data types are supported in PL/SQL. Operators in PL/SQL: The operators which are using in SQL same operators are by PL/SQL except assignment operator. Input statements in PL/SQL:
- There is no input statements in PL/SQL to input the values at run time.
- (^) If we want input values at run time then we use insertion operator (&). Output statements in PL/SQL: Dbms_output.put_line()/ dbms_output.put() This is other statement in PL/SQL which is used to print the values on the standard output device. SYN: dbms_output.put_line(‘message’); EX: dbms_output.put_line(‘Sathya Technologies’); SYN: dbms_output.put_line(‘message’||variable); EX: dbms_output.put_line(‘sum is ‘||C); SYN: dbms_output.put_line(Variable); EX: dbms_output.put_line©; Variable declaration in PL/SQL: SYN: variable data type EX: a number(10);
Block: Block is grouped code or set of statements, it is classified into two types.
- Anonymous block
- Sub programs
- Anonymous Blocks: i. (^) Anonymous blocks are those blocks which we can’t store permanently in database once the user logout from the database these blocks are completely destroyed. ii. These blocks can’t have any prototype that is those blocks can be called as unnamed blocks.
- Subprograms: i. Sub programs are those blocks which we can store permanently in database so that, the user can access these blocks at a particular point of time. ii. These blocks refer with same proper name or specific name that is so that these blocks as named PL/SQL. Structure of anonymous blocks: Declare Declaration of variables Begin Statement 1; Statement 2; . . Statement n; Exception Exception handling statements; End; PL/SQL anonymous block contains four sections those are declare, begin, exception and end. Here declare and exception sections are optional.
b:=a-b; a:=a-b; dbms_output.put_line('value of a is'||a); dbms_output.put_line('Value of b is'||b); end; P) Write a PL/SQL block to converts feets into inches. declare a number:=&a; begin dbms_output.put_line('enter feet values'||a); a:=a*12; dbms_output.put_line('inches for give value'||a); end; Conditional statements in PL/SQL: Syntax: If(condition) then Statement 1; Statemnet 2;
Statement n: Else if (Condition 1) Statement3; Statement4; else statement n; End if;
This is the conditional statement in the PL/SQL. Which checks the condition either true or false by means of relational operator such as >, <, >=, <= et.c., If the condition is satisfied it executes the statement between if and else. If the condition is not satisfied then it will execute the statements after else. Note: Every if condition should ends with end if statemet..
P) Write a PL/SQL block input two numbers and find biggest one. declare a number:=&a; b number:=&b; begin if(a>b) then dbms_output.put_line('a is big'); elsif(a=b) then dbms_output.put_line('both are same'); else dbms_output.put_line('b is big'); end if; end;
P) Write a PL/SQL block input any positive number and check it out even or odd? declare a number:=&a; begin if(a =0) then dbms_output.put_line(a||'is neither even nor odd');
nb number; begin if(tc='d') then nb:=cb+tamt; dbms_output.put_line('The net balance is' ||nb); else if(tc='w') then nb:=cb-tamt; dbms_output.put_line('The net balance is' ||nb); else dbms_output.put_line('Please enter d or w'); end if; end if; end;
P) Write Pl/SQL block input consumer number, Consumer name, and starting reading number ending reading number, slap type (i-industry, c-commercial, r-residence). Calculate units consumed? Conditions:
- If slab type is industry then unit rate is 5/-
- If slab type is commercial unit rate is 4/-
- If slab type is residence unit price is 3/-. Calculate the total charge?
declare cno number(10):=&no; cname varchar2(10):='&cname';
stno number(10):=&stno; edno number(10):=&edno; slab varchar2(1):='&slab'; tu number; begin tu:=edno+stno; if(slab='i') then tu:=tu5; dbms_output.put_line('total unit cost is'||tu); elsif(slab='c') then tu:=tu4; dbms_output.put_line('total unit cost is'||tu); elsif(slab='r') then tu:=tu*3; dbms_output.put_line('total unit cost is'||tu); else dbms_output.put_line('Please enter i or c or r'); end if; end; (OR) declare cno number(10):=&no; cname varchar2(10):='&cname'; stno number(10):=&stno; edno number(10):=&edno; slab varchar2(1):='&slab';
- If slab type is commercial and units consumed >=100 then unit rate is 4/- else5/-.
If slab type is residence and units consumed <=100 then unit rate is 3/- else 4/- calculate total charge. declare
cno number(10):=&no; cname varchar2(10):='&cname'; stno number(10):=&stno; edno number(10):=&edno; slab varchar2(1):='&slab'; tu number; begin tu:=edno+stno; if(slab='i') then if(tu<=100) then tu:=tu5; dbms_output.put_line('total unit cost is'||tu); else tu:=tu6; dbms_output.put_line('total unit cost is'||tu); end if; end if; if(slab='c') then if(tu<=100) then tu:=tu*4; dbms_output.put_line('total unit cost is'||tu); else
tu:=tu5; dbms_output.put_line('total unit cost is'||tu); end if; end if; if(slab='r') then if(tu<=100) then tu:=tu3; dbms_output.put_line('total unit cost is'||tu); else tu:=tu*4; dbms_output.put_line('total unit cost is'||tu); end if; end if; end; Loop:
- Loop is a mechanism which is supported by every programming language C, COBOL, e.t.c.,
- Whenever we need to execute a single or more than statements repeatedly then we use loops.
- Loops are categorized into two types. i. Range based loops ii. Condition based loops. i. Range based loops: Range based loop is a loop statements which executes statements as long as initial value reaches the final value. Once control crossed the final value then automatically comes under the loop. EX: For loop, for reverse.
Note: If i is participating in loop it is not possible.
P) To print 10 numbers on console. declare i number; begin for i in 1..10 loop dbms_output.put_line(i); end loop; end; P) To print 10 even numbers declare i number; begin for i in 1..5 loop dbms_output.put_line((i2)); end loop; end; P) To print 10 odd numbers declare i number; begin for i in 1..5 loop dbms_output.put_line((i2)-1); end loop; end; P) To find factorial number.
declare
i number; n number:=&n; f number:=1; begin for i in 1..n loop f:=f*i; end loop; dbms_output.put_line(f); end; P) Check it is prime or not. declare i number; n number:=&n; c number:=0; begin for i in 1..n loop if(mod(n,i)=0) then c:=c+1; end if; end loop; if(c=2) then dbms_output.put_line(n||'is prime'); else dbms_output.put_line(n|| 'is not prime'); end if;
n:=floor(n/10);
end loop; dbms_output.put_line(s); end;
for reverse:
set serveroutput on; declare a number:= 1; begin for i in reverse 1..5 loop dbms_output.put_line(i); end loop; end;
Loop Syntax: Syn: Loop Statement 1;. . Statement n; Exit when(condition) End loop;
Here the statements are executed as long as the given condition is not satisfied control automatically comes outer loop.
Write a PL/SQL block to print 10 times a String. declare i number:=1; begin loop dbms_output.put_line('Hello'); i:=i+1; exit when(i>10); end loop; end;
Write a PL/SQL block print the employee details such as employee name, salary and deptno when employee number passes as in value. declare a number; b varchar2(10); c number; d number; begin select ename,sal,deptno into b,c,d from emp where empno=&a; dbms_output.put_line(b||' ' ||c||' '||d);
P) Write a PL/SQL block to raise zero divide exception.
set serveroutput on; declare a number:=&a; b number:=&b; c number; begin c:=a/b; dbms_output.put_line('division is'||c); exception when zero_divide then dbms_output.put_line('second value cannot be zero'); end;
some predefined exceptions
Oracle Exception Name Oracle Error
Explanation DUP_VAL_ON_INDEX ORA- 01
You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index. TIMEOUT_ON_RESOURCE ORA- 51
You were waiting for a resource and you timed out. TRANSACTION_BACKED_OUT ORA- 61
The remote portion of a transaction has rolled back. INVALID_CURSOR ORA- 01
You tried to reference a cursor that does not yet exist. This may have happened because you've executed a FETCH cursor or CLOSE cursor before OPENing the cursor.
NOT_LOGGED_ON ORA-
You tried to execute a call to Oracle before logging in. LOGIN_DENIED ORA- 17
You tried to log into Oracle with an invalid username/password combination. NO_DATA_FOUND ORA- 03
You tried one of the following:
- You executed a SELECT INTO statement and no rows were returned.
- You referenced an uninitialized row in a table.
- You read past the end of file with the UTL_FILE package. TOO_MANY_ROWS ORA- 22
You tried to execute a SELECT INTO statement and more than one row was returned. ZERO_DIVIDE ORA- 76
You tried to divide a number by zero.
INVALID_NUMBER ORA- 22
You tried to execute a SQL statement that tried to convert a string to a number, but it was unsuccessful. STORAGE_ERROR ORA- 00
You ran out of memory or memory was corrupted. PROGRAM_ERROR ORA- 01
This is a generic "Contact Oracle support" message because an internal problem was encountered. VALUE_ERROR ORA- 02
You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data. CURSOR_ALREADY_OPEN ORA- 11
You tried to open a cursor that is already open.
