PL/SQL

  • CHR и ASCII - обрабатываем спец. символы на PL/SQL с примерами

    функции CHR и ASCII PL/SQLВстроенная функция PL/SQL  CHR особенно удобна в тех случаях, когда в программный код необходимо включить ссылку на непечатаемый символ (спецсимвол). Допустим, вы строите отчет, в котором выводятся адреса компаний. Помимо строк с названиями города, страны и индекса адрес может содержать до четырех дополнительных строк, и значение каждой из них должно выводиться с новой строки. Все строки адреса можно объединить в одно длинное текстовое значение и использовать функцию CHR для вставки разрывов строк в нужных местах. В стандартной кодировке ASCII символ новой строки имеет код 10, поэтому программа может выглядеть так: 

  • Creating a PL/SQL Stored Function by example

    PL/SQL Stored Function by exampleProblem

    One of your PL/SQL programs is using a few lines of code repeatedly for performing a calculation. Rather than using the same lines of code numerous times throughout your application, it makes more sense to encapsulate the functionality into a common routine that can be called and reused time and time again.

  • Creating a PL/SQL Stored Procedure for execute a regular tasks

    PL/SQL Stored Procedure creationProblem

    There is a Oracle database task that you are performing on a regular basis. Rather than executing a script that contains lines of PL/SQL code each time you execute the task, you want to store the code in the database so that you can simply execute the task by name or so that you can schedule it to execute routinely via Oracle Scheduler.

     

    Solution

    Place the code that is used to perform your task within a stored procedure. The following example creates a procedure named INCREASE_WAGE to update the employee table by giving a designated employee a pay increase. Of course, you will need to execute this procedure for each eligible employee in your department. Storing the code in a procedure makes the task easier to perform.

  • Creating PL/SQL Functions Within a Procedure or Code Block

    PL/SQL Functions Within a ProcedureProblem

    You want to create some functions within a stored procedure. You want the functions to be local to the procedure, available only from the procedure's code block.

     

    Solution

    Create a stored procedure, and then create functions within the declaration section. The internal functions will accept parameters and return values just as an ordinary stored function would, except that the scope of the functions will be constrained to the outer code block or to the procedure. The procedure that is demonstrated in this solution embodies two functions. One of the functions is used to calculate the federal tax for an employee paycheck, while the other calculates the state tax.

  • DETERMINISTIC: детерминированные функции в PL/SQL на примере

    DETERMINISTIC в PL/SQLФункция называется детерминированной, если при одном наборе параметров IN и IN OUT она всегда возвращает одно и то же значение. Отличительной чертой детерминированных функций является отсутствие побочных эффектов: все изменения, вносимые программой, отражаются в списке параметров.

    Пример детерминированной функции, которая представляет собой простую инкапсу­ляцию substr:

     

     

     

  • Executing a PL/SQL Stored Function from a Query

    PL/SQL Stored Function ExecutingProblem

    You want to invoke a function from an SQL query in PL/SQL. For example, you want to take the quarter-hour rounding function from this blog and invoke it on hourly values in a Oracle database table.

     

    Solution

    Write a query and invoke the function on values returned by the SELECT statement. In the following lines, the function that was written in the previous recipe will be called. The results of calling the function from within a query are as follows:

  • Executing a PL/SQL Stored Procedure from SQL*Plus

    PL/SQL Stored Procedure Executing from SQL*PlusProblem

    You want to execute a stored procedure from SQL*Plus.

     

    Solution

    Open SQL*Plus, and connect to the database schema that contains the procedure you are interested in executing. Execute the procedure by issuing the following command:

  • FORALL и BULK COLLECT - ускоряем запросы в PL/SQL

    Ускорение запросов в PL/SQL через FORALL и BULK COLLECTВ Oracle были введены новые средства, значительно повышающие производительность запросов в PL/SQL, — команда FORALL и секция BULK COLLECT. Они объединяются общим термином конструкций массовой обработки (bulk processing). Для чего нужны эти конструкции, спросите вы? Как известно, язык PL/SQL тесно интегрирован с SQL-ядром базы данных Oracle. Он является основным языком программирования для Oracle, несмотря на то что теперь в базе данных также может использоваться язык Java. Однако эта интеграция не означает, что выполнение кода SQL из программы PL/SQL не сопряжено с затратами.

  • Getting Started with Oracle and PL/SQL programming

    How to start coding on PL/SQL for Oracle Databases for novices?In this lesson, you’ll learn what Oracle and PL/SQL are, and what tools you can use to work with them.

    What Is Oracle?

    In the previous lesson, you learned about databases and SQL. As explained, it is the database software (DBMS or Database Management System) that actually does all the work of storing, retrieving, managing, and manipulating data. Oracle DBMS (or just “Oracle”) is a DBMS; that is, it is database software.

  • Introduction to PL/SQL

    PLSQL basics LearingStructured Query Language (SQL) is a language that has been widely accepted and adopted for accessing relational databases. This language allows users to perform database operations such as reading, creating, modifying, and deleting the data. Since the summer of 1970, when Dr. E.F. Codd published the paper A Relational Model of Data for Large Shared Data Banks for the ACM journal, the language has matured comprehensively as an industry standard. With its broad range of features and easy adaptation to enterprise environments, the SQL language has been typically regarded as the most reliable language for interacting with relational databases.

  • jQuery Events for APEX & PL/SQL developers

    DOM selection and manipulation in jQuery is essential for identifying web page components. Event handling facilitates interaction with these identified components by the end users by responding to their input devices.

    Touch screens have turned the user’s fingers into input devices and events such as those responding to gestures are handled specifically with jQueryMobile—a framework for creating mobile applications. APEX applications don’t need to include the mobile framework to handle standard tap and scrolling events. A small library called Touch Punch can be included on a web page to make it respond to dragging gestures, useful for slider items.

  • jQuery Fundamentals for APEX PL/SQL programmers

    jQuery for Oracle APEX PL/SQL programmersMastering the concept of selectors is a difficult part of learning jQuery. There are a number of methods to identify the right page element to act upon. In addition to the selectors mentioned in this blog, jQuery also provides the ability to traverse up and down the HTML tree using specific functions.

    More detailed examples of selectors will be introduced as the blog details traversal methods in addition to other fundamental concepts and common features. Just like SQL, a good percentage of what you’ll ever need to do will already have an appropriate documented function.

  • LPAD и RPAD - дополняем строку пробелами в PL/SQL

    Иногда в PL/SQL требуется, чтобы обрабатываемая строка имела фиксированный размер. Функции LPAD и RPAD дополняют строку пробелами (или другие символы) слева или справа до заданной длины. В следующем примере эти две функции выводят список имен в два столбца, причем левый столбец выравнивается по левому краю, а правый — по правому краю:

  • Optimizing a PL/SQL Function for Always Return the Same Result

    PL/SQL Function for Always Return the Same ResultProblem

    You want to create a PL/SQL function that will return the same result whenever a given input, or set of inputs, is presented to it. You want the database to optimize based upon that deterministic nature.

     

    Solution

    Specify the DETERMINISTIC keyword when creating the function to indicate that the function will always return the same result for a given input. For instance, you want to return a specific manager name based upon a given manager ID. Furthermore, you want to optimize for the fact that any given input will always return the same result. The following example demonstrates a function that does so by specifying the DETERMINISTIC keyword:

  • Oracle Regular Expressions Pocket Reference

    Книга Oracle Regular Expressions Pocket Reference

     

    Автор:Jonathan Gennick, Peter Linsley
    ИздательствоO'Reilly Media

    Год:2009
    Страниц:66
    Языканглийский
    ФорматEbook (отличное), 0.5 Мб
    ISBN978-0-596-10438-2

  • Oracle's Regular Expression in SQL and PL/SQL

    Using Regular Expression in Oracle SQL and PL/SQL Oracle's regular expression support manifests itself in the form of three SQL functions and one predicate that you can use to search and manipulate text in any of Oracle's supported text datatypes: VARCHAR2, CHAR, NVARCHAR2, NCHAR, CLOB, and NCLOB.


     Regular expression support does not extend to LONG, because LONG is supported only for backward compatibility with existing code.


  • Oracle8i Advanced PL/SQL Programming


    Книга Oracle8i Advanced PL/SQL ProgrammingАвтор: Scott Urman
    Издательство: Osborne/McGraw-Hill

    Год:April 24, 2000
    Страниц: 772
    Язык: английский
    Формат:PDF (отсканированные страницы)
    ISBN: 0072121467

  • PL/SQL Language Fundamentals

    Fundamentals of PL/SQL for novices?This article summarizes the fundamental components of the PL/SQL language: characters, identifiers, literals, delimiters, use of comments and pragmas, and construction of statements and blocks.

     

  • PL/SQL Variables and Program Data: using different data types

    PL/SQL Variables and Data TypesPL/SQL programs normally are used to manipulate database information. You commonly do this by declaring variables and data structures in your programs, and then working with that PL/SQL-specific data.

    A variable is a named instantiation of a data structure declared in a PL/SQL block (either locally or in a package). Unless you declare a variable as a CONSTANT, its value can be changed at any time in your program.

  • PL/SQL: "Белые списки" и управление доступом к программным модулям

    ACCESSIBLE BY - БЕЛЫЕ СПИСКИ PL/SQLПриложения PL/SQL обычно строятся из пакетов, одни из которых относятся к API верхнего уровня, используемого программистами для реализации пользовательских требований, а другие решают вспомогательные задачи и предназначены для использования только другими пакетами.

    До выхода Oracle Database 12c язык PL/SQL не мог помешать сеансу использовать любые подпрограммы пакетов, для которых схема данного сеанса обладает привилегией EXECUTE.

Страница 1 из 8