oracle pl/sql模板代码

 

 

select * from scott.emp;

==============for 循环=======================

declare
    empno NUMBER(4,0);
 ename VARCHAR2(10 BYTE);
    sal NUMBER(7,2);
begin
    for REC in
        (select empno, ename, sal from scott.emp)
    loop
        begin
            empno := REC.empno;
            ename := REC.ename;
            sal := REC.sal;
            /*---------------------------------写入一条明细开始------------------------------*/
            if NVL(empno,0) <> 0 then
                begin
                    dbms_output.put_line('记录： ' || empno || ' ' || ename || ' ' || sal );
                end;
            end if;
            /*---------------------------------写入一条明细开始------------------------------*/
        end;
    end loop;
end;
/

--===============带参数的游标==================--
DECLARE
   dept_code emp.deptno%TYPE; --声明列类型变量三个
   emp_code   emp.empno%TYPE;
   emp_name   emp.ename%TYPE;
   CURSOR emp_cur(deptparam NUMBER) IS
     SELECT empno, ename FROM EMP WHERE deptno = deptparam; --声明显示游标
BEGIN
   dept_code := &部门编号; --请用户输入想查看的部门编号
   OPEN emp_cur(dept_code); --打开游标
   LOOP
     --死循环
     FETCH emp_cur
       INTO emp_code, emp_name; --提取游标值赋给上面声明的变量
     EXIT WHEN emp_cur%NOTFOUND; --如果游标里没有数据则退出循环
     DBMS_OUTPUT.PUT_LINE(emp_code || '' || emp_name); --输出查询
   END LOOP;
   CLOSE emp_cur; --关闭游标
END;

--=================REF游标==================--

ACCEPT tab FROMPT '你想查看什么信息?员工(E)或部门信息(D):'; --使用ACCEPT命令弹出对话框让用户输入数据
DECLARE
   TYPE refcur_t IS REF CURSOR; --声明REF游标类型
   refcur     refcur_t; --声明REF游标类型的变量
   pid        NUMBER;
   p_name     VARCHAR2(100);
   selection VARCHAR2(1) := UPPER(SUBSTR('&tab', 1, 1)); --截取用户输入的字符串并转换为大写
BEGIN
   IF selection = 'E' THEN
     --如果输入的是'E',则打开refcurr游标,并将员工表查询出来赋值给此游标
     OPEN refcur FOR
       SELECT EMPNO ID, ENAME NAME FROM EMP;
     DBMS_OUTPUT.PUT_LINE('=====员工信息=====');
   ELSIF selection = 'D' THEN
     --如果输入是'D',则打开部门表
     OPEN refcur FOR
       SELECT deptno id, dname name FROM DEPT;
     DBMS_OUTPUT.PUT_LINE('=====部门信息======');
   ELSE
     --否则返回结束
     DBMS_OUTPUT.PUT_LINE('请输入员工信息(E)或部门信息(D)');
     RETURN;
   END IF;
   FETCH refcur
     INTO pid, p_name; --提取行
   WHILE refcur%FOUND LOOP
     DBMS_OUTPUT.PUT_LINE('#' || pid || ':' || p_name);
     FETCH refcur
       INTO pid, p_name;
   END LOOP;
   CLOSE refcur; --关闭游标
END;

--===================动态SQL=================--
VARIABLE maxsal NUMBER; --声明变量
EXECUTE :maxsal := 2500; --执行引用并给变量赋值
DECLARE
   r_emp EMP%ROWTYPE; --声明一个行类型变量
   TYPE c_type IS REF CURSOR; --声明REF游标类型
   cur       c_type; --声明REF游标类型的变量
   p_salary NUMBER; --声明一个标量变量
BEGIN
   p_salary := :maxsal; --引用变量
   --使用USING语句将引用到的值传给动态SQL语句'SAL >: 1'中的'1'
   OPEN cur FOR 'SELECT * FROM EMP WHERE SAL >: 1 ORDER BY SAL DESC'
     USING p_salary;
   DBMS_OUTPUT.PUT_LINE('薪水大于' || p_salary || '的员工有:');
   LOOP
     FETCH cur
       INTO r_emp;
     EXIT WHEN cur%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE('编号:' || r_emp.empno || '姓名:' || r_emp.ename ||
                          '薪水:' || r_emp.sal);
   END LOOP;
   CLOSE cur; --关闭游标
