pl/sql例子

1、使用游标、loop、%type、%rowtype

DECLARE
  Last_name VARCHAR2(50);
  email     employees.email%type;
  emp       employees%rowtype;
  Cursor c1 IS
    SELECT * FROM employees;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1
      INTO emp;
    EXIT WHEN c1%NOTFOUND;
    email:=emp.email;
    Last_name:=emp.last_name;
    DBMS_OUTPUT.PUT_LINE(emp.Last_name||'   '||email||'    '||last_name);
  END LOOP;
END;

2、Execption hander

DECLARE
   Emp_number   INTEGER := 9999;
   Emp_name     VARCHAR2(10);
BEGIN
   SELECT first_name INTO Emp_name FROM employees
      WHERE employee_id = Emp_number;   -- no such number
   DBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No such employee: ' || Emp_number);
END;

3、自定义Exception

declare
  total number(5);
  sizesmall Exception;
begin
  select count(1) into total from employees;
  if (total) < 1000 then
    raise sizesmall;
  else
    dbms_output.put_line('success'||total);
  end if;
 
Exception
  when sizesmall then
    dbms_output.put_line('sizesmall'||total);
end;

4、循环cursor

DECLARE
  CURSOR c1 IS
    SELECT last_name, salary, hire_date, job_id FROM employees;
  employee_rec c1%ROWTYPE;
BEGIN
  OPEN c1;
  loop
    FETCH c1
      INTO employee_rec;
    DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);
    EXIT WHEN c1%NOTFOUND;
  end loop;
END;

5、package

create or replace package mypackage is
  procedure get_emp_by_id(emp_id number);
end mypackage;
 
 
create or replace package body mypackage is
  procedure get_emp_by_id(emp_id number) is
    emp_name varchar(20);
  begin
    select first_name
      into emp_name
      from employees
     where employee_id = emp_id;
    dbms_output.put_line('first_name is  ' || emp_name);
  exception
    when no_data_found then
      dbms_output.put_line('data not found');
  end;
 
end mypackage;
 
begin 
  mypackage.get_emp_by_id(198);
end;

6、function

create or replace function get_emp_count return number is
  total number(5);
begin 
select count(1) into total from employees;
return total
end;
 
 
begin
    dbms_output.put_line(get_emp_count());
end;
 

7、collection

DECLARE
  TYPE staff_list IS TABLE OF employees.employee_id%TYPE;
  staff staff_list;
  lname employees.last_name%TYPE;
  fname employees.first_name%TYPE;
BEGIN
  staff := staff_list(100, 114, 115, 120, 122);
  FOR i IN staff.FIRST .. staff.LAST LOOP
    SELECT last_name, first_name
      INTO lname, fname
      FROM employees
     WHERE employees.employee_id = staff(i);
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(staff(i)) || ': ' || lname || ', ' ||
                         fname);
  END LOOP;
END;
 

8、savepoint

CREATE TABLE results ( res_name VARCHAR(20), res_answer VARCHAR2(3) );
CREATE UNIQUE INDEX res_name_ix ON results (res_name);
INSERT INTO results VALUES ('SMYTHE', 'YES');
INSERT INTO results VALUES ('JONES', 'NO');
 
DECLARE
  name   VARCHAR2(20) := 'SMYTHE';
  answer VARCHAR2(3) := 'NO';
  suffix NUMBER := 1;
BEGIN
  FOR i IN 1 .. 5 LOOP
    -- try 5 times
    BEGIN
      -- sub-block begins
      SAVEPOINT start_transaction; -- mark a savepoint
      /* Remove rows from a table of survey results. */
      DELETE FROM results WHERE res_answer = 'NO';
      /* Add a survey respondent's name and answers. */
      INSERT INTO results VALUES (name, answer);
      -- raises DUP_VAL_ON_INDEX if two respondents have the same name
      COMMIT;
      EXIT;
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        ROLLBACK TO start_transaction; -- undo changes
        suffix := suffix + 1; -- try to fix problem
        name   := name || TO_CHAR(suffix);
    END; -- sub-block ends
  END LOOP;
