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;
.
/
