Learning PL/SQL: - Page 2 - Computer Technical Help

----------------

in re: Objects.

Objects aren't covered until ch14, but I'll go ahead and post some codes here in a minute.

As I understand it, PL/SQL is NOT SQL per se. While there is an Oracle SQL, PL/SQL is simply a language to manipulate the data through a layer of higher-level programming, call to functions of other languages, and do other things.

So, on to a very temporary jaunt to ch14:

copied verbatim:

Code:

-- This is found in the map_comparison.sql on hte publisher's website.
DECLARE
    -- Declare a collection of an object type.
    TYPE object_list IS    TABLE OF MAP_COMP
    -- initialize 4 objects in mixed abc order
    object1 MAP_COMP := map_comp('Ron Weasley');
    object2 MAP_COMP := map_comp('Harry Potter');
    object3 MAP_COMP := map_comp('Luna Lovegood');
    object4 MAP_COMP := map_comp('Hermione Granger');
    -- Define a collection of the object type
    objects OBJECT_LIST := object_list(object1, object2, object3, object4);
    -- swaps a and b
    PROCEDURE swap (a IN OUT MAP_COMP, b OUT MAP_COMP) IS
        c MAP_COMP;
    BEGIN
        c := b;
        b := a;
        a := c;
    END swap;
BEGIN
    -- A bubble sort
    FOR i IN 1... objects.COUNT LOOP
        FOR J IN 1... objects.COUNT LOOP
            IF objects(i).equals = LEAST(objects(i).equals,objects(j).equals) THEN
                swap(objects(i),objects(j));
            END IF;
        END LOOP;
    END LOOP;
    FOR i IN 1... objects
        -- print reordered objects.COUNT LOOP
            dbms_output.put_line(objects(i).equals);
    END LOOP;
END;

The next section discusses Inheritance and Polymorphism. A very quick grab of one sentence every paragraph
shows you can create super-classes and sub-classes, use some code to inherit, and what-have-you.

Anyways. Just a teaser.

Quote

04-16-2011 , 05:35 AM

#27

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

day 10:

I was rather busy and alas, I am very tired right now, but that doesn't mean I didn't learn anything. I was planning to spend the next two hours destroying code, but eh..... that'll wait 'til tomorrow.

---------------------------------

Not sure why this section exists in this book, but it is there, and it takes up all of one page. The next session is error management, and I wonder if it shouldn't be better placed there. Regardless, if this thread is really saving someone's life, this would be important to know. Basically pay attention to all the dollar signs.

Conditional Compilation Statements

Starting with 10g, release two, can do CCS.

CC is a temp var that is session-level to debug.

Set compile-time var to 1:

ALTER SESSION SET PLSQL_CCFLAGS = debug:1;

for multipe vars:

ALTER SESSION SET PLSQL_CCFLAGS = debug:1,debug2:2,debug3:3...;

I really hate copying from the text, but there is almost no explanation for this stuff, so here it goes:

Code:

CREATE OR REPLACE FUNCTION conditional_type
(magic_number $IF $$ DEBUG = 1 $THEN  SIMPLE_NUMBER $ELSE NUMBER $END)
RETURN NUMBER IS 
BEGIN
  RETURN magic_number;
END;
/

and the output is:

FUNCTION conditional_type compiled
Warning: execution completed with warning

Well, it is there, and I am sure I'll find the correct places to use it. Don't want to think about it right now, but when the time comes, I'll come back to this.

------------------------

The next section discusses LOOPS. All the classic loops are included.

So, start up with the Simple Loop and copy/paste from my notes:

Simple Loops:

There's two ways to do the two types of simple Loops:

For guarded entry:

Code:

Loop
  [counter_statement]; 
  IF NOT enter_condition THEN
     EXIT;
  END IF;
  repeating_statements;
END LOOP;
/

Code:

Loop
  [counter_statement];
  EXIT WHEN NOT enter_condition;
  repeating_statements;
END LOOP;
/

for guarded exit:

Code:

Loop
  [counter_statement]; 
  repeating_statements;
  IF exit_condition THEN
    EXIT;
  END IF;
END LOOP;
/

Code:

Loop
  [counter_statement];
  repeating_statements;
  EXIT WHEN exit_condition;
END LOOP;
/

The book kindly reminded me of the exit parameters, unless I wanted to create an infinite loop.

Speaking of infinite loops. For the one day I was messing around with Python, I ended up creating an infinite loop. One of those things I have to learn the hard way I guess.