END
 

9、Declaring Nested Tables, Varrays, and Associative Arrays

DECLARE
  TYPE nested_type IS TABLE OF VARCHAR2(30);
  TYPE varray_type IS VARRAY(5) OF INTEGER;
  TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
  TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
  v1 nested_type;
  v2 varray_type;
  v3 assoc_array_num_type;
  v4 assoc_array_str_type;
  v5 assoc_array_str_type2;
BEGIN
  -- an arbitrary number of strings can be inserted v1
  v1 := nested_type('Shipping', 'Sales', 'Finance', 'Payroll');
  v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
  v3(99) := 10; -- Just start assigning to elements
  v3(7) := 100; -- Subscripts can be any integer values
  v4(42) := 'Smith'; -- Just start assigning to elements
  v4(54) := 'Jones'; -- Subscripts can be any integer values
  v5('Canada') := 'North America'; -- Just start assigning to elements
  v5('Greece') := 'Europe'; -- Subscripts can be string values
END;
 
 

10  Using the RETURNING Clause with a Record

DECLARE
  TYPE EmpRec IS RECORD(
    last_name employees.last_name%TYPE,
    salary    employees.salary%TYPE);
  emp_info EmpRec;
  emp_id   NUMBER := 100;
BEGIN
  UPDATE employees
     SET salary = salary * 1.1
   WHERE employee_id = emp_id
  RETURNING last_name, salary INTO emp_info;
  DBMS_OUTPUT.PUT_LINE('Just gave a raise to ' || emp_info.last_name ||
                       ', who now makes ' || emp_info.salary);
  ROLLBACK;
END;
 

11  Using BULK COLLECT With a SELECT INTO Statement

DECLARE
  TYPE EmployeeSet IS TABLE OF employees%ROWTYPE;
  underpaid EmployeeSet; -- Holds set of rows from EMPLOYEES table.
  CURSOR c1 IS
    SELECT first_name, last_name FROM employees;
  TYPE NameSet IS TABLE OF c1%ROWTYPE;
  some_names NameSet; -- Holds set of partial rows from EMPLOYEES table.
BEGIN
  -- With one query, we bring all the relevant data into the collection of records.
  SELECT * BULK COLLECT
    INTO underpaid
    FROM employees
   WHERE salary < 5000
   ORDER BY salary DESC;
  -- Now we can process the data by examining the collection, or passing it to
  -- a separate procedure, instead of writing a loop to FETCH each row.
  DBMS_OUTPUT.PUT_LINE(underpaid.COUNT || ' people make less than 5000.');
  FOR i IN underpaid.FIRST .. underpaid.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(underpaid(i)
                         .last_name || ' makes ' || underpaid(i).salary);
  END LOOP;
  -- We can also bring in just some of the table columns.
  -- Here we get the first and last names of 10 arbitrary employees.
  SELECT first_name, last_name BULK COLLECT
    INTO some_names
    FROM employees
   WHERE ROWNUM < 11;
  FOR i IN some_names.FIRST .. some_names.LAST LOOP
    DBMS_OUTPUT.PUT_LINE('Employee = ' || some_names(i)
                         .first_name || ' ' || some_names(i).last_name);
  END LOOP;
END;
 

12、Using a Cursor Expression

DECLARE
  TYPE emp_cur_typ IS REF CURSOR;
  emp_cur   emp_cur_typ;
  dept_name departments.department_name%TYPE;
  emp_name  employees.last_name%TYPE;
  CURSOR c1 IS
    SELECT department_name,
           -- second item in the result set is another result set,
           -- which is represented as a ref cursor and labelled "employees".
           CURSOR (SELECT e.last_name
                     FROM employees e
                    WHERE e.department_id = d.department_id) employees
      FROM departments d
     WHERE department_name like 'A%';
