14.PL_SQL——StoreProcedures的创建和使用
================Example 1===================
SQL> select * from emp_tmp
2;
EMPLOYEE_ID SALARYCOMMISSION_PCT
----------- ---------- --------------
301 2500 0
302 25 .1
$ 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);
2END;
3/
PL/SQL procedure successfully completed.
SQL> select * from emp_tmp;
EMPLOYEE_ID SALARYCOMMISSION_PCT
----------- ---------- --------------
301 2500 0
302 50 .1
=================Example 2=====================
$ 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;
/
$ vim s73_1.sql
SET SERVEROUTPUT ON
DECLARE
v_emp_nameemployees.last_name%TYPE;
v_emp_salemployees.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_NAME SALARY
------------------------- ----------
Smith 7400
==============Example 3=================
$ 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;
/
$ 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
页:
[1]