In this case a table alias is required DECLARE mystreet VARCHAR225 BEGIN SELECT

In this case a table alias is required declare

This preview shows page 313 - 316 out of 492 pages.

. In this case, a table alias is required. DECLARE my_street VARCHAR2(25), BEGIN SELECT p.home_address.street INTO my_street FROM persons p WHERE p.last_name = 'Lucas'; Inserting Objects You use the INSERT statement to add objects to an object table. In the following example, you insert a Person object into object table persons : BEGIN INSERT INTO persons VALUES ('Jenifer', 'Lapidus', ...); Alternatively, you can use the constructor for object type Person to insert an object into object table persons : BEGIN INSERT INTO persons VALUES (Person('Albert', 'Brooker', ...)); In the next example, you use the RETURNING clause to store Person refs in local variables. Notice how the clause mimics a SELECT statement.You can also use the RETURNING clause in UPDATE and DELETE statements. DECLARE p1_ref REF Person;
Image of page 313
Manipulating Objects through SQL 12-22 PL/SQL User's Guide and Reference p2_ref REF Person; BEGIN INSERT INTO persons p VALUES (Person('Paul', 'Chang', ...)) RETURNING REF(p) INTO p1_ref; INSERT INTO persons p VALUES (Person('Ana', 'Thorne', ...)) RETURNING REF(p) INTO p2_ref; To insert objects into an object table, you can use a subquery that returns objects of the same type. An example follows: BEGIN INSERT INTO persons2 SELECT VALUE(p) FROM persons p WHERE p.last_name LIKE '%Jones'; The rows copied to object table persons2 are given new object identifiers. No object identifiers are copied from object table persons . The script below creates a relational table named department , which has a column of type Person , then inserts a row into the table. Notice how constructor Person() provides a value for column manager . CREATE TABLE department ( dept_name VARCHAR2(20), manager Person, location VARCHAR2(20)) / INSERT INTO department VALUES ('Payroll', Person('Alan', 'Tsai', ...), 'Los Angeles') / The new Person object stored in column manager cannot be referenced because it is stored in a column (not a row) and therefore has no object identifier. Updating Objects To modify the attributes of objects in an object table, you use the UPDATE statement, as the following example shows: BEGIN UPDATE persons p SET p.home_address = '341 Oakdene Ave' WHERE p.last_name = 'Brody'; UPDATE persons p SET p = Person('Beth', 'Steinberg', ...) WHERE p.last_name = 'Steinway'; END; Deleting Objects You use the DELETE statement to remove objects (rows) from an object table. To remove objects selectively, use the WHERE clause: BEGIN DELETE FROM persons p WHERE p.home_address = '108 Palm Dr'; END;
Image of page 314
PL/SQL Language Elements 13-1 13 PL/SQL Language Elements Grammar, which knows how to control even kings. Molière This chapter is a quick reference guide to PL/SQL syntax and semantics. It shows you how commands, parameters, and other language elements are combined to form PL/SQL statements. It also provides usage notes and short examples. This chapter contains these topics: Assignment Statement AUTONOMOUS_TRANSACTION Pragma Blocks CASE Statement CLOSE Statement Collection Methods Collections Comments COMMIT Statement Constants and Variables Cursor Attributes Cursor Variables Cursors DELETE Statement
Image of page 315
Image of page 316

You've reached the end of your free preview.

Want to read all 492 pages?

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern

Stuck? We have tutors online 24/7 who can help you get unstuck.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes