Хранимые процедуры и функции. Типы параметров, синтаксис описания формальных параметров

create or replace procedure my_p2
 (s1 out varchar2, s2 in out nocopy varchar2)
as
begin
    s1 := 'aaaaaaaaaaaaaaaaaaaaaaaaaa';
    s2 := 'bbbbbbbbbbbbbbbbbbbbbbbbbb';
end;

create or replace package PackageTest
as
    type point is record
        (
            x number,
            y number
         );

    tpoint point;
    n number;
         
    procedure p(ii in number, ss out varchar2);
    function f(n in point) return number;
    function f(x in number, y in number) return number;
end PackageTest;

create or replace package body PackageTest
as
    type t1 is record
        (
            i number,
            j number
         );

    cnt number;
    trow1 test%rowtype;
     
    procedure p(ii in number, ss out varchar2)
    is
    begin
        select s into ss from test where i = ii;
    exception
        when no_data_found then
            ss := 'empty string';
    end;

    function f(n in point) return number
    as
    begin
        return n.x+n.y;
    end;

    function f(x in number, y in number) return number
    as
    begin
        return x+y;
    end;
begin
    trow1.i := 1;
    trow1.s := 'null string';
    n := 100;
    cnt := -10;
end PackageTest;

create or replace procedure PackageTest_test
as
-- error
--    d1 PackageTest.tl;
    s varchar2(100);
    n PackageTest.point;
begin
    n.x := 100;
    n.y := 200;
    PackageTest.p(1, s);
    dbms_output.put_line('s = ' || s);
    dbms_output.put_line('f((100, 100)) = ' || PackageTest.f(n));
    dbms_output.put_line('f(100, 100) = ' || PackageTest.f(n));
    dbms_output.put_line('n = ' || PackageTest.n);
-- error
--    dbms_output.put_line('cnt = ' || PackageTest.cnt);
end;

desc user_objects

select object_name, object_type, status from user_objects;

create or replace function f(i in number)
return varchar2
as
begin
    return to_char(i+1);
end;

create or replace procedure p(ii in number, ss out varchar2)
as
begin
    select s into ss from test where i = ii;
exception
    when no_data_found then
        ss := 'no data';
end;

select substr(object_name, 1, 30), object_type, 
  status from user_objects;

select substr(object_name, 1, 30), object_type, 
     status from user_objects where object_type in 
             ('PROCEDURE', 'FUNCTION', 'PACKAGE', 
               'PACKAGE BODY') and (object_name like 'P%' 
                 or object_name like 'F%');

declare
    i number;
    s varchar2(100);
begin
    i := 10;
    s := f(i);
    dbms_output.put_line(s);

    i := 1;
    p(i, s);
    dbms_output.put_line(s);
end;

alter table test add h date;
alter table test drop column h;

alter procedure p compile;

select * from user_errors;

alter function f compile;

alter package packagetest compile specification;
alter package packagetest compile body;
alter package packagetest compile package;

select substr(object_name, 1, 30), object_type, 
     status from user_objects where object_type in 
             ('PROCEDURE', 'FUNCTION', 'PACKAGE', 
                'PACKAGE BODY') and (object_name like 'P%'
                   or object_name like 'F%');

select * from user_errors;

----------------------------------------------------
set echo off
set linesize 1000
set pagesize 500
set heading off
set feedback off
spool c:\tmp\a.sql
select 'ALTER ' || object_type || ' ' || object_name || ' COMPILE;
  ' from user_objects where object_type in 
        ('PROCEDURE', 'FUNCTION', 'PACKAGE');
select 'SELECT * FROM USER_ERRORS;' from dual;
spool off
@c:\tmp\a.sql
host del c:\tmp\a.sql
-----------------------------------------------------

package org.mai806.jdbcsample;

import java.sql.*;

public class QuerySample {
    