END;

-- 例子：
CREATE OR REPLACE PROCEDURE x_ne_change
AS
   CURSOR cur_new
   IS
      SELECT int_id, omc_id || ':' || msc_id || ':' || bsc_id AS related_id,
             omc_id, msc_id, bsc_id, ne_name, cell_id, lac, freq_band,
             trx_count, tch, sdcch, gprs_enabled, gprs_trx, dedicated_pdch,
             max_pdch, device_type, software_version, dumpfre_type, site_no,
             cell_no, rac, ncc, bcc, sms_cb_used, bsc_omc_int_id, omc_int_id,
             TIMESTAMP
        FROM appuser.K_C_CELL
       WHERE TIMESTAMP = '2004-04-23 8' AND cell_id < 2000;

   CURSOR cur_old (c_no NUMBER)
   IS
      SELECT int_id, omc_id || ':' || msc_id || ':' || bsc_id AS related_id,
             omc_id, msc_id, bsc_id, ne_name, cell_id, lac, freq_band,
             trx_count, tch, sdcch, gprs_enabled, gprs_trx, dedicated_pdch,
             max_pdch, device_type, software_version, dumpfre_type, site_no,
             cell_no, rac, ncc, bcc, sms_cb_used, bsc_omc_int_id, omc_int_id,
             TIMESTAMP
        FROM appuser.K_C_CELL
       WHERE TIMESTAMP = '2004-04-21 6' AND cell_id = c_no;
BEGIN
   FOR v_new IN cur_new
   LOOP
      FOR v_old IN cur_old (v_new.cell_id)
      LOOP
         BEGIN
            IF v_new.related_id <>; v_old.related_id
            THEN
               INSERT INTO TEST_NE_CHANGE
                           (omc_id, omc_int_id, ne_id,
                            old_value, now_value,
                            modify_item, modify_time
                           )
                    VALUES (v_new.omc_id, v_new.omc_int_id, v_new.cell_id,
                            v_old.related_id, v_new.related_id,
                            'related_id', v_new.TIMESTAMP
                           );

               COMMIT;
            END IF;

            IF v_new.tch <>; v_old.tch
            THEN
               INSERT INTO TEST_NE_CHANGE
                           (omc_id, omc_int_id, ne_id,
                            old_value, now_value, modify_item, modify_time
                           )
                    VALUES (v_new.omc_id, v_new.omc_int_id, v_new.cell_id,
                            v_old.tch, v_new.tch, 'TCH', v_new.TIMESTAMP
                           );

               COMMIT;
            END IF;
         END;
      END LOOP;
   END LOOP;
END;

 

--- 多行数据提取
declare
 cursor cur_tsalary is
    select employeeid,positionid from tsalary whererownum < 10;
 type rec_tsalary isrecord(
 employeeid tsalary.employeeid%type,
 positionid tsalary.positionid%type);
 type all_rec_tsalary_type istableof rec_tsalary;
 all_rec_tsalary all_rec_tsalary_type;
begin
 --一次处理所有
    fetch cur_tsalary bulkcollectinto all_rec_tsalary limit5;
    for i in1..all_rec_tsalary.countloop      dbms_output.put_line(all_rec_tsalary(i).employeeid||all_rec_tsalary(i).positionid);
    endloop;
--使用limit分批提出大量数据
 open cur_tsalary;
 loop
    fetch cur_tsalary bulkcollectinto all_rec_tsalary limit5;
    for i in1..all_rec_tsalary.countloop      dbms_output.put_line(all_rec_tsalary(i).employeeid||all_rec_tsalary(i).positionid);
    endloop;
    exitwhen cur_tsalary%notfound;
 endloop;
 close cur_tsalary;
