SQL with PL/SQL

DDL commands --> create user / table / view / sequence

alter

DML --> data manipulation language (insert, select, update, delete)

eg :

SELECT ename FROM emp WHERE sal = (SELECT MAX(sal) FROM EMP);

%type(single col), %rowtype(single row/record)

cursor --> manu columns/rows

eg :

DECLARE

l_emp_ename emp.ename%type;

BEGIN

SELECT ename INTO l_emp_ename FROM emp WHERE sal = (SELECT MAX(sal) FROM EMP);

DBMS_OUTPUT.put_line(‘Message‘);

END;

DBMS_OUTPUT.put(‘message‘);

DBMS_OUTPUT.put_line(‘message);

the implicit cursor(give us 1 row/record)(create / open / get data / close by oracle automatically)

eg : select ename from emp where empno=1111;

the explicit cursor(give us many rows/records)(create / open / get data / close by ourselves)

eg : select * from emp;

an emplicit sursor works as follows :

open the cursor

fetch data from the cursor

fetch again to check if any more rows are found

eg :

DECLARE

l_find_job varchar2(10) := ‘PROGRAMMER‘;

BEGIN

UPDATE emp

SET job = ‘DEVELOPER‘

WHERE job = ‘PROGRAMMER‘;

END;

commir : save all DML commands

rollback : undo before commit

savepoint : roll back to the savepoint; clear all data till the last commit(when there is no savepoint)

aotocommit : when you  close SQL*PLUS if autocommit is on, the data is you delete, update or insert without commit is saved.

set autocommit on/off

show autocommit

transaction control

eg :

BEGIN

update

commit

END;

commit

eg :

BEGIN

DELETE debug;

SAVEPOINT deleted_debug;

DELETE transactions;

ROLLBACK TO deleted_debug;

COMMIT;

END;

TRANSACTION starts from the last commit end with the commit.

eg :

create table test1(ename varchar2(10));

whenever you issue a SQL statement in a PL/SQL block, PL/SQL creates an implicit cursor, the implicit cursor is using number of attribute that can be selected to find the result of the SQL command.

SQL%ROWCOUNT : the number of rows processed by the SQL statement

SQL%FOUND : true if at least one row was processed by the SQL statement, otherwise false

SQL%NOTFOUND : true if no rows were processed by the SQL statement, otherwise false.

eg : select ename from emp where empno = 1111;

DBMS_OUTPUT.put_line(SQL%ROWCOUNT); (1)

eg : select * from emp;

SQL%ROWCOUNT ()

eg : select ename from emp where 1 = 2;

SQL%ROWCOUNT(0)

 copy emp table to emp1 with all rec & data : create table emp1 as select * from emp;

find the largest number from three numbers

eg:

DECLARE

num1 number(10) := ‘&num1‘;

num2 number(10) := ‘&num2‘;

num3 number(10) := ‘&num3‘;

BEGIN

  if num1 >= num2 then

if num2 >= num3 then

DBMS_OUTPUT.put_line(num1);

elif num1 >= num3 then

DBMS_OUTPUT.put_line(num1);

else

DBMS_OUTPUT.put_line(num3);

elif num2 >= num3 then

DBMS_OUTPUT.put_line(num2);

else 

DBMS_OUTPUT.put_line(num3);

END;

eg :

DBMS_OUTPUT.new_line;

DBMS_OUTPUT.put_line();

DBMS_OUTPUT.put();

write a program to reverse the number user input : 5678 output : 8765

eg :

write a program to find the factorial (input 4 --> 4*3*2*1 ==) total as a result

eg :

declare

num number(4) := #

total number(10) := 1;

begin

for i in num

loop

total := total *i;

end loop;

end;

/

write a program to print fibonacci series(the next number is the sum of last two numbers)(0112358...)

eg :

declare

num1 number(5) := 0;

num2 number(5) := 1;

num3 number(5) := 1;

num number(5) := #

begin

for i in 1.. num

loop

num1 := num3 + num2;

num2 := num3 + num1;

num3 := num1 + num2;

DBMS_OUTPUT.put_line(num1);

DBMS_OUTPUT.put_line(num2);

DBMS_OUTPUT.put_line(num3);

end loop;

end;

/

eg :

declare

num number(6) := #

num1 number(6) := 0;

num2 number(6) := 1;

num3 number(6) := 1;

begin

DBMS_OUTPUT.put(‘ ‘ || num1);

DBMS_OUTPUT.put(‘ ‘ || num2);

for i in 3..num

loop

num3 := num1 + num2;

DBMS_OUTPUT.put(‘ ‘ || num3);

num2 := num2;

num2 := num3;

end loop;

DBMS_OUTPUT.new_line;

end;

wtite a program to display the given number is a prome number or not.(质数）(using mod() remaining=0 )

eg :

declare

num number(6) := #

begin

for i in 2..num

loop

if mod(num, i) != 0 then 

end loop;

end;

eg :

declare

num number(5) := #

a number(5);

begin

for i in 2..num-1

loop

a := MOD(num, i);

if  a = 0 then

goto ABC

end if;

end loop;

<<ABC>>

if a = 1 then

DBMS_OUTPUT.put_line(num || ‘ is a prime number‘);

else

DBMS_OUTPUT.put_line(num || ‘ is not a prime number‘);

end if;

end;

.

/

%type (single column)

%rowtype (all column)

some columns from the table

composite type

eg :

declare

TYPE emp_rec_type is record ( name varchar2(10), sal number(10), hiredate date);

emp_rec emp_rec_type;

begin

select ename, sal, hiredate into emp_rec from emp where empno = &empno;

DBMS_OUTPUT.put_line(emp_rec.name || emp_rec.sal || emp_rec.hiredate);

end;

.

/