    public static void main(String[] args) throws Exception {
        
        /* ======== Подключение к MS SQL Server ===== */
        // Загрузка драйвера
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        // Соединение с базой данных
        Connection connection = DriverManager.getConnection(
                "jdbc:sqlserver://localhost:1433;databaseName=o01;", 
                // localhost - сервер СУБД, o01 - имя базы данных
                "sa", "123"); // пользователь, пароль
        
        /* ======== Подключение к Oracle ============
        // Загрузка драйвера
        Class.forName("oracle.jdbc.OracleDriver");
        // Соединение с базой данных
        Connection connection = DriverManager.getConnection(
                        "jdbc:oracle:thin:@localhost:1521:orcl", 
                        // localhost - сервер СУБД, orcl - SID базы оракла
                        "o01", "o01"); // пользователь, пароль         
        
        // Создание Statement
        PreparedStatement stmt = connection.prepareStatement
        ("select ID, NAME from PERSON where NAME like ?");
        stmt.setString(1, "%S%");
        // Выполнение запроса
        ResultSet rs = stmt.executeQuery();
        // Перебор результата выполнения запроса
        while(rs.next()) {
            // Пример выбора параметра по номеру или по имени
            System.out.println("ID: " +
                    rs.getLong(1) + "; NAME="+
                    rs.getString("NAME"));
        }
        
        // закрытие использованных ресурсов БД
        rs.close();
        stmt.close();
        connection.close();
    }
}

package org.mai806.jdbcsample;

import java.sql.*;
import java.util.ResourceBundle;

public class StoredProcedureSample {
    
    private static Connection connection = null;
    public static void main(String[] args) throws Exception {
        // Получение соединения из значений параметров в файле properties
        ResourceBundle properties = ResourceBundle.getBundle("database");
        Class.forName(properties.getString("driver"));
        connection = DriverManager.getConnection(
                properties.getString("url"),
                properties.getString("user"),
                properties.getString("password"));
        
        transferAmount(1,2,100.0);
        connection.close();
    }
    
    /**
     * Переводит указанную сумму с одного счета на другой
     * @param from счет плательщика
     * @param to счет получателя
     * @param amount сумма
     */
    public static void transferAmount(long from, long to, double amount)
    throws Exception {
        // Создание Statement
        CallableStatement stmt
                = connection.prepareCall("{call TransferAmount(?,?,?)}");
        // Установка параметров
        stmt.setLong(1, from);
        stmt.setLong(2, to);
        stmt.setDouble(3, amount);
        // Выполнение процедуры
        stmt.execute(); 
    }
}

package org.mai806.jdbcsample;

import java.sql.*;
import java.util.ResourceBundle;

public class TransactionalSample {
    
    private static Connection connection = null;
    public static void main(String[] args) throws Exception {
        
        // Получение соединения из значений параметров в файле properties
        ResourceBundle properties = ResourceBundle.getBundle("database");
        Class.forName(properties.getString("driver"));
        connection = DriverManager.getConnection(
                properties.getString("url"),
                properties.getString("user"),
                properties.getString("password"));
        
        // Ручное управление транзакциями
        connection.setAutoCommit(false);
        
        try {
            transferAmount(2, 1, 10.0);
        } finally {
            connection.close();
        }
    }
    
    /**
     * Переводит указанную сумму с одного счета на другой
     * @param from счет плательщика
     * @param to счет получателя
     * @param amount сумма
     */
    public static void transferAmount(long from, long to,
            double amount) throws Exception {
        PreparedStatement stmt = null;
        Statement query = null;
        try {
            stmt = connection.prepareStatement
            ("update ACCOUNT set AMOUNT=AMOUNT+? where ID=?");
            // Забираем сумму со счета плательщика
            stmt.setDouble(1, -amount);
            stmt.setLong(2, from);
            stmt.execute();
            
            // Добавляем сумму на счет получателя
            stmt.setDouble(1, amount);
            stmt.setLong(2, to);
            stmt.execute();
            
            // Пост-проверка: отрицательность счета плательщика
            query = connection.createStatement();
            ResultSet rs = query.executeQuery(
                    "select AMOUNT from ACCOUNT where ID="+from+" and AMOUNT<0");
            if (rs.next()) {
                throw new Exception("На счете №"+from+" 
                недосточно средств ["+(amount+rs.getDouble(1))+"] 
                для снятия суммы ["+amount+"]");
            }
            connection.commit();
            System.out.println("Перечисление средств успешно выполнено");
        } catch(Exception e) {
            e.printStackTrace();
            connection.rollback();
        } finally {
            if (stmt!=null)
                stmt.close();
            if (query!=null)
                query.close();
        }
    }
    
}