Promise tomorrow will be more exciting, but man I am dead tired right now.

Quote

04-17-2011 , 01:02 AM

#28

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

****.

Day11: total disaster.

There's a major problem. The book asks to use sample codes that are based on sample databases. There is a minor problem: no such databases exist (that I can find anyways).

So, in order for me to continue on, I have to have something to query information from. I don't know why, but there isn't any mention of how to create a new database, or rather, how to create a full database through PL/SQL. Nope, just how to extract the information (at least so far).

So I am stuck, and this isn't some minor stickage either. This is huge.

-- solutions I have looked into:
finding sample databases to download from the internet: nothing.

So.....

How do I find a way to view a database. You know, the actual database.

I found some stuff online. I suppose I can use CREATE TABLE and get to work on this, but I'm not even sure if that is the proper syntax to use to create a new database/table, whatever.

ONE HOUR LATER.....

I search on back to page 220, and I find this gem:

BEGIN

Code:

CREATE TABLE individuals
(individualId       INTEGER       NOT NULL
,firstName          VARCHAR2(30)  NOT NULL
,middleName         VARCHAR2(30)  NOT NULL
,lastName           VARCHAR2(30)  NOT NULL
,title              VARCHAR2(10)  NOT NULL
,CONSTRAINT         PRIMARY KEY(individualId));

END;
/

Yes, I kept BEGIN and END out of the code wrapper on purpose. I ran the code as written and then ran the code with BEGIN and END. All I get are error messages.

This is ****ing absurd.

---------------------

I guess tonight: skip the rest of ch4, skip ch5, head on to ch6 and take a few notes and dive into ch7.

See how easy it is to learn from this book? 80% of it is worthless.

Quote

04-17-2011 , 07:12 PM

#29

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

Day 12: How to create tables.

Yep, the book does in fact describe how to create tables (with a functional code), but that is way back on page.... 6 hundred-something?

Code:

CREATE TABLE namenum
(person_id    NUMBER
,first_name   VARCHAR2(15)
,last_name    VARCHAR2(15)
,ph_areaCode  NUMBER
,ph_number    NUMBER);

table NAMENUM created.

Great:

Now to insert some values into all of this.

Code:

Error starting at line 1 in command:
INSERT INTO namenum 
(FIRST_NAME)
VALUES
(John, Jerry, Mary, Tina)
Error at Command Line:1 Column:12
Error report:
SQL Error: ORA-00913: too many values
00913. 00000 -  "too many values"
*Cause:    
*Action:

Fine... too many values, huh?

Code:

INSERT INTO namenum 
(FIRST_NAME)
VALUES
(John);

well....

Code:

Error starting at line 1 in command:
INSERT INTO namenum 
(FIRST_NAME)
VALUES
(John)
Error at Command Line:4 Column:1
Error report:
SQL Error: ORA-00984: column not allowed here
00984. 00000 -  "column not allowed here"

-----------------

Ugh. I swear I am about 2 days away from quitting this. I honestly don't know where to continue right now. Definitely need a different learning resource. I am spending way too many hours trying to do the simplest tasks. It's after 4 now. I guess I spent about 2 hours getting the program up and running and this one (extremely simple) item I learned today.

God.

Quote

04-17-2011 , 10:12 PM

#30

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

w3schools to the rescue!

So, in making up this table, quite a few things learned. I know they are dopey mistakes, but alas, I learn the slow way.

Code:

INSERT INTO namenum 
(FIRST_NAME)
VALUES
(John);

1 rows inserted

Great, at least that is a start. Now to input multiple items at once:

Code:

INSERT INTO namenum (FIRST_NAME)
VALUES
('Jerry')
('Mary')
('Shelly')

Jesus. I just realized that there is no semi-colon there. Maybe that's why this happened:

Code:

Error starting at line 1 in command:
INSERT INTO namenum (FIRST_NAME)
VALUES
('Jerry')
('Mary')
('Shelly')
Error at Command Line:3 Column:9
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:
1 rows inserted.

Error starting at line 4 in command:
('Mary')
Error at Command Line:4 Column:1
Error report:
SQL Error: Invalid SQL type

Error starting at line 5 in command:
('Shelly')
Error at Command Line:5 Column:1
Error report:
SQL Error: Invalid SQL type