BEGIN
  OPEN c1;
  LOOP
    FETCH c1
      INTO dept_name, emp_cur;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Department: ' || dept_name);
    -- for each row in the result set, the result set from a subquery is processed
    -- the set could be passed to a procedure for processing rather than the loop
    LOOP
      FETCH emp_cur
        INTO emp_name;
      EXIT WHEN emp_cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('-- Employee: ' || emp_name);
    END LOOP;
  END LOOP;
  CLOSE c1;
END;

13、Dynamic SQL

CREATE OR REPLACE PROCEDURE raise_emp_salary(column_value NUMBER,
                                             emp_column   VARCHAR2,
                                             amount       NUMBER) IS
  v_column VARCHAR2(30);
  sql_stmt VARCHAR2(200);
BEGIN
  -- determine if a valid column name has been given as input
  SELECT COLUMN_NAME
    INTO v_column
    FROM USER_TAB_COLS
   WHERE TABLE_NAME = 'EMPLOYEES'
     AND COLUMN_NAME = emp_column;
  sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE ' ||
              v_column || ' = :2';
  EXECUTE IMMEDIATE sql_stmt
    USING amount, column_value;
  IF SQL%ROWCOUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('Salaries have been updated for: ' || emp_column ||
                         ' = ' || column_value);
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Invalid Column: ' || emp_column);
END raise_emp_salary;

 
 
 
 
DECLARE
  plsql_block VARCHAR2(500);
BEGIN
  -- note the semi-colons (;) inside the quotes '...'
  plsql_block := 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;';
  EXECUTE IMMEDIATE plsql_block
    USING 110, 'DEPARTMENT_ID', 10;
  EXECUTE IMMEDIATE 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;'
    USING 112, 'EMPLOYEE_ID', 10;
END;
 
 
 
 
DECLARE
  sql_stmt  VARCHAR2(200);
  v_column  VARCHAR2(30) := 'DEPARTMENT_ID';
  dept_id   NUMBER(4) := 46;
  dept_name VARCHAR2(30) := 'Special Projects';
  mgr_id    NUMBER(6) := 200;
  loc_id    NUMBER(4) := 1700;
BEGIN
  -- note that there is no semi-colon (;) inside the quotes '...'
  EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
  sql_stmt := 'INSERT INTO departments VALUES (:1, :2, :3, :4)';
  EXECUTE IMMEDIATE sql_stmt
    USING dept_id, dept_name, mgr_id, loc_id;
  EXECUTE IMMEDIATE 'DELETE FROM departments WHERE ' || v_column ||
                    ' = :num'
    USING dept_id;
  EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
  EXECUTE IMMEDIATE 'DROP TABLE bonus';
END;

15、Dynamic SQL with BULK COLLECT INTO Clause

DECLARE
  TYPE EmpCurTyp IS REF CURSOR;
  TYPE NumList IS TABLE OF NUMBER;
  TYPE NameList IS TABLE OF VARCHAR2(25);
  emp_cv EmpCurTyp;
  empids NumList;
  enames NameList;
  sals   NumList;
BEGIN
  OPEN emp_cv FOR 'SELECT employee_id, last_name FROM employees';
  FETCH emp_cv BULK COLLECT
    INTO empids, enames;
  CLOSE emp_cv;
  EXECUTE IMMEDIATE 'SELECT salary FROM employees' BULK COLLECT
    INTO sals;
END;
 
 

17、Dynamic SQL with RETURNING BULK COLLECT INTO Clause

DECLARE
  TYPE NameList IS TABLE OF VARCHAR2(15);
  enames    NameList;
  bonus_amt NUMBER := 50;
  sql_stmt  VARCHAR(200);
