PL/SQL Programming - Syracuse University
PL/SQL Programming Program UnitsAs you recall from IST359, SQL by definition, is a declarative language that doesn’t have the “how to” capability that procedural languages have. Even though SQL provides ease of data retrieval it doesn’t give database developers the capability to apply business logic that can manipulate the data. So, DBMS vendors have implemented extensions to SQL that provide database developers with data input, manipulation and output capabilities. These extensions to SQL are known as procedural language constructs. In SQL Server you managed program units using Microsoft’s Transact-SQL or T-SQL. The Oracle version of SQL is called PL/SQL (Procedural Language/Structured Query Language). It provides procedural language control structures that control the program flow. These procedural constructs allow the database professional to build program units called procedures, functions and triggers. What distinguishes database program units from other programs is that program units may be stored in the database as data objects along with your data.In this lab we will focus on general PL/SQL programing (unnamed procedures) and at the end introduce you to the stored procedure. The stored that will allow you to build logical units of work that will manipulate the data that you have stored in your database. Examples and explanations of the following are provided:basic code blocks (unnamed procedures),variable declaration and anchor data types,substitution variables,date and other supplied functions,sequences, synonyms, andabstract data typesLearning OutcomesAfter this topic you will be able to design, construct and maintain a database and various database objects using procedural language constructs to solve problems including the ability to:Describe why procedures are used in Relational Database Management Systems (RDBMSs) Implement unnamed and named (stored) procedures using Oracle 11g SQL*PlusUse variables in a database procedure Declare variables using anchor data typesImplement sequences and synonymsCreate and use an abstract data typePart one – Procedures and Code Blocks1. OverviewWhat is a procedure and why are procedures important? How do you create them and can you use SQL in procedures? Why create them at all? What real value do they have? I could go on and on with questions regarding procedures. But, instead let’s take a closer look at what they really are and why they are useful. First, database procedures are small units of code that can be executed to perform tasks. They typically are written once and used many times. Procedures are not full blown applications, but they help automate processes that can be part of an application. Many relational database management systems (RDBMSs) allow procedures to be stored in the database itself along with other database objects such as tables and views. Procedures that are external to the database but interact with database objects are also possible in many systems.In Oracle 11g you can write two types of procedures:Unnamed procedures are code blocks without a representation in the database catalog. These code blocks are usually saved in a .sql file. When you would like to execute the procedure, you open the .sql file and run the procedure.Stored procedures are code blocks represented in the database catalog. These code blocks are given a name and when you would like execute the procedure you use the call statement to run the named block of code. We will focus on unnamed procedures initially. We will examine stored procedure in the next lab.Procedures are typically a combination of SQL and some other procedural programming language that is supported by the RDBMS. Procedures typically require vendor extensions to SQL since SQL only allows the user to tell the database what they want from the database not how to get it. In addition, there are no SQL commands for decision making and repetition. Therefore, most major RDBMS vendors have created programming language extension that can be used to implement this type of functionality in procedures. Oracle has developed a procedural language call PL/SQL. MS SQL Server has implemented a language called Transact SQL or T-SQL. Finally, procedures are important because they can be used to ensure that the database remains in a consistent state. They can be used to implement constraints that cannot be easily implemented other ways. Additionally procedures:Ensure data integrity by performing calculations and updating values based on triggering events,Facilitate data retrieval, and Facilitate repetitive tasks. There are a variety of ways that procedures can be used to ensure data integrity and facilitate the storage, manipulation, and retrieval of data. So, let’s take a closer look at procedures how to implement them in Oracle 11g.There are different procedure types that can be implemented in Oracle. The types of procedures supported in Oracle 11g are:Unnamed procedures,Stored procedures,Functions,Table level triggers, andSystem event triggers.Now let’s take a closer look at procedures by examining an unnamed (anonymous) stored procedure. We begin by exploring the basic procedure block.2. Basic Procedure Code Block StructureProcedures are blocks of executable code that have a two-tier structure consisting of declaration and execution sections (DECLARE and BEGIN sections terminated by an END statement). These blocks of code can be named or unnamed. Unnamed blocks are also referred as anonymous blocks. For this lab you will be building an unnamed or anonymous procedure block. The basic structure is the same whether the blocks are named or anonymous. We will be writing named block procedures next lab. Examine the block’s structure below:308610034924Put your variables here. Think of variables as memory work areas that temporarily hold data.00Put your variables here. Think of variables as memory work areas that temporarily hold data.DECLARE3429008191500BEGIN182880015240Put your executable PL/SQL statements here. Each statement ends with a semi-colon.00Put your executable PL/SQL statements here. Each statement ends with a semi-colon.571500129540004686300121920Notice that there is a semi-colon at the end of the block00Notice that there is a semi-colon at the end of the block4000508890000END;3. Hello, World!You write procedures using many of the same constructs that you use in SQL. There are additional commands that you will use that control the output to the screen, store temporary data, interact with the user, and perform calculations.One of the first things you learn to do in any programming language is say hello. Let’s Write a simple “HELLO WORLD” procedure that simply displays the message “Hello, World”. To do this, we need to know how to write to the display screen. Oracle provides a named stored procedure that can be used to display a message to the output device. This stored procedure is named PUT_LINE and it is located in the DBMS_OUTPUT package. We will cover packages later in the semester. For now, packages are a way of organizing database objects.Do This: Connect to SQL Developer as IST469 and open a new sql file. Type the following code into the query window as save it as hello-world.sql :54102002228215All PL/SQL statements end with a semicolon00All PL/SQL statements end with a semicolon476250123825000313372593345000666750106680000723900581025001685926295275003838575123825Send server output to the display screenStart the procedure code blockOutput ‘Hello, World!’End the procedure code blockExecute Immediately00Send server output to the display screenStart the procedure code blockOutput ‘Hello, World!’End the procedure code blockExecute ImmediatelyLet’s take a close look at the PUT_LINE procedure to better understand how to use it.504825021018500First, the format of the procedure call is:40957503111500024479252063750016859251587500072390025400100DBMS_OUTPUT.PUT_LINE(text to be displayed);377190039370Argument list00Argument list240030038100Procedure name00Procedure name137160038100Dot operator00Dot operator34290038100Package name00Package nameThis PL/SQL statement will call the PUT_LINE procedure in the DBMS_OUTPUT package (which is located in the SYS schema) and pass the parameter inside the parenthesis. Inside the parenthesis is what you want displayed. This is an input parameter passed to the procedure. It can be a variable that contains a value that you would like displayed or a literal string of character to be displayed. A literal is a character string that must have single quotes (‘) at the beginning and end of the string. You may display multiple variables and/or literals by concatenating them together. Two vertical bar symbols (||) are used as the concatenate symbol in PL/SQL (similar to the + symbol in other computer languages, like Javascript and T-SQL). To display a strong political message in your script output the procedure call would look like this:DBMS_OUTPUT.PUT_LINE(‘Vote for Pedro.’);4. Variables and Run-Time Prompts (Substitution Variables)Sometimes our procedures require data storage to execute properly. We can set aside temporary storage in the form of variables. Suppose the number of votes Pedro has is stored in the variable votes you can output its value as part of a message with the following procedure call: DBMS_OUTPUT.PUT_LINE(‘Pedro has ‘ || votes || ‘ votes.’); Let’s put this to work and write a procedure that uses a variable to show how many votes Pedro got.Do This: Connect to SQL Developer as IST469 and open a new sql file. Type the following code into the query window as save it as pedro-votes.sql : 289560012522200034861501252220001466850947420001466850537845004410075385445Variable declarationAssign 99 votes to the variableString concatenation00Variable declarationAssign 99 votes to the variableString concatenationOur Pedro program is deterministic; meaning it will execute the same each time. One of the reasons it is deterministic is because in line number 5 we have hard-coded Pedro’s votes to the value 99. One way to make this program non-deterministic is to ask the user to enter a number of votes for Pedro. We can make Oracle 11g prompt the user for input by placing an ampersand (&) in front of our parameter. This is known as a substitution variable, because the value accepted at run time is replaced inside the script prior to the script being executed.Do This: Connect to SQL Developer as IST469 and open a new sql file. Type the following code into the query window as save it as pedro-votes-with-prompt.sql : 3048000821691004495800669290Run-time Prompt00Run-time PromptWhen you execute the script, you will see the following prompt. Enter a number, for example 23495250035242500When the script completes you will see the following in the output window. Notice how the prompt is replaced in the script before it is actually executed.36576007620005. Variables and Anchor Data TypesAt this point you might be asking yourself where’s the SQL? Where’s the INSERT, UPDATE, DELETE or SELECT statements? If you’re thinking that, then you’re right! The purpose for procedures is to write logic that solves business problem in our database, so we can’t be doing anything important unless we’re touching our tables!Our next program does just that. It prompts you for a customer id and returns a customer name.Do This: Connect to SQL Developer as IST469 and open a new sql file. Type the following code into the query window as save it as fudgemart-lookup-name-from-id.sql : 191452514116050039338251154430This assigns the output from the select into variables.00This assigns the output from the select into variables.When you execute the script, it will ask for a customer id. If you enter 5 for example, you should see the following in the output window:This is a really neat step forward, but there’s one key problem: in lines 3-5 we specify the data types for the variables cust, fname, and lname. These data types must match the customer_id, customer_firstname, and customer_lastname data types respectively. Our scripts works now because these data types match, but if we alter the FUDGEMART.CUSTOMERS table and change the data type that will no longer be the case, and we will have a problem. To future proof this script against such changes, we can use an anchor data type. Simply put, an anchor data type creates a variable representing the data type of a table column. Here’s the same script with anchor types:Do This: Connect to SQL Developer as IST469 and open a new sql file. Type the following code into the query window as save it as fudgemart-lookup-name-from-id-with-anchor-types.sql : 3810000699770003971925433070The data types of these 3 variables match their respective column data types.00The data types of these 3 variables match their respective column data types.Part two – other PL/SQL StructuresIn this part we will explore other PL/SQL structures besides procedural code blocks.6. Sequences Sequences are useful database object that allows you to create a series of sequence numbers (integers only) automatically. These numbers are oftentimes used to create values for primary keys when you don’t have an alternate for a user entered primary key value. This capability is similar to the Autonumber in Microsoft Access and the Identity property in Microsoft SQL Server. There are also some significant differences between Oracle Sequences and the other vendor’s implementation for creating sequence numbers. The major distinction is that sequences are not tied exclusively to a table. In fact just the opposite, a sequence is not linked or attached to a table at all. Incidentally, IBM’s DB2 has both the Identity property and the sequence object. The syntax for creating a sequence with its optional clauses looks like this:CREATE SEQUENCE sequence_name Start with <number>Increment by <number>Maxvalue <number> | NomaxvalueMinvalue <number> | NominvalueCycle |NoCycleCache<number> |NoCacheOrder | NoOrder;All of the clauses are optional and if excluded will follow the Oracle 11g default settings identified below: Start with - identifies the starting value of the sequence. The default is 1. Increment by - identifies the value that you want each sequence number to be adjusted by. The default is 1.Maxvalue - the terminal value you wish to use to stop your sequence from producing additional sequence numbers. The default is Nomaxvalue which is the largest value allowed, 10 to the 27th power-1, essentially a very big number.Minvalue - also a terminal value if you want to assign sequence numbers in descending sequence. The default is Nominvalue.Cycle - allows sequence numbers to be used if the sequence reaches its maximum value. With Cycle, if you attempt to generate a sequence number and use it as a primary key after you reach your maximum value, Oracle will return a message like this: ORA-00001: unique constraint (DDISCHIAVE.SYS_C003045) violated. The default is Nocycle. With Nocycle, if you attempt to generate a sequence number after you reach your maximum value you get the following message: ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiatedCache - specifies to Oracle to generate a number of sequence numbers ahead, stored in the SGA buffer cache area and are to be used at a later time. This is primarily used to improve performance. The default value is 20. If you specify NoCache then no sequence numbers will be generated in advance and stored in cache. Order - specifies that sequence numbers are to be allocated in the exact chronological order in which they were requested. NoOrder is the default; but ensures that applications or users get a unique sequence number but necessarily in chronological order.3524250340359Here is an example of a Sequence for creating unique numbers for an Order table’s primary key. Notice that I cached 100 sequence numbers for performance reasons. 00Here is an example of a Sequence for creating unique numbers for an Order table’s primary key. Notice that I cached 100 sequence numbers for performance reasons. Here’s an example sequence I created in SQL Developer (you’ll get to try this later on)182880043624500 Notice I can see the sequences in OEM, as well Schema SequencesHow do you use Sequences?A sequence is a great way to generate numbers automatically for use as primary key values. But sequences can also be used in SQL expressions when you need to use an integer as part of your calculation. To use a sequence you need to use one of two pseudo columns in the sequence : currval and nextval.Currval - returns the current value of the sequenceNextval - the first time used Nextval returns the initial value then in all subsequent uses return the sum of the current value of the sequence plus the value as indicated by the Increment By option.4286250112395TESTSEQ.NEXTVAL is the pseudo column for generating the next number in the sequence.00TESTSEQ.NEXTVAL is the pseudo column for generating the next number in the sequence.Do This: Connect to SQL Developer as IST469 and open a new sql file. Type the following code into the query window as save it as test-sequence.sql : 26003262661920002676526158559500236220069977100396240034734500460057533019Make a table for the sequence.Make the sequenceUse the sequence in 5 insertsSee the sequence numbers users00Make a table for the sequence.Make the sequenceUse the sequence in 5 insertsSee the sequence numbers usersWhen you execute the code you should see the following script output:7. SynonymsSynonyms are another useful database objects that allow you to create alternative names for existing database objects. The capability to create alternative names is useful in two common scenarios: when using software packages where the vendor’s names are not meaningful to your organization, you may want to create a synonym for names that your organization recognizes and uses. In scenario two you may need to create a shorter name for a longer one originally established making your queries easier and less tedious to write. The syntax for the synonym looks like this:CREATE [PUBLIC] SYNONYM synonym_name FOR object_name;The PUBLIC is the only optional field and if excluded will follow the Oracle 11g default settings identified below: PUBLIC - an optional field that makes the synonym available to all user schemas. Without using the PUBLIC option, the synonym is only available to authorized users of the schema that created the synonym or those users given explicit permission to use the synonym object. 4486275128270Here is an example where you would a public synonym so that you can use a shorter name. Public synonyms are located in the Public schema.00Here is an example where you would a public synonym so that you can use a shorter name. Public synonyms are located in the Public schema.331470020955000571500132715SQL> CREATE PUBLIC SYNONYM emps FOR FUDGEMART.EMPLOYEES;Synonym created.SQL>00SQL> CREATE PUBLIC SYNONYM emps FOR FUDGEMART.EMPLOYEES;Synonym created.SQL>Public synonyms may also be used so that other users can access objects in another schema without having to fully qualify the table name. For example, if the EmployeeMaster table is in the HumanResource schema the Payroll schema would have to precede the table name with the schema name to access the attribute; however, using the public synonym eliminates the need to precede the table name with the schema name.That is SELECT * FROM FUDGEMART.EMPLOYEES;becomes SELECT * FROM EMPS;PRIVATE - synonyms are available only to the schema owner. This is the default if PUBLIC is not specified. 457200114935SQL> CREATE SYNONYM cust FOR FUDGEMART.CUSTOMERS;Synonym created.00SQL> CREATE SYNONYM cust FOR FUDGEMART.CUSTOMERS;Synonym created.To drop a synonym you need to be mindful of the schema the synonym was created in. In order to drop a public synonym you need to use the following syntax:DROP [PUBLIC] SYNONYM synonym_name;8. Abstract Data TypesWhen declaring a variable or identifying attributes in a table, we must identify the type of data that will be stored in the attribute or variable. This is done by indicating the data type. Oracle (or other RDBMS vendors) has supplied a variety of scalar (or primitive) data types for us. There are often attributes that we use in specific combinations over and over. To accommodate and also add to reuse capabilities, Oracle has provided a mechanism to create user defined or Abstract Data Types (ADT).A college may have various tables that all require attributes that define a person. All persons have attributes in common such as last name, first name, data of birth, and gender. A college would probably have employees, students, alumni, and applicants. Each of these entities would require a table and each table would also require similar attributes. Now, it is certainly possible to define each attribute for each table. However, it would be easier to define a datatype called Person and then use Person as the datatype for each attribute that defined a person. This is accomplished by creating an Abstract Data Type (ADT). The advantage here is that you define the Person attributes once and then use it over and over. If modifications are required (perhaps you want to change the number of characters that are define the last name), they are made in one place, in the ADT, and applied to all.2971800-159385My datatype Name00My datatype Name1828800806450020574008064500The format of an ADT is:CREATE TYPE MyDataType AS OBJECT( 274320065405Attributes that make up my datatype 00Attributes that make up my datatype variable1 dataType,2171700444500variable2 dataType);So, the Person datatype described above might be:CREATE TYPE PersonType AS OBJECT( lastName VARCHAR2(30), firstName VARCHAR2(30), dob DATE, gender CHAR(1));Ok, now that I’ve created a Person datatype, what do I do with it? Well, instead of redefining all 4 attributes when I need the attributes associated with a Person, like employee, I can simply use my new datatype. If we create a Student table, it might look like this:CREATE TABLE Student(studentIdNUMBER,studentPersonPersonType,CONSTRAINT studentPK PRIMARY KEY (studentId));2743200181610Datatype 00Datatype Now, to populate this table with values using the insert, it would look like this:20574007175500INSERT INTO STUDENT VALUES (100,PERSONTYPE('WASHINGTON','GEORGE','22-Feb-1732','M'));-85725281305STUDENTIDSTUDENTPERSON(LASTNAME, FIRSTNAME, DOB, GENDER)--------- --------------------------------------------------------------------- 100PERSONTYPE('WASHINGTON', 'GEORGE', '22-FEB-32', 'M')00STUDENTIDSTUDENTPERSON(LASTNAME, FIRSTNAME, DOB, GENDER)--------- --------------------------------------------------------------------- 100PERSONTYPE('WASHINGTON', 'GEORGE', '22-FEB-32', 'M')If we selected all of the attributes (SELECT * FROM STUDENT;), it would look like this:4705350154940Note: Using a table alias is required.00Note: Using a table alias is required.If we wanted to only select the first name, the select would be:39147758890000SELECT s.studentdemo.firstname from student s;And the result would look like this:012065STUDENTDEMO.FIRSTNAME------------------------------GEORGE00STUDENTDEMO.FIRSTNAME------------------------------GEORGEYou can find all ADTs in the schema under Types. Here’s a screenshot from SQL Developer.245745012700PersonType added in the Types folder00PersonType added in the Types folder143827525082500And also in OEM: under schema Object typesSo, now as you can see, you have created a new data type using the scalar data types supplied by the Oracle DBMS. You can now use this new data type when defining attributes. Do This: Connect to SQL Developer as IST469 and open a new sql file. Type the following code into the query window as save it as adt-demo.sql : Lab Assignment – On Your OwnLogin to SQL developer as FUDGEMART For each question provide a screenshot of the command or SQL, along with evidence the command worked, such as an OEM screenshot, etc.ADDRESSVARCHAR2(255)CITYVARCHAR2(20)STATECHAR(2)ZIPVARCHAR2(20)Create an Abstract Data Type (ADT) for ADDRESSES called ADDRESS_ADT that will be used for all Fudgemart addresses. The following characteristics have been identified for the ADT:Column NameTypeOther InstructionsADDRESSADDRESS_ADTUse your ADT from step 1Alter the CUSTOMERS table and add a column for your new ADT: Write a PL/SQL Unnamed procedure for FUDGEMART to migrate addresses fromCUSTOMERS into your new ADT Hint: Remember updates are not permanent until you COMMIT; in oracle. So if you do this step wrong you can always ROLLBACK;Alter the FUDGEMART.CUSTOMERS table. Remove the columns which are not redundant as part of the ADT.Write an anonymous PL/SQL procedure for FUDGEMART which prompts for the PRODUCT_ID and returns the ID and name of the VENDOR supplying the product.Write an anonymous PL/SQL procedure for FUDGEMART which prompts for the customer ID and displays the customer’s full name, city, and state.Write an anonymous PL/SQL procedure for FUDGEMART which prompts for an ORDER_ID and displays the total amount of the order (qty * retail price ) for all products.Answer the following questions What is PL/SQL? How is PL/SQL related to SQL.What is an anchor data type? Is it a good idea to use anchor data types? Why or why not?Why does PL/SQL require a declaration section?What is contained in the execution section?Explain the difference between creating and using a sequence number as opposed to the identity clause in MS SQL Server or auto number in MS Access?Explain why a synonym would be created and used? ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.