Yes, Jerry was inserted (that's gross), but the rest failed.

So, this format worked:

Code:

UPDATE namenum
SET person_id = 2
WHERE first_name = 'Jerry';

UPDATE namenum
SET person_id =  3
WHERE first_name ='Mary';

UPDATE namenum
SET person_id = 4
WHERE first_name = 'Shelly';

And so did this one, well, not really: one small issue:

Code:

UPDATE namenum
SET ph_number = 777-4545
WHERE first_name = 'John';

UPDATE namenum
SET ph_number = 555-5656
WHERE first_name = 'Jerry';

UPDATE namenum
SET ph_number = 987-6543
WHERE first_name ='Mary';

UPDATE namenum
SET ph_number = 767-7776
WHERE first_name = 'Shelly';

I don't know how to fix that problem. I attempted to surround the numbers with quotes, but that's obviously not the way to do it. I would have to convert the input of the columns into VARCHAR2 or something so the operator doesn't work. Regardless, I'm too lazy to figure out how to fix that right now, so I just took out the dashes and now I have a simplified database.

Now I can move on and if I really need something new, I'll just fix the table. See? Easy game:

Next, I think are Cursor Loops. I think I forgot to post about the For Loops, but whatever.

Quote

04-17-2011 , 10:42 PM

#31

_dave_

_Pooh_Bah_

Join Date: Feb 2005 Posts: 13,146

yeah it's be probably easier to start with a nicer language, but i dunno now you've gotten in to this one already, somewhat oracle-committed. just don't make that mistake if you come to making something to do an actual job lol

CREATE TABLE is indeed the correct way to make tables

yeah you can't have dashes in a number. i don't know any language that would allow this.

Whenever I've "studied" something, I prefer to read a book quickly cover to cover, knowing full well I'm not understanding it all or even much of it. Then go back and start again, following examples and such. makes it much easier to get over stupidness in the book such as expecting you to work on a database they haven't explained how to create yet!

Quote

04-18-2011 , 01:46 AM

#32

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

lol! I learn everything the hard way. Next time I get a book, I'll definitely do what you said. I sort of scanned the pages and thought: "ok, cool."

I'll try not to get Oracle committed. I'll (hopefully) come out of the other side a better programmer. I am also sure I'll come out the other side never wanting to see another database again.

I don't think the language itself is that hard, just that this book can make piling two lego blocks seem Herculean. I was looking at the reviews on Amazon, and there were a whopping NINE 5-star* reviews vs one 1-star (there were a handful of 2-stars as well). I fixed that problem.

*These ratings were given by people who clearly did not read the book, or were Oracle users who were already familiar with 10g/11g. At least a few DBAs reviewed it and corrected the praise on how all the codes were functional.

Quote

04-18-2011 , 09:16 AM

#33

jjshabado

Carpal Tunnel

Join Date: Jul 2006 Posts: 22,732

This thread is now making me a little sad.

Please quit. You now know more than enough to add PL/SQL to your resume. Any more time spent on advanced functions will probably be wasted.

Your dedication is great - and you could make much better use of it picking a more widely used and even more importantly - more fun language/project.

Quote

04-18-2011 , 04:23 PM

#34

clowntable

Carpal \'Tunnel

Join Date: Jun 2006 Posts: 45,557

Gotta agree lol. If you've never done it do a Djange or Rails project next. Should be a very different experience and you can actually use some DB stuff

Quote

04-18-2011 , 05:40 PM

#35

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

Just give me a few more days to tie up some loose ends. I agree that this thread should make people sad.

I really don't know where to go next. I sort of want to do the other SQL languages and try to create a functional program. v1 back-ended with postgre and the other back-ended in My. I figure those will be easy enough.

Ugh....

I'm so sorry this thread is a fail, but I see it as a learning lesson in so many ways. One of the reasons I continued on was because quitting is a public embarrassment. Now that I have permission, I can quit, but now that I am this far, I want to know more. Such an ugly dichotomy, I know.

Next log will either be:

MySQL and PostgreSQL learning and development log;

or

HTML5 and CSS3 learning log.

I think I can handle either one of those.

I sincerely apologize for starting a fail thread.

I never worked in DJango or Rails, though I do have a decent book on Rails. I would rather get Ruby out of the way before stepping into Rails though. I know that many people think it's not important to learn Ruby first, but alas, I am a stubborn pig.

Quote

04-18-2011 , 06:12 PM

#36

_dave_

_Pooh_Bah_

Join Date: Feb 2005 Posts: 13,146

This log isn't fail at all imo it's quite interesting.

you may as well "finish" pl/sql as per this book, since you've gotten the basics so far. Also I'm sure there will be valuable tips relevant to all database design throughout, and these will become more obvious only once you notice they appear in the other rdbms also.

Quote:

I really don't know where to go next. I sort of want to do the other SQL languages and try to create a functional program. v1 back-ended with postgre and the other back-ended in My. I figure those will be easy enough.

do you have an "end goal"? like, what is the idea behind this "functional program"? that's probably my favourite way of learning. start with an idea, figure out what's possibly needed to make it happen, learn and experiment with the goal in mind.

Quote

04-18-2011 , 07:11 PM

#37

jjshabado

Carpal Tunnel

Join Date: Jul 2006 Posts: 22,732

I don't think this thread was a failure. Learning technologies is never a failure. But I don't agree that you should finish the book just for the sake of finishing the book.

My main issues are that the more advanced features of PL/SQL are rarely used, rarely expected of a new hire (at least of a new hire that doesn't actually have work experience), and no fun to learn/use.

Even at a really high level, I think the general trend is moving away from putting business logic that requires things like PL/SQL in the database. It requires an expensive license, can't be tested very easily, and is hard to scale.

Quote

04-19-2011 , 01:23 AM

#38

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

There's a few things in the book I think I would like to learn. I really doubt I can place "PL/SQL" on a resume since I don't have things like: education, work experience, a functional program, certification, or anything else to prove I know what is up or down. What would I do, link to this thread? I doubt they'd want to hire a whiner.

The idea for the SQL-backed program is just a free version of Quickbooks. Nothing too original or spectacular, just a functional program to show prospective employers/clients/whatever. Basically input client's names, what they bought, paid, owe, etc. I'm guessing another table for inventory, prices, etc.

Considering those other languages are "easier" to use, I could probably get the SQL prototypes done in a few days. Of course, working out a good GUI for the actual program is another story completely.

I had another idea in mind that was poker-based, but I'm going to skip that for the time being.

Quote

04-19-2011 , 08:44 AM

#39

jjshabado

Carpal Tunnel

Join Date: Jul 2006 Posts: 22,732

Your resume should absolutely include PL/SQL at this point. Your resume isn't about proving to a perspective employer all of the things you know. It's about you telling them what you know and what you've done. At this point it doesn't sound like you've done very much (that's ok for many entry level jobs) and employers aren't going to be expecting a world class dba if you just list PL/SQL on your resume.

If an employer really wants PL/SQL they'll then ask you questions about it in an interviewer. That's where you have to prove that you know what you're talking about - and that doesn't mean pointing to certifications (which are often meaningless) or programs you've written (which you could have easily copied and tweaked). I seriously doubt that an interview for a junior position would ever ask advanced PL/SQL questions.

What features left in PL/SQL would you like to learn?

I'm really not trying to be really negative here. Like I said, learning any sort of language/technology is great and will help in the long run. But if your goal really is to focus on "learning web programming" this isn't a very efficient way to do it.

Quote

04-19-2011 , 10:02 AM

#40

clowntable

Carpal \'Tunnel

Join Date: Jun 2006 Posts: 45,557

I know you feel like you should get the language i.e. Python or Ruby out of the way first but you can really learn them as you go. If you got a book on Rails, just dive in and do Rails next and use a postgres database with it.
I'd suggest the following path:

1) Quickbrowse postgres online documentation, no need to "learn" anything. Just have a quick look and you'll see that you pretty much know most you'll need later. In fact you can skip this step altogether because the relevant database stuff will be explained in the Rails tutorials anyways
2) Do "Ruby in 20 minutes": http://www.ruby-lang.org/en/documentation/quickstart/
3) Do any of the Rails online tutorials, they are pretty excellent. Rails for Zombies seems like it would be awesome for an online journal like this
http://railsforzombies.org/

