SQL> set serveroutput on SQL> --wr'te a funct'on that converts euro to tl SQL> --assume 1euro=3,85 tl SQL> desc empğloyees ERROR: ORA-04043: object empğloyees does not exist 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> --assume employee salary is in euros SQL> --write a function to convert euro to tl SQL> select 4*0,5 2 from dual; 4*0 5 ---------- ---------- 0 5 SQL> select 4*0.5 2 from dual; 4*0.5 ---------- 2 SQL> ed Wrote file afiedt.buf 1 --function accepts a paremeter currency in euro 2 --returns currency in tl by multiplying the euro value with 3.85 3 create or replace function converte2tl(p_euro number) 4 return number 5 is 6 v_tl number; 7 begin 8 v_tl := p_euro*3.85; 9* end; SQL> / Function created. SQL> select converte2tl(5) 2 from dual; select converte2tl(5) * ERROR at line 1: ORA-06503: PL/SQL: Function returned without value ORA-06512: at "USER15.CONVERTE2TL", line 7 SQL> create function converte2tl(p_euro number) 2 return number 3 is 4 v_tl number; 5 begin 6 v_tl := p_euro*3.85; 7 return v_tl; 8 end; 9 / create function converte2tl(p_euro number) * ERROR at line 1: ORA-00955: name is already used by an existing object SQL> create or replace function converte2tl(p_euro number) 2 return number 3 is 4 v_tl number; 5 begin 6 v_tl := p_euro*3.85; 7 return v_tl; 8 end; 9 / Function created. SQL> select converte2tl(10) 2 from dual; CONVERTE2TL(10) --------------- 38.5 SQL> --in sql you used some functions such as SQL> -- average, max, count, min, to_char,nvl,upper,lower,initcap,rtrim ... SQL> SQL> --list last name and salary of all emps ın dept 30 SQL> select last_name, salary 2 from employees 3 where department_id=30; LAST_NAME SALARY ------------------------- ---------- Raphaely 11000 Khoo 3100 Baida 2900 Tobias 2800 Himuro 2600 Colmenares 2500 6 rows selected. SQL> --list last name (in upper case) and salary in tl for all emps in dept 30 SQL> select upper(last_name),converte2tl(SALARY) 2 from employees 3 where department_id=30; UPPER(LAST_NAME) CONVERTE2TL(SALARY) ------------------------- ------------------- RAPHAELY 42350 KHOO 11935 BAIDA 11165 TOBIAS 10780 HIMURO 10010 COLMENARES 9625 6 rows selected. 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> --for department 30, list the employee names together wıth theor managers name SQL> select EMPLOYEE_ID, LAST_NAME ,MANAGER_ID 2 . SQL> ed Wrote file afiedt.buf 1 select e.EMPLOYEE_ID, e.LAST_NAME,e.MANAGER_ID, m.employee_id,m.last_name 2 from employees e, employees m 3* where e.MANAGER_ID = m.employee_id and e.department_id=30 SQL> / EMPLOYEE_ID LAST_NAME MANAGER_ID EMPLOYEE_ID ----------- ------------------------- ---------- ----------- LAST_NAME ------------------------- 114 Raphaely 100 100 King 119 Colmenares 114 114 Raphaely 118 Himuro 114 114 Raphaely EMPLOYEE_ID LAST_NAME MANAGER_ID EMPLOYEE_ID ----------- ------------------------- ---------- ----------- LAST_NAME ------------------------- 117 Tobias 114 114 Raphaely 116 Baida 114 114 Raphaely 115 Khoo 114 114 Raphaely 6 rows selected. SQL> set linesize 200 SQL> set pagesize 150 SQL> r 1 select e.EMPLOYEE_ID, e.LAST_NAME,e.MANAGER_ID, m.employee_id,m.last_name 2 from employees e, employees m 3* where e.MANAGER_ID = m.employee_id and e.department_id=30 EMPLOYEE_ID LAST_NAME MANAGER_ID EMPLOYEE_ID LAST_NAME ----------- ------------------------- ---------- ----------- ------------------------- 114 Raphaely 100 100 King 119 Colmenares 114 114 Raphaely 118 Himuro 114 114 Raphaely 117 Tobias 114 114 Raphaely 116 Baida 114 114 Raphaely 115 Khoo 114 114 Raphaely 6 rows selected. SQL> --write a function that accepts an employee is and returns last name of the employee SQL> --if there is no such employee return 'no one' SQL> --in case of any error return 'error' SQL> --*****write a function that accepts an employee id SQL> create or replace function getname(p_employee_id employees.employee_id%type) 2 return varchar2 3 is 4 . SQL> ed Wrote file afiedt.buf 1 create or replace function getname(p_employee_id employees.employee_id%type) 2 return varchar2 3 v_last_name employees.last_name%type; 4 begin 5 select last_name into v_last_name 6 from employees 7 where employee_id=p_employee_id; 8 return v_last_name; 9 exception 10 when no_data_found then 11 return 'no one'; 12 when others then 13 return 'error'; 14* end; 15 / Warning: Function created with compilation errors. SQL> show errors Errors for FUNCTION GETNAME: LINE/COL ERROR -------- ----------------------------------------------------------------- 3/3 PLS-00103: Encountered the symbol "V_LAST_NAME" when expecting one of the following: . @ % ; is authid as cluster order using external character deterministic parallel_enable pipelined aggregate result_cache accessible The symbol "is" was substituted for "V_LAST_NAME" to continue. SQL> sho err Errors for FUNCTION GETNAME: LINE/COL ERROR -------- ----------------------------------------------------------------- 3/3 PLS-00103: Encountered the symbol "V_LAST_NAME" when expecting one of the following: . @ % ; is authid as cluster order using external character deterministic parallel_enable pipelined aggregate result_cache accessible The symbol "is" was substituted for "V_LAST_NAME" to continue. SQL> ed Wrote file afiedt.buf 1 create or replace function getname(p_employee_id employees.employee_id%type) 2 return varchar2 3 is 4 v_last_name employees.last_name%type; 5 begin 6 select last_name into v_last_name 7 from employees 8 where employee_id=p_employee_id; 9 return v_last_name; 10 exception 11 when no_data_found then 12 return 'no one'; 13 when others then 14 return 'error'; 15* end; SQL> / Function created. SQL> select e.EMPLOYEE_ID, e.LAST_NAME,e.MANAGER_ID, m.employee_id,m.last_name 2 from employees e, employees m 3 where e.MANAGER_ID = m.employee_id and e.department_id=30; EMPLOYEE_ID LAST_NAME MANAGER_ID EMPLOYEE_ID LAST_NAME ----------- ------------------------- ---------- ----------- ------------------------- 114 Raphaely 100 100 King 119 Colmenares 114 114 Raphaely 118 Himuro 114 114 Raphaely 117 Tobias 114 114 Raphaely 116 Baida 114 114 Raphaely 115 Khoo 114 114 Raphaely 6 rows selected. SQL> select employee_id,last_name,manager_id, getname(manager_id) 2 from employees 3 where department_id=30; EMPLOYEE_ID LAST_NAME MANAGER_ID ----------- ------------------------- ---------- GETNAME(MANAGER_ID) -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 114 Raphaely 100 King 115 Khoo 114 Raphaely 116 Baida 114 Raphaely 117 Tobias 114 Raphaely 118 Himuro 114 Raphaely 119 Colmenares 114 Raphaely 6 rows selected. SQL> --so far we called the functions from select statement SQL> --a function may be used in select clause, where clause or having clause, or groupo by clause SQL> --a function may also be called in another pl/sql program SQL> --when callıng a function in this way, WE MUST ASSIGN THE FUNCTION TO A VARIABLE SQL> ED Wrote file afiedt.buf 1 DECLARE 2 V_NAME VARCHAR2(50) 3 BEGIN 4 V_NAME :=GETNAME(114); 5 DBMS_OUTPUT.PUT_LINE('NAME OF 114 IS '||V_NAME); 6 V_NAME :=GETNAME(0); 7 DBMS_OUTPUT.PUT_LINE('NAME OF 0 IS '||V_NAME); 8* END; 9 / BEGIN * ERROR at line 3: ORA-06550: line 3, column 1: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following: := ; not null default character The symbol ";" was substituted for "BEGIN" to continue. SQL> ED Wrote file afiedt.buf 1 DECLARE 2 V_NAME VARCHAR2(50); 3 BEGIN 4 V_NAME :=GETNAME(114); 5 DBMS_OUTPUT.PUT_LINE('NAME OF 114 IS '||V_NAME); 6 V_NAME :=GETNAME(0); 7 DBMS_OUTPUT.PUT_LINE('NAME OF 0 IS '||V_NAME); 8* END; SQL> / NAME OF 114 IS Raphaely NAME OF 0 IS no one PL/SQL procedure successfully completed. SQL> ED Wrote file afiedt.buf 1 DECLARE 2 V_NAME VARCHAR2(50); 3 BEGIN 4 GETNAME(114); 5 V_NAME :=GETNAME(0); 6 DBMS_OUTPUT.PUT_LINE('NAME OF 0 IS '||V_NAME); 7* END; 8 / GETNAME(114); * ERROR at line 4: ORA-06550: line 4, column 2: PLS-00221: 'GETNAME' is not a procedure or is undefined ORA-06550: line 4, column 2: PL/SQL: Statement ignored SQL> A FUNCTION MUST BE ASSIGNED TO A VARIABLE 4* GETNAME(114);FUNCTION MUST BE ASSIGNED TO A VARIABLE SQL> ED Wrote file afiedt.buf 1 --PROCEDURE THAT ACCEPTS YOUR NAME AS A PARAMETER AND GREETS YOU 2 CREATE OR REPLACE PROCEDURE GREET(P_NAME VARCHAR2(50)) 3 IS 4 BEGIN 5 DBMS_OUTPUT.PUT_LINE('HELLO '||P_NAME); 6* END; 7 / Warning: Procedure created with compilation errors. SQL> SHOW ERR Usage: SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA SOURCE | JAVA CLASS } [schema.]name] SQL> SHO ERR Usage: SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA SOURCE | JAVA CLASS } [schema.]name] SQL> show error Usage: SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA SOURCE | JAVA CLASS } [schema.]name] SQL> sho err procedure greet Errors for PROCEDURE GREET: LINE/COL ERROR -------- ----------------------------------------------------------------- 1/32 PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue. SQL> e3d SP2-0042: unknown command "e3d" - rest of line ignored. SQL> ed Wrote file afiedt.buf 1 --PROCEDURE THAT ACCEPTS YOUR NAME AS A PARAMETER AND GREETS YOU 2 CREATE OR REPLACE PROCEDURE GREET(P_NAME VARCHAR2) 3 IS 4 BEGIN 5 DBMS_OUTPUT.PUT_LINE('HELLO '||P_NAME); 6* END; SQL> / Procedure created. SQL> begin 2 greet('alex'); 3 end; 4 / HELLO alex PL/SQL procedure successfully completed. SQL> execute greet('tomiwa') HELLO tomiwa PL/SQL procedure successfully completed. SQL> spool off