PL/SQL is a block-structured language that enables developers to combine the power of SQL with procedural statements. All the statements of a block are passed to the oracle engine all at once which increases processing speed and decreases the traffic.
PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other.
Typically, each block performs a logical action in the program. A block has the following structure:
DECLARE declaration statements; BEGIN executable statements EXCEPTIONS exception handling statements END;
Now write a program in pl/sql which print a pyramid of string “GeeksforGeeks” shown below
Examples –
GeeksforGeeks GeeksforGeek GeeksforGee GeeksforGe GeeksforG Geeksfor Geeksfo Geeksf Geeks Geek Gee Ge G
Code –
--Declaration Block DECLARE -- declaration of string as Geeksforgeeks str VARCHAR2(100) := 'GeeksforGeeks' ;
-- len of string and num for no of rows
len VARCHAR2(100); num NUMBER(15); -- execution part begin BEGIN --calculating length of string num:=LENGTH(str); -- starting of while from
-- from num to till num>1 WHILE num>=1 LOOP
len:=SUBSTR(str,1,num);
num:=num-1;
DBMS_OUTPUT.PUT_LINE(len);
--ending of loop here END LOOP; -- end of beginning block END; -- End program |
Output:
GeeksforGeeks GeeksforGeek GeeksforGee GeeksforGe GeeksforG Geeksfor Geeksfo Geeksf Geeks Geek Gee Ge G
Disadvantages of SQL –
- SQL doesn’t provide the programmers with a technique of condition checking, looping, and branching.
- SQL statements are passed to the Oracle engine one at a time which increases traffic and decreases speed.
- SQL has no facility of error checking during manipulation of data.