数据字典：
 用来查看有名块的源代码

select * from user_source;

用来查看编译错误信息：
show err

procedure：
 完成某个特
 定功能的一段pl/sql代码，并且被赋予一个名字；
 保存对象信息在数据字典当中，可以重复调用；

练习 1:导入型形式参数（in类型）
CREATE OR REPLACE PROCEDURE raise_salary
(p_id IN emp.empno%TYPE)
IS
--变量声明
BEGIN
  UPDATE emp SET sal = sal * 1.1
  WHERE empno = p_id;
END raise_salary;
/

create or replace PROCEDURE raise_sal(v_id in employees.employee_id%type)
is
begin
    update employees set salary=salary*1.1 where employee_id=v_id;
    commit;
end;
/


--存储过程的调用:
EXECUTE raise_salary (7839);

HR@prod> exec raise_sal(200);

PL/SQL procedure successfully completed.


declare
  v_empno emp.empno%type :=&p_empno;
begin
  RAISE_SALARY(v_empno);
end;
/

HR@prod> declare
  2  v_eid number := &id;
  3  begin
  4     raise_sal(v_eid);
  5  end;
  6  /
Enter value for id: 101
old   2: v_eid number := &id;
new   2: v_eid number := 101;

PL/SQL procedure successfully completed.

SQL>SHOW ERROR --查看编译错误;

练习 2:导出型形式参数（out类型）
CREATE OR REPLACE PROCEDURE query_emp
(p_id IN emp.empno%TYPE,
  p_name OUT emp.ename%TYPE,
  p_salary OUT emp.sal%TYPE,
  p_comm OUT emp.comm%TYPE)
IS
BEGIN
  SELECT ename, sal, comm
  INTO p_name, p_salary, p_comm
  FROM emp
  WHERE empno = p_id;
END query_emp;
/

VARIABLE g_name VARCHAR2(25)
VARIABLE g_sal NUMBER
VARIABLE g_comm NUMBER
EXECUTE query_emp(7369, :g_name, :g_sal, :g_comm);
PRINT

declare
  v_empno emp.empno%type:=7499;
  v_sal emp.sal%type;
  v_ename emp.ename%type;
  v_comm emp.comm%type;
begin
  query_emp(v_empno,v_ename,v_sal,v_comm);
  dbms_output.put_line(v_ename||' '||v_sal||' '||v_comm);
end;
/

--根据员工id，返回员工姓名和工资
create or replace procedure query_emp
(v_eno  in employees.employee_id%type,
 o_name  out employees.last_name%type,
 o_sal out employees.salary%type)
is
begin
   select last_name,salary into o_name, o_sal
   from employees
   where employee_id=v_eno;
end query_emp;
/
HR@prod> variable g_name varchar2(50);
HR@prod> variable g_sal number;

HR@prod> exec query_emp(100,:g_name,:g_sal);

PL/SQL procedure successfully completed.

HR@prod> print g_name;

G_NAME
--------------------------------------------------------------------------------
King

HR@prod> print g_sal;

     G_SAL
----------
     26400



练习 3:导入/导出型形式参数（in/out类型）
CREATE OR REPLACE PROCEDURE format_phone
(p_phone_no IN OUT VARCHAR2)
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;
/
VARIABLE g_phone_no VARCHAR2(15)
BEGIN
:g_phone_no:='8006330575';
END;
/
PRINT g_phone_no
EXECUTE format_phone (:g_phone_no)
PRINT g_phone_no

create or replace procedure phone_format
(phone_no in out VARCHAR2)
is
begin
--    02431500816
--     (024)-3150-0816
     phone_no := '('||substr(phone_no,1,3)||')'||
                  '-'||substr(phone_no,4,4)||
                  '-'||substr(phone_no,8,4);
end;
/

HR@prod> variable g_phone_no varchar2(15);
HR@prod> begin
  2  :g_phone_no := '02431500816';
  3  end;
  4  /

PL/SQL procedure successfully completed.

HR@prod> print g_phone_no;

G_PHONE_NO
--------------------------------------------------------------------------------
02431500816

HR@prod> exec phone_format(:g_phone_no);

PL/SQL procedure successfully completed.

HR@prod> print g_phone_no;

G_PHONE_NO
--------------------------------------------------------------------------------
(024)-3150-0816

 

 

练习 4:带有default值的参数

create sequence departments_seq start with 50;


CREATE OR REPLACE PROCEDURE add_dept
(p_name IN dept.dname%TYPE DEFAULT 'unknown',
 p_loc IN dept.loc%TYPE DEFAULT 'BJ')
IS
BEGIN
  INSERT INTO dept(deptno,
  dname, loc)
  VALUES (departments_seq.NEXTVAL, p_name, p_loc);
END add_dept;
/

 

BEGIN
add_dept;
add_dept ('TRAINING', 'SH');
END;
/

练习 5:校验数据存在否（save 该过程为valid_deptno.sql，后面要用到）
CREATE OR REPLACE PROCEDURE valid_deptno
    (v_deptno IN d.deptno%TYPE,V_RETURN OUT BOOLEAN)
  IS 
    v_dummy  CHAR(1);
  BEGIN
    SELECT 'x'
    INTO   v_dummy
    FROM   d
    WHERE  deptno = v_deptno;
    IF SQL%FOUND THEN
     V_RETURN:=TRUE;
    END IF;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN 
      V_RETURN:=FALSE;
  END valid_deptno;
/

DECLARE
  V_FLAG BOOLEAN;
BEGIN
  valid_deptno(90,V_FLAG);
  if v_flag then
    DBMS_OUTPUT.PUT_LINE('The department exist!');
  else
    DBMS_OUTPUT.PUT_LINE('The department not exist!');
  end if;
END;
/
**********************
HR@orcl11g> create table d as select * from departments;

Table created.

create or replace procedure valid_did
(v_did hr.d.department_id%type,
 v_bool out boolean)
is
  v_var char(1);
begin
  select 'x' into v_var from hr.d
  where department_id=v_did;
  if sql%found then
     v_bool := true;
  end if;
exception
  when no_data_found then
      v_bool :=false;
end;
/

declare
v_flag boolean;
begin
   valid_did(100,v_flag);
   if v_flag then
      dbms_output.put_line('the department exist.');
   else
      dbms_output.put_line('not exist.');
   end if;
end;
/

 

练习 6:创建增加雇员的过程（save 该过程为new_emp.sql，后面要用到）

create sequence seq_empno start with 8000;


CREATE OR REPLACE PROCEDURE new_emp
    (v_ename   emp.ename%TYPE,
     v_job     emp.job%TYPE     DEFAULT 'SALESMAN',
     v_mgr     emp.mgr%TYPE     DEFAULT 7839,
     v_sal     emp.sal%TYPE     DEFAULT 1000,
     v_comm    emp.comm%TYPE    DEFAULT 0,
     v_deptno  emp.deptno%TYPE  DEFAULT 30)
IS
V_FLAG BOOLEAN;
BEGIN
    valid_deptno(v_deptno,V_FLAG);
    IF V_FLAG THEN
      INSERT INTO emp
      VALUES (seq_empno.NEXTVAL, v_ename, v_job, v_mgr, 
              TRUNC (SYSDATE, 'DD'), v_sal, v_comm, v_deptno);
    ELSE
      DBMS_OUTPUT.PUT_LINE('Invalid Deptno ,try again!');
    END IF;
END new_emp;
/

练习 7:分页查询
create or replace procedure TABLEPAGE_SELECT
 (v_page_size number,v_current_page number)
as
cursor c(v_page_size number,v_current_page number) is SELECT *
            FROM (select rownum rn,emp.*
                  FROM emp WHERE rownum <= v_page_size*v_current_page)
            WHERE rn >= (v_page_size*v_current_page-(v_page_size-1));
begin
  dbms_output.put_line('pagesize : '||v_page_size||' rows');
  dbms_output.put_line('current page : '||v_current_page||' page');
  for r in c(v_page_size,v_current_page) loop
      dbms_output.put_line('employee name : '||r.ename||' employee''s salary : '||r.sal);
  end loop;
end;
/

exec TABLEPAGE_SELECT(5,2);
***************************************************8
create or replace procedure page_disp
(v_page_size number,v_current_page number)
is
   cursor c
   is
   select * from 
       (select rownum rn,e.* from employees e 
        where rownum <= v_page_size*v_current_page)
   where rn >= v_page_size*v_current_page-v_page_size+1;
begin
   dbms_output.put_line('current_page_size:'|| v_page_size);
   dbms_output.put_line('current_page is: '||v_current_page);
   for e in c loop
       dbms_output.put_line(e.rn||':'||e.employee_id||':'||e.last_name);
   end loop;
