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,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

转载于:https://blog.51cto.com/wuyelan/1551785