Disadvantages of SQL: Limited Procedural Capabilities, Decreased Speed, and Error Display

1. Disadvantages Of SQL: SQL does not have any procedural capabilities. SQL statements are passed to Oracle engine one at a time. Each time the SQL statement is executed , the call is made to engine resources. This adds traffic on the network. Which decreases speed of data processing. While processing SQL statement if an error occurs, the Oracle engine displays its own error message.

2. Programming in Oracle with PL/SQL Procedural Language Extension to SQL

3. PL/SQL • Allows using general programming tools with SQL, for example: loops, conditions, functions, etc. • This allows a lot more freedom than general SQL. • We write PL/SQL code in a regular file, for example PL.sql, and load it with @PL in the sqlplus console.

4. PL/SQL Blocks • PL/SQL code is built of Blocks, with a unique structure. • There are two types of blocks in PL/SQL: • Anonymous Blocks: have no name (like scripts) • can be written and executed immediately in SQLPLUS • can be used in a trigger 2. Named Blocks: • Procedures • Functions

5. Anonymous Block Structure: DECLARE (optional) /* Here you declare the variables you will use in this block */ BEGIN (mandatory) /* Here you define the executable statements (what the block DOES!)*/ EXCEPTION (optional) /* Here you define the actions that take place if an exception is thrown during the run of this block */ END;(mandatory) / A correct completion of a block will generate the following message: PL/SQL procedure successfully completed Always put a new line with only a / at the end of a block! (This tells Oracle to run the block)

6. DECLARE Syntax Examples identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; Notice that PL/SQL includes all SQL types, and more… Declare birthday DATE; age NUMBER(2) NOT NULL := 27; name VARCHAR2(13) := 'Levi'; magic CONSTANT NUMBER := 77; valid BOOLEAN NOT NULL := TRUE;

7. Declaring Variables with the %TYPE Attribute Examples Accessing column sname in table Sailors DECLARE sname Sailors.sname%TYPE; fav_boat VARCHAR2(30); my_fav_boat fav_boat%TYPE := 'Pinta'; ... Accessing another variable

8. Declaring Variables with the %ROWTYPE Attribute Declare a variable with the type of a ROW of a table. And how do we access the fields in reserves_record? Accessing table Reserves reserves_record Reserves%ROWTYPE; reserves_record.sid:=9; Reserves_record.bid:=877;

9. Control Structures: • Conditional Control • Iterative Control • Sequential Control 1. Conditional Control: if <condition> then sequence of statements; end if;

10. IF-THEN-ELSIF Statements . . . IF rating > 7 THEN v_message := 'You are great'; ELSIF rating >= 5 THEN v_message := 'Not bad'; ELSE v_message := 'Pretty bad'; END IF; . . .

11. Write PL/SQL code that will except an account number from the user and debit amount of Rs. 2000 from a/c if the balance of a/c remains minimum Rs.500. The process is to be fired on Account table. account (ac_Id, Name, bal) Declare ac_bal number(10,2); ac_no varchar2(6); debit_amt number(5) :=2000; min_bal constant number(5,2):=500; Begin ac_no:=&ac_no; select bal into ac_bal from account where ac_id=ac_no; ac_bal:=ac_bal - debit_amt; if ac_bal >= min_bal then update accounts set bal=bal – debit_amt where ac_Id=ac_no; end if; End;

12. 2. Iterative Control: i) Simple loop ii) For loop iii) while loop • i) Simple loop loop sequence of statements; end loop; • ii) For loop for counter in [reverse]lowerbound .. Upperbound loop sequence of statements; end loop; • iii) while loop while <condition> loop <action> end loop;

13. Write a PL/SQL block to calculate the area of a circle for a value of radius varying from 3 to 7. Store the radius & the corresponding values of calculated area in a table, ‘Areas’. Declare pi constant number(4,2) := 3.14; radius number(5); area number(10,2); Begin radius:=3; while radius<=7 loop area:=pi*power(radius,2); insert into areas values(radius,area); end loop; End;

14. Write a PL/SQL block of code for inverting a number 5639 to 9365. Declare given_number(5):=‘5639’; str_len number(2); inverted_no varchar2(5); Begin str_len:=length(given_number); for cntr in reverse 1 .. Str_len loop inverted number := inverted_number||substr(given_number, cntr,1); end loop; dbms_output.put_line(‘The given no. is’ || given_number) dbms_output.put_line(‘The inverted number is’ || inverted_number) End;

15. Sequential Control: goto <code block name>;

16. Write PL/SQL block of code to achieve the following. If the price of product p1 is less than 4000, then change the price to 4000. Th price change is to be recorded in the old_price_table along with the product_no & the date on which the price was last changed. Declare selling_price number(10,2) Begin select sell_price into selling_price from product_master where product_no=‘p1’; if selling_price<4000 then goto add_old_price; else dbms_output.put_line(‘Current price’ || selling _price); end if <<Add_old_price>> update product_master set sell_price=4000 where product_no= ‘p1’ insert into old_price (prduct_no, date_change, old_price) values(‘p1’, sysdate, selling_price); dbms_output.put_line(‘the new price of p1 is 4000’); End;

17. Error Handling in PL/SQL: when <exception name> then user defined actions to be carried out; • Types Of Exceptions: 1. predefined exceptions: They are raised automatically by the system during run time. 2. user defined Exceptions: They must be raised explicitly using Raise statement.

18. Some Predefined Exceptions: 1. No_data_found 2. Cursor_already_open 3. Dup_val_on_index 4. Srorage_error 5. Program_error 6. Zero_divide 7. Invalid_cursor 8. Login_denied 9. Invalid_cursor 10. Too_many_rows

19. User Defined Exception: exception name <exception>; Raise Statement: raise <exception name>; • Declare exception name <exception>; Begin SQL sentense; if condition then raise <exception name>; end if; Exception when <exception name> then user defined actions to be carried out; End;

20. The X company wants to check qty_in_hand.if it is less than 500 the company wants to display msg. Declare lo_val exception; qty item_master.qty_in_hand%type; Begin select qty_in_hand into qty from item_master where itemcode=‘i100’; if qty<500 then raise lo_val; end if; Exception when lo_val then dbms_output.put_line=(‘Qty not enogh’); End;