SQL> --parameter types 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. 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> 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. SP2-0734: unknown command beginning "The actual..." - rest of line ignored. SQL> SQL> --write a procedure to porint hello SQL> create or replace procedure printhello 2 is 3 begin 4 dbms_output.putr_line(hello); 5 end; 6 / Warning: Procedure created with compilation errors. SQL> sho err Errors for PROCEDURE PRINTHELLO: LINE/COL ERROR -------- ----------------------------------------------------------------- 4/3 PL/SQL: Statement ignored 4/25 PLS-00201: identifier 'HELLO' must be declared SQL> 4 4* dbms_output.putr_line(hello); SQL> c/r/ 4* dbms_output.put_line(hello); SQL> / Warning: Procedure created with compilation errors. SQL> sho 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 anonymous block SQL> begin 2 printhello; 3 end; 4 / hello PL/SQL procedure successfully completed. SQL> --write a procedure that accepts a name an d prints hello on screen SQL> SQL> --input parameter, we will not modify it ==> IN parameter 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(p_name); 5* end; SQL> / Procedure created. SQL> begin 2 printhello; 3 end; 4 / printhello; * ERROR at line 2: ORA-06550: line 2, column 2: PLS-00306: wrong number or types of arguments in call to 'PRINTHELLO' ORA-06550: line 2, column 2: PL/SQL: Statement ignored SQL> begin 2 printhello('ugurcan'); 3 end; 4 / ugurcan PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v varchar2(20); 3 begin 4 v := 'ali'; 5 printhello(v); 6* end; SQL> / ali PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v varchar2(20); 3 begin 4 printhello(v); 5* end; 6 / PL/SQL procedure successfully completed. SQL> create or replace procedure printhello(p_name IN varchar2) 2 is 3 begin 4 dbms_output.put_line('hello'||p_name||'!'); 5 end; 6 / Procedure created. SQL> declare 2 v varchar2(20); 3 begin 4 printhello(v); 5 end; 6 / hello! PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 create or replace procedure printhello(p_name IN varchar2) 2 is 3 begin 4 p_name := 'Molhim'; 5 dbms_output.put_line('hello'||p_name||'!'); 6* end; SQL> / Warning: Procedure created with compilation errors. SQL> sho 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> --you cannot modify the value of an in parameter SQL> -- SQL> --write a procedure that accepts a department is as input and returns the total number of SQL> --employees working in that department SQL> -- accept department id value and return employee count 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 employees.department_id%type :=30; 3 v_count number; 4 begin 5 dbms_output.put_line('1-'||v_id||'*'||v_count); 6 count_emps(v_id ,v_count); 7 end; 8 9 . SQL> ed Wrote file afiedt.buf 1 declare 2 v_id employees.department_id%type :=30; 3 v_count number; 4 begin 5 dbms_output.put_line('1-'||v_id||'*'||v_count); 6 count_emps(v_id ,v_count); 7 dbms_output.put_line('2-'||v_id||'*'||v_count); 8* end; 9 / 1-30* 2-30*6 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_id employees.department_id%type :=30; 3 v_count number; 4 begin 5 dbms_output.put_line('1-'||v_id||'*'||v_count); 6 count_emps(v_id ,6); 7 dbms_output.put_line('2-'||v_id||'*'||v_count); 8* end; SQL> SQL> / count_emps(v_id ,6); * ERROR at line 6: ORA-06550: line 6, column 20: PLS-00363: expression '6' cannot be used as an assignment target ORA-06550: line 6, column 3: PL/SQL: Statement ignored SQL> --when we call a procedure with an out parameter the actual parameter must be a variable SQL> --because it will be used as an assignmentt target SQL> ed Wrote file afiedt.buf 1 declare 2 v_id employees.department_id%type :=30; 3 v_count number; 4 begin 5 dbms_output.put_line('1-'||v_id||'*'||v_count); 6 count_emps(v_id ,6); 7 dbms_output.put_line('2-'||v_id||'*'||v_count); 8* end; 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; 16 / Procedure created. SQL> declare 2 v_id employees.department_id%type :=30; 3 v_count number; 4 begin 5 dbms_output.put_line('1-'||v_id||'*'||v_count); 6 count_emps(v_id ,v_count); 7 dbms_output.put_line('2-'||v_id||'*'||v_count); 8 end; 9 / 1-30* ****** 2-30*6 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 select count(*) into p_emp_count 8 from employees 9 where department_id = p_department_id; 10 v_emp_count :=999; 11 exception 12 when others then 13 p_emp_count := -1; 14* end; 15 / Warning: Procedure created with compilation errors. SQL> sho err Errors for PROCEDURE COUNT_EMPS: LINE/COL ERROR -------- ----------------------------------------------------------------- 10/3 PL/SQL: Statement ignored 10/3 PLS-00201: identifier 'V_EMP_COUNT' must be declared 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 select count(*) into p_emp_count 8 from employees 9 where department_id = p_department_id; 10 p_emp_count :=999; 11 exception 12 when others then 13 p_emp_count := -1; 14* end; SQL> / Procedure created. SQL> declare 2 v_id employees.department_id%type :=30; 3 v_count number; 4 begin 5 dbms_output.put_line('1-'||v_id||'*'||v_count); 6 count_emps(v_id ,v_count); 7 dbms_output.put_line('2-'||v_id||'*'||v_count); 8 end; 9 / 1-30* 2-30*999 PL/SQL procedure successfully completed. SQL> --write a procedure that increments its argument by 1 SQL> --if x is 7, incr(x) will make x 8 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> begin 2 incr(9); 3 end; 4 / incr(9); * ERROR at line 2: ORA-06550: line 2, column 7: PLS-00363: expression '9' cannot be used as an assignment target ORA-06550: line 2, column 2: PL/SQL: Statement ignored SQL> declare 2 v number; 3 begin 4 v :=9; 5 dbms_output.put_line('before procedure call : '||v); 6 incr(v); 7 dbms_output.put_line('afdter procedure call : '||v); 8 end; 9 / before procedure call : 9 afdter procedure call : 10 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v number; 3 begin 4 --v :=9; 5 dbms_output.put_line('before procedure call : '||v||'**'); 6 incr(v); 7 dbms_output.put_line('afdter procedure call : '||v||'**'); 8* end; SQL> / before procedure call : ** afdter procedure call : ** PL/SQL procedure successfully completed. SQL> --write a function that accepts an ýnput parameter, increments it SQL> --and returns the new value SQL> ed Wrote file afiedt.buf 1 create or replace function f_incr(p in number) 2 return number 3 is 4 v number; 5 begin 6 v :=p+1; 7 return v; 8* end; 9 / Function created. SQL> ed Wrote file afiedt.buf 1 declare 2 a number :=5; 3 b number; 4 begin 5 f_incr(a); 6* end; SQL> / f_incr(a); * ERROR at line 5: ORA-06550: line 5, column 2: PLS-00221: 'F_INCR' is not a procedure or is undefined ORA-06550: line 5, column 2: PL/SQL: Statement ignored SQL> ed Wrote file afiedt.buf 1 declare 2 a number :=5; 3 b number; 4 begin 5 b := f_incr(a); 6* end; SQL> / PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 a number :=5; 3 b number; 4 begin 5 b := f_incr(a); 6 dbms_output.put_line('the result is '||b); 7* end; SQL> / the result is 6 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 a number :=5; 3 b number; 4 begin 5 b := f_incr(a); 6 dbms_output.put_line('1- the result is '||b); 7 dbms_output.put_line('2- the result is '||f_incr(b) ); 8 dbms_output.put_line('3- the result is '||f_incr(a) ); 9 dbms_output.put_line('4- the result is '||f_incr(b) ); 10* end; 11 / 1- the result is 6 2- the result is 7 3- the result is 6 4- the result is 7 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 a number :=5; 3 b number; 4 begin 5 a := f_incr(a); 6 dbms_output.put_line('1- the result is '||a); 7* end; 8 / 1- the result is 6 PL/SQL procedure successfully completed. SQL> spool off