SQL> --parameter modes SQL> IN : An IN parameter lets you pass a value to the subprogram. It is a read-only parameter. Inside the subprogram, an IN parameter acts like a constant. It cannot be assigned a value. You can pass a constant, literal, initialized variable, or expression as an IN parameter. You can also initialize it to a default value; however, in that case, it is omitted from the subprogram call. It is the default mode of parameter passing. Parameters are passed by reference. SQL> OUT: An OUT parameter returns a value to the calling program. Inside the subprogram, an OUT parameter acts like a variable. You can change its value and reference the value after assigning it. The actual parameter must be variable and it is passed by value. SP2-0734: unknown command beginning "OUT: An OU..." - rest of line ignored. SQL> --IN OUT : An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and the value can be read. SQL> --The actual parameter corresponding to an IN OUT formal parameter must be a variable, not a constant or an expression. Formal parameter must be assigned a value. Actual parameter is passed by value. SQL> SQL> --write a procedure that prints hello on screen SQL> set serveroutput on SQL> create or replace procedure printhello 2 is 3 begin 4 dbms_output.put_line(hello); 5 end; 6 / Warning: Procedure created with compilation errors. SQL> show err Errors for PROCEDURE PRINTHELLO: LINE/COL ERROR -------- ----------------------------------------------------------------- 4/3 PL/SQL: Statement ignored 4/24 PLS-00201: identifier 'HELLO' must be declared SQL> ed Wrote file afiedt.buf 1 create or replace procedure printhello 2 is 3 begin 4 dbms_output.put_line('hello'); 5* end; SQL> / Procedure created. SQL> --call the procedure from an anonymopus block SQL> begin 2 printhello; 3 end; 4 / hello PL/SQL procedure successfully completed. SQL> --write a procedure that accepts a name as ýnput and prýnts hello on screen SQL> ed Wrote file afiedt.buf 1 create or replace procedure printhello(p_name IN varchar2) 2 is 3 begin 4 dbms_output.put_line('hello '||p_name); 5* end; SQL> / Procedure created. SQL> declare 2 x varchar(10); 3 begin 4 x :='ALI'; 5 . SQL> ed Wrote file afiedt.buf 1 declare 2 x varchar(10); 3 begin 4 x :='ALI'; 5 printhello(x); 6 printhello('tomiwa'); 7* end; SQL> / hello ALI hello tomiwa PL/SQL procedure successfully completed. SQL> create or replace procedure printhello(p_name varchar2) 2 is 3 begin 4 dbms_output.put_line('hello '||p_name); 5 end; 6 / Procedure created. SQL> declare 2 x varchar(10); 3 begin 4 x :='ALI'; 5 printhello(x); 6 printhello('tomiwa'); 7 end; 8 / hello ALI hello tomiwa PL/SQL procedure successfully completed. SQL> --IN is the default parameter mode SQL> --if you do not specify the mode the parameter ýs an IN parameter SQL> ed Wrote file afiedt.buf 1 create or replace procedure printhello(p_name varchar2) 2 is 3 begin 4 p_name := 'ayse'; 5 dbms_output.put_line('hello '||p_name); 6* end; SQL> / Warning: Procedure created with compilation errors. SQL> show err Errors for PROCEDURE PRINTHELLO: LINE/COL ERROR -------- ----------------------------------------------------------------- 4/3 PL/SQL: Statement ignored 4/3 PLS-00363: expression 'P_NAME' cannot be used as an assignment target SQL> --p_name is an IN parameter, therefore it is read-only and we cannot modify it SQL> --inside the function/proc edure SQL> ed Wrote file afiedt.buf 1 create or replace procedure printhello(p_name varchar2) 2 is 3 y varchar2(10); 4 begin 5 --p_name := 'ayse'; --gives a compilation error 6 dbms_output.put_line('hello '||p_name); 7 y := p_name; 8 dbms_output.put_line('bye '||y); 9* end; 10 / Procedure created. SQL> declare 2 x varchar(10); 3 begin 4 x :='ALI'; 5 printhello(x); 6 printhello('tomiwa'); 7 end; 8 / hello ALI bye ALI hello tomiwa bye tomiwa PL/SQL procedure successfully completed. SQL> declare 2 x varchar(10); 3 begin 4 printhello(x); 5 end; 6 / hello bye PL/SQL procedure successfully completed. SQL> --write a procedure that accepts a department number as ýnput and returns the number of amployees in that department SQL> ed Wrote file afiedt.buf 1 create or replace procedure count_emps( 2 p_department_id IN employees.department_id%type, 3 p_emp_count OUT number ) 4 is 5 begin 6 select count(*) into p_emp_count 7 from employees 8 where department_id=p_department_id; 9 exception 10 when others then 11 p_emp_count :=-1; 12* end; 13 / Procedure created. SQL> declare 2 v_id number :=30; 3 v_count number; 4 begin 5 count_emps(v_id, v_count); 6 dbms_output.put_line('there are '||v_count||' employees in department ||v_id); 7 end; 8 / ERROR: ORA-01756: quoted string not properly terminated SQL> ed Wrote file afiedt.buf 1 declare 2 v_id number :=30; 3 v_count number; 4 begin 5 count_emps(v_id, v_count); 6 dbms_output.put_line('there are '||v_count||' employees in department '||v_id); 7* end; SQL> / there are 6 employees in department 30 PL/SQL procedure successfully completed. SQL> declare 2 v_id number :=30; 3 v_count number; 4 begin 5 count_emps(30,10) 6 dbms_output.put_line('there are '||v_count||' employees in department '||v_id); 7 end; 8 . SQL> declare 2 v_id number :=30; 3 v_count number; 4 begin 5 count_emps(30,6) 6 dbms_output.put_line('there are '||v_count||' employees in department '||v_id); 7 end; 8 / dbms_output.put_line('there are '||v_count||' employees in department '||v_id); * ERROR at line 6: ORA-06550: line 6, column 1: PLS-00103: Encountered the symbol "DBMS_OUTPUT" when expecting one of the following: := . ( % ; The symbol ":=" was substituted for "DBMS_OUTPUT" to continue. SQL> declare 2 v_id number :=30; 3 v_count number; 4 begin 5 count_emps(30,6); 6 dbms_output.put_line('there are '||v_count||' employees in department '||v_id); 7 end; 8 / count_emps(30,6); * ERROR at line 5: ORA-06550: line 5, column 16: PLS-00363: expression '6' cannot be used as an assignment target ORA-06550: line 5, column 2: PL/SQL: Statement ignored SQL> --the actual parameter used in procedure call for an OUT formal parameter must be VARIABLE SQL> ed Wrote file afiedt.buf 1 declare 2 v_id number :=30; 3 v_count number; 4 begin 5 count_emps(30,v_count); 6 dbms_output.put_line('there are '||v_count||' employees in department '||v_id); 7* end; SQL> / there are 6 employees in department 30 PL/SQL procedure successfully completed. SQL> declare 2 v_id number :=30; 3 v_count number; 4 begin 5 count_emps(9,v_count); 6 dbms_output.put_line('there are '||v_count||' employees in department '||v_id); 7 end; 8 / there are 0 employees in department 30 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 create or replace procedure count_emps( 2 p_department_id IN employees.department_id%type, 3 p_emp_count OUT number ) 4 is 5 v number; 6 begin 7 v := p_emp_count; 8 -- dbms_output.put_line(p_emp_count); 9 select count(*) into p_emp_count 10 from employees 11 where department_id=p_department_id; 12 exception 13 when others then 14 p_emp_count :=-1; 15* end; 16 / Procedure created. SQL> ed Wrote file afiedt.buf 1 create or replace procedure count_emps( 2 p_department_id IN employees.department_id%type, 3 p_emp_count OUT number ) 4 is 5 v number; 6 begin 7 v := p_emp_count; 8 dbms_output.put_line('****'||v||'****'); 9 select count(*) into p_emp_count 10 from employees 11 where department_id=p_department_id; 12 exception 13 when others then 14 p_emp_count :=-1; 15* end; SQL> / Procedure created. SQL> declare 2 v_id number :=30; 3 v_count number; 4 begin 5 count_emps(30,v_count); 6 dbms_output.put_line('there are '||v_count||' employees in department '||v_id); 7 end; 8 / ******** there are 6 employees in department 30 PL/SQL procedure successfully completed. SQL> --the out parameter contains NULL value untill you assign something tgoý it SQL> -- SQL> --write a procedure that increments an integer argument SQL> ed Wrote file afiedt.buf 1 create or replace procedure incr(p IN OUT number) 2 is 3 begin 4 p := p+1; 5* end; SQL> / Procedure created. SQL> declare 2 x number := 7; 3 begin 4 dbms_output.put_line('1-'||x); 5 incr(x); 6 dbms_output.put_line('2-'||x); 7 end; 8 / 1-7 2-8 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 create or replace procedure incr(p IN OUT number) 2 is 3 begin 4 dbms_output.put_line('++++'||p||'++++'); 5 p := p+1; 6* end; SQL> / Procedure created. SQL> declare 2 x number := 7; 3 begin 4 dbms_output.put_line('1-'||x); 5 incr(x); 6 dbms_output.put_line('2-'||x); 7 end; 8 / 1-7 ++++7++++ 2-8 PL/SQL procedure successfully completed. SQL> declare 2 x number := 7; 3 begin 4 dbms_output.put_line('1-'||x); 5 incr(7); 6 dbms_output.put_line('2-'||x); 7 end; 8 9 / incr(7); * ERROR at line 5: ORA-06550: line 5, column 7: PLS-00363: expression '7' cannot be used as an assignment target ORA-06550: line 5, column 2: PL/SQL: Statement ignored SQL> --the procedure call should contain a varýable for the ýn out formal paremeter SQL> spool off