BEGIN
  sql_stmt := 'UPDATE employees SET salary = salary + :1 
                RETURNING last_name INTO :2';
  EXECUTE IMMEDIATE sql_stmt
    USING bonus_amt
    RETURNING BULK COLLECT
    INTO enames;
    for i in enames.first..enames.last
    loop
     show(enames(i));
    end loop;
END;
 

18、Dynamic SQL Inside FORALL Statement

DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  TYPE NameList IS TABLE OF VARCHAR2(15);
  empids NumList;
  enames NameList;
BEGIN
  empids := NumList(101, 102, 103, 104, 105);
  FORALL i IN 1 .. 5 EXECUTE IMMEDIATE 'UPDATE employees SET salary = salary * 1.04 WHERE employee_id = :1
         RETURNING last_name INTO :2' USING empids(i)
                   RETURNING BULK COLLECT INTO enames
    ;
END;
 

19、Accessing %ROWCOUNT For an Explicit Cursor

DECLARE

  TYPE cursor_ref IS REF CURSOR;
  c1 cursor_ref;
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  rec_tab      emp_tab;
  rows_fetched NUMBER;
BEGIN
  OPEN c1 FOR 'SELECT * FROM employees';
  FETCH c1 BULK COLLECT
    INTO rec_tab;
  rows_fetched := c1%ROWCOUNT;
  DBMS_OUTPUT.PUT_LINE('Number of employees fetched: ' ||
                       TO_CHAR(rows_fetched));
END;
 
 

Exception ORA Error SQLCODE Raise When ...
ACCESS_INTO_NULL 06530 -6530 A program attempts to assign values to the attributes of
an uninitialized object
CASE_NOT_FOUND 06592 -6592 None of the choices in the WHEN clauses of a CASE
statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL 06531 -6531 A program attempts to apply collection methods other
than EXISTS to an uninitialized nested table or varray,
or the program attempts to assign values to the
elements of an uninitialized nested table or varray.
CURSOR_ALREADY_OPEN 06511 -6511 A program attempts to open an already open cursor. A
cursor must be closed before it can be reopened. A
cursor FOR loop automatically opens the cursor to
which it refers, so your program cannot open that
cursor inside the loop.
DUP_VAL_ON_INDEX 00001 -1 A program attempts to store duplicate values in a
column that is constrained by a unique index.
INVALID_CURSOR 01001 -1001 A program attempts a cursor operation that is not
allowed, such as closing an unopened cursor.
INVALID_NUMBER 01722 -1722 n a SQL statement, the conversion of a character string
into a number fails because the string does not
represent a valid number. (In procedural statements,
VALUE_ERROR is raised.) This exception is also raised
when the LIMIT-clause expression in a bulk FETCH
statement does not evaluate to a positive number.
LOGIN_DENIED 01017 -1017 A program attempts to log on to Oracle with an invalid
username or password.
NO_DATA_FOUND 01403 +100 A SELECT INTO statement returns no rows, or your
program references a deleted element in a nested table
or an uninitialized element in an index-by table.
Because this exception is used internally by some SQL
functions to signal completion, you should not rely on
this exception being propagated if you raise it within a
function that is called as part of a query.
NOT_LOGGED_ON 01012 -1012 A program issues a database call without being
connected to Oracle.
PROGRAM_ERROR 06501 -6501 PL/SQL has an internal problem.
ROWTYPE_MISMATCH 06504 -6504 The host cursor variable and PL/SQL cursor variable
involved in an assignment have incompatible return
types. When an open host cursor variable is passed to a
stored subprogram, the return types of the actual and
formal parameters must be compatible.
SELF_IS_NULL 30625 -30625 A program attempts to call a MEMBER method, but the
instance of the object type has not been initialized. The
built-in parameter SELF points to the object, and is
always the first parameter passed to a MEMBER method.
STORAGE_ERROR 06500 -6500 PL/SQL runs out of memory or memory has been
corrupted.
SUBSCRIPT_BEYOND_COUNT 06533 -6533 A program references a nested table or varray element
using an index number larger than the number of
elements in the collection.
SUBSCRIPT_OUTSIDE_LIMIT 06532 -6532 A program references a nested table or varray element
using an index number (-1 for example) that is outside
the legal range.
SYS_INVALID_ROWID 01410 -1410 The conversion of a character string into a universal
rowid fails because the character string does not
represent a valid rowid.
TIMEOUT_ON_RESOURCE 00051 -51 A time out occurs while Oracle is waiting for a
resource.
TOO_MANY_ROWS 01422 -1422 A SELECT INTO statement returns more than one row.
VALUE_ERROR 06502 -6502 An arithmetic, conversion, truncation, or
size-constraint error occurs. For example, when your
program selects a column value into a character
variable, if the value is longer than the declared length
of the variable, PL/SQL aborts the assignment and
raises VALUE_ERROR. In procedural statements,
VALUE_ERROR is raised if the conversion of a character
string into a number fails. (In SQL statements,
INVALID_NUMBER is raised.)
ZERO_DIVIDE 01476 -1476 A program attempts to divide a number by zero






