It is Procedural Language that we can set package of procedure or function to executing multiple line of query or handle exeute time error to stope server to crash. It is simple use BEGIN /* query */ END; better for you practicle with this offical side of orcale https:livesql.orcale.com
/*
-- simple write query
*/
BEGIN
dbms_output.put_line(' Welcome to PL/SQL ');
END;
You may be set variable or done any opertion in plsql and write operation like plus, minus, guda, bhag adi chige. Ap kar sakte hai.
DECLARE a number := 1; b number := 2; c number; s varchar := ' sum of number is '; BEGIN c := a + b; dbms_output.put_line(s||c); END;
You can exevute inside mostly sql query inside the procedure but it some limitation to execute. Simpliy you can use mutliple Multiple row insert query inside.
DECLARE age number; BEGIN /* - use normal query inside the unnamed procedure */ INSERT INTO myTable Values(1,21,'lopmudra'); INSERT INTO myTable Values(2,18,'sikora'); INSERT INTO myTable Values(3,10,'zuiskey'); /* - select statement is different - syntax :- SELECT variable INTO column FROM table WHERE condition; */ SELECT age INTO person_age FROM myTable WHERE id = 1; dbms_output.put_line(age); END;
You use exit simple to terminate procedure and it more use full with when it help to set exit at contition.
DECLARE
i number := 0;
BEGIN
-- i := 2;
dbms_output.put_line('first line execute');
EXIT WHEN i = 2;
dbms_output.put_line('second line execute');
EXIT;
dbms_output.put_line('third line execute');
END;
The goto is jumping keyword that work to terimate control to targeting label it such a danger if you not set exit.
BEGIN
dbms_output.put_line('start');
GOTO label1;
dbms_output.put_line(' inner ');
<< label1 >>
dbms_output.put_line('end');
END;
if structure is the set to control execution. simple if condiiton is true then execute queries until to if end. It is very useful with cursor attribute otherwise you use operator or other function like mod();
DECLARE
age number;
BEGIN
-- select query
SELECT age INTO age FROM student WHERE id = 1;
-- if statement
IF age >= 18 THEN
dbms_output.put_line('adult');
END IF;
END;
if else is simplity like if but it also execute condition if false. simple if condition is ture then execute query esle excute next query.
DECLARE
age number;
BEGIN
-- select query
SELECT age INTO age FROM student WHERE id = 3;
-- if else statement
IF age >= 18 THEN
dbms_output.put_line('adult');
ELSE
dbms_output.put_line('child');
END IF;
END;
The nested if else is the mutiple if else that set mutiple condition and check for execute.
DECLARE
age number;
BEGIN
-- select query
SELECT age INTO age FROM student WHERE id = 3;
-- if else if statement
IF age >= 18 THEN
dbms_output.put_line('adult');
ELSIF age >= 12 THEN
dbms_output.put_line('teen');
ELSE
dbms_output.put_line('child');
END IF;
END;
Simple loop is very simple that used to stare loop /*codea*/ end loop; in plsql. It iterative infinite until to set inside terimation condition.
DECLARE
i number := 0;
BEGIN
-- simple loop
LOOP
-- select query
select age into age from student where id = i++;
-- exit when
EXIT WHEN age <> 0;
END LOOP;
END;
While loop is loop that executeing contion set before it sytax is while condition loop /*code*/ end loop;.
DECLARE
fname varchar := null;
i number := 1;
BEGIN
-- while loop
WHILE fname = null LOOP
SELECT fname INTO fname FROM studnet WHERE id = i;
i := i + 1;
END LOOP;
dbms_output.put_line('stuent name is '||fname);
END;
In plsql for loop is loop work most with cursor looping it not like other language. It is sytax for vaiable in start..end loop /*code*/ end loop;. It vaiable data type not require to define in declare section.
BEGIN -- for loop FOR n IN 1..10 LOOP dbms_output.put_line(n); END LOOP; END;
Implicit cursor is default cursor in server that contextarea then query output return by that database. The default cursor name is sql and that inside multiple attribute like rowcount, found, notfound, isopen . that help to set condition to fetch data.
/*
default cursor = sql
attribute { %rowcount, %found, %nofound, %isopen }
*/
DECLARE
rollno student.id%type;
-- [ variable_name table_name.column_name%type; ]
-- auto set type according to table column
BEGIN
SELECT rollno INTO id FROM student WHERE fname like 'Kajal' or 'kajal';
-- sql%found : if rowcount not 0 return ture else flase
IF sql%found THEN
dbms_output.put_line('Kajal rollno is '||rollno);
ELSE
dbms_output.put_line('Kajal is bunk');
END IF;
END;
Expliciti cursor is that contextarea that declare by the programmer who create procedure.
/* explicet [ cursor ] 1. CURSOR cursor_name IS select_query; 2. OPEN cursor_name; 3. FETCH cursor_name INTO variable_name,...; 4. CLOSE cursor_name; */ DECLARE name t1.navme%type; id t1.name%type; CURSOR crsr IS SELECT * FROM t1 WHERE id = 1; BEGIN OPEN crsr; FETCH crsr INTO id, name; IF crsr%rowcount THEN dbms_output.put_line(id||' name is '||name); END IF; COLSE crsr; END;
In real life we can select mutiple data in cursor that we can use loops let do with practicale example.
/*
select all data with cursor
*/
DECLARE
mrow t1%rowtype; -- variable_name table_name%rowtype;
cursor crsr is select * from t1;
BEGIN
OPEN crsr;
LOOP;
FETCH crsr INTO mrow;
EXIT WHEN crsr%notfound;
dbms_output.put_line(mrow.id,mrow.fname);
END LOOP;
CLOSE crsr;
dbms_output.put_line('end loop');
END;
The procedure is the set instruction of query that execute with condition to fulfill the user requirement task. It is take arguments and return mutiple outputs and it return is optional.
/* Stored Named Procedure - CREATE keyword required - need name - store in database - use in call in side or other time */ CREATE Procedure dalo(x IN number,y IN varchar2) IS BEGIN INSERT INTO t1 VALUES(x,y); END dalo; /* Named Procedure - CREATE keyword not required - need name - not store in database - create to call start to end */ Procedure dikha IS DECLARE CURSOR crsr IS SELECT * FROM t1; BEGIN FOR mrow IN crsr LOOP; dbms_output.put_line(mrow.id||' - '||mrow.fname); END LOOP; END dikha; /* Unnamed Procedure - CREATE keyword not required - not need name - not store in database - not call */ BEGIN dalo(1,'lusanda'); dalo(2,'mayur'); dikha; END;
Function is the set instruction of query that execute with conditional follow and it reutnr not option and it return one data at time.
/* Stored Function */ CREATE Function plus(n1 IN number, n2 IN number) RETURN number IS BEGIN RETURN n1 + n2; END; /* call function */ BEGIN dbms_output.put_line(plus(5,1)); END;
The package is the collection of procedures and functions. All package have two pare one declare second is body. we can create, replace and drop it.
-- package declare
CREATE Package Agni AS
Procedure likh(x varchar2);
END Agni;
-- pacakge body
CREATE Package BODY Agni AS
Procedure likh(x varchar2) IS
BEGIN
dbms_output.put_line(x);
END;
END Agni;
/*
use of package
*/
BEGIN
dalo(3,'zoran');
dalo(4,'fredish');
Agni.likh('-- GET RED FOR FIRE PACKAGE --');
dikha;
END;
Exception is method to handle the mistake in database produce that helpful to avoid crash the site app or what you used. They are two type one named and other unnamed exception.
DECLARE
id number;
-- handle code error
mera_err EXCEPTION;
Pragma exception_init(mera_err,00947);
BEGIN
SELECT id INTO id FROM t1 WHERE id = 6;
dalo(5,'lopez'),(7,'jesika');
EXCEPTION
/*
named error
*/
WHEN no_data_found THEN
dbms_output.put_line(' kripya - kuchh dale ');
WHEN value_error THEN
dbms_output.put_line(' kripya - kishi or akar ka dale ');
WHEN to_many_rows THEN
dbms_output.put_line(' kripya - ekbar me ek nikale ');
/*
code error
*/
WHEN mera_err THEN
dbms_output.put_line(' kripya - ruk ke dale ');
/*
unseen error
*/
WHEN others THEN
dbms_output.put_line(' kya dal diya be ');
END;