PL/SQL程序设计

 

创建一个判断你幸福指数的函数

1、创建一个个人信息表

Happiness

2、创建一个带有显式游标的函数

Set serveroutput On;

Function  Happiness_judge(Name_in  In Happiness.Name%Type)

         Return Number

As

       Cursor  happiness_degree

       Is 

           Select  Likeability  From  happiness

                   Where name = Upper(Name_in);

        

         Happiness_rec   Happiness_cur%Rowtype;

         Retval           Number;

Begin

         Open  Happiness_cur;

 

         Fetch  Happiness_cur  Into Happiness_rec;

         If  Happiness_cur%Found

                  Then

                            If Happiness_rec. Likeability = ‘优秀’

                                     Then retval ;=10;

                            If Happiness_rec. Likeability = ‘一般’

                                     Then retval ;=8;

                            If Happiness_rec. Likeability = ‘还行’

                                     Then retval ;=7;

                            If Happiness_rec. Likeability = ‘合格’

                                     Then retval ;=6;

                            End If;

                           

                            Close  Happiness_cur;

                           

                            Return retval;

                   Exception

                            When Others  Then

                                     If Happiness_cur%Isopen Then

                                               Dbms_output.put_line(‘该我上了~’);

                                               Close  Happiness_cur;

                                     End If;

                   End;