PL/SQL (En)

  • A Brief History of SQL

    SQL_historyDr. Edgar F. Codd (1923–2003), an IBM research scientist, first conceived the relational database model in 1969. He was looking into new ways to handle large amounts of data in the late 1960s and began thinking of how to apply mathematical principles to solve the myriad problems he had been encountering.

    After Dr. Codd presented the relational database model to the world in 1970, organizations such as universities and research laboratories began efforts to develop a language that could be used as the foundation of a database system that supported the relational model. Initial work led to the development of several different languages in the early to mid-1970s. One such effort occurred at IBM’s Santa Teresa Research Laboratory in San Jose, California.

  • Define PL/SQL Data Types: varchar2, number, date, Boolean, VARRAY

    PL/SQL Data TypesThe use of local variables within a PL/SQL program is important for everyone using the language to understand. It is a basic component of each program, and as such, it is invaluable for understanding what is available and how best to use it. You can now look at how you use and define variables and working storage within your PL/SQL programs.

    As with all programming languages, there are characters that you use to write your programs. Each language has its own rules and restrictions when it comes to the valid characters. In the following sections of article, we will show you:

  • Describe the Basic PL/SQL Program Structure

    "PL/SQLThe structure you use in PL/SQL is the foundation for the language as a whole. Once you’ve mastered it, you will then be able to move forward; however, if you do not take the time to get this first step right, your journey will be difficult. Thankfully, it’s quite simple.

    The structure is quite basic. You will have areas for your program parameters (these are used to pass values from outside a program to the program itself), your internal variables, the main program code and logic, and various ways to deal with problem situations. Let’s look at the basic form of a PL/SQL block:

     

  • Oracle: Granting the Ability to Create & Execute PL/SQL Stored Programs

    Granting the Ability to Create & Execute PL/SQL codeProblem

    You want to grant someone the ability to create and execute stored PL/SQL programs.

    Solution

    To grant the ability for a user to create a procedure, function, or package, you must log in to the Oracle database with a privileged account and grant the CREATE PROCEDURE privilege to the user. Here's an example:

  • PL/SQL package: Collecting Related Routines into a Single Unit

    Collecting Related Routines into a Single PL/SQL packageProblem

    You have a number of procedures and functions that formulate an entire application when used together. Rather than defining each subprogram individually, you prefer to combine all of them into a single, logically related entity.

    Solution

    Create a PL/SQL package that in turn declares and defines each of the procedures together as an organized entity. You declare each of the subprograms in the package specification (otherwise known as a header) and define them in the package body.

  • PL/SQL procedure: Setting Default Parameter Values

    Setting Default Parameter Values in PL/SQL procedureProblem

    You want to create a PL/SQL procedure that accepts several parameters. However, some of those parameters should be made optional and contain default values.

    Solution

    You can allow the procedure caller to omit the parameters if default values are declared for the variables within the procedure. The following example shows a procedure declaration that contains default values:

  • PL/SQL: Passing Parameters by Name

    Passing Parameters by Name in PL/SQL procedureProblem

    You have a PL/SQL procedure in your Oracle database that accepts a large number of parameters. When calling the procedure, you would rather not worry that the positioning of the parameters is correct.

    Solution

    Rather than trying to pass all the parameters to the procedure in the correct order, you can pass them by name. The code in this solution calls a procedure that accepts six parameters, and it passes the parameters by name rather than in order.

  • PL/SQL: Writing Initialization Code for a Package

    Writing Initialization Code for a Package for PL/SQL packageProblem

    You want to execute some code each time a particular PL/SQL package is instantiated in a session.

     

    Solution

    Create an initialization block for the package in question. By doing so, you will have the ability to execute code each time the package is initialized. The following example shows the same package that was constructed in this article. However, this time the package contains an initialization block.

     

  • Why Oracle DBAs learn PL/SQL and how to apply it

    Oracle DBAs learning PL/SQL why?Oracle Database 12cis more than just a database management system - it’s also an engine for many programming languages. Not only does it serve as a Java engine with the built-in Java Virtual Machine (JVM), it’s a PL/SQL engine as well. This means that the code you write can be stored in a database and then run as required when it is needed.

    The PL/SQL engine is bundled together with the database and is an integral part of the Oracle server, providing you with a powerful language to empower your logic and data. Let’s look at how PL/SQL fits into the Oracle server. Figure 1 shows you how PL/SQL works from both within and outside the Oracle database.