It has exercises and you "advance" and can unlock stuff by completing exercises etc.

Just trust me on this, the webframework tutorials do a pretty good job at teaching the stuff you'll need from relevant DB knowledge to webserver stuff, HTML and CSS

Basically I agree that you can pot PL/SQL on your resume as of now; finish a Rails tutorial and you can pretty much put this down:
PL/SQL - Oracle
Postgres
Ruby and Rails
Webserver configuration
HTML+CSS

Which is pretty decent for a junior level position. The greatest benefit with going this route is that you can start to build interesting stuff pretty quickly which means the learning curve is accelerated a lot. You can just sit there and ponder what an interesting project to stay sharp could look like and build say "my own pokerblog/pokerwebsite"; "a pokertraining site"; "a website to log my IT studies; create study plans etc"

Last edited by clowntable; 04-19-2011 at 10:07 AM.

Quote

04-19-2011 , 07:52 PM

#41

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

Quote:

Originally Posted by jjshabado

What features left in PL/SQL would you like to learn?

I'm really not trying to be really negative here. Like I said, learning any sort of language/technology is great and will help in the long run. But if your goal really is to focus on "learning web programming" this isn't a very efficient way to do it.

Honesty is good.

I am not just trying to focus on "web programming." Honestly, I have no idea how I got stuck in this particular windmill, but that's not important right now.