%FOUND Attribute: Has a DML Statement Changed Rows?
Until a SQL data manipulation statement is executed, %FOUND yields NULL. Thereafter,
%FOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected one or
more rows, or a SELECT INTO statement returned one or more rows. Otherwise,
%FOUND yields FALSE. In Example 6–7, you use %FOUND to insert a row if a delete
succeeds.
Example 6–7 Using SQL%FOUND
CREATE TABLE dept_temp AS SELECT * FROM departments;
DECLARE
  dept_no NUMBER(4) := 270;
BEGIN
  DELETE FROM dept_temp WHERE department_id = dept_no;
  IF SQL%FOUND THEN  -- delete succeeded
    INSERT INTO dept_temp VALUES (270, 'Personnel', 200, 1700);
  END IF;
END;
/
%ISOPEN Attribute: Always FALSE for Implicit Cursors
Oracle closes the SQL cursor automatically after executing its associated SQL
statement. As a result, %ISOPEN always yields FALSE.
%NOTFOUND Attribute: Has a DML Statement Failed to Change Rows?
%NOTFOUND is the logical opposite of %FOUND. %NOTFOUND yields TRUE if an INSERT,
UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned
no rows. Otherwise, %NOTFOUND yields FALSE.
%ROWCOUNT Attribute: How Many Rows Affected So Far?
%ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, or DELETE
statement, or returned by a SELECT INTO statement. %ROWCOUNT yields 0 if an
INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement
returned no rows. In Example 6–8, %ROWCOUNT returns the number of rows that have
been deleted.
Example 6–8 Using SQL%ROWCOUNT
CREATE TABLE employees_temp AS SELECT * FROM employees;
DECLARE
  mgr_no NUMBER(6) := 122;
BEGIN
  DELETE FROM employees_temp WHERE manager_id = mgr_no
DBMS_OUTPUT.PUT_LINE('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT));
END;



Using Collection Methods
Collection methods make collections easier to use, and make your applications easier
to maintain. These methods include COUNT, DELETE, EXISTS, EXTEND, FIRST, LAST,
LIMIT, NEXT, PRIOR, and TRIM.
A collection method is a built-in function or procedure that operates on collections and
is called using dot notation. The following apply to collection methods:
■ Collection methods cannot be called from SQL statements.
■ EXTEND and TRIM cannot be used with associative arrays.
■ EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, and NEXT are functions; EXTEND,
TRIM, and DELETE are procedures.
■ EXISTS, PRIOR, NEXT, TRIM, EXTEND, and DELETE take parameters
corresponding to collection subscripts, which are usually integers but can also be
strings for associative arrays.
■ Only EXISTS can be applied to atomically null collections. If you apply another
method to such collections, PL/SQL raises COLLECTION_IS_NULL.