SQL> --using different parameter modes SQL> --IN parameter mode is the default mode SQL> --it is implemented in the same way as "call by value" in C SQL> --up until now we used IN parameter mode SQL> --IF you do NOT specify the mode of a parameter by default it is IN SQL> --ex: write a procedure that accepts a gender value as input and prints SQL> --"hello sir" if gender is male, "hello madam" if gender is female and SQL> --"hello mate" otherwise SQL> --gendre parameter will be sent as input to the procedure SQL> --it will be a single character SQL> set serveroutput on SQL> create or replace procedure greetings(p_gender IN char) 2 is 3 begin 4 if p_gender in ('F','f') then 5 dbms_output.put_line('Hello Sir!'); 6 elsif p_gender in ('M','m') then 7 dbms_output.put_line('Hello Sir!'); 8 else 9 dbms_output.put_line('Hello mate!'); 10 end if; 11 end; 12 / Procedure created. SQL> execute greetings('F') Hello Sir! PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 create or replace procedure greetings(p_gender IN char) 2 is 3 begin 4 if p_gender in ('F','f') then 5 dbms_output.put_line('Hello Madam!'); 6 elsif p_gender in ('M','m') then 7 dbms_output.put_line('Hello Sir!'); 8 else 9 dbms_output.put_line('Hello mate!'); 10 end if; 11* end; SQL> / Procedure created. SQL> declare 2 v_gender :='m'; 3 v_gender2; 4 v_gender3 :='a'; 5 begin 6 dbms_output.put_line('call#1'); 7 greetings('F'); 8 dbms_output.put_line('call#2'); 9 greetings(v_gender ); 10 dbms_output.put_line('call#3'); 11 greetings(v_gender3); 12 dbms_output.put_line('call#4'); 13 greetings(v_gender2); 14 end; 15 / v_gender :='m'; * ERROR at line 2: ORA-06550: line 2, column 11: 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. ORA-06550: line 3, column 11: 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 ORA-06550: line 14, column 4: 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 SQL> ed Wrote file afiedt.buf 1 declare 2 v_gender char(1) :='m'; 3 v_gender2 char(1); 4 v_gender3 char(1) :='a'; 5 begin 6 dbms_output.put_line('call#1'); 7 greetings('F'); 8 dbms_output.put_line('call#2'); 9 greetings(v_gender ); 10 dbms_output.put_line('call#3'); 11 greetings(v_gender3); 12 dbms_output.put_line('call#4'); 13 greetings(v_gender2); 14* end; SQL> / call#1 Hello Madam! call#2 Hello Sir! call#3 Hello mate! call#4 Hello mate! PL/SQL procedure successfully completed. SQL> declare 2 v_gender char(1) :='m'; 3 v_gender2 char(1); 4 v_gender3 char(1) :='a'; 5 begin 6 dbms_output.put_line('call#1'); 7 greetings(); 8 end; 9 / greetings(); * ERROR at line 7: ORA-06550: line 7, column 2: PLS-00306: wrong number or types of arguments in call to 'GREETINGS' ORA-06550: line 7, column 2: PL/SQL: Statement ignored SQL> ed Wrote file afiedt.buf 1 declare 2 v_gender char(1) :='m'; 3 v_gender2 char(1); 4 v_gender3 char(1) :='a'; 5 begin 6 dbms_output.put_line('call#1'); 7 greetings(null); 8* end; SQL> / call#1 Hello mate! PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 create or replace procedure greetings(p_gender IN char) 2 is 3 begin 4 p_gender := 'F'; 5 if p_gender in ('F','f') then 6 dbms_output.put_line('Hello Madam!'); 7 elsif p_gender in ('M','m') then 8 dbms_output.put_line('Hello Sir!'); 9 else 10 dbms_output.put_line('Hello mate!'); 11 end if; 12* end; SQL> / Warning: Procedure created with compilation errors. SQL> sho err Errors for PROCEDURE GREETINGS: LINE/COL ERROR -------- ----------------------------------------------------------------- 4/2 PL/SQL: Statement ignored 4/2 PLS-00363: expression 'P_GENDER' cannot be used as an assignment target SQL> ed Wrote file afiedt.buf 1 create or replace procedure greetings(p_gender IN char default 'F') 2 is 3 begin 4 if p_gender in ('F','f') then 5 dbms_output.put_line('Hello Madam!'); 6 elsif p_gender in ('M','m') then 7 dbms_output.put_line('Hello Sir!'); 8 else 9 dbms_output.put_line('Hello mate!'); 10 end if; 11* end; 12 / Procedure created. SQL> ed Wrote file afiedt.buf 1 declare 2 v_gender char(1) :='m'; 3 v_gender2 char(1); 4 v_gender3 char(1) :='a'; 5 begin 6 dbms_output.put_line('call#1'); 7 greetings('M'); 8 dbms_output.put_line('call#2'); 9 greetings(v_gender ); 10 dbms_output.put_line('call#3'); 11 greetings(v_gender3); 12 dbms_output.put_line('call#4'); 13 greetings(v_gender2); 14* end; SQL> / call#1 Hello Sir! call#2 Hello Sir! call#3 Hello mate! call#4 Hello mate! PL/SQL procedure successfully completed. SQL> declare 2 v_gender char(1) :='m'; 3 v_gender2 char(1); 4 v_gender3 char(1) :='a'; 5 begin 6 dbms_output.put_line('call#1'); 7 greetings('M'); 8 dbms_output.put_line('call#2'); 9 greetings(v_gender ); 10 dbms_output.put_line('call#3'); 11 greetings(v_gender3); 12 dbms_output.put_line('call#4'); 13 greetings(v_gender2); 14 dbms_output.put_line('call#5'); 15 greetings(); 16 end; 17 / call#1 Hello Sir! call#2 Hello Sir! call#3 Hello mate! call#4 Hello mate! call#5 Hello Madam! PL/SQL procedure successfully completed. SQL> --default value is used when you do not use the parameter!!!! as in call#5 SQL> SQL> --write a procedure that acepts a departmentid as input and returns the name of the department SQL> desc departments Name Null? Type ----------------------------------------- -------- ---------------------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) SQL> ed Wrote file afiedt.buf 1 create or replace procedure get_dname(p_department_id IN departments.department_id%type, 2 p_department_name OUT departments.department_name%type) 3 is 4 begin 5 select department_name into p_department_name 6 from departments 7 where department_id=p_department_id; 8* end; 9 / ERROR: ORA-03114: not connected to ORACLE create or replace procedure get_dname(p_department_id IN departments.department_id%type, * ERROR at line 1: ORA-03113: end-of-file on communication channel SQL> conn user15/user15@orcl Connected. SQL> / Procedure created. SQL> select DEPARTMENT_ID,DEPARTMENT_NAME from departments; select DEPARTMENT_ID,DEPARTMENT_NAME from departments * ERROR at line 1: ORA-03113: end-of-file on communication channel SQL> conn user15/user15@orcl ERROR: ORA-12541: TNS:no listener SQL> conn user15/user15@orcl ERROR: ORA-12541: TNS:no listener SQL> conn user15/user15@orcl Connected. SQL> select DEPARTMENT_ID,DEPARTMENT_NAME from departments; select DEPARTMENT_ID,DEPARTMENT_NAME from departments * ERROR at line 1: ORA-03113: end-of-file on communication channel SQL> conn user15/user15@orcl ERROR: ORA-12541: TNS:no listener SQL> conn user15/user15@orcl ERROR: ORA-12541: TNS:no listener SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> conn user15/user15@orcl Connected. SQL> r 1* select DEPARTMENT_ID,DEPARTMENT_NAME from departments DEPARTMENT_ID DEPARTMENT_NAME ------------- ------------------------------ 10 Administration 20 Marketing 30 Purchasing 40 Human Resources 50 Shipping 60 IT 70 Public Relations 80 Sales 90 Executive 100 Finance 110 Accounting DEPARTMENT_ID DEPARTMENT_NAME ------------- ------------------------------ 120 Treasury 130 Corporate Tax 140 Control And Credit 150 Shareholder Services 160 Benefits 170 Manufacturing 180 Construction 190 Contracting 200 Operations 210 IT Support 220 NOC DEPARTMENT_ID DEPARTMENT_NAME ------------- ------------------------------ 230 IT Helpdesk 240 Government Sales 250 Retail Sales 260 Recruiting 270 Payroll 27 rows selected. SQL> SQL> declare 2 v_id departments.department_id%type :=10; 3 v_name departments.department_name%type; 4 begin 5 dbms_output.put_line('Before proc call '||v_department_id||' and '||v_department_name||'!') 6 . SQL> ed Wrote file afiedt.buf 1 declare 2 v_id departments.department_id%type :=10; 3 v_name departments.department_name%type; 4 begin 5 dbms_output.put_line('Before proc call '||v_department_id||' and '||v_department_name||'!'); 6 get_dname(v_id,v_name); 7 dbms_output.put_line('After proc call '||v_department_id||' and '||v_department_name||'!'); 8* end; SQL> / dbms_output.put_line('Before proc call '||v_department_id||' and '||v_department_name||'!'); * ERROR at line 5: ORA-06550: line 5, column 44: PLS-00201: identifier 'V_DEPARTMENT_ID' must be declared ORA-06550: line 5, column 2: PL/SQL: Statement ignored ORA-06550: line 7, column 42: PLS-00201: identifier 'V_DEPARTMENT_ID' must be declared ORA-06550: line 7, column 1: PL/SQL: Statement ignored SQL> ed Wrote file afiedt.buf 1 declare 2 v_department_id departments.department_id%type :=10; 3 v_department_name departments.department_name%type; 4 begin 5 dbms_output.put_line('Before proc call '||v_department_id||' and '||v_department_name||'!'); 6 get_dname(v_id,v_name); 7 dbms_output.put_line('After proc call '||v_department_id||' and '||v_department_name||'!'); 8* end; SQL> / get_dname(v_id,v_name); * ERROR at line 6: ORA-06550: line 6, column 12: PLS-00201: identifier 'V_ID' must be declared ORA-06550: line 6, column 2: PL/SQL: Statement ignored SQL> ed Wrote file afiedt.buf 1 declare 2 v_department_id departments.department_id%type :=10; 3 v_department_name departments.department_name%type; 4 begin 5 dbms_output.put_line('Before proc call '||v_department_id||' and '||v_department_name||'!'); 6 get_dname(v_department_id,v_department_name); 7 dbms_output.put_line('After proc call '||v_department_id||' and '||v_department_name||'!'); 8* end; SQL> / PL/SQL procedure successfully completed. SQL> set serveroutput on SQL> declare 2 v_department_id departments.department_id%type :=10; 3 v_department_name departments.department_name%type; 4 begin 5 dbms_output.put_line('Before proc call '||v_department_id||' and '||v_department_name||'!'); 6 get_dname(v_department_id,v_department_name); 7 dbms_output.put_line('After proc call '||v_department_id||' and '||v_department_name||'!'); 8 end; 9 / Before proc call 10 and ! After proc call 10 and Administration! PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_department_id departments.department_id%type :=10; 3 v_department_name departments.department_name%type :='Ali'; 4 begin 5 dbms_output.put_line('Before proc call '||v_department_id||' and '||v_department_name||'!'); 6 get_dname(v_department_id,v_department_name); 7 dbms_output.put_line('After proc call '||v_department_id||' and '||v_department_name||'!'); 8* end; SQL> / Before proc call 10 and Ali! After proc call 10 and Administration! PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_department_id departments.department_id%type :=10; 3 v_department_name departments.department_name%type :='Ali'; 4 begin 5 dbms_output.put_line('Before proc call '||v_department_id||' and '||v_department_name||'!'); 6 get_dname(v_department_id,'Administration'); 7 dbms_output.put_line('After proc call '||v_department_id||' and '||v_department_name||'!'); 8* end; SQL> / get_dname(v_department_id,'Administration'); * ERROR at line 6: ORA-06550: line 6, column 28: PLS-00363: expression 'Administration' cannot be used as an assignment target ORA-06550: line 6, column 2: PL/SQL: Statement ignored SQL> ed Wrote file afiedt.buf 1 declare 2 v_department_id departments.department_id%type :=10; 3 v_department_name departments.department_name%type :='Ali'; 4 begin 5 dbms_output.put_line('Before proc call '||v_department_id||' and '||v_department_name||'!'); 6 get_dname(30,v_department_name); 7 dbms_output.put_line('After proc call '||v_department_id||' and '||v_department_name||'!'); 8* end; SQL> / Before proc call 10 and Ali! After proc call 10 and Purchasing! PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 create or replace procedure get_dname(p_department_id IN departments.department_id%type, 2 p_department_name OUT departments.department_name%type) 3 is 4 begin 5 dbms_output.put_line('Parameter 1 is '||p_department_id); 6 -- dbms_output.put_line('Parameter 2 is '||p_department_name); 7 select department_name into p_department_name 8 from departments 9 where department_id=p_department_id; 10* end; SQL> / Procedure created. SQL> ed Wrote file afiedt.buf 1 create or replace procedure get_dname(p_department_id IN departments.department_id%type, 2 p_department_name OUT departments.department_name%type) 3 is 4 begin 5 dbms_output.put_line('Parameter 1 is '||p_department_id); 6 dbms_output.put_line('Parameter 2 is '||p_department_name); 7 select department_name into p_department_name 8 from departments 9 where department_id=p_department_id; 10* end; SQL> / ERROR: ORA-03114: not connected to ORACLE create or replace procedure get_dname(p_department_id IN departments.department_id%type, * ERROR at line 1: ORA-03113: end-of-file on communication channel SQL> conn user15/user15@orcl Connected. SQL> / Procedure created. SQL> declare 2 . SQL> ed Wrote file afiedt.buf 1 declare 2 v_department_id departments.department_id%type :=10; 3 v_department_name departments.department_name%type :='Ali'; 4 begin 5 dbms_output.put_line('Before proc call '||v_department_id||' and '||v_department_name||'!'); 6 get_dname(v_department_id,v_department_name); 7 dbms_output.put_line('After proc call '||v_department_id||' and '||v_department_name||'!'); 8* end; SQL> / PL/SQL procedure successfully completed. SQL> set serveroutput on SQL> / Before proc call 10 and Ali! Parameter 1 is 10 Parameter 2 is After proc call 10 and Administration! PL/SQL procedure successfully completed. SQL> --write a procedure that accepts a number as input, modifies it by incrementing by 1 and returns that value through the same parameter SQL> create or replace procedure incby1(p IN OUT number) 2 is 3 begin 4 p := p+1; 5 end; 6 / Procedure created. SQL> declare 2 x number :=10; 3 begin 4 dbms_output.put_line('Before proc call '||x||'!'); 5 incby1(x); 6 dbms_output.put_line('After proc call '||x||'!'); 7 end; 8 / Before proc call 10! After proc call 11! PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 x number :=10; 3 begin 4 dbms_output.put_line('Before proc call '||x||'!'); 5 incby1(10); 6 dbms_output.put_line('After proc call '||x||'!'); 7* end; SQL> / ERROR: ORA-03114: not connected to ORACLE declare * ERROR at line 1: ORA-03113: end-of-file on communication channel SQL> conn user15/user15@orcl Connected. SQL> / incby1(10); * ERROR at line 5: ORA-06550: line 5, column 9: PLS-00363: expression '10' cannot be used as an assignment target ORA-06550: line 5, column 2: PL/SQL: Statement ignored SQL> ed Wrote file afiedt.buf 1 declare 2 x number ; 3 begin 4 dbms_output.put_line('Before proc call '||x||'!'); 5 incby1(x); 6 dbms_output.put_line('After proc call '||x||'!'); 7* end; SQL> / PL/SQL procedure successfully completed. SQL> set serveroutput on ERROR: ORA-03113: end-of-file on communication channel SQL> conn user15/user15@orcl Connected. SQL> set serveroutput on SQL> declare 2 x number ; 3 begin 4 dbms_output.put_line('Before proc call '||x||'!'); 5 incby1(x); 6 dbms_output.put_line('After proc call '||x||'!'); 7 end; 8 / Before proc call ! After proc call ! PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 x number ; 3 begin 4 dbms_output.put_line('is x null or not null?????'); 5 if x=NULL then 6 dbms_output.put_line('x is null'); 7 end if; 8 if x!=NULL then 9 dbms_output.put_line('x is not null'); 10 end if; 11 dbms_output.put_line('Did you understand what the value of x is?????'); 12* end; 13 / is x null or not null????? Did you understand what the value of x is????? PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 x number ; 3 begin 4 dbms_output.put_line('is x null or not null?????'); 5 if x is NULL then 6 dbms_output.put_line('x is null'); 7 end if; 8 if x is not NULL then 9 dbms_output.put_line('x is not null'); 10 end if; 11 dbms_output.put_line('Did you understand what the value of x is?????'); 12* end; SQL> / is x null or not null????? x is null Did you understand what the value of x is????? PL/SQL procedure successfully completed. SQL> spool off