What do I want to learn about PL/SQL? Jees, I don't know now, but definitely not going to use this book anymore. I guess it is that psychological bump that tells me "I'm not good enough" that throws me to the wind (life issue, whatever), so I want to be perfect, and this is re-enforced since I know I am competing with people that have a degree or an impressive resume (no mention of the very high unemployment rate in Los Angeles [should probably look at every damn city in the nation]).

I actually got started in programming because I wanted to develop apps and games for cell-phone and notepads. Obviously, I got way side-tracked by learning HTML/CSS/javascript, but I was totally clueless and wanted to start at the easy stuff. As for learning Java, I know how to do some basic GUI, so that is a plus (once again, not confident in my ability).

The point is that I thought (and still think) it is better for someone like me to focus on:

- if I want to find a job, find a programming language that is in demand that has low competition. SQL fits that perfectly. I also think that HTML5 and CSS3 are important for this. Ironically, in order to even write for many websites, you have to know HTML and CSS, which is sort of screwy that they get a programmer/writer for writer fees, but that is the way the world is turning out.

- If I want to build the apps and games, I have to do my own work, I think, since I can create the idea and implement it without having to "prove" to anyone that I am able to do it. How many people "know" how to program in Java?

The same issue with Java is apparent with Rails. It appears to me that there are a bunch of schlubs who are out programming Rails and apparently people are buying it up (this is a personal experience that cost me tons of money): how do you compete with a bunch of low-ballers even if you are more knowledgeable than they are? So for now, there is no way that I can differentiate myself from the faceless masses. I don't even think that the lead programmers know how to distinguish good code from bad, and I most certainly don't trust interviewers to know the difference. They'll go with those who have the resume or whatever, not to someone like me who will take the time to create an impressive code in Rails/Ruby/whatever.

So all the cool kids on the block are now using Rails and honestly, I'm not in the mood to play. To correlate to poker: all the No Limit players became the cool kids, and so a bunch of braggarts gave out tons of bad advice and called themselves winners. Unfortunately, many people believed the hype and took all the bad as gospel and now the believers (customers) are mis-informed by the losers (the fly-by-nights companies who **** the customers and its employees). Rails is turning into the NLHE of programming, IMO.

So the framework is basically find a day job and then work on my own projects at night.

Right now I have a knife, a spoon, a plate, but no food. And I am basically a five year old learning how to coordinate it all without spilling it on the floor. I think it is time to actually create a functional program, if for no other reason to have the confidence to face down the interviewer. The second reason is to feel like I haven't wasted the past few months of my life learning this stuff. And the third reason is: I did it, and that is priceless, so to speak.

Yeah, I have to open my own website too, but I'm not going to hang my dirty laundry right now.

---------------------------------------

I went ahead and downloaded MySQL, PSQL, and the relevant IDE's. I'm pretty shocked at how much I actually picked up from this book. It wasn't until I stepped down and began working on the basics that I realize where I actually stand now.

It's pretty easy so far. I think I can get the basic project done within a week, so there is really no excuse to not build it and be done with it. As I said the back-end will the one of each SQL. The front-end will be Java, obv. I'm really interested in seeing how it is all put together. To someone like me, I am very excited to see how to use these two programs together. This is exciting for me anyways.

Go ahead, laugh.

Quote

04-21-2011 , 06:56 AM

#42

Gullanian

Carpal \'Tunnel

Join Date: Dec 2006 Posts: 14,014

Quote:

Originally Posted by daveT

w3schools to the rescue!

Just a word of warning, W3 schools quality is pretty poor, see:

http://w3fools.com/

I use it for quick reference if it pops up in my google searches, but as an authority, it's pretty crap so just be aware of this

Also in regards to your database table names, I personally don't like them (but this of course is heavily weighted towards preference. You can ignore me if you want, but in my opinion:

- Namenum isn't the greatest table name, (I'm assuming it means, numbers for peoples names). Name and phone numbers are properties of a higher level entity, Person. So I'd just rename the table to `Person` or something like that. It makes more semantic sense.

- Now we have the table called 'Person', we don't need the PK to be called 'PersonID', just 'ID' would suffice as calling ID from Person makes semantic sense, IE, Person.ID, as supposed to Person.PersonID, it's just duplicate information tbh in the field name that's not required.

^ Probably a bit nitty, but these semantics make a big difference when the databases and queries start getting more complex.

None of this makes much sense if 'PH_Number' doesn't mean phone number but at the risk of wasting a ton of everyones time:

Quote:

Originally Posted by daveT

,ph_number NUMBER);

First of all PH_number could be named better, something like 'Telephone' makes perfect sense, 'PH_number' (as I am experiencing now) can cause a bit of confusion, it makes sense to you, but to someone looking at it, it doesn't really.

You would instinctively think a phone number should be stored as a number, but it's one of those ones that should be stored as a string for a couple of reasons:

- It's not a number in the tradition sense, that is, you wont be doing any sums on it!
- It can be stored in multiple formats, ie (+44)3535, etc.

Also in regards to your inserts, you shouldn't be allowing nulls on a lot of those values, IE, if I inserted a record just specifying the first name, I'd properly require the surname (not always but probably in this example). Nulls shouldn't be used, unless their application is suitable. Knowing when they are suitable can be a bit tricky if you are learning, but it's worth reaadnig up on and applying properly.

Quote:

Originally Posted by daveT

So, this format worked:

[CODE]UPDATE namenum
SET person_id = 2
WHERE first_name = 'Jerry';

Person_ID (or ID) should be a primary key, and auto incrementing, it might work differently in Oracle as I have no experience with it, but whenever you have to set a PK value that should be auto incrementing something is wrong.

Quote

04-23-2011 , 05:54 AM

#43

mrkeith

newbie

Join Date: Dec 2010 Posts: 27

I use Pl/SQL pretty much every day. I tend to stay away form the object stuff as it wrecks my head. Its bad enough trying to maintain those sections of code.

PL/SQL is only available on the oracle database and it is a very nice language.

From what I can see, your problems are in not understanding the sql syntax, but I have only had a brief glance.

What version of the database are you running your oracle against?

A couple of tips:
i) Do not ever have variable names the same as table names. It can lead to a heap of trouble and confusion.
ii) Avoid using Oracle reserved words. More trouble

I see you are using Oracle Sql Developer. Use the debugger to walk through your programs.

Good luck with this

Quote

04-23-2011 , 06:01 AM

#44

mrkeith

newbie

Join Date: Dec 2010 Posts: 27

For autoincrementing, you want to use an Oracle Sequence.

You create a sequence called example_seq as follows:

create sequence example_seq start with 1;

(There are a lot more options as well). One of the things you will notice is a jump in values if you disconnect your session and reconnect. This is due to sequence caching. If you don't want sequence caching, you can do the following:

create sequence example_seq start with 1 nocache;

Just note that you are never ever guaranteed gap free numbers with oracle sequences. Nocache is the closest you will get.

When you need to get a value into the variable you do the following:
(I'll just use an anonymous block) Its like a procedure but does not have a name.

declare
v_example_seq number;
begin
select example_seq.nextval
into v_example_seq
from dual;

end;

Quote

04-23-2011 , 06:11 AM

#45

mrkeith

newbie

Join Date: Dec 2010 Posts: 27

Quote:

Originally Posted by Gullanian

- Now we have the table called 'Person', we don't need the PK to be called 'PersonID', just 'ID' would suffice as calling ID from Person makes semantic sense, IE, Person.ID, as supposed to Person.PersonID, it's just duplicate information tbh in the field name that's not required.

I agree that it is not required, however there are a couple of strong arguments for actually calling it personID.

The main one is related to foreign keys. Ideally we do not want column names in our database which mean one thing when in one table and another when in a different table.

Suppsoe we have person_id in the person table and job_id in the job table.
Now we want to assign a job to a person so we have a personjob table which links persons to jobs

so now we have table personjob with
person_id, job_id

The more ID's we have the more important it is to distinguish them.

Quote

Page 2 of 2

First

1 2

Last

Post Reply Subscribe

...

Page 2 of 2

First

1 2

Last