Creation and use of 14.PL_SQL--StoreProcedures
= 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 recently emptied name, v_emp_sal)
DBMS_OUTPUT.PUT_LINE (v_emp_name | | 'earns' | | to_char)
END
/
SQL > @ notes/s73.sql
Procedure created.
SQL > @ notes/s73_1.sql
Smith earns $7400.00
PL/SQL procedure successfully completed.
SQL > select last_name, salary from employees whereemployee_id = 171,
LAST_NAME SALARY
--
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