end;

 -- 例六：%NotFound
 
 BEGIN
   DELETE   FROM  Rs_Employees
     Where  HeTongId = ' WL-090001 ' ;
 
   if  sql % Notfound  then
    Dbms_Output.put_line( ' 没有找到要删除的记录 ' );
   else
    Dbms_Output.put_line( ' 已删除记录 ' );
   end   if ;

 END ;
  
  
  
 -- 例七：%RowCount，查询记录行数
 Declare
  v_name Rs_Employees.Name % type;
 BEGIN
   SELECT  Name  Into  v_Name
     FROM  Rs_Employees
     Where  HeTongId = ' WL-090010 ' ;
 
   if  sql % RowCount   > 0   Then
    Dbms_Output.put_line( ' 已从表中选择行,Name为： ' || v_Name);
   else
    Dbms_Output.put_line( ' 从表中未选择行 ' );
   end   if ;

 END ;
  
 -- 再演示以下代码
 BEGIN
   DELETE   FROM  Rs_Employees
     Where  HeTongId <= ' WL-090010 ' ;
 
  Dbms_Output.put_line( ' 已从表中删除 ' || To_Char(sql % RowCount ) || ' 条记录 ' );
 END ;  
  
 
 
 -- 例八：显式游标
-- 以下示例在所有游标的记录中的Name字段中加一字串
-- 同时在此示范了如何使用%NotFound属性
-- set serveroutput on;
 Declare
  v_Id    Rs_Employees.Hetongid % type;
  v_name  Rs_Employees.Name % type;
  v_Count  Number : = 0 ;
 
   Cursor  MyCur  Is
     SELECT  HetongId,Name  FROM  Rs_Employees
       Where  HeTongId <= ' WL-090010 ' ;
     
 BEGIN
   -- 打开游标 
    Open  MyCur;
   -- 进入循环
   Loop
     Fetch  MyCur  Into  v_id,v_name;
     Exit   When  MyCur % NotFound;
   
     Update  Rs_Employees
       Set  Name  =  Name  ||   ' X '
       Where  HeTongId = v_Id;
    v_Count : =  v_Count  +   1 ;
   End  Loop;
 
  Dbms_Output.put_line( ' 已更新 ' || v_Count || ' 行 ' );
 END ;

 -- 例10：以下示范%RowCount和%IsOpen，同时示范了%RowType的使用
-- 例10：以下示例%RowCount
 Declare
  v_Row   Rs_Employees % RowType;
 
   Cursor  MyCur  Is
     SELECT   *   FROM  Rs_Employees
       Where  HeTongId <= ' WL-090010 ' ;
 BEGIN
   if   Not  MyCur % IsOpen  then
    Dbms_Output.put_line( ' 游标未打开 ' );
     -- 打开游标 
      Open  MyCur;
   end   if ;

   -- 进入循环
   Loop
     Fetch  MyCur  Into  v_row;
     Exit   When  MyCur % NotFound;
 
    Dbms_Output.put_line( ' 当前已取得 ' || MyCur % RowCount || ' 行 ' );
    Dbms_Output.put_line( ' 姓名： ' || v_row.Name || '      ' || ' 合同号： ' || v_row.HeTongId);
    Dbms_Output.put_line( '' );
   End  Loop;
 
  Dbms_Output.put_line( ' 总共已取得 ' || MyCur % RowCount || ' 行 ' );
 
   if  MyCur % IsOpen  then
    Dbms_Output.put_line( ' 游标已打开 ' );
     Close  MyCur;
   end   if ;

   if   Not  MyCur % IsOpen  then
    Dbms_Output.put_line( ' 游标已关闭 ' );
   end   if ;
 
 END ;

 -- 例12：查询嵌套表中数据的游标
-- 1 创建类型
    CREATE   OR   REPLACE  TYPE emp_type  As  Object
   (eno    number ,
    ename  varchar2 ( 20 ),
    esal   number );
 -- 2 使用Table of 子句创建Table类型
    CREATE  TYPE emp_nt  AS   Table   Of  emp_type;
 -- 3 使用emp_nt数据类型创建myemp表
    CREATE