SQL> --answer to the quiz question SQL> --read a number from keyboard and print -ve number, small number (0-100) and large number otherwise SQL> --handle two exceptions:onepredefıned exception ınvalıd_number and nonprefedıned exception ora-12504 SQL> declare 2 v number; 3 begin 4 . SQL> ed Wrote file afiedt.buf 1 declare 2 e exception; 3 pragma exception_init(e, -12154); 4 v number; 5 begin 6 v := &any_number; 7 if v > 100 then 8 dbms_output.put_line('bigggg'); 9 elsif v >=0 then 10 dbms_output.put_line('small'); 11 else 12 dbms_output.put_line('-ve'); 13 end if; 14 exception 15 when invalid_number or value_error then 16 dbms_output.put_line('value enetered has problems'); 17 when e then 18 dbms_output.put_line('connection error'); 19* end; 20 / Enter value for any_number: 1 old 6: v := &any_number; new 6: v := 1; PL/SQL procedure successfully completed. SQL> set serveroutput on SQL> / Enter value for any_number: 1 old 6: v := &any_number; new 6: v := 1; small PL/SQL procedure successfully completed. SQL> / Enter value for any_number: 110 old 6: v := &any_number; new 6: v := 110; bigggg PL/SQL procedure successfully completed. SQL> / Enter value for any_number: -9 old 6: v := &any_number; new 6: v := -9; -ve PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 e exception; 3 pragma exception_init(e, -12154); 4 v number; 5 begin 6 v := &any_number; 7 if v > 100 then 8 dbms_output.put_line('bigggg'); 9 elsif v >=0 then 10 dbms_output.put_line('small'); 11 else 12 dbms_output.put_line('-ve'); 13 end if; 14 exception 15 when invalid_number or value_error then 16 dbms_output.put_line('value enetered has problems'); 17 when e 18 dbms_output.put_line('connection error'); 19* end; SQL> / Enter value for any_number: 5 old 6: v := &any_number; new 6: v := 5; dbms_output.put_line('connection error'); * ERROR at line 18: ORA-06550: line 18, column 14: PLS-00103: Encountered the symbol "DBMS_OUTPUT" when expecting one of the following: . then or The symbol "then" was substituted for "DBMS_OUTPUT" to continue. SQL> show error No errors. SQL> ed Wrote file afiedt.buf 1 declare 2 e exception; 3 pragma exception_init(e1, -12154); 4 v number; 5 begin 6 v := &any_number; 7 if v > 100 then 8 dbms_output.put_line('bigggg'); 9 elsif v >=0 then 10 dbms_output.put_line('small'); 11 else 12 dbms_output.put_line('-ve'); 13 end if; 14 exception 15 when invalid_number or value_error then 16 dbms_output.put_line('value enetered has problems'); 17 when e then 18 dbms_output.put_line('connection error'); 19* end; SQL> / Enter value for any_number: 1 old 6: v := &any_number; new 6: v := 1; pragma exception_init(e1, -12154); * ERROR at line 3: ORA-06550: line 3, column 25: PLS-00109: unknown exception name 'E1' in PRAGMA EXCEPTION_INIT SQL> show err No errors. SQL> ed Wrote file afiedt.buf 1 declare 2 e exception; 3 pragma exception_init(e, -12154); 4 v number; 5 begin 6 v := &any_number; 7 if v > 100 then 8 dbms_output.put_line('bigggg'); 9 elsif v >=0 then 10 dbms_output.put_line('small'); 11 else 12 dbms_output.put_line('-ve'); 13 end if; 14 exception 15 when invalid_number or value_error then 16 dbms_output.put_line('value enetered has problems'); 17 when e then 18 dbms_output.put_line('connection error'); 19* end; SQL> / Enter value for any_number: 4 old 6: v := &any_number; new 6: v := 4; small PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 create or replace procedure print_hello 2 is 3 begin 4 dbms_output.put_line('hello there Vanessa'); 5 exceptions 6 when others then 7 dbms_output.put_line('Error:'||sqlerrm); 8* end; 9 / Warning: Procedure created with compilation errors. SQL> show error Errors for PROCEDURE PRINT_HELLO: LINE/COL ERROR -------- ----------------------------------------------------------------- 6/1 PLS-00103: Encountered the symbol "WHEN" when expecting one of the following: := . ( @ % ; 8/4 PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map SQL> ed Wrote file afiedt.buf 1 create or replace procedure print_hello 2 is 3 begin 4 dbms_output.put_line('hello there Vanessa'); 5 exception 6 when others then 7 dbms_output.put_line('Error:'||sqlerrm); 8* end; SQL> / Procedure created. SQL> / Procedure created. SQL> execute print_hello hello there Vanessa PL/SQL procedure successfully completed. SQL> --the procedures may be invoke/call/run the procedure from sql*plus by using the é SQL> --execute SQL> SQL> --a procedure may also be called from amy other pl/sql program by just using its name (and parameters if any) SQL> ed Wrote file afiedt.buf 1 begin 2 print_hello; 3* end; 4 / hello there Vanessa PL/SQL procedure successfully completed. SQL> -- write a procedure that accepts two numbers as inout and print tgheir summation SQL> ed Wrote file afiedt.buf 1 create or replace procedure add2nos(a number, b number) 2 is 3 c number; 4 begin 5 c := a+b; 6 dbms_output.put_line('the answer is '||c); 7 print_hello; 8* end; SQL> / Procedure created. SQL> execute add2nos(2,7); the answer is 9 hello there Vanessa PL/SQL procedure successfully completed. SQL> --by default (if you do not specify the parameter types pf the arguments) the artrguments are IN argument SQL> --thıs means we use call by value SQL> ed Wrote file afiedt.buf 1 create or replace procedure add2nos(a IN number, b number) 2 is 3 c number; 4 begin 5 c := a+b; 6 dbms_output.put_line('the answer is '||c); 7 print_hello; 8* end; SQL> / Procedure created. SQL> --write a function that accepts two numbers as input and return their multiplication SQL> ed Wrote file afiedt.buf 1 create or replace function mult(p_num1 number, p_num2 number) 2 return number 3 is 4 v_sum number; 5 begin 6 v_sum := p_num1*p_num2; 7 return v_sum; 8 exception 9 when others then 10 return -1; 11* end; 12 / Function created. SQL> declare 2 x number; 3 begin 4 . SQL> ed Wrote file afiedt.buf 1 declare 2 x number; 3 begin 4 x := mult(2,4); 5 dbms_output.put_line('The answer is '||x); 6* end; SQL> / The answer is 8 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 x number; 3 begin 4 x := mult(2,4); 5 dbms_output.put_line('The answer is '||x); 6 dbms_output.put_line('The answer is '||mult(9,9) ); 7* end; 8 / The answer is 8 The answer is 81 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 x number; 3 begin 4 x := mult(2,4); 5 dbms_output.put_line('The answer is '||x); 6 dbms_output.put_line('The answer is '||mult(9,9) ); 7 mult(9,9); 8* end; SQL> / mult(9,9); * ERROR at line 7: ORA-06550: line 7, column 2: PLS-00221: 'MULT' is not a procedure or is undefined ORA-06550: line 7, column 2: PL/SQL: Statement ignored SQL> ed Wrote file afiedt.buf 1 declare 2 x number; 3 begin 4 x := mult(2,4); 5 dbms_output.put_line('*The answer is '||x); 6 dbms_output.put_line('**The answer is '||mult(9,9) ); 7 x := mult(x,x); 8 dbms_output.put_line('***The answer is '||x); 9* end; 10 / *The answer is 8 **The answer is 81 ***The answer is 64 PL/SQL procedure successfully completed. SQL> --once you write functions you can also use them in sel statemenst! SQL> SELECT MULT(2,4); SELECT MULT(2,4) * ERROR at line 1: ORA-00923: FROM keyword not found where expected SQL> SELECT MULT(2,4) 2 FROM DUAL; MULT(2,4) ---------- 8 SQL> SELECT LAST_NAME, SALARY "SALARY $", MULT(SALARY,4) "SALARY TL" 2 FEOM EMPLOYEES 3 WHERE DEPARTMENT_ID=30; FEOM EMPLOYEES * ERROR at line 2: ORA-00923: FROM keyword not found where expected SQL> ED Wrote file afiedt.buf 1 SELECT LAST_NAME, SALARY "SALARY $", MULT(SALARY,4) "SALARY TL" 2 FROM EMPLOYEES 3* WHERE DEPARTMENT_ID=30 SQL> / LAST_NAME SALARY $ SALARY TL ------------------------- ---------- ---------- Raphaely 11000 44000 Khoo 3100 12400 Baida 2900 11600 Tobias 2800 11200 Himuro 2600 10400 Colmenares 2500 10000 6 rows selected. SQL> --IN PARAMETER MODE IS THE DEFAULT SQL> --SO FAR ALL PARAMETERS WE USED WERE IN PARAMETERS SQL> --IF YOU WANT TO IMPLEMEBNT TYHE MULT FUNCTION AS A PROCEDURE WITHOUT CHANGING THE CODE SQL> -- SO THAT THERE THREE PARAMETERS AND WE RETURN THE RESULT THROUGHT THE THIRSD PARAMETER SQL> DROP FUNCTION MULT; Function dropped. SQL> ED Wrote file afiedt.buf 1 create or replace PROCEDURE mult(p_num1 number, p_num2 number, P_RES out NUMBER) 2 is 3 v_RES number; 4 begin 5 v_RES := p_num1*p_num2; 6 exception 7 when others then 8 V_RES := -111; 9* end; 10 / Procedure created. SQL> ED Wrote file afiedt.buf 1 DECLARE 2 A NUMBER :=3; 3 B NUMBER := 4; 4 C NUMBER; 5 BEGIN 6 MULT(A,B,C); 7 DBMS_OUTPUT.PUT_LINE('THE RESULT IS '||C); 8* END; SQL> / THE RESULT IS PL/SQL procedure successfully completed. SQL> EED SP2-0042: unknown command "EED" - rest of line ignored. SQL> ED Wrote file afiedt.buf 1 create or replace PROCEDURE mult(p_num1 number, p_num2 number, P_RES out NUMBER) 2 is 3 v_RES number; 4 begin 5 v_RES := p_num1*p_num2; 6 P_RES := V_RES 7 exception 8 when others then 9 P_RES := -111; 10* end; SQL> / Warning: Procedure created with compilation errors. SQL> SH ERR SP2-0042: unknown command "SH ERR" - rest of line ignored. SQL> SHO ERR Errors for PROCEDURE MULT: LINE/COL ERROR -------- ----------------------------------------------------------------- 7/2 PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following: . ( * @ % & = - + ; < / > at in is mod remainder not rem <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset The symbol ";" was substituted for "EXCEPTION" to continue. SQL> ED Wrote file afiedt.buf 1 create or replace PROCEDURE mult(p_num1 number, p_num2 number, P_RES out NUMBER) 2 is 3 v_RES number; 4 begin 5 v_RES := p_num1*p_num2; 6 P_RES := V_RES; 7 exception 8 when others then 9 P_RES := -111; 10* end; SQL> / Procedure created. SQL> DECLARE 2 A NUMBER :=3; 3 B NUMBER := 4; 4 C NUMBER; 5 BEGIN 6 MULT(A,B,C); 7 DBMS_OUTPUT.PUT_LINE('THE RESULT IS '||C); 8 END; 9 / THE RESULT IS 12 PL/SQL procedure successfully completed. SQL> ED Wrote file afiedt.buf 1 CREATE OR REPLACE PROCEDURE ADD3(P IN OUT NUMBER) 2 IS 3 BEGIN 4 P := P+3; 5* END; SQL> / Procedure created. SQL> ED Wrote file afiedt.buf 1 DECLARE 2 X NUMBER := 7; 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE('BEFORE PROCEDURE CALL tHE VALUE OF X IS '||X); 5 ADD3(X); 6 DBMS_OUTPUT.PUT_LINE('AFTER PROCEDURE CALL tHE VALUE OF X IS '||X); 7* END; 8 / BEFORE PROCEDURE CALL tHE VALUE OF X IS 7 AFTER PROCEDURE CALL tHE VALUE OF X IS 10 PL/SQL procedure successfully completed. SQL> SPOOL OFF