14.PL_SQL——StoreProcedures的創(chuàng)建和使用

================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_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