end;

exec page_disp(5,3);

************************************
create or replace procedure page_select
(v_page_size number,v_current_page number)
is
    cursor c1(v_page_size number,v_current_page number)
    is
    select * from ( select rownum rn,employees.* from employees 
                    where rownum <= v_page_size* v_current_page)
    where rn >=(v_page_size* v_current_page - v_page_size +1);
begin
    dbms_output.put_line('PageSize: '|| v_page_size||' rows');
    dbms_output.put_line('CurrentPage: '|| v_current_page|| ' page');
    for emp_info in c1(v_page_size, v_current_page) loop
        dbms_output.put_line('Name: '||emp_info.last_name ||' -- '|| 'Salary: '|| emp_info.salary);
    end loop;
end;
set serveroutput on
exec page_select(10,3);

练习 8:本地动态SQL语句(execute immediate)
create or replace procedure proc_test
(
　　table_name in varchar2,　　 --表名    varchar2(10) shi cuo de
　　field1 in varchar2,　　　　　--字段名
　　datatype1 in varchar2,　　　--字段类型
　　field2 in varchar2,　　　　　--字段名
　　datatype2 in varchar2　　　 --字段类型
) as 
　　str_sql varchar2(500);
begin 
　　str_sql:='create table '||table_name||'('||field1||' '||datatype1||','||field2||' '||datatype2||')';
　　execute immediate str_sql;　 --动态执行DDL语句
　　exception 
　　　　when others then 
　　　　　　null;
end ;
/

SQL> execute proc_test('dinya_test','id','number(8) not null','name','varchar2(100)');

PL/SQL procedure successfully completed

SQL> desc dinya_test;
Name Type　　　　　Nullable Default Comments 
---- ------------- -------- ------- -------- 
ID　 NUMBER(8)

NAME VARCHAR2(100) Y

##要给hr用户单独授予create table 权限：
sys> grant create table to hr;

create or replace procedure table_create
(table_name varchar2,
 field1 varchar2,
 dtype1 varchar2,
 field2 varchar2,
 dtype2 varchar2
 )
is
   sql_str varchar2(500);
begin
   sql_str := 'create table '||table_name||'('||field1||' '||dtype1||','||field2||' '||dtype2||')';
--  dbms_output.put_line(sql_str);
   execute immediate sql_str;
end;
drop table tt purge; 
exec table_create('tt','id','number','name','varchar2(20)');

declare
   v_sql VARCHAR2(1000);
begin
   v_sql := 'create table hr.t1227(id number)';
   dbms_output.put_line(v_sql);
   execute immediate v_sql;
end;
.
查看过程的源代码:
select text from user_source where name='TABLEPAGE_SELECT';

为procedure加密:
oracle$wrap iname=1.sql
@1.plb
[oracle$db254~]cat p.sql
create or replace procedure query_emp
(v_eno  in employees.employee_id%type,
 o_name  out employees.last_name%type,
 o_sal out employees.salary%type)
is
begin
   select last_name,salary into o_name, o_sal
   from employees
   where employee_id=v_eno;
end query_emp;

[oracle$db254 ~]wrap iname=p.sql
[oracle$db254 ~]ls p.sql p.plb
[oracle@db11g ~]$ file p.plb
ps.plb: ASCII text
[oracle@db11g ~]$ cat p.plb 
create or replace procedure test_p wrapped 
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
45 7d
Uf/v0f5sZZ5by4/ZHjt0V9oxYSIwg5nnm7+fMr2ywFwWoWLR9JaPm3SLwMAy/tKGCWlp57K4
UnRSXKXnJceBsmnnn7LM55/SUnQYoAJpg0A8ceI/0Tx0pnWm04g=

/

hr@orcl11g>@p.plb

HR@orcl11g> select text from user_source where name='TEST_P';

TEXT
--------------------------------------------------------------------------------
procedure test_p wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd

TEXT
--------------------------------------------------------------------------------
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
45 7d
Uf/v0f5sZZ5by4/ZHjt0V9oxYSIwg5nnm7+fMr2ywFwWoWLR9JaPm3SLwMAy/tKGCWlp57K4
UnRSXKXnJceBsmnnn7LM55/SUnQYoAJpg0A8ceI/0Tx0pnWm04g=

存储过程的使用授权：
HR@prod> grant execute on hr.query_emp to scott;