SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY,
3 fname VARCHAR2(50),
4 lname VARCHAR2(50)
5 );
Table created.
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, 'A', 'B');
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (2, 'C', 'D');
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (3, 'Enn', 'F');
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (4, 'G', 'H');
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (5, 'G', 'Z');
1 row created.
SQL>
SQL> PROMPT as bind variables
as bind variables
SQL>
SQL> CREATE OR REPLACE PROCEDURE bind_test (i_emp_fname IN emp.fname%TYPE)
2 IS
3 v_emp_lname emp.lname%TYPE;
4 BEGIN
5 SELECT lname INTO v_emp_lname FROM emp WHERE fname = i_emp_fname;
6
7 DBMS_OUTPUT.PUT_LINE(i_emp_fname||' has a last name of '||v_emp_lname);
8 EXCEPTION
9 WHEN OTHERS
10 THEN
11 DBMS_OUTPUT.PUT_LINE(sqlerrm);
12 END;
13 /
Procedure created.
SQL>
SQL>
SQL> ALTER SESSION SET SQL_TRACE = TRUE;
Session altered.
SQL> EXEC bind_test('Ron')
ORA-01403: no data found
PL/SQL procedure successfully completed.
SQL> EXEC bind_test('Mike')
ORA-01403: no data found
PL/SQL procedure successfully completed.
SQL> ALTER SESSION SET SQL_TRACE = FALSE;
Session altered.
SQL>
SQL> drop table emp;
Table dropped.
SQL>