================Example 1===================

SQL> select * from emp_tmp

2 ;

EMPLOYEE_ID SALARYCOMMISSION_PCT

----------- ---------- --------------

301 2500 0

302 25 .1

[oracle@localhost notes]$ vim s72.sql

CREATE OR REPLACE PROCEDURE raise_salary

(p_id INemployees.employee_id%TYPE,

p_percent INNUMBER)

IS

BEGIN

UPDATE emp_tmp

SET salary =salary * ( 1 + p_percent/100)

WHERE employee_id= p_id;

COMMIT;

END raise_salary;

/

SQL> @notes/s72.sql

Procedure created.

SQL> BEGIN raise_salary(302, 100);

2 END;

3 /

PL/SQL procedure successfully completed.

SQL> select * from emp_tmp;

EMPLOYEE_ID SALARYCOMMISSION_PCT

----------- ---------- --------------

301 2500 0

302 50 .1


=================Example 2=====================


[oracle@localhost notes]$ vim s73.sql

CREATE OR REPLACE PROCEDURE query_emp

(p_id IN employees.employee_id%TYPE,

p_name OUT employees.last_name%TYPE,

p_salary OUTemployees.salary%TYPE) IS

BEGIN

SELECT last_name,salary INTO p_name, p_salary

FROM employees

WHERE employee_id= p_id;

END query_emp;

/

[oracle@localhost notes]$ vim s73_1.sql

SET SERVEROUTPUT ON

DECLARE

v_emp_nameemployees.last_name%TYPE;

v_emp_sal employees.salary%TYPE;

BEGIN

query_emp(171,v_emp_name, v_emp_sal);

DBMS_OUTPUT.PUT_LINE(v_emp_name || ' earns ' || to_char(v_emp_sal,'$999,999.00'));

END;

/

SQL> @notes/s73.sql

Procedure created.

SQL> @notes/s73_1.sql

Smith earns$7,400.00

PL/SQL procedure successfully completed.

SQL> select last_name, salary from employees whereemployee_id = 171;

LAST_NAMESALARY

------------------------- ----------

Smith 7400



==============Example 3=================


[oracle@localhost notes]$ vim s74.sql

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE format_phone

(p_phone_no IN OUTVARCHAR2) IS

BEGIN

p_phone_no := '('|| SUBSTR(p_phone_no, 1, 3) ||

')'|| SUBSTR(p_phone_no, 4, 3) ||

'-'|| SUBSTR(p_phone_no, 7);

END format_phone;

/

[oracle@localhost notes]$ vim s74_1.sql

VARIABLE b_phone_no VARCHAR2(15)

EXECUTE :b_phone_no := '8006330575'

PRINT b_phone_no

EXECUTE format_phone (:b_phone_no)

PRINT b_phone_no

SQL> @notes/s74.sql

Procedure created.

SQL> @notes/s74_1.sql

PL/SQL procedure successfully completed.

B_PHONE_NO

--------------------------------

8006330575

PL/SQL procedure successfully completed.

B_PHONE_NO

--------------------------------

(800)633-0575