oracle procedure-pl

sdfsdnfslk

　　数据字典：
　　用来查看有名块的源代码
　　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-(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-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 - 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;