Shulou(Shulou.com)06/01 Report--

= 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.