SQL> set serveroutput on SQL> select upper(last_name) 2 from employees 3 where department_id=50; UPPER(LAST_NAME) ------------------------- ATKINSON BELL BISSOT BULL CABRIO CHUNG DAVIES DELLINGER DILLY EVERETT FEENEY UPPER(LAST_NAME) ------------------------- FLEAUR FRIPP GATES GEE GEONI GRANT JONES KAUFLING LADWIG LANDRY MALLIN UPPER(LAST_NAME) ------------------------- MARKLE MARLOW MATOS MCCAIN MIKKILINENI MOURGOS NAYER OCONNELL OLSON PATEL PERKINS UPPER(LAST_NAME) ------------------------- PHILTANKER RAJS ROGERS SARCHAND SEO STILES SULLIVAN TAYLOR VARGAS VOLLMAN WALSH UPPER(LAST_NAME) ------------------------- WEISS 45 rows selected. SQL> select avg(salary) 2 from employees 3 where department_id=50; AVG(SALARY) ----------- 3475.55556 SQL> create or replace function calculate(num1 number, num2 number) 2 return number 3 is 4 result number; 5 begin 6 result := num1* num2; 7 return result; 8 end; 9 / Function created. SQL> select calculate(4,5) 2 from dual; CALCULATE(4,5) -------------- 20 SQL> declare 2 mult number; 3 begin 4 mult := calculate(4,5); 5 dbms_output.put_line(mult); 6 end; 7 / 20 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 mult number; 3 begin 4 --mult := calculate(4,5); 5 dbms_output.put_line(calculate(7,5)); 6* end; SQL> / 35 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 mult number; 3 begin 4 --mult := calculate(4,5); 5 dbms_output.put_line(calculate(7,5)); 6* end; SQL> create or replace function calculate(num1 number, num2 number) 2 return number 3 is 4 result number; 5 begin 6 result := num1* num2; 7 return result; 8 end; 9 / Function created. SQL> ed Wrote file afiedt.buf 1 create or replace function exchange(dollars number) 2 return number 3 is 4 result number; 5 begin 6 result := dollars * 5.7; 7 return result; 8* end; SQL> / Function created. SQL> select exchange(&Enter_amount) 2 from dual; Enter value for enter_amount: 50 old 1: select exchange(&Enter_amount) new 1: select exchange(50) EXCHANGE(50) ------------ 285 SQL> ed Wrote file afiedt.buf 1 declare 2 r number :=&Enter_dollar; 3 amount number; 4 begin 5 amount:=exchange(r); 6 dbms_output.put_line('you Have' || amount || 'TL'); 7* end; 8 / Enter value for enter_dollar: 30 old 2: r number :=&Enter_dollar; new 2: r number :=30; you Have171TL PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 r number :=&Enter_dollar; 3 amount number; 4 begin 5 amount:=exchange(r); 6 dbms_output.put_line('you Have ' || amount || 'TL'); 7* end; SQL> / Enter value for enter_dollar: 23 old 2: r number :=&Enter_dollar; new 2: r number :=23; you Have 131.1TL PL/SQL procedure successfully completed. SQL> select last_name, salary , exchange(salary) 2 from employees 3 where department_id=50; LAST_NAME SALARY EXCHANGE(SALARY) ------------------------- ---------- ---------------- Weiss 8000 45600 Fripp 8200 46740 Kaufling 7900 45030 Vollman 6500 37050 Mourgos 5800 33060 Nayer 3200 18240 Mikkilineni 2700 15390 Landry 2400 13680 Markle 2200 12540 Bissot 3300 18810 Atkinson 2800 15960 LAST_NAME SALARY EXCHANGE(SALARY) ------------------------- ---------- ---------------- Marlow 2500 14250 Olson 2100 11970 Mallin 3300 18810 Rogers 2900 16530 Gee 2400 13680 Philtanker 2200 12540 Ladwig 3600 20520 Stiles 3200 18240 Seo 2700 15390 Patel 2500 14250 Rajs 3500 19950 LAST_NAME SALARY EXCHANGE(SALARY) ------------------------- ---------- ---------------- Davies 3100 17670 Matos 2600 14820 Vargas 2500 14250 Taylor 3200 18240 Fleaur 3100 17670 Sullivan 2500 14250 Geoni 2800 15960 Sarchand 4200 23940 Bull 4100 23370 Dellinger 3400 19380 Cabrio 3000 17100 LAST_NAME SALARY EXCHANGE(SALARY) ------------------------- ---------- ---------------- Chung 3800 21660 Dilly 3600 20520 Gates 2900 16530 Perkins 2500 14250 Bell 4000 22800 Everett 3900 22230 McCain 3200 18240 Jones 2800 15960 Walsh 3100 17670 Feeney 3000 17100 OConnell 2600 14820 LAST_NAME SALARY EXCHANGE(SALARY) ------------------------- ---------- ---------------- Grant 2600 14820 45 rows selected. SQL> l 1 select last_name, salary , exchange(salary) 2 from employees 3* where department_id=50 SQL> desc employees Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) SQL> create or replace function dept_avg( deptid number) 2 return number 3 is 4 v number; 5 begin 6 select avg(salary) into v 7 from employees 8 where department_id=deptid; 9 return v; 10 / Warning: Function created with compilation errors. SQL> ed Wrote file afiedt.buf 1 declare 2 v number; 3 begin 4 select avg(salary) into v 5 from employees 6 where department_id=50; 7* dbms_output.put_line(v); SQL> / dbms_output.put_line(v); * ERROR at line 7: ORA-06550: line 7, column 24: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge SQL> ed Wrote file afiedt.buf 1 declare 2 v number; 3 begin 4 select avg(salary) into v 5 from employees 6 where department_id=50; 7* dbms_output.put_line(v); SQL> ed Wrote file afiedt.buf 1 declare 2 v number; 3 begin 4 select avg(salary) into v 5 from employees 6 where department_id=50; 7 dbms_output.put_line(v); 8* end; SQL> / 3475.555555555555555555555555555555555556 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 create or replace function dept_avg( deptid number) 2 return number 3 is 4 v number; 5 begin 6 select avg(salary) into v 7 from employees 8 where department_id=50; 9 return v; 10* end; SQL> / Function created. SQL> ed Wrote file afiedt.buf 1 create or replace function dept_avg( deptid number) 2 return number 3 is 4 v number; 5 begin 6 select avg(salary) into v 7 from employees 8 where department_id=deptid; 9 return v; 10* end; SQL> / Function created. SQL> ed Wrote file afiedt.buf 1 create or replace function dept_avg( deptid number) 2 return number 3 is 4 v number; 5 begin 6 select avg(salary) into v 7 from employees 8 where department_id=deptid; 9 return v; 10* end; SQL> select last_name, dept_avg(department_id) 2 from employees 3 where department_id<50; LAST_NAME DEPT_AVG(DEPARTMENT_ID) ------------------------- ----------------------- Whalen 4400 Hartstein 9500 Fay 9500 Raphaely 4150 Khoo 4150 Baida 4150 Tobias 4150 Himuro 4150 Colmenares 4150 Mavris 6500 10 rows selected. SQL> ed Wrote file afiedt.buf 1 select last_name, department_id ,dept_avg(department_id) 2 from employees 3* where department_id<50 SQL> / LAST_NAME DEPARTMENT_ID DEPT_AVG(DEPARTMENT_ID) ------------------------- ------------- ----------------------- Whalen 10 4400 Hartstein 20 9500 Fay 20 9500 Raphaely 30 4150 Khoo 30 4150 Baida 30 4150 Tobias 30 4150 Himuro 30 4150 Colmenares 30 4150 Mavris 40 6500 10 rows selected. SQL> spool off