SQL> --procedures SQL> conn user27@orcl Connected. SQL> create or replace procedure greet 2 is 3 dbms_output.put_line('hello'); 4 end; 5 / Warning: Procedure created with compilation errors. SQL> sho err Errors for PROCEDURE GREET: LINE/COL ERROR -------- ----------------------------------------------------------------- 3/13 PLS-00103: Encountered the symbol "." when expecting one of the following: constant exception table long double ref char time timestamp interval date binary national character nchar The symbol "" was substituted for "." to continue. 4/1 PLS-00103: Encountered the symbol "END" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior SQL> show error Errors for PROCEDURE GREET: LINE/COL ERROR -------- ----------------------------------------------------------------- 3/13 PLS-00103: Encountered the symbol "." when expecting one of the following: constant exception table long double ref char time timestamp interval date binary national character nchar The symbol "" was substituted for "." to continue. 4/1 PLS-00103: Encountered the symbol "END" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior SQL> ed Wrote file afiedt.buf 1 create procedure greet 2 is 3 begin 4 dbms_output.put_line('hello'); 5* end; SQL> SQL> / create procedure greet * ERROR at line 1: ORA-00955: name is already used by an existing object SQL> -- if we want to overwrite the procedure, we must say so SQL> ed Wrote file afiedt.buf 1 create or replace procedure greet 2 is 3 begin 4 dbms_output.put_line('hello'); 5* end; SQL> / Procedure created. SQL> execute greet PL/SQL procedure successfully completed. SQL> set serveroutput on SQL> execute greet hello PL/SQL procedure successfully completed. SQL> begin 2 greet 3 end; 4 / end; * ERROR at line 3: ORA-06550: line 3, column 1: PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( @ % ; The symbol ";" was substituted for "END" to continue. SQL> begin 2 greet; 3 end; 4 / hello PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 for i in 1 .. 5 3 loop 4 greet; 5 end loop; 6* end; SQL> / hello hello hello hello hello PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 for i in 1 .. 5 3 loop 4 greet; 5 end loop; 6* end; SQL> ed Wrote file afiedt.buf 1 --greet procedure will accept a name as parameter an d greet that person 2 create or replace procedure greet( p_name varchar2(50) ) 3 is 4 begin 5 dbms_output.put_line('hello '||p_name); 6* end; SQL> / Warning: Procedure created with compilation errors. SQL> sho err 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/33 PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue. SQL> ed Wrote file afiedt.buf 1 --greet procedure will accept a name as parameter an d greet that person 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> execute greet('ali') hello ali PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 for i in 1 .. 5 3 loop 4 greet(i); 5 end loop; 6* end; SQL> / hello 1 hello 2 hello 3 hello 4 hello 5 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 for i in 1 .. 5 3 loop 4 greet(i); 5 end loop; 6* end; SQL> ed Wrote file afiedt.buf 1 create or replace procedure greet( p_name varchar2 ) 2 declare 3 begin 4 dbms_output.put_line('hello '||p_name); 5* end; SQL> / Warning: Procedure created with compilation errors. SQL> sho err Errors for PROCEDURE GREET: LINE/COL ERROR -------- ----------------------------------------------------------------- 2/2 PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: ; is with authid as cluster order using external deterministic parallel_enable pipelined result_cache accessible 5/5 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 create or replace procedure greet( p_name varchar2 ) 2 as 3 begin 4 dbms_output.put_line('hello '||p_name); 5* end; SQL> / Procedure created. SQL> ed Wrote file afiedt.buf 1 create or replace procedure greet( p_name1 varchar2, p_name2 varchar2) 2 as 3 begin 4 dbms_output.put_line('hello '||p_name1||p_name2); 5* end; SQL> / Procedure created. SQL> execute greet('ali','ayse') hello aliayse PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 create or replace procedure greet( p_name employees.last_name%type) 2 as 3 begin 4 dbms_output.put_line('hello '||p_name); 5* end; SQL> / Procedure created. SQL> execute greet('ali') hello ali PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v employees.last_name%type; 3 begin 4 select last_name into v 5 from employees 6 where employee_id=100; 7 greet(v); 8* end; 9 / hello King PL/SQL procedure successfully completed. SQL> --write a function to convert euro to tl SQL> --assume 1euro is 3,85 tl SQL> select 2*3,85 2 from dual; 2*3 85 ---------- ---------- 6 85 SQL> select 2*3.85 2 from dual; 2*3.85 ---------- 7.7 SQL> ed Wrote file afiedt.buf 1 create or replace function convrt(p_amt number) 2 return number 3 is 4 v_newamt number; 5 begin 6 v_newamt := p_amt*3.85; 7 return v_newamt; 8* end; SQL> / Function created. SQL> ed Wrote file afiedt.buf 1 declare 2 v number; 3 begin 4 v := convrt(100); 5 dbms_output.put_line('100 euro in tl is '||v); 6* end; SQL> / 100 euro in tl is 385 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v number; 3 begin 4 -- v := 5 convrt(100); 6 -- dbms_output.put_line('100 euro in tl is '||v); 7* end; SQL> / convrt(100); * ERROR at line 5: ORA-06550: line 5, column 1: PLS-00221: 'CONVRT' is not a procedure or is undefined ORA-06550: line 5, column 1: PL/SQL: Statement ignored SQL> ed Wrote file afiedt.buf 1 declare 2 v number; 3 begin 4 -- v :=convrt(100); 5 dbms_output.put_line('100 euro in tl is '||convrt(100)); 6* end; 7 / 100 euro in tl is 385 PL/SQL procedure successfully completed. SQL> --WE MUST USE THE VALUE(s) RETURNED BY THER FUNCTIONS SQL> --OTHERWISE WE GET THE FOLLOWING ERROR SQL> -- PLS-00221: 'CONVRT' is not a procedure or is undefined SQL> --FUNCTIONS CAN ALSO BE USED IN SQL COMMANDS SQL> --SELECT CLAUSE, WHERE, HAVING SQL> --.... SQL> SELECT CONVRT(10) 2 FROM DUAL; CONVRT(10) ---------- 38.5 SQL> --ASSUME THE SALARY COLUMN IN THE EMPLOYEES TABLE ÝS ÝN EUROS SQL> --LIST NAME OF THE EMPLOYEES IN UPPERCASE AND SALARY IN TL FOR DEPT 30 SQL> SELECT EMPLOYEE_ID,LAST_NAME,DEPARTMENT_ID,SALARY 2 FROM EMPLOYEES 3 WHERE DEPARTMENT_ID=30; EMPLOYEE_ID LAST_NAME DEPARTMENT_ID SALARY ----------- ------------------------- ------------- ---------- 114 Raphaely 30 11000 115 Khoo 30 3100 116 Baida 30 2900 117 Tobias 30 2800 118 Himuro 30 2600 119 Colmenares 30 2500 6 rows selected. SQL> --SALARY ÝS ÝN EURO SQL> SELECT UPPER(LAST_NAME),CONVRT(SALARY) 2 FROM EMPLOYEES 3 WHERE DEPARTMENT_ID=30; UPPER(LAST_NAME) CONVRT(SALARY) ------------------------- -------------- RAPHAELY 42350 KHOO 11935 BAIDA 11165 TOBIAS 10780 HIMURO 10010 COLMENARES 9625 6 rows selected. SQL> WRÝTE AN SQL STATEMENT TO LIST FOR DEPT 30, EMPLOYEE NAME TOGETHER WITH MANAGER NAME SP2-0734: unknown command beginning "WRÝTE AN S..." - rest of line ignored. 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 117 Tobias 114 114 Raphaely 116 Baida 114 114 Raphaely 115 Khoo 114 114 Raphaely 6 rows selected. SQL> ED Wrote file afiedt.buf 1 --THIS FUNCTION ACCEPTS AN EMPLOYEE ID AND RETURNS LAST_NAME OF THE EMPLOYEE 2 CREATE OR REPLACE FUNCTION GETNAME(P_EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE) 3 RETURN EMPLOYEES.LAST_NAME%TYPE 4 IS 5 V_LAST_NAME EMPLOYEES.LAST_NAME%TYPE; 6 BEGIN 7 SELECT LAST_NaME INTO V_LAST_NAME 8 FROM EMPLOYEES 9 WHERE EMPLOYEE_ID= P_EMPLOYEE_ID; 10 RETURN V_LAST_NAME; 11 EXCEPTION 12 WHEN NO_DATA_FOUND THEN 13 RETURN 'NO ONE'; 14 WHEN OTHERS THEN 15 RETURN 'ERROR'; 16* END; 17 / Function created. 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> SELECT GETNAME(202) 2 FROM DUAL; GETNAME(202) -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Fay SQL> SELECT GETNAME(2) 2 FROM DUAL; GETNAME(2) -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NO ONE SQL> SET LINESIZE 500 SQL> / GETNAME(2) -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NO ONE 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> SET LINESIZE 1000 SQL> / 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> --FOR DEPARTMENT 30, LIST NAME OF THE EMPLOYEE TOGETHER WITH THE TOTAL NUMBER OF EMPLOYEES IN THAT DEPARTMENT SQL> --LIST NAME OF THE EMPLOYEE TOGETHER WITH THE TOTAL NUMBER OF EMPLOYEESWHO WORK IN THE SAME DEPT AS THAT EMPLOYEE SQL> SELECT EMPLOYEE_ID,LAST_NAME,COUNT(*) 2 FROM EMPLOYEES 3 WHERE DEPARTMENT_ID=30; SELECT EMPLOYEE_ID,LAST_NAME,COUNT(*) * ERROR at line 1: ORA-00937: not a single-group group function SQL> CREATE ORF REPLACE FUNCTION COUNT_EMPS(P_DEPARTMENT_ID EMPLOYEESÇDEPATRMENT_ID%TYPE= 2 Ç 3 . SQL> ED Wrote file afiedt.buf 1 CREATE OR REPLACE FUNCTION COUNT_EMPS(P_DEPARTMENT_ID EMPLOYEES.DEPATRMENT_ID%TYPE) 2 RETURN NUMBER 3 IS 4 V_COUNT NUMBER; 5 BEGIN 6 SELECT COUNT(*) INTO V_COUNT 7 FROM EMPLOYEES 8 WHERE DEPARTMENT_ID=P_DEPARTMENT_ID; 9 RETURN V_COUNT; 10 EXCEPTION 11 WHEN OTHERS THEN 12 RETURN -1; 13* END; 14 / Warning: Function created with compilation errors. SQL> SHO ERR Errors for FUNCTION COUNT_EMPS: LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 PL/SQL: Compilation unit analysis terminated 1/48 PLS-00302: component 'DEPATRMENT_ID' must be declared SQL> ED Wrote file afiedt.buf 1 CREATE OR REPLACE FUNCTION COUNT_EMPS(P_DEPARTMENT_ID EMPLOYEES.DEPARTMENT_ID%TYPE) 2 RETURN NUMBER 3 IS 4 V_COUNT NUMBER; 5 BEGIN 6 SELECT COUNT(*) INTO V_COUNT 7 FROM EMPLOYEES 8 WHERE DEPARTMENT_ID =P_DEPARTMENT_ID; 9 RETURN V_COUNT; 10 EXCEPTION 11 WHEN OTHERS THEN 12 RETURN -1; 13* END; SQL> / Function created. SQL> SELECT COUNT_EMPS(30) 2 FROM DUAL; COUNT_EMPS(30) -------------- 6 SQL> SELECT COUNT_EMPS(10) 2 FROM DUAL; COUNT_EMPS(10) -------------- 1 SQL> DESC DUAL Name Null? Type ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ DUMMY VARCHAR2(1) SQL> SELECT 8*8 2 FROM DUAL; 8*8 ---------- 64 SQL> SELECT * FROM DUAL; D - X SQL> --DUAL IS A DUMMY TABLE SQL> SELECT 'HELLO' 2 FROM DUAL; 'HELL ----- HELLO SQL> SELECT 'HELLO' 2 FROM REGIONS; 'HELL ----- HELLO HELLO HELLO HELLO SQL> --LIST NAME OF THE EMPLOYEE TOGETHER WITH THE TOTAL NUMBER OF EMPLOYEESWHO WORK IN THE SAME DEPT SQL> SELECT DEPARTMENT_ID,LAST_NAME, COUNT_EMPS(DEPARTMENT_ID) 2 FROM EMPLOYEES 3 ORDER BY DEPARTMENT_ID; DEPARTMENT_ID LAST_NAME COUNT_EMPS(DEPARTMENT_ID) ------------- ------------------------- ------------------------- 10 Whalen 1 20 Hartstein 2 20 Fay 2 30 Raphaely 6 30 Khoo 6 30 Baida 6 30 Tobias 6 30 Himuro 6 30 Colmenares 6 40 Mavris 1 50 Weiss 45 50 Fripp 45 50 Kaufling 45 50 Vollman 45 50 Mourgos 45 50 Nayer 45 50 Mikkilineni 45 50 Landry 45 50 Markle 45 50 Bissot 45 50 Atkinson 45 50 Marlow 45 50 Olson 45 50 Mallin 45 50 Rogers 45 50 Gee 45 50 Philtanker 45 50 Ladwig 45 50 Stiles 45 50 Seo 45 50 Patel 45 50 Rajs 45 50 Davies 45 50 Matos 45 50 Vargas 45 50 Taylor 45 50 Fleaur 45 50 Sullivan 45 50 Geoni 45 50 Sarchand 45 50 Bull 45 50 Dellinger 45 50 Cabrio 45 50 Chung 45 50 Dilly 45 50 Gates 45 50 Perkins 45 50 Bell 45 50 Everett 45 50 McCain 45 50 Jones 45 50 Walsh 45 50 Feeney 45 50 OConnell 45 50 Grant 45 60 Hunold 5 60 Ernst 5 60 Austin 5 60 Pataballa 5 60 Lorentz 5 70 Baer 1 80 Russell 34 80 Partners 34 80 Errazuriz 34 80 Cambrault 34 80 Zlotkey 34 80 Tucker 34 80 Bernstein 34 80 Hall 34 80 Olsen 34 80 Cambrault 34 80 Tuvault 34 80 King 34 80 Sully 34 80 McEwen 34 80 Smith 34 80 Doran 34 80 Sewall 34 80 Vishney 34 80 Greene 34 80 Marvins 34 80 Lee 34 80 Ande 34 80 Banda 34 80 Ozer 34 80 Bloom 34 80 Fox 34 80 Smith 34 80 Bates 34 80 Kumar 34 80 Abel 34 80 Hutton 34 80 Taylor 34 80 Livingston 34 80 Johnson 34 90 King 3 90 Kochhar 3 90 De Haan 3 100 Greenberg 6 100 Faviet 6 100 Chen 6 100 Sciarra 6 100 Urman 6 100 Popp 6 110 Higgins 2 110 Gietz 2 Grant 0 107 rows selected. SQL> SPOOL OFF